PCDE Practice Test 2 — 30 Questions

Question 1

A retail company uses Cloud Spanner to handle global transaction processing. The database has a single regional instance in us-central1. The company expects a 10x increase in write traffic from a new mobile app. The database engineer needs to design for low latency writes globally and high availability. What should the Database Engineer do?

Accepted Answer

Change the instance configuration to a multi-region configuration like nam3 (us-central1, us-east1, us-west1) and configure a dedicated write region.. Option C is correct because a multi-region configuration like nam3 (us-central1, us-east1, us-west1) with a dedicated write region provides low-latency writes globally by using Google's managed replication and automatic failover. This design ensures high availability and meets the 10x write traffic increase without sacrificing write performance, as writes are processed in the designated write region and asynchronously replicated to other regions.

Answer

Shard the database across multiple regional instances based on user geography.

Answer

Create read replicas in other regions to offload read traffic and keep writes in the primary region.

Answer

Increase the number of nodes in the existing regional instance to handle the increased write capacity.

Question 2

A startup is using Cloud SQL for PostgreSQL and wants to minimize downtime during maintenance. The application can tolerate a few minutes of read-only mode. Which configuration should they use?

Accepted Answer

Use a read replica and promote it during maintenance.. Option A is correct because using a read replica and promoting it during maintenance allows the application to switch to a read-write capable instance with minimal downtime. The application can tolerate a few minutes of read-only mode, so the brief period when the replica is promoted and the original primary is unavailable is acceptable. This approach avoids the longer downtime associated with other methods like HA failover or simply waiting for maintenance to complete.

Answer

Enable automatic storage increase.

Answer

Configure a high availability (HA) instance with regional failover.

Answer

Schedule maintenance during off-peak hours only.

Question 3

A Database Engineer is designing a tiered storage strategy for a large BigQuery dataset. The dataset contains data that is accessed frequently for the first 30 days, moderately for the next 6 months, and rarely after that. The engineer wants to minimize overall storage cost while maintaining fast query performance on recent data. Which approach should the engineer take?

Accepted Answer

Use physical storage billing, partition the table by ingestion date, cluster on frequently used columns, and set an expiration on partitions older than 6 months to move them to long-term storage.. Option D is correct because physical storage billing in BigQuery charges separately for active and long-term storage, and setting a partition expiration on partitions older than 6 months automatically moves that data to long-term storage at a lower cost. Partitioning by ingestion date and clustering on frequently used columns ensures fast query performance on recent data while minimizing overall storage costs.

Answer

Use logical storage billing and partition the table by date; older data automatically moves to long-term storage after 90 days.

Answer

Use Bigtable for recent data and BigQuery for historical data, with a scheduled transfer between them.

Answer

Export data older than 30 days to Cloud Storage as Avro files and delete them from BigQuery; query them externally when needed.

Question 4

A company uses BigQuery for BI reporting. They have a table 'orders' with columns: order_id, customer_id, order_date, amount, status. The BI team frequently runs queries that filter on order_date and group by customer_id to compute total sales per customer. Which partitioning and clustering strategy optimizes query performance and cost?

Accepted Answer

Partition by order_date, cluster by customer_id. Option D is correct because partitioning by order_date allows BigQuery to prune partitions for queries filtering on order_date, reducing the amount of data scanned. Clustering by customer_id organizes data within each partition so that GROUP BY customer_id queries can efficiently read only relevant blocks, minimizing shuffle and cost. This combination directly aligns with the BI team's query pattern of filtering by date and aggregating by customer.

Answer

Partition by order_date, cluster by status

Answer

Do not partition, cluster by customer_id

Answer

Partition by customer_id, cluster by order_date

Question 5

The exhibit shows IAM policy for a BigQuery dataset. The BI team reports they can query tables but cannot create views. What is the missing role?

Accepted Answer

roles/bigquery.dataEditor. The BI team can query tables but cannot create views, which requires write access to the dataset. The `roles/bigquery.dataEditor` role grants permissions to read, create, update, and delete datasets, tables, and views, including the `bigquery.tables.create` and `bigquery.tables.update` permissions necessary for view creation. The existing query capability indicates they have at least `roles/bigquery.dataViewer`, but view creation demands the additional write permissions provided by `dataEditor`.

