# Vì sao doanh thu của khách bị nhân đôi sau một lệnh JOIN

> Khoá chính đúng vẫn chưa đủ: muốn chặn lỗi, hãy gộp dữ liệu về đúng grain của measure trước khi ghép bảng.

Bản gốc: https://fdetimes.net/vi/bach-khoa/mo-hinh-hoa-du-lieu-grain-khoa-va-join-fan-out-cho-fde/

dbt Labs từng nhắc lại một sự cố mà người làm dữ liệu nào cũng sợ: vào một ngày thứ Hai, số doanh thu lịch sử bỗng tăng gấp đôi vì fan-out. Khoá chính bị trùng hoặc rỗng có thể khiến dòng bị mất hoặc bị đếm sai, và thường thì người dùng phát hiện báo cáo sai trước khi test kịp chạy.

Với FDE, kịch bản này nguy hiểm hơn với một kỹ sư dữ liệu nội bộ. Bạn ghép những bảng mình không thiết kế, trong một schema mới nhìn thấy tuần trước, và dashboard đầu tiên bạn đưa lên sẽ bị phía tài chính của khách so với sổ sách. Lệch một lần là mất niềm tin cho cả engagement.

Tin tốt là lỗi này có cơ chế rõ ràng và cách phòng có thể luyện thành phản xạ. Mọi thứ bắt đầu từ một câu hỏi bạn cần trả lời trước mỗi JOIN: một dòng trong bảng này đại diện cho cái gì?

## Grain phải được nói ra trước mọi thứ khác

Kimball, cái tên gắn với mô hình hoá theo chiều (dimensional modeling), định nghĩa grain là ý nghĩa chính xác của một dòng trong bảng fact. Grain phải được khai báo trước khi chọn dimension hay fact.

Ralph Kimball còn viết rằng lỗi thiết kế phổ biến nhất, bỏ xa các lỗi khác, là không khai báo grain của bảng fact ngay từ đầu quá trình thiết kế. Ở phía khách, bạn hiếm khi được thiết kế từ đầu, nhưng bạn vẫn có thể khai báo grain cho từng bảng trước khi viết dòng SQL đầu tiên.

Khoá chính là cách biến câu khai báo đó thành thứ máy kiểm tra được. Nếu `order_id` thật sự duy nhất và không rỗng, bảng `orders` đúng là "một dòng, một đơn". Nếu không, mọi phép đếm phía sau đều đáng ngờ.

## Một đơn 10 đô la trở thành 30 đô la như thế nào

Tài liệu của Holistics định nghĩa fan-out là khi phép JOIN nhân bản các dòng ở phía "một" của quan hệ một-nhiều, và hàm tổng hợp sau đó coi các bản sao là dữ liệu thật. Sigma gọi một dạng của lỗi này là fan trap: số tiền ở header đơn hàng bị lặp lại cho từng sản phẩm trong đơn khi JOIN với bảng chi tiết.

Thử hình dung một schema quen thuộc. Bảng `orders` có grain "một dòng là một đơn hàng" với cột `order_total`; bảng `order_items` có grain "một dòng là một sản phẩm trong một đơn".

Một đơn 10 đô la gồm ba sản phẩm sẽ xuất hiện ba lần sau JOIN, và SUM báo cáo nó thành 30 đô la. Khách muốn biết doanh thu và số sản phẩm theo từng khách hàng, và câu SQL đầu tiên nhiều người viết trông vô hại:

```sql
-- SAI: order_total bị lặp theo từng dòng order_items
SELECT o.customer_id,
       SUM(o.order_total)  AS revenue,
       COUNT(i.product_id) AS n_products
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.customer_id;
```

`n_products` đúng, vì nó nằm ở grain của `order_items`. `revenue` sai, vì `order_total` nằm ở grain của `orders` nhưng bị kéo xuống grain thấp hơn.

Mỗi đơn bị nhân với số sản phẩm của chính nó, nên khách mua giỏ hàng lớn bị thổi phồng nhiều nhất. Sai số không phải một hệ số cố định, và bạn khó nhận ra nếu chỉ liếc qua tổng.

## Sửa bằng cách gộp về đúng grain trước khi JOIN

