Redshift

This guide covers how to configure Amazon Redshift as a warehouse in Zeotap, including connection setup, authentication, required permissions, and the capability limits that are specific to Redshift. Both provisioned Redshift clusters and Redshift Serverless workgroups are supported.

Redshift is derived from PostgreSQL but is not PostgreSQL — several features you may expect from a Postgres-compatible database are absent. The Redshift-Specific Notes section lists every capability that behaves differently from the other supported warehouses. Read it before you connect, not after.

Prerequisites

• An Amazon Redshift provisioned cluster or Redshift Serverless workgroup
• The cluster or workgroup reachable from Zeotap — Redshift lives inside a VPC and is private by default, so this needs deliberate setup. See Network Configuration.
• A Redshift database user with a password (password authentication is the only supported method)
• A schema containing the customer data you want to model
• Permission for that user to create schemas in the database, or pre-created operational schemas (see Required Permissions)

Connection Configuration

Required Fields

Host

Use the endpoint hostname on its own, without the trailing :5439/dbname that the AWS console displays:

# Provisioned cluster
my-cluster.abc123.eu-west-2.redshift.amazonaws.com

# Redshift Serverless workgroup
my-workgroup.123456789012.eu-west-2.redshift-serverless.amazonaws.com

Database and Schema

Redshift nominally has three levels — database.schema.table — but cross-database references are read-only, restricted to RA3 node types, and rejected for the writes Zeotap performs. Zeotap therefore treats Redshift as two-level: everything happens inside the one database you configure, and tables are referenced as schema.table.

SELECT * FROM analytics.customers

Identifier casing

Redshift folds every identifier to lower case, including quoted ones. "Customers", CUSTOMERS and customers all resolve to customers. This is the opposite of Snowflake (which folds to upper case and preserves quoted names) and of ClickHouse (which is case-sensitive).

Zeotap normalises identifiers to lower case on Redshift and quotes them with double quotes ("analytics"."customers"). When you write SQL by hand, use lower-case names — a table created as MyTable is stored and must be referenced as mytable.

Authentication

Password

Zeotap authenticates with a database username and password over a TLS-encrypted connection. Leave SSL Mode at require for any cluster reachable over the public internet.

Password authentication is the only supported method in this release. IAM database authentication, temporary cluster credentials, and federated identity are not supported.

To create a dedicated user:

CREATE USER zeotap_cdp PASSWORD 'a-Strong-Password1';

Redshift enforces a password policy: 8–64 characters, with at least one upper-case letter, one lower-case letter, and one digit.

Required Permissions

Grant the Zeotap user read access to your source schema, plus the ability to create and manage the operational schemas Zeotap uses for its own state (cdp_planner, cdp_audit, cdp_journey, cdp_identity, cdp_raw, and audit_logs) — plus cdp_prep, but only if Data Prep is enabled for the workspace, and cdp_metadata, but only if the metadata export is enabled for the workspace (see below).

The simplest setup lets Zeotap create those schemas itself:

-- Read access to your source schema
GRANT USAGE ON SCHEMA analytics TO zeotap_cdp;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO zeotap_cdp;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO zeotap_cdp;
 
-- Allow Zeotap to create its own operational schemas
GRANT CREATE ON DATABASE dev TO zeotap_cdp;
 
-- Introspection (usually already granted to PUBLIC)
GRANT USAGE ON SCHEMA information_schema TO zeotap_cdp;

If your policy forbids CREATE ON DATABASE, pre-create each operational schema and grant on it instead:

CREATE SCHEMA IF NOT EXISTS cdp_planner;
CREATE SCHEMA IF NOT EXISTS cdp_audit;
CREATE SCHEMA IF NOT EXISTS cdp_journey;
CREATE SCHEMA IF NOT EXISTS cdp_identity;
CREATE SCHEMA IF NOT EXISTS cdp_raw;
CREATE SCHEMA IF NOT EXISTS audit_logs;
 
