dbt — Real World & Lab Guide

What is dbt?

dbt (data build tool) is an open-source transformation framework that lets data teams write modular SQL SELECT statements and compile them into a full transformation DAG (directed acyclic graph) with lineage, tests, and documentation — all version-controlled in Git.

dbt sits in the T of ELT. You load raw data into your warehouse first (Fivetran, Airbyte, Snowpipe…), then dbt transforms it into clean, tested, documented tables.

dbt Core vs dbt Cloud

Project structure

my_dbt_project/
├── dbt_project.yml        # project config: name, vars, model paths, materializations
├── profiles.yml           # connection config (usually ~/.dbt/profiles.yml)
├── models/
│   ├── staging/           # 1:1 with raw sources, light cleaning
│   │   ├── stg_orders.sql
│   │   └── _staging.yml   # source definitions + column tests
│   ├── intermediate/      # business logic, not exposed to BI
│   │   └── int_order_items_pivoted.sql
│   └── marts/             # final, consumer-ready models
│       ├── finance/
│       │   └── fct_revenue.sql
│       └── core/
│           └── dim_customers.sql
├── tests/                 # singular (custom) tests — one .sql per test
├── macros/                # reusable Jinja functions
├── seeds/                 # static CSV files loaded as tables
├── snapshots/             # SCD Type 2 history capture
├── analyses/              # ad-hoc SQL (compiled but not run by default)
└── docs/                  # markdown for doc blocks

Models

A model is a single .sql file containing one SELECT statement. dbt wraps it in CREATE TABLE AS or CREATE VIEW AS depending on the materialization.

Materializations

Incremental model example

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

SELECT
    order_id,
    customer_id,
    order_date,
    status,
    total_amount
FROM {{ source('raw', 'orders') }}

{% if is_incremental() %}
  -- only process rows newer than the latest row in the existing table
  WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}

Sources & refs

{{ source() }} — raw data

References a table in your warehouse that dbt did not create. Define sources in a _sources.yml file for freshness checks and lineage.

-- models/staging/_staging.yml
version: 2
sources:
  - name: raw
    database: RAW_DB
    schema: salesforce
    tables:
      - name: orders
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}
        loaded_at_field: _loaded_at
        columns:
          - name: order_id
            tests: [unique, not_null]

{{ ref() }} — dbt-managed models

References another model by name. dbt resolves the actual schema/table at compile time and builds the DAG automatically.

-- models/marts/fct_revenue.sql
SELECT
    o.order_id,
    c.customer_name,
    o.total_amount,
    o.order_date
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('dim_customers') }} c USING (customer_id)

Tests

Generic tests (schema tests)

Defined in YAML, applied to columns. Built-in: unique, not_null, accepted_values, relationships.

-- models/marts/_marts.yml
version: 2
models:
  - name: fct_revenue
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'delivered', 'cancelled']
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id

Singular tests (custom SQL)

A .sql file in tests/ that returns rows when the test fails. Zero rows = pass.

-- tests/assert_revenue_positive.sql
SELECT order_id, total_amount
FROM {{ ref('fct_revenue') }}
WHERE total_amount <= 0

dbt-utils & dbt-expectations

Popular packages that add extra test types: not_empty_string, at_least_one, expect_column_values_to_be_between, and more.

-- packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]

Macros & Jinja

dbt uses Jinja2 templating inside SQL files. Macros are reusable functions defined in macros/.

Built-in Jinja

-- Use variables
{{ var('start_date', '2024-01-01') }}

-- Conditional logic
{% if target.name == 'prod' %}
  WHERE created_at >= '2020-01-01'
{% else %}
  WHERE created_at >= DATEADD('year', -1, CURRENT_DATE)
{% endif %}

-- Loop
{% set payment_methods = ['credit_card', 'bank_transfer', 'gift_card'] %}
SELECT
  order_id,
  {% for method in payment_methods %}
  SUM(CASE WHEN payment_method = '{{ method }}' THEN amount END) AS {{ method }}_amount
  {% if not loop.last %},{% endif %}
  {% endfor %}
FROM {{ ref('stg_payments') }}
GROUP BY 1

Custom macro example

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, decimal_places=2) %}
  ROUND({{ column_name }} / 100.0, {{ decimal_places }})
{% endmacro %}

-- Usage in a model:
SELECT {{ cents_to_dollars('price_cents') }} AS price_dollars FROM ...

Snapshots (SCD Type 2)

Snapshots capture how a row looked at different points in time — creating a history table with dbt_valid_from / dbt_valid_to columns.

-- snapshots/snp_customers.sql
{% snapshot snp_customers %}

{{
  config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at'
  )
}}

SELECT * FROM {{ source('raw', 'customers') }}

{% endsnapshot %}

Seeds

Small static CSV files committed to the repo and loaded into the warehouse as tables via dbt seed. Good for lookup tables: country codes, cost centres, exchange rates.

-- seeds/country_codes.csv
country_code,country_name
US,United States
GB,United Kingdom
AU,Australia

-- Reference a seed like any other model
SELECT * FROM {{ ref('country_codes') }}

Snowflake integration

profiles.yml for Snowflake

my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: myorg-myaccount
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('DBT_PASSWORD') }}"
      role: TRANSFORMER
      warehouse: DBT_WH
      database: DEV_DB
      schema: DBT_DEV
      threads: 4
    prod:
      type: snowflake
      account: myorg-myaccount
      authenticator: externalbrowser   # SSO
      role: TRANSFORMER
      warehouse: DBT_WH_LARGE
      database: PROD_DB
      schema: DBT_PROD
      threads: 16

Snowflake-specific config options

{{ config(
    materialized='table',
    transient=true,          -- skips Fail-Safe (saves storage cost)
    cluster_by=['order_date', 'region'],
    query_tag='dbt_{{ model.name }}',
    copy_grants=true
) }}

Snowflake + dbt patterns

• Use transient tables for intermediate/ephemeral models — no Fail-Safe charges.
• Cluster keys on large fact tables match your most common filter columns.
• Warehouse sizing: small warehouse for dev/test; dedicated larger warehouse for prod full refresh runs.
• Dynamic Tables (Snowflake feature): dbt can target Snowflake Dynamic Tables for near-real-time refresh — check dynamic_table materialization in dbt-snowflake adapter.
• Query tags: Set query_tag config to trace dbt runs in Snowflake's query history.

Snowflake access roles pattern

Key dbt commands

Node selection syntax

# Run a single model
dbt run --select fct_revenue

# Run a model and all its upstream dependencies
dbt run --select +fct_revenue

# Run a model and all its downstream dependents
dbt run --select fct_revenue+

# Run all models in the marts folder
dbt run --select marts.*

# Run only models changed since last commit (CI optimization)
dbt run --select state:modified+

# Run all failed models from the previous run
dbt run --select result:error --state ./target

Interview Q&A