4.3 Change Data Capture (CDC) with Datastream for Continuous Database Ingestion

4.3 Change Data Capture (CDC) with Datastream for Continuous Database Ingestion

Exam Focus: The Professional Data Engineer exam tests your understanding of Change Data Capture (CDC) architectures. You must understand how Google Cloud Datastream reads transaction logs across Oracle (LogMiner), MySQL (binlog), and PostgreSQL (WAL / pgoutput), configure secure connectivity using Private Connectivity (VPC Peering) and Reverse SSH Tunnels, orchestrate historical backfills vs. live deltas, and implement modern BigQuery CDC continuous upsert to permanently replace resource-heavy SQL MERGE jobs.

Modern data platforms cannot rely on legacy batch database dumps (pg_dump, mysqldump, or periodic SQL SELECT * sweeps). Performing full table extracts against production Online Transaction Processing (OLTP) databases introduces severe read lock contention, degrades transactional throughput, spikes database CPU and memory utilization, and provides data that is already hours stale by the time it reaches the warehouse. Change Data Capture (CDC) resolves these challenges by reading committed mutations directly from the database's internal transaction log, streaming continuous inserts, updates, and deletes to analytical repositories with sub-second latency and near-zero database overhead.

---

1. Datastream Serverless Architecture

Google Cloud Datastream is a serverless, agentless Change Data Capture and replication service engineered to synchronize heterogeneous databases with Google Cloud storage and analytical sinks.

+-----------------------------------------------------------------------------------------+
|                         DATASTREAM SERVERLESS CDC TOPOLOGY                              |
+-----------------------------------------------------------------------------------------+
| SOURCE TRANSACTIONAL DATABASES                                                          |
| - Oracle (LogMiner / Redo Logs)                                                         |
| - MySQL / Cloud SQL for MySQL (Row-Based Binary Logs)                                   |
| - PostgreSQL / Cloud SQL for PostgreSQL (WAL / pgoutput plugin)                         |
| - Microsoft SQL Server (CDC Capture Tables)                                             |
+-----------------------------------------------------------------------------------------+
                                             │
                                             ▼ (Secure Transit)
| Private Connectivity (VPC Peering) │ Reverse SSH Tunnel │ IP Allowlist + TLS |
                                             │
                                             ▼
+-----------------------------------------------------------------------------------------+
| GOOGLE CLOUD DATASTREAM (Serverless Managed Processing)                                |
| - Agentless: Zero agent software installed on database server                          |
| - Dynamic Autoscaling: Handles variable transaction volumes serverlessly                |
| - Unified Stream Engine: Backfill Snapshot Extraction + Continuous Delta Streaming      |
| - In-Flight Schema Drift & Metadata Injection (_metadata_timestamp, _metadata_deleted)  |
+-----------------------------------------------------------------------------------------+
                                             │
                                             ▼
+-----------------------------------------------------------------------------------------+
| DESTINATION ANALYTICAL SINKS                                                            |
| - BigQuery: Direct Continuous Upsert with Primary Keys (Auto-reconciled Storage Engine)|
| - Cloud Storage: Raw CDC events in Apache Avro or JSON format                           |
| - Cloud Spanner: Low-latency replication for globally distributed ledgers               |
+-----------------------------------------------------------------------------------------+

Core Architectural Characteristics

• Agentless Deployment: Datastream requires no proprietary agents, sidecars, or daemon processes to be installed on the source database server. It connects to the database as a standard replication client using native database protocols, minimizing maintenance overhead and eliminating kernel compatibility issues.
• Serverless Scaling: There are no worker VMs, clusters, or compute instances to size, provision, or patch. Datastream automatically scales compute capacity to absorb sudden transaction surges without manual intervention.
• Pricing Model: Customers pay strictly for the data volume processed (priced per gigabyte of CDC data ingested), making it highly economical for databases with moderate transaction volumes.

---

2. Engine-Specific CDC Mechanics and Prerequisites

Each relational database engine maintains its own proprietary transaction log format and replication protocol. Configuring Datastream requires preparing the source database with specific logging parameters:

1. Oracle Database CDC

