PCDE Timed Practice Test 1 — 10 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.