> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/build/productize/connect-engine/redshift.md).

# Amazon Redshift

Configure Amazon Redshift as the engine for your Data Product.

Amazon Redshift is a cloud data warehouse with a Postgres-family SQL dialect, running on RA3 provisioned clusters or Redshift Serverless. Use the `redshift` adapter when your data already lands in a Redshift cluster or Serverless workgroup and you want governed transformations to run close to it on AWS. This page covers the connection. For grants, materialization internals, WLM tuning, and the full reference, see the [Redshift engine manual](https://v2.dataos.info/references/engine-guide/redshift) in References.

## Before you start

* A Redshift cluster or Serverless workgroup, its endpoint, and port (`5439` by default). `connection.database` must already exist, and the service user must be able to connect over SSL.
* A database user for Vulcan, plus a separate read-only user for consumers and metadata scanning.
* An external Postgres `state_connection` (strongly recommended): Vulcan's plan and interval state lives in Postgres, isolated per project with `state_schema`.
* The Vulcan CLI with the `redshift` extra: `pip install "./vulcan-0.228.1.32-py3-none-any.whl[redshift]"`. Redshift has no official local container, so point local development at a small dev cluster or a low-RPU Serverless workgroup and keep only the state store local.
* DataOS access: a `roles:id:data-dev`-equivalent role tag, `depot:rw:<redshift-depot-name>` (or `depot:r` for read-only), **Can Use** on Compute/Depots/Secrets, and a git-sync Secret. Verify with `dataos-ctl user get` and `dataos-ctl resource -t depot get -a`.

## Permissions

Three roles are required — don't collapse them into one: an AWS/warehouse **admin** (provisions the cluster, creates databases and schemas, configures WLM and Spectrum IAM roles), the **Vulcan service role** (`CREATE`, `INSERT`, `DELETE`, `MERGE` on target schemas; reads sources), and a **consumer role** (`SELECT` on published schemas and tables only). Run minimum grants as admin on the cluster:

```sql
CREATE SCHEMA IF NOT EXISTS bronzev2;
CREATE SCHEMA IF NOT EXISTS silverv2;
CREATE SCHEMA IF NOT EXISTS goldv2;

GRANT USAGE ON SCHEMA bronzev2, silverv2, goldv2 TO vulcan_etl;
GRANT CREATE ON SCHEMA bronzev2, silverv2, goldv2 TO vulcan_etl;
GRANT ALL ON ALL TABLES IN SCHEMA bronzev2 TO vulcan_etl;
GRANT ALL ON ALL TABLES IN SCHEMA silverv2 TO vulcan_etl;
GRANT ALL ON ALL TABLES IN SCHEMA goldv2 TO vulcan_etl;

GRANT USAGE ON SCHEMA public TO vulcan_etl;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO vulcan_etl;
```

Adjust schema names to your project. Grant consumers read-only access the same way, with `USAGE` / `SELECT` on the published schema instead. Verify with `SELECT current_user, current_database();` and a query on `information_schema.schemata`.

## Connection options

Connect directly with credentials, or through a [DataOS Depot](#using-a-depot) (recommended for production).

| Option     | Required | Description                                      |
| ---------- | :------: | ------------------------------------------------ |
| `type`     |    Yes   | Must be `redshift`                               |
| `host`     |    Yes   | Redshift cluster or Serverless endpoint          |
| `port`     |    Yes   | Port number. Defaults to `5439`                  |
| `database` |    Yes   | Database to connect to. Must already exist       |
| `user`     |    Yes   | Authentication username                          |
| `password` |    Yes   | Authentication password                          |
| `ssl`      |    No    | Enable SSL for the connection                    |
| `sslmode`  |    No    | Connection security level, for example `require` |

### Authentication methods

* **Username and password** (required): set `user` and `password`.
* **SSL** (required for production): set `ssl: true` and `sslmode: require`.
* Always inject the password from an environment variable: `password: "{{ env_var('REDSHIFT_PASSWORD') }}"`.

## Example

```yaml
gateways:
  default:
    connection:
      type: redshift
      host: "{{ env_var('REDSHIFT_HOST') }}"
      port: "{{ env_var('REDSHIFT_PORT', default='5439') }}"
      database: "{{ env_var('REDSHIFT_DATABASE', default='dev') }}"
      user: "{{ env_var('REDSHIFT_USER') }}"
      password: "{{ env_var('REDSHIFT_PASSWORD') }}"
      ssl: true
      sslmode: require
    state_connection:
      type: postgres
      host: "{{ env_var('STATESTORE_HOST') }}"
      port: "{{ env_var('STATESTORE_PORT', default='5432') }}"
      user: "{{ env_var('STATESTORE_USER') }}"
      password: "{{ env_var('STATESTORE_PASSWORD') }}"
      database: "{{ env_var('STATESTORE_DATABASE') }}"
    state_schema: <project_name>
default_gateway: default
model_defaults:
  dialect: redshift
```

For local state, run a Postgres container (`postgres:15`) on port 5432, then validate against your Redshift target with `vulcan migrate` and `vulcan plan`.

{% hint style="info" %}
Redshift's adapter is Postgres-family but not identical. Set `dialect: redshift`, not `postgres`: identifier casing, `MERGE`/replace-table behavior, and view comments all differ from vanilla PostgreSQL.
{% endhint %}

### Using a Depot

```yaml
gateways:
  default:
    connection:
      type: depot
      address: dataos://<redshift-depot-name>?purpose=rw
```

The depot manages the Redshift credential and access purpose. Bind each purpose to its real use: `rw` for the Vulcan workflow, `scan` for the metadata scanner, and `query` for read-only consumers. A `query` credential can't create tables, and handing consumers an `rw` credential over-permissions them. Apply the Redshift instance secret, the Redshift depot, and the git-sync Secret in that order, then deploy from `config.yaml`: deploy settings live under `deployment:` there, and `vulcan create_deploy_yaml` renders the Vulcan resource.

{% hint style="warning" %}
The depot `name` must match `dataos://<name>?purpose=rw` exactly in the gateway `address` in `config.yaml`, and in `deployment.depots` if you override it there. A mismatch passes `apply` but fails workflow runs.
{% endhint %}

## Supported model kinds

`FULL`, `VIEW`, `INCREMENTAL_BY_TIME_RANGE`, `INCREMENTAL_BY_UNIQUE_KEY`, and `INCREMENTAL_BY_PARTITION`; validate at volume before relying on them for large backfills. `FULL` rebuilds the table (truncate plus insert per adapter), `VIEW` compiles to `CREATE VIEW`, `INCREMENTAL_BY_TIME_RANGE` runs a windowed delete/merge plus insert, `INCREMENTAL_BY_UNIQUE_KEY` uses `MERGE` or delete-plus-insert on the key, and `INCREMENTAL_BY_PARTITION` overwrites the partition. Python models run where the adapter supports them; validate them against your Vulcan image first. For the full mapping, see the [Redshift engine manual](https://v2.dataos.info/references/engine-guide/redshift).

> Redshift has no `REPLACE TABLE` semantics and no view comments through Vulcan (document views in the semantic `description` or `ai_context` instead), and transactions are limited, so design incrementals to be idempotent on retry. Match semantic identifiers to the physical column casing, which is often lowercase snake\_case: the same direction as Postgres, the opposite of Snowflake and Spark.

Add `DISTKEY` / `COMPOUND SORTKEY` in `post_statements`, and wrap all lifecycle DDL in `@IF(@runtime_stage = 'evaluating', ...)`, or `vulcan plan` executes it. Set session tuning such as `SET statement_timeout` and `SET query_group` in `before_all` and reset it in `after_all`; run `ANALYZE` on hot tables in `after_all` after large loads. Declare cross-schema sources in `inputs.yaml`.

## Limits and performance

Use a small node or a low-RPU Serverless workgroup for development, and scale up temporarily for a first backfill. Align `concurrent_tasks` with WLM queue slots, raise API replicas before maxing out cluster concurrency, and give the semantic API and BI a dedicated queue or concurrency scaling. Keep `api.resources.limits.memory` at 3 GiB for high-row responses. WLM queue wait dominates under load: monitor `STL_WLM_QUERY`. The semantic API adds roughly 1.5–3 seconds of overhead.

Redshift bills for provisioned cluster hours or Serverless RPUs, plus storage. Keep development in a capped WLM queue, enable concurrency scaling only for approved production queues with a usage limit set, and bound runaway queries with `statement_timeout`. Put a sort key on the incremental time column and filter incrementals on that same column to reduce scanned blocks.

## Troubleshooting

| Symptom                         | Likely cause                   | Fix                                                                   |
| ------------------------------- | ------------------------------ | --------------------------------------------------------------------- |
| SSL / connection refused        | Wrong host or port, or SSL off | Fix the depot or connection; enable SSL.                              |
| `permission denied for schema`  | Missing grant                  | Apply the schema grants above.                                        |
| Depot not resolvable            | Depot name mismatch            | Align the depot URL in the gateway `address` and `deployment.depots`. |
| `database does not exist`       | Wrong `REDSHIFT_DATABASE`      | Fix the database field on the depot or connection.                    |
| Semantic `column not found`     | Casing mismatch or drift       | Match the semantic model to the physical column casing.               |
| `statement timeout`             | Heavy SQL or low timeout       | Tune the SQL; raise the timeout in session.                           |
| `vulcan plan` runs DDL          | Lifecycle DDL not guarded      | Wrap it in `@IF(@runtime_stage = 'evaluating', ...)`.                 |
| Slow queries after a large load | Missing statistics             | Run `ANALYZE` in `after_all`.                                         |

For the full failure-mode table, health-check queries, and recovery procedures, see [Troubleshooting](https://v2.dataos.info/references/engine-guide/redshift/troubleshooting) in the Redshift engine manual.

Once `vulcan info` succeeds, continue to [Assets](https://v2.dataos.info/build/productize/assets.md).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/build/productize/connect-engine/redshift.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
