Data EngineerMột lần cập nhật SCD type 2 gồm hai việc: đóng dòng hiện tại của những khách có thuộc tính thay đổi, rồi chèn dòng mới cho họ và cho khách mới hoàn toàn.
Chạy cả hai trong một transaction để người đọc không thấy trạng thái nửa vời.
BEGIN;
-- 1. Close current rows whose tracked attributes changed
UPDATE dim_customer d
SET valid_to = :run_date - INTERVAL '1 day', is_current = FALSE
FROM stg_customer s
WHERE d.customer_id = s.customer_id
AND d.is_current
AND (d.city, d.tier) IS DISTINCT FROM (s.city, s.tier);
-- 2. Insert new versions and brand-new customers
INSERT INTO dim_customer (customer_id, city, tier, valid_from, valid_to, is_current)
SELECT s.customer_id, s.city, s.tier, :run_date, DATE '9999-12-31', TRUE
FROM stg_customer s
LEFT JOIN dim_customer d
ON d.customer_id = s.customer_id AND d.is_current
WHERE d.customer_id IS NULL;
COMMIT;Bước 2 dựa vào kết quả bước 1: khách vừa bị đóng dòng không còn dòng is_current, nên LEFT JOIN coi họ như chưa có và chèn phiên bản mới. customer_sk tự sinh (identity/sequence).
Các điểm cần nói khi phỏng vấn:
- So sánh null an toàn: dùng IS DISTINCT FROM, vì city <> NULL trả về null và bỏ sót thay đổi. Nhiều cột thì có thể so một cột hash của các thuộc tính.
- Staging phải sạch trùng trước, nếu không một khách sẽ bị chèn hai dòng hiện tại.
- Chạy lại cùng ngày không sinh thêm dòng vì lần hai không còn khác biệt nào.
- Dữ liệu đến trễ (thay đổi có hiệu lực từ trước dòng hiện tại) phải chèn vào giữa lịch sử, phức tạp hơn nhiều; nên nêu ra như giới hạn của cách làm này.
Lưu ý: dbt snapshot và Lakeflow pipelines của Databricks (trước là Delta Live Tables) đã hỗ trợ SCD type 2 sẵn. Vẫn nên viết tay được đoạn trên, vì câu này hay được hỏi để kiểm tra hiểu cơ chế.