• Underlying Mechanism: Datastream integrates with Oracle LogMiner or uses Continuous Mining to extract transaction records directly from Oracle Redo Logs and Archived Redo Logs.
• Database Prerequisites:
  • Database must operate in ARCHIVELOG mode to ensure transaction logs are retained until read.
  • Enable supplemental logging at the database level to ensure that update records capture primary key columns along with modified values:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
    

  • Create a dedicated Datastream user with explicit read permissions on internal dynamic performance views (V_$DATABASE, V_$ARCHIVED_LOG, V_$LOG, V_$LOGMNR_CONTENTS) and grant execution privileges on DBMS_LOGMNR.

2. MySQL & Cloud SQL for MySQL CDC

• Underlying Mechanism: Datastream connects to MySQL as a replication slave, parsing the Binary Log (binlog) stream.
• Database Prerequisites:
  • Row-Based Logging: The binary log format must be set to ROW. Statement-based logging (STATEMENT) only logs SQL queries (e.g., UPDATE users SET status = 'active' WHERE signup_date < '2026-01-01'), which cannot be deterministically replayed across heterogeneous systems without identical database contexts:

    # MySQL configuration (my.cnf)
    binlog_format = ROW
    binlog_row_image = FULL
    expire_logs_days = 3
    

  • Full Row Image (binlog_row_image = FULL): Ensures that both the "before" and "after" images of all table columns are logged during updates, which is mandatory for downstream analytical reconciliations.
  • Binlog Retention: MySQL must retain binary logs on disk for at least 24–48 hours. If a network disruption occurs and MySQL purges binlogs before Datastream consumes them, the stream enters an unrecoverable state, requiring a full historical backfill.

3. PostgreSQL & Cloud SQL for PostgreSQL CDC

• Underlying Mechanism: Datastream uses PostgreSQL Logical Replication and Write-Ahead Logging (WAL). It consumes logical decoding events using the native pgoutput plugin introduced in PostgreSQL 10+.
• Database Prerequisites:
  • Set WAL level to logical:

    # PostgreSQL configuration (postgresql.conf)
    wal_level = logical
    max_replication_slots = 5
    max_wal_senders = 5
    

  • Create a publication that exposes the desired tables to the replication slot:

    CREATE PUBLICATION datastream_publication FOR ALL TABLES;
    

  • Create a logical replication slot using the pgoutput plugin:

    SELECT PG_CREATE_LOGICAL_REPLICATION_SLOT('datastream_slot', 'pgoutput');
    

  • Tables must have a PRIMARY KEY or a REPLICA IDENTITY configured (typically REPLICA IDENTITY FULL if tables lack natural primary keys) so that update and delete operations can identify which row was modified.

[!CAUTION] The Critical PostgreSQL WAL Disk Overflow Pitfall: In PostgreSQL, a logical replication slot guarantees that the primary database will never delete WAL files until the connected consumer (Datastream) acknowledges that it has read and processed them. If you stop or pause a Datastream stream for maintenance and leave the replication slot open, PostgreSQL continues retaining all newly generated WAL files in pg_wal. Within days or even hours, WAL files will consume 100% of the database disk space, causing the PostgreSQL server to crash. If a Datastream stream is permanently paused or deleted, always drop the replication slot immediately on the PostgreSQL primary (SELECT pg_drop_replication_slot('datastream_slot');).

---

3. Secure Connectivity Methods for Datastream

Connecting Datastream to source databases requires establishing a secure, authenticated network path. Datastream provides three distinct Connection Profile connectivity methods:

+-----------------------------------------------------------------------------------------+
|                       DATASTREAM CONNECTIVITY ARCHITECTURES                             |
+-----------------------------------------------------------------------------------------+
| 1. PRIVATE CONNECTIVITY (VPC Peering)                                                   |
| [ Datastream Tenant VPC ] ──(VPC Peering)──> [ Customer VPC ] ──> [ Cloud SQL / DB ]    |
| * Best for: Cloud SQL, GCE databases, and on-premises reachable via Interconnect/VPN    |
+-----------------------------------------------------------------------------------------+
| 2. REVERSE SSH TUNNEL                                                                   |
| [ Datastream Tenant VPC ] ──(SSH Outbound to Bastion)──> [ Bastion ] ──> [ On-Prem DB ] |
| * Best for: On-premises databases where corporate firewall blocks inbound internet      |
+-----------------------------------------------------------------------------------------+
| 3. IP ALLOWLISTING                                                                      |
| [ Datastream Public VIPs ] ──(TLS Encrypted over Internet)──> [ Public DB Endpoint ]     |
| * Best for: Cloud-hosted databases with public IPs; requires strict IP firewall rules   |
+-----------------------------------------------------------------------------------------+

