dbt Architect common mistakes
dbt Architect · Understand the traps and how to avoid them.
Candidates model every source table as a single-layer set of views that reference raw tables directly, then add staging logic inside marts, so transformation logic is duplicated across models and lineage becomes unreadable.
They treat dbt as a SQL runner rather than a layered system, and they optimize for getting a dashboard working quickly instead of for maintainability. The layering convention (staging → intermediate → marts) is often known in name but not applied in practice under time pressure.
What to do instead
Enforce one staging model per source table with only renaming, casting, and light deduplication; push joins and business logic to intermediate models; keep marts as consumption-shaped models. Review the DAG for any mart that selects from a raw source or repeats a staging expression, and refactor before moving on.
Candidates set materializations uniformly (everything a view, or everything a table) instead of choosing per model based on query frequency, rebuild cost, and downstream fan-out.
Materialization choice is taught as a default rather than a trade-off. Candidates also confuse incremental models with tables and assume incremental is always cheaper, without considering merge cost, late-arriving data, and unique key correctness.
What to do instead
Justify each materialization explicitly: views for cheap, rarely queried staging; tables for frequently joined intermediate models; incremental only where the source has a reliable high-water mark and the unique key is truly unique. State the cost and freshness consequence of each choice when explaining the design.
Candidates build an incremental model without a correct unique key or late-arriving-data strategy, then claim it is equivalent to a full refresh. They also use is_incremental() filters that silently drop updates to existing rows.
The incremental pattern is memorized as a code template. The subtle part - that an append-only filter only captures new rows, not changed ones - is easy to miss, and the exam often probes behavior after a source row is updated rather than inserted.
What to do instead
For each incremental model, state the grain, the unique key, and how updates and deletes are handled (merge/delete+insert strategy, lookback window, or a snapshot). Test with a scenario where an existing row changes and confirm the model reflects it, or document that it intentionally does not.
Candidates hardcode environment-specific values (database names, schemas, warehouse sizes, credentials paths) inside models or profiles committed to the repo, rather than using targets, variables, and environment-aware configuration.
Local development works fine with hardcoded values, so the problem is invisible until promotion. Candidates underestimate how much of environment design is about isolating dev, CI, and prod without forking the project.
What to do instead
Drive all environment differences through target properties, dbt vars with defaults, and generate_schema_name macros. Keep one project for all environments, and verify that a CI run and a prod run differ only by target configuration, not by model code.
Candidates design a CI pipeline that runs the full project against production data on every pull request, or that runs only a syntax check and never builds the changed models.
There is a pull between speed and safety, and candidates pick one extreme. They also forget that CI needs an isolated schema and a bounded data subset, so they either touch prod or skip meaningful validation.
What to do instead
Use state-based selection (dbt build --select state:modified+) against a production manifest, run in an ephemeral or per-PR schema, and gate merges on tests for changed models and their immediate downstream. Explain how the manifest is fetched and why the selection keeps CI fast without losing coverage.
Candidates describe deployment as 'run dbt on a schedule' without specifying job ordering, source freshness gating, failure handling, or how partial failures are recovered.
Orchestration is treated as a cron entry rather than a dependency graph with contracts. The interaction between source freshness checks, upstream ingestion completion, and dbt job start time is rarely reasoned through.
What to do instead
Describe the job graph explicitly: ingestion completion signal, source freshness check as a gate, dbt build with fail-fast semantics, alerting on test failures, and a documented rerun procedure that is safe to repeat. Note which steps must be idempotent.
Candidates write tests only as not_null and unique on primary keys and treat that as data quality coverage, ignoring accepted values, referential integrity, and freshness for sources.
Generic tests are the easiest to recall, so they become the whole answer. Candidates also conflate testing with documentation and assume a passing build means the data is correct.
What to do instead
Layer tests by purpose: structural (unique/not_null on keys), domain (accepted_values, relationships to dimension keys), and freshness (source freshness thresholds tied to SLA). Add singular tests for business rules that generic tests cannot express, and state what failure of each test should trigger.
Candidates treat documentation as a README written after the fact, leaving models, columns, and exposures undescribed, and never surfacing lineage or ownership to consumers.
Documentation feels like overhead and is deprioritized under exam time pressure. Candidates also do not connect docs to governance outcomes such as impact analysis and deprecation.
What to do instead
Put descriptions in schema YAML next to the model, declare sources with freshness and owner metadata, declare exposures for dashboards and downstream jobs, and use contracts or model versions for breaking changes. Explain how lineage plus exposures answers 'what breaks if I change this column'.
Candidates tune performance by adding cluster keys or increasing warehouse size everywhere, without identifying the actual bottleneck (scan volume, join spill, or concurrency).
Warehouse sizing is the most visible knob, so it becomes the default answer. Candidates rarely distinguish between a slow model and a slow overall run caused by too many concurrent small queries.
What to do instead
Diagnose first: is the cost in bytes scanned (pruning/partitioning), in shuffling (join order and keys), or in concurrency (queueing)? Then apply the matching fix - partition and cluster on filter/join columns, pre-aggregate in an intermediate model, or split jobs and manage warehouse concurrency - and state the expected effect.
Candidates reduce cost by switching models to views or by shortening schedules, without accounting for downstream query cost and freshness requirements, and they never quantify the trade-off.
Cost optimization is framed as 'make things cheaper' rather than 'meet the freshness SLA at the lowest total cost'. Candidates forget that a view pushes compute to every consumer query, which can cost more than a nightly table.
What to do instead
Tie each optimization to an SLA and a measured baseline: bytes scanned per run, run duration, and consumer query cost. Compare options (table vs view vs incremental, full vs partial refresh) on total cost at the required freshness, and state the number you expect to move.