Google Professional Cloud Database Engineer (PCDE) — Questions 1–75

Why this answer

Why this order

Why this answer

How to eliminate wrong answers

Option A is wrong because the rules do not restrict write access to only the owner; they allow any authenticated user to write to any document (allow write: if request.auth != null;), which is a broader security concern. Option B is wrong because Firestore security rules implicitly allow delete operations when write access is granted, as 'write' encompasses create, update, and delete unless explicitly separated. Option D is wrong because the wildcard path `{database}` matches any database name within the project, which is standard for multi-database setups and does not introduce a security concern by itself.

Why this answer

How to eliminate wrong answers

Option A is wrong because Cluster tier with multiple shards distributes data across shards for horizontal scaling, but does not inherently provide zone-level redundancy unless each shard has replicas in different zones, which is not guaranteed by this option alone. Option B is wrong because Basic tier with a single node has no replication or failover, so a zone failure would cause complete data loss and downtime. Option C is wrong because Standard tier cross-region is not a valid Memorystore tier; cross-region replication is not supported by Memorystore for Redis, and this option incorrectly implies multi-region failover.

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because Cloud SQL for MySQL does not impose any mandatory delay after a failover; writes are allowed immediately once the replica is promoted to primary. Option C is wrong because a successful failover automatically promotes the replica to read-write mode; if it remained read-only, applications would receive errors, not high latency. Option D is wrong because replication lag does not cause high write latency; it affects read consistency but the promoted replica becomes the new primary with full write capability, and any lag is resolved asynchronously.

Why this answer

How to eliminate wrong answers

Option B is wrong because Cloud Spanner uses interleaved tables for hierarchical data relationships, not as a direct replacement for foreign keys; it does not support PostgreSQL triggers or the same constraint enforcement, requiring significant schema redesign and application logic changes. Option C is wrong because removing foreign keys and triggers shifts data integrity to the application layer, which increases complexity, risk of data corruption, and violates the goal of minimizing migration effort while maintaining integrity. Option D is wrong because Firestore in Datastore mode is a NoSQL document database that does not support SQL foreign keys, triggers, or relational integrity constraints, requiring a complete schema transformation and loss of existing PostgreSQL functionality.

Why this answer

How to eliminate wrong answers

Option B is wrong because enabling high availability only provides automatic failover but does not address the need to handle increased read traffic; read replicas are required for read scaling. Option C is wrong because using a CMEK key (Customer-Managed Encryption Key) and enabling binary logging are related to encryption and point-in-time recovery, not to read scaling or automatic failover. Option D is wrong because creating multiple read replicas without HA handles increased read traffic but does not provide automatic failover in case of a zone outage; HA is necessary for failover.

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud Spanner is a globally distributed, horizontally scalable database that requires schema redesign and does not support PostgreSQL wire protocol natively, making it unsuitable for a direct PostgreSQL migration. Option B is wrong because Cloud SQL for MySQL is a different database engine; migrating from PostgreSQL to MySQL would require schema and query conversion, increasing complexity and downtime, and does not leverage the existing PostgreSQL setup. Option C is wrong because pg_dump and psql export/import is a logical backup method that requires the source database to be read-only or quiesced during the dump to ensure consistency, causing significant downtime; for a 2 TB database with high write workload, this would result in hours of downtime and risk of data loss.

Why this answer

How to eliminate wrong answers

Option B is wrong because converting to Avro format does not inherently optimize query performance or cost in BigQuery; Avro is a storage format for import/export, not a query optimization technique, and partitioning alone does not reduce the column scan overhead. Option C is wrong because column-level security controls access but does not reduce the amount of data scanned or improve query performance; it adds administrative overhead without addressing the cost or speed issue. Option D is wrong because clustering by event_date is redundant when the table is already partitioned by event_date, and using SELECT * is the opposite of optimization—it forces scanning all columns, increasing cost and latency.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because binary logging is automatically enabled when you enable point-in-time recovery in Cloud SQL; manually enabling it and storing logs in Cloud Storage would incur additional storage costs and management overhead, not minimum cost. Option B is wrong because increasing backup retention to 30 days exceeds the 7-day requirement and incurs more storage cost, and disabling point-in-time recovery prevents recovery to any point within the retention period, only to backup times. Option D is wrong because disabling automated backups and creating manual backups daily would not allow point-in-time recovery (only to backup snapshots) and manual backups can be more expensive and less reliable than automated backups.

