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 ofall
,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 ofinterleaved
orcompound
. if no setting is specified, sort_type defaults tocompound
.
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_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:
{{ config(materialized='view', bind=False) }}
select *
from source.data
To make all views late-binding, configure your dbt_project.yml
file like this:
models:
+bind: false # Materialize all views as late-binding
project_name:
....