dbt Analytics Engineering Certification · 16% practice allocation

dbt Analytics Engineering Certification: Developing and Optimizing dbt Models Practice Questions

5 free sample questions from the Developing and Optimizing dbt Models domain, with answers and explanations. The full dbt Analytics Engineering Certification bank has 327 questions across 7 domains. Questions are scenario-based and written from the official dbt Labs exam guide.

1.

A developer is building an incremental model fct_orders.sql and adds the following block:

{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

The developer runs dbt run --full-refresh. What does is_incremental() evaluate to during this run?

  • A. True, but the WHERE clause is ignored because dbt detects the --full-refresh flag
  • B. It raises a compilation error because --full-refresh is incompatible with is_incremental()
  • C. False, because --full-refresh forces a full rebuild and the macro returns false✓ Correct
  • D. True, because the model is configured as incremental
Explanation

is_incremental() returns False when the --full-refresh flag is used, when the model does not yet exist in the warehouse, or when dbt is running in a context that requires a full build. In all these cases, the WHERE filter block is skipped and the full dataset is loaded. The macro does not raise an error — it simply evaluates to false. There is no mechanism where the clause is 'ignored' separately from is_incremental() evaluating to false; the false evaluation IS the mechanism.

2.

An analytics engineer is building an incremental model fct_transactions.sql on Snowflake. They configure it with unique_key='transaction_id' and incremental_strategy='merge'. On the next run, a source row with an existing transaction_id has a corrected amount value. What will happen?

  • A. The corrected row will be ignored because incremental models only process rows with new unique_key values.
  • B. The run will fail because the merge strategy does not support updates to existing rows.
  • C. The existing row in the target table will be updated with the new amount value.✓ Correct
  • D. The row will be duplicated in the target table because incremental models always append new rows.
Explanation

The merge incremental strategy uses the unique_key to perform an upsert: if a row with the matching unique_key already exists in the target table, it is updated with the incoming data. This is precisely the purpose of the merge strategy. — The row will not be duplicated; the merge checks the unique_key to determine whether to insert or update. — The merge strategy explicitly supports updates; that is its primary advantage over append. — The corrected row is not ignored; the merge logic matches on unique_key and updates the existing record.

3.

A developer defines a dbt model with no {{ config() }} block and no entry in dbt_project.yml for that model path. Which materialization will dbt use when the model is executed?

  • A. table
  • B. view✓ Correct
  • C. incremental
  • D. ephemeral
Explanation

dbt's global default materialization is view. If no config block is present in the model file and no materialization is set in dbt_project.yml, dbt falls back to this default. table would require explicit configuration. ephemeral and incremental must always be explicitly declared.

4.

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 {{ source('crm', 'customers') }}✓ Correct
  • B. FROM raw.crm.customers
  • C. FROM {{ ref('crm', 'customers') }}
  • 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.

5.

A developer is configuring a dbt project that targets Snowflake. They want the model fct_sales.sql to be rebuilt from scratch on every dbt run, but they also want it to exist as a permanent warehouse table (not a view). Which materialized config value achieves this?

  • A. view
  • B. incremental
  • C. table✓ Correct
  • D. ephemeral
Explanation

The table materialization drops and recreates the full table on every dbt run, which satisfies both requirements: it is a permanent warehouse object and it is fully rebuilt each time. — view stores only the SQL definition; it is not a table and does not store data. — ephemeral creates no warehouse object at all; it is inlined as a CTE. — incremental does not rebuild from scratch on each run; it only processes new or changed rows, and a full refresh requires --full-refresh.

Test yourself across every dbt Analytics Engineering Certification domain

The free dbt Analytics Engineering Certification practice test mixes about 20 questions weighted like the exam. The full 327-question bank adds instant grading, flashcards and a timed mock exam.

Take the free dbt Analytics Engineering Certification practice testPractice the full bank

Study this domain

Developing and Optimizing dbt Models study guide (dbt Analytics Engineering Certification) →

Other dbt Analytics Engineering Certification domains

← All dbt Analytics Engineering Certification study resources