Prompt and scope
The business wants data written to BigQuery to trigger alerts quickly and send results to a table, Pub/Sub, Bigtable, or Spanner. Explain whether to use BigQuery continuous queries and how you would handle input semantics, authorization, runtime, regions, cost, and recovery. Do not limit the answer to SQL syntax.
What interviewer is testing
- Whether you understand a continuous query as continuously running SQL, not fixed-interval polling.
- Whether you can place it relative to Dataflow, Pub/Sub, and ordinary queries based on latency and data semantics.
- Whether you verify the Enterprise edition,
CONTINUOUSreservation, service account, and regional constraints. - Whether you address duplicate output, backpressure, monitoring, restart behavior, and cost.
Clarifying questions
- Is the input append-only, or can existing rows be updated and deleted? Are duplicates acceptable?
- Is the destination BigQuery, Pub/Sub, Bigtable, or Spanner, and can consumers retry safely?
- What latency, runtime, and data-region guarantees are required? Is the project provisioned for them?
- After failure, where does processing resume, and how are lag, errors, and output volume monitored?
A 30-second answer
I would first verify that the requirement is genuinely continuous processing. Continuous queries analyze incoming BigQuery data and write or export results, but they have edition, capacity, authorization, and regional constraints. Define idempotency keys, choose the destination, and prepare a service account, a CONTINUOUS reservation, and monitoring. Validate latency, duplicates, cost, stop, and recovery with controlled traffic; do not treat the feature as an unlimited, free replacement for Cron.
Step-by-step design
1. Define data semantics first
A continuous query keeps processing data written to BigQuery tables. Appends, late events, updates, and deletes have different meanings. If the business needs complex state, event-time windows, or strict ordering, confirm the supported SQL behavior and compare a stream processor such as Dataflow.
2. Choose the output path
The documentation supports inserting results into a BigQuery table or using EXPORT DATA to Pub/Sub, Bigtable, or Spanner. Choose based on downstream throughput, ordering, idempotency, and region. Pub/Sub is useful for another event-processing stage; direct table writes need deduplication and retention keys.
3. Verify runtime and authorization constraints
You can create and run a continuous query with a user account or service account; exporting to Pub/Sub requires a service account. A user-account job can run for up to two days, while a service-account job can run for up to 150 days. Continuous queries require the Enterprise or Enterprise Plus edition and a reservation assignment of type CONTINUOUS.
4. Budget, monitor, and recover
Continuous queries use BigQuery capacity compute pricing, while receiving services cost separately. Monitor query-specific metrics, input-to-output latency, errors, restarts, and output volume; define stop, rebuild, and alert procedures. Recover from an idempotency key or watermark and replay only an acceptable range, so a restart does not duplicate side effects.
Model high-quality answer
I would evaluate a continuous query as a data-product operating constraint. It can continuously analyze data written to BigQuery and write or export results to BigQuery, Pub/Sub, Bigtable, or Spanner, but append-versus-change semantics and duplicate tolerance determine whether it fits. I would verify the Enterprise edition, CONTINUOUS reservation, service account, maximum runtime, and region boundary. The output contract would define idempotency, retries, and dead-letter handling; telemetry would cover lag, backlog, errors, and cost. Before launch, controlled traffic would test latency and restart behavior, with explicit pause, resume, and backfill procedures. If the workload needs rich event-time state, strict ordering, or a longer-lived topology, I would compare a dedicated stream processor instead of forcing every real-time requirement into BigQuery.
Common mistakes
- Treating a continuous query as a query that runs once per minute.
- Ignoring Enterprise or Enterprise Plus, the
CONTINUOUSreservation, or service-account requirements. - Assuming every destination has identical ordering and duplicate semantics.
- Omitting watermarks or idempotency keys for restarts, duplicates, and late data.
- Measuring SQL latency without pricing BigQuery capacity and downstream services.
- Treating the two-day or 150-day runtime limits as a promise of permanent execution.
Follow-up questions and responses
When would you choose Dataflow?
Compare Dataflow or another stream processor when the workload needs complex event-time windows, state management, strict ordering, rich connectors, or a long-lived topology. The boundary is semantic and operational, not the number of SQL lines.
How do you avoid duplicate alerts after a restart?
Put an event or business idempotency key in each output, deduplicate or transact downstream, and record a watermark and processing batch. Resume from a safe replay boundary and document any unavoidable duplicate behavior to consumers.
How do you decide whether the cost is acceptable?
Estimate capacity slots, ingestion and storage, plus Pub/Sub, Bigtable, or Spanner charges separately. Load-test steady state, idle periods, and peaks, then calibrate the budget with observed latency and slot consumption.