PCDE Practice Test 4 — 25 Questions

Question 1

A data engineering team ingests JSON logs into BigQuery using a streaming pipeline. Queries need to extract specific fields from nested arrays. Which SQL construct should be used to efficiently transform the nested data into a flat table for BI?

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

Accepted Answer

UNNEST with CROSS JOIN. `UNNEST` with `CROSS JOIN` is the standard SQL construct in BigQuery to flatten nested arrays (repeated fields) into a flat table. When JSON logs contain arrays of structs, `CROSS JOIN UNNEST(array_column)` expands each array element into its own row, allowing BI tools to access individual fields directly. This is the most efficient and idiomatic way to transform nested data into a relational format for querying.

Answer

ARRAY_AGG with STRUCT

Answer

STRUCT with nested field access

Answer

SELECT * EXCEPT with UNNEST

Question 2

A company is migrating an on-premises PostgreSQL database to Cloud SQL. The database is 2 TB and must have minimal downtime during migration. Which migration strategy should the Database Engineer recommend?

Accepted Answer

Use Database Migration Service to set up continuous replication from the on-premises database to Cloud SQL, then perform a minimal-downtime cutover.. The Database Migration Service (DMS) supports continuous replication from an on-premises PostgreSQL database to Cloud SQL using logical replication (pglogical or native PostgreSQL logical replication). This allows the source database to remain operational during the migration, and the cutover to Cloud SQL can be performed with minimal downtime, typically seconds to minutes, meeting the 2 TB size and low-downtime requirements.

Answer

Export the database using pg_dump and import into Cloud SQL using pg_restore during a maintenance window.

Answer

Set up PostgreSQL on Compute Engine and replicate data, then switch over to Cloud SQL.

Answer

Use Cloud SQL's built-in replication feature to connect directly to the on-premises database.

Question 3

An organization uses Artifact Registry to store Docker images. They want to enforce that only images that have passed vulnerability scanning and are signed can be deployed to GKE. Which two services should they use together?

Accepted Answer

Binary Authorization and Container Scanning API. Binary Authorization enforces that only signed and verified container images can be deployed to GKE, while the Container Scanning API (now part of Artifact Analysis) performs vulnerability scanning on images stored in Artifact Registry. Together, they ensure that only images that have passed vulnerability scanning and are cryptographically signed can be deployed, meeting the organization's requirements.

Answer

Cloud Deploy and Cloud Run

Answer

Cloud Build and Artifact Registry

Answer

Security Command Center and Cloud Asset Inventory

Question 4

A company is using Cloud SQL and wants to automatically increase storage when disk usage reaches a threshold. What should they configure?

Accepted Answer

Enable 'auto-storage increase' in the instance settings.. Cloud SQL provides a built-in 'auto-storage increase' setting that, when enabled, automatically increases the instance's storage capacity when disk usage reaches a predefined threshold (typically 90% or when free space drops below a certain amount). This eliminates the need for manual intervention or custom automation, ensuring high availability and preventing out-of-disk errors.

Answer

Use Active Assist recommendations to manually resize.

Answer

Set up a Cloud Monitoring alert to manually increase storage when usage exceeds 80%.

Answer

Configure a Cloud Function to resize the disk via API when threshold is reached.

Question 5

An organization wants to set up a landing zone with separate projects for development, staging, and production environments. They also need a shared VPC for networking and a centralized logging project. Which folder structure aligns with Google Cloud best practices?

Accepted Answer

Create folders: /environments/dev, /environments/staging, /environments/prod, and /common. Place networking and logging projects in /common.. It follows Google Cloud best practices by separating environments (dev, staging, prod) into their own folder under an /environments parent, and placing shared resources like networking and logging into a /common folder. This structure enables consistent IAM policy inheritance, resource isolation, and centralized management of shared services, which is critical for a landing zone in a DevOps pipeline.

Answer

Create a single folder /landing-zone and put all projects there.

Answer

Create folders: /dev, /staging, /prod. Place all projects directly under the root.

Answer

Create folders: /prod, /non-prod, /shared. Put dev and staging in /non-prod.

Question 6

