A reporting team wants to join a live customer table in a Cloud SQL for PostgreSQL instance with BigQuery sales data, without copying the customer table into BigQuery. Which capability should it use?
Show answer and explanation
Answer: A
BigQuery federated queries let SQL in BigQuery pass a query to a registered Cloud SQL connection through EXTERNAL_QUERY. The query runs against the PostgreSQL data where it lives, and the result rows come back into BigQuery, where they can be joined with the sales tables. Nothing is copied or exported ahead of time, so results reflect the current customer table. Exports and external tables over files work with different sources.
Why the other options are wrong
- B. Exporting to CSV creates a stale copy in Cloud Storage. It adds a pipeline to maintain and does not query the live Cloud SQL data.
- C. BigQuery Omni runs BigQuery analytics over data in other clouds such as AWS or Azure. It is not the feature for reaching into a Cloud SQL instance in Google Cloud.
- D. BigLake tables read files in object storage. They cannot reach into a relational database instance, so they do not solve this join.