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

Google Cloud Certified Professional Data Engineer Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Security, IAM, and Data Protection

1.1 Cloud IAM, Service Accounts, and Resource Hierarchy in GCP Data Systems 1.2 Data Encryption, Key Management (CMEK), and Cloud KMS 1.3 Sensitive Data Protection: Cloud DLP, De-identification, and Masking

2 Chapter 2: Reliability and Compliance Architecture

2.1 High Availability, Regional vs Multi-Regional Deployments, and Disaster Recovery 2.2 Data Residency, Sovereignty, and Regulatory Compliance (HIPAA, GDPR) 2.3 Modern Data Architecture Patterns: Data Mesh, Lakehouse, and Modern Data Stack

3 Chapter 3: Cross-Cloud and Federated Governance

3.1 BigLake Architecture: Unified Multicloud Storage and Fine-Grained Access Control 3.2 BigQuery Omni: Multicloud Analytics Across AWS S3, Azure ADLS, and Google Cloud 3.3 Analytics Hub and Federated Computational Governance

4 Chapter 4: Data Ingestion and Pipeline Planning

4.1 Data Pipeline Planning: Sources, Sinks, and Network Architecture (VPC, PSC, Interconnect) 4.2 Batch vs Streaming Processing Trade-Offs and Architecture Selection 4.3 Change Data Capture (CDC) with Datastream for Continuous Database Ingestion

5 Chapter 5: Streaming Ingestion with Cloud Pub/Sub

5.1 Cloud Pub/Sub Core Architecture: Topics, Subscriptions, and Message Lifecycle 5.2 Pub/Sub Message Ordering, Exactly-Once Delivery, and Deduplication 5.3 Pub/Sub BigQuery/Cloud Storage Direct Subscriptions and Dead-Letter Topics

6 Chapter 6: Pipeline Processing with Apache Beam and Cloud Dataflow

6.1 Apache Beam Programming Model: Pipelines, PCollections, and Core PTransforms 6.2 Cloud Dataflow Architecture: Autoscaling, Worker Allocation, and Dataflow Prime 6.3 Windowing Strategies in Streaming Dataflow: Fixed, Sliding, and Session Windows

7 Chapter 7: Advanced Dataflow: Robustness and Exactly-Once Semantics

7.1 Watermarks, Triggers, Accumulating vs Discarding Panes, and Late Data Handling 7.2 Side Inputs, Broadcast Joins, and Stateful Stream Processing 7.3 Pipeline Robustness: Dead-Letter Queues, Error Handling, and Pipeline Lifecycle Management

8 Chapter 8: Managed Data Processing: Cloud Dataproc and Cloud Data Fusion

8.1 Cloud Dataproc: Architecture, Ephemeral vs Persistent Clusters, and Spark Tuning 8.2 Dataproc Serverless and Autoscaling Policies for Batch Processing 8.3 Cloud Data Fusion: Visual ETL/ELT Pipelines, CDAP Plugins, and Data Lineage

9 Chapter 9: Storage Selection and Cloud Storage Architecture

9.1 GCP Storage Selection Decision Matrix: Relational, NoSQL, Object, and Analytical Stores 9.2 Cloud Storage Architecture: Storage Classes, Lifecycle Rules, and Object Versioning 9.3 Cloud Storage Security: Bucket Lock (WORM), Access Control, and Data Migration Tools

10 Chapter 10: Relational Cloud Databases: Cloud Spanner and Cloud SQL

10.1 Cloud Spanner Architecture: TrueTime, External Consistency, and Global Scalability 10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning 10.3 Cloud SQL: High Availability, Read Replicas, Maintenance, and Point-in-Time Recovery

11 Chapter 11: Scalable NoSQL Databases: Cloud Bigtable and Firestore

11.1 Cloud Bigtable Architecture: Storage Model, Tablet Splitting, and Cluster Management 11.2 Cloud Bigtable Row Key Design: Lexicographical Ordering, Salt Keys, and Anti-Patterns 11.3 Cloud Firestore: Document Model, Real-Time Sync, and ACID Collections

12 Chapter 12: Enterprise Data Warehousing with BigQuery

12.1 BigQuery Architecture: Capacitor Columnar Format, Jupiter Network, and Borg Slots 12.2 BigQuery Partitioning: Ingestion-Time, Date/Timestamp, and Integer Range Strategies 12.3 BigQuery Clustering: Cardinality, Multi-Column Ordering, and Performance Optimization 12.4 Data Warehouse Modeling: Normalization, Star Schemas, and Slowly Changing Dimensions

13 Chapter 13: Advanced Analytical Queries and Transformations in BigQuery

13.1 Advanced SQL in BigQuery: Window Analytic Functions, ARRAY/STRUCT, and PIVOT/UNPIVOT 13.2 Modern SQL Orchestration with Dataform: DAG Modeling, Testing, and Documentation 13.3 BI Engine In-Memory Acceleration and Enterprise Looker/LookML Semantic Modeling

14 Chapter 14: Machine Learning and Feature Engineering with BigQuery ML

14.1 BigQuery ML Architecture: In-Database Training, Model Topologies, and SQL Analytics 14.2 Feature Engineering: ML.TRANSFORM, Preprocessing Pipelines, and Vertex AI Feature Store 14.3 MLOps, Model Governance, Continuous Monitoring, and Deployment Lifecycles

