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

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.