The short answer
In many data architectures, analytics queries need to join historical warehouse tables with live operational data sitting in transactional databases like Amazon RDS or Aurora PostgreSQL. Building a dedicated pipeline just to copy small transactional tables introduces lag and maintenance overhead.
Redshift Federated Queries allow your Redshift cluster to query external operational relational databases directly without moving data first.
How Federated Queries work
You create an external schema in Redshift pointing to your transactional PostgreSQL or MySQL instance. Redshift connects to the database using credentials securely retrieved from AWS Secrets Manager.
CREATE EXTERNAL SCHEMA operational_db
FROM POSTGRES
DATABASE 'ecommerce_prod'
URI 'aurora-cluster.cluster-xyz.us-east-1.rds.amazonaws.com'
PORT 5432
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSecretsRole'
SECRET_ARN 'arn:aws:secretsmanager:us-east-1:123456789012:secret:rds_creds';Once configured, analysts and ETL scripts can write standard SQL queries that join warehouse with live operational tables:
SELECT
w.sale_id,
w.amount,
live.current_tier
FROM sales_fact w
JOIN operational_db.customer_profiles live
ON w.customer_id = live.customer_id
WHERE live.is_active = true;Predicate pushdown and performance considerations
Redshift uses to send filtering conditions and column projections directly to the remote database engine. If your query filters on is_active = true, the remote PostgreSQL database filters the rows before transmitting them over the network.
However, federated queries consume CPU, memory, and connection pools on your operational production database. Running heavy unindexed analytical scans through federation can degrade transactional application performance. Use federation for light lookups and incremental extraction batches, not massive table scans.