Federated Queries in Amazon Redshift: Live Joins to Aurora PostgreSQL and MySQL

Zero-ETL and streaming ingestion get most of the attention, but a large share of real Redshift work still comes down to a simpler question: can I join the warehouse to the live operational database right now, without building a pipeline? Federated query is the answer to that question. It lets Amazon Redshift read directly from Amazon RDS / Aurora PostgreSQL and MySQL at query time, so a fact table in Redshift can be joined against rows that were written to the OLTP database seconds ago.

This tutorial covers the setup end to end, what actually gets pushed down to the remote engine, the failure modes we see most often on client engagements, and how to decide between federated query, zero-ETL and Spectrum.

When federated query is the right tool

Use federated query when:

• You need transactionally current operational data — order status, entitlement flags, a config table — joined to warehouse history.
• The remote table is small to moderate (dimensions, lookups, recent-activity slices), or you can push a selective predicate down to it.
• You want to avoid standing up a pipeline for a table that changes shape often, or that is only needed by one report.
• You are doing an incremental ELT and want to read a source's high-water mark without a landing zone.

Use something else when:

• You need the whole 400 GB orders table every hour → zero-ETL integration or CDC into Redshift.
• The data already lives in S3 as Parquet/Iceberg → Redshift Spectrum / external tables.
• Dozens of concurrent dashboard users would each hammer the OLTP box → replicate first; federated query does not cache.

A useful rule of thumb: federated query is for freshness on narrow slices, zero-ETL is for volume.

Prerequisites

• An RA3 provisioned cluster or Redshift Serverless workgroup. DC2 is not supported.
• The RDS/Aurora instance and the Redshift namespace must be network reachable: same VPC, or peered/Transit Gateway connected, with the database's security group allowing inbound 5432 (Postgres) / 3306 (MySQL) from the Redshift security group or its VPC CIDR. Redshift must be VPC-attached (an enhanced VPC routing or Serverless workgroup subnet in the same VPC is the simplest arrangement).
• Credentials for the remote database stored in AWS Secrets Manager.
• An IAM role attached to Redshift that can read that secret.

Point federated query at a read replica, not the primary, whenever the source is a production OLTP system. This is the single most valuable piece of advice in this article.

Step 1 — Store the remote credentials in Secrets Manager

aws secretsmanager create-secret \
  --name prod/aurora-pg/redshift-reader \
  --secret-string '{"username":"redshift_reader","password":"REPLACE_ME"}'

On the remote database, create that user with the narrowest possible grants:

-- On Aurora PostgreSQL
CREATE USER redshift_reader WITH PASSWORD 'REPLACE_ME';
GRANT USAGE ON SCHEMA public TO redshift_reader;
GRANT SELECT ON public.customers, public.subscriptions TO redshift_reader;

Step 2 — Give Redshift an IAM role that can read the secret

Attach a role to the cluster/workgroup with a policy like:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["secretsmanager:GetSecretValue"],
      "Resource": "arn:aws:secretsmanager:us-east-1:111111111111:secret:prod/aurora-pg/redshift-reader-*"
    }
  ]
}

If the secret is encrypted with a customer-managed KMS key, add kms:Decrypt on that key.

Step 3 — Create the external schema

For Aurora / RDS PostgreSQL:

CREATE EXTERNAL SCHEMA ops_pg
FROM POSTGRES
DATABASE 'appdb'
SCHEMA 'public'
URI 'prod-aurora-ro.cluster-ro-abc123.us-east-1.rds.amazonaws.com'
PORT 5432
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftFederatedQuery'
SECRET_ARN 'arn:aws:secretsmanager:us-east-1:111111111111:secret:prod/aurora-pg/redshift-reader-AbCdEf';

For Aurora / RDS MySQL, the only differences are the keyword and that MySQL has no schema layer:

CREATE EXTERNAL SCHEMA ops_mysql
FROM MYSQL
DATABASE 'appdb'
URI 'prod-aurora-mysql-ro.cluster-ro-abc123.us-east-1.rds.amazonaws.com'
PORT 3306
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftFederatedQuery'
SECRET_ARN 'arn:aws:secretsmanager:us-east-1:111111111111:secret:prod/aurora-mysql/redshift-reader-AbCdEf';

Grant it out like any other schema:

GRANT USAGE ON SCHEMA ops_pg TO ROLE analyst;

Inspect what Redshift can see:

SELECT * FROM SVV_EXTERNAL_SCHEMAS WHERE schemaname = 'ops_pg';
SELECT * FROM SVV_EXTERNAL_TABLES WHERE schemaname = 'ops_pg';
SELECT * FROM SVV_EXTERNAL_COLUMNS WHERE schemaname = 'ops_pg' AND tablename = 'subscriptions';

There is no CREATE EXTERNAL TABLE step — the remote catalog is read live, so a column added upstream shows up immediately (which is a blessing and, for downstream views, occasionally a curse).

Step 4 — Query it

-- Warehouse history joined to live subscription state
SELECT s.plan_code,
       s.status,
       COUNT(DISTINCT f.customer_id)   AS customers,
       SUM(f.net_revenue)              AS revenue_90d
