dbt for Data Analysts

Models, materialisation strategies, sources, refs, schema tests, incremental models, documentation, and lineage DAG — the analytics engineering toolkit that turns SQL into production data infrastructure.

your_dbt_project/
├── dbt_project.yml          # project config: name, profile, model paths
├── profiles.yml             # warehouse connection settings (usually ~/.dbt/)
├── models/
│   ├── staging/             # 1-to-1 with source tables: rename, cast, light cleaning
│   │   ├── _staging.yml     # source definitions + staging model docs/tests
│   │   ├── stg_orders.sql
│   │   ├── stg_customers.sql
│   │   └── stg_products.sql
│   ├── intermediate/        # business logic: joins, calculations, aggregations
│   │   ├── int_order_items_enriched.sql
│   │   └── int_customer_lifetime_value.sql
│   └── marts/               # final consumption layer for BI tools / analysts
│       ├── core/
│       │   ├── fct_orders.sql
│       │   └── dim_customers.sql
│       └── marketing/
│           └── mkt_campaign_performance.sql
├── tests/                   # custom (singular) tests
│   └── assert_no_negative_revenue.sql
├── macros/                  # reusable Jinja SQL snippets
│   └── cents_to_dollars.sql
└── seeds/                   # static CSV files loaded as tables
    └── country_codes.csv

-- models/staging/stg_orders.sql
-- Staging layer: rename columns, cast types, no business logic yet

SELECT
    id                                      AS order_id,
    customer_id,
    created_at                              AS order_created_at,
    CAST(total_amount_cents AS NUMERIC)
        / 100                               AS order_total,
    LOWER(status)                           AS order_status,
    COALESCE(discount_code, 'none')         AS discount_code,
    DATE(created_at)                        AS order_date
FROM {{ source('raw', 'orders') }}          -- references a declared source
WHERE id IS NOT NULL


-- models/intermediate/int_order_items_enriched.sql
-- Intermediate: join staging models, add calculated fields

SELECT
    o.order_id,
    o.order_date,
    o.order_status,
    c.customer_name,
    c.region,
    p.product_name,
    p.category,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price             AS line_revenue,
    p.cost_price,
    (oi.unit_price - p.cost_price)
        * oi.quantity                       AS line_margin
FROM {{ ref('stg_orders') }}        o
JOIN {{ ref('stg_order_items') }}   oi  USING (order_id)
JOIN {{ ref('stg_customers') }}     c   ON o.customer_id = c.customer_id
JOIN {{ ref('stg_products') }}      p   ON oi.product_id = p.product_id


-- models/marts/core/fct_orders.sql
-- Mart: final aggregated fact table consumed by BI tools

SELECT
    order_id,
    order_date,
    customer_name,
    region,
    category,
    SUM(line_revenue)                       AS total_revenue,
    SUM(line_margin)                        AS total_margin,
    COUNT(DISTINCT product_name)            AS distinct_products,
    SUM(quantity)                           AS total_units
FROM {{ ref('int_order_items_enriched') }}
GROUP BY 1, 2, 3, 4, 5

-- Set materialisation in dbt_project.yml (applies to all models in a folder):
models:
  your_project:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table

-- Or override per model with a config block at the top of the .sql file:
{{ config(materialized='table') }}

SELECT ...


-- Incremental model pattern:
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'   -- or 'insert_overwrite', 'append'
) }}

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

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

-- Declare sources in a _staging.yml file:
# models/staging/_staging.yml

version: 2

sources:
  - name: raw                              # logical name for this source
    database: your_project                 # BigQuery project / Snowflake DB
    schema: raw_data                       # schema where raw tables live
    loaded_at_field: _etl_loaded_at        # for freshness checks
    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}

    tables:
      - name: orders
        description: "Raw orders from the transactional database"
      - name: customers
      - name: products

models:
  - name: stg_orders
    description: "Cleaned and renamed orders from raw source"
    columns:
      - name: order_id
        description: "Primary key"
        tests:
          - unique
          - not_null
      - name: order_status
        tests:
          - accepted_values:
              values: ['pending', 'processing', 'completed', 'cancelled', 'refunded']
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id

-- Generic tests — declared in schema YAML (see above)
-- Run with: dbt test

-- Four built-in generic tests:
-- unique        : no duplicate values in this column
-- not_null      : no NULL values
-- accepted_values: all values in a defined list
-- relationships : foreign key integrity check


-- Singular test: custom SQL that should return 0 rows if test passes
-- tests/assert_no_negative_revenue.sql

SELECT
    order_id,
    total_revenue
FROM {{ ref('fct_orders') }}
WHERE total_revenue < 0

-- If this query returns any rows, the test FAILS


-- Useful dbt commands:
-- dbt run              → compile and run all models
-- dbt test             → run all tests
-- dbt run --select stg_orders   → run one model
-- dbt test --select fct_orders  → test one model
-- dbt build            → run + test together
-- dbt source freshness → check when sources were last loaded
-- dbt docs generate    → build documentation site
-- dbt docs serve       → open docs in browser with lineage DAG

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name) %}
    ({{ column_name }} / 100.0)
{% endmacro %}

-- Use in any model:
SELECT
    order_id,
    {{ cents_to_dollars('total_amount_cents') }} AS order_total
FROM {{ source('raw', 'orders') }}


-- macros/generate_surrogate_key.sql
-- dbt_utils package provides this — install via packages.yml:
-- packages:
--   - package: dbt-labs/dbt_utils
--     version: [">=1.0.0", "<2.0.0"]

-- Then use:
SELECT
    {{ dbt_utils.generate_surrogate_key(['order_id', 'product_id']) }} AS order_line_key,
    order_id,
    product_id
FROM {{ ref('stg_order_items') }}