Bài tập: làm sạch bảng khách hàng

Bạn sẽ học được

• Chạy một quy trình làm sạch hoàn chỉnh từ staging đến bảng sạch.
• Đối soát số dòng ở từng bước.
• Xử lý NULL, trùng lặp, kiểu dữ liệu và ngày trong cùng một pipeline.
• Viết báo cáo chất lượng dữ liệu.
• Trả lời câu hỏi về từng quyết định làm sạch.

Đề bài

Bảng staging_customers_raw nhận dữ liệu từ ba nguồn: website, ứng dụng di động và nhập tay. Tất cả cột đều là text.

CREATE TABLE staging_customers_raw (
  id            bigint,
  name          text,
  email         text,
  phone         text,
  city          text,
  signup_date   text,
  segment       text,
  source        text
);

Dữ liệu có các vấn đề đã hồ sơ:

Câu 1 — Thứ tự bước

Nên làm sạch theo thứ tự nào và vì sao?

Trả lời: Hồ sơ → chuẩn hoá chuỗi → kiểm tra/ép kiểu ngày → tìm và khử trùng → gắn cờ thiếu → đối soát. Chuẩn hoá trước khi khử trùng để không bỏ sót cặp trùng; ép kiểu trước khi đối soát để báo cáo có kiểu đúng; gắn cờ thiếu sau cùng để không loại nhầm dòng trùng.

Câu 2 — Viết bước chuẩn hoá

Viết truy vấn tạo bảng sạch bước 1.

Trả lời:

CREATE TABLE customers_norm AS
SELECT
  id,
  regexp_replace(trim(name), '\s+', ' ', 'g') AS name,
  NULLIF(lower(trim(email)), '')              AS email,
  NULLIF(regexp_replace(phone, '[^0-9+]', '', 'g'), '') AS phone,
  lower(unaccent(trim(city)))                 AS city_key,
  trim(city)                                  AS city_raw,
  signup_date,
  lower(trim(segment))                        AS segment,
  lower(trim(source))                         AS source
FROM staging_customers_raw;

Điểm cần chú ý:

• NULLIF(..., '') biến chuỗi rỗng thành NULL để đếm thiếu chính xác.
• phone chỉ giữ chữ số và dấu +.
• city_key để join với bảng tham chiếu thành phố.
• city_raw giữ giá trị gốc để truy vết.

Câu 3 — Map thành phố

Làm sao gộp 14 biến thể về 5 thành phố?

Trả lời: Tạo bảng tham chiếu với khóa chuẩn hoá:

CREATE TABLE city_map (
  city_key text PRIMARY KEY,
  city     text NOT NULL
);

INSERT INTO city_map VALUES
  ('ha noi',  'Hà Nội'),
  ('hanoi',   'Hà Nội'),
  ('tp hcm',  'TP. Hồ Chí Minh'),
  ('tphcm',   'TP. Hồ Chí Minh'),
  ('ho chi minh', 'TP. Hồ Chí Minh'),
  ('da nang', 'Đà Nẵng'),
  ('dn',      'Đà Nẵng'),
  ('can tho', 'Cần Thơ'),
  ('hai phong', 'Hải Phòng');

Sau đó join theo city_key. Các khóa không có trong bảng map cần được báo cáo riêng, không tự động gán "Khác".

Câu 4 — Parse ngày

Viết bước parse signup_date, xử lý hai định dạng.

Trả lời:

SELECT
  id,
  signup_date,
  CASE
    WHEN signup_date ~ '^\d{4}-\d{2}-\d{2}$' THEN to_date(signup_date, 'YYYY-MM-DD')
    WHEN signup_date ~ '^\d{2}/\d{2}/\d{4}$' THEN to_date(signup_date, 'DD/MM/YYYY')
    ELSE NULL
  END AS signup_date_parsed
FROM customers_norm;

180 dòng sai định dạng nhận NULL và được gắn cờ ngay_khong_hop_le, không bị bỏ im lặng.