A data engineer is migrating a legacy on-premises Oracle data warehouse to Google Cloud. The source schema uses star schemas and advanced Oracle features like materialized views. The target must support real-time data from streaming sources and run complex SQL joins over 50 TB of data with low latency. Which architecture is most appropriate?

Accepted Answer

Migrate to BigQuery and use streaming inserts for real-time data.. BigQuery is the most appropriate target because it supports real-time streaming inserts, can handle complex SQL joins over 50 TB of data with low latency via its columnar storage and distributed query engine, and can replace Oracle materialized views with logical views or scheduled queries. It also natively integrates with streaming sources like Pub/Sub, making it ideal for the real-time data requirement.

Answer

Migrate to AlloyDB and use columnar engine for analytics.

Answer

Migrate to Cloud Spanner and use its analytics interface.

Answer

Migrate to Cloud SQL for PostgreSQL and use read replicas for analytics.

Question 7

A company is using Cloud Bigtable to store user events for real-time analytics. The current row key is userId_timestamp (e.g., user123_20240115103000). However, writes to the table are unevenly distributed, causing hotspotting on a few nodes. Which row key modification can best distribute writes evenly?

Accepted Answer

Add a hash prefix (e.g., 2-byte hash of userId) at the beginning.. Adding a hash prefix (e.g., a 2-byte hash of the userId) at the beginning of the row key ensures that writes are distributed across all Bigtable nodes by randomizing the initial portion of the key. This prevents hotspotting because sequential or clustered userId values no longer concentrate writes on a single tablet server, as Bigtable splits and distributes rows based on the row key prefix.

Answer

Increase the number of nodes to handle the load.

Answer

Reverse the timestamp and prepend it to the key.

Answer

Move userId to the end of the row key.

Question 8

Your team is migrating a 5 TB MySQL database from on-premises to Cloud SQL. The database receives 2,000 writes per second and the network link has 500 Mbps bandwidth. You need to minimize migration time with zero data loss. What should you do?

Accepted Answer

Configure Database Migration Service with a connectivity test and start continuous replication.. Database Migration Service (DMS) with continuous replication is the correct choice because it supports live migration with minimal downtime and zero data loss for large databases like 5 TB. DMS uses MySQL binlog-based replication to synchronize changes from the on-premises source to Cloud SQL while the source remains operational, then performs a cutover with only seconds of downtime. This approach handles the 2,000 writes/second load and 500 Mbps bandwidth efficiently by streaming incremental changes rather than transferring the entire 5 TB in one shot.

Answer

Export the database using mysqldump and import into Cloud SQL using the console.

Answer

Use gcloud sql import with a compressed CSV export from on-premises.

Answer

Set up a VPN, then use mysqldump with --master-data and pipe to mysql on Cloud SQL.

Question 9

A company is migrating an Oracle database to Cloud SQL for PostgreSQL. They have a table with a column defined as NUMBER(10,2). To maintain data integrity, what should be the corresponding PostgreSQL data type?

Accepted Answer

NUMERIC(10,2). NUMBER(10,2) represents a decimal number with 10 digits total and 2 after decimal; NUMERIC(10,2) is the equivalent in PostgreSQL.

Answer

FLOAT

Answer

INTEGER

Answer

DECIMAL(10,0)

Question 10

You need to transfer 10 TB of data from on-premises servers to Cloud Storage for loading into Bigtable. Which method is the most efficient and reliable for this volume?

Accepted Answer

Use Storage Transfer Service. The Storage Transfer Service is designed for large-scale, online data transfers from on-premises or other cloud providers to Google Cloud. It handles 10 TB efficiently by automatically managing retries, checksums, and network optimization without requiring manual scripting or persistent VPN connections, making it the most reliable and efficient choice for this volume.

Answer

Set up Cloud VPN and use rsync

Answer

Use gsutil cp in parallel

Answer

Use Dataflow to read and write data

Question 11

A Database Engineer is responsible for managing a Cloud SQL for MySQL instance. The engineer needs to ensure that automated backups are retained for 14 days and that point-in-time recovery (PITR) is enabled. Which configuration should the engineer set?

Accepted Answer

