An Agent 'Optimized' a Join and Nobody Noticed for Three Weeks

Day 5: The Join That Quietly Tripled Regional Revenue

Here's a bug class dbt tests were supposed to have killed years ago, and agentic pipelines just brought it back from the dead: silent join fanout. An agent can hand you SQL that's syntactically flawless, sails through every test you've got, and still wrecks your numbers — because 'looks right' and 'is right' are different bars, and nobody wrote a test for the one assumption the agent happened to break.

Cold open

Three weeks after a routine PR — title: 'refactor slow region revenue model, agent-assisted' — finance pings the data channel. Why is regional revenue 2.1x what the board deck showed last quarter? No pipeline failure. No alert. Not a single red check anywhere in the CI history. Just a number that had been quietly wrong for three weeks straight, compounding through three separate reporting cycles before anyone noticed reality had drifted away from the dashboard.

Rewind: what was actually asked

The original ask was about as mundane as it gets: 'this model is slow, clean it up.' The agent got pointed at a dbt model joining an orders table to a region lookup table, told to optimize, and did exactly that. The rewritten join ran faster, read cleaner, and — here's the part that bites — looked like an obvious improvement. It dropped what the agent's own commit message called 'a redundant filter.' That filter was not redundant. It was the only thing keeping the join one-to-one.

Why CI didn't catch it

The existing dbt tests on this model checked what most teams check: not_null on the key columns, a uniqueness test on the final order_id, and a row-count-within-tolerance test on the output. None of that encodes any assumption about the join's cardinality. The agent's SQL didn't trip a single one — it just made some orders match multiple lookup rows, which nudges total row count up and inflates summed revenue, but not by enough to trip a percentage-based row-count guard tuned for ordinary week-to-week swings. The tests were validating the shape of today's output. Nobody had written a test for the invariant the join actually depended on.

The bug, in SQL

-- BEFORE: region_lookup is SCD2 — it keeps one row per region per
-- effective date range. is_current = true is the ONLY thing that
-- makes this join one-to-one.
select
    o.order_id,
    o.amount,
    r.region_name
from {{ ref('orders') }} o
join {{ ref('region_lookup') }} r
    on o.region_code = r.region_code
    and r.is_current = true

-- AFTER: agent's commit message —
-- "removed redundant filter, region_code is already unique per order"
-- region_code is unique on ORDERS. It is NOT unique on region_lookup
-- once you drop is_current. Every order whose region has 2-3
-- historical rows now matches 2-3 times, and its amount gets
-- summed 2-3x downstream.
select
    o.order_id,
    o.amount,
    r.region_name
from {{ ref('orders') }} o
join {{ ref('region_lookup') }} r
    on o.region_code = r.region_code

Why a human reviewer would've caught it in 30 seconds

Anyone who's spent real time with an SCD2 lookup table has the reflex baked in: see a join against a table with an is_current or valid_to column, and the first question that fires is 'what's enforcing 1:1 here?' A reviewer with that muscle memory would've flagged the missing filter on sight, no query execution needed — pure pattern recognition. The step got skipped because the diff 'looked like every other agent PR that week': tidy rewrite, green CI, plausible commit message. Review fatigue on agent output is real, and it goes straight for the PRs that look the most routine.

The dbt-testing lesson this relearns

dbt tests were built to catch exactly this class of bug — but only if you write tests that encode assumptions, not just today's output shape. 'Row count didn't move much' and 'no nulls' describe what a table happens to look like right now. They say nothing about:

• ▹Grain — what one row of this model is actually supposed to represent
• ▹Uniqueness on the columns you're about to join on, on BOTH sides
• ▹Expected join cardinality — is this join supposed to be 1:1, 1:many, or many:many, and does the code actually enforce that

Guardrail for Day 5: reviewing agent SQL for join fanout

Make this non-negotiable in your PR template for any agent-authored SQL that touches a join: state the grain before and after every join, explicitly. If the agent — or the human merging its PR — can't fill this in, the PR doesn't merge.

• ▹Grain of the left input, stated as a sentence: 'one row per order_id'
• ▹Grain of the right input, stated as a sentence: 'one row per region_code' — and is that actually true, or only true under a filter?
• ▹Expected relationship of the join: 1:1, 1:many (intentional fanout, e.g. exploding line items), or many:many
• ▹A test that asserts output row count matches the expected relationship, not just 'row count within X% of baseline'
• ▹Flag any join where output rows can exceed max(input rows) unless that fanout is the explicit purpose of the model

-- tests/assert_orders_region_join_grain.sql
-- dbt test convention: a test PASSES when this query returns 0 rows.
-- This fails loudly the moment the join stops being 1:1,
-- regardless of whether overall row-count drift looks tolerable.
select o.order_id, count(*) as match_count
from {{ ref('orders') }} o
join {{ ref('region_lookup') }} r
    on o.region_code = r.region_code
group by o.order_id
having count(*) > 1

Pair that with a dbt_utils.unique_combination_of_columns test directly on region_lookup, scoped to is_current = true, so the lookup table's own uniqueness contract gets asserted independently of any downstream join. Two tests, two different failure modes covered: the source table's contract, and the join's actual behavior.

Close

The agent didn't do anything malicious, or even unreasonable — it optimized against the tests and the ask it was given, and both were silent on cardinality. That's the pattern running through this whole series: the agent can write the SQL, but it can't vouch for the SQL, because vouching requires assumptions nobody bothered to state. Tomorrow: turning this into a repeatable habit — writing the assumption down as a test before you ever hand the model to an agent to touch.

Extend your knowledge

• ▹Read dbt's docs on the built-in relationships test and dbt_utils's unique_combination_of_columns test — these are the direct tools for encoding cardinality assumptions, not just output shape
• ▹Try Elementary for anomaly detection on ratios (e.g. row-count-per-key over time) rather than raw thresholds — it's dbt-native and built specifically for catching partial fanout that a single global tolerance would miss; Great Expectations is a better fit if what you want instead is explicit, codified distributional or uniqueness expectations
• ▹Audit one existing dbt model in your warehouse: write down its grain and every join's expected cardinality in a comment, then check whether a test actually enforces it
• ▹Add a 'grain before / grain after' field to your team's PR template for any agent-touched SQL, and make it a required field, not optional