Thực hành SCD loại 2: giữ lịch sử khách hàng và sản phẩm bằng SQL và dbt snapshot

Tháng Ba, chị Lan chuyển từ Hà Nội vào Đà Nẵng và được nâng lên hạng VIP. Đội CRM sửa lại đúng một dòng. Sáng hôm sau, báo cáo doanh thu theo thành phố tự dời toàn bộ đơn hàng chị mua ở Hà Nội từ năm ngoái vào Đà Nẵng, và cũng không còn ai biết chị từng ở hạng nào.

Lỗi này không ồn ào. Không có exception nào bắn ra, chỉ có những con số lệch dần, và người phát hiện thường là khách hàng chứ không phải bạn. Vì vậy, khi một FDE ngồi xuống với đội dữ liệu của khách, câu đầu tiên đáng hỏi là bảng khách hàng và bảng sản phẩm của họ có giữ lịch sử hay không.

Cách chữa kinh điển là slowly changing dimension loại 2. Có hai cách làm, nên học theo thứ tự: tự viết bằng SQL để hiểu cơ chế, rồi giao lại cho dbt snapshot để chạy được trong production.

Bạn sẽ dựng gì, và cần chuẩn bị gì?

Bạn sẽ có một bảng dim_customer giữ đủ lịch sử, một snapshot dbt cho bảng sản phẩm, và một câu join đưa đơn hàng về đúng phiên bản khách hàng tại thời điểm mua.

Phần chuẩn bị gồm một database SQL bất kỳ (Postgres là đủ), một project dbt phiên bản 1.9 trở lên vì ví dụ dùng cấu hình hard_deletes, và một bảng nguồn customers hoặc products kiểu ghi đè, tức mỗi lần có thay đổi thì dòng cũ bị sửa thẳng.

Cú pháp dưới đây đã được rút gọn để dễ đọc. Trước khi đưa lên môi trường thật, hãy đối chiếu với tài liệu dbt đúng phiên bản mình dùng.

Bước 1: vì sao phải có surrogate key?

Định nghĩa của Kimball rất ngắn: mỗi thay đổi Type 2 thêm một dòng mới vào dimension, mang các giá trị thuộc tính đã cập nhật. Dòng mới đó được gán một surrogate key mới. Từ thời điểm thay đổi trở đi, mọi bảng fact dùng key này làm foreign key.

Mã khách hàng KH001 lấy từ CRM vì thế không thể làm khoá chính nữa, vì cùng một người giờ có nhiều dòng. Hướng dẫn trên SQLShack cũng khẳng định: đã làm Type 2 thì không né được surrogate key. Kimball khuyên thêm tối thiểu ba cột theo dõi: ngày hiệu lực, ngày hết hiệu lực và cờ đánh dấu dòng hiện hành.

-- Ví dụ minh hoạ, rút gọn
CREATE TABLE dim_customer (
  customer_key    INT PRIMARY KEY,   -- surrogate key
  customer_id     VARCHAR(20),       -- natural key từ CRM
  segment         VARCHAR(20),
  city            VARCHAR(50),
  effective_date  DATE,
  expiration_date DATE,
  is_current      BOOLEAN
);

Kiểm tra: customer_key là khoá chính, còn customer_id thì không có ràng buộc UNIQUE. Nếu bạn lỡ đặt UNIQUE lên customer_id, ngay lần thay đổi đầu tiên câu INSERT sẽ vi phạm ràng buộc UNIQUE.

Bước 2: xử lý một thay đổi bằng tay

Khi chị Lan đổi thành phố và hạng, ta không UPDATE thuộc tính mà làm hai việc: đóng dòng cũ, rồi mở dòng mới.

-- Đóng phiên bản cũ
UPDATE dim_customer
SET expiration_date = '2026-03-01', is_current = FALSE
WHERE customer_id = 'KH001' AND is_current = TRUE;

-- Mở phiên bản mới với key mới
INSERT INTO dim_customer
VALUES (102, 'KH001', 'VIP', 'Đà Nẵng', '2026-03-01', NULL, TRUE);

Sau hai câu lệnh, bảng sẽ trông như sau:

customer_key	customer_id	segment	city	effective_date	expiration_date	is_current
17	KH001	Thường	Hà Nội	2025-01-10	2026-03-01	FALSE
102	KH001	VIP	Đà Nẵng	2026-03-01	NULL	TRUE

Ví dụ trên SQLShack mô tả đúng hiện tượng này: khi chức danh của khách hàng đổi, hệ thống tạo một bản ghi mới với CustomerKey mới, nên các fact cũ vẫn trỏ về phiên bản cũ. Đơn chị Lan mua năm ngoái mang key 17 nên vẫn được tính cho Hà Nội. Đơn từ tháng Ba mang key 102 nên thuộc về Đà Nẵng.

Kiểm tra: mỗi customer_id chỉ được có đúng một dòng is_current = TRUE. Nếu có hai, nghĩa là bước đóng dòng cũ đã bị bỏ sót.

Bước 3: giao việc lặp lại cho dbt snapshot

Viết tay thì hiểu sâu, nhưng chạy hằng đêm cho hàng chục bảng thì dễ sai. Tài liệu dbt mô tả snapshot là cơ chế cài đặt SCD Type 2 trên các bảng nguồn có thể bị sửa, đúng loại bảng như khách hàng hay sản phẩm.

# snapshots/products.yml — rút gọn, dbt 1.9+
snapshots:
  - name: products_snapshot
    relation: source('erp', 'products')
    config:
      unique_key: product_id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: invalidate

