PCDE Practice Questions 1 — 50 Questions

Question 1

A company stores sensor data in BigQuery. They have a table 'sensor_readings' with columns: sensor_id, reading_time, value. The table is partitioned by reading_time (hourly) and clustered by sensor_id. A BI query aggregates average value per sensor for the last week. The query still scans many bytes. What is the most likely cause?

Accepted Answer

Partition granularity is too fine for the query range. Option D is correct because the query scans a full week of data (168 hourly partitions), and each partition must be read entirely even though only a subset of sensors may be active. Hourly partitioning over a 7-day range means the query engine must scan all 168 partitions, which can result in a large number of bytes being processed. Clustering on sensor_id helps within each partition but does not reduce the number of partitions scanned; the fine granularity of hourly partitioning is the primary cause of excessive bytes scanned.

Answer

The query uses SELECT * instead of specific columns

Answer

Clustering on sensor_id is ineffective

Answer

The table is not using columnar storage

Question 2

Which THREE are valid considerations when designing BigQuery tables for BI reporting?

Accepted Answer

Use partitioning on date columns to reduce query cost. Option C is correct because partitioning BigQuery tables by date columns (e.g., using _PARTITIONTIME or a DATE/TIMESTAMP column) allows the query engine to prune entire partitions during query execution. This significantly reduces the amount of data scanned, directly lowering query costs (since BigQuery charges per byte processed) and improving performance for time-range filters.

Answer

Use nested and repeated fields to avoid JOINs

Answer

Create indexes on frequently queried columns

Question 3

A team is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The existing schema uses a large number of foreign key constraints and triggers for data validation. The team wants to minimize migration effort and maintain data integrity. Which schema design approach is most appropriate for Cloud SQL?

Accepted Answer

Keep the existing foreign keys and triggers as-is in Cloud SQL for PostgreSQL. Option A is correct because Cloud SQL for PostgreSQL is fully compatible with the PostgreSQL engine, meaning foreign key constraints and triggers operate identically to on-premises PostgreSQL. This approach minimizes migration effort by preserving the existing schema logic and maintaining referential integrity without requiring application changes or data validation rewrites.

Answer

Migrate to Cloud Spanner and use interleaved tables to simulate foreign keys

Answer

Remove all foreign keys and triggers and implement validation in the application layer

Answer

Convert the schema to use Firestore in Datastore mode with composite indexes

Question 4

A company is designing a Cloud Firestore schema for a social media application. Users can follow other users, and the application needs to display a feed of posts from followed users ordered by timestamp. Which schema design is most cost-effective and performant for querying the feed?

Accepted Answer

Store a feed subcollection under each user document containing references to posts from followed users.. Option B is correct because it uses a feed subcollection under each user document to store pre-computed references to posts from followed users. This design avoids expensive collection-group queries or multiple individual queries per followed user, ensuring that fetching the feed is a single, indexed read operation ordered by timestamp, which is both cost-effective and performant at scale.

Answer

Store all posts in a top-level collection and query for posts where user ID is in the list of followed users, ordered by timestamp.

Answer

Store all user posts in an array within a single document and use array-contains queries.

Answer

Store a 'follows' collection with documents containing follower and followed user IDs; then query posts for each followed user.

Question 5

Match each Cloud SQL tier to its description.

Question 6

A financial services company uses Cloud Spanner for a global transaction processing system. They notice that certain read queries on a table with frequent writes are returning stale data even though they use strong reads. The table has a primary key of (user_id, transaction_id) and a secondary index on (timestamp). What is the most likely cause of the stale reads?

Accepted Answer

The query is using a secondary index that has not yet been updated with the latest write.. Option B is correct because in Cloud Spanner, secondary indexes are implemented as separate tables that are updated asynchronously relative to the base table. When a strong read uses a secondary index, the read may still see a stale version of the index if the write has not yet been fully replicated to the index table. This is a known behavior: strong reads guarantee consistency only when reading from the base table using the primary key, not when using a secondary index.

Answer

The query is using a stale read timestamp.

Answer

The query is reading from a read-only replica.

Answer

Cloud Spanner is using eventual consistency for this query.

