dbt Deep Dive

What dbt actually is

dbt (data build tool) is — at its core — a SQL compiler with conventions. You write SELECT statements; dbt wraps them in CREATE TABLE / CREATE VIEW / INCREMENTAL INSERT logic and runs them against your warehouse in the right order.

The pieces that make it more than that:

• Dependency graph — uses {{ ref('model_name') }} to determine build order. The DAG is the entire reason dbt works.
• Materialization strategies — view, table, incremental, ephemeral, snapshot. Same SQL, different runtime behavior.
• Tests — first-class SQL-based assertions on data. Run alongside builds.
• Jinja templating — Python-like macros over SQL.
• Documentation as code — yaml descriptions + auto-generated docs site with lineage graph.
• Environments + targets — same code, different warehouses/schemas (dev / staging / prod).

Project layout — know it

my_dbt_project/
├── dbt_project.yml          # project config — name, profile, paths, configs
├── packages.yml             # external dbt packages
├── models/
│   ├── staging/             # 1:1 with source tables, light cleanup
│   │   ├── _sources.yml     # declares raw schemas
│   │   ├── stg_users.sql
│   │   └── stg_orders.sql
│   ├── intermediate/        # complex joins, reusable building blocks
│   │   └── int_user_lifetime_value.sql
│   └── marts/               # business-facing tables (fct_, dim_)
│       ├── core/
│       │   ├── fct_orders.sql
│       │   └── dim_users.sql
│       └── finance/
│           └── fct_daily_revenue.sql
├── tests/                   # singular (custom) tests
├── macros/                  # reusable Jinja macros
├── snapshots/               # SCD type 2 tracking
├── seeds/                   # CSVs that get loaded as tables
└── analyses/                # one-off SQL files (NOT materialized)

The staging → intermediate → marts pattern

• Staging (stg_*): one model per source table. Rename columns, cast types, light filtering. No business logic.
• Intermediate (int_*): complex joins and computations that get reused. Optional layer.
• Marts (fct_*, dim_*): the analytics-facing layer. Star schema tables. This is what BI tools query.

This convention comes from dbt Labs' style guide. Most companies follow it. If asked "how do you organize a dbt project?" — name these three layers.

Models & materializations

A model is just a SELECT in a .sql file. dbt wraps it based on the materialization config:

Config — three places to set it

-- 1. In the model file (highest precedence for that model):
{{ config(materialized='incremental', unique_key='order_id', on_schema_change='append_new_columns') }}

SELECT ...

-- 2. In a yaml file alongside the model:
-- models/marts/_marts.yml
models:
  - name: fct_orders
    config:
      materialized: incremental

-- 3. In dbt_project.yml (lowest precedence — folder-level defaults):
models:
  my_project:
    staging:
      +materialized: view
    marts:
      +materialized: table

Most teams set folder-level defaults in dbt_project.yml (staging = view, marts = table) and override at the model level when needed.

Sources — declare your raw data

Sources declare which raw tables in your warehouse dbt depends on. Two reasons they matter:

1. Lineage — dbt's DAG starts at source tables.
2. Freshness checks — dbt can run dbt source freshness to alert when source data is stale.

version: 2

sources:
  - name: raw_app
    database: raw
    schema: app_prod
    loaded_at_field: _ingested_at
    freshness:
      warn_after: {count: 2, period: hour}
      error_after: {count: 6, period: hour}

    tables:
      - name: users
        description: "User accounts from the app database"
        columns:
          - name: id
            tests:
              - unique
              - not_null
      - name: orders
        loaded_at_field: created_at  # override per-table

SELECT
  id           AS user_id,
  email,
  created_at,
  updated_at,
  is_active
FROM {{ source('raw_app', 'users') }}

ref() and source() — the magic

These are the two Jinja functions that make dbt's DAG work:

• {{ source('schema', 'table') }} — references a raw source table.
• {{ ref('model_name') }} — references another dbt model. dbt builds it first, then yours.

Never hard-code a table name. Always use ref(). This:

• Builds the dependency graph.
• Handles environment-specific schemas automatically (dev vs prod).
• Lets dbt resolve cross-database / cross-project references.

WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),
order_items AS (
    SELECT * FROM {{ ref('stg_order_items') }}
),
users AS (
    SELECT * FROM {{ ref('stg_users') }}
)
SELECT ...

Tests — first-class data quality

dbt has two kinds of tests:

1. Generic tests (declarative, in yaml)

Built in: unique, not_null, accepted_values, relationships.

version: 2