Một bài phân tích độc lập trên SystemCraftPress đưa ra cách sửa gọn: tổng hợp phía "nhiều" riêng ra, trước khi JOIN, để phép nhân không bao giờ chạm tới con số tổng chính. OWOX nói cùng ý khi cả hai bảng đều mang số đo: gộp về grain chung rồi mới ghép.

```sql
WITH items_per_order AS (
  SELECT order_id,
         COUNT(product_id) AS n_products
  FROM order_items
  GROUP BY order_id          -- giờ grain là một đơn hàng
)
SELECT o.customer_id,
       SUM(o.order_total)  AS revenue,
       SUM(i.n_products)   AS n_products
FROM orders o
LEFT JOIN items_per_order i ON i.order_id = o.order_id
GROUP BY o.customer_id;
```

Sau CTE, hai phía của JOIN có cùng grain, quan hệ thành một-một, và không còn dòng nào bị nhân. `n_products` vẫn là số dòng sản phẩm, chỉ được đếm trước ở cấp đơn hàng. `LEFT JOIN` giữ lại những đơn chưa có sản phẩm, vốn sẽ bị `JOIN` thường lặng lẽ loại bỏ.

Để ý rằng trong ví dụ này, cả hai bảng đều có khoá chính hợp lệ mà vẫn bị fan-out. OWOX tóm lại tình huống đó: khoá đúng, grain thì không. Test khoá chính bảo đảm mỗi bảng đúng grain đã khai báo; việc gộp trước khi JOIN mới là thứ chặn phép nhân.

Khi chuỗi JOIN dài hơn, chẳng hạn `customers` với `orders` rồi với `order_items` trong một lệnh, mọi measure ở cấp khách hàng có thể bị nhân hai lần liên tiếp. Nguyên tắc vẫn vậy: mỗi tầng gộp về grain của tầng trên trước khi nối.

Các công cụ BI có lời giải riêng. Holistics cấu trúc lại truy vấn để fan-out không xảy ra thay vì vá bằng phép tính, còn Omni dùng symmetric aggregates để phép tổng hợp luôn đúng. Nhưng khi bạn viết SQL thô trong warehouse của khách, lớp bảo vệ đó không có mặt.

**Điểm mấu chốt:** Trước mỗi JOIN, hãy biết một dòng của mỗi bảng đại diện cho cái gì.

## Quy trình mang theo đến mọi khách hàng

Bước đầu là viết grain của từng bảng bạn sẽ dùng thành một câu, rồi nhờ người chủ dữ liệu phía khách xác nhận. Câu "một dòng là một lần thanh toán, kể cả thanh toán bị hoàn" thường lộ ra những hiểu lầm mà tên cột không cho thấy.

Bước hai là kiểm tra câu đó bằng dữ liệu. Truy vấn `SELECT order_id, COUNT(*) FROM orders GROUP BY 1 HAVING COUNT(*) > 1` phải trả về rỗng. Vì khoá trùng hay khoá rỗng đều làm dòng bị đếm sai, nếu dự án dùng dbt, hãy ghi cả hai điều kiện thành test để lần sau máy tự kiểm:

```yaml
models:
  - name: orders
    columns:
      - name: order_id
        tests: [unique, not_null]
```

Bước ba là trước mỗi JOIN, ghi chiều quan hệ và grain của từng measure bạn định cộng. Measure nào nằm ở phía "một" thì phải được gộp trước hoặc đi qua cơ chế như symmetric aggregates.

Bước cuối là đối soát. SystemCraftPress nhắc một điều dễ bỏ qua: hãy xác nhận số dòng, chứ đừng chỉ nhìn tổng. Đếm số dòng trước và sau mỗi JOIN, rồi so kết quả với một con số khách đã tin, như tổng doanh thu một tháng trong hệ thống kế toán, trước khi bất kỳ ai nhìn thấy dashboard.

## Những cách vá làm lỗi khó thấy hơn

Cách vá phổ biến nhất là `SUM(DISTINCT o.order_total)`. Nó cho ra số đúng ở mẫu thử, rồi âm thầm làm hụt doanh thu khi hai đơn khác nhau có cùng tổng tiền, vì DISTINCT chỉ khử trùng theo giá trị, không theo khoá.

