examprotocol.
Free check

Developing and optimizing dbt models: sample questions

6 free practice questions on developing and optimizing dbt models, one of the seven topics in the official v1.11 outline for the dbt Analytics Engineering Certification Exam — an estimated 45% of the exam, or about 29 of its 65 questions. Answers, reasoning and documentation links are all on this page.

Last updated . We revise these pages whenever dbt Labs revises the exam.

SHARE OF THE EXAM~45%our estimate
QUESTIONS HERE64 with runnable SQL
FORMATS SHOWN4of 6 on the exam

What this topic covers

This is the biggest topic on the exam by a wide margin, and the outline gives it fourteen subtopics — more than the next two domains combined. It covers the core materializations, incremental strategy selection, snapshots defined in YAML, Python models, packages, the grants config, project-level configuration in dbt_project.yml, building clean DAGs, and the command surface: build, run, test, docs, show, snapshot and seed.

Three of those subtopics arrived with the current exam edition and are where most existing prep is silent: dry runs with --empty, sample mode with --sample, and the microbatch materialization.

SUBTOPICS, FROM THE OFFICIAL OUTLINE · 14

  1. 01Identifying and verifying any raw object dependencies
  2. 02Understanding core dbt materializations
  3. 03Conceptualizing modularity and how to incorporate DRY principles
  4. 04Using commands such as build, run, test, docs, show, snapshot, and seed
  5. 05Creating a logical flow of models and building clean DAGs
  6. 06Defining configurations in dbt_project.yml
  7. 07Using dbt Packages
  8. 08Creating Python Models
  9. 09Providing access to users to models with the "grants" config
  10. 10Creating snapshots in YAML
  11. 11Selecting the optimal incremental strategy based on a dataset's characteristics
  12. 12Validating model logic and schema definitions in dry-runs using the --empty flag
  13. 13Running models in sample mode using the --sample flag
  14. 14Understanding advanced dbt materializations such as microbatch

Where the marks go

The questions below are picked to sit on the lines people actually get wrong. The first turns on what a materialization stores versus what it recomputes on every read. The second is about --empty, which limits refs and sources to zero rows so CI can catch a bad cast without scanning four terabytes.

The hotspot question is the one worth doing slowly. A model builds without error, but no lineage edge appears and it breaks in production — because dbt assembles the DAG from ref() and source() calls at parse time and from nothing else, so a hardcoded table name compiles perfectly and points at the wrong schema the moment the target changes.

6 sample questions, with answers

Nothing is hidden. Each question shows the correct answer, why it is right, why every other option is wrong, and the documentation page that settles it. Where the answer is a claim about SQL behaviour, there is a query you can run in the browser against a small sample schema.

Q1Multiple choiceEasyRunnable proof

Two models select from the same source table. rpt_orders_view is materialized as view, rpt_orders_table as table. You run dbt run, then new rows land in the source. You query both models in the warehouse. Which statement regarding the results is true?

  • Both return the new rows, because dbt refreshes each object when it is queried
  • rpt_orders_table returns the new rows, rpt_orders_view does not until the next dbt run
  • rpt_orders_view returns the new rows, rpt_orders_table does not until the next dbt runCORRECT
  • Neither returns the new rows until the next dbt run

WHY

On each run dbt rebuilds a view model with a create view as statement: the object stores no data, only the select. Every query against rpt_orders_view re-executes that select against the source, so rows that landed after the run are included. A table model is rebuilt with a create table as statement, which writes the result set to storage at run time. rpt_orders_table therefore holds a snapshot frozen at the last dbt run and only picks up the new rows when dbt rebuilds it. That is the core trade-off: the view costs no storage and is always current but pays the transformation cost on every query, while the table costs storage and staleness between runs but is fast to query.

WHY THE OTHERS ARE WRONG

Both return the new rows, because dbt refreshes each object when it is queried
dbt does no work at query time — it only runs when you invoke it. The view looks current because the warehouse re-executes its select, not because dbt refreshed anything, and the table is not refreshed at all.
rpt_orders_table returns the new rows, rpt_orders_view does not until the next dbt run
This inverts the two materializations. The table is the one holding a stored copy from the last run, so it is the stale object; the view carries no data of its own.
Neither returns the new rows until the next dbt run
A view stores no rows, so there is nothing stale to refresh: the warehouse resolves it against the current source data on every query.
docs.getdbt.com/docs/build/materializations
Q2Fill in the blankEasy

You move your .sql model files out of the default folder into transform/models/. Which key in dbt_project.yml tells dbt where to look for model files? Give the key name.

In the exam you type the answer. What counts as correct is below.

ACCEPTED ANSWERSmodel-paths · model-paths: · "model-paths" · 'model-paths'