Why this answer

How to eliminate wrong answers

Option B is wrong because Cloud SQL is a relational database (MySQL, PostgreSQL, SQL Server) with limited write throughput (typically thousands of writes per second) and is not designed for high-velocity time-series data. Option C is wrong because Firestore is a document-oriented NoSQL database optimized for mobile and web apps with moderate throughput (up to 10,000 writes per second per database) and does not support the millions of writes per second required. Option D is wrong because Cloud Spanner is a globally distributed relational database with strong consistency and horizontal scaling, but its write throughput is limited by node count and transaction overhead, making it unsuitable for the extreme write volume of millions per second.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because pre-aggregating in a subquery reduces row count but does not address the root cause of shuffling during the join; the join still requires redistribution unless the subquery result is small enough to broadcast. Option C is wrong because a LIMIT clause only restricts the final output rows, not the intermediate data shuffled during the join; the full join still executes. Option D is wrong because window functions operate on already partitioned data and do not reduce shuffling; they can even introduce additional shuffles if the PARTITION BY clause differs from the join keys.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because rewriting queries to use fewer joins and aggregations might reduce complexity but does not guarantee performance improvements if the underlying slot contention is the root cause; it also requires significant development effort and may not fully resolve timeouts under high concurrency. Option C is wrong because simply increasing total slots to 2000 does not prioritize the dashboard queries; ad-hoc queries could still consume all slots, leading to the same contention and timeout issue. Option D is wrong because scheduling the dashboard refresh at a different time only avoids the conflict temporarily and does not solve the fundamental slot contention problem; it also may not be feasible if the dashboard requires hourly updates.

Why these pairings

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Query Insights focuses on analyzing query performance, such as latency and execution plans, not on visualizing key-level access patterns to detect hot spots. Option C is wrong because Cloud Trace is a distributed tracing tool for latency analysis of requests across services, not for identifying hot keys in a database. Option D is wrong because Cloud Monitoring provides metrics and alerting for overall system health and performance, but lacks the key-space heatmap visualization required to pinpoint hot spots in Cloud Spanner.

Why this answer

How to eliminate wrong answers

Option A is wrong because flushing the cache periodically removes all data, which would drastically reduce the hit ratio and increase load on the database, the opposite of the desired effect. Option C is wrong because enabling persistence (e.g., RDB snapshots or AOF logs) protects against data loss on restart but does not influence how long data remains in the cache or the hit ratio. Option D is wrong because increasing instance memory size only delays evictions under the maxmemory-policy; if the TTL is too short, data still expires quickly and the hit ratio remains low regardless of memory size.

Why this answer

How to eliminate wrong answers

Option A is wrong because BigQuery does not use indexes; clustering is a performance optimization that organizes data into blocks based on cluster column values, not an index. Option C is wrong because partitioning by date does not inherently cause data skew; data skew is more likely from uneven distribution of symbol values, not from date partitioning. Option D is wrong because partitioning by date is already granular enough for a 30-day window; the issue is not the partition granularity but the clustering inefficiency for queries that span many symbols across multiple partitions.

Why this answer

How to eliminate wrong answers

Option A is wrong because converting in the BI tool's application layer can lead to inconsistencies if multiple tools access the same data, and it offloads conversion logic to the presentation tier, which may not handle daylight saving time changes correctly without additional configuration. Option C is wrong because storing timestamps as text strings with time zone offsets breaks date arithmetic, indexing, and sorting, and makes it impossible to use native temporal functions for filtering or aggregation. Option D is wrong because storing both UTC and local time in separate columns duplicates data, increases storage overhead, and risks synchronization errors when time zone rules change (e.g., daylight saving time policy updates).

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because 'received_bytes_count' measures network throughput, not connection count; a spike in bytes could be due to large queries or data transfers, not necessarily a connection spike. Option B is wrong because 'seconds_behind_master' is a replication lag metric relevant only for read replicas, not for correlating connection spikes with performance degradation on the primary instance. Option C is wrong because 'instance/uptime' only indicates restarts, which are not directly caused by connection spikes; performance degradation can occur without any instance restart.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because Cloud Logging captures raw log entries and events but does not provide aggregated query-level performance metrics or resource consumption analysis for Cloud Spanner. Option C is wrong because the Performance Dashboard in Google Cloud Console offers a high-level overview of database metrics like latency and throughput, but it lacks the per-query breakdown and resource attribution needed to identify specific resource-heavy queries. Option D is wrong because Cloud Monitoring metrics provide system-level metrics (e.g., CPU utilization, storage) but do not offer query-level insights or the ability to sort and analyze individual query performance.