Answer

roles/bigquery.admin

Answer

roles/bigquery.metadataViewer

Answer

roles/bigquery.user

Question 6

A financial services company uses BigQuery for risk analysis. They have a table `market_data` with columns `symbol`, `date`, `price`, and `volume`. The query pattern involves window functions over the last 30 days for many symbols. The table is partitioned by date and clustered by symbol. However, analysts report that queries are slow and expensive. What is the most likely cause?

Accepted Answer

Clustering on symbol may cause many blocks to be scanned because symbols are not sorted. Option B is correct because clustering in BigQuery does not physically sort data within partitions; it only co-locates rows with similar cluster column values. When a query uses window functions over a rolling 30-day window for many symbols, BigQuery must scan all blocks that contain any of those symbols, even if only a subset of rows is needed. Since symbols are not strictly sorted, many blocks contain multiple symbols, leading to excessive block scans and high query costs.

Answer

Clustering does not create indexes on symbol

Answer

Partitioning causes data skew across partitions

Answer

Partitioning by date is not granular enough

Question 7

A data engineer is designing a BigQuery schema for a time-series dataset of IoT sensor readings. The queries will filter primarily on a timestamp column and also on sensor_id. To optimize query performance and cost, which table design is best?

Accepted Answer

Partition by timestamp, cluster by sensor_id. Partitioning by timestamp allows BigQuery to prune entire partitions when queries filter on the timestamp column, reducing the amount of data scanned and thus lowering cost and improving performance. Clustering by sensor_id further organizes data within each partition, enabling block-level pruning for queries that filter on sensor_id. This combination optimizes for the primary filter (timestamp) and secondary filter (sensor_id) without the overhead of excessive partitions.

Answer

Partition by sensor_id, cluster by timestamp

Answer

Partition by timestamp, cluster by timestamp

Answer

No partitioning, cluster by timestamp

Question 8

A data engineer runs a BigQuery query that joins a large fact table with a small lookup table. The query processes 1 TB of data and takes 30 seconds. The engineer wants to reduce the amount of data processed. Which optimization technique is MOST effective?

Accepted Answer

Use a WITH clause to pre-filter the fact table before joining.. Option B is correct because pre-filtering the fact table with a WITH clause (CTE) reduces the amount of data scanned and processed before the join occurs. Since the fact table is large (1 TB), applying filters early minimizes the data shuffled and joined, directly reducing the bytes billed in BigQuery. This is a form of predicate pushdown that leverages BigQuery's columnar storage and dynamic query optimization.

Answer

Increase the number of slots available for the query.

Answer

Cluster the lookup table on the join key.

Answer

Materialize the lookup table as a separate table with the same data.

Question 9

A data engineer is designing a BI solution in BigQuery for a retail chain. They need to support queries that aggregate sales by store, product, and date across millions of transactions. The data is loaded in near real-time from Cloud Pub/Sub. Which table design provides the best balance of query performance and cost?

Accepted Answer

Partition by date, cluster by store_id and product_id. Option B is correct because partitioning by date enables BigQuery to prune entire partitions when querying by date range, which is the most common filter in sales aggregation queries. Clustering on store_id and product_id further reduces the data scanned within each partition by colocating rows with similar store and product values. This design minimizes both query cost (bytes billed) and latency, while supporting near-real-time ingestion from Pub/Sub without requiring table rewrites.

Answer

Partition by store_id, cluster by product_id

Answer

Unpartitioned table with clustering on store_id and product_id

Answer

Use materialized views with aggregation on store_id, product_id, and date

Question 10

A financial institution uses BigQuery for BI reporting. They have a table 'transactions' (10 TB) partitioned by transaction_date and clustered by customer_id. A common report filters on customer_id and last 30 days. The report is slow. Which change would most improve query performance for this specific report?

Accepted Answer