models:
  - name: dim_users
    description: "One row per user"
    columns:
      - name: user_id
        description: "Primary key"
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['active', 'inactive', 'churned']
      - name: plan_id
        tests:
          - relationships:
              to: ref('dim_plans')
              field: plan_id

2. Singular tests (custom SQL in tests/)

A singular test is just a SQL file in tests/ that returns the failing rows. Empty result = test passes.

SELECT *
FROM {{ ref('fct_orders') }}
WHERE revenue < 0

Powerful packages

dbt-utils and dbt-expectations add dozens of tests: expression_is_true, recency, at_least_one, distribution tests, etc. Mention these in interviews to show ecosystem awareness.

Snapshots — SCD Type 2 for free

A snapshot tracks how a row's columns change over time. Each version gets dbt_valid_from and dbt_valid_to columns. This is the canonical way to implement Slowly Changing Dimension Type 2 in dbt.

{% snapshot users_snapshot %}

{{
  config(
    target_schema='snapshots',
    unique_key='user_id',
    strategy='check',
    check_cols=['plan_id', 'status', 'email']
  )
}}

SELECT * FROM {{ source('raw_app', 'users') }}

{% endsnapshot %}

Strategies:

• check: dbt compares the columns you list; new row written when any change.
• timestamp: dbt watches an updated_at column. More efficient when available.

Run with dbt snapshot on a schedule (usually nightly).

Seeds — small CSVs as tables

Drop a CSV into seeds/, run dbt seed, get a table. Good for: country code mappings, manually-curated business rules, fixture data for tests. Not for: anything large or that changes often.

Macros & Jinja

Macros are reusable Jinja functions over SQL. Useful for DRYing up repeated logic.

{% macro cents_to_dollars(column_name, precision=2) %}
  ROUND(({{ column_name }} / 100.0), {{ precision }})
{% endmacro %}

SELECT
  order_id,
  {{ cents_to_dollars('amount_cents') }} AS amount_usd
FROM {{ ref('stg_orders') }}

Common Jinja patterns

• {% if target.name == 'dev' %} ... {% endif %} — environment-conditional SQL.
• {% for col in get_columns_in_relation(ref('x')) %} — iterate over columns at compile time.
• {{ var('start_date') }} — parameterize via project variables.
• {{ env_var('SOMETHING') }} — read from environment.

Incremental models — the most-asked-about

When a fact table has billions of rows and you can't afford to rebuild it nightly, you go incremental.

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
) }}

SELECT
  event_id,
  user_id,
  event_type,
  occurred_at,
  payload
FROM {{ ref('stg_events') }}

{% if is_incremental() %}
  -- only process new events on subsequent runs
  WHERE occurred_at > (SELECT MAX(occurred_at) FROM {{ this }})
{% endif %}

Incremental strategies

Packages — don't reinvent

dbt has a Hub. Common packages:

• dbt-utils — generic tests, macros (generate_surrogate_key, pivot, star, group_by).
• dbt-expectations — Great Expectations-style tests ported to dbt.
• codegen — generates yaml from your warehouse schema.
• elementary — data observability layer over dbt artifacts.
• dbt_audit_helper — compare two model outputs (for refactors / migrations).

Exposures & the semantic layer

Exposures declare the downstream consumers of your models — dashboards, ML models, applications. They appear in the lineage graph.

version: 2
exposures:
  - name: gpu_utilization_dashboard
    type: dashboard
    maturity: high
    owner:
      name: Data Team
      email: data@example.com
    depends_on:
      - ref('fct_gpu_hours')
      - ref('dim_providers')

