dbt Cheat Sheet

-- Top of any .sql model file
{{
  config(
    materialized  = 'table',
    schema        = 'marts',
    alias         = 'orders_final',
    tags          = ['daily', 'finance'],
    meta          = {'owner': 'data_team'},
    post_hook     = "grant select on {{ this }} to role reporter"
  )
}}

SELECT * FROM ...

# Apply config to all models in a folder
models:
  my_project:
    staging:
      +materialized: view
      +schema: staging
    marts:
      +materialized: table
      +tags: ['mart']
    intermediate:
      +materialized: ephemeral

models:
  my_project:
    +persist_docs:
      relation: true   # model description → table comment
      columns:  true   # column descriptions → column comments

-- Reference another model
SELECT *
FROM {{ ref('stg_orders') }}

-- Reference a model in another project (cross-project)
FROM {{ ref('other_project', 'stg_orders') }}

-- ref() resolves to the full relation name:
-- analytics.staging.stg_orders (database.schema.table)

-- _sources.yml definition first:
sources:
  - name: raw_shopify
    database: raw
    schema: shopify
    tables:
      - name: orders
        loaded_at_field: _fivetran_synced
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}

-- Then in a model:
SELECT *
FROM {{ source('raw_shopify', 'orders') }}

# Test source freshness
dbt source freshness

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'returned']
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id

Custom SQL files in tests/ that express business-specific assertions generic tests can't cover. dbt runs the query — any row returned = failure.

-- tests/assert_no_negative_revenue.sql
SELECT order_id, revenue
FROM {{ ref('fct_orders') }}
WHERE revenue < 0

-- Variables
{% set my_date = '2024-01-01' %}
{% set cols = ['a', 'b', 'c'] %}

-- Conditionals
{% if target.name == 'prod' %}
  WHERE is_test = false
{% endif %}

-- Loop
{% for col in cols %}
  sum({{ col }}) as total_{{ col }}
  {% if not loop.last %},{% endif %}
{% endfor %}

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

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

-- Macro with run_query (meta-programming)
{% macro get_column_values(table, column) %}
  {% set query %}
    SELECT DISTINCT {{ column }} FROM {{ table }}
  {% endset %}
  {% set results = run_query(query) %}
  {{ return(results.columns[0].values()) }}
{% endmacro %}

target.name

Active profile target (dev, prod…)

target.schema

Target schema

this

Current model relation (in hooks)

env_var('VAR')

Read environment variable

var('name', default)

Project variable from dbt_project.yml

log('msg', info=True)

Print to console

run_started_at

Timestamp of current run

execute

True during run, False during parse

{{
  config(
    materialized = 'incremental',
    unique_key   = 'event_id',       # for merge/delete+insert
    on_schema_change = 'sync_all_columns'
  )
}}

SELECT
  event_id,
  user_id,
  event_at,
  event_type
FROM {{ source('raw', 'events') }}

{% if is_incremental() %}
  -- Only process new rows on incremental runs
  WHERE event_at > (
    SELECT max(event_at) FROM {{ this }}
  )
{% endif %}

{{ config(
  materialized      = 'incremental',
  incremental_strategy = 'merge',
  unique_key        = ['order_id', 'item_id'],
  merge_update_columns = ['status', 'updated_at']
) }}

version: 2

models:
  - name: fct_orders
    description: "One row per order."
    meta:
      owner: "data-team@company.com"
    config:
      tags: ['finance', 'daily']
    columns:
      - name: order_id
        description: "Surrogate key for orders."
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed','shipped','returned']

-- docs/orders.md
{% docs order_id %}
The unique identifier for an order.
Generated by the upstream ERP system.
{% enddocs %}

{% docs order_status %}
Lifecycle status of the order.
Possible values: **placed**, **shipped**, **returned**.
{% enddocs %}

# Reference in schema.yml
columns:
  - name: order_id
    description: "{{ doc('order_id') }}"
  - name: status
    description: "{{ doc('order_status') }}"

exposures:
  - name: orders_dashboard
    type: dashboard       # dashboard | notebook | analysis | ml | application
    maturity: high
    url: "https://metabase.company.com/dashboard/42"
    description: "Finance orders dashboard"
    owner:
      name: "Alice"
      email: "alice@company.com"
    depends_on:
      - ref('fct_orders')
      - ref('dim_customers')