SET UP AMAZON REDSHIFT

A COMPLETE WORKING PROFILE

User/password auth is the method that works everywhere in DVT. Drop this in your profiles.yml — the host is your cluster endpoint (or your Serverless workgroup endpoint, which behaves identically):

my_project:
  target: rs_prod
  outputs:
    rs_prod:
      type: redshift
      host: my-cluster.abc123.us-east-1.redshift.amazonaws.com
      port: 5439
      user: analytics
      password: "{{ env_var('REDSHIFT_PASSWORD') }}"
      dbname: analytics
      schema: public
      threads: 4

Always write port: 5439 explicitly.Redshift listens on 5439, but because the connection plumbing is shared with Postgres, an omitted port falls back to Postgres's 5432 — and you get a connection timeout instead of a login. One line saves the debugging session.

Then install the driver and prove the connection end to end:

dvt sync           # reads profiles.yml, installs the Redshift adapter + driver
dvt debug --all    # connects in both lanes and says exactly what failed if anything

dvt sync is per-profile: it installs drivers for the output types you actually declared, nothing more.

IAM AUTH — THE HONEST PICTURE

dbt-redshift supports method: iam, and DVT's dbt lane inherits it: as your default target, an IAM-authenticated output runs standard models fine. The data-movement lane is a different animal — it connects over the Postgres wire protocol with its own driver, and that driver has no IAM handshake. The moment an IAM output is used for federation (as a source being read, or as the target of an f_table), DVT refuses with the fix named rather than failing somewhere deeper:

redshift: method 'iam' works in the dbt lane, but Sling
federation needs user/password — add user and password to
this output for federation

The working setup for federation is a plain database user. Create one with the grants your models need, keep the secret in an environment variable, and the same output now works in every lane. If your security posture requires IAM everywhere, keep the IAM output for default-target runs and give federation its own user/password output — two entries in profiles.yml, each honest about what it can do.

TLS — SECURE BY DEFAULT

Redshift endpoints enforce TLS, so DVT's data-movement lane defaults sslmode to require— the opposite of the Postgres lane's local-dev-friendly soft default. An unset sslmode (or the soft prefer/allow) becomes require; the strict modes verify-ca and verify-full are honored as written. To verify the server certificate, point sslrootcert at the Amazon Redshift CA bundle:

    rs_verified:
      type: redshift
      host: my-cluster.abc123.us-east-1.redshift.amazonaws.com
      port: 5439
      user: analytics
      password: "{{ env_var('REDSHIFT_PASSWORD') }}"
      dbname: analytics
      schema: public
      sslmode: verify-full
      sslrootcert: ~/.redshift/redshift-ca-bundle.crt

ENGINE BEHAVIOR WORTH KNOWING

Naming. The canonical database key is dbname, with database accepted as an alias. The default schema is public, and identifiers fold to lowercase — Redshift is case-insensitive by default, so mixed-case source tables are rarely a problem here.

It's a warehouse, not a Postgres. The SQL dialect is Postgres-flavored, but the physical model is columnar: there are no indexes to manage — performance tuning happens through sort keys, dist keys, and column encodings, all available as dbt model configs when Redshift is your default target.

Serverless. Redshift Serverless needs no special handling — put the workgroup endpoint in host and everything on this page applies unchanged.

INCREMENTAL MODELS — POSTGRES'S LIST, REDSHIFT'S BEHAVIOUR

On a Redshift default target, incremental_strategy accepts four names, spelled exactly as here: append, delete+insert, merge, and microbatch. The list matches Postgres's, but Redshift writes its own macros for three of them, and the differences are the reason this section exists.

Set the strategy explicitly. Redshift declares no default macro of its own— the fallback is resolved by dbt's dispatch chain, not by anything the Redshift adapter says, so the honest answer is that this page cannot read it off the source you are running. Writing incremental_strategy into the config removes the question entirely, and costs one line:

{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',   -- append | delete+insert | merge | microbatch
    unique_key='order_id',
    sort='updated_at',
    dist='order_id'
) }}

select order_id, status, updated_at
from {{ source('warehouse', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

The key rules are the loose kind that cost you rows rather than raising errors. delete+insert without a unique_key skips the delete entirely and behaves as an append; a string key is normalised to a single-element list, so both spellings work. merge without a unique_key is likewise not an error — the join predicate becomes FALSE and the matched branch is skipped, giving an insert-only run. merge does honour merge_update_columns, merge_exclude_columns, and sql_header.

microbatch is Redshift's deliberate divergence: it does not require a unique_key — the adapter re-implements delete+insert without one on purpose — and instead needs batch bounds and an event_time config, from which it builds the window it deletes and reloads. Run it outside a batch context and you get dbt could not compute the start and end timestamps for the running batch.

One more Redshift-only rewrite, easy to trip over: incremental_predicates are rewritten before they reach the engine. Both spellings of the internal alias — DBT_INTERNAL_DEST. and dbt_internal_dest.— are replaced with the target relation's own name, on merge, delete+insert, and microbatchalike. A predicate written against the alias still works; it just isn't the SQL that runs.

insert_overwriteis a dbt builtin but is absent from Redshift's list, so it is refused by name: The incremental strategy 'insert_overwrite' is not valid for this adapter. Redshift ships no validator of its own, so that base message is the one you will see — a name outside dbt's builtins is instead treated as a custom strategy macro and fails with dbt could not find an incremental strategy macro with the name "…". Separately, an incremental model can only span two databases on RA3 nodes: Cross-db references allowed only in redshift RA3.* node.

When Redshift is the target of a federated f_incremental model, the vocabulary shrinks to append, merge, and delete+insert; insert_overwrite and microbatch are refused because they have no equivalent in the bulk loader that lands the rows. On that lane merge and delete+insert collapse into the same operation — a merge on unique_key — and a missing key is a warning and a full refresh rather than an error, as is append without a watermark_column. Redshift is one of the engines whose loader has been verified to honour an update-key-only load, so a keyless f_incremental with a watermark runs here; unverified engines — StarRocks, Snowflake, Databricks, BigQuery, Fabric and Athena — are refused with DVT026. DVT substitutes no strategy on Redshift — the one substitution the product makes anywhere is ClickHouse's merge → delete+insert.

REFERENCE — EVERY PROFILE FIELD

Everything a type: redshiftoutput reads. Fields not listed here are ignored by DVT's connection mapping.