Core Concepts of dbt

Models — SQL Files That Become Tables

A model is a .sql file containing a single SELECT statement. When you run dbt run, dbt wraps each model in DDL (CREATE TABLE/VIEW) and executes it in the warehouse.

-- models/staging/stg_orders.sql
SELECT
    id AS order_id,
    user_id AS customer_id,
    order_date,
    status,
    amount
FROM {{ source('raw', 'orders') }}
WHERE status != 'deleted'

You write the SELECT. dbt writes the CREATE TABLE AS, handles schema names, and runs models in dependency order.

ref() & source() — The Dependency Graph

ref() references another dbt model. source() references raw tables loaded by your EL tool.

-- models/marts/fct_orders.sql
-- ref() builds the DAG: this model depends on stg_orders and stg_customers
SELECT
    o.order_id,
    o.customer_id,
    c.customer_name,
    o.amount,
    o.order_date
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('stg_customers') }} c ON o.customer_id = c.customer_id

# models/staging/_sources.yml
sources:
  - name: raw
    database: analytics_raw
    schema: public
    tables:
      - name: orders
        loaded_at_field: _etl_loaded_at
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}

Tests — Data Quality Guards

dbt has two types of tests:

Generic Tests (YAML-defined)

# models/staging/_schema.yml
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'returned']
      - name: customer_id
        tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id

Singular Tests (custom SQL)

-- tests/assert_positive_amounts.sql
-- Returns rows that FAIL the test (should return 0 rows)
SELECT order_id, amount
FROM {{ ref('fct_orders') }}
WHERE amount < 0

Materializations — How Models Are Built

-- models/marts/fct_events.sql
{{
  config(
    materialized='incremental',
    unique_key='event_id'
  )
}}

SELECT * FROM {{ ref('stg_events') }}

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

Macros & Jinja — Reusable SQL

Jinja is a templating language mixed into dbt SQL. Macros are reusable Jinja functions.

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name) %}
    ({{ column_name }} / 100.0)::numeric(10,2)
{% endmacro %}

-- Usage in a model:
SELECT
    order_id,
    {{ cents_to_dollars('amount_cents') }} AS amount_dollars
FROM {{ ref('stg_orders') }}

Documentation — Auto-Generated Docs

dbt generates a searchable website from your YAML descriptions with a visual lineage graph.

# models/marts/_schema.yml
models:
  - name: fct_orders
    description: "One row per order with customer details. Grain: order_id."
    columns:
      - name: order_id
        description: "Primary key from source system"
      - name: amount_dollars
        description: "Order total in USD (converted from cents)"

dbt docs generate   # Creates the docs site
dbt docs serve       # Opens it in your browser