WHY

model-paths is the dbt_project.yml key that lists the directories dbt searches for model files (and the sources and unit tests defined alongside them). It defaults to ["models"], so a project that keeps models anywhere else must set it explicitly — here model-paths: ["transform/models"]. The value is a list of paths relative to the project root, and dbt only parses .sql and .py model files found under those directories: anything outside them is invisible to the graph, so refs to those models fail to resolve. Each resource type has its own key (seed-paths, macro-paths, snapshot-paths, test-paths, analysis-paths), and target-path and clean-targets control build output rather than source lookup. Note that model-paths is the current name; source-paths was the pre-1.0 spelling and no longer works in dbt Core 1.11.

docs.getdbt.com/reference/project-configs/model-pathsdocs.getdbt.com/reference/dbt_project.yml
Q3Fill in the blankEasy

Consider this selectors.yml. You want to print every resource this stored selector matches, without executing any of them. Complete the command: dbt ls ______

selectors:
- name: nightly
definition:
method: tag
value: nightly

In the exam you type the answer. What counts as correct is below.

ACCEPTED ANSWERS--selector nightly · --selector=nightly · dbt ls --selector nightly · dbt ls --selector=nightly · dbt list --selector nightly · dbt list --selector=nightly

WHY

Selectors stored in selectors.yml are invoked by name with --selector. --select takes inline selection syntax instead, so --select nightly would search for a node named nightly rather than resolving the stored selector. dbt ls (alias: dbt list) applies that selection to the project graph and prints the matching resources rather than executing them, which is how you preview what a selector covers before a job uses it.

docs.getdbt.com/reference/commands/listdocs.getdbt.com/reference/node-selection/yaml-selectors
Q4Multiple choiceMediumRunnable proof

Consider this snapshot definition. The crm accounts source is overwritten in place and has no reliable updated_at column. Overnight, account 77's plan_tier changes from 'basic' to 'pro' and its billing_email changes too. What does tonight's dbt snapshot run produce for account 77?

snapshots:
- name: accounts_snapshot
relation: source('crm', 'accounts')
config:
unique_key: id
strategy: check
check_cols:
- plan_tier
- status
  • A new record holding the new plan_tier and the new billing_email, with the previous record closed outCORRECT
  • A run error, because billing_email is not listed in check_cols
  • An in-place update of the current record with both new values
  • No new record, because check_cols must list every column that can change
  • A new record holding the new plan_tier and the previous billing_email, with the previous record closed out

WHY

check_cols is a change-detection list, not a projection list. The check strategy compares the current values of the listed columns (plan_tier, status) against the values in the current snapshot record; plan_tier moved from 'basic' to 'pro', so a change is detected. Once detected, dbt closes the current record by stamping dbt_valid_to and inserts a new record that is a full copy of the row returned by the snapshot query — every column, including billing_email, at its current source value. This is also the situation check exists for: the strategy is documented for tables that lack a reliable updated_at column, whereas timestamp is the choice when the source does maintain one you can trust. Neither is universally better — timestamp needs a dependable timestamp column to work at all, and check needs a well-chosen column list.

WHY THE OTHERS ARE WRONG

A run error, because billing_email is not listed in check_cols
There is no validation that ties non-check_cols columns to the strategy. Columns outside check_cols are simply not compared; they are still selected, stored, and updated in each new record. The run completes normally.
An in-place update of the current record with both new values
Snapshots do not overwrite data columns of existing records. The existing record is touched only in its dbt_ meta columns — dbt_valid_to is set to close the version out — and the new values arrive as a separate, newly inserted record. Updating in place would destroy the history the snapshot exists to keep.
No new record, because check_cols must list every column that can change
check_cols never has to be exhaustive. plan_tier is listed and it changed, so a change was detected and recorded. The real trade-off is the opposite one: a change confined to an unlisted column (billing_email alone) would go unrecorded — but an unlisted column cannot suppress a change detected on a listed one.
A new record holding the new plan_tier and the previous billing_email, with the previous record closed out
check_cols governs whether a change is detected, not which columns are written. dbt does not merge the new row column-by-column into the old one; the inserted record is the whole source row as it stands now, so billing_email lands at its new value.
docs.getdbt.com/docs/build/snapshotsdocs.getdbt.com/reference/snapshot-configs
Q5Discrete option (DOMC)HardRunnable proof

You are configuring an incremental model that runs on Snowflake. You will be shown statements about unique_key one at a time. For each statement, answer YES if it is true, NO if it is false.

