+1 (726) 227-3497
RougeWarehouse

Setting Up Aurora to Redshift Zero-ETL, Step by Step

A zero-ETL integration replicates tables from Amazon Aurora (MySQL or PostgreSQL) into Amazon Redshift continuously. There is no extract job, no S3 staging and no COPY schedule: inserts, updates and deletes on the source appear in Redshift within seconds to a few minutes. This walkthrough sets one up from an Aurora PostgreSQL cluster to a Redshift Serverless namespace. The Aurora MySQL steps differ only in the parameter names.

Prerequisites

On Aurora PostgreSQL

  • A supported engine version (check the zero-ETL documentation for the current minimum; it moves forward with engine releases).
  • A custom cluster parameter group with logical replication enabled: rds.logical_replication = 1, and aurora.enhanced_logical_replication = 1 with aurora.logical_replication_backup = 0 and aurora.logical_replication_globaldb = 0. Changing these requires a reboot of the writer instance.
  • Each table you want replicated should have a primary key. Tables without one can be replicated but cost more to apply updates, and some DDL on them is not supported.

On Redshift

  • A provisioned RA3 cluster or a Serverless namespace. Zero-ETL does not work with DC2 nodes.
  • enable_case_sensitive_identifier set to true in the parameter group (provisioned) or workgroup configuration (Serverless). Aurora identifiers are case sensitive; Redshift's default is not.
  • A resource policy on the namespace that authorizes the Aurora cluster (or the whole account) as an integration source. The console adds this for you when you create the integration with the "fix it for me" option; the CLI version is below.
aws redshift put-resource-policy \
  --resource-arn arn:aws:redshift-serverless:us-east-1:123456789012:namespace/abcd1234-... \
  --policy '{
    "Version": "2012-10-17",
    "Statement": [{
      "Effect": "Allow",
      "Principal": {"Service": "redshift.amazonaws.com"},
      "Action": ["redshift:AuthorizeInboundIntegration"],
      "Resource": "arn:aws:redshift-serverless:us-east-1:123456789012:namespace/abcd1234-...",
      "Condition": {"StringEquals": {"aws:SourceArn": "arn:aws:rds:us-east-1:123456789012:cluster:orders-db"}}
    }]
  }'

If the source and target are in different accounts, the target account also needs a policy allowing redshift:CreateInboundIntegration for the source account.

Create the integration

aws rds create-integration \
  --integration-name orders-to-redshift \
  --source-arn arn:aws:rds:us-east-1:123456789012:cluster:orders-db \
  --target-arn arn:aws:redshift-serverless:us-east-1:123456789012:namespace/abcd1234-... \
  --data-filter 'include: orders.public.orders, include: orders.public.customers, include: orders.public.order_items'

Two things to notice:

  • --data-filter limits replication to named tables. The pattern is database.schema.table for PostgreSQL, database.table for MySQL, with wildcards allowed (include: orders.public.*, exclude: orders.public.audit_*). Replicating everything is the default and it is rarely what you want: application scratch tables and audit logs take storage in Redshift and keep the replication busy.
  • The integration takes several minutes to become active. The initial load is a full snapshot of the filtered tables, so the first sync time depends on their size.
aws rds describe-integrations --integration-identifier orders-to-redshift \
  --query 'Integrations[0].Status'

Create the destination database in Redshift

An active integration does not yet have a database on the Redshift side. Find the integration ID and create one from it:

SELECT integration_id, source_type, state
FROM SVV_INTEGRATION;

CREATE DATABASE orders_replica
FROM INTEGRATION 'a1b2c3d4-5678-90ab-cdef-1234567890ab'
DATABASE "orders";

For Aurora PostgreSQL you must name the source database (the last clause). The replicated tables appear under a schema matching the source schema. The database is read-only; you query it directly or, more usually, build your models on top of it:

SELECT c.region, DATE_TRUNC('day', o.created_at) AS day,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders_replica.public.orders o
JOIN orders_replica.public.order_items oi ON oi.order_id = o.id
JOIN orders_replica.public.customers c ON c.id = o.customer_id
WHERE o.created_at >= DATEADD(day, -30, GETDATE())
GROUP BY 1, 2
ORDER BY 1, 2;

Cross-database queries like this work from any database in the namespace, so your existing dev database and dbt project can reference the replica without moving anything.

Monitor replication lag and errors

-- Integration-level state and last-known lag
SELECT integration_id, state, current_lag, last_replicated_checkpoint
FROM SVV_INTEGRATION;

-- Per-table state: which tables are synced, failed or filtered
SELECT integration_id, schema_name, table_name, table_state, table_failure_message
FROM SVV_INTEGRATION_TABLE_STATE
WHERE table_state <> 'Synced';

In CloudWatch, the IntegrationLag metric (in the AWS/Redshift namespace, dimensioned by integration) is the one to alarm on. Set a threshold the business will accept, for example five minutes, and route it to the team that owns the source database, because most lag comes from long transactions or DDL on the Aurora side.

Things that pause replication on a table: unsupported DDL (some ALTER TABLE variants), unsupported column types, and dropping the primary key. The table goes to a Failed state with a message; fix the source and the integration resyncs that table.

Cost line items to watch

Zero-ETL itself has no separate price. What you pay for is:

  1. Redshift Managed Storage for the replicated data. Replicas count in full.
  2. Compute on the target. On a provisioned cluster the apply work shares the nodes. On Serverless, applying changes consumes RPU time, and a busy integration can keep a workgroup from scaling to zero. Check SYS_SERVERLESS_USAGE in the first week.
  3. Aurora side. Enhanced logical replication adds some write amplification and I/O on the writer; for Aurora I/O-Optimized clusters this is usually invisible, for standard clusters watch the I/O line.
  4. Change data volume. A table that updates every row once an hour replicates all of those updates. Filter such tables out, or replicate a view-friendly summary via a different path.

Wrapping up

You now have an Aurora database mirrored in Redshift with nothing scheduled and nothing to restart at 3 a.m. The next steps are modeling on top of the replica (dbt sources pointing at orders_replica), and reviewing the filter list every quarter as the application adds tables.

If you are replacing a DMS pipeline or a nightly dump-and-COPY process with zero-ETL, our Zero-ETL & Real-Time Ingestion service covers the cutover and the cost review.