Datastream
Datastream is the managed Change Data Capture (CDC) service. It connects to an operational source — Oracle, MySQL, PostgreSQL (including Cloud SQL and AlloyDB), SQL Server, MongoDB, Spanner, or even Salesforce — and streams row-level changes primarily to BigQuery or Cloud Storage (and, via Dataflow templates, to other destinations) in near real time. Analytics workloads see operational data within minutes of the change happening in the source, without the periodic full-table dump that used to be the only option.
Datastream closes the chapter by linking the operational world (Chapter 3 databases) to the analytical one (BigQuery, Looker). It is the modern replacement for the "nightly dump" ingestion pattern that hammered operational databases at 02:00 and left analytics 24 hours behind — though it captures and replicates only; transformations still happen downstream in BigQuery or Dataflow.
Change Data Capture Pattern
CDCDatastreamTwo ways to bring operational data into analytics: periodic dump or change capture. A periodic dump exports every row of every table on a schedule (nightly, hourly), copies it into the analytical store, and overwrites the previous copy. Simple, but it hammers the source database every run, transfers far more data than has changed, and the analytical view is as stale as the gap between dumps.
CDC consumes the database's change stream from its transaction log (binlog for MySQL, WAL for PostgreSQL, redo log for Oracle) — Datastream does this agentlessly, connecting over the network as a replication client rather than reading raw log files — and emits row-level changes as they happen. Source load is lower than repeated full exports, though it still needs capacity and log-retention planning; the analytical store sees changes within minutes; the data transferred is just the deltas. CDC has been the right architectural pattern for analytics-on-operational-data for over a decade; Datastream is what makes the pattern operationally cheap on GCP.
Sources and Destinations
Datastream sources: Oracle, MySQL, PostgreSQL (including Cloud SQL and AlloyDB), SQL Server, MongoDB, Spanner, and Salesforce, with more application sources in preview. Databases require their CDC mechanism to be enabled (binlog, logical replication, etc.) and a user with the right permissions to read it.
Destinations: BigQuery (the common case for analytics), Apache Iceberg tables, and Cloud Storage (the building block for custom downstream processing — a Dataflow pipeline reads the Avro/JSON files Datastream writes and does further transformation). Direct-to-BigQuery is the simpler path when the analytics use case does not need pre-load transformation.
Replication to BigQuery — Near Real-Time
In the direct-to-BigQuery mode, Datastream writes changes into BigQuery target tables continuously. The end-to-end lag — source commit to analytics-visible — is typically a few seconds to a few minutes, depending on source database load and Datastream throughput. For most analytics, this freshness is dramatically better than the daily-dump baseline and good enough for everything short of real-time operational reporting.
Datastream supports both upsert mode (target tables reflect the current state of source rows, with deletes applied) and append mode (every change recorded as a new row with a CDC type column — useful for audit and replay). Pick upsert for dashboards over current-state data, append for analytical histories where every change matters.
Schema Evolution
Schemas in source databases change. New columns added, columns renamed, types altered. Datastream handles many schema changes automatically — adding a column on the source propagates to the target. Some changes, particularly type changes that lose data (varchar to int) or column drops with downstream BigQuery dependencies, require manual intervention. Document the schema-change protocol with the team that owns the source database; "we changed the source schema and analytics broke" is the most common Datastream incident.
Datastream vs Pub/Sub + Dataflow
An older pattern uses Debezium-style CDC published to Pub/Sub, with a Dataflow pipeline reading from Pub/Sub and writing to BigQuery. Datastream is the simpler, managed alternative: no Pub/Sub topic to manage, no Dataflow pipeline to maintain, no Debezium connector versioning. Direct-to-BigQuery for the basic case.
The Pub/Sub + Dataflow pattern earns its place when the workload needs transformation, filtering, or routing before the data lands in BigQuery — for example, masking PII columns mid-stream or splitting one source into multiple destinations. In that case, Datastream writes to Cloud Storage and Dataflow processes the Avro files; or use Datastream-to-Pub/Sub via a Cloud Function shim. The base path is Datastream-direct-to-BigQuery, and there is no reason to add Pub/Sub plus Dataflow until a concrete transformation need shows up.
- Continuing to run nightly full-table dumps when Datastream provides incremental near-real-time CDC for the same workload at a fraction of the source load.
- Adding Pub/Sub plus Dataflow into the path when Datastream-direct-to-BigQuery covers the actual analytics need. Extra infrastructure, extra failure modes, no benefit.
- No plan for source schema changes. The database team renames a column, analytics breaks at the next sync. Document the change protocol up front.
- Treating Datastream as a backup mechanism. CDC replicates current state — it is not point-in-time recoverable, not transactionally consistent across all tables at a given timestamp, and not a substitute for actual database backups.
- Network or permission setup incomplete. Datastream cannot reach the source, fails silently for hours, no events flow, no obvious error. Test connectivity and permissions end-to-end before declaring the stream live.
- No monitoring of replication lag. The source pushes more writes than Datastream can keep up with; the lag grows; analytics is stale; nobody notices until a dashboard looks wrong.
- Datastream as the default for moving operational data into BigQuery for analytics. Periodic dumps are the legacy pattern.
- Direct-to-BigQuery when no pre-load transformation is needed. Add Cloud Storage plus Dataflow only when a concrete transformation requirement appears.
- Connect to source databases through Private Service Connect or VPN, not over public IPs. Production replication should not traverse the public internet.
- Document a schema-change protocol with the source-database owner. Datastream handles many changes automatically, but breaking changes need coordination.
- Cloud Monitoring alerts on replication lag and stream error rate from day one.
- Maintain a separate, transactionally consistent backup mechanism (Cloud SQL point-in-time recovery, AlloyDB continuous backup). Datastream is not a backup.
Knowledge Check
What does Datastream do?
- Takes scheduled full-table snapshots of operational databases and copies them wholesale into BigQuery tables on a fixed cron schedule each night
- Streams row-level changes (Change Data Capture) from sources like Oracle, MySQL, PostgreSQL, or SQL Server into BigQuery or Cloud Storage in near real time
- Replicates existing BigQuery tables across regions for global low-latency reads
- Provides a streaming SQL engine for running analytical queries directly against Pub/Sub topics
What is the principal advantage of CDC over periodic full-table dumps?
- CDC supports a noticeably wider set of source database engines and versions than periodic full-table dumps do
- CDC reads the database's transaction log and transfers only deltas — the source barely notices the load, the analytical store sees changes within minutes, and far less data moves
- CDC is the only pattern that supports cross-region data movement, whereas periodic full-table dumps are strictly limited to staying within the same region as the source database instance
- CDC is entirely free of charge, while periodic dumps are billed per scanned byte
When does adding Pub/Sub plus Dataflow in front of BigQuery beat Datastream-direct-to-BigQuery?
- Always — Datastream simply cannot write to BigQuery without an intermediate Pub/Sub topic in the path
- When the source database is MySQL, since PostgreSQL is the only engine that Datastream supports as a direct-to-BigQuery source while every other engine must route through Pub/Sub first
- When the workload needs pre-load transformation, filtering, or routing — for example, masking PII columns mid-stream or splitting one source into multiple destinations
- When the team prefers the operational simplicity that comes from running more moving parts
Why should Datastream not be treated as a backup mechanism?
- Datastream stores only the most recent change per row, discarding prior versions immediately
- CDC replicates current state — it is not point-in-time recoverable, not transactionally consistent across tables at an arbitrary timestamp, and no substitute for real backups like Cloud SQL PITR
- Datastream writes its output into BigQuery, and BigQuery cannot serve as a recovery source for an OLTP database because it stores no transaction log, no write-ahead log, and no rollback segments to replay from
- The CDC source connection auto-disconnects after 7 days of inactivity, losing the backup chain
A new Datastream from PostgreSQL to BigQuery shows zero events for two hours with no errors in the console. What is the most likely cause?
- BigQuery does not accept streaming CDC writes during the first two hours after stream creation, by design, to warm up the write path
- Network or permission setup is incomplete — Datastream cannot reach the source, no events flow, and the silent state is not always surfaced as a loud error
- Datastream batches changes over rolling two-hour windows, so seeing nothing yet is expected behavior on a fresh stream
- The source database's transaction log is exclusively locked by a competing CDC consumer, and Datastream is patiently waiting in line for its turn to read it
You got correct