PCDE Practice Test 6 — 10 Questions

Question 1

You are a cloud database engineer for a financial services firm. The firm uses Cloud SQL for PostgreSQL to support a BI reporting tool. The main table 'transactions' has 500 million rows and is growing daily. Reports often run aggregations over date ranges and group by account_id. The 'transactions' table has indexes on date and account_id separately. Despite these indexes, the reporting queries are slow, often taking over 30 minutes. The database is deployed on a high-memory machine with 32 vCPUs and 256 GB RAM. You notice that the queries perform sequential scans instead of using indexes. What is the most likely reason, and what single change would you make to improve performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a composite index on (date, account_id). The reporting queries filter by date ranges and group by account_id, but the existing separate indexes on date and account_id cannot be combined efficiently for both conditions. PostgreSQL's query planner often chooses a sequential scan over using two separate indexes because it estimates that reading the entire table is cheaper than the bitmap scan overhead of combining them. A composite index on (date, account_id) allows the database to directly locate rows matching the date range and then access them in account_id order, eliminating the need for a separate sort or join step.

Answer

Partition the table by date using PostgreSQL declarative partitioning

Answer

Increase the shared_buffers setting to 128 GB

Answer

Disable sequential scans by setting enable_seqscan = off

Question 2

You administer a Cloud SQL for PostgreSQL instance running with 4 vCPUs and 15 GB of memory. The application frequently runs complex read-only reporting queries during business hours. Recently, the database started throwing 'out of memory' errors, and the instance's memory usage is consistently above 90%. You have enabled the pgtune recommendations and applied them. The workload is read-heavy and does not require a high write throughput. Which change would most effectively reduce memory pressure while maintaining query performance?

Accepted Answer

Scale up the instance to a higher memory tier, such as 8 vCPUs and 30 GB of memory.. Scaling up the instance to a higher memory tier (8 vCPUs, 30 GB) directly increases available memory, addressing the 'out of memory' errors and high memory usage while maintaining read performance. Option A (pgzstd compression) does not significantly reduce memory consumption; it helps with storage or network I/O. Option B (PgBouncer) reduces memory per connection but does not reduce overall memory needs when the database itself requires more memory for caching and operations. Option D (decreasing shared_buffers) sacrifices cache performance, likely slowing queries, and may not free enough memory to resolve the issue.

Answer

Enable the pgzstd extension to compress data in memory.

Answer

Set up connection pooling using PgBouncer to limit the number of concurrent connections.

Answer

Decrease the shared_buffers configuration parameter to free memory for other uses.

Question 3

Your company runs a critical application on Google Kubernetes Engine (GKE) with a StatefulSet using persistent volumes backed by Compute Engine persistent disks. The application performs frequent small random writes to a MySQL database stored on the persistent disks. You notice that the disk write latency has increased significantly, and the application's throughput has dropped. Monitoring shows that the disk queue depth is consistently high. The current disk type is pd-standard. What is the most cost-effective way to reduce write latency and improve throughput?

Accepted Answer

Change the persistent disk type from pd-standard to pd-ssd.. The application is experiencing high write latency due to insufficient IOPS from pd-standard disks, which are HDD-based and optimized for sequential reads, not small random writes. Changing to pd-ssd (SSD-based) provides significantly higher IOPS and lower latency for random write workloads, directly addressing the high queue depth and throughput drop. This is the most cost-effective solution because pd-ssd offers the necessary performance improvement without requiring architectural changes or over-provisioning capacity.

Answer

Use a regional persistent disk for higher availability and performance.

Answer

Add more replicas of the StatefulSet to distribute writes across multiple disks.

Answer

Increase the size of the persistent disks to improve IOPS limits.

Question 4

A team uses Cloud Deploy with a delivery pipeline that deploys to GKE clusters across dev, staging, and prod targets. They want to automatically roll back a release if the canary deployment in staging fails to meet a defined service-level objective (SLO) for error rate. Which Cloud Deploy feature enables this?

Accepted Answer

Configure canary deployment with the '--canary-percentage' flag and enable SLO verification in the delivery pipeline.. Cloud Deploy's canary deployment strategy supports SLO verification via the `--canary-percentage` flag combined with a `canaryDeployment` configuration that includes a `verify` phase. This allows the pipeline to automatically roll back the release if the canary fails to meet the defined error rate SLO, without manual intervention or external services.

Answer

Use preDeploy and postDeploy hooks to run Cloud Run jobs that check error rate and roll back.

