Daily terabytes of Google Analytics event data land in BigQuery. Customer profile tables live in Cloud SQL for MySQL and Cloud SQL for PostgreSQL. Marketing needs to run joined campaigns 100–300 times/day while minimizing load on the Cloud SQL instances. What do you implement?

Creating Datastream streams to replicate the required tables from both Cloud SQL databases into BigQuery is the most effective solution. Datastream provides Change Data Capture (CDC) to continuously and efficiently replicate data from operational databases like Cloud SQL into BigQuery. This minimizes the load on the Cloud SQL instances because the analytical queries (100-300 times/day) will run against the replicated data in BigQuery, not directly against the transactional Cloud SQL databases. BigQuery is optimized for large-scale analytical queries, making it ideal for joining with the Google Analytics data. Using BigQuery federated queries would directly query Cloud SQL for each campaign, placing a significant and potentially disruptive load on the operational databases. An Apache Spark job on Dataproc Serverless or a Dataproc cluster with Trino would also query Cloud SQL directly for each execution, leading to similar load issues and increased latency compared to pre-replicating the data.