Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
A retail company runs its transactional order system on a Cloud SQL for PostgreSQL instance in us-east4 and stores historical sales data in a BigQuery dataset in us-east4. Data analysts need to join live order data with historical sales in real time without building an ETL or replication pipeline.
Your design must meet these requirements:
Which architecture should you implement?
us-east4 BigQuery dataset referencing the primary Cloud SQL instance URI through a Cloud resource connection, and grant analysts the roles/bigquery.dataViewer role.BigLake tables are BigQuery external data source tables designed to unify data lakes and warehouses by querying structured files in object storage with access delegation. Using a Google Cloud resource connection associated with a service account, BigLake decouples user permissions on the BigQuery table from permissions on the underlying storage objects, enabling row-level security, column-level security, and dynamic data masking.
This architecture would be appropriate if your data consisted of structured analytical files (such as Parquet or Iceberg) stored in Cloud Storage, Amazon S3, or Azure Blob Storage, and you needed to enforce fine-grained row- and column-level access controls without granting analysts direct access to the storage buckets.
US multi-region linked directly to the Cloud SQL instance, grant analysts the roles/bigquery.dataViewer role, and join the exposed tables using standard GoogleSQL.BigQuery external datasets automatically expose schemas and tables from supported external platforms directly inside BigQuery Studio. This allows analysts to discover tables and run standard GoogleSQL queries, joins, and subqueries without explicitly invoking the EXTERNAL_QUERY function or writing in a remote SQL dialect.
EXTERNAL_QUERY.us-east4) cannot reference resources or connections located in the US multi-region; the query processing location and connection location must match. Cross-region querying also incurs higher latency and a 1 TB per project per day cross-region federated query limit.EXTERNAL_QUERY.us-east4).COLLATE, whereas filters inside EXTERNAL_QUERY use the source database's collation.This approach would work if your operational database were Spanner instead of Cloud SQL, your BigQuery dataset location matched your query execution location, and you wanted analysts to query tables directly in GoogleSQL using Data Boost isolation.
us-east4, configure a BigQuery external connection to the replica, grant analysts the roles/bigquery.connectionUser role, and query via EXTERNAL_QUERY with column selection and WHERE filters inside the external SQL statement.BigQuery federated queries allow analysts to send read-only SQL statements directly to operational databases like Cloud SQL using the EXTERNAL_QUERY function without migrating or replicating data. The BigQuery Connection API establishes an encrypted connection resource that stores database credentials securely, while BigQuery translates the returned rows into GoogleSQL data types for seamless joins with native BigQuery tables.
EXTERNAL_QUERY executes live queries against PostgreSQL on demand, eliminating the need to configure Datastream, Dataflow, or batch export jobs.roles/bigquery.connectionUser) IAM role to invoke the connection, while the BigQuery Connection service agent uses the Cloud SQL Client (roles/cloudsql.client) role.WHERE filters (and aggregations) inside the EXTERNAL_QUERY SQL statement reduces the volume of data streamed over the network. Colocating the replica, connection, and dataset in us-east4 keeps latency low and avoids cross-region query limits.SELECT * FROM table patterns (column pruning and filter pushdowns) alongside explicit inner SQL tuning.information_schema views directly through EXTERNAL_QUERY.Unlike BigLake tables or external datasets, which do not support Cloud SQL, federated queries via EXTERNAL_QUERY provide native real-time integration with Cloud SQL for PostgreSQL. Routing connections through a colocated read replica protects production transactions while maintaining low-latency, cost-efficient analytics.
EXTERNAL_QUERY with query_execution_priority set to low, and apply GROUP BY aggregations in the outer BigQuery query.query_execution_priority and outer aggregations doThe query_execution_priority JSON option in EXTERNAL_QUERY allows callers to tag federated queries as low, medium, or high priority. Placing GROUP BY clauses in the outer BigQuery query causes BigQuery to perform grouping and aggregation after receiving the result set from the external database.
query_execution_priority setting is supported only for Spanner federated queries and cannot be used to deprioritize Cloud SQL workloads.EXTERNAL_QUERY, BigQuery's SQL pushdowns only support column pruning and filter pushdowns (WHERE clauses) on SELECT * FROM table patterns. Compute, join, LIMIT, ORDER BY, and GROUP BY aggregation pushdowns are not supported. Running GROUP BY in the outer query forces PostgreSQL to send unaggregated rows across the network.low priority queries are best-effort and can be preempted by background tasks like backups, which can cause BigQuery job timeouts.EXTERNAL_QUERY SQL string so that the source database reduces the row count before network transfer.EXTERNAL_QUERY only accepts read-only SELECT statements.Setting query_execution_priority would only be valid when querying Cloud Spanner via EXTERNAL_QUERY (though Data Boost is preferred for isolation), and aggregations would still belong inside the inner query to optimize network bandwidth.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated