BigQuery vs Cloud SQL for High-QPS API Serving

PDE By islever Editorial Last updated

Short answer

D 1. Store the historical data in BigQuery for analytics.

Question

You migrated a data backend for an application that serves 10 PB of historical product data for analytics. Only the last known state for a product, which is about 10 GB of data, needs to be served through an API to the other applications. You need to choose a cost-effective persistent storage solution that can accommodate the analytics requirements and the API performance of up to 1000 queries per second (QPS) with less than 1 second latency. What should you do?
    1. Store the historical data in BigQuery for analytics.
    1. Store the products as a collection in Firestore with each product having a set of historical changes.
    1. Store the historical data in Cloud SQL for analytics.
    1. Store the historical data in BigQuery for analytics.
    Correct Answer

Key Takeaways

The exam tests your understanding that BigQuery is optimized for analytics, not transactional API serving, and the trap is assuming materialized views can directly handle high-QPS, low-latency workloads.

When serving petabyte-scale analytics data alongside a high-QPS API, the best practice is to store historical data in BigQuery and the last-known-state data in Cloud SQL. This combination provides cost-effective analytics and sub-second 1000 QPS API performance.

Common mistake: The most common wrong answer is Option A, which uses a BigQuery materialized view to serve the API. Though clever, BigQuery's architecture and API quotas make it unreliable for 1000 QPS with less than 1 second latency, unlike a purpose-built transactional store like Cloud SQL.

Expert Analysis

Why the Answer Is Correct

Option D correctly separates the two distinct workloads: BigQuery for the 10 PB historical analytics and Cloud SQL for the 10 GB last-known-state data. BigQuery is a serverless data warehouse designed for petabyte-scale analytical queries, offering cost-effective on-demand storage. Cloud SQL, a fully managed relational database, is optimized for transactional workloads and can easily handle 1000 QPS with sub-second latency for a small dataset (10 GB).

The architecture works by storing historical changes in BigQuery and maintaining a current-state table in Cloud SQL that is updated after each product change. This keeps the API access pattern simple and performant, while still allowing deep analytics on the full history.

Why the Other Options Are Wrong

Option A is tempting because it avoids adding another database, but BigQuery is not designed for point lookups or high-concurrency transactional access. BigQuery's API quotas and typical query latency (even with materialized views) make it difficult to consistently meet 1000 QPS with <1 second latency, as noted in comment [6]. Materialized views are for precomputation, not for serving as an operational datastore.

Option B (Firestore) would not handle the 10 PB analytics requirement well, and storing all historical changes in Firestore documents is costly and inefficient. Option C (Cloud SQL for analytics) is inappropriate because Cloud SQL is not built for petabyte-scale analytical workloads, both in terms of cost and performance.

Community Comment Notes

Several comments correctly point out that BigQuery should be used only for analytics, while Cloud SQL should handle the API. Comment [2] provides the exact architecture for Option D, and comment [10] explains why Cloud SQL handles high-QPS transactional workloads effectively. Comment [6] even references BigQuery's API request quotas, including the 1000 req/s table-data-list limit, emphasizing that other methods like queries have lower limits. Comments supporting Option A (materialized views) miss that BigQuery's latency and quota characteristics are not suitable for a sustained 1000 QPS operational API.

Official Reference

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

Exam Strategy

When facing questions that mix analytics and high-QPS transactional serving, remember to separate the stores: use data warehouses like BigQuery for analytics and dedicated transactional databases like Cloud SQL for API serving. Look for clues about latency, QPS, and data size—Cloud SQL is the typical answer for sub-second, high-concurrency point lookups.

Community Votes

