AWS RDS/Aurora → Datastream → BigQuery — Full A‑Z Implementation Guide
On this page
- 1. Prerequisites & decisions
- Phase 1 — Prepare the AWS source database
- Phase 2 — Network connectivity (AWS ↔ GCP)
- Phase 3 — Google Cloud project setup
- Phase 4 — Create Datastream connection profiles
- Phase 5 — Create & start the stream
- Phase 6 — Backfill + ongoing CDC
- Phase 7 — Validation
- Phase 8 — Cost control & optimization
- Phase 9 — Monitoring & alerting
- Phase 10 — Security hardening
- Troubleshooting
- Go‑live checklist
Goal: Continuously replicate an AWS RDS/Aurora database into Google BigQuery in near‑real‑time using Google Datastream (serverless CDC), with native upserts/deletes — the cheapest, lowest‑ops option.
Architecture at a glance
AWS RDS / Aurora ──CDC (binlog/WAL)──▶ Datastream (GCP, serverless) ──native upsert──▶ BigQuery
│ │ │
source DB reads change log, no agents live replicated tables
- Latency: seconds → ~2 min
- You manage: the source DB config + networking + one stream. No Kafka, no servers.
- Cost shape: ~$0.018/GB of change data + AWS egress (~$0.09/GB) + BigQuery storage/compute. No fixed minimum.
1. Prerequisites & decisions
Confirm these before you start:
| Item | Your value | Notes |
|---|---|---|
| Source engine | MySQL or PostgreSQL | Aurora MySQL/Postgres or RDS MySQL/Postgres all supported |
| Engine version | — | MySQL 5.7+/8.0; PostgreSQL 10+ (logical replication) |
| DB size & daily change volume | — | Drives backfill time + cost |
| Latency target | seconds vs 1–5 min | Affects BigQuery write mode/cost |
| Connectivity method | IP allowlist / VPN / Private | See Phase 2 |
| Tables to replicate | — | Replicate only what you need (saves egress) |
Accounts/access you need:
- AWS: ability to modify the RDS parameter group, create a DB user, and edit security groups.
- GCP: a project with Owner/Editor or the specific Datastream + BigQuery IAM roles.
- A machine with
mysql/psqlclient and thegcloudCLI installed.
Supported & key facts:
- Datastream → BigQuery supports MySQL, PostgreSQL, Oracle, SQL Server as sources.
- It writes to BigQuery using the Storage Write API and performs MERGE automatically (upsert mode) so the BigQuery table mirrors the source — or append‑only mode if you want full change history.
- Every replicated table must have a primary key for upsert mode. Tables without one go append‑only.
Phase 1 — Prepare the AWS source database
The source must expose its transaction log and have a least‑privilege replication user.
1A. If your source is MySQL (RDS or Aurora MySQL)
Step 1 — Enable binary logging (ROW format) via a custom parameter group.
RDS/Aurora won’t let you edit the default parameter group, so create a custom one and attach it.
# Create a custom DB parameter group (RDS MySQL example)
aws rds create-db-parameter-group \
--db-parameter-group-name datastream-mysql \
--db-parameter-group-family mysql8.0 \
--description "Datastream CDC params"
# Set binlog to ROW format + full image
aws rds modify-db-parameter-group \
--db-parameter-group-name datastream-mysql \
--parameters \
"ParameterName=binlog_format,ParameterValue=ROW,ApplyMethod=pending-reboot" \
"ParameterName=binlog_row_image,ParameterValue=full,ApplyMethod=pending-reboot"
Aurora MySQL: binlog is controlled by the cluster parameter group + the
binlog_formatsetting in the cluster; useaws rds modify-db-cluster-parameter-groupand setbinlog_format=ROW.
Step 2 — Attach the parameter group and set binlog retention.
aws rds modify-db-instance \
--db-instance-identifier YOUR_DB_INSTANCE \
--db-parameter-group-name datastream-mysql \
--apply-immediately
# Reboot to apply pending-reboot params
aws rds reboot-db-instance --db-instance-identifier YOUR_DB_INSTANCE
Set binlog retention so Datastream has time to read logs (run inside the DB):
-- Keep binlogs for 7 days (168 hours). RDS-specific procedure:
CALL mysql.rds_set_configuration('binlog retention hours', 168);
Step 3 — Create the least‑privilege replication user.
CREATE USER 'datastream'@'%' IDENTIFIED BY 'STRONG_PASSWORD_HERE';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT
ON *.* TO 'datastream'@'%';
FLUSH PRIVILEGES;
1B. If your source is PostgreSQL (RDS or Aurora PostgreSQL)
Step 1 — Enable logical replication via a custom parameter group.
aws rds create-db-parameter-group \
--db-parameter-group-name datastream-pg \
--db-parameter-group-family postgres15 \
--description "Datastream CDC params"
aws rds modify-db-parameter-group \
--db-parameter-group-name datastream-pg \
--parameters \
"ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot"
Attach + reboot:
aws rds modify-db-instance \
--db-instance-identifier YOUR_DB_INSTANCE \
--db-parameter-group-name datastream-pg --apply-immediately
aws rds reboot-db-instance --db-instance-identifier YOUR_DB_INSTANCE
Aurora PostgreSQL: set
rds.logical_replication=1on the cluster parameter group.
Step 2 — Create publication, replication slot, and user (run inside the DB):
-- 1. Replication user — NO "WITH REPLICATION" on RDS/Aurora (see warning below)
CREATE USER datastream WITH LOGIN PASSWORD 'STRONG_PASSWORD_HERE';
-- 2. RDS/Aurora ONLY — this is what actually grants replication capability.
-- Run as the RDS master user. Skipping it fails Datastream validation.
GRANT rds_replication TO datastream;
-- 3. Grant read on the schema/tables you'll replicate
GRANT USAGE ON SCHEMA public TO datastream;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO datastream;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO datastream;
-- 4. Publication = the set of tables Datastream will capture
CREATE PUBLICATION datastream_pub FOR ALL TABLES;
-- (or: FOR TABLE public.orders, public.customers; -- to limit scope)
-- 5. Logical replication slot (Datastream reads from this).
-- Name rules: lowercase/digits/underscores only — NO hyphens — max 63 chars,
-- and UNIQUE ACROSS THE WHOLE INSTANCE. Name it after the DB, e.g.:
SELECT pg_create_logical_replication_slot('datastream_slot_erp', 'pgoutput');
These two names are what you type into the stream’s Configure source screen. The publication is the table allow‑list (what gets sent); the slot is the read‑position bookmark (how far you’ve read). Different objects, both required — Datastream creates neither, it only references them.
⚠️ Never use
WITH REPLICATIONon RDS/Aurora. It fails outright — Postgres only lets a role grant the REPLICATION attribute if it holds that attribute itself, and on managed RDS no role does, not even the master user. You’ll getpermission denied to create role / Only roles with the REPLICATION attribute may create roles with the REPLICATION attribute(PG16+; PG≤15 words itmust be superuser to create replication users). Create the user plain and useGRANT rds_replicationinstead — that membership is the capability on RDS. Note thatrolreplicationthen staysf, which is normal; verify membership viapg_auth_members, not the flag.
⚠️ Watch the replication slot. If the stream is paused/broken for a long time, the slot retains WAL and can fill the disk. Monitor
pg_replication_slots. Drop unused slots.
Phase 2 — Network connectivity (AWS ↔ GCP)
Datastream (in GCP) must reach your RDS endpoint (in AWS). Three options, cheapest/simplest first:
Option A — IP allowlisting (simplest, good for a start)
- In GCP, note the Datastream public IP ranges for your region (shown when you create the connection profile).
- In AWS, edit the RDS security group to allow inbound on the DB port (3306/5432) from those Datastream IPs only.
- Ensure the RDS instance is reachable (publicly accessible, or via the path you choose). Prefer Option B/C over making the DB public.
Option B — Forward‑SSH tunnel (no public DB)
- Stand up a small bastion host (EC2) in AWS with a public IP.
- Datastream connects via SSH to the bastion, which forwards to the private RDS endpoint.
- Configure the SSH host/user/key in the Datastream source connection profile.
Option C — Private connectivity (most secure, production)
- Create a Datastream private connectivity configuration (a VPC peering in GCP).
- Establish VPN or interconnect between the GCP VPC and your AWS VPC.
- The DB never touches the public internet. Recommended for production / compliance.
Cost note: any of these incurs AWS egress (~$0.09/GB) for change data leaving AWS. A dedicated VPN/interconnect can lower per‑GB egress at high volume.
Always enable TLS/SSL on the DB connection regardless of option.
Phase 3 — Google Cloud project setup
# Set your project
gcloud config set project YOUR_GCP_PROJECT_ID
# Enable the required APIs
gcloud services enable \
datastream.googleapis.com \
bigquery.googleapis.com
# Create the destination BigQuery dataset (choose a region near your AWS region to cut latency/egress)
bq --location=US mk --dataset \
--description "Datastream replica of AWS RDS" \
YOUR_GCP_PROJECT_ID:rds_replica
IAM — grant the Datastream service account access to BigQuery. Datastream uses a Google‑managed service agent; ensure it has roles/bigquery.dataEditor on the dataset (the console wires this automatically when you pick a BigQuery destination; for manual setups grant it explicitly).
Phase 4 — Create Datastream connection profiles
You need two profiles: one for the source (AWS DB) and one for the destination (BigQuery).
4A. Source connection profile (example: MySQL)
gcloud datastream connection-profiles create aws-rds-source \
--location=us-central1 \
--type=mysql \
--mysql-hostname=YOUR_RDS_ENDPOINT.amazonaws.com \
--mysql-port=3306 \
--mysql-username=datastream \
--mysql-password=STRONG_PASSWORD_HERE \
--display-name="AWS RDS MySQL source"
# add --static-ip-connectivity OR --private-connection=... per Phase 2
For PostgreSQL use --type=postgresql, --postgresql-* flags, plus --postgresql-database=YOUR_DB.
4B. Destination connection profile (BigQuery)
gcloud datastream connection-profiles create bigquery-dest \
--location=us-central1 \
--type=bigquery \
--display-name="BigQuery destination"
Easiest path: do this in the Cloud Console → Datastream → Connection profiles, which runs a built‑in connectivity test and auto‑creates IAM bindings. Use the CLI for repeatable IaC.
Phase 5 — Create & start the stream
The stream ties source → destination, selects tables, and sets write behavior.
Console flow (recommended for first run):
- Datastream → Streams → Create stream.
- Pick the source connection profile → run the connectivity test.
- Select objects to include: choose specific schemas/tables (replicate only what you need).
- Pick the BigQuery destination profile and choose write mode:
- Merge (upsert) — BigQuery table mirrors the source (recommended for analytics). Requires primary keys.
- Append‑only — keeps every change as a row (full history / audit).
- Set data freshness / staleness limit (e.g.
15 minutes). Higher staleness = fewer MERGEs = lower cost. Start at a few minutes unless you truly need seconds. - Run validation → fix any flagged issues (missing PK, permissions, log config).
- Create, then Start the stream.
CLI equivalent (abridged):
gcloud datastream streams create rds-to-bq \
--location=us-central1 \
--display-name="RDS to BigQuery" \
--source=aws-rds-source \
--destination=bigquery-dest \
--mysql-source-config=source_config.json \
--bigquery-destination-config=dest_config.json \
--backfill-all
# then:
gcloud datastream streams update rds-to-bq --location=us-central1 --state=RUNNING
dest_config.json is where you set the dataset, write mode, and dataFreshness (staleness).
Phase 6 — Backfill + ongoing CDC
Datastream does two things automatically:
- Backfill (historical snapshot) — copies existing rows table‑by‑table on first run. Large tables take time; this is the bulk of initial cost/egress.
- CDC (ongoing) — after backfill, it streams every commit from the log.
Tips:
- You can exclude huge/cold tables from backfill and add them later.
- Backfill and CDC run concurrently; CDC changes during backfill are reconciled.
- Monitor progress in Datastream → your stream → Objects (each table shows backfill %, then “CDC”).
Phase 7 — Validation
1. Row‑count parity — compare source vs BigQuery after backfill settles.
-- In source (MySQL/Postgres):
SELECT COUNT(*) FROM orders;
-- In BigQuery:
SELECT COUNT(*) FROM `YOUR_GCP_PROJECT_ID.rds_replica.orders`;
2. Live‑change test — insert/update/delete a row in the source, then confirm it appears in BigQuery within your freshness window:
-- source
INSERT INTO orders (id, status) VALUES (999999, 'TEST');
UPDATE orders SET status='TEST2' WHERE id=999999;
-- BigQuery (a minute later)
SELECT * FROM `YOUR_GCP_PROJECT_ID.rds_replica.orders` WHERE id = 999999;
3. Metadata columns — Datastream adds columns like datastream_metadata.source_timestamp and a change type; verify deletes are reflected (row removed in merge mode, or marked in append mode).
Phase 8 — Cost control & optimization
| Lever | What to do | Why it saves |
|---|---|---|
| Replicate less | Include only needed tables/columns | Cuts egress (~$0.09/GB) + storage |
| Tune staleness | Set freshness to 5–15 min if seconds aren’t required | Fewer BigQuery MERGE runs → big query‑cost savings |
| Partition target tables | Partition by date, cluster by common filter keys | MERGE & queries scan less data |
| Region alignment | Put BigQuery dataset/region close to the AWS region | Lower latency, sometimes lower egress |
| Exclude high‑churn noise | Skip audit/log tables that change constantly | Avoids paying to replicate churn you don’t query |
| Private interconnect | At high sustained volume, use VPN/interconnect | Lowers per‑GB egress vs public internet |
The #1 cost lever: if you don’t truly need second‑level freshness, raising the staleness window from “seconds” to a few minutes typically cuts MERGE compute 5–10×.
Phase 9 — Monitoring & alerting
- Datastream metrics (Cloud Monitoring):
datastream.googleapis.com/stream/...— throughput, freshness/latency, unsupported‑event count. - Alert on stream freshness exceeding your SLA (e.g. lag > 15 min).
- Alert on stream state ≠
RUNNING(paused/failed). - Postgres only: alert on replication‑slot lag / retained WAL size so a stalled stream doesn’t fill the source disk.
- BigQuery: watch storage growth and on‑demand query bytes (set a billing budget + alert).
Phase 10 — Security hardening
- Least privilege: the
datastreamDB user has onlyREPLICATION+SELECT(no writes). - Secrets: store the DB password in Secret Manager; don’t hardcode in scripts/IaC.
- Network: prefer private connectivity (Phase 2 Option C); never expose RDS publicly in production.
- TLS: enforce SSL on the DB connection in the source profile.
- IAM: grant the Datastream service account only
bigquery.dataEditoron the specific dataset, not project‑wide. - Audit: enable Cloud Audit Logs for Datastream + BigQuery.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
CREATE USER: “permission denied to create role” (Postgres) | You used WITH REPLICATION — impossible on RDS/Aurora, no role holds that attribute | Create the user plain, then GRANT rds_replication TO datastream; as the master user |
| Validation: “replication slot status: Failed” (Postgres) | rds_replication never granted (RDS ignores WITH REPLICATION) | GRANT rds_replication TO datastream; → verify membership in pg_auth_members, not the rolreplication flag (it stays f on RDS) |
| Stream fails validation: “binlog not enabled” | binlog_format not ROW, or not rebooted | Re‑check parameter group, reboot instance |
| Connectivity test fails | Security group / firewall blocking Datastream IPs | Allowlist Datastream IP ranges on the port |
| Table is append‑only unexpectedly | No primary key on the table | Add a PK, or accept append mode |
| Postgres disk filling up | Replication slot retaining WAL (stream stalled) | Restart/fix stream; drop orphan slots |
| High BigQuery bill | MERGE running too frequently | Increase staleness/freshness window |
| Missing recent changes | Binlog/WAL retention too short | Increase retention (MySQL: binlog retention hours) |
| Unsupported events logged | Unsupported column types/DDL | Review Datastream unsupported‑event docs; handle column type |
Go‑live checklist
- Source engine, version, and tables confirmed
- Binlog (
ROW) / logical replication enabled and rebooted - Log retention set (MySQL ≥ 168h; Postgres slot created)
- Least‑privilege
datastreamuser created (Postgres: noWITH REPLICATION— useGRANT rds_replicationinstead) - Network path chosen (allowlist / SSH / private) + TLS on
- GCP APIs enabled, BigQuery dataset created in the right region
- Source + BigQuery connection profiles pass connectivity tests
- Stream created with correct write mode + freshness window
- Backfill completed; row counts match
- Live insert/update/delete verified in BigQuery
- Monitoring + billing/freshness alerts configured
- Secrets in Secret Manager; least‑privilege IAM applied
- Runbook documented for stream restart / slot cleanup
Quick start summary (the 8 moves)
- Enable binlog/logical replication on RDS (+ reboot).
- Create least‑privilege
datastreamDB user (+ Postgres publication & slot). - Open network path to Datastream (allowlist/SSH/private) with TLS.
- Enable GCP APIs + create BigQuery dataset.
- Create source + BigQuery connection profiles.
- Create the stream → pick tables, merge mode, freshness → validate → start.
- Let backfill finish, then verify parity + a live change.
- Add monitoring, alerts, and cost tuning (staleness + partitioning).