agent's unit test mock drifted from the model's real columns

Not specified by the original author.

TL;DR

Unit tests feed the model fixture rows instead of real upstreams. When the model starts selecting a new column (or renames one) and the fixture is not updated, the unit test errors or silently tests stale logic. Sync the fixture to the model current inputs: add the new columns with realistic values, including the edge values the new logic branches on, and rerun the unit test.

agent's unit test mock drifted from the model's real columns

Use this when

• a dbt unit test fails with a missing column or binder style error after the model changed
• the unit test passes but the model fails in real runs on a column the fixture lacks
• fixture rows in the unit test YAML predate a model refactor

Not for this skill when

• the model change was wrong and should be reverted, the fixture is correctly exposing it
• the failure is a generic test on real data, not a mocked unit test
• you use an older dbt without unit tests, this class of fixture does not exist yet

Steps

1. Diff the model inputs against the fixture: list the columns the model selects from each ref or source (from its SQL), and list the fixture columns in the unit test given rows for those inputs. Verify: column differences between model and fixture are listed explicitly.

2. Update the fixture rows to the current shape: add the new columns, rename changed ones, and remove dropped ones. Keep at least one row exercising each branch of the changed logic. Verify: fixture columns match the model inputs exactly.

3. Align formats: fixture values use the YAML format (or csv fixture files) consistently, with types that match (dates quoted as dates need, numbers unquoted). Verify: the unit test parses the fixture without type coercion surprises.

4. Run dbt test --select for the unit test (resource type unit_test where your version distinguishes) and confirm pass, then run the model against a small real sample if available. Verify: the unit test passes on the synced fixture and the model behaves the same on real sample data.

Variant phrasings

expected output drifted too

Model logic changes usually move the expected rows as well. Update expect rows in the same edit and note why in the commit, so reviewers see the intended behavior change.

fixture lives in csv files

CSV fixtures hide column order and type issues differently than inline rows. Check headers after model changes, not just row values.

Compatibility: dbt-core unit tests, 1.8 and later (and the earlier experimental unit testing in dbt-utils style setups conceptually). Fixtures defined in YAML with given inputs and expect outputs.

Why it happens

A unit test is only as honest as its fixture. The fixture is a hand-maintained snapshot of the upstream contract, and nothing in dbt forces it to track the model SQL. Model edits therefore drift the fixture silently: the test keeps validating yesterday shape, failing loudly on missing columns if you are lucky, and passing vacuously if the drift hides in unused columns.

Edge cases / pitfalls

• Add a schema or contract on the real upstream so drift is caught where it starts, not only inside unit fixtures.
• When a model gains a column for a narrow branch, fixture rows that never exercise the branch give false confidence; add the branch row.
• Treat fixture updates as part of the model change checklist in review, like updating tests after any code change.

Provenance

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

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

Thread query: agent's unit test mock drifted from the model's real columns

TL;DR: Unit tests feed the model fixture rows instead of real upstreams. When the model starts selecting a new column (or renames one) and the fixture is not updated, the unit test errors or silently tests stale logic. Sync the fixture to the model current inputs: add the new columns with realistic values, including the edge values the new logic branches on, and rerun the unit test.

Not specified by the original author.

TL;DR

dbt unit tests feed mocked rows into your model, and the test fails when the fixture does not match the model's real inputs: wrong column names, wrong types, or missing columns. Open the unit test YAML, compare the fixture to the model's select list, fix the mismatch, and rerun the test.

Error

dbt unit test failed on mocked ref data

Steps

1. Open the unit test definition (the YAML file with unit_tests:) and read the given input fixtures. Expected: you see the mocked rows and their columns.
2. Open the model SQL and list the columns it actually selects from that ref. Expected: you have the true column list to compare against.
3. Fix the fixture: rename misspelled columns, add missing ones, and match types (a string '1' is not the integer 1). Expected: fixture columns match the model's expectations exactly.
4. Check the expect block too: expected output rows must match the model's output columns. Expected: both sides of the test are consistent.
5. Run dbt test --select [UNIT TEST NAME]. Expected: the unit test passes.

When to use

• A dbt unit test fails and the failure involves the mocked input rows.
• You just changed the model's columns without updating the test.

When not to use

• The fixtures are right but the model logic is wrong (fix the model).
• The failure is a parse error in the YAML (fix the YAML structure).

Tool compatibility

• dbt Core 1.8 and later (unit tests are a 1.8+ feature), all adapters.

Variant phrasings

Unit test fails: column not found in mock

The fixture is missing a column the model selects; add it.

Unit test passes the wrong rows to the model

The input ref name in the fixture does not match the ref the model uses.

Why it happens

Unit tests isolate the model from the warehouse by substituting fixture rows for real refs. The substitution is literal: any drift between the fixture shape and the model's real inputs breaks the test, even when the model itself is fine.

Edge cases

• Fixture rows need the same types the warehouse would return; type mismatches fail comparisons silently.
• Models with ref calls inside conditionals still need fixtures for every ref, even rarely used ones.
• Overriding a fixture with overrides changes the model config for the test; make sure the override is intentional.

Provenance

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

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

Thread query: dbt unit test failed on mocked ref data

TL;DR: dbt unit tests feed mocked rows into your model, and the test fails when the fixture does not match the model's real inputs: wrong column names, wrong types, or missing columns. Open the unit test YAML, compare the fixture to the model's select list, fix the mismatch, and rerun the test.

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.