4.3 Change Data Capture (CDC) with Datastream and Batch Ingestion

4.3 Change Data Capture (CDC) with Datastream and Batch Ingestion

[!NOTE] In enterprise data engineering on Google Cloud, operational databases (OLTP) must feed analytical warehouses (OLAP) without degrading transactional performance. The Professional Data Engineer exam frequently evaluates two complementary patterns: real-time log-based Change Data Capture (CDC) using Datastream for low-latency operational replication, and event-driven Cloud Storage batch loading into BigQuery to maximize cost efficiency.

Enterprise architectures rely on operational databases (such as Cloud SQL, on-premises MySQL, PostgreSQL, Oracle, and SQL Server) to power customer-facing transactional applications. To drive real-time business intelligence, fraud detection, customer 360 dashboards, and continuous machine learning inference, data engineers must replicate mutating operational data into centralized analytical sinks—such as BigQuery and Cloud Storage—with minimal latency and zero downtime.

Historically, teams attempted operational replication using periodic batch polling queries (e.g., executing SELECT * FROM orders WHERE updated_at > :last_batch_time). However, query-based polling suffers from fatal architectural flaws in production environments:

• Heavy Table Locks and CPU Spikes: Querying large tables strains production database CPU and disk I/O, competing with transactional user traffic.
• Missed Hard Deletes: If an application issues a hard DELETE statement, the row vanishes from the table. Polling queries filtering on updated_at can never detect deleted records.
• Loss of Intermediate State Changes: If an order status changes from PENDING to PROCESSING to SHIPPED within a 15-minute polling window, a periodic query only captures SHIPPED, permanently losing critical intermediate state transitions.
• High Analytical Latency: Business intelligence dashboards remain hours out of date.

Modern enterprise architectures replace polling with non-intrusive, log-based Change Data Capture (CDC).

---

Real-Time CDC with Google Cloud Datastream

Google Cloud Datastream is a fully managed, serverless change data capture and replication service that streams data from operational relational databases into Google Cloud storage and analytical engines with sub-second latency.

+-----------------------------------------------------------------------------------------+
| Source Relational Database (OLTP)                                                       |
|                                                                                         |
|  +-------------------+      +-------------------------------------------------------+   |
|  | Operational Table |      | Database Transaction Log (Committed Mutations)        |   |
|  | [User / Order]    |      | • MySQL: Binary Log (ROW format)                      |   |
|  +-------------------+      | • PostgreSQL: Write-Ahead Log (WAL / pgoutput)        |   |
|          | (Mutations)      | • Oracle: Redo Log / Archive Log (LogMiner)           |   |
|          v                  +-------------------------------------------------------+   |
|  [INSERT / UPDATE / DELETE]                            |                                |
+--------------------------------------------------------|--------------------------------+
                                                         | (Continuous Log Streaming)
                                                         v
+-----------------------------------------------------------------------------------------+
| Google Cloud Datastream (Serverless CDC Engine)                                         |
|                                                                                         |
| • Secure Transport: VPC Peering / Reverse SSH Tunnel / Cloud VPN                        |
| • Backfill Coordination: Merges historical table snapshot with live CDC log stream     |
| • Metadata Injection: Appends _metadata_timestamp, _metadata_change_type, etc.         |
| • Schema Drift Engine: Dynamically propagates newly added source columns downstream     |
+-----------------------------------------------------------------------------------------+
                                 |                                    |
                 [Direct BigQuery Replication]          [Continuous Stream Export]
                                 v                                    v
+----------------------------------------------------+  +---------------------------------+
| BigQuery Data Warehouse (OLAP)                     |  | Cloud Storage Data Lake         |
| • Continuous UPSERT via Storage Write API          |  | • Raw Ingest Files (Avro / JSON)|
| • 1:1 Fresh, Queryable Table Replica               |  | • Partitioned by date / entity  |
+----------------------------------------------------+  +---------------------------------+

Non-Intrusive Transaction Log Mining

Datastream achieves near-zero source database overhead by bypassing the SQL query execution engine entirely. Instead, it directly reads and parses the database's native transaction log files:

