CDC Pipeline Lagging 40 Minutes and Producing Duplicate Revenue Rows

Root cause of duplicates: the destination table cannot be upserted when the key is ambiguous. Datastream applies CDC as upserts where it can identify a stable key. Here line_no is nullable and header rows have none, so the merge identity is unstable and conflicting rows land as appends. Every retry or backfill therefore adds a copy. The fix starts in the source, not the pipeline: make the key non-null and unique (surrogate order_line_id), which is a schema change in MySQL.

Root cause of the missing columns: DDL events are not automatically reflected in the destination schema in every configuration. New columns in the source stream are dropped when the destination table schema has not been updated. The durable fix is to make the BigQuery landing zone schema-flexible: land raw change events into a partitioned append-only table with a JSON payload column, then build a typed, deduplicated orders_current view/table with MERGE on the deterministic key. That decouples pipeline schema evolution from reporting schema.

Concretely:

• Add surrogate primary key in MySQL; backfill it before enabling the new stream.
• Land raw events in orders_cdc_raw partitioned by ingestion date, clustered by order_id.
• Run a scheduled MERGE (or materialized view if supported for your pattern) selecting the latest event per key by source timestamp and a tiebreaker; make it idempotent.
• Monitor datastream.googleapis.com/stream/event_count and freshness, alerting at 60 seconds of lag, not 40 minutes.
• Reconcile nightly: compare SUM(amount) for yesterday between MySQL and BigQuery; a revenue number nobody reconciles is a revenue number nobody should trust.

Tradeoff: the raw+MERGE pattern doubles storage and adds 1-3 minutes of latency, but it makes backfills safe - you can replay a window without duplicating rows, which is impossible when events are applied directly into the reporting table.

Diagnose before redesigning. A 9-million-row UPDATE is one transaction producing ~18 million change events (before/after images) in a very short window. Three things typically break: the source's binary log retention and read throughput are consumed by one giant transaction; Datastream's single stream and its BigQuery write path become the bottleneck; and the destination applies each event individually, so per-row overhead dominates. Confirm by checking whether lag correlates exactly with the batch and whether the destination write QPS or the source read is saturated.

Fixes, in order of leverage:

1. Stop generating the event storm in the source. A price update of that size should be batched into chunks of 50k-200k rows with commits between them, so CDC streams continuously instead of arriving as a cliff. This is the highest-value change and requires no pipeline work.
2. Separate backfill from streaming. Datastream backfills compete with live CDC for the same stream resources. Use a dedicated backfill stream or a GCS-based bulk load for large historical reloads, then let the live stream handle only recent changes.
3. Right-size and partition the destination. Partition orders_cdc_raw by day and cluster by order_id so MERGE touches only recent partitions. A MERGE over an unpartitioned 3-billion-row table is itself a multi-minute job.
4. Don't ask the stream to do analytics. Keep BigQuery for reporting only.

On the 5-minute freshness goal: if the 02:00 batch still exists in some form, a 5-minute SLA is achievable because streaming stays live underneath it. If the business insists on a single 9-million-row transaction, no CDC product will hold 5 minutes - say so and price the alternative (larger stream, more cost, still a spike).

Reject request-time cross-store joins as the default. BigQuery can query Bigtable through external tables, but scans are row-key oriented and the external table cannot push down arbitrary predicates or participate efficiently in a large join. Joining a billion-row BigQuery table to a Bigtable external table will either time out or scan the entire Bigtable table per query. The symptom described - joins failing or timing out - is the expected behavior, not a configuration bug.

Design the data flow so each store answers the question it is good at:

• Bigtable serves point lookups by row key at millisecond latency: 'what is in this customer's cart right now'. Keep it that way and add the one or two order fields needed for cart display (denormalized) so the common screen needs no second store.
• BigQuery serves aggregate and historical analysis. If analysts genuinely need cart history, export cart state changes (Bigtable change streams → Dataflow → BigQuery) into a cart_events table and join inside BigQuery. Storage is cheap; this is the clean solution.
• Reverse ETL for enrichment: if Bigtable needs a computed order value from BigQuery, publish it with Dataflow into Bigtable row keys rather than making the serving path call BigQuery.

When a cross-store join is legitimate: small, bounded dimensions (e.g., 200 store locations) can be exported to BigQuery as a table and joined there. The rule is that the Bigtable side of any join must be bounded and known at planning time, not discovered by scanning.

Cost note: external-table scans are billed by data processed at the BigQuery side and can be surprisingly expensive when analysts iterate. The exported-events design makes cost predictable and lets you cluster on the join keys.