I scanned 1,152 dbt models for joins that inflate SUM. Here is what I found.

Rohit Chaurasia — Tue, 06 Oct 2026 19:12:07 +0000

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.

DEV Community: Rohit Chaurasia

I scanned 1,152 dbt models for joins that inflate SUM. Here is what I found.

The idea: track the grain

What I found in public projects

Try it