Why this answer

How to eliminate wrong answers

Option B is wrong because using the auto-increment value directly as the primary key creates a monotonically increasing sequence, which Cloud Spanner will route to a single split, causing a hot spot and severely limiting write throughput. Option C is wrong because interleaved tables colocate parent and child rows for efficient joins, but they do not address the distribution of writes for a table with a monotonically increasing primary key; the hot spot would still occur in the parent table. Option D is wrong because while a UUID primary key avoids monotonicity and can distribute writes, it is not the best design for Cloud Spanner; UUIDs are large (128-bit), increase storage and index size, and can still cause hot spots if not carefully designed (e.g., time-based UUIDs), whereas a hash prefix of the auto-increment value is more efficient and purpose-built for this scenario.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because querying a top-level posts collection with a list of followed user IDs requires an 'in' query, which is limited to 10 values per query and does not scale to hundreds or thousands of followed users, leading to multiple queries and high read costs. Option C is wrong because storing all user posts in an array within a single document violates the 1 MiB document size limit and cannot support ordered queries or pagination, making it impractical for any real-world social media feed. Option D is wrong because querying posts for each followed user individually results in N+1 read operations per feed request, causing high latency and cost proportional to the number of followed users, with no built-in ordering across results.

Why this answer

How to eliminate wrong answers

Option A is wrong because too many column families do not cause write hotspotting; they affect storage and read performance, not write distribution. Option B is wrong because inefficient reads (e.g., full scans) are a read-side issue, not a cause of high write latency; reverse scan is a read optimization, not a write fix. Option D is wrong because misconfigured tablet splits or pre-splitting addresses initial distribution, but the root cause here is the key design pattern, not split configuration; even with pre-splits, monotonically increasing keys will still hotspot writes to the last tablet.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because the absence of an ORDER BY clause does not cause a query to take a long time; it merely affects the order of results, not the scan method. Option B is wrong because interleaving (table interleaving in Cloud Spanner) is a design pattern that can improve performance by co-locating parent and child rows; it does not inherently cause extra I/O and is not relevant to a simple filter on a non-key column. Option C is wrong because the primary key is already defined; the issue is that the query predicate does not match the primary key order, so the primary key cannot be used efficiently for this filter.

Why this answer

How to eliminate wrong answers

Option A is wrong because cross-region backups with 5-minute retention cannot achieve a 5-minute RPO; backups are point-in-time snapshots and restoring them takes longer than 1 hour, failing the RTO. Option B is wrong because Cloud SQL for MySQL does not support cross-region replicas with synchronous replication; it uses asynchronous replication, which cannot guarantee a 5-minute RPO across continents due to replication lag. Option D is wrong because exporting to Cloud Storage every 5 minutes introduces significant latency and data loss risk; exports are not incremental and cannot meet the 5-minute RPO or 1-hour RTO, as restoring from exports requires manual import and is not designed for disaster recovery.

Why this answer

How to eliminate wrong answers

Option B is wrong because partitioning tables (e.g., range or hash partitioning) improves query performance on large tables by reducing scan scope, but it does not add read capacity or offload traffic from the primary instance. Option C is wrong because Cloud Spanner is a globally distributed, strongly consistent relational database designed for horizontal write scalability and global transactions, which is overkill and cost-prohibitive for a simple read-scaling need on an existing MySQL workload. Option D is wrong because enabling automatic storage increase only prevents out-of-disk errors by expanding storage capacity; it does not improve read throughput or distribute query load.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because sharding across multiple regional instances would require manual application-level logic and does not leverage Cloud Spanner's built-in distributed transaction support, leading to increased complexity and potential consistency issues. Option B is wrong because read replicas do not offload write traffic; writes must still go to the primary region, which would become a bottleneck under a 10x write increase, and read replicas do not improve write latency or availability for writes. Option D is wrong because increasing nodes in a single regional instance only scales capacity within that region, failing to provide global low-latency writes or multi-region high availability, and does not address geographic distribution requirements.

Why this answer

How to eliminate wrong answers