Add clustering on transaction_date in addition to customer_id. Option C is correct because adding clustering on transaction_date alongside customer_id improves query performance for the specific report that filters on both customer_id and the last 30 days. BigQuery uses clustering to sort data within partitions, so clustering by transaction_date ensures that within each partition, the rows for the last 30 days are colocated, reducing the amount of data scanned. This complements the existing partition pruning by further narrowing the scan to relevant blocks.

Answer

Change partition column to customer_id

Answer

Remove clustering and rely only on partitioning

Answer

Manually recluster the table daily

Question 11

A multinational corporation uses BigQuery to combine sales data from multiple regions. Each region stores data in separate tables with identical schemas. The BI team needs to create a unified view for a dashboard that queries data by region and product. Which TWO strategies should the data engineer implement to optimize query performance and reduce costs?

Accepted Answer

Partition the table by date and cluster by region and product. Option A is correct because partitioning the table by date and clustering by region and product allows BigQuery to use partition pruning and clustering block elimination to scan only the relevant data for queries filtered by region and product. This directly reduces the amount of data read, lowering query costs and improving performance. Clustering also sorts data within partitions, enabling efficient filtering without full scans.

Answer

Create a view with UNION ALL of all region tables

Answer

Create materialized views for each region

Answer

Store all data in a single table with region as a column

Question 12

A BI team wants to create a report that shows daily active users for the last 7 days. Which SQL construct is most appropriate for fast performance on a large dataset?

Accepted Answer

SELECT APPROX_COUNT_DISTINCT(user_id) ... WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY). Option D is correct because APPROX_COUNT_DISTINCT uses HyperLogLog (HLL) algorithm, which provides near-exact distinct counts with significantly less memory and faster performance than COUNT(DISTINCT) on large datasets. This is ideal for a daily active users report over 7 days where exact precision is not critical.

Answer

SELECT COUNT(DISTINCT user_id) ... WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

Answer

SELECT DISTINCT user_id ...

Answer

SELECT COUNT(user_id) ... GROUP BY user_id

Question 13

Your company runs a critical PostgreSQL database on Cloud SQL. You need to minimize downtime during a schema migration that could take up to 30 minutes. What should you do?

Accepted Answer

Create a clone of the instance, perform the migration on the clone, then promote the clone.. Option B is correct because creating a clone of the Cloud SQL instance allows you to perform the schema migration on an isolated copy without affecting the production database. Once the migration is complete and verified, you can promote the clone to take over as the primary instance, minimizing downtime to just the brief promotion switchover (typically seconds). This approach avoids the long 30-minute migration window on the live database.

Answer

Use Database Migration Service to migrate to a new instance during the migration window.

Answer

Configure a high-availability instance and perform the migration during a planned failover.

Answer

Add a read replica, perform the migration on the replica, then promote it.

Question 14

Refer to the exhibit.

Which of the following statements is true regarding this schema design?

Accepted Answer

Deleting a Singer row will automatically delete all associated Album rows.. Option A is correct because the `ON DELETE CASCADE` clause on the foreign key from `Albums` to `Singer` ensures that when a row in the `Singer` table is deleted, all rows in the `Albums` table that reference that singer are automatically deleted. This is a standard referential integrity behavior in relational databases, and in Cloud Spanner (the technology context for PCDE), it is enforced at the database level to maintain consistency.

Answer

The Albums table cannot have any secondary indexes because of the INTERLEAVE clause.

Answer

The Albums table's rows are physically stored independent of the Singer table.

Answer

The Albums table's primary key must include the SingerId column only.

Answer

The ON DELETE CASCADE clause ensures that deleting an Album row will delete the corresponding Singer row.

Question 15

Arrange the steps to set up database encryption with Cloud KMS for Cloud SQL.

Question 16

Arrange the steps to configure high availability for a Cloud SQL for MySQL instance.

Question 17

Arrange the steps to create and connect to a Cloud SQL for PostgreSQL instance using the gcloud command-line tool.

Question 18

Order the steps to export data from Cloud Bigtable to Cloud Storage using Dataflow.

Question 19

Order the steps to perform a disaster recovery drill for a Cloud Spanner database using backups.

Question 20

Match each Cloud SQL high-availability feature to its description.