There is a bug in analytics SQL that never throws an error.
select o.customer_id, sum(o.amount) as revenue
from orders o
left join payments p on p.order_id = o.order_id
group by o.customer_id
Customer 1 has two orders worth ₹150 in total. They paid in five instalments. After the join, each order appears once per payment, and SUM adds it every time. The query says ₹400.
This is called a fan out, or a fan trap. Its cousin is the chasm trap: one table joined to two one to many tables at once, where the two multiply each other. BI tools have some protection against it inside their semantic layers. Plain SQL and dbt have none.
The idea: track the grain
The grain of a table is what one row means. One row per order. One row per (customer, day). If you know the grain of both sides of a join, you know whether the join can repeat rows. If it can, any SUM or COUNT over the repeated side is suspicious.
You already declare most of this in dbt. A unique test on order_id says stg_orders is one row per order. grainguard reads those declarations, infers the rest from the SQL (GROUP BY, DISTINCT, QUALIFY ROW_NUMBER() = 1, LIMIT 1) and carries the grain through CTEs, subqueries and refs in dependency order.
Then for every aggregate it asks one question: can the rows this column comes from be repeated by a join? If yes, and the aggregate is sensitive to duplicates (SUM, COUNT, AVG), it reports it with a fix. MIN, MAX and COUNT(DISTINCT) are never reported.
ERROR FANOUT_AGG models/marts/customer_revenue.sql
SUM(o.amount) is computed after LEFT join to stg_payments (alias p)
on p.order_id = o.order_id [one row per (payment_id)].
Rows of stg_orders (alias o) can be matched more than once,
so this value is inflated.
Fix: Aggregate stg_payments to one row per join key in a CTE before joining.
What I found in public projects
I ran grainguard 0.1.0 on 14 public dbt projects.
| Models | 1,152 (3.2% could not be parsed) |
| Joins | 765: 236 provably safe, 66 provably fan out, 463 unknown grain |
| Aggregates after a join | 346 |
| Flagged | 89 (39 fan outs, 50 chasm traps) in 14 models |
Three lessons.
First, 61% of joins go to relations whose grain is not declared anywhere in the project. Often the unique tests live in a separate source package. A tool can only prove what it is told, and neither can a reviewer.
Second, every flagged aggregate I read by hand was a latent fan out: correct only if some uniqueness assumption holds, and nothing in the project states or tests that assumption. These are not bugs today. They are bugs the day the data changes.
Third, real code made the tool better. Two idioms caused false positives at first and are now handled with tests.
Try it
pip install grainguard
grainguard check path/to/your/dbt_project
It needs no database and no credentials, and the exit code makes it easy to put in CI.
Code, survey script and results: https://github.com/rohitchaurasia195-web/GRAINGUARD
If it is wrong on your SQL, please open an issue. That is the most useful thing you can send me.
Top comments (2)
Vấn đề join gây nhân đôi dữ liệu khi tính SUM là cơn ác mộng kinh điển trong data engineering. Mình từng mất cả buổi sáng chỉ để debug một dashboard báo cáo doanh thu sai lệch hoàn toàn do một lỗi fan-out từ bảng mapping trung gian. Cách tiếp cận quét tự động các model dbt như bạn làm thực sự rất cần thiết để kiểm soát chất lượng dữ liệu ở quy mô lớn. Thay vì chỉ kiểm tra logic join, mình thấy việc kết hợp thêm các dbt tests về tính duy nhất của primary key trước khi thực hiện join cũng giúp giảm thiểu đáng kể rủi ro này trong pipeline.
Cảm ơn bạn rất nhiều vì đã chia sẻ trải nghiệm thực tế và góc nhìn rất hữu ích! 🙏 Mình hoàn toàn đồng ý rằng việc kiểm tra tính duy nhất của key trước khi join có thể giúp phát hiện và ngăn ngừa rất nhiều vấn đề fan-out.
Mục tiêu của mình khi xây dựng cách quét tự động này cũng là để phát hiện sớm những rủi ro như vậy trước khi chúng ảnh hưởng đến dữ liệu và dashboard. Rất vui khi bài viết có thể gợi lại một vấn đề mà bạn cũng từng gặp trong thực tế. Cảm ơn bạn đã đóng góp thêm một góc nhìn rất giá trị! 🙏