Ensuring Consistency in BigQuery Streaming Analytics

PDE By islever Editorial Last updated

Deploying and operationalizing the pipelines.

Short answer

A Re-write the application to load accumulated data every 2 minutes to ensure strong consistency for aggregation queries.

Question

You need to store and analyze social media postings in Google BigQuery at a rate of 10,000 messages per minute in near real-time. Initially, design the
application to use streaming inserts for individual postings. Your application also performs data aggregations right after the streaming inserts. You discover that
the queries after streaming inserts do not exhibit strong consistency, and reports from the queries might miss in-flight data. How can you adjust your application
design?
  1. Re-write the application to load accumulated data every 2 minutes. Correct Answer
  2. Convert the streaming insert code to batch load for individual messages.
  3. Load the original message to Google Cloud SQL, and export the table every hour to BigQuery via streaming inserts.
  4. Estimate the average latency for data availability after streaming inserts, and always run queries after waiting twice as long.

Key Takeaways

Streaming inserts in BigQuery are eventually consistent; use micro-batching to achieve strong consistency for immediate aggregations without sacrificing near real-time requirements.

Learn how to resolve eventual consistency issues in Google BigQuery streaming inserts by using micro-batch accumulation for reliable near real-time analytics.

Common mistake: Choosing Option D (waiting based on estimated latency) is a common trap. While it attempts to account for lag, it relies on variable network conditions and does not guarantee consistency, unlike the deterministic completion of a batch load.

Expert Analysis

The core issue is BigQuery's eventual consistency model for streaming inserts. When data is streamed via tabledata.insertAll, it becomes visible in query results after a short delay (typically seconds to minutes), and strong consistency is not guaranteed during this window. Consequently, aggregation queries running immediately after the insert may miss 'in-flight' data.

Option D suggests estimating latency and waiting twice that amount. This is operationally fragile because network conditions and load vary; a static multiplier cannot guarantee consistency against unpredictable lag spikes or replication delays. It also fails to address the fundamental architectural mismatch between real-time streaming and consistent reporting.

Option B proposes converting to batch loads for individual messages. This is inefficient and contradicts the requirement for near real-time ingestion of 10,000 messages per minute. Batch loading small files individually incurs high overhead and low throughput.

Option A, rewriting the application to accumulate data and load it every 2 minutes, is the correct design adjustment. By buffering incoming streams into micro-batches (e.g., 2-minute windows) and performing batch loads, you leverage BigQuery’s highly optimized batch ingestion path. Batch loads are strongly consistent once completed, ensuring that aggregations run against complete, committed datasets. This approach balances the need for near real-time data (2-minute latency is often acceptable for 'near real-time' analytics) with the requirement for accurate, consistent reports.

Comments supporting Option A correctly identify that accumulating data reduces inconsistency. Comments favoring Option D are criticized for relying on heuristics ('wait twice as long') that do not solve the consistency problem reliably. The key insight is that when consistency is required, streaming should be replaced or supplemented by micro-batching.

Official Reference

https://cloud.google.com/bigquery/streaming-data-into-bigquery

This link opens the official vendor documentation in a new tab.

Exam Strategy

When a question involves BigQuery streaming and mentions 'consistency' or 'missing data', evaluate whether the solution requires switching from stream-to-batch or micro-batching. Avoid heuristic-based timing solutions (like 'wait X seconds') unless explicitly supported by specific API features like the Storage Write API with commit semantics. Look for options that trade minimal latency for data integrity if strong consistency is mandated.

Frequently Asked Questions

Why isn't streaming insert strongly consistent?

BigQuery optimizes for availability and performance. Streamed data is written to a buffer and replicated asynchronously, leading to eventual consistency where recent rows might not be immediately visible or fully replicated.

Is 2-minute batching considered near real-time?

Yes, in many enterprise contexts, 2-minute latency is acceptable for 'near real-time' dashboards and reports, especially when it guarantees data accuracy compared to the inconsistencies of raw streaming.

More PDE FAQ →

Community Votes

D
45%
A
33%
B
22%

