Prompt and context
Within a BigQuery change data capture (CDC) table's configured max_staleness, a dashboard may read the allowed stale baseline at normal latency. Once pending changes are older than that window, query-time merge can return current results at higher latency. The task is to distinguish these states and background apply backlog with the platform's own watermarks and job metrics.
What the interviewer evaluates
- Measuring applied progress with
upsert_stream_apply_watermark. - Separating allowed staleness, background apply backlog, and runtime merge.
- Connecting insufficient background capacity to user query latency.
- Alerting on business thresholds and duration instead of one noisy sample.
Clarifications before answering
Establish business tolerance, each table's max_staleness, write peaks, background reservations, and dashboard frequency. Include a deletion objective because a CDC delete is applied only after the watermark passes its write time.
A 30-second answer framework
Poll INFORMATION_SCHEMA.TABLES.upsert_stream_apply_watermark and compare now - watermark with both table configuration and the business threshold. Correlate it with background apply P95, queueing and failure, plus runtime-merge count and query latency. Page only when sustained watermark breach combines with resource saturation or user impact.
Step-by-step deep dive
Create low-cardinality metrics per data product: watermark age, max_staleness, background apply P95, failures, and runtime-merge queries. Within the configured window, a query can read the baseline. Outside it, BigQuery may merge pending changes at query time, increasing latency; that runtime merge does not advance the watermark.
Use two gates: two consecutive business-threshold breaches create a warning; reservation saturation, apply timeout, or dashboard latency promotes it to a page. A source heartbeat separates no new data from an apply backlog.
Diagnose writes, background jobs, capacity, and runtime merges in that order. Then add background capacity, smooth input, or revisit the window. Do not weaken the business objective merely to silence alerts.
Strong sample answer
The applied watermark is my primary signal; apply jobs and query merges explain it. Each minute I compare watermark age with the SLO and table option, then correlate apply P95, capacity, and query tail latency.
Recovery requires the watermark to catch up, runtime merges to fall, and dashboard latency to normalize. A peak-load replay proves the added headroom.
Common mistakes
- Monitoring only successful writes or green pipeline jobs.
- Treating event time as BigQuery's applied watermark.
- Assuming a runtime merge advances the watermark.
- Increasing
max_stalenessonly to remove alerts. - Lacking a source heartbeat.
Follow-up questions
Why can runtime merge slow a query?
The query merges baseline data with pending modifications to return current results, consuming additional time and compute.
What if the watermark is null?
Check CDC configuration, recent mutations, and background jobs. Treat unknown as its own state, not zero lag.
When is a CDC delete applied?
Only after upsert_stream_apply_watermark passes the timestamp at which the delete was streamed.
How do you prove scaling worked?
Under representative peak load, require better apply P95, watermark age, runtime-merge count, and dashboard tail latency together.