PCDE Practice Test 4 — 25 Questions

Question 1

A financial services company uses BigQuery to run complex analytical queries on trading data. They notice that a particular query joining a large fact table (10 TB) with a small dimension table (100 MB) is slow. The fact table is partitioned by date and clustered by symbol. The dimension table is not partitioned. The query filters on a specific date range and a few symbols. Which optimization is MOST likely to improve query performance?

Accepted Answer

Enable automatic query rewriting to use clustering keys for pruning on the dimension table join.. Option B is correct because BigQuery's automatic query rewriting can leverage clustering keys from the fact table to prune the join, even though the dimension table is not clustered. When the query filters on a specific date range and symbols, BigQuery can use the fact table's clustering on symbol to skip irrelevant blocks during the join, reducing data scanned and improving performance. This optimization is automatic and does not require manual denormalization or repartitioning.

Answer

Denormalize the dimension table into the fact table.

Answer

Partition the dimension table by its primary key.

Answer

Cluster the dimension table on its primary key.

Question 2

When designing a schema for a data warehouse in BigQuery, which table type is most cost-effective for storing raw event data that will be queried by date range filters?

Accepted Answer

A partitioned table partitioned by date column. Partitioned tables in BigQuery allow you to divide a table into segments based on a date, timestamp, or integer column. When querying with a date range filter, BigQuery can prune partitions, scanning only the relevant data rather than the entire table. This dramatically reduces the amount of data processed, lowering query costs and improving performance, making it the most cost-effective choice for storing raw event data that is frequently queried by date.

Answer

A table with integer range partitioning on an ID column

Answer

A regular table with no partitioning

Answer

A regular table clustered on timestamp

Question 3

Refer to the exhibit. Which BigQuery SQL query correctly flattens the items into rows?

Accepted Answer

SELECT * FROM orders CROSS JOIN UNNEST(items) AS items. Option D is correct because `CROSS JOIN UNNEST(items)` is the standard BigQuery syntax to flatten a repeated (array) column into individual rows. The `UNNEST` operator expands each array element into a separate row, and `CROSS JOIN` ensures that all non-array columns from the `orders` table are preserved alongside each element. This is the only option that correctly transforms the nested `items` array into a normalized row-per-item structure.

Answer

SELECT * FROM orders WHERE items IS NOT NULL

Answer

SELECT * FROM orders, UNNEST(items) AS items

Answer

SELECT * FROM orders INNER JOIN items ON true

Question 4

A team is designing a schema for a user activity logging system using Bigtable. Each log entry includes a user ID, activity type, timestamp, and details. The access pattern requires retrieving all activities for a specific user within a time range. Which TWO row key designs are suitable? (Choose TWO.)

Accepted Answer

user_id#activity_type#timestamp. Option D (user_id#activity_type#timestamp) is correct because it groups all activities for a user under a single row key prefix, enabling efficient row range scans for a specific user. The activity_type suffix allows filtering by activity type if needed, while the timestamp ensures uniqueness and ordered storage. Option E (user_id#timestamp) is correct because it directly supports the access pattern of retrieving all activities for a user within a time range by scanning rows with the user_id prefix and filtering on the timestamp component.

Answer

timestamp#user_id

Answer

random_uuid

Answer

reverse_timestamp

Question 5

Match each Cloud SQL backup type to its retention policy.

Accepted Answer

Automated backups: Retained for a user-specified number of days (default 7). Cloud SQL backup types: Automated backups have configurable retention (default 7 days). On-demand backups are retained until deleted (max 365 days). Point-in-time recovery logs are kept for the same period as automated backup retention. Common confusions involve swapping these policies.

Answer

Automated backups: Retained until deleted

Answer

On-demand backups: Retained for 7 days by default

Answer

Point-in-time recovery logs: Retained for 365 days regardless of backup retention

Question 6

An organization wants to use a GitOps workflow for infrastructure deployment with Terraform. They use GitHub as the source of truth and want to automatically apply Terraform changes when a pull request is merged to the main branch. They need to review Terraform plans before apply. Which solution meets these requirements?

Accepted Answer

Use Terraform Cloud with VCS integration: plan on pull request, apply on merge to main.. Terraform Cloud / Enterprise supports VCS-driven workflows with speculative plans on PRs and automatic applies on merges. Atlantis is a self-hosted option that also does plan on PR and apply on merge. Cloud Build would require custom scripting. Cloud Source Repositories is a Git host but lacks native plan/apply workflow.

Answer

Use Cloud Source Repositories and Cloud Functions to trigger Terraform on merge.

Answer

Use Terraform workspaces with remote state and manual apply after merging.

Answer

Configure Cloud Build triggers to run `terraform apply` on every push to main.

Question 7

You are designing a database architecture for a global e-commerce application. The application requires low-latency reads in multiple regions and must handle up to 100,000 writes per second globally. Which Google Cloud database solution should you use?

Accepted Answer

Cloud Spanner with multi-region configuration.. Cloud Spanner with a multi-region configuration is the correct choice because it provides globally distributed, strongly consistent relational database service with horizontal scalability, supporting over 100,000 writes per second across multiple regions while maintaining ACID transactions and low-latency reads via regional replicas. This meets the requirements of a global e-commerce application needing high write throughput and low read latency in multiple regions.

Answer

Firestore in multi-region mode.

Answer

Bigtable with multiple clusters.

Answer

Cloud SQL with cross-region replication.

Question 8

You run the above command to create a Spanner instance. Later, you need to increase the instance's compute capacity to handle higher traffic. What is the correct approach?

Accepted Answer

Run gcloud spanner instances update test-instance --nodes=5. Option D is correct because Cloud Spanner allows you to increase the compute capacity of an existing instance by updating the node count using the `gcloud spanner instances update` command. This operation is performed online without downtime, as Spanner supports live resizing of nodes to handle increased traffic.

Answer

Use the console to change the instance configuration to a larger one.

Answer

Delete the instance and recreate with --nodes=5.

Answer

Create a new instance with a larger config and migrate data.

Question 9

A gaming company uses Memorystore for Redis to cache player session data. They need to ensure high availability with automatic failover in case of a zone failure. Which configuration should the Database Engineer choose?

Accepted Answer

Deploy a Standard tier Redis instance with replication across two zones.. Memorystore for Redis Standard tier provides cross-zone replication with automatic failover, ensuring high availability during a zone failure. The Standard tier uses a primary and replica instance in different zones, and if the primary fails, the replica is automatically promoted. This meets the requirement for automatic failover without manual intervention.

Answer

Deploy a Basic tier Redis instance with multiple read replicas.

Answer

Deploy a Memcached cluster with multiple nodes.

Answer

Deploy a Basic tier Redis instance in a single zone.

Question 10

A Database Engineer is responsible for managing a Cloud SQL for MySQL instance. The engineer needs to ensure that automated backups are retained for 14 days and that point-in-time recovery (PITR) is enabled. Which configuration should the engineer set?

Accepted Answer

Enable automated backups, set backup retention to 14 days, and enable binary logging.. Option B is correct because enabling automated backups with a 14-day retention ensures backup files are kept for the required duration, and enabling binary logging (which is required for PITR) allows point-in-time recovery by replaying transaction logs against a base backup. In Cloud SQL for MySQL, PITR is not automatically enabled with automated backups; binary logging must be explicitly enabled.

Answer

Create scheduled manual backups each day and retain them for 14 days; PITR is not needed because backups are daily.

Answer

Enable automated backups with a retention of 14 days; PITR is automatically enabled.

Answer

Enable automated backups with 14-day retention and set up a cross-region replica for disaster recovery.

Question 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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