Option A is wrong because ARRAY_AGG with LIMIT 1 returns an array containing a single element, not a scalar value, and is less efficient than MIN or ROW_NUMBER. Option B is wrong because it performs a self-join on both session_id and event_time, which is redundant and less efficient than a simple GROUP BY or window function; it also requires an exact match on the timestamp, which can fail if there are duplicate timestamps for the same session. Option C is wrong because although it correctly returns the first event time per session, it is not the most efficient option in the context of the PCDE exam, which often tests window functions and QUALIFY as a more modern and flexible approach.

Why this answer

How to eliminate wrong answers

Option A is wrong because a primary key index on `Users(Email)` would require `Email` to be the primary key, which is not necessarily the case; the primary key might be `UserId`, and adding a primary key on `Email` could change table structure and is not the missing index for a simple lookup. Option C is wrong because a composite index on `Users(Email, UserId)` is overkill; the query only filters on `Email`, so a single-column index on `Email` suffices, and a composite index may be larger and less efficient for this specific query. Option D is wrong because relying on a full table scan is inefficient and does not guarantee a row is returned if the table is large or the query plan uses a scan that skips the row due to concurrency or statistics; an index is needed for reliable, fast access.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because removing the secondary index on account_id would degrade SELECT performance for account lookups, and the index's write overhead is negligible compared to the cost of foreign key checks. Option C is wrong because changing the primary key order does not eliminate foreign key validation overhead; it only affects index clustering and does not address the root cause of slow INSERTs. Option D is wrong because temporal tables with system-versioning add additional metadata and version-row writes on every INSERT, which would further degrade performance, not improve it.

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing storage capacity does not address index fragmentation; it only provides more space, which does not improve query performance if the indexes are fragmented. Option C is wrong because enabling query insights helps analyze performance but does not fix the root cause of index scans; it is a diagnostic tool, not a solution. Option D is wrong because read replicas offload read queries but do not resolve index fragmentation on the primary instance; the degraded performance would persist on the primary instance.

Why this answer

How to eliminate wrong answers

Option A is wrong because using pg_dump and psql in a single connection is a manual, offline migration method that requires the source database to be locked or read-only during the dump, causing significant downtime. Option B is wrong because Cloud SQL does not support being configured as a read replica of an on-premises PostgreSQL primary; Cloud SQL read replicas can only replicate from a Cloud SQL primary, not from external sources. Option D is wrong because a one-time full dump and import (even via DMS) does not include ongoing change data capture, so the target will be out of sync with the source by the time the import completes, requiring additional downtime for a final sync.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because a view would not cause this specific error; views can be queried without a WHERE clause, and the error message would differ (e.g., 'invalid object' or 'view does not exist'). Option C is wrong because if the table were not partitioned, there would be no partition-related error; the error specifically indicates a partition-related issue. Option D is wrong because permission errors typically produce 'insufficient privileges' or 'access denied' messages, not the error shown in the exhibit.

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because `ARRAY_LENGTH(items) > 0` only checks if the array is non-empty, not whether it contains the specific product 'ABC'. Option C is wrong because `'ABC' IN UNNEST(items)` is invalid syntax; `IN` with `UNNEST` requires a scalar comparison, but `items` is an array of structs, not scalars, so this will cause a type mismatch error. Option D is wrong because the implicit `CROSS JOIN` with `UNNEST` multiplies rows for each array element, which is inefficient for large tables and requires a `DISTINCT` or `SELECT o.*` with deduplication to avoid duplicate order rows, making it slower and more resource-intensive than the `EXISTS` approach.

Why this answer

How to eliminate wrong answers

Option A is wrong because DATE_TRUNC does not return a STRING; it returns a temporal type, not a text representation. Option B is wrong because DATE_TRUNC on a TIMESTAMP column returns a TIMESTAMP, not a DATE; a DATE would lack the time component entirely. Option C is wrong because DATETIME is a different type that does not include timezone context, whereas BigQuery's DATE_TRUNC on a TIMESTAMP preserves the TIMESTAMP type with timezone awareness.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because adding a secondary index on `user_id` does not help queries that filter only on `transaction_id`; it would only be useful if queries filtered on `user_id` alone or in combination with `transaction_id`. Option C is wrong because a query hint to force a specific index scan is unnecessary and ineffective if no suitable index exists; the hint cannot create an index that doesn't exist, and without an index on `transaction_id`, Spanner would still perform a full scan. Option D is wrong because changing the primary key to `(transaction_id, user_id)` would require a costly schema change and data migration, and it would break the interleaved table structure with the parent `users` table, which expects `user_id` as the first part of the primary key for interleaving.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because partitioning by product category is not supported in BigQuery (partitioning is based on date, timestamp, or integer range, not on string columns like product category), and even if it were, partitioning alone does not pre-aggregate data, so the query would still need to scan all partitions to compute the aggregation. Option B is wrong because creating a separate summary table using scheduled queries introduces additional complexity and cost for manual refresh scheduling, and it does not provide automatic incremental updates like a materialized view, leading to potential data staleness and extra storage costs for the duplicate table. Option D is wrong because clustering the table by region only improves the performance of queries that filter or sort by region, but it does not pre-compute the aggregation; the query would still scan all rows in the clustered blocks to perform the GROUP BY, so it does not reduce the data scanned for the aggregation itself.