Question 7

Your Cloud SQL for MySQL instance is experiencing intermittent performance degradation. You suspect that the issue is due to a sudden spike in connections from a specific application. Which metric and monitoring approach would best help you correlate the connection spike with performance degradation?

Accepted Answer

Monitor 'cloudsql.googleapis.com/database/mysql/threads/threads_connected' and correlate with CPU utilization and query latency.. Option D is correct because the 'threads_connected' metric directly measures the number of active connections to the MySQL instance. Correlating this with CPU utilization and query latency allows you to pinpoint whether a sudden spike in connections is causing resource contention and degraded query performance, which is the exact scenario described.

Answer

Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and compare with connection count.

Answer

Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master' and compare with query latency.

Answer

Monitor 'cloudsql.googleapis.com/instance/uptime' and check for instance restarts during degradation.

Question 8

A company uses Cloud Spanner. The backup service account 'sa-backup' needs to create and manage backups of the 'orders' database. However, backup creation fails with a permission error. What is the most likely cause?

Accepted Answer

The backup role must be granted at the instance level, not on the database.. Option C is correct because Cloud Spanner backup permissions must be granted at the instance level, not on the database itself. The service account 'sa-backup' needs the `spanner.backups.create` permission on the instance resource to create backups, and assigning a role like `roles/spanner.databaseBackupAdmin` at the database level does not propagate the necessary instance-level permissions, causing the backup creation to fail with a permission error.

Answer

The service account lacks the spanner.databases.read permission.

Answer

The service account is assigned the role roles/spanner.databaseBackupAdmin, which is a custom role that does not include the spanner.backups.create permission.

Answer

The instance 'orders-db' is in a regional configuration, which does not support backups.

Question 9

A Cloud Database Engineer is designing a schema for an e-commerce application on Cloud Spanner. The application requires high read throughput for product queries by category and price range, and must support global scale with strong consistency. The team is considering primary key design and interleaved tables. Which TWO design considerations should the engineer apply? (Choose TWO.)

Accepted Answer

Use a primary key that starts with the category column to colocate product data for efficient queries by category.. Option D is correct because colocating product data by category in the primary key enables efficient range scans on category and price, as Cloud Spanner stores rows in sorted order by primary key. This design minimizes cross-node fan-out for queries filtering by category, directly supporting high read throughput at global scale with strong consistency.

Answer

Define secondary indexes on price and category columns to support range queries without considering the primary key design.

Answer

Use a timestamp as the first part of the primary key to enable time-based partitioning and efficient range scans.

Answer

Define interleaved tables for all related entities, even if they are not always accessed together, to reduce joins.

Question 10

Arrange the steps to perform a point-in-time recovery (PITR) for a Cloud SQL instance.

Question 11

Order the steps to migrate an on-premises MySQL database to Cloud SQL using Database Migration Service (DMS).

Question 12

Arrange the steps to import data from Cloud Storage into Cloud Firestore using a managed import.

Question 13

A company is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL. The database is 2 TB in size and the network bandwidth to Google Cloud is limited to 500 Mbps. The migration window is 48 hours. Which migration strategy should the Database Engineer recommend?

Accepted Answer

Export the database to flat files, compress, upload to Cloud Storage, then import to Cloud SQL.. Option C is correct because the 2 TB database size and 500 Mbps bandwidth yield a theoretical transfer time of approximately 9.5 hours (2 TB * 1024 GB/TB * 8 bits/byte / 500 Mbps / 3600 seconds/hour), which fits within the 48-hour window. However, pg_dump/pg_restore over a VPN (Option A) would be slower due to TCP overhead and latency, and Database Migration Service with continuous replication (Option B) requires ongoing connectivity and may not complete the initial load within the window. Exporting to flat files, compressing them (e.g., with gzip), uploading to Cloud Storage, and then importing to Cloud SQL leverages high-throughput parallel uploads and avoids network latency issues, making it the most reliable strategy for a one-time migration within the given constraints.

Answer

Create a VPN tunnel and use pg_dump/pg_restore over the network.

Answer

Use Database Migration Service with continuous replication.

Answer