15 Chapter 15: Operations, Orchestration, and Data Governance

15.1 Workflow Orchestration with Cloud Composer: Managed Airflow, DAG Design, and Dependencies 15.2 Unified Data Governance with Dataplex: Lakes, Zones, Data Quality, and Lineage 15.3 Pipeline Observability, FinOps, and Cost Optimization Across GCP Data Services

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

Key Takeaways Explain This Topic AI

Datastream is a serverless, agentless Change Data Capture (CDC) and replication service that streams real-time data changes with minimal compute and I/O impact on operational transaction processing (OLTP) database engines.

Engine-specific CDC mechanisms vary: Oracle uses LogMiner or Continuous Log Mining, MySQL mandates row-based binary logging ('binlog_format = ROW'), and PostgreSQL relies on logical replication slots with the 'pgoutput' plugin and Write-Ahead Logging ('wal_level = logical').

Datastream supports three secure network connectivity methods: Private Connectivity (VPC Peering with Datastream tenant VPC) for private RFC 1918 communication, Reverse SSH Tunnels via a customer-hosted bastion, and IP Allowlisting with TLS.

Historical backfills extract an initial consistent snapshot without taking table read locks, while continuous streaming captures real-time deltas; schema evolution automatically handles column additions and table additions without halting replication streams.

Ingesting into BigQuery via Datastream supports native BigQuery CDC continuous upsert using table primary keys, allowing BigQuery's Storage Engine to merge row changes automatically in real time without executing expensive recurring SQL MERGE statements.

Last updated: September 2026

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:

The enterprise provisions a lightweight Linux bastion host in a corporate DMZ or perimeter network.

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.

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.

Connectivity Method Network Path Security Boundary Setup Complexity Best Use Case

Private Connectivity Google internal peering (RFC 1918) Complete private isolation Low to Moderate (Requires /29 CIDR) Cloud SQL, GCE DBs, Hybrid via Interconnect

Reverse SSH Tunnel Encrypted SSH tunnel via Bastion DMZ Bastion proxy Moderate (Requires Bastion VM & Keys) On-Premises DBs with strict inbound firewalls

IP Allowlisting Public Internet with TLS Static regional Google VIPs Lowest (Firewall rule update) Cloud-hosted DBs (AWS RDS, Azure) with public IPs

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:

Land CDC change events in a raw staging table in BigQuery.

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 :

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
• );

Direct Ingestion via Storage Write API: Datastream writes CDC events directly into BigQuery using the Storage Write API in CDC mode.

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.

• Architecture Attribute Legacy Staging + SQL MERGE Modern Native BigQuery CDC with Datastream
• Data Freshness 15 – 60 minutes (Batch schedule) Sub-second to seconds (Continuous)
• Slot Consumption High (Heavy full-table scans & joins) Zero query slots (Storage engine background merge)
• Operational Overhead High (Managing Composer DAGs & locks) Zero (Fully serverless managed integration)
• Cost Profile Expensive (Query processing fees) Low (Incur only Storage Write API ingest fees)
• Failure Recovery Complex (Tracking failed merge checkpoints) Automated (Built-in watermark tracking)
• 6. Concrete Exam Scenarios & Architecture Pitfalls

Scenario / Architecture Challenge Common Anti-Pattern Correct Google Cloud Architecture

PostgreSQL Primary Database Crashing ; Two weeks after a Datastream stream is paused for application maintenance, the production PostgreSQL server crashes due to 100% disk utilization. Blaming Datastream for creating temporary log files and attempting to expand persistent disk size. Understand that PostgreSQL retains all Write-Ahead Logs (WAL) in pg_wal because the logical replication slot was never acknowledged. Resume the stream to drain logs, or drop the replication slot ( pg_drop_replication_slot ) if decommissioned.

MySQL Missing Updates in Warehouse ; Datastream replicates a MySQL database to BigQuery, but updates to user records are either dropped or result in incomplete column values in BigQuery. Assuming Datastream has dropped the connection and recreating the entire stream. Inspect MySQL binary log configuration. Ensure binlog_format = ROW and binlog_row_image = FULL . If row image is set to MINIMAL , MySQL only logs modified columns, preventing complete downstream row updates.

Eliminating High-Cost BigQuery Merges ; An enterprise runs 120 scheduled SQL MERGE queries every hour to reconcile CDC staging tables in BigQuery, consuming 80% of their slot reservations. Purchasing more dedicated BigQuery slots to reduce query queue latency. Modernize the pipeline to Native BigQuery CDC . Re-create destination tables with PRIMARY KEY ... NOT ENFORCED and configure Datastream to write directly to BigQuery CDC. Eliminates all SQL MERGE queries entirely.

Replicating Behind Corporate Firewalls ; An on-premises Oracle database must replicate to BigQuery. Corporate security policy strictly prohibits opening any inbound internet ports into the corporate datacenter. Opening inbound database port 1521 to the public internet and using IP allowlisting. Deploy a Reverse SSH Tunnel . Provision a lightweight bastion in the on-premises DMZ that initiates an outbound SSH connection to Datastream. Datastream forwards replication traffic securely through the established tunnel without opening inbound firewall holes.

Loading diagram... Continuous Database CDC Pipeline: Heterogeneous Sources, Secure Networking, and BigQuery Upsert