In the exam these appear one at a time and you answer YES or NO to each, without seeing the rest. All of them, with their answers, are below.

  • Setting unique_key lets a run update the row already in the table for that key instead of adding a second one.YES
  • unique_key names exactly one column, so a multi-column grain needs a hashed surrogate key column in the model.NO
  • With no unique_key, a run inserts every row the model's SQL returns, including rows that duplicate rows already in the table.YES
  • On the model's first run, dbt applies unique_key to drop duplicate rows from the model's own output.NO
  • With incremental_strategy='merge' and no unique_key set, the run still succeeds and behaves like append.YES

WHY

unique_key is the grain declaration that turns an incremental run from insert-only into match-then-write. (a) It tells dbt which rows in the target correspond to rows in the new batch, so when new information arrives for a key that already landed, that row is updated in place rather than appended a second time — on merge it becomes the ON condition, on delete+insert it is the predicate for the delete. (c) Omit it and there is nothing to match on: dbt appends everything the model's SQL returned, duplicates included. This is why an incremental filter that re-reads its boundary (>= on a timestamp, or a lookback window) silently accumulates copies until a key is configured. (e) The failure mode is silent, not loud: dbt does not error on a merge model with no unique_key — with no key to match on, the merge degrades to inserting the new rows, i.e. the append strategy.

WHY THE OTHERS ARE WRONG

unique_key names exactly one column, so a multi-column grain needs a hashed surrogate key column in the model.
unique_key takes either a single column name or a list of column names that define the grain together, for example unique_key=['order_id', 'line_item_id']. dbt builds the match condition from every column in the list, so a composite grain needs no hashed surrogate column — that is a modelling convenience, not a requirement of the config.
On the model's first run, dbt applies unique_key to drop duplicate rows from the model's own output.
The unique_key matching logic only exists in the incremental branch of the materialization, which runs when the relation already exists. On the first run (and on any --full-refresh) dbt builds the table from scratch with a plain create-table-as of the model's SELECT, so duplicate rows inside that SELECT land untouched. De-duplicating the model's own output is the SQL's job — a qualify row_number() over (...) = 1 or equivalent — not the key's.
docs.getdbt.com/docs/build/incremental-modelsdocs.getdbt.com/reference/resource-configs/unique_keydocs.getdbt.com/docs/build/incremental-strategy
Q6HotspotMediumRunnable proof

Your project already has an int_order_items model. fct_orders builds without error, but its DAG shows edges only to the two staging models. Click the line that declares the name shadowing the model.

In the exam you click the region. The answer is highlighted below.

-- models/marts/fct_orders.sql
with orders as (
select * from {{ ref('stg_shopify__orders') }}
),
int_order_items as (
select
order_id,
sum(item_amount) as item_amount
from {{ ref('stg_shopify__order_items') }}
group by 1
)
select
orders.order_id,
orders.ordered_at,
int_order_items.item_amount
from orders
left join int_order_items on orders.order_id = int_order_items.order_id
Correct region: line 9.

WHY

Line 9 opens a CTE named int_order_items — the same name as a model in the project. Inside a query, an unqualified relation name resolves to a CTE in scope before any physical table, so the join on line 25 reads the local CTE and never touches the model. dbt derives the graph only from ref() calls, so fct_orders gets edges to stg_shopify__orders and stg_shopify__order_items and none to int_order_items: the file reads like it depends on that model, and the lineage says otherwise. Renaming the CTE (order_items_agg) or replacing it with {{ ref('int_order_items') }} restores the edge — ref() compiles to a fully-qualified relation, which a CTE cannot shadow.

WHY THE OTHERS ARE WRONG

Lines 3–7
The orders CTE refs stg_shopify__orders, so that edge is real and appears in the DAG. Its name matches no model in the project, so nothing is shadowed here.
Lines 11–15
This body duplicates logic the int_order_items model already owns, but its ref points at a staging model and correctly produces that edge. The duplication is a consequence of the shadowing name, not the name itself.
Lines 19–22
Reading int_order_items.item_amount only projects a column off whatever the identifier already resolves to. Select-list expressions never create or remove graph edges.
Lines 24–25
This is where the shadowing bites — the join silently binds to the CTE — but the name is declared on line 9; the join only consumes it. Leave the CTE in place and the identifier still resolves locally.
docs.getdbt.com/reference/dbt-jinja-functions/refdocs.getdbt.com/docs/build/sql-models

Know whether developing and optimizing dbt models is actually costing you marks.

The free readiness check is weighted like the real exam across all seven topics, so it tells you where you stand on this topic relative to the rest — which is the only version of that question worth answering before you book.

Take the free readiness check20 questions · ~15 minutes · no card

The other six topics

Keep reading

All sample questionsThe hub, with every domain and how the weights were derived.The exam format65 questions, 120 minutes, 65% to pass, and how DOMC works.

SOURCES

Every source above was read in full and last checked on .