Advanced dbt Patterns

Macros

Macros are reusable Jinja functions. Use them to DRY up repeated SQL patterns.

Utility macro: cents to dollars

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

Usage in a model:

select
    order_id,
    amount_cents,
    {{ cents_to_dollars('amount_cents') }} as amount_dollars
from {{ ref('stg_stripe__payments') }}

Generate schema name macro

Override the default schema naming to control where models land:

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}
        {{ default_schema }}
    {%- elif target.name == 'prod' -%}
        {{ custom_schema_name | trim }}
    {%- else -%}
        {{ default_schema }}_{{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

This ensures schema: finance resolves to finance in prod but dev_jsmith_finance in development - preventing dev runs from writing to production schemas.

Pivot macro

-- macros/pivot.sql
{% macro pivot(column, values, alias=True, agg='sum', then_value=1, else_value=0) %}
    {% for value in values %}
        {{ agg }}(
            case when {{ column }} = '{{ value }}'
                then {{ then_value }}
                else {{ else_value }}
            end
        ) {% if alias %} as {{ column }}_{{ value | replace(' ', '_') | lower }} {% endif %}
        {% if not loop.last %},{% endif %}
    {% endfor %}
{% endmacro %}

Essential packages

Add to 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"]
  - package: dbt-labs/codegen
    version: [">=0.12.0", "<0.13.0"]

dbt_utils highlights

dbt_expectations highlights

Statistical and distribution tests:

columns:
  - name: amount_cents
    tests:
      - dbt_expectations.expect_column_values_to_be_between:
          min_value: 0
          max_value: 10000000
      - dbt_expectations.expect_column_mean_to_be_between:
          min_value: 1000
          max_value: 50000

Hooks

Pre-hook: grant access after model build

# dbt_project.yml
models:
  my_project:
    marts:
      +post-hook:
        - "grant select on {{ this }} to role analyst_readonly"

On-run-end: refresh BI cache

on-run-end:
  - "{{ log('Run completed at ' ~ run_started_at, info=True) }}"

Custom materializations

Incremental with delete+insert strategy

For warehouses that do not support MERGE (e.g., Redshift without MERGE support):

{{
    config(
        materialized='incremental',
        incremental_strategy='delete+insert',
        unique_key='event_id',
        on_schema_change='append_new_columns'
    )
}}

Snapshot (SCD Type 2)

-- snapshots/snap_customers.sql
{% snapshot snap_customers %}
{{
    config(
        target_schema='snapshots',
        unique_key='customer_id',
        strategy='timestamp',
        updated_at='updated_at',
        invalidate_hard_deletes=True
    )
}}

select * from {{ source('shopify', 'customers') }}

{% endsnapshot %}

This creates dbt_valid_from, dbt_valid_to, and dbt_updated_at columns automatically. A row with dbt_valid_to IS NULL is the current version.

CI/CD integration

Slim CI with state comparison

# Only run models that changed since last production run
dbt build --select state:modified+ --state ./prod-manifest/

CI pipeline steps

# .github/workflows/dbt-ci.yml (simplified)
steps:
  - name: Install deps
    run: dbt deps

  - name: Check source freshness
    run: dbt source freshness --target ci

  - name: Build changed models
    run: dbt build --select state:modified+ --state ./prod-manifest/ --target ci

  - name: Run all tests on changed models
    run: dbt test --select state:modified+ --state ci

  - name: Generate docs
    run: dbt docs generate

Production run pattern

# Full refresh on schedule (weekly or as needed)
dbt build --full-refresh --target prod

# Daily incremental run
dbt build --target prod

# Source freshness check before build
dbt source freshness --target prod && dbt build --target prod

Model selection syntax

Project configuration best practices

# dbt_project.yml
name: my_analytics
version: '1.0.0'

profile: my_analytics

vars:
  my_analytics:
    start_date: '2020-01-01'

models:
  my_analytics:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table
      +schema: analytics
      finance:
        +tags: ['finance', 'daily']
      marketing:
        +tags: ['marketing', 'daily']

seeds:
  my_analytics:
    +schema: seeds

snapshots:
  my_analytics:
    +target_schema: snapshots

Key rules:

• Staging as views (no warehouse cost, always fresh)
• Intermediate as ephemeral (inlined into downstream queries, no table created)
• Marts as tables (fast for analyst queries)
• Override per-folder when needed (e.g., a heavy intermediate that should be a table)

Self-Serve Analytics Patterns

The self-serve spectrum

Self-serve analytics is not binary. Teams operate on a spectrum:

Most teams should target Level 2-3. Level 4 is for data teams querying their own warehouse. Level 1 alone creates a dashboard factory with infinite ticket queues.

Building analyst-friendly marts

Naming conventions

Never use abbreviations. cust_id saves 4 characters and costs every analyst a lookup. Use customer_id.

Column descriptions in YAML

Every column in every mart model must have a description:

models:
  - name: fct_orders
    description: >
      One row per order. Grain: order_id. Includes payment totals joined from
      the payments intermediate model. Updated incrementally on each dbt run.
    columns:
      - name: order_id
        description: "Unique order identifier from Shopify. Primary key."
      - name: customer_id
        description: "FK to dim_customers. The customer who placed this order."
      - name: order_date
        description: "Date the order was placed (UTC, date only, no time)."
      - name: status
        description: >
          Current order status. Values: pending, confirmed, shipped, delivered,
          cancelled, refunded.
      - name: total_amount_cents
        description: >
          Total order value in US cents (integer). Divide by 100 for dollars.
          Includes tax, excludes shipping.
      - name: is_first_order
        description: >
          True if this is the customer's first order by order_date. Useful for
          new vs returning customer analysis.

Grain documentation pattern

Every mart model's YAML description must state the grain explicitly:

Grain: one row per <entity>

Examples:

• fct_orders: "Grain: one row per order"
• fct_order_items: "Grain: one row per order line item"
• dim_customers: "Grain: one row per customer (current state)"
• fct_daily_active_users: "Grain: one row per user per day"

If you cannot state the grain in one sentence, the model is likely mixing grains and should be split.

Documentation strategies

dbt docs

Generate and host dbt docs for the entire project:

dbt docs generate
dbt docs serve --port 8080

For production, deploy to a static hosting service (S3 + CloudFront, Netlify, etc.) or use dbt Cloud's hosted docs.

What to document beyond column descriptions

1. Model-level description - What this model is, its grain, key assumptions
2. Source freshness expectations - How often data arrives, acceptable lag
3. Known limitations - "Does not include orders from the legacy system before 2021"
4. Business rules - "An order is considered 'completed' when status = 'delivered' AND payment_status = 'captured'"
5. Metric definitions - Link to the semantic layer metric, not a BI dashboard

Data dictionary template

For teams that need a document outside of dbt docs:

## fct_orders

| Column | Type | Description | Example |
|---|---|---|---|
| order_id | varchar | Unique order ID from Shopify | ord_abc123 |
| customer_id | varchar | FK to dim_customers | cust_xyz789 |
| order_date | date | Date order was placed (UTC) | 2024-03-15 |
| status | varchar | Order status enum | delivered |
| total_amount_cents | integer | Total in cents, incl. tax, excl. shipping | 4999 |

**Grain**: One row per order
**Refresh**: Incremental, every 6 hours
**Owner**: Data Engineering (@data-eng)

Data catalog integration

Popular catalog tools

Catalog integration pattern

1. Run dbt docs generate to produce manifest.json and catalog.json
2. Push these artifacts to your catalog tool's API
3. The catalog ingests model descriptions, column descriptions, lineage, and tests
4. Analysts search the catalog, find the right table, and start querying

# Example: push dbt artifacts to DataHub
datahub ingest -c dbt_recipe.yml

# dbt_recipe.yml (DataHub)
source:
  type: dbt
  config:
    manifest_path: target/manifest.json
    catalog_path: target/catalog.json
    target_platform: snowflake

Access patterns for self-serve

Role-based access

-- Create analyst role with read-only access to marts
CREATE ROLE analyst_readonly;
GRANT USAGE ON SCHEMA analytics TO analyst_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO analyst_readonly;

-- Staging and raw schemas are NOT granted to analysts
-- They should only query marts

Query guardrails

Prevent runaway queries from consuming warehouse resources:

-- Snowflake: warehouse-level query timeout
ALTER WAREHOUSE analyst_wh SET
  STATEMENT_TIMEOUT_IN_SECONDS = 300;

-- BigQuery: use BI Engine for fast, bounded queries
-- Redshift: WLM queue with memory and concurrency limits

Onboarding analysts to self-serve

The self-serve onboarding checklist

1. Share the dbt docs URL (or data catalog link)
2. Walk through the 3-5 most important mart tables and their grains
3. Show how to find metric definitions in the semantic layer
4. Provide 5-10 example queries covering common analysis patterns
5. Set up a Slack channel (#data-questions) for questions with a 24h SLA
6. Review their first 3 queries/dashboards for correctness
7. After 2 weeks, check: are they filing fewer data tickets? If not, the models or documentation need improvement, not the analyst.

Example starter queries for analysts

-- Revenue by month
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(total_amount_cents) / 100.0 AS revenue_dollars
FROM analytics.fct_orders
WHERE status = 'delivered'
GROUP BY 1
ORDER BY 1;

-- Customer segmentation breakdown
SELECT
  customer_segment,
  COUNT(*) AS customer_count,
  AVG(lifetime_value_cents) / 100.0 AS avg_ltv_dollars
FROM analytics.dim_customers
GROUP BY 1
ORDER BY 3 DESC;

-- New vs returning customers by week
SELECT
  DATE_TRUNC('week', o.order_date) AS week,
  COUNT(CASE WHEN o.is_first_order THEN 1 END) AS new_customers,
  COUNT(CASE WHEN NOT o.is_first_order THEN 1 END) AS returning_customers
FROM analytics.fct_orders o
WHERE o.status != 'cancelled'
GROUP BY 1
ORDER BY 1;

Measuring self-serve success

Track these metrics to know if self-serve is working:

Semantic Layer Deep Dive

What is a semantic layer?

A semantic layer is a declarative abstraction between your data warehouse tables and the consumers (BI tools, notebooks, APIs, LLM agents). It defines:

• Entities - the join keys and relationships between tables
• Dimensions - the columns you group by or filter on
• Measures - the aggregations (sum, count, average) applied to columns
• Metrics - business-level calculations composed from measures

The semantic layer compiles these definitions into optimized SQL at query time. Every consumer gets the same metric logic, regardless of which tool they use.

MetricFlow (dbt Semantic Layer)

MetricFlow is the engine behind the dbt Semantic Layer (dbt Cloud). It uses semantic_models and metrics defined in YAML.

Semantic model anatomy

semantic_models:
  - name: orders
    description: "Order fact table"
    model: ref('fct_orders')
    defaults:
      agg_time_dimension: order_date

    entities:
      - name: order_id
        type: primary
      - name: customer_id
        type: foreign
      - name: product_id
        type: foreign

    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: status
        type: categorical
      - name: channel
        type: categorical

    measures:
      - name: order_count
        agg: count
        expr: order_id
        description: "Count of orders"
      - name: total_revenue
        agg: sum
        expr: total_amount_cents
        description: "Sum of order amounts in cents"
      - name: avg_order_value
        agg: average
        expr: total_amount_cents

Entity types

Dimension types

Measure aggregation types

Metric types

Simple metric

Direct reference to a single measure:

metrics:
  - name: total_orders
    type: simple
    label: "Total Orders"
    description: "Count of all orders"
    type_params:
      measure: order_count

Derived metric

Calculation across multiple measures:

metrics:
  - name: average_revenue_per_customer
    type: derived
    label: "Revenue per Customer"
    description: "Total revenue divided by unique customer count"
    type_params:
      expr: total_revenue / unique_customers
      metrics:
        - name: total_revenue
        - name: unique_customers

Ratio metric

Special case of derived for common ratio patterns:

metrics:
  - name: order_conversion_rate
    type: ratio
    label: "Order Conversion Rate"
    type_params:
      numerator: completed_orders
      denominator: total_orders

Cumulative metric

Running totals over time:

metrics:
  - name: cumulative_revenue
    type: cumulative
    label: "Cumulative Revenue"
    type_params:
      measure: total_revenue
      window: 1 month
      grain_to_date: month

Cube semantic layer

Cube is an alternative semantic layer that works with any BI tool via REST/GraphQL API.

Cube schema file

// schema/Orders.js
cube('Orders', {
  sql_table: 'analytics.fct_orders',

  joins: {
    Customers: {
      relationship: 'many_to_one',
      sql: `${CUBE}.customer_id = ${Customers}.customer_id`
    }
  },

  dimensions: {
    orderId: {
      sql: 'order_id',
      type: 'string',
      primary_key: true
    },
    status: {
      sql: 'status',
      type: 'string'
    },
    orderDate: {
      sql: 'order_date',
      type: 'time'
    }
  },

  measures: {
    count: {
      type: 'count'
    },
    totalRevenue: {
      sql: 'total_amount_cents',
      type: 'sum'
    },
    averageOrderValue: {
      sql: 'total_amount_cents',
      type: 'avg'
    }
  }
});

Cube vs MetricFlow comparison

BI tool integration patterns

Exposures (dbt)

Document which BI assets depend on which models:

# models/exposures.yml
exposures:
  - name: weekly_revenue_dashboard
    type: dashboard
    maturity: high
    url: https://bi.company.com/dashboards/42
    description: "Executive revenue dashboard, refreshed daily"
    depends_on:
      - ref('fct_orders')
      - ref('dim_customers')
    owner:
      name: Data Team
      email: data@company.com

Exposures show up in the dbt docs DAG, making it visible which dashboards break when a model changes.

Metric query patterns

When querying metrics through the semantic layer API:

# dbt Cloud Semantic Layer (Python SDK)
from dbt_sl_sdk import SemanticLayerClient

client = SemanticLayerClient(
    environment_id=12345,
    auth_token="dbt_cloud_token"
)

result = client.query(
    metrics=["revenue", "order_count"],
    group_by=["metric_time__month", "customer__segment"],
    where=["{{ Dimension('order__status') }} = 'completed'"],
    order_by=["-metric_time__month"],
    limit=100
)

Common semantic layer pitfalls