45% of anonymous learners picked answer D. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 68 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 30 of 68

  1. noob_master 👍 11 Answered D
    Answer: D. The only that describe a way to resolve the problem, with buffering the data. (the question is possible old, the best approach would be Pub/Sub + Dataflow Streaming + Bigquery for streaming data instead near-real time)
  2. MaxNRG 👍 7
    B. Streams data into BigQuery one record at a time without needing to run a load job: https://cloud.google.com/bigquery/docs/reference/rest/v2/tabledata/insertAll Instead of using a job to load data into BigQuery, you can choose to stream your data into BigQuery one record at a time by using the tabledata.insertAll method. This approach enables querying data without the delay of running a load job: https://cloud.google.com/bigquery/streaming-data-into-bigquery The BigQuery Storage Write API is a unified data-ingestion API for BigQuery. It combines the functionality of streaming ingestion and batch loading into a single high-performance API. You can use the Storage Write API to stream records into BigQuery that become available for query as they are written, or to batch process an arbitrarily large number of records and commit them in a single atomic operation. Committed mode. Records are available for reading immediately as you write them to the stream. Use this mode for streaming workloads that need minimal read latency. https://cloud.google.com/bigquery/docs/write-api
  3. musumusu 👍 5
    Answer: D What to learn or look for 1. In-Flight data = (Real Time data, i.e still in streaming pipeline and not landed in BigQuery) 2. Dataflow (assume in best case) streaming pipeline is running to send data to Bigquery. Why not option B: change streaming to batch upload is not business requirement, we have to stuck to streaming and real time analysis. Option D: make bigquery run after waiting for sometime (twice here), How will you do it? - there is not setting in BQ to do it, right!. So, adjust it in your pipeline (dataflow) - For example, add Fixed window, and you want to execute aggregation query after 2 min. Code ``pipeline.apply(...) .apply(Window.<TableRow>into(FixedWindows.of(Duration.standardMinutes(2)))) .apply(BigQueryIO.writeTableRows() .to("my_dataset.my_table") ``
  4. kmaiti 👍 5 Answered B
    B is more appropriate(insert in batch mode). A & D are not suitable one to me. Questions for: A - why data should be accumulated in every 2 mins(Why 2 mins, it can be 1.5 mins, 1.8 mins, 2.x mins, 3 mins) etc, what is the justification behind selecting "2 mins". D - why user should wait twice time of average latency of data availability. If average latency is 3 mins, then user should wait 6 mins to run query. BQ docs states "some recently streamed rows might not be available for table copy typically for a few minutes. In rare cases, this can take up to 90 minutes"
  5. fire558787 👍 5
    "D" seems to use the typical approximate terminology of a wrong answer. "estimate the time" (how do you do that? do you do that over different times of the day?) and "wait twice as long" (who tells you that there are not a lot of cases when lag is twice as long?). Instead, "A" seems good. You don't need to show the exact results, but an approximation thereof, but you still want consistency. So an aggregation of the data every 2 minutes is a viable thing.
Show 25 more comments
  1. ckanaar 👍 5
    I'd argue that this question became outdated with the introduction of the BigQuery Storage Write API: https://cloud.google.com/bigquery/docs/write-api
  2. PRABHUKKARTHI 👍 5
    Option D: In Streaming buffer also, we can query the data. Only We need to have some latency to fetch the results.
  3. Rav761 👍 4 Answered A
    To address the issue of strong consistency and ensure your reports do not miss in-flight data after streaming inserts, you should re-write the application to load accumulated data every 2 minutes (option A). Here's why: By accumulating and loading data in 2-minute intervals, you can balance between real-time data processing and ensuring data consistency. This approach allows you to process the data in manageable batches, reducing the likelihood of inconsistencies that might occur with individual streaming inserts. It maintains a near real-time analysis capability while allowing enough time for all in-flight data to be captured and accurately represented in your reports. This adjustment should help improve the reliability of your data analysis and reporting.
  4. kishanu 👍 4
    There is no way of estimating the average latency. So, no for option D. Instead, option A is suitable, where the data can be accumulated for 2minutes using the windowing technique in Dataflow and then use the data in BigQuery.
  5. nana1995 👍 4 Answered D
    D is correct. B only adds a code batch load, it does not mention what you are going to do with this code, in this exam you can not assume.
  6. abunasar786 👍 4
    D is better as it at least talks about taking into account latency. WIndowing can be set in dataflow with it
  7. MarcoDipa 👍 3
    Answer is B. if you use the Committed mode, records are available for reading immediately as you write them to the stream. It is recommended to use his mode for streaming workloads that need minimal read latency. And the requirement is near rea-time. https://cloud.google.com/bigquery/docs/write-api#overview_of_the Besides, - You can use the Storage Write API to stream records into BigQuery that become available for query as they are written. - The Storage Write API has a significantly lower cost than the older tabledata.insertAll streaming API. In addition, you can ingest up to 2 TB per month for free https://cloud.google.com/bigquery/docs/write-api#advantages
  8. BigDataBB 👍 3 Answered A
    As written in GCP documentation the best approach to this kind of scenario is accumulate data ad save in batch mode: https://docs.cloud.google.com/bigquery/docs/optimize-load-jobs#batch_loading > Perform fewer, larger writes instead of many small ones. > Minimize separate write jobs to your final production table each day. > Instead of sending data to BigQuery each time new information becomes available, collect and load it into BigQuery using a single large job. So the answer is A
  9. imran79 👍 3
    A. Re-write the application to load accumulated data every 2 minutes. By accumulating data and performing a batch load every 2 minutes, you can reduce the potential inconsistency caused by streaming inserts. While this introduces a slight delay, it provides a more consistent approach than streaming each individual message. This method can still meet the near real-time requirement, and the slight delay is often acceptable in scenarios where data consistency is paramount.
  10. alecuba16 👍 3 Answered B
    Estimation is something that is only done by a designer that doesn't think technically, estimation requires a deep study of the distribution of the data latency and to be sure about the best time and most of the data you have to choose a narrow confidence interval with a big %. The best way to have confidence that the data is complete, is to confirm from the producer side that the stream of data is finish, that is to confirm the current stream, creating batches.
  11. AmirN 👍 3
    Wait how can you batch load individual messages? Isn't that the same as streaming inserts? If it said batch load blocks of messages that makes sense because then they can be aggregated and we wont have missing data. With that said I think its D. Its not actually hard to trial and error (estimate) average latency. Like if you estimate latency is 5 seconds, try 10 second delay in aggregation, if you have missing data bump it up and see the results.
  12. klughund 👍 3
    Streaming inserts in BigQuery are not immediately available to be queried, which is causing the weak consistency you're observing. A better approach is to batch the data and load it at regular intervals. Loading the data every two minutes is still relatively real-time, and it should help solve the consistency problem. Answer A.
  13. WillemHendr 👍 3
    Streaming Inserts is marked as Legacy now. https://cloud.google.com/bigquery/docs/streaming-data-into-bigquery#dataavailability The documentation is hinting on it can take up to 90 minutes to process the buffered data. This question is testing if you are aware of the possible long times the buffer can build up.
  14. izekc 👍 3 Answered B
    In my experience, estimation in D is not a technical solution. it is just a guess solution. You might still get caught when loading get higher and easily take twice as long latency, then problem occur again. So for a more permanent solution, you should definitely go with B
  15. bha11111 👍 3 Answered D
    1s tline of question requires near real time queries so D is the best option as batch load is never near real time
  16. StefanoG 👍 3
    Also this time I agree with @MaxNRG, the right answer is B https://cloud.google.com/bigquery/docs/write-api
  17. sergio6 👍 3
    How can you calculate average latency for data availability AFTER streaming inserts into biqquery ?
  18. techtitan 👍 3 Answered D
    near realtime
  19. sandipk91 👍 3
    sorry it's D
  20. jkhong 👍 2 Answered D
    There are assumptions over the quality of data acceptable. If slight variations of the analytics against actual can be accepted, then D would be a good choice. Many people chose B, but this also requires some form of waiting for the late data to arrive. I think a combination of D and B can be applied, but for an intial fix, delaying the aggregation queries with D seems to make more sense. If the variance is small and the some late data leakage is acceptable, and we can remain as D. If problems arise, we can always proceed to attempt B
  21. nats828 👍 2 Answered A
    A. Loading data in batches (using batch loads instead of streaming inserts) ensures strong consistency in BigQuery. Batch loads are available for querying immediately after the load job completes. This approach is recommended when you need consistent query results right after data ingestion. Answer D (which is most voted) is a workaround, not a solution
  22. korntewin 👍 2 Answered D
    The streaming mode may be in pending mode or buffered mode where the streaming data is not immediately available before committing or flushing. Thus, we need to wait before the data will be available. Or else we need to switch to commited mode (which is not present in the choices).
  23. imrane1995 👍 2 Answered A
    Accumulating data and loading it periodically (e.g., every 2 minutes) via batch inserts ensures strong consistency for queries. Batch loads in BigQuery allow you to avoid the latency issues inherent to streaming inserts and guarantee data availability for queries.
  24. Parth_P 👍 2 Answered D
    D is correct. The problem requirement is doing analytics on real-time data. You cannot do batch processing because the business requires it to be real-time even if it makes your job simpler, so B is incorrect. Other options are not streaming.
  25. StelSen 👍 2
    Option-A is incorrect. How can I conclude that in-flight data will come within 2 minutes? Atleast for Option-D, I will try to find avg latency first and doubling the time. Makes more sense.

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

How to establish consistent BigQuery spend?

S-grade

Triggering Airflow DAGs Reactively in Private Cloud Composer

S-grade

How to resolve Cloud Composer 2 worker pod evictions and task failures?

S-grade

Which Google Cloud database for on-prem PostgreSQL with analytics?

S-grade

← Back to PDE Study Guide