Enable automated backups, set backup retention to 14 days, and enable binary logging.. Enabling automated backups with a 14-day retention ensures backup files are kept for the required duration, and enabling binary logging (which is required for PITR) allows point-in-time recovery by replaying transaction logs against a base backup. In Cloud SQL for MySQL, PITR is not automatically enabled with automated backups; binary logging must be explicitly enabled.

Answer

Create scheduled manual backups each day and retain them for 14 days; PITR is not needed because backups are daily.

Answer

Enable automated backups with a retention of 14 days; PITR is automatically enabled.

Answer

Enable automated backups with 14-day retention and set up a cross-region replica for disaster recovery.

Question 12

A company uses Cloud SQL for MySQL with a failover replica. The primary instance is in us-central1 and the replica is in us-east1. During a regional outage in us-central1, the database engineer executes an emergency failover to the replica. After the failover, applications experience high latency when writing to the new primary. What is the most likely cause?

Accepted Answer

The new primary is in a different region, causing higher network round-trip times for applications that are still in us-central1.. After a cross-region failover, the new primary resides in us-east1 while the applications remain in us-central1. This geographic distance increases network round-trip time (RTT) for write operations, as each write must traverse the WAN between regions. Cloud SQL for MySQL does not automatically relocate compute resources, so latency-sensitive applications will experience higher write latency until they are migrated or configured to connect to the new region.

Answer

Cross-region replication triggers a mandatory 1-hour delay before writes are allowed.

Answer

The failover did not complete successfully; the replica is still in read-only mode.

Answer

The replica had a replication lag of 5 minutes, causing data inconsistency.

Question 13

A DevOps team is designing a landing zone in Google Cloud. They need to set up a folder structure that supports multiple teams and environments. Which TWO practices should they follow? (Choose 2)

Accepted Answer

Create separate folders for each team (e.g., 'Team-A', 'Team-B') under the environment folders.. Option D is correct because creating environment folders such as prod, staging, and dev under the organization or a top-level folder lets you apply distinct IAM policies, organization policies, and billing/quota controls per environment, achieving strong isolation between production and non-production workloads. Option C is correct because nesting separate team folders (e.g., Team-A, Team-B) under each environment folder maps the resource hierarchy to both organizational and environmental boundaries, so permissions and policies can be inherited and scoped precisely at the team level within each environment. Option A is not appropriate because organization policies are best applied at higher nodes (organization, folder) so they inherit down; applying them only at the project level creates gaps and inconsistent compliance. Option B is wrong because a flat project structure with no folders prevents hierarchical policy inheritance and scalable governance. Option E is wrong because lumping all team projects into a single 'Teams' folder without further grouping removes the per-team and per-environment separation needed for least-privilege IAM and policy scoping.

Answer

Use organization policies at the project level only to enforce compliance.

Answer

Create a flat project structure under the organization node with no folders.

Answer

Place all team projects in a single folder named 'Teams' without further grouping.

Question 14

Your team is using Cloud Monitoring dashboards to monitor a multi-service architecture. You want to manage dashboards as code using Terraform. Which approach should you use to create a dashboard?

Accepted Answer

Use the google_monitoring_dashboard Terraform resource with a dashboard JSON configuration. The google_monitoring_dashboard Terraform resource accepts a dashboard_json field containing the Cloud Monitoring dashboard definition, allowing dashboards to be managed declaratively as code. This is the supported, first-class Terraform approach for Cloud Monitoring dashboards. It integrates with Terraform state, plan/apply, and version control without custom tooling.

Answer

Use the Monitoring API directly from Terraform with a custom provider

Answer

Create the dashboard manually and export it as a JSON file, then import into Terraform

Answer

Use gcloud alpha monitoring dashboards create command with a YAML file

Question 15

During an incident, an SRE team uses an incident command system. Which role is responsible for coordinating communication and resources, but not for technical debugging?

Accepted Answer

Incident Commander. In an incident command system (ICS), the Incident Commander owns overall coordination, resource allocation, and communication with stakeholders — but deliberately does not dive into technical debugging. That separation lets the IC maintain situational awareness and keep the response organized while subject-matter experts handle the technical work.

Answer

Subject Matter Expert

Answer

