DataOps Peach

Data Foundations · Dimensional modeling và Gold · Bài 4/5

Gold: grain và định nghĩa nghiệp vụ

Mỗi bảng gold trả lời một câu hỏi. Viết grain thành lời trước khi viết SQL, ghi giả định nghiệp vụ ngay trong code, và đối chiếu tổng với fact.

Học tương tác →

Vì sao cần tầng gold riêng

Star schema đủ để hỏi bất cứ gì, nhưng để mỗi người tự viết SQL từ fact thì:

Gold là các bảng đã tổng hợp sẵn, mỗi bảng một câu hỏi, với định nghĩa đã thống nhất.

Ví dụ: ba con số "khách hàng hoạt động"

Ba dashboard báo 12.400, 11.850 và 15.200 khách hoạt động trong tháng. Cả ba đều đúng: một bên đếm khách có đơn completed trong 30 ngày, một bên đếm theo tháng lịch, một bên tính cả đơn huỷ. Vấn đề không nằm ở SQL mà ở chỗ chưa ai viết định nghĩa chính thức. Gold là nơi đóng băng định nghĩa đó.

Grain: "mỗi dòng là một ___"

Trước khi viết SQL, viết câu này ra:

Bảng Grain
gold_daily_revenue Mỗi dòng là một ngày × một vùng
gold_cancel_rate_by_channel Mỗi dòng là một kênh bán
gold_customer_360 (bài sau) Mỗi dòng là một khách

Grain cho biết số dòng phải là bao nhiêu (365 ngày × số vùng) và cột nào cộng được. Không rõ grain thì dễ join nhầm làm nhân dòng: một bảng "theo ngày" lỡ join thêm sản phẩm sẽ có nhiều dòng mỗi ngày, và SUM(revenue) trên đó ra gấp nhiều lần.

Ghi giả định ngay trong code

-- gold_daily_revenue · grain: một ngày × một vùng
-- Doanh thu chỉ tính đơn completed (thống nhất với Tài chính).
-- Đơn của khách không có trong dim_customer tính vào vùng 'không rõ', không ẩn đi.
-- Ngày không có đơn: revenue = 0, order_count = 0 (lưới ngày × vùng dựng từ dimension).

Người đọc bảng sau sáu tháng không cần đi hỏi ai.

Hai giả định nhỏ nữa của bài: 221 đơn completed thiếu giá (trong 305 đơn thiếu giá) có revenue NULL nên sum bỏ qua chúng nhưng order_count vẫn đếm; tỉ lệ huỷ chia cho mọi đơn của kênh, kể cả pending.

Đối chiếu với fact: bắt lỗi rơi dòng

Lần dựng đầu của bài dùng JOIN dim_customer để lấy vùng. Grain đúng (1095 dòng), không lỗi nào, nhưng phép đối chiếu cho thấy:

tổng revenue completed của fact − tổng revenue của gold = 1,254,226,000

Đó là doanh thu của các đơn mồ côi C99999: JOIN bỏ chúng vì không có vùng. Dựng lại với LEFT JOIN và coalesce(c.region, 'không rõ'), lưới thành 4 vùng × 365 = 1460 dòng, chênh lệch về 0.

Ẩn "không rõ" hay unknown là che giấu vấn đề chất lượng. Hiện ra thì có người hỏi "vì sao có 1,25 tỉ không rõ vùng?", và đó là câu hỏi đúng.

Tổng giữa các bảng gold phải khớp

Mỗi bảng gold đếm trên cùng một fact, nên:

Hai chênh lệch phải là 0. Đặt các phép này thành check tự động ở Module 05.

flowchart LR
  F[fact_orders] --> D[gold_daily_revenue · ngày × vùng]
  F --> C[gold_cancel_rate_by_channel · kênh]
  D --> K{tổng khớp fact?}
  C --> K

Lệnh hay dùng

Lệnh Để làm gì
FROM dim_date d CROSS JOIN (SELECT DISTINCT region ...) r LEFT JOIN ... lưới đầy đủ, giữ ô không có đơn
coalesce(sum(x), 0) ô không có đơn hiện 0 thay vì NULL
coalesce(channel, 'unknown') hiện nhóm thiếu dữ liệu thay vì ẩn
SELECT (SELECT sum(...) FROM fact) - sum(...) FROM gold đối chiếu, phải ra 0

Lỗi hay gặp

Học tương tác →