PCDE Practice Test 25 — 15 Questions

Question 1

A data team uses BigQuery for ad-hoc BI queries. They have a table with 100 columns. Analysts often select many columns. The table is partitioned by event_date. Queries are slow and expensive. What two-step optimization should they implement? (Note: This is a single correct answer among four options that combine two steps.)

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

Accepted Answer

Cluster the table by commonly used columns and limit the selected columns in queries.. Clustering by commonly used columns organizes data within partitions so that queries scanning only those columns read fewer blocks, reducing bytes processed. Limiting selected columns in queries further reduces the data scanned by avoiding unnecessary column reads. Together, these two steps directly address the high cost and slow performance caused by scanning many columns across a large partitioned table.

Answer

Convert the table to an Avro format and use partitioned tables.

Answer

Partition by event_date and use column-level security.

Answer

Cluster the table by event_date and use SELECT *.

Question 2

A Cloud SQL for PostgreSQL instance is running low on disk space. The database size is 500 GB and the current storage is 600 GB. The engineer needs to increase storage to 800 GB without downtime. What should they do?

Accepted Answer

Use gcloud sql instances patch to increase storage size to 800 GB.. Cloud SQL for PostgreSQL supports online storage increases using the `gcloud sql instances patch` command, which resizes the disk without requiring an instance restart or downtime. The storage can be increased up to the maximum allowed for the instance tier, and the change takes effect immediately while the instance remains available.

Answer

Shut down the instance, edit the configuration to increase storage, then restart.

Answer

Create a new instance with 800 GB storage and migrate data using pg_dump.

Answer

Enable auto-storage increase and wait for it to happen automatically.

Question 3

Which TWO are best practices for designing a Cloud Spanner schema?

Accepted Answer

Use commit timestamp columns to track row versions. Cloud Spanner's commit timestamp columns allow you to automatically track the time of the last write to a row, which is essential for implementing optimistic concurrency control, ordering versions, and building change data capture pipelines. This feature leverages the TrueTime API to provide globally consistent timestamps, making it a best practice for versioning and auditing.

Answer

Avoid secondary indexes to keep writes faster

Answer

Use monotonically increasing primary keys

Answer

Store all related data in a single row to avoid joins

Question 4

An organization is migrating from Teradata to BigQuery. They have 20 TB of data, complex BTEQ scripts, and require near-zero downtime. Which THREE components should be part of their migration plan? (Choose THREE.)

Accepted Answer

Schema Conversion Tool (SCTS). Schema Conversion Tool (SCTS) is correct because it automates the conversion of Teradata-specific DDL, BTEQ scripts, and stored procedures into BigQuery-compatible SQL. This is critical for migrating 20 TB of data and complex BTEQ scripts without manual rewriting, reducing errors and accelerating the migration timeline.

Answer

Cloud SQL Auth Proxy

Answer

Database Migration Service (DMS)

Question 5

A company has a Cloud SQL for PostgreSQL instance that experiences high CPU usage during peak hours due to read-heavy queries. Which optimization is most effective for reducing CPU load?

Accepted Answer

Add read replicas. Adding read replicas offloads read-heavy queries from the primary Cloud SQL for PostgreSQL instance, distributing the query load and reducing CPU utilization on the primary. This is the most direct and effective optimization for read-heavy workloads because replicas handle SELECT traffic while the primary focuses on writes and critical operations.

Answer

Use connection pooling

Answer

Increase memory size

Answer

Enable automatic storage increase

Question 6

An organization wants to restrict the creation of Cloud SQL instances outside of specific regions. Which organization policy constraint should they use?

Accepted Answer

`resourceLocations`. The `resourceLocations` constraint is the correct organization policy to restrict Cloud SQL instance creation to specific regions. This policy defines a list of allowed locations (e.g., `us-central1`, `europe-west1`) where all resources, including Cloud SQL instances, can be created. By setting this constraint at the organization, folder, or project level, any attempt to create a Cloud SQL instance outside the permitted regions will be denied.

Answer

`compute.requireShieldedVm`

Answer

`iam.disableServiceAccountKeyCreation`

Answer

`sql.restrictPublicIp`

Question 7

Which Cloud Monitoring dashboard chart type is best suited to show the distribution of request latencies across multiple services over time?

Accepted Answer

Heatmap. A heatmap is ideal for visualizing the distribution of request latencies across multiple services over time because it uses color intensity to represent value ranges, allowing quick identification of patterns, outliers, and hotspots across two dimensions (e.g., time and service). This makes it well-suited for latency distribution analysis. Other chart types are less effective for this specific need.

Answer

Scorecard

Answer

Line chart

Answer

Stacked bar chart

Question 8

A BI team needs to analyze user behavior with sessionization. Each event has a timestamp and session ID. The table 'sessions' contains columns: session_id, user_id, event_time, event_name. The team wants the first event time per session. Which query is most efficient?

Accepted Answer

SELECT session_id, MIN(event_time) FROM sessions GROUP BY session_id. The most efficient query for this requirement. It uses the MIN aggregate function with GROUP BY, which leverages the database's optimized aggregation engine. This approach requires scanning the table once and grouping by session_id, computing the minimum event_time per group. It is both concise and performant. Option D with QUALIFY and ROW_NUMBER is more complex and may be less efficient because it involves window function processing and sorting within each partition, which is unnecessary when only the minimum time is needed.

Answer

SELECT session_id, ARRAY_AGG(event_time ORDER BY event_time LIMIT 1) FROM sessions GROUP BY session_id

Answer