1. Private Connectivity (VPC Peering)

• Architecture: Datastream provisions compute infrastructure inside an internal Google-managed tenant VPC. A Private Connectivity configuration establishes a direct VPC Network Peering between this tenant VPC and the customer's Google Cloud VPC.
• IP Allocation: The customer allocates an unused private RFC 1918 CIDR block of size /29 within their VPC. Datastream uses this range to communicate privately with internal database endpoints.
• Best For: Connecting to Cloud SQL instances configured with private IPs, Compute Engine databases, or on-premises databases accessible through existing Cloud Interconnect or Cloud HA VPN connections.

2. Reverse SSH Tunnel

• Architecture: Solves the classic on-premises firewall dilemma: corporate security policies strictly prohibit opening inbound firewall ports from the internet into the internal datacenter. To overcome this:
  1. The enterprise provisions a lightweight Linux bastion host in a corporate DMZ or perimeter network.
  2. The bastion runs an SSH daemon and initiates an outbound SSH connection to a designated Datastream public IP endpoint, or Datastream initiates an SSH connection to the bastion.
  3. Datastream routes all database queries and replication requests over this encrypted SSH tunnel, which the bastion forwards locally to the database port (3306, 5432, or 1521).
• Best For: Hybrid database replication when no dedicated Cloud Interconnect or VPN exists, and where enterprise security forbids inbound internet connections.

3. IP Allowlisting

• Architecture: The source database exposes a publicly accessible IP address. The network or database firewall restricts inbound access exclusively to the specific, published regional public IP addresses assigned to Datastream.
• Security Requirement: Mandatory enforcement of Transport Layer Security (TLS/SSL) encryption for all database connections to prevent interception of cleartext data in transit.

---

4. Backfill Operations vs. Continuous Streaming Deltas & Schema Evolution

A production CDC stream consists of two operational phases: historical snapshotting (backfill) and continuous change synchronization (deltas).

+-----------------------------------------------------------------------------------------+
|                        BACKFILL VS. CONTINUOUS STREAMING                                |
+-----------------------------------------------------------------------------------------+
| PHASE 1: HISTORICAL BACKFILL (Initial Snapshot)                                         |
| - Datastream captures existing database state without acquiring table read locks        |
| - Reads historical records concurrently while live delta streaming buffers in memory   |
| - Can be configured per-table: Automatic, Manual, or Excluded                           |
+-----------------------------------------------------------------------------------------+
                                             │
                                             ▼
| PHASE 2: CONTINUOUS DELTA SYNCHRONIZATION                                               |
| - Sub-second extraction from transaction logs (WAL, binlog, redo logs)                  |
| - Preserves transactional ordering using source commit timestamps and sequence IDs       |
| - Injects metadata fields: _metadata_timestamp, _metadata_change_type, _metadata_deleted|
+-----------------------------------------------------------------------------------------+

Historical Backfill Mechanics

• When a stream is initialized, downstream tables in BigQuery or Cloud Storage are empty. Datastream executes a consistent read of all historical rows currently residing in the source tables.
• Non-Locking Reads: Datastream uses multi-version concurrency control (MVCC) snapshot reads (such as SELECT ... AS OF SCN in Oracle or REPEATABLE READ transactions in MySQL/Postgres) to extract table snapshots without acquiring shared table locks, allowing OLTP write workloads to proceed without disruption.
• Granular Control: Backfill can be set to Automatic (runs immediately upon stream start) or Manual (delayed until off-peak hours). Individual high-volume historical tables can be excluded from backfill if they were already migrated via bulk transfer tools.

Schema Evolution and Drift Handling

In dynamic application environments, developers frequently alter database schemas by adding, modifying, or dropping columns. Datastream handles schema evolution gracefully:

