Mapping Google Cloud Services for Real-Time Streaming, SQL Analytics, and Batch Exports

PDE By islever Editorial Last updated

Planning the data pipelines. Designing for a data platform.

Short answer

B Cloud Pub/Sub handles real-time event streams, BigQuery provides ANSI SQL access to real-time and historical data, and Cloud Storage enables batch historical exports.

Question

You are building an application to share financial market data with consumers, who will receive data feeds. Data is collected from the markets in real time.
Consumers will receive the data in the following ways:
✑ Real-time event stream
✑ ANSI SQL access to real-time stream and historical data
✑ Batch historical exports
Which solution should you use?
  1. Cloud Dataflow, Cloud SQL, Cloud Spanner
  2. Cloud Pub/Sub, Cloud Storage, BigQuery Correct Answer
  3. Cloud Dataproc, Cloud Dataflow, BigQuery
  4. Cloud Pub/Sub, Cloud Dataproc, Cloud SQL

Key Takeaways

The trap is assuming you need a processing engine like Dataflow or Dataproc for every streaming scenario; however, BigQuery's native streaming ingestion allows it to serve as both the sink for real-time data and the SQL query engine, simplifying the architecture.

This question tests the ability to select the right Google Cloud services for a multi-modal data architecture involving real-time streaming, SQL analytics, and batch exports. The correct solution uses Pub/Sub, BigQuery, and Cloud Storage.

Common mistake: Many candidates choose Option D (Pub/Sub, Dataproc, Cloud SQL) because they assume raw data must be processed by Dataproc before entering a database. However, Cloud SQL is not optimized for large-scale real-time streaming SQL access or efficient batch exports compared to BigQuery and Cloud Storage respectively.

Expert Analysis

The scenario requires a solution for three distinct data consumption patterns: real-time event streaming, ANSI SQL access to both real-time and historical data, and batch historical exports. To solve this, we must map each requirement to the most appropriate Google Cloud service based on its core capabilities.

First, for Real-time event stream, the data is collected in real time from markets. Cloud Pub/Sub is Google's globally distributed, scalable messaging service designed specifically for ingesting and distributing real-time event streams. It allows producers to publish messages and consumers (subscribers) to receive them with low latency, making it the ideal choice for fan-out scenarios where multiple consumers need immediate access to market data.

Second, for ANSI SQL access to real-time stream and historical data, we need a storage and query engine that supports standard SQL and can handle both streaming inserts and large-scale historical analysis. BigQuery is a serverless, highly scalable data warehouse that natively supports ANSI SQL. Crucially, BigQuery has built-in streaming ingestion capabilities, allowing data from sources like Pub/Sub to be written directly into tables without complex pipeline code. This provides unified SQL access to both the live stream and historical records stored within the same platform.

Third, for Batch historical exports, consumers need to download or transfer large volumes of historical data. Cloud Storage is the object storage service designed for durability and cost-effective storage of large datasets. It is the standard mechanism for exporting data in batch formats (e.g., CSV, Parquet) for offline consumption or archival purposes. While databases like Cloud SQL or Spanner are optimized for transactional queries, they are not typically used for bulk batch exports of historical data due to performance and cost constraints compared to object storage.

Option B (Cloud Pub/Sub, Cloud Storage, BigQuery) correctly aligns these services with the requirements. Option A fails because Cloud SQL/Spanner do not efficiently support real-time streaming SQL access at scale. Option C includes Dataproc, which is unnecessary when BigQuery handles the SQL/streaming needs directly. Option D uses Cloud SQL, which lacks native high-throughput streaming SQL capabilities and is less suitable for batch exports than Cloud Storage.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

When designing data architectures, always look for managed services that combine functionality to reduce complexity. For example, if a requirement asks for 'SQL access to real-time data,' prioritize data warehouses like BigQuery over traditional relational databases (Cloud SQL) because they offer native streaming ingestion and powerful analytical SQL capabilities out-of-the-box, eliminating the need for separate processing layers like Dataflow for simple ingest-to-SQL pipelines.