Why this answer

How to eliminate wrong answers

Option A is wrong because insufficient nodes would cause general performance degradation across all queries, not specifically slow down the all-devices query while keeping the per-device query fast. Option B is wrong because scanning multiple column families adds overhead only if the query retrieves data from many families, but the problem statement does not mention column families, and the slowness is tied to the row key design, not column family access. Option D is wrong because too many tablets can cause high overhead for any scan, but the per-device query would also be affected; the asymmetry between fast per-device and slow all-devices queries points directly to row key ordering, not tablet count.

Why this answer

How to eliminate wrong answers

Option A is wrong because keeping the same sequence-based integer keys will cause all new inserts to target the same tablet (split) in Cloud Spanner, leading to severe hotspotting and degraded write performance. Option B is wrong because using a timestamp prefix does not guarantee distribution; if timestamps are monotonically increasing (e.g., insertion time), writes will still concentrate on the latest split, causing hotspotting. Option D is wrong because UUIDs are random but not designed to avoid hotspotting in Cloud Spanner; without a hash prefix or similar distribution mechanism, UUIDs can still lead to uneven splits and performance issues, especially under high write loads.

Why this answer

How to eliminate wrong answers

Option B is wrong because secondary indexes on join columns do not change the physical co-location of parent and child rows; they only provide an alternative access path, and queries may still require distributed joins across splits, which is the root cause of slowness. Option C is wrong because batch update operations reduce round trips for writes, not for read-heavy join queries; they do not address the physical data layout needed for efficient joins. Option D is wrong because removing interleaving would break the physical co-location guarantee, forcing Spanner to perform distributed cross-split joins, which would make queries even slower, not faster.

Why this answer

How to eliminate wrong answers

Option A is wrong because a view only stores the SQL query definition, not the results; each query against the view still executes the UNNEST operation at runtime, providing no performance improvement. Option B is wrong because it violates the requirement to not change the source schema, and redesigning the table to be flat would require altering the ingestion pipeline and breaking existing queries that rely on nested fields. Option C is wrong because subqueries with UNNEST and caching are not natively supported in BigQuery; caching applies only to the final query result, not intermediate subquery results, and manual caching via temporary tables is not a best practice for ongoing analyst queries.

Why this answer

How to eliminate wrong answers

Option A is wrong because materialized views reduce the amount of data scanned and recomputation for repeated aggregations, but they do not increase the available slot capacity; if the query is already hitting 100% slot usage, the bottleneck is compute resources, not data volume. Option C is wrong because clustering improves data pruning and scan efficiency for filtered queries, but it does not add more slots; the query will still be throttled by the fixed slot pool. Option D is wrong because partitioning reduces the amount of data read by date range filters, but like clustering, it does not address the root cause of slot exhaustion; the query will still run at the same slot limit.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because BigQuery does not support traditional indexes; it uses columnar storage and clustering/partitioning for performance, so adding indexes is not applicable. Option C is wrong because replacing LEFT JOINs with INNER JOINs changes the business logic by excluding rows that do not have matching records in the joined table, which may alter the BI report results. Option D is wrong because increasing the number of BigQuery slots only addresses resource contention, not the root cause of slow JOINs; it is a costly workaround that does not optimize the query structure.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Database Migration Service is used for migrating databases to Cloud SQL, not for failover; failing over to a read replica would cause downtime during the promotion process and does not diagnose the latency issue. Option C is wrong because gcloud sql instances describe only returns static configuration metadata (e.g., machine type, region, maintenance window) and does not provide real-time query performance or latency diagnostics. Option D is wrong because VPC Flow Logs capture network-level metadata (source/destination IP, ports, packet count) and cannot reveal slow SQL queries, lock waits, or database engine internals.