• Column Additions: If an ALTER TABLE users ADD COLUMN loyalty_tier VARCHAR(50); is executed on the source database, Datastream automatically detects the new column in the transaction log, updates its internal schema registry, and appends the new field to downstream Avro files or BigQuery tables without stopping the stream.
• Table Additions: If a stream is configured to replicate an entire schema (prefix.*), newly created tables are automatically discovered and can be set to automatically initiate a backfill snapshot.
• Breaking Changes: Dropping columns or changing a column's data type (e.g., changing a field from INT to VARCHAR) cannot be applied automatically to strict downstream schemas without intervention. Datastream flags the event and allows engineers to pause and reconcile the target schema.

---

5. Target Sinks and Upsert Processing: Cloud Storage vs. BigQuery CDC

Datastream writes CDC events to several target destinations, each supporting distinct architectural consumption patterns:

Target 1: Cloud Storage (Raw Event Lake)

• Data Formats: Writes continuous event streams in Apache Avro (binary, schema-embedded, highly performant) or JSON format.
• Object Partitioning: Files are landed in Google Cloud Storage using date and time directory hierarchies: gs://lakehouse-cdc-raw/hr_db/employees/2026/09/15/14/file_123.avro
• Injected CDC Metadata Fields: Datastream appends rich metadata to every record, allowing downstream Dataflow or Spark pipelines to reconstruct exact historical state:
  • _metadata_source_timestamp: The exact microsecond timestamp when the transaction was committed on the source database.
  • _metadata_change_type: The operation type (INSERT, UPDATE-INSERT, UPDATE-DELETE, or DELETE).
  • _metadata_deleted: Boolean flag indicating whether the row was deleted.
  • _metadata_tx_id: The transaction identifier from the source database log.

Target 2: BigQuery Native CDC Continuous Upsert

Historically, landing CDC streams into BigQuery was an operational nightmare. Data teams were forced to:

1. Land CDC change events in a raw staging table in BigQuery.
2. Schedule recurring, complex SQL MERGE statements (executed via Cloud Composer or Scheduled Queries every 15 to 60 minutes) to reconcile updates and deletes against the target production table:

-- THE LEGACY ANTI-PATTERN: Resource-Intensive Hourly SQL MERGE Statement
MERGE `production.customers` T
USING (
  SELECT * EXCEPT(row_num)
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY _metadata_source_timestamp DESC) as row_num
    FROM `staging.customers_cdc`
  )
  WHERE row_num = 1
) S
ON T.customer_id = S.customer_id
WHEN MATCHED AND S._metadata_deleted = TRUE THEN DELETE
WHEN MATCHED THEN UPDATE SET name = S.name, email = S.email, updated_at = S._metadata_source_timestamp
WHEN NOT MATCHED AND S._metadata_deleted = FALSE THEN INSERT (customer_id, name, email, updated_at) VALUES (S.customer_id, S.name, S.email, S._metadata_source_timestamp);

Why the legacy MERGE pattern fails at scale: Running large MERGE queries every 15 minutes consumes massive numbers of query slots, spikes BigQuery costs, introduces 15-to-60 minute data staleness, and causes concurrent update lock conflicts on target tables.

The Modern Architecture: Native BigQuery CDC with Primary Keys

Google Cloud natively integrated Datastream with BigQuery to provide Continuous Upsert with BigQuery CDC:

1. Primary Key Enforcement: The target BigQuery table is defined with an unenforced primary key:

   CREATE TABLE `production.customers` (
     customer_id INT64 NOT NULL,
     name STRING,
     email STRING,
     PRIMARY KEY (customer_id) NOT ENFORCED
   );
   

2. Direct Ingestion via Storage Write API: Datastream writes CDC events directly into BigQuery using the Storage Write API in CDC mode.
3. Real-Time Storage Engine Reconciliation: BigQuery's underlying storage engine (Capacitor) automatically reconciles inserts, updates, and deletes in real time based on the source commit timestamp and primary key. Queries querying production.customers always return the latest, correctly merged state with sub-second data freshness, and zero scheduled SQL MERGE queries are required.

---

6. Concrete Exam Scenarios & Architecture Pitfalls