Câu 5 — Khử trùng

300 dòng thuộc 150 nhóm email trùng. Chọn bản ghi nào giữ?

Trả lời: Giữ bản ghi đầy đủ nhất, sau đó tạo sớm nhất, chốt hoà bằng id:

WITH xep_hang AS (
  SELECT
    id, email,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY
        (phone IS NULL) ASC,
        (signup_date_parsed IS NULL) ASC,
        id ASC
    ) AS hang
  FROM customers_norm
  WHERE email IS NOT NULL
)
SELECT id, email, hang
FROM xep_hang
WHERE hang > 1;

150 dòng có hang > 1 bị loại khỏi bảng sạch nhưng được lưu vào customers_merged kèm kept_id.

Câu 6 — Đối soát

Sau tất cả các bước, kiểm tra gì?

Trả lời: Bốn phép đối soát:

10.000 (staging)
 = 9.850 (sạch cơ bản)
 + 150  (bị gộp do trùng email)

9.850 = 9.850 (mỗi dòng có đúng một bản ghi đầu ra hoặc lý do loại)
email NULL: 420 dòng vẫn còn, được gắn cờ
signup_date NULL sau parse: 180 dòng, đúng bằng số sai định dạng ban đầu
segment không hợp lệ: 60 dòng, đúng bằng số đã hồ sơ

Nếu bất kỳ con số nào không khớp, pipeline có lỗi và phải dừng lại.

Câu 7 — Cột cờ

Nên thêm những cột cờ nào?

Trả lời:

ALTER TABLE customers_clean
  ADD COLUMN email_missing      boolean NOT NULL DEFAULT false,
  ADD COLUMN phone_missing      boolean NOT NULL DEFAULT false,
  ADD COLUMN signup_date_invalid boolean NOT NULL DEFAULT false,
  ADD COLUMN segment_invalid    boolean NOT NULL DEFAULT false;

Cờ cho phép phân tích riêng nhóm thiếu và ngăn người dùng báo cáo hiểu nhầm giá trị điền là thật.

Câu 8 — Segment không hợp lệ

60 dòng có segment ngoài 4 giá trị hợp lệ. Xử lý thế nào?

Trả lời: Không tự gán vào một nhóm. Gán segment = 'unknown', bật cờ segment_invalid, và báo cáo cho đội vận hành để sửa ở nguồn. Tự gán theo suy đoán sẽ tạo ra phân khúc giả.

Câu 9 — Báo cáo chất lượng

Viết báo cáo chất lượng dữ liệu 4 dòng.

Trả lời mẫu:

Báo cáo này đi kèm bảng sạch để người dùng biết độ tin cậy của từng cột.

Câu 10 — Ngăn lỗi tái diễn

Ba thay đổi nào cần làm ở nguồn?

Trả lời:

1. Thêm kiểm tra định dạng ngày ở form nhập và ứng dụng.
2. Chuẩn hoá thành phố bằng danh sách chọn thay vì nhập tự do.
3. Đặt UNIQUE trên email đã chuẩn hoá hoặc kiểm tra trùng khi tạo tài khoản.

Pipeline làm sạch là giải pháp tạm; ràng buộc ở nguồn là giải pháp bền vững.

Tự kiểm tra

1. Vì sao chuẩn hoá phải trước khử trùng?
2. Vì sao dùng NULLIF(..., '')?
3. Làm sao đối soát tổng số dòng?
4. Vì sao không tự gán segment sai vào nhóm có sẵn?
5. Nêu ba biện pháp ngăn lỗi ở nguồn.

Tóm tắt

• Làm sạch theo thứ tự: chuẩn hoá → kiểu → trùng → cờ → đối soát.
• Dùng bảng tham chiếu cho giá trị phân loại.
• Giữ giá trị gốc và cờ cho mọi bước xử lý.
• Đối soát tổng dòng ở mọi bước chuyển.
• Báo cáo chất lượng đi kèm bảng sạch.