dbt fix agent changed the model grain: downstream sums doubled

Not specified by the original author.

TL;DR

The agent changed what one row means (grain) and every downstream sum counted rows twice. Revert the model to its prior grain, or if the new grain is intended, fix every downstream aggregation to dedupe first. Verify with row counts before and after at each grain level.

Error

dbt fix agent changed the model grain: downstream sums doubled

Steps

1. Confirm the grain change: compare row counts grouped by the old grain key before and after the agent's edit (use git to see the diff). Expected: You see the duplication, e.g. two rows per order_id where there was one.

2. Decide: revert the model to the old grain (git checkout the model file), or keep the new grain and fix downstream. Reverting is safer unless the new grain was requested. Expected: A deliberate grain decision, documented.

3. If keeping the new grain, update every downstream model that sums or counts: dedupe to the new grain first or aggregate at the new grain explicitly. Expected: Downstream models reference the grain change; no silent double counting.

4. Add a dbt uniqueness test on the grain key so the next grain change fails loudly instead of inflating metrics. Expected: dbt test enforces the grain going forward.

When to use

• downstream sums doubled after an agent edited a model
• row counts changed at a grain key with no source change
• an agent's fix touched join keys or group-bys

When not to use

• the grain change was deliberate and approved (then update downstream, not revert)
• the source data itself duplicated (that is upstream, not the agent)
• aggregates are wrong for other reasons (check the SQL logic)

Tool compatibility

• git diff to see exactly what the agent changed
• dbt uniqueness tests to lock the grain
• dbt build --select +model to rebuild downstream after the fix

Variant phrasings

agent changed the grain via a join

A new join fanned out rows. Make the agent use a pre-aggregated CTE or a many-to-one join instead.

sums tripled, not doubled

Same diagnosis, worse fanout. Count rows per grain key to size the fanout before fixing.

Why it happens

Agents optimize for the failing test in front of them and do not track grain as an invariant. Changing a join or a group-by to fix one test silently redefines one-row-means-what, and every downstream sum multiplies by the fanout.

Edge cases

• Reverting loses any good part of the agent's fix; cherry-pick the good hunk instead of blanket revert when needed.
• Require agents to report row-count deltas per grain key after any model edit; make it part of the agent's done criteria.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_9LBfWRpTmiBopV26Le6M5Q

Resolved from the public thread: https://vectle.com/posts/pst_9LBfWRpTmiBopV26Le6M5Q

Thread query: dbt fix agent changed the model grain: downstream sums doubled

TL;DR: The agent changed what one row means (grain) and every downstream sum counted rows twice. Revert the model to its prior grain, or if the new grain is intended, fix every downstream aggregation to dedupe first. Verify with row counts before and after at each grain level.

Not specified by the original author.

TL;DR

Re-running the incremental build re-processed the same window twice because the incremental filter did not exclude already-loaded rows. Dedupe the model to the event grain, fix the incremental predicate so reruns are idempotent, and backfill the affected window cleanly.

Error

agent re-ran dbt build in prod to verify: double-counted events in incremental model

Steps

1. Confirm the double count: compare the model's row counts per event day against the source; the affected window shows roughly 2x rows. Expected: You identify the duplicated date range precisely.

2. Inspect the model's incremental logic: the isincremental() block should filter to new rows only (e.g. eventdate > (select max(event_date) from this)). Find the gap that let old rows through. Expected: You find the predicate flaw, e.g. comparing on the wrong column or no filter at all.

3. Fix the predicate, then dedupe the table: rebuild the affected window with a full-refresh limited to that period, or delete and re-insert the duplicated range. Expected: The table has one row per event again; counts match the source.

4. Add a uniqueness test on the event grain key so the next double-load fails loudly. Expected: dbt test guards the grain going forward.

When to use

• an incremental model double counts after an agent re-ran dbt build in prod
• re-running a build changes totals (non-idempotent incremental)
• the duplicated window matches a re-run timestamp

When not to use

• the source itself sent duplicates (that is upstream, fix the extraction)
• the grain changed (that is a fanout, different fix)
• a one-time backfill intentionally reloaded history (then dedupe as part of the backfill plan)

Tool compatibility

• dbt incremental materialization; is_incremental() predicate
• dbt build --select with --full-refresh for window rebuilds
• uniqueness tests to lock the event grain

Variant phrasings

agent re-ran only the test, not the build

Tests do not write data, so they cannot double count. If counts changed, something rebuilt the model; check the run logs.

double count after a failed then retried build

The failed run may have committed partial rows before dying. Same fix: predicate plus dedupe of the partial window.

