What is an Amazon Redshift federated query to RDS or Aurora, and when should you use one?

## What it is Amazon Redshift **federated query** lets a warehouse query read directly from a live operational database — Amazon RDS or Aurora, PostgreSQL or MySQL flavour — without any ETL hop. Like Spectrum, it is exposed as an external schema, but the source is a running database rather than files in S3: ```sql CREATE EXTERNAL SCHEMA ops FROM POSTGRES DATABASE 'appdb' SCHEMA 'public' URI 'prod-app.abcdefg.eu-west-1.rds.amazonaws.com' IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftFederatedRole' SECRET_ARN 'arn:aws:secretsmanager:eu-west-1:123456789012:secret:appdb-reader'; ``` After that, `ops.customers` is queryable and joinable with `dim_customer`, `fact_orders` or a Spectrum external table in the same statement. ## The moving parts - **Credentials** live in AWS Secrets Manager; the cluster's IAM role must be allowed to read that secret. You never put a password in DDL. - **Connectivity** is network-level: the cluster must reach the RDS/Aurora endpoint, which means VPC routing and security-group rules that admit the cluster. - **Access is read-only.** Federated query does not write to the source. - **A dedicated read-only database user** on the source is the right credential to store — this is a production OLTP system and the warehouse should not be able to alter it. ## What execution actually looks like Redshift pushes down what the remote engine can evaluate cheaply — predicates, and some aggregation and projection — so a filtered lookup often becomes a small indexed query on the source. What it cannot push down it must materialise: rows stream across the network into the cluster, and the rest of the plan runs there. That asymmetry is the whole story. `SELECT * FROM ops.orders WHERE order_id = 12345` is a point lookup on an indexed column and costs the source almost nothing. `SELECT count(*) FROM ops.orders o JOIN fact_shipments f ON ...` over three years of orders is a full scan of a production table that also competes with live user traffic for its buffer cache — and the OLTP database is row-stored, so it is exactly the wrong engine for that scan. ## When it is the right tool - **Freshness beats volume.** A small dimension that must reflect the last few seconds — entitlement state, current subscription tier, an operational status flag. - **Avoiding a pipeline for a small table.** Building and monitoring a CDC pipeline for a 20,000-row reference table is not worth it. - **Reconciliation.** Comparing warehouse aggregates against the source of truth to prove a load was complete. - **Late-binding lookups** in an otherwise warehouse-native query, where the joined set is already narrowed to a handful of keys. ## When it is the wrong tool - Scanning or aggregating a large transactional table. Use CDC or a scheduled extract into Redshift instead. - Anything a business dashboard hits repeatedly — every refresh is load on production. - Building a permanent "virtual warehouse" over the OLTP schema. Federated query is a bridge, not an architecture. ## The production pattern The safe shape in a scheduled job is to pull once and reuse: ```sql CREATE TEMP TABLE recent_customers AS SELECT customer_id, tier, updated_at FROM ops.customers WHERE updated_at >= dateadd(day, -1, current_date); -- every subsequent join reads the local copy SELECT ... FROM fact_orders f JOIN recent_customers c ON c.customer_id = f.customer_id; ``` One bounded, filtered read against production; everything else local. This also makes the query deterministic: without it, two references to a federated table in one statement can see the source at different moments. ## Contrast with Spectrum Both are external schemas, and that similarity misleads people. Spectrum reads immutable files from S3 with a managed scan fleet and a per-byte-scanned charge; its scaling limit is S3 and file layout. Federated query reads a live, row-stored transactional database over a network connection; its scaling limit is **the production database's capacity**, and the cost of getting it wrong is paid by your users, not by your AWS bill.