Frequently Asked Questions

Can BigQuery handle real-time data without Dataflow?

Yes. BigQuery supports direct streaming inserts via the API, allowing data to be written to tables in near real-time without needing an intermediary processing service like Dataflow for simple ingestion.

Why not use Cloud SQL for the SQL access requirement?

Cloud SQL is a relational database optimized for OLTP workloads. It does not efficiently support high-volume real-time streaming data ingestion or complex analytical SQL queries at the scale implied by financial market data compared to BigQuery.

More PDE FAQ →

Community Votes

B
78%
D
11%
A
11%

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

Discussion 30 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

  1. itche_scratche 👍 12
    D, not ideal but only option that work. You need pubsub, then a processing layer (dataflow or dataproc), then storage (some sql database).
  2. medeis_jar 👍 10 Answered B
    ✑ Real-time event stream -> Pub/Sub ✑ ANSI SQL access to real-time stream and historical data -> BigQuery ✑ Batch historical exports -> Cloud Storage
  3. VishalB 👍 9
    Correct Answer: B Streaming Data -- Pub/Sub ANSI SQL access -- Big Query Batch historical exports -- Cloud Storage
  4. Rajokkiyam 👍 8
    Answer B. Streaming Data - Pub/Sub. Query Batch and Live Streaming Data - BigQuery.
  5. jalk 👍 6
    because of the requirement: Batch historical exports