Request a dedicated interconnect and then migrate.

Question 14

A company runs a production Cloud SQL for PostgreSQL instance used by a web application. The instance experiences intermittent latency spikes during peak hours. You need to diagnose the cause without downtime. Which tool should you use first?

Accepted Answer

Use Cloud SQL Insights to analyze query performance and identify slow queries.. Cloud SQL Insights provides built-in query performance monitoring and diagnostics without requiring any downtime. It surfaces slow queries, lock contention, and resource bottlenecks directly from the PostgreSQL engine, making it the ideal first step to identify the root cause of intermittent latency spikes during peak hours.

Answer

Use Database Migration Service to failover to a read replica.

Answer

Use gcloud sql instances describe to check instance configuration.

Answer

Use VPC Flow Logs to analyze network traffic.

Question 15

A financial services company uses Cloud Spanner for transaction processing. They need to ensure zero downtime during a schema change that adds a new column with a default value to a large table. Which approach should the Database Engineer take?

Accepted Answer

Use ALTER TABLE to add the column; Spanner handles schema changes online.. Option D is correct because Cloud Spanner supports online schema changes, including adding columns with default values, without requiring a maintenance window or causing downtime. Spanner applies schema updates asynchronously across all nodes while the database remains fully available for reads and writes, making it the ideal approach for zero-downtime requirements.

Answer

Create a new table with the new column, then use a fan-out pattern to write to both tables until the old table is deprecated.

Answer

Use an ALTER TABLE statement during a maintenance window.

Answer

Drop the table and recreate it with the new schema.

Question 16

Your company runs a global application on Cloud Spanner. You notice that recent schema changes have caused a significant increase in latency for cross-node transactions. The previous schema used interleaved tables for parent-child relationships, but the new schema uses separate tables with foreign keys. What is the most likely cause of the increased latency?

Accepted Answer

The new schema uses foreign keys that require cross-node transactions.. The new schema uses separate tables with foreign keys instead of interleaved tables. In Cloud Spanner, interleaved tables guarantee that parent and child rows are co-located on the same split, allowing local joins without cross-node communication. Foreign keys between non-interleaved tables can reference rows stored on different splits, forcing distributed transactions that require two-phase commit across nodes, which significantly increases latency.

Answer

The new schema does not use commit timestamps for versioning.

Answer

The new schema lacks secondary indexes on foreign key columns.

Answer

The Spanner instance was not resized after the schema change.

Question 17

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 18

A company is designing a BigQuery data model for a business intelligence dashboard that shows sales by region and product. The data is refreshed daily. Which schema design is MOST cost-effective and performant for this use case?

Accepted Answer

A star schema with a fact table for sales and separate dimension tables for region and product.. Option B is correct because a star schema with a fact table for sales and dimension tables for region and product is optimized for analytical queries in BigQuery. Option A is wrong because a flat table with all columns leads to higher storage costs and slower queries due to scanning unnecessary columns. Option C is wrong because a wide table with nested columns is better for hierarchical data, not for simple dimensional analysis. Option D is wrong because a normalized schema with many joins is not ideal for BI queries and increases complexity.

Answer

A table with nested repeated columns for regions and products within each sale.

Answer

A fully normalized schema with separate tables for each attribute.

Answer

A single flat table containing all sales, region, and product columns.

Question 19

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 20

You are running a Memorystore for Redis instance with a high write volume. You notice that the eviction rate is high and the cache hit ratio has dropped significantly. Which configuration change would most directly reduce the eviction rate?

Accepted Answer

Change maxmemory-policy to allkeys-lru.. Option D is correct because setting `maxmemory-policy` to `allkeys-lru` tells Redis to evict the least recently used keys from the entire keyspace when memory is full. This directly reduces the eviction rate by ensuring that only the least accessed data is removed, preserving the most frequently accessed data and improving the cache hit ratio. In a high-write-volume scenario, this policy adapts to access patterns and minimizes unnecessary evictions of hot data.

Answer

Use many small keys instead of a few large keys.

Answer

Decrease the maxmemory setting to force earlier eviction.

Answer

Set maxmemory-policy to noeviction.