Một cái bẫy khác là tin vào tên cột. Một cột tên `id` trong bảng của khách chưa chắc đã duy nhất, và bảng được nạp lại nhiều lần là nơi dòng trùng dễ lọt vào. Chỉ có test mới cho bạn biết.

Cũng đừng nghĩ lỗi chỉ chạm tới doanh thu. Ví dụ của Holistics dùng chính `orders` và `order_items`: `SUM(orders.delivery_attempts)` đếm một đơn nhiều lần và trả về con số thổi phồng. Bất kỳ cột cấp đơn hàng nào, từ phí giao hàng đến số lần giao, đều chịu chung số phận khi bị kéo xuống grain sản phẩm.

## "Tìm lỗi trong câu truy vấn này" nên được trả lời ra sao?

Nếu tin tuyển dụng FDE bạn nhắm tới nhắc đến data modeling hay dbt, hãy tập trước với một buổi phỏng vấn thử. Nhờ một người bạn đưa câu SQL sai ở trên kèm lời phàn nàn "doanh thu theo khách cao hơn sổ kế toán, lỗi ở đâu?", rồi trả lời thành tiếng theo đúng thứ tự: grain, quan hệ, cách sửa, cách kiểm.

Một câu trả lời mẫu nghe như sau:

> "`orders` một dòng là một đơn, `order_items` một dòng là một sản phẩm trong đơn. JOIN theo `order_id` là một-nhiều, nên `order_total` lặp theo số sản phẩm: đơn 10 đô la có ba sản phẩm bị tính thành 30.

COUNT vẫn đúng, chỉ SUM sai.
>
> Cách sửa là gộp `order_items` theo `order_id` trước rồi LEFT JOIN. Không dùng SUM DISTINCT, vì hai đơn cùng tổng tiền sẽ bị gộp làm một. Sau đó đếm số dòng trước và sau JOIN, rồi so tổng một tháng với số kế toán."

Nửa sau của câu trả lời là chỗ phân biệt người đã làm thật. Khi đã nói trôi được đoạn này, hãy ghi lên CV một lần bạn làm đúng như vậy với dữ liệu thật, kèm kết quả đối soát.

Lần tới khi số doanh thu trông đẹp bất thường, đừng vội vui. Hãy đếm số dòng trước và sau JOIN.

**Thử ngay tuần này:**

- Chọn ba bảng bạn hay JOIN nhất trong dự án hiện tại, viết grain của mỗi bảng thành một câu và chạy truy vấn GROUP BY khoá HAVING COUNT(*) > 1 để kiểm tra.
- Thêm test unique và not_null cho khoá chính của mọi model dbt chưa có, bắt đầu từ bảng chứa doanh thu.
- Mở một dashboard doanh thu đang dùng, đếm số dòng trước và sau mỗi JOIN trong SQL nguồn, ghi lại chỗ nào số dòng tăng.

## Nguồn

- [What's a Primary Key and Why Do We Test Them?](https://docs.getdbt.com/blog/primary-key-testing)

- [Fan-out and how Holistics resolves it](https://docs.holistics.io/docs/joins/fan-out-resolution)

- [How Holistics handles joins](https://docs.holistics.io/docs/joins/how-joins-work)

- [Resolving The Fan Trap with Sigma and Snowflake UDF](https://quickstarts.sigmacomputing.com/guide/tables_fan_traps/index.html)

- [Grain (Kimball Dimensional Modeling Techniques)](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/grain/)

- [Declaring the Grain](https://www.kimballgroup.com/2003/03/declaring-the-grain/)

- [Handling fan out with symmetric aggregates](https://docs.omni.co/analyze-explore/sql/symmetric-aggregates.md)

- [Your JOIN is quietly doubling your numbers](https://buttondown.com/SystemCraftPress/archive/your-join-is-quietly-doubling-your-numbers/)

- [Why Your Totals Are Silently Wrong after a Join: Finding Fan-Out before the Number Ships](https://www.owox.com/blog/articles/join-fan-out-wrong-totals)
