Redshift configurations

Heads up!

These docs are a work in progress.

Performance Optimizations

Using sortkey and distkey

Tables in Amazon Redshift have two powerful optimizations to improve query performance: distkeys and sortkeys. Supplying these values as model-level configurations apply the corresponding settings in the generated CREATE TABLE DDL. Note that these settings will have no effect for models set to view or ephemeral models.

  • dist can have a setting of all, even, auto, or the name of a key.
  • sort accepts a list of sort keys, for example: ['timestamp', 'userid']. dbt will build the sort key in the same order the fields are supplied.
  • sort_type can have a setting of interleaved or compound. if no setting is specified, sort_type defaults to compound.

Sort and dist keys should be added to the {{ config(...) }} block in model .sql files, eg:

-- Example with one sort key
{{ config(materialized='table', sort='id', dist='received_at') }}
select ...
-- Example with multiple sort keys
{{ config(materialized='table', sort=['id', 'category'], dist='received_at') }}
select ...
-- Example with interleaved sort keys
{{ config(materialized='table',
sort=['id', 'category'],
select ...

For more information on distkeys and sortkeys, view Amazon's docs:

Late Binding Views

Redshift supports views unbound from their dependencies, or late binding views. This DDL option "unbinds" a view from the data it selects from. In practice, this means that if upstream views or tables are dropped with a cascade qualifier, the late-binding view does not get dropped as well.

Using late-binding views in a production deployment of dbt can vastly improve the availability of data in the warehouse, especially for models that are materialized as late-binding views and are queried by end-users, since they won’t be dropped when upstream models are updated. Additionally, late binding views can be used with external tables via Redshift Spectrum.

To materialize a dbt model as a late binding view, use the bind: false configuration option:

{{ config(materialized='view', bind=False) }}
select *

To make all views late-binding, configure your dbt_project.yml file like this:

+bind: false # Materialize all views as late-binding