1. MySQL: Mines the Binary Log (binlog). Requires row-based logging (binlog_format = ROW), full image row updates (binlog_row_image = FULL), and Global Transaction Identifiers (gtid_mode = ON).
2. PostgreSQL: Reads the Write-Ahead Log (WAL) via logical decoding replication slots using the pgoutput or test_decoding output plugins, requiring wal_level = logical.
3. Oracle: Extracts database changes from redo logs and archive logs using Oracle LogMiner or binary log parsing APIs.
4. Microsoft SQL Server: Reads committed transactions using SQL Server CDC or Change Tracking features.

Because transaction logs are written sequentially to disk during transactional commits, Datastream's extraction imposes negligible CPU and I/O overhead on production databases, while capturing every single INSERT, UPDATE, and DELETE event with absolute fidelity.

---

Backfill vs. Stream Replication and Coordination

A production CDC pipeline must solve the initial synchronization problem: replicating existing historical data while simultaneously streaming in-flight transactional mutations.

Datastream handles this through coordinated phases:

1. Historical Backfill (Snapshot): When a stream starts, Datastream initiates an automated backfill phase. It reads historical records from the tables in parallel chunks without placing read locks on tables.
2. Continuous CDC Streaming: Concurrently, Datastream establishes a replication slot on the database transaction log, capturing all live mutations occurring while the backfill is in progress.
3. Stream Coordination and Deduplication: Using transaction log sequence identifiers—such as Log Sequence Numbers (LSN) in PostgreSQL, System Change Numbers (SCN) in Oracle, or GTID positions in MySQL—Datastream merges historical table snapshots with the ongoing change stream. It ensures that transactions occurring during the backfill are neither duplicated nor lost, smoothly transitioning into steady-state sub-second replication.

Schema Evolution and Metadata Enrichment

Datastream is designed to accommodate operational schema drift without human intervention:

• Dynamic Schema Evolution: If an administrator alters a source table by adding a new column (ALTER TABLE users ADD COLUMN loyalty_tier VARCHAR(50)), Datastream automatically detects the schema modification in the transaction log, updates downstream table definitions, and populates the new column in destination records without stopping or resetting the replication stream.
• Operational Metadata Enrichment: Datastream injects system metadata attributes into each emitted change record:
  • _metadata_timestamp: The UTC timestamp when the transaction was committed at the source database.
  • _metadata_change_type: The exact DML operation (INSERT, UPDATE-INSERT, UPDATE-DELETE, or DELETE).
  • _metadata_schema: The source database or schema name.
  • _metadata_table: The source table name.
  • _metadata_uuid: A globally unique event identifier for deterministic downstream deduplication.

BigQuery Direct Replication vs. Cloud Storage Destination

• BigQuery Direct Replication: Datastream integrates directly with BigQuery. Using the BigQuery Storage Write API and automated UPSERT (MERGE) logic, Datastream automatically maintains an up-to-date, queryable 1:1 replica of the operational source table inside BigQuery. This eliminates the legacy requirement of deploying custom Dataflow pipelines to execute BigQuery MERGE SQL statements.
• Cloud Storage Destination: Datastream writes change records as micro-batches of Apache Avro or JSON files into a Cloud Storage bucket. This pattern is ideal when changes must be consumed by multiple downstream systems, processed through complex Apache Beam transformations, or archived in a data lake.

---

Private Network Connectivity for Datastream

Because production transactional databases house an enterprise's most sensitive data, exposing database ports to the public internet is strictly forbidden by corporate security policies. Datastream provides multiple private connectivity mechanisms:

1. Private Connectivity (VPC Peering via Service Networking): Datastream provisions internal compute resources inside a Google-managed tenant VPC. A VPC Network Peering connection is established between Datastream's tenant network and the customer's Virtual Private Cloud (VPC). All replication traffic flows across Google's private backbone network using internal RFC 1918 private IP addresses. This is the recommended, best-practice architecture for connecting to Cloud SQL instances and Compute Engine-hosted databases.
2. Reverse SSH Tunnels: When replicating from an on-premises database or third-party cloud where VPC peering cannot be established, data engineers deploy a lightweight Linux bastion host VM with a public IP in the source environment. Datastream establishes an encrypted SSH connection to the bastion, which tunnels traffic directly to the private database port.
3. Forward Proxy / Static IP Allowlisting: For environments where databases are reachable via public IP but protected by strict corporate firewalls, Datastream provides a static pool of regional public IP addresses that can be allowlisted on the source firewall. In this architecture, database traffic must be secured using SSL/TLS encryption.
4. Cloud VPN / Cloud Interconnect: Datastream private connectivity can route across dedicated Cloud Interconnect or Cloud VPN tunnels, reaching private on-premises database endpoints without traversing the public internet.

