How to Connect Redshift to dbt in PostgreSQL

How do I connect Amazon Redshift to dbt?

Connect Amazon Redshift as a target in dbt so you can compile, test, and run transformation models directly on your warehouse.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why pair dbt with Amazon Redshift?

dbt automates SQL transforms, while Redshift supplies elastic storage and compute. Linking them lets teams version code, enforce tests, and deploy production pipelines in minutes.

What are the prerequisites?

Create a Redshift user with CREATE and USAGE on target schemas, whitelist your IP (or use an SSH tunnel), and install the adapter: pip install dbt-redshift.

How do I create a Redshift profile in dbt?

Add a profile to ~/.dbt/profiles.yml. Each key below is required unless noted.

profiles.yml template

my_redshift_project: target: dev outputs: dev: type: redshift # adapter name host: redshift-cluster.abc123.us-east-1.redshift.amazonaws.com port: 5439 user: analytics password: "{{ env_var('REDSHIFT_PASSWORD') }}" dbname: analytics schema: public threads: 4 sslmode: require ra3_node: false # set true for RA3 clusters search_path: "$user,public"

How can I test the connection?

Run dbt debug --target dev. A "PASSED" message confirms credentials, network, and permissions are correct.

How do I run models on Redshift?

Compile and run with dbt run --select customers orders order_items. dbt creates tables or views in analytics.public unless overridden in dbt_project.yml.

What best practices should I follow?

Use environment variables for secrets, limit threads to your WLM queue, adopt incremental models for large fact tables, and grant model schemas to downstream BI roles.

What are common mistakes?

Wrong schema: Leaving schema: public in profiles.yml when production tables live elsewhere causes run failures. Update the schema or set generate_schema_name macro.

Missing privileges: Forgetting to grant CREATE on target schemas leads to permission errors. Grant the role or switch to a schema you own.

Why How to Connect Redshift to dbt in PostgreSQL is important

How to Connect Redshift to dbt in PostgreSQL Example Usage


-- models/order_revenue.sql
select
  o.id          as order_id,
  c.id          as customer_id,
  sum(oi.quantity * p.price) as order_total
from       o
join    c on c.id = o.customer_id
join   oi on oi.order_id = o.id
join     p on p.id = oi.product_id
group by 1,2;

How to Connect Redshift to dbt in PostgreSQL Syntax


# profiles.yml syntax for Amazon Redshift
type: redshift
host: <cluster-endpoint>
port: 5439
user: <db-user>
password: <db-password> | 
dbname: <database>
schema: <schema>
threads: <int>  # parallelism
sslmode: require | verify-full
ra3_node: true | false
search_path: "$user,public"

Common Mistakes

Using the wrong adapter type (e.g., "postgres") instead of "redshift". Always set <code>type: redshift</code> to enable Redshift-specific features and optimizations.
Forgetting to include the port (5439) or using the JDBC port (5432). Redshift listens on 5439; mismatching ports results in timeouts.