Developing and Optimizing dbt Models Study Guide — dbt Analytics Engineering Certification

Developing dbt Models

This domain covers the full lifecycle of building, structuring, and configuring dbt models using SQL and Jinja. It is the heaviest domain on the exam at 46% of total weight, meaning roughly half of all questions test your ability to choose correct materializations, write valid Jinja, configure models properly, and apply project structure conventions. Expect scenario-based questions that require you to distinguish between similar options (e.g., merge vs. delete+insert, ref() vs. direct table reference) and to predict the behavior of configurations.

Materializations

A materialization defines how dbt persists a model's results in the data warehouse. dbt ships with five built-in materializations.

Default materialization is view unless overridden in dbt_project.yml or a model-level config block.

How to Set Materializations

-- Model-level config block (highest precedence)
{{ config(materialized='table') }}
SELECT ...

# dbt_project.yml (applies to entire folder)
models:
  my_project:
    marts:
      +materialized: table

Precedence order (highest → lowest): model config block → individual model YAML → dbt_project.yml folder config.

Materialized Views

A materialized view is a warehouse-native object (not the same as a dbt table). Supported on Snowflake, BigQuery, Postgres, Redshift, and Databricks — but behavior differs. dbt does not manage the refresh schedule; the warehouse does. Use when you want warehouse-managed incremental refresh without custom dbt incremental logic.

Common Traps

• Assuming ephemeral models appear in the warehouse — they do not; they compile to CTEs inside the consuming model.
• Confusing materialized view (warehouse-native, auto-refreshed) with table (dbt drops and recreates every run).
• Assuming table is incremental — it is not; every run is a full rebuild.
• Forgetting that view is the default; a model with no config is a view.
• Believing ephemeral models can be referenced outside dbt (e.g., by a BI tool) — they cannot.

Worked Scenario

Requirement: A model runs on 500 M rows and is queried heavily by a BI tool, but the underlying source data is fully replaced daily. Choose the materialization.

Reasoning: incremental is not needed because the source is fully replaced, eliminating the "only process new rows" benefit. view would force the BI tool to re-execute 500 M rows on every query. Answer: table — full rebuild nightly, fast BI reads.

Incremental Models

Incremental models avoid full rebuilds by processing only new or changed rows on each run after the initial load.

Core Mechanics

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}

SELECT *
FROM {{ source('raw', 'orders') }}
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

• is_incremental(): Jinja macro that returns true only when the model already exists in the warehouse AND --full-refresh is not passed. On first run or dbt run --full-refresh, it returns false, causing a full table build.
• {{ this }}: Refers to the current model's relation (schema + table name) in the warehouse, used inside is_incremental() blocks to query the existing table.

Incremental Strategies

Default strategy depends on the adapter: most adapters default to append when no unique_key is set; when unique_key is provided, most adapters default to merge.

unique_key Behavior

• When unique_key is a single column: pass a string — unique_key='order_id'
• When unique_key is a composite key: pass a list — unique_key=['order_id', 'line_item_id']
• Without unique_key, dbt cannot deduplicate and will use append strategy.

--full-refresh Flag

Running dbt run --full-refresh forces is_incremental() to return false for all incremental models, causing them to drop and recreate from scratch. Use when source data has changed retroactively.

Common Traps

• Forgetting is_incremental() returns false on the first run — the full dataset is loaded initially.
• Using append strategy with unique_key — unique_key is ignored by append.
• Expecting delete+insert to work without a unique_key — it will error.
• Confusing {{ this }} (current model's table) with {{ source() }} (source table).
• Assuming --full-refresh only affects the targeted model — it affects all incremental models in the run unless --select narrows scope.

Decision tree for choosing incremental strategy:

Flowchart: choosing the correct incremental strategy based on data characteristics