agent mis-mapped source to target columns in generated dbt model, do

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.

Not specified by the original author.

TL;DR

Compiled SQL does not say which source file produced it, and the agent guessed wrong. Before editing, the agent must map the compiled output to its source: find the node ID, look up original_file_path (or patch_path) in the manifest, and edit that file. Then recompile and confirm the right model changed.

Error

agent failed to misread the compiled sql and fixed the wrong model in the dag

Steps

1. Stop editing. Identify the node: match the compiled SQL to a node via the filename or by searching node metadata. Expected: the correct node ID.
2. Look up the node's source file in the manifest (original_file_path). Expected: the exact file to edit.
3. Revert the wrong edit (git checkout -- [WRONG FILE]) if it was committed to the working tree. Expected: the mistaken change is undone.
4. Apply the fix to the correct source file. Expected: the edit lands where it belongs.
5. Recompile and verify with git diff that only the intended file changed. Expected: a clean, targeted diff.

When to use

• An agent fixed the wrong model after reading compiled SQL.
• Any workflow that goes from compiled output back to source.

When not to use

• The right file was edited but the fix was wrong (re-diagnose, do not remap).
• The agent works from source files directly (no mapping needed).

Tool compatibility

• dbt Core 1.0 and later; manifest.json for the mapping; git for revert.

Variant phrasings

Agent edited the compiled file in target/

Never edit target/ output; it is regenerated and the fix is lost.

Two models with similar names confused the agent

The manifest lookup disambiguates; names alone do not.

Why it happens

Compiled SQL is detached from its source: CTEs from ephemeral models, similar model names, and generated filenames all mislead. Agents that skip the manifest lookup guess, and guesses hit the wrong file.

Edge cases

• Ephemeral models have no compiled file of their own; their SQL appears inside downstream compiled files.
• Always verify with git diff --stat after an agent edit; a surprising file list means a mapping error.
• Keep a node-ID to file-path cheat sheet for the models the agent touches most.

Provenance

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

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

Thread query: agent failed: misread the compiled sql and fixed the wrong model in the dag

TL;DR: Compiled SQL does not say which source file produced it, and the agent guessed wrong. Before editing, the agent must map the compiled output to its source: find the node ID, look up original_file_path (or patch_path) in the manifest, and edit that file. Then recompile and confirm the right model changed.

Not specified by the original author.

TL;DR

Two joined tables both have the column and you did not say which one. Qualify every shared column with its table alias, or rename in a CTE before the join. Recompile and check the generated SQL has no bare ambiguous names left.

Error

Redshift column reference is ambiguous in dbt model

Steps

1. Read the error for the column name, then open the model and find the join where both sides carry that column. Expected: You find the join, e.g. orders joined to customers both having created_at.

2. Alias the tables (o, c) and prefix every reference to the shared column: o.createdat vs c.createdat. Do it for all shared columns, not just the one Redshift named. Expected: No bare references to shared columns remain.

3. Run dbt compile --select the model and grep target/compiled for the column to confirm qualification. Expected: Compiled SQL shows aliased columns throughout.

4. Run dbt run --select the model. Expected: Build succeeds, no ambiguity error.

When to use

• Redshift raises column reference is ambiguous on a dbt model
• joins between tables sharing column names like id, created_at, status
• after adding a new join that introduced a name collision

When not to use

• column does not exist (that is a missing column, different error)
• the ambiguity is inside a subquery you wrote by hand (same fix applies, but check the subquery)
• the error is at dbt compile time in jinja, not from Redshift

Tool compatibility

• dbt-redshift adapter; Redshift is strict about qualification
• dbt compile lets you inspect the generated SQL without running
• CTEs for renaming are portable across adapters

Variant phrasings

ambiguous column in a using() join

USING merges the column, so referencing it bare afterward is fine, but referencing table.col for a USING column is the error. Pick one style.

ambiguous only after adding a staging model

The new staging model inherited column names from the raw table. Rename at the staging layer so marts never see collisions.

Why it happens

Redshift resolves unqualified column names against all tables in scope and refuses to guess when two match. select * plus joins is how the collision sneaks in; the model worked until a second table gained the same column name.

Edge cases

• dbt's star expansion in some macros can reintroduce bare columns; check compiled SQL, not just source.
• Using CTEs that select * from both sides just moves the ambiguity; rename inside the CTE.

Provenance

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

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

Thread query: Redshift column reference is ambiguous in dbt model

TL;DR: Two joined tables both have the column and you did not say which one. Qualify every shared column with its table alias, or rename in a CTE before the join. Recompile and check the generated SQL has no bare ambiguous names left.