Answer

Use Binary Authorization with a custom attestor that checks error rate.

Answer

Set an approval gate on the staging target that requires manual review.

Question 5

A company uses Memorystore for Redis with the 'volatile-lru' eviction policy. They notice that recently added keys with TTL are being evicted even though there is still memory available. What is the most likely cause?

Accepted Answer

The volatile-lru policy evicts only keys with an expiry set, and there are many keys without expiry occupying memory. The 'volatile-lru' eviction policy in Memorystore for Redis evicts only keys that have a TTL (expiry) set, using an LRU (Least Recently Used) algorithm among those keys. If many keys without expiry are consuming memory, the eviction process will still target only the volatile keys, even if overall memory is not fully exhausted, because the policy is constrained to evict only from the subset of keys with TTLs. This explains why recently added keys with TTL are being evicted prematurely — the non-volatile keys are 'protected' from eviction, forcing the eviction of volatile keys to free memory.

Answer

The allkeys-lru policy would be more appropriate

Answer

The maxmemory setting is not configured

Answer

The maxmemory-policy is misconfigured

Question 6

A company wants to reduce BigQuery query costs for their BI workloads. Which THREE actions effectively lower the amount of data processed per query? (Choose THREE.)

Accepted Answer

Use partitioned tables on date column. Partitioned tables in BigQuery allow queries to use the WHERE clause to filter on the partition column (e.g., a date column), so BigQuery can prune entire partitions from the scan. This directly reduces the amount of data read and billed, lowering query costs. Option A is correct because it is a primary cost-control mechanism in BigQuery.

Answer

Use LIMIT in subqueries to reduce output

Answer

Use SELECT * to avoid missing columns

Question 7

Match each Cloud SQL tier to its description.

Accepted Answer

db-f1-micro: 0.5 vCPU, 0.6 GB memory. Cloud SQL tiers are defined by vCPU and memory. Common confusions arise when swapping memory-heavy and compute-heavy tiers, or mixing vCPU counts. Ensure you match the exact specifications for each tier.

Answer

db-n1-highmem-2: 2 vCPU, 3.75 GB memory

Answer

db-n1-highcpu-2: 0.5 vCPU, 0.6 GB memory

Question 8

A BI developer is designing a BigQuery dataset for a sales dashboard. Which column naming convention is considered a best practice for column names in BI reports?

Accepted Answer

Use descriptive, snake_case names (e.g., total_revenue).. BigQuery column names are case-insensitive but must follow standard SQL naming rules. Using descriptive snake_case (e.g., total_revenue) improves readability, avoids ambiguity, and is consistent with BigQuery's own system tables and best practices for BI tools like Looker or Tableau, which often expect clean, underscore-separated identifiers.

Answer

Use names with spaces (e.g., Total Revenue).

Answer

Use short, cryptic abbreviations (e.g., tr).

Answer

Use camelCase names (e.g., totalRevenue).

Question 9

A company is using Bigtable for a high-throughput write workload. They need to monitor replication lag between clusters in a replicated setup and ensure that reads are eventually consistent. Which two configurations should they check? (Choose TWO.)

Accepted Answer

Ensure replication is set to asynchronous (default). Bigtable replication is asynchronous and eventually consistent by default. Replication lag can be monitored using the 'replication lag' metric in Cloud Monitoring. Consistency levels are not configurable; async is the default.

Answer

Create a replication dashboard in Data Studio

Answer

Set the replication consistency level to 'strong'

Answer

Enable synchronous replication for zero lag

Question 10

A DevOps team wants to create a custom metric in Cloud Monitoring that counts the number of failed login attempts per user per minute. This metric should be of type DELTA. What is the correct approach?

Accepted Answer

Create a metric descriptor using gcloud alpha monitoring metrics create with --metric-kind=DELTA and --value-type=INT64. DELTA metrics represent the change in a value over a time interval. To create such a metric, you must use the monitoring API to define a metric descriptor with metricKind DELTA and valueType INT64. The metric is then written via the API. OpenTelemetry can also be used, but the direct API call is the most straightforward. Cloud Monitoring does not support direct creation of DELTA metrics via the Metrics Explorer; CLI examples use custom metric descriptors.

Answer

Use the Metrics Explorer to define a new metric with type DELTA

Answer

Create a log-based metric using the Logs Explorer with a filter for failed logins, which automatically creates a CUMULATIVE metric

Answer

Use the Cloud Console to manually add a metric entry, setting type to DELTA