GRANT USAGE, CREATE ON SCHEMA cdp_planner  TO zeotap_cdp;
GRANT USAGE, CREATE ON SCHEMA cdp_audit    TO zeotap_cdp;
GRANT USAGE, CREATE ON SCHEMA cdp_journey  TO zeotap_cdp;
GRANT USAGE, CREATE ON SCHEMA cdp_identity TO zeotap_cdp;
GRANT USAGE, CREATE ON SCHEMA cdp_raw      TO zeotap_cdp;
GRANT USAGE, CREATE ON SCHEMA audit_logs   TO zeotap_cdp;

• cdp_planner — plan tables used for incremental sync change detection
• cdp_audit — sync run and audit history
• cdp_journey — journey member state and event log tables
• cdp_identity — identity resolution output (default schema; configurable per identity graph)
• cdp_raw — landing schema for loader (inbound ELT) runs (default schema; configurable per loader)
• audit_logs — observability events and event delivery logs

Zeotap owns every table it creates in these schemas, so no extra DROP grant is needed — in Redshift only the owner (or a superuser) can drop a table.

cdp_prep — only if Data Prep is enabled for the workspace

Data Prep is a per-workspace entitlement, off by default. A workspace that has it builds prepared tables into one more schema, cdp_prep, and its connection test then carries a seventh write probe (write_prep). Until Data Prep is enabled nothing creates or reads this schema, so there is nothing to grant.

CREATE SCHEMA IF NOT EXISTS cdp_prep;
GRANT USAGE, CREATE ON SCHEMA cdp_prep TO zeotap_cdp;

USAGE, CREATE is the same pair the six schemas above take, and it is enough for the same reason: Zeotap owns every table it creates here, so INSERT, UPDATE, DELETE and DROP on a prepared table come with ownership rather than needing their own grants. An incremental build uses all four — it appends a window, merges upserts on the unique key, deletes what its delete_when predicate matches, and drops the transient <slug>__next / <slug>__delta siblings once the swap is done.

Revoking the entitlement does not drop the schema or its tables, so this grant can be withdrawn at your convenience rather than urgently.

cdp_metadata — only if the metadata export is enabled for the workspace

The metadata export is a per-workspace entitlement, off by default, that keeps the workspace’s audience catalogue as the table cdp_metadata.audiences. When it is enabled for a workspace, the connection test of the source chosen to hold the table carries one more write probe (write_metadata); the workspace’s other sources are tested exactly as before. Until then nothing creates or reads this schema, so there is nothing to grant.

CREATE SCHEMA IF NOT EXISTS cdp_metadata;
GRANT USAGE, CREATE ON SCHEMA cdp_metadata TO zeotap_cdp;

USAGE, CREATE is enough for the same reason as above: Zeotap owns the tables it creates here. Each export creates audiences__next, inserts into it, and in one transaction drops audiences and renames audiences__next into its place. Because the table is recreated, SELECT granted on it directly is lost at the next export — give readers USAGE on the schema and use ALTER DEFAULT PRIVILEGES FOR USER zeotap_cdp IN SCHEMA cdp_metadata GRANT SELECT ON TABLES TO <reader>.

Revoking the entitlement does not drop the schema or its table.

The connection test probes each schema the same way the runtime does — create the schema if missing, then create and drop a small test table — and reports the exact GRANT or CREATE SCHEMA statement to run if a step fails.

Data Types

Redshift has no ARRAY, STRUCT, JSON, or UUID type. Where the other warehouses expose a native array or object column, Redshift exposes text.

When Zeotap creates tables in Redshift (operational tables, event tables, loader landing tables), it maps its logical types the other way: integers become BIGINT, floats DOUBLE PRECISION, booleans BOOLEAN, timestamps TIMESTAMP, dates DATE, and strings, JSON values and string arrays all become VARCHAR with an explicit width. The explicit width matters: a bare VARCHAR or a TEXT column in Redshift silently becomes VARCHAR(256) and truncates anything longer.

Redshift-Specific Notes

These are the capability differences between Redshift and the other supported warehouses. Everything listed here is a limit of Redshift itself, and each one is enforced up front — where a capability is absent, Zeotap hides it rather than letting it fail later.

• Network reachability is real onboarding work. Redshift clusters are private by default, inside a VPC. Snowflake and BigQuery are internet-reachable endpoints you can connect to with credentials alone; Redshift is not. You must either make the cluster publicly accessible and allowlist Zeotap’s egress IPs in its security group, or set up AWS PrivateLink. Budget time for this — it usually needs someone with AWS network permissions. See Network Configuration.