FROM   analytics.fact_orders f
JOIN   ops_pg.subscriptions  s ON s.customer_id = f.customer_id
WHERE  f.order_date >= CURRENT_DATE - 90
  AND  s.updated_at >= CURRENT_DATE - 7      -- pushed down to Aurora
GROUP  BY 1, 2
ORDER  BY revenue_90d DESC;

Writes are not supported: federated tables are read-only. INSERT, UPDATE, DELETE and DDL against ops_pg all fail. You can read from a federated table inside INSERT INTO ... SELECT against a Redshift table, which is how most people use it in ELT.

What gets pushed down (and what doesn't)

Redshift rewrites part of the query into a remote SQL statement. Reliably pushed down:

• Column projection (only referenced columns are fetched)
• Single-table WHERE predicates on remote columns, including IN lists and simple functions
• LIMIT in many single-table cases
• Aggregations in simple single-remote-table cases

Generally not pushed down:

• Join conditions between a Redshift table and a remote table — Redshift pulls the remote rows and joins locally
• Predicates whose values depend on a Redshift-side join or subquery
• Redshift-specific functions with no remote equivalent

The practical consequence: ops_pg.big_table joined to a Redshift fact table with no selective remote predicate will stream the whole remote table over the network on every execution. Always give the federated side its own WHERE clause.

Verify rather than assume:

EXPLAIN
SELECT * FROM ops_pg.subscriptions WHERE updated_at > CURRENT_DATE - 7;

Look for the Remote PG Seq Scan / Remote MySQL Seq Scan step and check that your filter appears in its Filter: line. If the filter is applied in a step above the remote scan, it is not being pushed down.

Pattern: materialize once, join many times

When a report joins the same remote slice several times, pull it once into a temp table so the remote database is hit exactly once and the optimizer gets real statistics:

CREATE TEMP TABLE live_subs
DISTKEY(customer_id) SORTKEY(customer_id) AS
SELECT customer_id, plan_code, status, updated_at
FROM   ops_pg.subscriptions
WHERE  status IN ('active','past_due');

ANALYZE live_subs;

For scheduled refreshes rather than ad-hoc reporting, the same idea in durable form:

CREATE TABLE staging.subs_snapshot AS SELECT ... FROM ops_pg.subscriptions WHERE ...;

Note that materialized views cannot be defined over federated tables — that is a common design assumption that fails at deploy time. Use a scheduled CREATE TABLE AS / MERGE instead.

Monitoring and cost

Federated query has no separate price tag: you pay Redshift compute plus any cross-AZ data transfer, and you pay the real cost in load on the source database.

-- Per-query federated activity
SELECT query, external_query_text, source_type, rows, bytes, elapsed_time
FROM   SVL_FEDERATED_QUERY
ORDER  BY starttime DESC
LIMIT  50;

Watch the rows column over time. A federated query whose row count grows linearly with the source table is a pipeline waiting to be built.

Failure modes we hit repeatedly

Symptom	Usual cause
Timeout on remote connection when creating the schema	Security group or route table — Redshift subnets cannot reach the DB port
Access denied to secret	IAM role not attached to the cluster/workgroup, or missing kms:Decrypt
Query works then intermittently fails	Remote replica failover, or the remote database killing long-running read transactions
Suddenly slow after weeks	Remote table grew, or a predicate stopped being pushed down after a query rewrite
Unsupported data type	Remote types with no Redshift equivalent (arrays, json, ranges, enum) — cast them in a remote view
Downstream view breaks	Upstream ALTER TABLE changed a column type; there is no schema pinning

For the unsupported-type case, the cleanest fix is a view on the remote side that exposes only stable, castable columns, and to point Redshift at that instead of the base table.

Federated query vs zero-ETL vs Spectrum, in one table

	Federated query	Zero-ETL	Spectrum / Iceberg
Freshness	Seconds (live read)	Seconds to minutes	Whatever the lake write cadence is
Data volume	Small slices	Whole tables, large	Very large
Load on OLTP	Yes, on every query	Replication only	None
Concurrency friendly	No	Yes	Yes
Setup effort	Low	Low–medium	Medium

Most mature architectures use all three: zero-ETL or CDC for the high-volume operational tables, Spectrum/Iceberg for historical and semi-structured data, and federated query for the handful of small, must-be-current lookups that would be silly to replicate.

Checklist before you ship a federated query

1. It points at a read replica, not the primary.
2. The remote user has SELECT on named tables only.
3. EXPLAIN confirms the selective predicate is inside the remote scan.
4. Row volume per execution is bounded and known.
5. Repeated use is materialized into a temp or staging table.
6. Unsupported types are handled by a remote view, not by hope.
7. Someone owns the alert for when the source schema changes.

---

Need help deciding which of your source tables belong in a federated query, a zero-ETL integration or a CDC pipeline — or want an existing Redshift architecture reviewed before it hits the next volume ceiling? Our senior Redshift consultants do exactly this work, on direct engagements and as subcontractors. Get in touch.