7 dbt Anti-Patterns AI Should Catch Before Your PR Merges

A fan-out join is a join where the key repeats on one side, so each row from the other side comes back several times. Put one in a revenue model and the sum of gross_amount reports four times the real revenue when every order has four line items. The model still builds. The inflated total still looks like a plausible dollar figure, so the reviewer approves it and the pull request merges.

All seven dbt anti-patterns below work the same way. The code compiles, the model runs, and the result looks right. The cost arrives weeks later as a wrong figure, a slow model, or a warehouse bill nobody can explain.

A machine check should own these seven problems. A reviewer reading a long diff checks the logic against the ticket, so a reviewer looks for intent errors. None of the seven is an intent error. Each one has an exact definition, and a compiled rule evaluates that definition the same way on every run. A human applies it consistently on the first model of the day and less consistently by the last.

What a reviewer should see next to the diff.

First, the dbt terms these examples lean on. A dbt project is a set of SQL files called models, each building one table or view. A model reads from raw tables through source() and from other models through ref(). Those links form the project's dependency graph. A model's prefix marks its layer. A stg_ staging model cleans one raw source. The fct_ and dim_ models are the marts people query. A model is "downstream" of another when it reads from it. A change to a shared upstream model reaches everything downstream of it.

1. SELECT * in a Model Anything Depends On

SELECT * in a staging model reads as tidy code and behaves badly. The star couples the model to whatever columns the source holds today. When somebody adds a column upstream, your staging table widens on the next run.

-- models/staging/stg_orders.sql
-- Anti-pattern: the column list is whatever the source happens to hold today.
select * from {{ source('shop', 'orders') }}

The first cost is a wider scan, because every downstream model that also selects a star reads the new columns too. The worse cost is a name collision. A new upstream column arrives with a name a downstream model already uses. That downstream model then breaks, and it breaks in a file nobody edited.

-- models/staging/stg_orders.sql
-- Fix: name the columns. A new source column now changes nothing here.
select
    order_id,
    customer_id,
    order_status,
    gross_amount,
    discount_amount,
    ordered_at
from {{ source('shop', 'orders') }}

The check is a text match on the star, so any linter can run it in the editor. A reviewer sees the same three characters and cannot see the columns they will pull in after the source grows. Scope the rule to models that have dependents. A star in a one-off exploratory model harms nobody, and a rule that fires on that model gets switched off for the whole project. A dbt model contract enforces the declared column list at build time.

2. Joins That Fan Out Without Anyone Noticing

A join fans out when the join key is unique in one table and repeats in the other. Each row on the unique side comes back once per match on the repeating side. The model still builds, and every sum over the result is multiplied by the number of matches. Here stg_orders holds one row per order and stg_order_items holds one row per line item. Joining them on order_id returns each order once for every item it has.

-- models/marts/fct_order_revenue.sql
-- Anti-pattern: order_items repeats order_id, so each order row multiplies.
select
    o.order_id,
    o.gross_amount,
    i.sku
from {{ ref('stg_orders') }} o
join {{ ref('stg_order_items') }} i using (order_id)

With four line items per order, every order row appears four times, and a sum of gross_amount reports four times the real revenue. The fan-out join is the most expensive of the seven anti-patterns. The inflated total still reads as a plausible dollar figure, so nobody questions the total. Nobody reconciles a revenue total against the source system every week, so the wrong figure can survive for months.

-- Fix: collapse the many side to one row per key before the join.
with items as (
    select order_id, count(*) as item_count
    from {{ ref('stg_order_items') }}
    group by 1
)
select
    o.order_id,
    o.gross_amount,
    items.item_count
from {{ ref('stg_orders') }} o
left join items using (order_id)

Catching a fan-out join needs the cardinality of the join key, and cardinality is a property of the data. Nothing in the query text says whether order_id repeats in stg_order_items. A parser, a linter and a language model reading the file all share that blind spot. The check has to run against the warehouse. It is two counts on the many side of the join, and the key repeats whenever the two counts differ.

-- The check: the key repeats, so this join fans out.
select
    count(*)                  as rows_in,
    count(distinct order_id)  as keys_in
from {{ ref('stg_order_items') }}

3. Filters That Defeat Pruning

A filter that wraps a column in a function stops the warehouse from skipping partitions. The warehouse skips a partition by comparing the filter value against the range of values it stores for that partition. A column wrapped in date_trunc is a computed value with no stored range, so the warehouse reads every partition. Both queries below return the same rows, and one of them reads the whole table.

-- Anti-pattern: the filter wraps the column, so the pruner cannot use it.
select order_id, gross_amount
from {{ ref('fct_orders') }}
where date_trunc('month', ordered_at) = '2026-03-01'

-- Fix: a half-open range on the raw column. Same rows, three partitions.
select order_id, gross_amount
from {{ ref('fct_orders') }}
where ordered_at >= '2026-03-01'
  and ordered_at <  '2026-04-01'

