+1 (726) 227-3497
RougeWarehouse

Running dbt on Amazon Redshift: Project Layout, Incremental Models and CI

Most Redshift warehouses we are brought into have their loading solved — COPY, Zero-ETL or streaming ingestion drops raw data in — and their transformation layer held together with stored procedures fired by a scheduler, with no tests, no lineage and no way to rebuild one model without rebuilding everything. dbt fixes that, and it fits Redshift well because Redshift is a plain PostgreSQL-dialect SQL engine with a good MERGE implementation.

This guide covers the parts that are specific to Redshift, not dbt basics. We assume you have raw tables landing in a raw schema and want a tested, incremental analytics layer on top.

Connecting: profiles for RA3 and Serverless

The dbt-redshift adapter connects over the normal PostgreSQL wire protocol. Do not put a password in profiles.yml. Two better options:

IAM authentication against a provisioned RA3 cluster:

warehouse:
  target: dev
  outputs:
    dev:
      type: redshift
      method: iam
      cluster_id: analytics-ra3
      host: analytics-ra3.abc123.us-east-1.redshift.amazonaws.com
      user: dbt_dev
      dbname: warehouse
      schema: "dbt_{{ env_var('USER') }}"
      region: us-east-1
      port: 5439
      threads: 8
      keepalives_idle: 240
      connect_timeout: 30
      sslmode: require

Serverless uses method: iam too, but with the workgroup instead of a cluster ID:

    prod:
      type: redshift
      method: iam
      host: analytics-wg.111111111111.us-east-1.redshift-serverless.amazonaws.com
      is_serverless: true
      serverless_work_group: analytics-wg
      user: dbt_prod
      dbname: warehouse
      schema: analytics
      region: us-east-1
      threads: 12

Notes from experience:

  • threads is how many models dbt builds concurrently, and each one is a Redshift connection. On a small Serverless workgroup or a 2-node RA3, 4–8 is plenty; pushing to 32 just queues everything in WLM and makes the run slower and harder to read. Increase only while watching queue wait in SYS_QUERY_HISTORY.
  • Give dbt its own database user and its own query priority so a long rebuild cannot starve BI dashboards. If you use manual WLM, route the dbt user to its own queue.
  • Set keepalives_idle — long-running models behind a NAT gateway otherwise die silently.
  • Every developer gets their own target schema (dbt_alice). Production writes to analytics.

Project layout

models/
  staging/        # one model per source table, views, light renaming/casting
    _sources.yml
    stg_orders.sql
  intermediate/   # ephemeral or view joins, no user ever queries these
  marts/
    finance/
      fct_orders.sql
      dim_customer.sql
snapshots/
tests/
macros/

Staging models should be views — they cost nothing to build and Redshift inlines them. Marts are tables or incremental models. Intermediate models are usually ephemeral (compiled into a CTE) unless they are reused by many downstream models and expensive, in which case make them tables.

Declare your raw tables as sources with freshness so a stalled pipeline fails loudly:

version: 2
sources:
  - name: raw
    schema: raw
    tables:
      - name: orders
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 2, period: hour}
          error_after: {count: 6, period: hour}

Redshift-specific model configs

The adapter exposes the physical design knobs directly:

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    dist='customer_id',
    sort=['ordered_at', 'customer_id'],
    sort_type='compound',
    on_schema_change='append_new_columns'
) }}

What we actually recommend setting:

  • dist — set it when you know the join column of a large fact table. Leaving it off gives AUTO, which is a good default; Automatic Table Optimization will converge on the right style after it has seen a workload. See our post on sort and distribution keys in the auto era.
  • sort — a compound sort key led by the timestamp that most queries filter on. Never sort_type='interleaved'; the VACUUM REINDEX cost is not worth it.
  • bind=false for views built on external/Spectrum or datashare objects, which creates a late-binding view so dbt does not have to resolve the dependency at build time.
  • backup=false on large rebuildable staging tables to keep them out of snapshots.

Skip ENCODE entirely — Redshift picks AZ64/ZSTD better than a hand-written list will.

Incremental strategies

This is where most of the runtime savings are. dbt-redshift supports three strategies.

append — fastest, no deduplication. Only correct for immutable event streams.

delete+insert — deletes rows matching the unique_key then inserts the new batch. Two statements in one transaction.

merge — compiles to Redshift's native MERGE, which is a single statement and generally the best choice for mutable facts and dimensions:

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

