PCDE Practice Test 5 — 10 Questions

Question 1

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 2

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 3

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 4

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 5

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

Question 6

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

Question 7

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

Question 8

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

Question 9

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

Question 10

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