D
71%
A
29%

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

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

  1. einchkrein 👍 7
    Serve the last state data directly from Cloud SQL to the API. Here's why this option is most suitable: BigQuery for Analytics: BigQuery is an excellent choice for storing and analyzing large datasets like your 10 PB of historical product data. It is designed for handling big data analytics efficiently and cost-effectively. Cloud SQL for Last State Data: Cloud SQL is a fully managed relational database that can effectively handle the storage of the last known state of products. Storing this subset of data (about 10 GB) in Cloud SQL allows for optimized and faster query performance for your API needs. Cloud SQL can comfortably handle the requirement of up to 1000 QPS with sub-second latency. Separation of Concerns: This approach separates the analytics workload (BigQuery) from the operational query workload (Cloud SQL). This separation ensures that analytics queries do not interfere with the operational performance of the API and vice versa.
  2. datapassionate 👍 7 Answered D
    D. 1. Store the historical data in BigQuery for analytics. 2. In a Cloud SQL table, store the last state of the product after every product change. 3. Serve the last state data directly from Cloud SQL to the AP This approach leverages BigQuery's scalability and efficiency for handling large datasets for analytics. BigQuery is well-suited for managing the 10 PB of historical product data. Meanwhile, Cloud SQL provides the necessary performance to handle the API queries with the required low latency. By storing the latest state of each product in Cloud SQL, you can efficiently handle the high QPS with sub-second latency, which is crucial for the API's performance. This combination of BigQuery and Cloud SQL offers a balanced solution for both the large-scale analytics and the high-performance API needs.
  3. Matt_108 👍 3 Answered D
    Option D is the right one, compared to option A, Cloud SQL is more efficient and cost effective for the amount of time the data needs to be accessed by the api
  4. scaenruy 👍 2 Answered A
    A. 1. Store the historical data in BigQuery for analytics. 2. Use a materialized view to precompute the last state of a product. 3. Serve the last state data directly from BigQuery to the API.
  5. clouditis 👍 2 Answered A
    A is the most plausible option - Cloud SQL can not retrieve results out with 1 second latency as the requirement here is, with BQ MV"s that could be a possibility as its pre-computed.
Show 7 more comments
  1. ToiToi 👍 1 Answered A
    Why A? Because: Materialized View for API: A materialized view in BigQuery pre-computes the last known state of each product. This ensures that your API can quickly retrieve the latest product information without needing to query the entire historical dataset. BigQuery for API Serving: BigQuery can handle high query volumes with low latency, meeting your requirement of 1000 QPS with sub-second latency. Cost-Effectiveness: This solution avoids the need for a separate database like Cloud SQL, minimizing costs and management overhead. Why not D: While Cloud SQL is a good option for transactional workloads, it's not as cost-effective or scalable as BigQuery for analytical queries on 10 PB of data. It might also not be the ideal choice for serving high-volume API requests with low latency.
  2. CGS22 👍 1 Answered D
    Why D is the best choice: Cost-Effective Analytics: BigQuery excels at handling large datasets (10 PB) and complex analytical queries. Its columnar storage and massively parallel processing make it ideal for analyzing historical product data. High-Performance API: Cloud SQL provides a managed relational database service optimized for transactional workloads. It can easily handle the 1000 QPS requirement with low latency, ensuring fast API responses. Separation of Concerns: Storing historical data in BigQuery and the last known state in Cloud SQL separates analytical and transactional workloads, optimizing performance and cost for each use case.
  3. josech 👍 1 Answered A
    Materialized views are precomputed views that periodically cache the results of a query for increased performance and efficiency. Materialized views can optimize queries with high computation cost and small dataset results. https://cloud.google.com/bigquery/docs/materialized-views-intro#use_cases https://cloud.google.com/bigquery/docs/materialized-views-intro
  4. ML6 👍 1 Answered D
    BigQuery = data warehouse that is optimized for querying and analyzing large datasets using SQL. Can easily process petabytes of data. Cloud SQL = designed for transactional workloads and traditional relational database use cases, such as web applications, e-commerce platforms, and content management systems.
  5. zanhsieh 👍 1 Answered D
    Why not A? Because BQ API 100 reqs per second for API method. Other possible limits do not meet the 1000 QPS requirement. Yes, max number of tabledata.list reqs per second is 1000 but we won't always call tabledata.list all time. https://cloud.google.com/bigquery/quotas#api_request_quotas
  6. Anudeep58 👍 1 Answered D
    Why not A: Serving data directly from BigQuery to the API may not meet the low latency requirements for high QPS operations, as BigQuery is optimized for analytical queries rather than transactional workloads.
  7. JyoGCP 👍 1 Answered D
    Option D

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