A reviewer reads this query as ordinary. The SQL is short, the logic is right, and the result set is small. The full-table scan sits in the plan, and nobody opens the plan during a review. A lint rule finds the wrapped column from the text alone, so it fires in the editor while the author still has the file open. Our write-up of profiling a slow dbt query covers reading the plan.

The same rows, two ways. The left plan reads 480 partitions and the right reads three.

4. Non-Deterministic Ordering Treated as Stable

A LIMIT with no ORDER BY returns whichever rows the engine reaches first. An ORDER BY on a column with duplicate values has the same problem, because the engine breaks each tie however it likes. Both look stable in testing, because the data has not moved yet.

-- Anti-pattern: two events share one updated_at, so rn = 1 picks either row.
select * from (
    select *,
        row_number() over (
            partition by customer_id
            order by updated_at desc
        ) as rn
    from {{ ref('stg_customer_events') }}
)
where rn = 1

The window above numbers each customer's events newest first and keeps number one, which should leave one row per customer. Downstream logic then depends on an order the engine never promised. After the next reload the engine breaks the tie on updated_at the other way, so the model keeps a different row for the same customer. The dashboard number moves with no commit behind it, and somebody goes looking for a diff that does not exist.

-- Fix: break the tie on a column that is unique inside the partition.
select * from (
    select *,
        row_number() over (
            partition by customer_id
            order by updated_at desc, event_id desc
        ) as rn
    from {{ ref('stg_customer_events') }}
)
where rn = 1

The check reads the SQL alone, so it runs in the editor. It flags any LIMIT with no total ordering, and any window function whose partition and order cannot pin down one row. A reviewer misses both, because a LIMIT 1 and a row_number() window both look like finished code.

5. Incremental Models That Drop Rows or Duplicate Them

An incremental model adds only new rows on each run instead of rebuilding the whole table. That is what keeps a large model cheap to run. It has two defaults that cost you rows in opposite directions. A filter with no lookback window drops rows that arrive late. A config with no unique_key duplicates rows the table already holds. Neither default ever fails a run, so the model stays green while the row count drifts.

-- models/marts/fct_events.sql
-- Anti-pattern: no lookback window, and no unique_key on the config.
{{ config(materialized='incremental') }}

select * from {{ ref('stg_events') }}
{% if is_incremental() %}
where event_at > (select max(event_at) from {{ this }})
{% endif %}

The filter above loads only rows with a timestamp above the stored maximum. A row that lands later with an earlier timestamp fails that filter on every run, so it never enters the table. The model runs green and a slice of the data is absent. The gap is invisible, because you cannot see a row that never arrived.

The second default adds rows instead of hiding them. On most adapters a model with no unique_key appends every row the query returns, whether or not the table already holds it. dbt's incremental models documentation states that plainly. Widen the lookback on a model with no key, and every run appends three days of history the table already has.

-- Fix: a lookback window, plus a key so late rows merge instead of appending.
{{ config(materialized='incremental', unique_key='event_id') }}

select * from {{ ref('stg_events') }}
{% if is_incremental() %}
where event_at >= (select max(event_at) - interval '3 days' from {{ this }})
{% endif %}

A check finds both defaults from the file alone. The first finding is an incremental filter that reads strictly greater than the stored maximum, with no lookback window. The second is a config with no unique_key, which is a single grep over the config blocks. A reviewer misses both, because each one is an absence, and a diff shows what was written rather than what was left out. How wide the lookback should be depends on how late your source delivers rows, and nobody can automate that judgment. Whether the model has a lookback at all is a yes-or-no question, and the check answers it.

6. Tests That Cannot Fail

A dbt test is an assertion on a column, such as not_null or unique. It fails the build when the data breaks that rule. One config key produces the worst version of a test that cannot fail. Set severity: warn at the project level, and every test still runs and reports a result. None of them can block a merge again, because a warning does not fail the build.

# dbt_project.yml
# Anti-pattern: one line, and no test in the project can fail a build again.
tests:
  +severity: warn

The default severity is error. Once the key reads warn, a failing test becomes an error only when somebody passes --warn-error. dbt's severity reference spells that out. An audit that looks for missing tests finds nothing wrong, because every test is still there.

Two quieter versions do the same damage one test at a time. A not_null test on a column the database already declares NOT NULL can never fail, because the database rejects the null first. A unique test on a surrogate key the model generates tests your own hash function. Both pass forever and both count toward coverage, so the coverage number overstates what the suite protects.

The check for severity: warn is a grep over dbt_project.yml and every schema file. The check for the two quieter versions asks whether a given test has ever failed and whether it can. That needs test history, and dbt does not keep test results between runs. The data tests documentation says store_failures replaces the previous failures for the same test, so the store holds only the latest run. Collect run_results.json from each CI run, because that file is the only history you will have.