---

Batch Ingestion Patterns: Cloud Storage and BigQuery

While real-time CDC is required for operational replication, high-throughput batch ingestion remains the dominant, most cost-effective pattern for ingesting periodic flat files, partner data feeds, transactional logs, and massive historical archives.

Event-Driven Batch Pipeline Architecture

A standard Google Cloud architecture for automated file ingestion combines Cloud Storage, Pub/Sub, and serverless compute:

+-----------------------------------------------------------------------------------------+
| 1. Cloud Storage Landing Bucket (gs://enterprise-landing-raw)                           |
|    External partners or on-prem ETL drop hourly files (e.g., sales_2026_09_14.parquet) |
+-----------------------------------------------------------------------------------------+
                                             |
                                             | (Emits OBJECT_FINALIZE Event)
                                             v
+-----------------------------------------------------------------------------------------+
| 2. Cloud Storage Pub/Sub Notification                                                   |
|    gcloud storage buckets notifications create gs://landing-raw --topic=gcs-file-events |
+-----------------------------------------------------------------------------------------+
                                             |
                                             | (Push or Pull Dispatch)
                                             v
+-----------------------------------------------------------------------------------------+
| 3. Serverless Orchestrator (Cloud Functions / Cloud Run / Workflows)                    |
|    • Extracts bucket and object name from event envelope                                |
|    • Performs schema validation and file integrity checks                               |
|    • Submits asynchronous BigQuery Load Job via API                                     |
+-----------------------------------------------------------------------------------------+
                                             |
                                             | (Executes bq load / SQL LOAD DATA)
                                             v
+-----------------------------------------------------------------------------------------+
| 4. BigQuery Data Warehouse (Zero Ingestion Compute Cost)                                |
|    • Data appended directly into partitioned, clustered analytical tables               |
+-----------------------------------------------------------------------------------------+

The BigQuery Free Batch Load Advantage

[!IMPORTANT] A fundamental architectural principle tested heavily on the Professional Data Engineer exam is the cost structure of BigQuery batch loading: Loading data into BigQuery from Cloud Storage using batch load jobs (bq load, SQL LOAD DATA, or BigQuery Data Transfer Service) is 100% FREE.

Google Cloud charges zero compute slot fees for BigQuery batch load jobs. Batch loads execute on an internal, shared Google resource pool rather than consuming customer-purchased slot commitments or on-demand query bytes. In contrast, streaming data into BigQuery using the Storage Write API or legacy streaming inserts incurs a per-gigabyte ingestion charge.

Therefore, unless business requirements mandate sub-second or sub-minute analytical data availability, data engineers should always favor batch loading from Cloud Storage over streaming inserts to minimize cloud operational expenditure.

BigQuery Batch Load Methods and Formats

Data engineers execute batch loads via several native mechanisms:

1. SQL LOAD DATA Statement: Executes declarative, scriptable ELT loading within BigQuery SQL scripts or stored procedures:

   LOAD DATA INTO `my_project.analytics.daily_transactions`
   FROM FILES (
     format = 'PARQUET',
     uris = ['gs://enterprise-landing-raw/transactions/*.parquet']
   );
   

2. bq load CLI Tool: Submits an asynchronous load job from shell scripts or Airflow operators:

   bq load \
     --source_format=PARQUET \
     --time_partitioning_field=transaction_date \
     --clustering_fields=customer_id,merchant_id \
     analytics.daily_transactions \
     gs://enterprise-landing-raw/transactions/*.parquet
   

3. Format Selection Best Practices:
   • Parquet and Avro (Recommended): Binary, compressed, strongly typed, and self-describing. BigQuery automatically extracts the table schema from Parquet/Avro metadata without type inference errors or file scanning overhead.
   • CSV and JSON (Newline-Delimited): Plaintext formats. Require explicit schema files or schema auto-detection (--autodetect). Prone to schema mismatch errors, string parsing latency, and delimiter collisions.

---

Ingestion Architecture Decision Matrix

---

Exam Traps and Antipatterns Summary