dbt Analytics Engineering common mistakes
dbt Analytics Engineering · Understand the traps and how to avoid them.
Writing ref() calls to staging models inside a source definition or trying to ref() a source. Candidates declare raw tables as models and then ref() them, or wrap sources in a model just to be able to ref() them, producing duplicate lineage and unnecessary build steps.
The candidate has memorized that ref() is the standard way to reference upstream data and applies it uniformly, without internalizing that sources are declared in YAML and referenced with source(), while ref() is reserved for models and seeds. The two-function split feels like redundancy rather than a deliberate boundary between raw and transformed layers.
What to do instead
Practice the rule as a decision: if the relation exists outside dbt (loaded by Fivetran, Airbyte, a warehouse job), declare it under sources: in a YAML file and reference it with source('source_name','table_name'). Only use ref() for things dbt builds or seeds. Reserve a staging model for the first transformation layer, and let that staging model be the only place source() appears, so downstream models depend on staging via ref().
Adding tests only on the primary key of staging models and treating that as sufficient coverage, leaving foreign-key relationships, accepted values, and freshness unchecked on the models that actually feed dashboards.
Candidates equate data quality with uniqueness and not_null on IDs because those are the tests they practiced first. They do not translate business rules into test selection, so they miss that a relationship test between fct_orders and dim_customers catches orphaned rows that a not_null test never will.
What to do instead
For each model, walk the columns and assign a test by intent: unique/not_null on grain keys, relationships on every foreign key, accepted_values on low-cardinality status columns, and source freshness on raw tables with a loaded_at timestamp. Write a short comment above each test explaining the failure it prevents, which forces you to justify the test rather than copy a template.
Assuming dbt test failures stop the downstream build. Candidates configure tests, see them fail in CI, and still find marts refreshed in production, then misdiagnose it as a test configuration problem.
The mental model is that tests are gates. In reality, dbt test is a separate command from dbt run, and a failing test does not automatically block models built in a later run step; the orchestration layer must sequence and gate them. Candidates who have only run dbt build locally (which does interleave tests and models) assume the same behavior in a two-command production job.
What to do instead
Decide deliberately between dbt build (tests and models interleaved, downstream models skipped when an upstream test fails) and separate dbt run plus dbt test steps where the orchestrator stops on non-zero exit. Know that dbt build is the simplest way to get gating, and configure your scheduler to halt the job on failure rather than ignoring the exit code.
Putting a singular test in the tests/ directory that returns many rows and expecting dbt to report each offending row, or writing a singular test with a trailing semicolon or a query that returns a count instead of failing rows.
Candidates conflate singular tests with assertions in a unit-testing framework, where a boolean or a count is a natural return value. dbt's contract is narrower: the query must return the rows that violate the rule, and zero rows means pass. Extra statements or a count column silently change the meaning.
What to do instead
Write singular tests as a single SELECT that returns only violating rows, with no trailing semicolon and no aggregate wrapper. Verify the semantics by temporarily inverting the WHERE clause and confirming the test fails. When you need per-row reporting or reusable logic, move to a generic test with parameters instead of a one-off query.
Treating the generated docs site as the deliverable: writing model descriptions but never adding column descriptions, exposures, or meta, then being unable to answer a lineage question about which dashboard depends on a given column.
Documentation feels like a checkbox, and the visible artifact (a docs site that builds) creates false confidence. Candidates also underestimate that lineage in dbt is derived from ref(), source(), and exposure definitions, so undocumented dependencies are invisible regardless of how good the prose is.
What to do instead
Document at the column level for any model consumed outside the project, and add exposures for dashboards and reports with depends_on entries pointing at the models they read. Use dbt docs generate plus the lineage graph to verify that a change to a column surfaces every downstream consumer. Treat descriptions as part of the model's definition, reviewed in the same pull request as the SQL.
Hardcoding schema and database names in model SQL or in dbt_project.yml instead of using target schema, custom schema macros, or environment-specific targets, so a CI run writes into production schemas.
Local development against a single warehouse makes hardcoding invisible. Candidates test with dev credentials, never simulate a second target, and so never see the collision until a CI job or a colleague's run overwrites shared tables.
What to do instead
Keep environment differences in profiles and targets, not in SQL. Use generate_schema_name overrides or the schema config to namespace by target, and confirm by running dbt compile with two different targets and diffing the resolved relation names. Add a CI check that fails if a production target name appears in project files.
Misconfiguring incremental models: omitting the is_incremental() guard, using a filter that misses late-arriving rows, or choosing merge without a unique_key, then wondering why counts drift or duplicates appear after the second run.
The incremental logic looks correct on a full refresh because the guard is irrelevant then. Candidates test once, see correct output, and never run the model twice against changed source data, which is the only way the incremental branch executes.
What to do instead
Always wrap the incremental filter in {% if is_incremental() %}, and choose the strategy deliberately: merge with a tested unique_key, delete+insert for partitions, or append only for immutable event data. Test by running the model, inserting or updating source rows including a backdated row, running again, and reconciling counts against a full-refresh build.
Assuming a passing dbt build means the data is correct. Candidates stop at green tests without checking row counts, grain, or reconciliation against the source, so a model that silently drops rows via an inner join still passes uniqueness and not_null tests.
Green output is treated as proof of correctness. Tests validate declared constraints, not undeclared ones, and an inner join that eliminates unmatched rows makes the surviving data cleaner, not more complete. Candidates rarely write a test for row-count preservation because it is not in the standard test set.
What to do instead
For every join, decide explicitly whether unmatched rows should be dropped (inner) or kept (left) and add a test that pins the expectation, such as a relationships test on the foreign key or a singular test comparing counts before and after the join. Reconcile fact tables to source totals for a sample period and record the expected variance.
Using a source freshness check without a loaded_at_field or with a timestamp in the wrong timezone, so freshness always passes or always fails, and the team disables the check.
Freshness configuration is easy to set and hard to validate: a wrong column or a naive timestamp compared against a UTC current time produces plausible-looking output. Candidates do not test the failure path, so they never see the check misbehave.
What to do instead
Declare loaded_at_field explicitly per source table, confirm the column's timezone and type, and set warn_after and error_after as thresholds with a stated tolerance. Validate by manually inserting an old timestamp and confirming the check errors, then by loading fresh data and confirming it passes.
Building orchestration as a flat sequence of dbt run then dbt test for the whole project, ignoring selector syntax, tags, and model-level dependencies, so a single failing staging model blocks or silently bypasses unrelated marts.
Candidates learn the two commands as a pair and never explore selection methods. In a scheduler, that becomes one monolithic job where partial failures are hard to attribute and reruns rebuild everything, which discourages frequent runs and masks which model actually broke.
What to do instead
Use selectors to scope jobs: tag models by layer or domain, run staging and marts as separate tasks, and use --select with state or graph operators (for example, models downstream of a changed model) to limit blast radius. Configure the orchestrator to fail the task on non-zero exit and to surface the failing model name from dbt's output.