dbt tutorial — from zero to production.

You write SELECT statements as models (.sql files). dbt:

• Compiles them (Jinja templating) and figures out the dependency DAG from ref() calls.
• Materializes them in your warehouse — as views, tables, incremental tables, or ephemeral CTEs.
• Runs tests you declare in YAML (not_null, unique, relationships, custom).
• Generates docs and a lineage graph automatically.

The mental shift: SQL is your source code. Git is your source of truth. The warehouse is the compiler target.

pip install dbt-duckdb
dbt init my_project      # creates the scaffold
cd my_project
dbt debug                # verifies connection
dbt run                  # builds all models
dbt test                 # runs all tests
dbt docs generate && dbt docs serve

Swap dbt-duckdb for dbt-snowflake, dbt-bigquery, or dbt-databricks when you're ready — the model code doesn't change.

models/
  staging/       -- 1:1 with sources, light typing/renaming
    stg_orders.sql
    stg_customers.sql
  intermediate/  -- reusable business logic
    int_orders_enriched.sql
  marts/         -- final consumable models
    core/
      dim_customers.sql
      fct_orders.sql
    finance/
      mrr.sql
snapshots/       -- SCD Type 2
seeds/           -- small CSV lookups
macros/          -- reusable Jinja
tests/           -- singular tests
sources.yml      -- declare raw tables
schema.yml       -- tests + docs per model

Never skip staging. Staging is where you rename columns, cast types, and stabilize the interface. Every downstream model reads from staging, never from raw.

Declare raw tables as sources in sources.yml. Reference them with {{ source('jaffle_shop', 'raw_orders') }}. Reference other models with {{ ref('stg_orders') }}. That's how dbt builds the DAG.

Rule: only staging models read from sources. Everything else reads from ref(). Enforce it with dbt-project-evaluator.

• view (default). No storage, always fresh. Fine for staging and small marts.
• table. Full rebuild each run. Fast reads, slow builds on big data.
• incremental. Only process new/changed rows. Configure unique_key and incremental_strategy (merge, append, delete+insert).
• ephemeral. Inlined as a CTE — no warehouse object. Use sparingly; kills reusability.

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

SELECT * FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Declare in schema.yml:

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests: [not_null, unique]
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id

For business invariants, write singular tests in tests/ — any SELECT that returns rows fails the test. Add dbt-expectations for Great-Expectations-style checks.

{% snapshot snap_customers %}
{{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at'
) }}
SELECT * FROM {{ source('crm', 'customers') }}
{% endsnapshot %}

Run dbt snapshot on a schedule. dbt adds dbt_valid_from, dbt_valid_to, dbt_scd_id. Query it like any table.

Macros are Jinja functions — extract repeated SQL. Install packages via packages.yml: dbt-utils (surrogate keys, pivots, date spines), dbt-expectations (advanced tests), dbt-project-evaluator (enforces best practices). Run dbt deps.

Minimum viable CI: on every PR, spin up a temp schema, dbt build --select state:modified+ --defer --state ./prod-manifest, tear down. Slim CI catches broken changes without rebuilding the world.

On merge to main: dbt build against production, publish the manifest as an artifact for next PR's slim CI.

• Selecting from sources outside of staging.
• Using table materialization on a 2B-row model that could be incremental.
• Incremental model without unique_key — silent duplicates.
• No tests on primary keys. Ever.
• Circular refs — dbt catches these; know how to read the error.

The DataForge dbt track walks the exact structure above with hands-on models — sources, staging, marts, snapshots, incremental, macros, CI. Once you've shipped a dbt repo you can defend, pair it with SQL for data engineering and the interview questions.