Show 25 more comments
  1. barnac1es 👍 4
    B. Cloud Pub/Sub, Cloud Storage, BigQuery. Here's how this solution aligns with your requirements: Real-time Event Stream: Cloud Pub/Sub is a managed messaging service that can handle real-time event streams efficiently. You can use Pub/Sub to ingest and publish real-time market data to consumers. ANSI SQL Access: BigQuery supports ANSI SQL queries, making it suitable for both real-time and historical data analysis. You can stream data into BigQuery tables from Pub/Sub and provide ANSI SQL access to consumers. Batch Historical Exports: Cloud Storage can be used for batch historical exports. You can export data from BigQuery to Cloud Storage in batch, making it available for consumers to download.
  2. kino2020 👍 4
    "Processing logs at scale using Cloud Dataflow" As far as this document is concerned. ・Cloud Storage for storing exported logs in batch mode. ・Pub/Sub for streaming exported logs in streaming mode. ・Dataflow for processing log data. ・BigQuery for storing processing output and supporting rich queries on that output. It is stated that. It says the data is collected from the market, and the problem is that the methods are defined as requirements. Therefore, I think a close answer is B. https://cloud.google.com/solutions/processing-logs-at-scale-using-dataflow?hl=ja However, it's Spanner that's good at finance.
  3. MaxNRG 👍 4 Answered B
    Cloud Pub/Sub, Cloud Dataflow, BigQuery https://cloud.google.com/solutions/stream-analytics/
  4. John_Pongthorn 👍 3 Answered B
    B: https://cloud.google.com/solutions/stream-analytics/ Real-time made real easy Adopt simple ingestion for complex events Ingest and analyze hundreds of millions of events per second from applications or devices virtually anywhere on the globe with Pub/Sub. Or directly stream millions of events per second into your data warehouse for SQL-based analysis with BigQuery's streaming API.
  5. AdrianMonter26 👍 3
    I think it must be D because you need Pub/Sub for streaming data, Dataflow or DataProc to get the data from Pub/Sub and store it in a database and finally the Cloud SQL database to store the data. A and C cannot be because it is missing something for streaming data B It can't be because you need something to pass the data from Pub/Sub to Cloud storage
  6. seiyassa 👍 3
    I think pubsub doesn't have good connection to dataproc, so D is not the answer
  7. serg3d 👍 3
    So, why do we need Cloud Storage in this pipeline?
  8. AzureDP900 👍 3
    B. Cloud Pub/Sub, Cloud Storage, BigQuery
  9. vaga1 👍 2 Answered B
    I was in doubt as I did not know that BQ handles real-time access to data without dataflow underneath. https://cloud.google.com/bigquery/docs/write-api#:~:text=You%20can%20use%20the%20Storage,in%20a%20single%20atomic%20operation.
  10. cetanx 👍 2
    Here is the reference: https://cloud.google.com/blog/products/data-analytics/pub-sub-launches-direct-path-to-bigquery-for-streaming-analytics
  11. jkhong 👍 2
    As of Dec 2022,there is the PubSub Lite connector to Dataproc
  12. tavva_prudhvi 👍 2
    You gonna use batch historical export for Spanner? It's B!
  13. haroldbenites 👍 2
    B is correct
  14. rgpalop 👍 2
    I think B
  15. carbonscave 👍 1 Answered B
    Why B is correct This option perfectly maps the exact right Google Cloud managed service to each of the three architectural requirements: Real-time event stream (Cloud Pub/Sub): Pub/Sub is Google's globally distributed message bus. It allows you to ingest real-time market data and effortlessly fan it out to thousands of independent consumers via subscriptions. ANSI SQL access to real-time and historical data (BigQuery): BigQuery is a petabyte-scale data warehouse (OLAP) with full ANSI SQL support. Because BigQuery supports a Streaming API (Storage Write API), you can stream the market data directly into the tables. When consumers run a SQL query, BigQuery seamlessly calculates the results across both the massive historical archive and the data that arrived just milliseconds ago. Batch historical exports (Cloud Storage): Cloud Storage is the de facto destination for batch file delivery. You can easily schedule BigQuery to export historical datasets as compressed CSV, JSON, or Parquet files into a Cloud Storage bucket, which your external consumers can then download in batches.
  16. MaxNRG 👍 1
    B. Cloud Pub/Sub, Cloud Storage, BigQuery The key requirements here are: 1. Real-time event stream (Pub/Sub) 2. ANSI SQL access to real-time and historical data (BigQuery) 3. Batch historical exports (Cloud Storage) So Cloud Pub/Sub provides the real-time stream, BigQuery provides ANSI SQL access to stream and historical data, and Cloud Storage enables batch historical exports. Option A is incorrect because Cloud Spanner does not offer batch exports and Dataflow is overkill for just SQL access. Option C is incorrect as Dataproc is for spark workloads, not serving consumer data. Option D is incorrect as Cloud SQL does not provide batch export capabilities. Therefore, option B with Pub/Sub, Storage, and BigQuery is the best solution given the stated requirements. Dataflow https://cloud.google.com/solutions/stream-analytics/
  17. SamuelTsch 👍 1 Answered D
    why B? The main goal of the question is data storage. Thus BigQuery is not neccessary for this situation. Option D from my point of view cover the whole requirements. Pub/Sub for streaming data, dataproc for data processing, SQL for storage.
  18. jkhong 👍 1
    We can have our pubsub topics to have BigQuery subscriptions, where data is automatically streamed into our BQ tables. Autoscaling is already handled automatically so this renders Dataflow and Dataproc pretty irrelevant for our usecase
  19. John_Pongthorn 👍 1 Answered B
    No matter what the last of it must end up with bigquery and the first service is pubsub I think intimidate service it should be dataflow
  20. Motivated_Gamer 👍 1 Answered A
    Dataflow: Streaming data CLoud SQL: for ansi sql support Spanner: for batch historical data export
  21. midgoo 👍 1 Answered B
    Event Stream -> PubSub PubSub has direct Write to BigQuery Historical Exports to GCS
  22. AzureDP900 👍 1
    https://cloud.google.com/solutions/stream-analytics/
  23. Prasanna_kumar 👍 1
    Answer is B
  24. JG123 👍 1
    Correct: B
  25. sumanshu 👍 1
    Vote for B

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

Bigtable Row Key Design for Time-Series Data

S-grade

Monitoring Cloud Bigtable for Cluster Scaling Decisions

S-grade

← Back to PDE Study Guide