DataOps Peach

Data Foundations · Medallion: Bronze và Silver · Bài 2/4

Khử trùng lặp

Hai loại trùng cần hai cách xử lý. DISTINCT cho dòng trùng hoàn toàn, ROW_NUMBER có tiêu chí phá hoà cho dòng trùng khoá nhưng khác nội dung.

Học tương tác →

Hai loại trùng

Trùng hoàn toàn: mọi cột giống nhau. Thường do nạp lại cùng một lô. Trong bronze_orders có 80 dòng như vậy: 5100 dòng nhưng chỉ 5020 dòng khác nhau.

Trùng khoá nghiệp vụ, khác nội dung: cùng customer_id nhưng tên hoặc ngày đăng ký khác nhau. bronze_customers có 525 dòng cho 500 mã khách, tức 25 mã xuất hiện hai lần.

DISTINCT chỉ xử lý được loại thứ nhất. Chạy SELECT DISTINCT * trên bảng khách vẫn ra 525 dòng, vì không cặp nào giống nhau ở mọi cột.

flowchart LR
  BO[bronze_orders 5100] -->|DISTINCT| DO[_dedup_orders 5020]
  BC[bronze_customers 525] -->|ROW_NUMBER, rn = 1| SC[silver_customers 500]

Bỏ metadata ra khỏi phép so sánh

SELECT DISTINCT * EXCLUDE (_ingested_at, _source_file) so sánh trên các cột nghiệp vụ. Nếu để cả metadata, hai dòng giống hệt nhau nhưng nạp ở hai thời điểm sẽ có _ingested_at khác nhau và DISTINCT coi là hai dòng khác nhau. Trong lab cả bảng được nạp bằng một lệnh nên mọi dòng cùng _ingested_at và bạn chưa thấy khác biệt; nó lộ ra khi nạp nhiều lô (module 04).

Giữ bản nào?

Với dòng trùng khoá, bạn phải chọn một bản. Các chiến lược thường gặp:

Không có đáp án đúng cho mọi trường hợp. Điều bắt buộc là ghi lại chiến lược và lý do ở nơi người khác đọc được: comment trong code và một dòng trong bảng kiểm toán (bài 2.4).

Ví dụ trong dữ liệu lab: mã C0057 có hai dòng, "Trần Thanh Hà" đăng ký 2024-03-25 và "Nguyễn Thu Tâm" đăng ký 2024-03-03. Lab chọn giữ bản có signup_date mới nhất, với giả định bản ghi sau là bản khách đã cập nhật thông tin.

ROW_NUMBER và tiêu chí phá hoà

SELECT * EXCLUDE (rn) FROM (
  SELECT *, row_number() OVER (
    PARTITION BY customer_id
    ORDER BY signup_date DESC, full_name, email
  ) AS rn
  FROM bronze_customers
) WHERE rn = 1

Nếu ORDER BY không xác định duy nhất thứ tự trong nhóm, database được quyền trả bản nào cũng được. Chạy hai lần có thể ra hai kết quả khác nhau, và bạn không tái hiện được con số của hôm qua. Kết quả phải tất định (deterministic): cùng đầu vào thì cùng đầu ra.

signup_date ở bronze là chuỗi dạng YYYY-MM-DD, nên sắp xếp chuỗi cũng cho đúng thứ tự thời gian. Với định dạng khác (ví dụ DD/MM/YYYY) phải ép kiểu trước khi sắp.

Lệnh hay dùng

Lệnh Để làm gì
SELECT count(*), count(DISTINCT customer_id) FROM bronze_customers phát hiện trùng khoá
SELECT DISTINCT * EXCLUDE (_ingested_at, _source_file) FROM bronze_orders bỏ dòng trùng hoàn toàn
row_number() OVER (PARTITION BY <khoá> ORDER BY <tiêu chí>, <phá hoà>) chọn một bản cho mỗi khoá

Lỗi hay gặp

Học tương tác →