SELECT a.session_id, a.event_time FROM sessions a INNER JOIN (SELECT session_id, MIN(event_time) min_ts FROM sessions GROUP BY session_id) b ON a.session_id = b.session_id AND a.event_time = b.min_ts

Answer

SELECT session_id, event_time FROM sessions QUALIFY ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time) = 1

Question 9

You want to continuously profile the CPU usage of a production application running on Compute Engine to identify performance bottlenecks. Which Google Cloud service should you use?

Accepted Answer

Cloud Profiler. Cloud Profiler is Google Cloud's continuous CPU and heap profiling service, designed to identify performance bottlenecks in production with low overhead. It collects statistical profiles of function-level CPU and memory usage, which is exactly what's needed to find hotspots in a Compute Engine application. Cloud Trace, Monitoring, and Error Reporting serve different observability roles.

Answer

Cloud Trace

Answer

Cloud Monitoring

Answer

Error Reporting

Question 10

Which THREE of the following SQL techniques are commonly used to improve BI query performance in BigQuery?

Accepted Answer

Avoid JOINs by storing all relevant data in a single table. Denormalizing data into a single table avoids expensive JOIN operations, which in BigQuery can cause significant performance degradation due to shuffling and data redistribution across slots. By storing all relevant data in one table, you reduce the need for large-scale data shuffling, leading to faster query execution and lower slot consumption.

Answer

Select all columns using SELECT * to avoid missing data

Answer

Use self-joins to compare rows within the same table

Question 11

A data warehouse in BigQuery stores event logs with nested and repeated fields (e.g., page views within a session). Which schema type is optimal for storing this data?

Accepted Answer

Use ARRAY> columns for nested repeated data. BigQuery natively supports nested and repeated data through ARRAY> columns, which preserve the hierarchical relationship (e.g., sessions containing page views) without joins and allow efficient, columnar access. Option C is optimal because it matches BigQuery's native storage model, reduces data duplication, and lets queries use UNNEST to flatten only when needed. This is the schema pattern Google recommends for event-log workloads.

Answer

Use RECORD type columns for each nested level

Answer

Normalize into separate tables and join

Answer

Store as JSON strings and parse at query time

Question 12

An engineer configures Cloud Deploy to promote a container to production after manual approval. The deployment strategy must gradually shift traffic from the current version to the new version while monitoring error rates. Which deployment strategy should they select in the delivery pipeline?

Accepted Answer

Canary. Canary deployment is the correct choice because it gradually shifts traffic from the current version to the new version while allowing monitoring of error rates. In Cloud Deploy, a canary strategy uses a sequence of phases (e.g., 10%, 50%, 100%) to incrementally route traffic, enabling real-time observability and automated rollback if error thresholds are breached.

Answer

Blue/green

Answer

Rollback

Answer

Standard

Question 13

A Cloud Spanner database needs to add a column 'discount' to the 'Products' table without any downtime. The table is actively used. What is the correct approach?

Accepted Answer

Execute ALTER TABLE Products ADD COLUMN discount FLOAT64. Cloud Spanner supports online schema changes via ALTER TABLE without downtime. The operation is performed asynchronously in the background, allowing the table to remain fully available for reads and writes. The new column 'discount' is automatically populated with NULL for existing rows. Option A is incorrect because creating a new table and copying data introduces downtime and complexity. Option C is incorrect because a secondary index cannot add a column; it only indexes existing columns. Option D is incorrect because a generated column is derived from other columns, not used to add a new independent column.

Answer

Create a new table with the column and copy data over

Answer

Create a secondary index that includes the new column

Answer

Define a generated column based on an existing column

Question 14

A development team is using Firestore in Native mode. They want to ensure that queries on array fields return correct results. Which two actions should they take? (Choose TWO.)

Accepted Answer

Use the 'array-contains' operator only on indexed arrays.. Option C is correct because Firestore in Native mode supports the array-contains operator, and for such queries to work efficiently and correctly the array field must be indexed; Firestore automatically indexes single fields, so array-contains queries rely on that automatic single-field index. Option E is correct because Firestore automatically creates single-field indexes for all fields, including array fields, so developers do not need to manually index each array field for basic array-contains queries. Option A is not required because composite indexes are only needed for queries that combine multiple fields (e.g., equality plus array-contains plus orderBy), not for every query involving arrays. Option B is incorrect because setting index exemptions for arrays and maps would disable automatic indexing and break array-contains queries. Option D is incorrect because Firestore automatically indexes array fields as single-field indexes, so manual creation for each array field is unnecessary.

Answer

Create a composite index for every query involving array fields.

Answer

Set index exemptions for arrays and maps to disable automatic indexing.

Answer

Manually create indexes for each array field in the Firestore console.

Question 15

An organization wants to monitor a batch processing job that runs daily on Compute Engine. They need to be alerted if the job fails to produce output within 24 hours. Which THREE components should they use? (Choose 3)

Accepted Answer

Create a log-based counter metric that increments upon job completion. Option A is correct because a log-based counter metric is the mechanism that converts the job's completion log entry into a numeric time series that Cloud Monitoring can evaluate; the counter increments each time the job successfully finishes. Option D is correct because a metric-absent condition fires when the expected counter metric stops arriving, and setting the duration to 24h aligns with the requirement to alert if no output/completion occurs within a day. Option C is correct because an alerting policy needs a notification channel (such as email, SMS, or Pub/Sub) to actually deliver the alert to the organization. Option B is not required because dashboards only visualize data and do not generate alerts. Option E is not appropriate because Cloud Trace is for distributed latency tracing of requests, not for detecting a missing batch job completion.

Answer

Create a Cloud Monitoring dashboard to visualize the metric

Answer

Use Cloud Trace to trace the job execution