Skip to main content

Redshift setup

profiles.yml file is for dbt Core users only

If you're using dbt Cloud, you don't need to create a profiles.yml file. This file is only for dbt Core users. To connect your data platform to dbt Cloud, refer to About data platforms.

  • Maintained by: dbt Labs
  • Authors: core dbt maintainers
  • GitHub repo: dbt-labs/dbt-redshift
  • PyPI package: dbt-redshift
  • Slack channel: #db-redshift
  • Supported dbt Core version: v0.10.0 and newer
  • dbt Cloud support: Supported
  • Minimum data platform version: n/a

Installing dbt-redshift

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

Configuring dbt-redshift

For Redshift-specific configuration, please refer to Redshift configs.

Configurations

Profile fieldExampleDescription
typeredshiftThe type of data warehouse you are connecting to
hosthostname.region.redshift.amazonaws.comHost of cluster
port5439
dbnamemy_dbDatabase name
schemamy_schemaSchema name
connect_timeoutNone or 30Number of seconds before connection times out
sslmodepreferoptional, set the sslmode to connect to the database. Default prefer, which will use 'verify-ca' to connect. For more information on sslmode, see Redshift note below
roleNoneOptional, user identifier of the current session
autocreatefalseOptional, default false. Creates user if they do not exist
db_groups['ANALYSTS']Optional. A list of existing database group names that the DbUser joins for the current session
ra3_nodetrueOptional, default False. Enables cross-database sources
autocommittrueOptional, default True. Enables autocommit after each statement
retries1Number of retries

Authentication Parameters

The authentication methods that dbt Core supports are:

  • database Password-based authentication (default, will be used if method is not provided)
  • IAM IAM

For dbt Cloud users, log in using the default Database username and password. This is necessary because dbt Cloud does not support IAM authentication.

Click on one of these authentication methods for further details on how to configure your connection profile. Each tab also includes an example profiles.yml configuration file for you to review.

The following table contains the parameters for the database (password-based) connection method.

Profile fieldExampleDescription
methoddatabaseLeave this parameter unconfigured, or set this to database
hosthostname.region.redshift.amazonaws.comHost of cluster
userusernameAccount username to log into your cluster
passwordpassword1Password for authentication

Example profiles.yml for database authentication

~/.dbt/profiles.yml
company-name:
target: dev
outputs:
dev:
type: redshift
host: hostname.region.redshift.amazonaws.com
user: username
password: password1
dbname: analytics
schema: analytics
port: 5439

# Optional Redshift configs:
sslmode: prefer
role: None
ra3_node: true
autocommit: true
threads: 4
connect_timeout: None

Specifying an IAM Profile

When the iam_profile configuration is set, dbt will use the specified profile from your ~/.aws/config file instead of using the profile name default

Redshift notes

sslmode change

Before to dbt-redshift 1.5, psycopg2 was used as the driver. psycopg2 accepts disable, prefer, allow, require, verify-ca, verify-full as valid inputs of sslmode, and does not have an ssl parameter, as indicated in PostgreSQL doc.

In dbt-redshift 1.5, we switched to using redshift_connector, which accepts verify-ca, and verify-full as valid sslmode inputs, and has a ssl parameter of True or False, according to redshift doc.

For backward compatibility, dbt-redshift now supports valid inputs for sslmode in psycopg2. We've added conversion logic mapping each of psycopg2's accepted sslmode values to the corresponding ssl and sslmode parameters in redshift_connector.

The table below details accepted sslmode parameters and how the connection will be made according to each option:

sslmode parameterExpected behavior in dbt-redshiftActions behind the scenes
disableConnection will be made without using sslSet ssl = False
allowConnection will be made using verify-caSet ssl = True & sslmode = verify-ca
preferConnection will be made using verify-caSet ssl = True & sslmode = verify-ca
requireConnection will be made using verify-caSet ssl = True & sslmode = verify-ca
verify-caConnection will be made using verify-caSet ssl = True & sslmode = verify-ca
verify-fullConnection will be made using verify-fullSet ssl = True & sslmode = verify-full

When a connection is made using verify-ca, will look for the CA certificate in ~/redshift-ca-bundle.crt.

For more details on sslmode changes, our design choices, and reasoning please refer to the PR pertaining to this change.

autocommit parameter

The autocommit mode is useful to execute commands that run outside a transaction. Connection objects used in Python must have autocommit = True to run operations such as CREATE DATABASE, and VACUUM. autocommit is off by default in redshift_connector, but we've changed this default to True to ensure certain macros run successfully in your dbt project.

If desired, you can define a separate target with autocommit=True as such:

~/.dbt/profiles.yml
profile-to-my-RS-target:
target: dev
outputs:
dev:
type: redshift
...
autocommit: False


profile-to-my-RS-target-with-autocommit-enabled:
target: dev
outputs:
dev:
type: redshift
...
autocommit: True

To run certain macros with autocommit, load the profile with autocommit using the --profile flag. For more context, please refer to this PR.

Deprecated profile parameters in 1.5

  • iam_duration_seconds

  • keepalives_idle

sort and dist keys

Where possible, dbt enables the use of sort and dist keys. See the section on Redshift specific configurations.

0