dbt Analytics Engineering Certification · 46% of the exam

Developing dbt models: free practice questions

5 sample questions from our 115-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real dbt Labs exam.

1. A developer wants to test a new macro in isolation before using it in a production model. They run `dbt run-operation my_macro --args '{column_name: order_id}'`. Which of the following statements accurately describes what `dbt run-operation` does?

  • A. It compiles and executes the macro against the warehouse, returning a result set that dbt displays in the terminal.
  • B. It executes the macro as a standalone operation against the warehouse but does NOT create or modify any model relations unless the macro explicitly runs DDL/DML statements.✓ Correct
  • C. It compiles the macro to SQL and saves the output to the `target/compiled/` directory without executing anything.
  • D. It runs the macro and automatically creates a temporary table in the warehouse with the macro's output for inspection.
Explanation

`dbt run-operation` invokes a macro directly against the warehouse connection. It executes whatever SQL the macro contains (e.g., `run_query`, logging, DDL), but it does not create dbt model relations on its own. If the macro doesn't execute any SQL statements, nothing happens in the warehouse. It does not return a result set to the terminal like a SELECT would (unless the macro explicitly logs output). It does not save output to `target/compiled/`. It does not auto-create temporary tables.

2. An analytics engineer needs to track historical changes to a `pricing` table using a dbt snapshot. The source table has an `updated_at` timestamp column that is reliably updated whenever a row changes. Which snapshot strategy and configuration is MOST appropriate?

  • A. strategy: 'check', check_cols: 'all' — compares every column to detect changes
  • B. strategy: 'timestamp', updated_at: 'updated_at' — uses the reliable timestamp to detect new and changed rows efficiently✓ Correct
  • C. strategy: 'timestamp', updated_at: 'created_at' — uses the creation timestamp to capture inserts only
  • D. strategy: 'check', check_cols: ['price'] — compares only the price column for efficiency
Explanation

When a reliable `updated_at` column exists, the `timestamp` strategy is the recommended and most efficient approach. dbt compares the `updated_at` value in the snapshot table against the source to detect changed rows. The `check` strategy (options A and D) is for sources without a reliable updated timestamp — it compares column values directly, which is less efficient and can miss changes if the timestamp is unreliable. Using `created_at` (option C) would only detect new inserts, missing updates to existing rows.

3. A developer writes the following inside a dbt model: ```sql {% set regions = var('active_regions', ['us', 'eu', 'apac']) %} SELECT * FROM ( {% for region in regions %} SELECT *, '{{ region }}' AS region_code FROM {{ source('app', region ~ '_events') }} {% if not loop.last %} UNION ALL {% endif %} {% endfor %} ) ``` The developer runs `dbt run --vars '{active_regions: ["us", "eu"]}'`. Which of the following BEST describes what happens?

  • A. dbt raises a compilation error because `var()` cannot be used inside a `{% set %}` block
  • B. The model compiles to a UNION ALL of `us_events` and `eu_events` only, because the runtime variable overrides the default✓ Correct
  • C. The model compiles to a UNION ALL of `us_events`, `eu_events`, and `apac_events` because defaults are always used
  • D. The model compiles to a UNION ALL of all three tables but filters results to `us` and `eu` at runtime
Explanation

When `--vars '{active_regions: ["us", "eu"]}'` is passed at runtime, dbt overrides the default value `['us', 'eu', 'apac']` defined in `var('active_regions', [...])`. The Jinja loop then iterates over only `['us', 'eu']`, producing a UNION ALL of `us_events` and `eu_events`. Using `var()` inside a `{% set %}` block is perfectly valid Jinja in dbt. The default value is only used when the variable is NOT provided at runtime. No runtime SQL filtering occurs — the table list is resolved at compile time, so `apac_events` is never referenced in the compiled query.

4. A developer writes the following dbt model configuration: ```sql {{ config( materialized='incremental', unique_key='event_id', incremental_strategy='delete+insert' ) }} SELECT * FROM {{ ref('stg_events') }} {% if is_incremental() %} WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM {{ this }}) {% endif %} ``` The team notices that some late-arriving records with older `event_timestamp` values are being dropped entirely. Which change would BEST fix this while keeping the incremental strategy?

  • A. Switch the incremental strategy to `merge` and keep the same `WHERE` clause.
  • B. Remove the `is_incremental()` block entirely so all records are always loaded.
  • C. Expand the `WHERE` clause to look back a fixed window (e.g., the past 3 days) instead of only selecting records newer than the current maximum.✓ Correct
  • D. Add `on_schema_change='append_new_columns'` to the config block.
Explanation

The root cause is the filter `WHERE event_timestamp > MAX(event_timestamp)`, which excludes any record with a timestamp equal to or older than the current max, causing late-arriving events to be silently dropped. Using a lookback window (e.g., last 3 days) ensures late arrivals within that window are captured and, combined with `delete+insert` on the affected partition or `unique_key`, will correctly upsert them. Switching to `merge` alone doesn't fix the WHERE clause that filters late data out before it even reaches the merge. Removing `is_incremental()` entirely defeats the purpose of incremental loading. `on_schema_change` controls column evolution, not row filtering.

5. An analytics engineer is writing a staging model `stg_customers.sql`. They want to reference the raw source table `customers` defined in a `sources.yml` file under the source name `crm`. Which of the following is the CORRECT way to reference it inside the model SQL?

  • A. FROM raw.crm.customers
  • B. FROM {{ ref('crm', 'customers') }}
  • C. FROM {{ source('crm', 'customers') }}✓ Correct
  • D. FROM {{ source('customers', 'crm') }}
Explanation

`{{ source('crm', 'customers') }}` is the correct syntax — the first argument is the source name defined in `sources.yml` and the second is the table name. This keeps the source in dbt's DAG and enables source freshness checks. Hard-coding `raw.crm.customers` bypasses dbt's lineage graph entirely. `{{ ref() }}` is used to reference other dbt *models*, not source tables. `{{ source('customers', 'crm') }}` reverses the arguments, so dbt would look for a source named `customers` with a table named `crm`, which would fail.

110 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free