Redshift configurations

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:

my_model.sql

-- 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_type='interleaved'
          sort=['id', 'category'],
          dist='received_at')
}}

select ...

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

• AWS Documentation » Amazon Redshift » Database Developer Guide » Designing Tables » Choosing a Data Distribution Style
• AWS Documentation » Amazon Redshift » Database Developer Guide » Designing Tables » Choosing Sort Keys

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:

my_view.sql

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

select *
from source.data

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

dbt_project.yml

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