Operations Lead

Answer

Scribe

Question 16

A gaming company uses Firestore in Native mode to store player profiles and game state. They need to query the data by both 'playerId' and 'lastLoginTimestamp' sorted descending. The current index configuration is automatic. How should they configure indexing to support this query efficiently?

Accepted Answer

Create a composite index on 'playerId' ascending and 'lastLoginTimestamp' descending. Firestore requires a composite index for queries that filter on one field and order by another (or filter on multiple fields). Since the query filters by 'playerId' and sorts by 'lastLoginTimestamp' descending, a composite index on 'playerId' ascending and 'lastLoginTimestamp' descending is needed. Automatic single-field indexes cannot serve this combined query, so the composite index must be created explicitly.

Answer

Create two separate single-field indexes: one on 'playerId' and one on 'lastLoginTimestamp'

Answer

Use an exemption to remove automatic indexing on 'playerId' and rely on single-field indexes

Answer

Use the automatic index configuration; Firestore will create the necessary composite index automatically

Question 17

A team needs to monitor CPU utilization on Cloud SQL databases and receive an alert when it exceeds 80% for more than 5 minutes. Which Google Cloud service should they use?

Accepted Answer

Cloud Monitoring. Cloud Monitoring (formerly Stackdriver Monitoring) is the correct service because it provides built-in metrics for Cloud SQL, including CPU utilization, and allows you to create alerting policies based on threshold conditions (e.g., CPU > 80% for 5 minutes). It integrates directly with Cloud SQL and can trigger notifications via email, Pub/Sub, or other channels when the condition is met.

Answer

Cloud Audit Logs

Answer

Active Assist

Answer

Cloud Logging

Question 18

A production Cloud SQL for PostgreSQL instance needs to handle increased read traffic and provide automatic failover in case of a zone outage. Which architecture satisfies both requirements?

Accepted Answer

Enable high availability (regional) and create a read replica in the same region.. Enabling high availability (regional) for Cloud SQL for PostgreSQL creates a primary and standby instance in different zones within the same region, providing automatic failover during a zone outage. Adding a read replica in the same region offloads read traffic from the primary instance, satisfying the increased read traffic requirement while the HA configuration ensures high availability.

Answer

Enable high availability only.

Answer

Use a CMEK key and enable binary logging.

Answer

Create multiple read replicas without HA.

Question 19

A company is migrating an on-premises PostgreSQL database to Cloud SQL. The database is 2 TB and has a high write workload. They need minimal downtime. Which migration approach is best?

Accepted Answer

Use Database Migration Service with continuous replication. Database Migration Service (DMS) with continuous replication is the best approach because it supports minimal-downtime migrations from on-premises PostgreSQL to Cloud SQL. DMS uses change data capture (CDC) to replicate ongoing writes while the initial data load completes, then performs a cutover with only seconds of downtime. This handles the 2 TB size and high write workload without requiring manual export/import or schema changes.

Answer

Use Cloud Spanner

Answer

Use Cloud SQL for MySQL instead

Answer

Export using pg_dump and import via psql

Question 20

A company is using Cloud Spanner and wants to perform disaster recovery testing. They need to validate that their backup and restore process works without impacting production. Which TWO actions should they take? (Choose 2 correct answers.)

Accepted Answer

Restore a backup to a new database in the same instance using a different database ID. Option A is correct because Cloud Spanner backups can be restored to a new database within the same instance by specifying a different database ID, which lets the team validate the restore process without touching the production database. Option D is correct because restoring a backup to a new Spanner instance in a separate test project provides full isolation from production, allowing disaster recovery testing with no impact on live workloads. Option B is not appropriate because exporting to Cloud Storage and importing is a data movement method, not the native Cloud Spanner backup/restore validation path, and it does not test the backup restore process itself. Option C is incorrect because read replica failover tests availability and replication behavior, not backup and restore. Option E is incorrect because restoring directly to the production database would overwrite or disrupt production data, violating the requirement to avoid impacting production.

Answer

Export the database to Cloud Storage and import it into a new instance

Answer

Perform a failover to a read replica and then fail back

Answer

Directly restore a backup to the production database to test recovery time