Chạy dbt snapshot. Lần đầu, mỗi sản phẩm có một dòng. Hãy sửa giá một sản phẩm ở nguồn, cập nhật updated_at, rồi chạy lại.

Kiểm tra: sản phẩm vừa sửa giờ có hai dòng. Dòng hiện hành có dbt_valid_to bằng NULL, vì đó là mặc định của dbt. Bạn cũng có thể dùng dbt_valid_to_current để đặt một giá trị khác, chẳng hạn một ngày ở xa trong tương lai, nếu công cụ BI của khách xử lý NULL kém.

Timestamp hay check: chọn theo độ tin cậy của nguồn

dbt khuyên dùng strategy timestamp, vì nó xử lý việc thêm hoặc bớt cột hiệu quả hơn check. Nhưng timestamp chỉ đáng tin khi cột updated_at ở nguồn đáng tin.

Thử hình dung một hệ ERP cũ, nơi bộ phận kế toán sửa giá trực tiếp trong database và không ai cập nhật updated_at. Với strategy timestamp, những thay đổi đó sẽ trôi qua mà không để lại dấu vết.

Lúc này nên dùng check, vì tài liệu dbt viết rằng strategy này hợp với các bảng không có cột updated_at đáng tin. Nó so sánh những cột bạn liệt kê để phát hiện thay đổi.

    config:
      unique_key: product_id
      strategy: check
      check_cols: ['price', 'category']   # rút gọn

Một cách kiểm tra nhanh ở hiện trường là sửa thử một dòng trên môi trường test của khách, rồi xem updated_at có nhảy theo hay không. Năm phút thử như vậy giúp bạn tránh được nhiều tuần lịch sử bị hổng.

Ba lỗi khiến lịch sử sai mà không ai hay

Lỗi thứ nhất là unique_key không thực sự duy nhất. dbt dùng key này để ghép dòng nguồn với dòng snapshot, nên tài liệu yêu cầu key phải duy nhất thật sự. Một bảng sản phẩm có cùng mã ở hai kho sẽ bị ghép nhầm. Trước lần chạy đầu, hãy chạy SELECT product_id, COUNT(*) ... GROUP BY 1 HAVING COUNT(*) > 1.

Lỗi thứ hai là quên các dòng bị xoá ở nguồn. Mặc định, dbt snapshot bỏ qua những dòng đã biến mất, nên một sản phẩm ngừng kinh doanh vẫn hiện như đang bán. Từ phiên bản 1.9, cấu hình hard_deletes: invalidate sẽ đóng các dòng đó bằng cách gán dbt_valid_to, đúng như cấu hình ở bước 3.

Lỗi thứ ba nằm ở phía người dùng bảng: join fact vào dimension mà chỉ lọc dòng hiện hành, tức tự biến Type 2 về lại kiểu ghi đè. Ở đây bảng orders chỉ mang product_id gốc từ nguồn, nên phải join theo natural key cộng khoảng hiệu lực.

-- Rút gọn; kiểm tra tên cột trong bảng snapshot của bạn
SELECT o.order_id, p.price
FROM orders o
JOIN products_snapshot p
  ON o.product_id = p.product_id
 AND o.order_date >= p.dbt_valid_from
 AND (o.order_date < p.dbt_valid_to OR p.dbt_valid_to IS NULL);

Cách này không mâu thuẫn với Bước 2. Join bằng surrogate key, như đơn của chị Lan mang key 17, dùng khi bảng fact đã được gán key ngay lúc nạp. Join theo natural key cộng khoảng hiệu lực dùng khi fact chỉ có mã gốc. Cả hai cho cùng kết quả nếu khoảng hiệu lực không chồng nhau.

Kiểm tra: tổng số dòng sau khi join phải bằng số đơn hàng. Nếu nhiều hơn, các khoảng hiệu lực đang chồng lên nhau. Nếu ít hơn, có đơn rơi vào khoảng trống giữa hai phiên bản.

Câu hỏi nào nên đặt cho đội dữ liệu ngay ngày đầu?

Một FDE hiếm khi được giao việc mang tên “làm SCD”. Yêu cầu thường đến dưới dạng một câu hỏi kinh doanh: doanh thu theo hạng khách hàng tại thời điểm mua, hay biên lợi nhuận theo giá niêm yết cũ. Nếu dimension đã bị ghi đè, câu trả lời đúng không còn tồn tại.

Vì thế, trước khi mở bất kỳ dashboard nào, hãy hỏi đội dữ liệu của khách một câu duy nhất: “Khi một khách hàng đổi hạng hay một sản phẩm đổi giá, bản cũ còn được giữ ở đâu không, và cột updated_at có đổi theo khi ai đó sửa thẳng trong database không?”

Nửa đầu câu trả lời cho biết những bảng nào cần snapshot, còn nửa sau quyết định chọn timestamp hay check.

Nếu câu trả lời là “ghi đè”, hãy đặt snapshot lên các bảng đó ngay trong tuần đầu. Lịch sử chỉ bắt đầu được lưu từ lần chạy đầu tiên, và mỗi ngày chậm trễ là thêm một ngày bạn không bao giờ lấy lại được.

Khi viết CV, hãy kể lại đúng chuỗi việc đó thay vì ghi “biết SCD”: bạn đã hỏi gì, phát hiện bảng nào bị ghi đè, bắt được unique_key trùng ở đâu, và sửa cách join để báo cáo theo kỳ khớp với số kế toán. Người phỏng vấn FDE muốn nghe một câu chuyện như thế, không phải thêm một từ khoá.