Semantic Layer (dbt's metrics framework, also called MetricFlow): centralizes metric definitions. Instead of every dashboard re-defining "MRR," you define it once in YAML, and BI tools query it via a unified API.

CLI essentials

# Build everything (run + test)
dbt build

# Just compile templates — no warehouse calls
dbt compile

# Run a specific model and its descendants
dbt run -s fct_orders+

# Run a model and its ancestors
dbt run -s +fct_orders

# Run a folder, models tagged "daily", or by source
dbt run -s marts.finance
dbt run -s tag:daily
dbt run -s source:raw_app+

# Run only models changed in this PR (state-based)
dbt run -s state:modified+ --state ./prod-manifest

# Run tests
dbt test
dbt test -s fct_orders

# Full refresh an incremental model
dbt run --full-refresh -s fct_events

# Build docs site
dbt docs generate
dbt docs serve

The + graph syntax is essential. +model+ = ancestors and descendants. Know it cold.

CI / CD with dbt

Modern dbt teams treat models like code:

• PR opens → CI runs dbt build -s state:modified+ against a staging schema (Slim CI).
• Tests must pass before merge.
• Slim CI uses the production manifest as state — only rebuilds what changed.
• Production scheduler (dbt Cloud, Airflow, Dagster) runs dbt build on a cron + after upstream loads complete.
• Audit packages (dbt_audit_helper) used during PR review to diff before/after of model output.

Performance & cost

• Materialize wisely — view for staging, table for marts, incremental for big facts.
• Cluster / partition — Snowflake: cluster_by in config; BigQuery: partition_by + cluster_by; Databricks: partition_by + Z-order.
• Limit ephemeral chains — many ephemerals in a chain produce monstrous compiled SQL.
• Tag and schedule — not every model needs to run every hour. Tag by SLA, schedule accordingly.
• Look at dbt_artifacts / Elementary — track run times, see which models are getting slow.

Interview talking points

✓ Check yourself

Answer from memory before opening each one.

1. A model selects from analytics.stg_orders by its literal name instead of ref('stg_orders'). Name two things that break or get lost.

   Show answer

   dbt cannot see the dependency, so the model may build before its parent, and the lineage graph loses the edge. It also hard-codes the schema, so a dev run reads production tables instead of the developer's own. See ref() and source().

2. An incremental model filters on occurred_at > (SELECT MAX(occurred_at) FROM {{ this }}). An event that happened four hours ago arrives now. What happens to it, and what do you change?

   Show answer

   It is skipped on every later run, because the table's maximum timestamp has already passed it. Use a lookback such as the maximum minus 24 hours, and a merge strategy on a unique key so reprocessed rows update instead of duplicating. See incremental models.

3. Pick a materialization for each: staging models over small tables, a fact table of four billion immutable events, and a 50,000-row dimension refreshed daily.

   Show answer

   View for staging, since it is cheap and always fresh. Incremental, with the append strategy, for the immutable events. Table for the dimension, because a full rebuild is fast and keeps the logic simple. Incremental is only worth its complexity when a rebuild is too slow or costly. See models and materializations.

4. What do dbt run -s +fct_orders, dbt run -s fct_orders+ and dbt build -s state:modified+ each select, and which one runs on a pull request?

   Show answer

   The first selects the model and its ancestors, the second the model and its descendants, the third everything changed relative to a saved manifest plus its descendants. The third is what pull request CI runs, so only affected models rebuild. See CLI essentials and CI with dbt.

5. A snapshot of the users table uses the timestamp strategy on updated_at, but the application does not bump updated_at when plan_id changes. What goes wrong, and what is the alternative?

   Show answer

   dbt never detects the change, so no new version row is written and the history misses plan changes. Use the check strategy and list plan_id in check_cols, accepting a slower comparison. See snapshots.

6. A distribution test on average order value fails most days, and it pages the on-call engineer. What do you change, and what stays at error severity?

   Show answer

   Set it to warn, or use a threshold with warn_if and error_if, since it is a signal rather than a gate. Keep error for contracts: primary key uniqueness, not-null on keys and source freshness. See tests.

Takeaway

• dbt is a SQL compiler with a dependency graph: ref() and source() give build order, environment-aware names and lineage, so never hard-code a table name.
• Organise models as staging, intermediate and marts; staging models are 1:1 with sources and hold no business logic.
• Default to view for staging and table for marts, and go incremental only when a full rebuild is too slow or costly.
• Incremental models need a unique key, a lookback for late data and a plan for full refreshes and schema changes.
• Tests are part of the build: use dbt build, set severity by consequence, and give every test an owner.
• Snapshots implement slowly changing dimensions of Type 2, and Slim CI with state:modified+ rebuilds only what a pull request changed.
• Have an opinion on the semantic layer: it stops finance, product and ops from defining one metric three ways.

Models are only as good as the shapes they build, so the next chapter covers dimensional modeling. → Data Modeling

Further reading

• Materializations — dbt Labs docs, accessed 2026. What each materialization does and its trade-offs.
• Configure incremental models — dbt Labs docs, accessed 2026. Strategies, unique keys, and schema change handling.
• Add data tests to your DAG — dbt Labs docs, accessed 2026. Generic and singular tests, severity and thresholds.
• Add snapshots to your DAG — dbt Labs docs, accessed 2026. Timestamp and check strategies, and the columns dbt adds.
• Syntax overview — dbt Labs docs, accessed 2026. The graph operators and state selectors.
• Source freshness — dbt Labs docs, accessed 2026. Declaring and running freshness checks on sources.