How to minimize Cloud SQL load for BigQuery analytics?

PDE By islever Editorial Last updated

Short answer

C Create streams in Datastream to replicate the required tables from both Cloud SQL databases to BigQuery for these queries.

Question

You have terabytes of customer behavioral data streaming from Google Analytics into BigQuery daily. Your customers’ information, such as their preferences, is hosted on a Cloud SQL for MySQL database. Your CRM database is hosted on a Cloud SQL for PostgreSQL instance. The marketing team wants to use your customers’ information from the two databases and the customer behavioral data to create marketing campaigns for yearly active customers. You need to ensure that the marketing team can run the campaigns over 100 times a day on typical days and up to 300 during sales. At the same time, you want to keep the load on the Cloud SQL databases to a minimum. What should you do?
  1. Create BigQuery connections to both Cloud SQL databases. Use BigQuery federated queries on the two databases and the Google Analytics data on BigQuery to run these queries.
  2. Create a job on Apache Spark with Dataproc Serverless to query both Cloud SQL databases and the Google Analytics data on BigQuery for these queries.
  3. Create streams in Datastream to replicate the required tables from both Cloud SQL databases to BigQuery for these queries. Correct Answer
  4. Create a Dataproc cluster with Trino to establish connections to both Cloud SQL databases and BigQuery, to execute the queries.

Key Takeaways

The exam tests the ability to offload analytical query workloads from operational databases (Cloud SQL) to analytical warehouses (BigQuery) using replication services like Datastream, avoiding the trap of using federated queries which increase load on the source.

To minimize load on Cloud SQL databases while frequently joining data with BigQuery, use Datastream to replicate the SQL data into BigQuery. This allows the marketing team to run high-frequency analytical queries directly within BigQuery without impacting the performance of the transactional databases.

Common mistake: Choosing Option A (BigQuery federated queries) is a common mistake because it allows querying without moving data, but it fails the requirement to minimize load on Cloud SQL as each query hits the source database directly.

Expert Analysis

Why the Answer Is Correct

Option C is correct because Google Datastream enables change data capture (CDC) to replicate data from Cloud SQL (MySQL and PostgreSQL) into BigQuery in near real-time. Once the data resides in BigQuery, the marketing team can run up to 300 analytical queries per day against the combined dataset (Analytics + SQL data) entirely within BigQuery. This approach completely isolates the heavy analytical workload from the transactional Cloud SQL instances, ensuring minimal load and high performance.

Why the Other Options Are Wrong

Option A is incorrect because BigQuery federated queries execute the query portion against the external Cloud SQL database for every run. Running this 100-300 times a day would place significant read load on the transactional databases, violating the requirement to keep load to a minimum. Options B and D are incorrect because using Dataproc (Serverless or Cluster) to query Cloud SQL directly still requires connecting to the source databases for each job, incurring similar load and complexity without the benefits of BigQuery's native performance for this specific use case.

Community Comment Notes

The community strongly supports Option C, noting that Datastream is a fully managed service designed for this specific integration scenario. Several users initially considered Option A but switched to C upon realizing that federated queries are slower and shift processing burden to the source database. Commenters highlighted that while read replicas could help with federated queries, replication to BigQuery is the superior architectural pattern for high-frequency analytics.

Official Reference

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

Exam Strategy

When questions ask to 'minimize load' on a transactional database (Cloud SQL) while performing analytics in BigQuery, look for data replication (Datastream) rather than direct querying (Federated Query).

Community Votes

C
100%

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

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

  1. raaad 👍 11 Answered C
    - Datastream: It's a fully managed, serverless service for real-time data replication. It allows to stream data from various sources, including Cloud SQL, into BigQuery. - Reduced Load on Cloud SQL: By replicating the required tables from both Cloud SQL databases into BigQuery, you minimize the load on the Cloud SQL instances. The marketing team's queries will be run against BigQuery, which is designed to handle high-volume analytics workloads. - Frequency of Queries: BigQuery can easily handle the high frequency of queries (100 times daily, up to 300 during sales events) due to its powerful data processing capabilities. - Combining Data Sources: Once the data is in BigQuery, you can efficiently combine it with the Google Analytics data for comprehensive analysis and campaign planning.
  2. 987af6b 👍 3 Answered C
    Initially I said A, but this question was how I learned about Datastream, which I think would be the better solution in this scenario. So my answer is C
  3. scaenruy 👍 3 Answered C
    C. Create streams in Datastream to replicate the required tables from both Cloud SQL databases to BigQuery for these queries.
  4. joao_01 👍 1
    Its option C. "Performance. A federated query is likely to not be as fast as querying only BigQuery storage. BigQuery needs to wait for the source database to execute the external query and temporarily move data from the external data source to BigQuery. Also, the source database might not be optimized for complex analytical queries." So, it will load the Cloud SQL external sources with the queries, impacting performance on those. Link: https://cloud.google.com/bigquery/docs/federated-queries-intro
  5. AlizCert 👍 1 Answered C
    C, noting that federated queries on read replicas would be the ideal solution
Show 3 more comments
  1. Blackstile 👍 1 Answered C
    To Replication data, use datastream
  2. datasmg 👍 1 Answered C
    C is make sense
  3. JyoGCP 👍 1 Answered C
    Option C

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