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ì:
- Định nghĩa phân mảnh. Một người tính doanh thu gồm đơn pending, người khác không.
- Chậm và tốn. Dashboard mở 200 lần một ngày, lần nào cũng join và tổng hợp lại từ đầu.
- Sai đối tượng. Người làm marketing không nên phải biết
is_orphan_customerlà gì.
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:
sum(total_orders)của bảng kênh = số đơn của fact (4972).sum(order_count)của bảng ngày = số đơn completed của fact.
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
- Viết SQL trước, nghĩ grain sau. Kết quả "trông đúng" nhưng không ai nói được mỗi dòng là gì.
JOINđể lấy thuộc tính làm rơi các dòng không khớp dimension.- Không đối chiếu tổng. Số lượng dòng đúng không có nghĩa nội dung đủ.
- Định nghĩa nằm trong đầu người viết, không nằm trong code.