7. Undocumented Columns in a Model Others Build On

An undocumented column looks harmless, because nothing fails when you skip the description. It becomes expensive the moment somebody downstream has to decide what the column means with nothing to read. A human asks in Slack and waits for an answer. An agent picks the most plausible reading and reports a number.

# models/marts/schema.yml
# Anti-pattern: the column is declared, and nothing says what it counts.
models:
  - name: fct_orders
    columns:
      - name: net_revenue

The label net_revenue could exclude tax, refunds, canceled orders or all three. Every reading produces a defensible number. A human building on the model picks one reading, and it differs from the one the author had in mind. Two reports then disagree by the size of the refunds. An agent guesses faster, and it gives no sign that it guessed.

# Fix: one description an agent and a new analyst both read the same way.
models:
  - name: fct_orders
    columns:
      - name: net_revenue
        description: >
          Gross amount minus discounts and refunds, in USD, excluding tax.
          Canceled orders are excluded. Finance reports on this column.

The check compares the columns a model exposes against the columns its schema file describes, and it flags any column with no description on a model that has dependents. That is a text comparison, so it runs in CI, where the project graph already exists. A revenue analysis agent could not answer a single question in one Atlan-documented deployment at Workday, because nothing mapped the term revenue to authoritative columns.

How Each dbt Anti-Pattern Shows Up and What Catches It

Every symptom in the middle column shows up weeks after the commit that caused it. Review misses all seven because of that delay. A reviewer connects a diff to a problem they can see, and at review time the problem has not happened yet. A check reads the diff for the pattern, so it does not need the symptom to appear first.

Where to Catch These dbt Anti-Patterns

Run these checks in three places: the editor, CI, and the warehouse. Each place catches something the other two miss.

The editor catches a pattern while the person who wrote it is still looking at it, which is when a fix costs seconds. A slow check cannot run on every keystroke, so an editor check works from the text of the open file alone.

CI catches whatever arrived from somewhere else, including code an agent wrote while nobody watched. CI is the only place where a check is never optional, so it owns the rules you block a merge on. A gate that fires on style opinions gets bypassed, and a bypassed gate protects nothing.

The warehouse catches what neither the editor nor CI can see. Key cardinality, real row counts and test history are properties of the data, so you get them from a query against the warehouse and never from parsing a file. Cost history belongs in the same layer, and tracking query cost over time is where a pruning regression shows up as money.

One rule decides where each check goes. If the SQL text alone answers the question, put the check in the editor. If the check needs the project graph, put it in CI, because CI builds the graph anyway. If the check needs the data, run it against the warehouse on a schedule, and accept that some problems surface after they merge.

Which of the seven each layer can catch.

Dbt's Fusion Engine Changes Where Checks Belong In 2026

Until 2026, catching these anti-patterns meant a separate linter with its own SQL parser. Then dbt's Fusion engine shipped. Fusion is a Rust rewrite of dbt that parses and resolves SQL itself. dbt Labs publishes it as up to 30x faster than dbt Core v1, with real-time error detection in the editor. An engine that understands the query catches a class of structural problem with no second tool reading the same files.

The SQL comprehension, the linting and the column-level lineage ship in the Fusion binary only. dbt Core v2.0, the Apache-licensed build that reached its first alpha on 1 June 2026, does not carry them.

Fusion covers part of the list. Reference errors, type mismatches and column resolution are properties of the SQL, so they improve once the engine comprehends the query. Anything that needs the data stays out of reach. The fan-out join needs cardinality and the test that cannot fail needs test history, so no engine catches either one from a parse.

Our own rule layer sits between the engine and the warehouse query. It catches the pattern-level anti-patterns the engine has no opinion about, such as SELECT * in a shared model or a pruning-defeating filter. Each check runs in about 0.48 ms, so it fits between two keystrokes rather than waiting for a save. The Validators reference lists what each rule checks, and dbt PR Review covers running the same rules at merge time.

A team that puts everything into one custom linter rewrites SQL parsing badly, then maintains it through every dialect change. A team that expects the engine to catch everything ships fan-out joins, because no engine reads cardinality out of a query it has not run.

Which Anti-Patterns a Machine Should Own

Six of the seven anti-patterns have an exact definition, which is what makes them automatable. The exception is the incremental model, where the presence of a lookback is exact and its width is a judgment. Altimate publishes 19 anti-pattern rules at 100% accuracy across 1,077 benchmark queries with zero false positives. The false-positive rate matters more than the rule count, because a check that fires wrongly once gets the whole set turned off.

The general form of this argument is why deterministic tooling beats LLM-only data agents. For sizing a change before review, column-level lineage use cases covers the other half.

Start with two greps. Search your project for severity: warn, then for incremental configs with no unique_key. Neither needs any tooling you do not already have.