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:
- Mới nhất: bản có ngày cập nhật gần nhất.
- Đầy đủ nhất: bản có ít cột rỗng nhất.
- Nguồn tin cậy hơn: CRM thắng file Excel nhập tay.
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
PARTITION BY customer_id: đánh số riêng trong từng nhóm cùng mã khách.ORDER BY signup_date DESC: bản mới nhất được số 1.full_name, emaillà tiêu chí phá hoà (tie-breaker): khi hai bản cùngsignup_date, vẫn có thứ tự xác định. Tiêu chí phá hoà phải làm thứ tự duy nhất trong nhóm; nếu hai bản vẫn có thể giống nhau ở các cột đã chọn thì thêm cột nữa.
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
- Dùng DISTINCT cho trùng khoá. Không bỏ được dòng nào và không báo lỗi; số dòng vẫn là 525.
- PARTITION BY sai cột. Chia theo
full_namethay vìcustomer_idsẽ gộp nhầm hai khách trùng tên và giữ lại các mã trùng. - Thiếu tiêu chí phá hoà. Pipeline chạy đúng trong lúc test, rồi một ngày số liệu lệch vài dòng mà không ai đổi code.
- Khử trùng ngay trên bronze. Bronze giữ nguyên; kết quả khử trùng ghi vào bảng mới.