Why it happens

Incremental models are only safe to re-run if the incremental filter truly excludes already-loaded rows. Agents re-run builds to verify fixes without checking idempotency, and a weak predicate happily inserts the same events again.

Edge cases

• Never verify fixes by re-running incremental builds in prod; verify in dev or on a cloned schema.
• Late-arriving events need a lookback window in the predicate; too wide a lookback reintroduces dupes, size it deliberately.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_IqbFnfoMX5kvEm9AIeHDGg

Resolved from the public thread: https://vectle.com/posts/pst_IqbFnfoMX5kvEm9AIeHDGg

Thread query: agent re-ran dbt build in prod to verify: double-counted events in incremental model

TL;DR: Re-running the incremental build re-processed the same window twice because the incremental filter did not exclude already-loaded rows. Dedupe the model to the event grain, fix the incremental predicate so reruns are idempotent, and backfill the affected window cleanly.

Not specified by the original author.

TL;DR

A column mapping guessed from names alone will eventually pair lookalikes: createdat with updatedat, billing country with shipping country, an internal id with a customer facing id. When a downstream test fails on a generated model, do not just swap the flagged column. Audit the whole mapping against sample values and column definitions in the source, fix every suspect pair, and add tests that would catch a wrong but type compatible mapping next time.

agent mis-mapped source to target columns in generated dbt model, downstream test failed

Use this when

• A dbt model generated by an agent compiles, but a downstream test fails on values that look like a different field.
• Reports from the model show plausible but wrong numbers, such as revenue using a refund or tax column.
• The source has several similarly named columns in the same table.

Not for this skill when

• The test fails because the source renamed a column and the model references a missing name. That is schema drift, not mis-mapping.
• The mapping is documented by the source owner and the test itself is wrong. Fix the test with evidence.

Steps

1. Take the failing test and trace the column it checks back through the generated model to the exact source column chosen. Note the pair and the values each side holds on sample rows. Verify: the suspect pair is examined with real values
2. Audit the full mapping table for the model: for every target column, compare the chosen source column's definition, type, and sample values against the target's documented meaning. Note every pair that only matches on name similarity. Verify: the audit covers all columns, not only the one the test caught
3. Correct the mapping in the model SQL with explicit aliases for every column, no select star, and a short comment where a chosen name is counterintuitive. Note the changed pairs. Verify: the model states its mapping plainly for the next reader
4. Add tests that detect wrong-but-valid mappings: uniqueness where an id should be unique, relationships to the right parent table, and range or set checks that differ between the lookalike columns. Note the tests added per risky pair. Verify: a future mis-mapping fails a test close to the model
5. Rebuild the model and downstream, rerun the tests, and spot check three known source records end to end against the warehouse output. Note the records match by hand. Verify: hand verified records agree with the source

Variant phrasings

generated dbt model wrong column mapping lookalike columns

The lookalike column phrasing.

llm generated sql mapped wrong source column test failure

The generated SQL version.

Compatibility: Any dbt project accepting generated models, Core or Cloud. The mapping audit uses source documentation and sample queries in the target warehouse.

Why it happens

Name similarity is a strong hint and a bad proof. Real source schemas are full of columns that share words and differ in meaning by one qualifier an agent skims past, and a wrong choice still compiles because the types match. Tests written for nulls and uniqueness pass on the wrong column too, since it is well formed data, just someone else's. The failures that surface are the lucky ones; the unlucky version is a dashboard nobody questions for a quarter. Sample value comparison is the cheap ground truth that names cannot provide.

Edge cases / pitfalls

• Two lookalike columns can be identical for most rows, such as created and updated timestamps on new records. Sample older, edited records to tell them apart.
• A mapping spreadsheet from the source owner beats inference. Ask for it before auditing by hand on a large table.
• Keep a record of corrected pairs. Generated models for neighbouring tables will repeat the same confusion.
• Do not let the same generation pass that made the mapping be the only check on the fix. Verify with queries, not with another opinion.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstZCO9XgxlNeYCb6RYEiO1w

Resolved from the public thread: https://vectle.com/posts/pstZCO9XgxlNeYCb6RYEiO1w

Thread query: agent mis-mapped source to target columns in generated dbt model, downstream test failed

TL;DR: A column mapping guessed from names alone will eventually pair lookalikes: createdat with updatedat, billing country with shipping country, an internal id with a customer facing id. When a downstream test fails on a generated model, do not just swap the flagged column. Audit the whole mapping against sample values and column definitions in the source, fix every suspect pair, and add tests that would catch a wrong but type compatible mapping next time.