• No native change data capture. Redshift has no equivalent of Snowflake Streams or Delta Lake Change Data Feed. Incremental syncs work by diffing plan tables between runs, the same mechanism used for BigQuery and ClickHouse. Syncs behave identically — added, changed and removed members are all detected — but the change detection is computed by Zeotap rather than read from the warehouse.

• No custom-code transforms. JavaScript and Python custom-code field transforms are not available on Redshift sources. Redshift has never supported JavaScript UDFs, and AWS is removing Python UDF support after 30 June 2026. Use SQL-based transforms instead; attempts to configure a JS or Python transform are rejected with a clear message.

• No nested-data filters. Because Redshift has no ARRAY or STRUCT type, filters on nested fields and array elements are not available on Redshift sources in this release. SUPER columns are readable as JSON text and can be manipulated in your own model SQL, but the visual filter builder does not offer nested-field or array-element conditions for them. Flatten nested data into typed columns in a model if you need to filter on it.

• No staged loading. When Zeotap loads data into Redshift (loaders, event forwarding), records are written with batched INSERT statements rather than a bulk COPY from cloud storage. Redshift’s COPY command can only read from Amazon S3, and Zeotap stages to Google Cloud Storage, so the staged-file fast path used for Snowflake, BigQuery and Databricks does not apply. Large loader runs are correspondingly slower — correct, but not as fast. S3-based staged loading is planned for a future release.

• VARCHAR caps at 65,535 bytes. BigQuery and Snowflake allow string values up to 16 MB. If you load documents, long JSON blobs or raw payloads that exceed 64 KB, they will not fit in a Redshift column.

• No query cost estimates, and no pre-execution validation. Redshift’s EXPLAIN refuses DDL and MERGE statements and provides no bytes-scanned estimate, so Zeotap cannot show an estimated query cost before you run a query, and validation happens by executing the query with a zero-row limit rather than by planning it.

• No QUALIFY. If you write model SQL by hand, note that Redshift does not support the QUALIFY clause. Use a ROW_NUMBER() subquery with an outer WHERE instead.

• CONCAT takes exactly two arguments. CONCAT(a, b, c) fails on Redshift. Nest the calls, or use the || operator.

Example Configuration

curl -X POST https://agentic.zeotap.com/api/v1/sources \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Production Redshift",
    "type": "redshift",
    "config": {
      "host": "my-cluster.abc123.eu-west-2.redshift.amazonaws.com",
      "port": 5439,
      "database": "dev",
      "schema": "public",
      "ssl_mode": "require",
      "auth_method": "password"
    },
    "credentials": {
      "username": "zeotap_cdp",
      "password": "a-Strong-Password1"
    }
  }'

Network Configuration

Redshift is deployed inside an Amazon VPC and, by default, accepts no connections from outside it. Unlike Snowflake and BigQuery — where allowlisting is optional hardening — making Redshift reachable is a required setup step.

All Zeotap connections to your warehouse originate from these static egress IPs:

These addresses are stable — Zeotap does not rotate them. If the list ever changes, this page is updated first.

Option 1 — Public endpoint with an IP allowlist

The quickest route, and the one most teams use to get started:

1. In the Amazon Redshift console, open your cluster (or Serverless workgroup) and edit its network settings
2. Enable Publicly accessible
3. Open the VPC security group attached to the cluster
4. Add an inbound rule: type Custom TCP, port 5439, source 34.76.7.172/32
5. Add a second inbound rule for 34.22.225.249/32
6. Confirm the cluster sits in a subnet with a route to an internet gateway

Restrict the rules to those two /32 addresses. Do not open port 5439 to 0.0.0.0/0.

Option 2 — AWS PrivateLink

If your security policy forbids a public endpoint, Zeotap can connect over AWS PrivateLink so traffic never traverses the public internet. This requires coordination with the Zeotap team to establish the endpoint service — contact support to start the process.

Troubleshooting

Next Steps

• Create a model using your Redshift warehouse
• Use the SQL editor to write Redshift SQL queries
• Set up a Reverse ETL sync to activate your data