AWS RDS/Aurora → Datastream → BigQuery — Full A‑Z Implementation Guide

On this page

  1. 1. Prerequisites & decisions
  2. Phase 1 — Prepare the AWS source database
    1. 1A. If your source is MySQL (RDS or Aurora MySQL)
    2. 1B. If your source is PostgreSQL (RDS or Aurora PostgreSQL)
  3. Phase 2 — Network connectivity (AWS ↔ GCP)
    1. Option A — IP allowlisting (simplest, good for a start)
    2. Option B — Forward‑SSH tunnel (no public DB)
    3. Option C — Private connectivity (most secure, production)
  4. Phase 3 — Google Cloud project setup
  5. Phase 4 — Create Datastream connection profiles
    1. 4A. Source connection profile (example: MySQL)
    2. 4B. Destination connection profile (BigQuery)
  6. Phase 5 — Create & start the stream
  7. Phase 6 — Backfill + ongoing CDC
  8. Phase 7 — Validation
  9. Phase 8 — Cost control & optimization
  10. Phase 9 — Monitoring & alerting
  11. Phase 10 — Security hardening
  12. Troubleshooting
  13. Go‑live checklist
    1. Quick start summary (the 8 moves)

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/psql client and the gcloud CLI 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_format setting in the cluster; use aws rds modify-db-cluster-parameter-group and set binlog_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=1 on 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 REPLICATION on 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 get permission denied to create role / Only roles with the REPLICATION attribute may create roles with the REPLICATION attribute (PG16+; PG≤15 words it must be superuser to create replication users). Create the user plain and use GRANT rds_replication instead — that membership is the capability on RDS. Note that rolreplication then stays f, which is normal; verify membership via pg_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)

  1. In GCP, note the Datastream public IP ranges for your region (shown when you create the connection profile).
  2. In AWS, edit the RDS security group to allow inbound on the DB port (3306/5432) from those Datastream IPs only.
  3. 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):

  1. Datastream → Streams → Create stream.
  2. Pick the source connection profile → run the connectivity test.
  3. Select objects to include: choose specific schemas/tables (replicate only what you need).
  4. 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).
  5. 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.
  6. Run validation → fix any flagged issues (missing PK, permissions, log config).
  7. 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:

  1. Backfill (historical snapshot) — copies existing rows table‑by‑table on first run. Large tables take time; this is the bulk of initial cost/egress.
  2. 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 datastream DB user has only REPLICATION + 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.dataEditor on 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 datastream user created (Postgres: no WITH REPLICATION — use GRANT rds_replication instead)
  • 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)

  1. Enable binlog/logical replication on RDS (+ reboot).
  2. Create least‑privilege datastream DB user (+ Postgres publication & slot).
  3. Open network path to Datastream (allowlist/SSH/private) with TLS.
  4. Enable GCP APIs + create BigQuery dataset.
  5. Create source + BigQuery connection profiles.
  6. Create the stream → pick tables, merge mode, freshness → validate → start.
  7. Let backfill finish, then verify parity + a live change.
  8. Add monitoring, alerts, and cost tuning (staleness + partitioning).