SELECT
    o.order_id,
    o.customer_id,
    o.status,
    o.total_amount,
    o.ordered_at,
    o.updated_at
FROM {{ source('raw', 'orders') }} o
{% if is_incremental() %}
WHERE o.updated_at > (SELECT COALESCE(MAX(updated_at), '1900-01-01') FROM {{ this }})
{% endif %}

Two things make or break incremental models on Redshift:

  1. The is_incremental() filter must be sargable against the sort key. WHERE updated_at > (SELECT MAX(...)) is fine; wrapping the column in a function is not, and you will rescan the whole source.
  2. Give yourself a late-arrival window. Sources are rarely perfectly ordered. WHERE o.updated_at > (SELECT MAX(updated_at) FROM {{ this }}) - INTERVAL '3 days' reprocesses three days every run and, with merge, remains idempotent.

For very large backfills use the microbatch materialization, which splits the build into per-day batches so a failed run does not lose the whole rebuild:

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='ordered_at',
    batch_size='day',
    lookback=3,
    begin='2024-01-01',
    unique_key='order_id'
) }}

SELECT * FROM {{ source('raw', 'orders') }}

dbt generates the time filter for each batch itself; you do not write is_incremental().

Whichever strategy you pick, add full_refresh: false to models whose source data has aged out of the raw layer, so nobody accidentally truncates history with dbt run --full-refresh.

Tests worth having

Start with four generic tests on every mart model and resist adding fifty more:

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

Redshift does not enforce primary keys or uniqueness — the constraints are declarative hints to the planner only. That makes unique and not_null tests non-optional; they are the only thing standing between you and a silently duplicated fact table after a replayed load.

Do declare the constraints anyway (primary key, foreign key, not null) via dbt contracts: the optimizer uses them to eliminate joins and de-duplicate, which is a real speedup on star-schema queries.

Snapshots for slowly changing dimensions

If your source overwrites records in place, a dbt snapshot gives you SCD Type 2 history without hand-written merge logic:

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

Snapshot tables are append-mostly and grow forever, so give them a sort key on dbt_valid_from and never --full-refresh them.

A CI job that only builds what changed

The pattern that works on Redshift: production runs into analytics, CI builds into a throwaway schema seeded from production state via defer.

# .github/workflows/dbt_ci.yml (excerpt)
- name: dbt build changed models
  run: |
    dbt deps
    aws s3 cp s3://our-dbt-artifacts/prod/manifest.json ./prod/manifest.json
    dbt build --select state:modified+ \
              --defer --state ./prod \
              --target ci --fail-fast
- name: drop CI schema
  if: always()
  run: dbt run-operation drop_ci_schema --args "{schema: $CI_SCHEMA}"

state:modified+ builds the changed models and everything downstream; --defer resolves unchanged upstream ref()s to the production tables instead of rebuilding them. On a 400-model project that is the difference between a 40-minute PR check and a 3-minute one. Upload the production manifest.json to S3 at the end of every scheduled prod run so CI has something to diff against.

Drop the CI schema in an always() step — orphaned CI schemas are one of the more common sources of unexplained Redshift storage growth.

Operating notes

  • Watch the run, not just the exit code. dbt writes run_results.json with per-model timing; load it into Redshift and you have a model-level performance history for free.
  • dbt run-operation for maintenance. Auto-vacuum handles most of it, but a monthly ANALYZE on the biggest incremental tables after large merges is cheap insurance against stale statistics.
  • Concurrency Scaling is worth enabling for the queue dbt runs in if your build overlaps with BI traffic; it burns credits only under queueing.
  • Don't grant dbt superuser. It needs CREATE on its target schemas and SELECT on sources. Use RBAC roles — see our post on RBAC, row-level security and dynamic data masking.

Where it usually goes wrong

The three failure modes we are called in to fix:

  1. Everything is a table, nothing is incremental. A 6-hour nightly run that rebuilds facts from scratch. Converting the top five models to merge incrementals typically cuts it by 80%.
  2. threads: 32 on a small workgroup. The run looks parallel and is actually serialized in the queue, with worse memory allocation per query.
  3. No dist/sort on the big marts, and no workload history for ATO to learn from because everything is dropped and recreated nightly. Incremental models keep the table alive so Automatic Table Optimization can do its job.

If you have a Redshift warehouse and a transformation layer you cannot test or rebuild safely, that migration — stored procedures to a tested dbt project with CI — is one of the most common engagements we run. Talk to us via Get In Touch, or read more about our Data Modeling & Architecture and Redshift Performance Optimization work.