PDE Practice Test 21 — 15 Questions

Question 1

A financial services firm stores transaction records in a BigQuery table partitioned by transaction_date. Compliance requires that rows older than seven years be permanently and irreversibly deleted, and the team must prove deletion occurred. The table currently uses the default partition expiration of never. The engineer must implement the retention policy without dropping the whole table. (Choose two.)

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

Accepted Answer

Verify retention by querying INFORMATION_SCHEMA.PARTITIONS to confirm partitions beyond the retention window no longer exist.. Partition expiration is the native BigQuery mechanism that irreversibly removes whole partitions once they age past the retention window, and it operates without dropping the table so recent data stays available. The compliance proof comes from INFORMATION_SCHEMA.PARTITIONS, which shows exactly which partitions remain. A scheduled DELETE leaves recoverable copies through time travel and fail-safe, Cloud Storage lifecycle rules cannot see BigQuery-managed storage, and key destruction is not selective enough to age out only old rows.

Answer

Use Cloud Key Management Service with a customer-managed encryption key and destroy the key version after seven years.

Answer

Configure a Cloud Storage lifecycle rule on the table's underlying data to remove objects older than seven years.

Answer

Create a scheduled query that runs DELETE FROM the table WHERE transaction_date < DATE_SUB(CURRENT_DATE(), INTERVAL 7 YEAR).

Question 2

A healthcare company stores patient records in Cloud SQL for PostgreSQL and needs to run analytical queries on the same data without impacting the production database. The analytics team requires near-real-time replication and wants to use BigQuery for querying. The data must be kept in sync with minimal latency and without writing custom ETL code. Which solution should the data engineer implement?

Accepted Answer

Use Datastream to replicate from Cloud SQL for PostgreSQL to BigQuery with a dataset-level replication. Datastream provides serverless change data capture from Cloud SQL for PostgreSQL to BigQuery. It reads the database's write-ahead log and applies changes to BigQuery with low latency, keeping the analytical store in sync without custom code. This offloads analytics from the production database and meets the near-real-time and no-ETL requirements. Other options either introduce batch latency, require pipeline development, or do not create a separate analytical store.

Answer

Create a read replica of the Cloud SQL instance and point BigQuery federated queries at the replica

Answer

Use Cloud Data Fusion to build a pipeline that reads from Cloud SQL and writes to BigQuery on a schedule

Answer

Schedule a nightly export from Cloud SQL to Cloud Storage and load the files into BigQuery

Question 3

You need to load a large CSV file from Cloud Storage into BigQuery. The file contains a header row and is comma-delimited. You want to ensure that the header row is skipped and that the schema is automatically detected. Which BigQuery load option should you use?

Accepted Answer

Set skip_leading_rows to 1 and autodetect to true.. To load a CSV with a header row and automatically detect the schema, you should use skip_leading_rows=1 to skip the header and autodetect=true to infer the schema. These options are part of the load job configuration in BigQuery. They streamline the loading process and reduce manual schema definition.

Answer

Set allow_jagged_rows to true and ignore_unknown_values to true.

Answer

Set field_delimiter to '	' and skip_leading_rows to 0.

Answer

Set max_bad_records to 1 and autodetect to false.

Question 4

A logistics company writes shipment telemetry to a Cloud Bigtable table using a row key of shipment_id, where shipment_id values are monotonically increasing and roughly sequential over time. Write throughput has become uneven, with a small number of tablet servers overloaded while others sit idle. The engineer must improve write distribution without changing the schema of the column families. What should the engineer do?

Accepted Answer

Prepend a hashed or reversed component to the row key so writes spread across the key space instead of concentrating at the high end.. Bigtable distributes rows by row key ranges, so a monotonically increasing key sends every new write to the tablet owning the highest range, overloading one node while others idle. Adding a hash bucket prefix or reversing the key scatters writes across many ranges and tablets, restoring even load without altering column families. Automatic rebalancing, extra column families, and replication clusters all leave the underlying key concentration in place, so the hotspot persists.

Answer

Create a Cloud Bigtable replication cluster in a second zone and write half the traffic to each cluster.

Answer

Increase the number of column families in the table to spread the write load across more storage structures.

Answer

Enable automatic region rebalancing so Bigtable redistributes the hot rows across the cluster.

Question 5

A company processes financial transactions using Cloud Dataflow. They need to ensure that late-arriving data is handled correctly for fraud detection. The pipeline uses event time processing. Which approach should they use to handle late data?

Accepted Answer

Fixed windows with allowed lateness. Fixed windows with allowed lateness are the standard approach in Cloud Dataflow (Apache Beam) for handling late-arriving data in event-time processing. By specifying an allowed lateness duration, the pipeline retains the window state for that period, allowing late events to be correctly assigned to their original window and triggering recomputation of results. This ensures fraud detection pipelines can account for delayed transactions without missing or misordering data.

Answer

Sliding windows with early firing

Answer

Session windows with gap duration

Answer

Global windows with triggers

Question 6

Your team maintains a BigQuery dataset where a partitioned table 'transactions' is partitioned by DATE(transaction_time) and clustered by customer_id. Analysts frequently run queries filtering on a specific customer_id and a date range of the last 7 days. They report that these queries scan more data than expected, and you notice that the table was created without a partition filter requirement but with a clustering column. Which action should you take to optimize query performance and reduce bytes scanned?

Accepted Answer

Ensure that queries filter on the clustering column customer_id and use the partition column in the WHERE clause to allow partition pruning and cluster pruning.. The table is partitioned by DATE(transaction_time) and clustered by customer_id. To optimize queries that filter on customer_id and a date range, analysts must ensure their WHERE clause includes both the partition column and the clustering column without transformations. This enables partition pruning and cluster pruning, minimizing data scanned. Requiring a partition filter or changing clustering does not fix the root cause if queries are not written to leverage the existing schema.

Answer

Add a partition filter requirement to the table using ALTER TABLE SET OPTIONS(require_partition_filter = true).

Answer

Recreate the table with clustering on both customer_id and transaction_time to improve filter effectiveness.

Answer

Change the table to use ingestion-time partitioning instead of column-based partitioning to reduce overhead.

Question 7

A media company wants to analyze clickstream data stored in Cloud Storage as JSON files. They need to run SQL queries directly on these files without loading them into BigQuery. Which BigQuery feature should they use?

Accepted Answer

External tables. External tables enable BigQuery to query data stored in Cloud Storage directly, supporting JSON and other formats. This avoids data duplication and loading costs, making it ideal for ad-hoc analysis of raw files. Other features either load data into BigQuery or serve different purposes.

Answer

BigQuery Data Transfer Service

Answer

BigQuery Omni

Answer

BigQuery BI Engine

Question 8

A media company stores video files in a Cloud Storage bucket and wants to serve them to users through a custom domain over HTTPS. The files are not sensitive, but the company wants to reduce egress cost and latency for users distributed globally. The company also wants to avoid managing SSL certificates manually. What should the data engineer recommend?

Accepted Answer

Create a Cloud CDN backend bucket with the Cloud Storage bucket as the origin and enable Google-managed SSL certificates on the load balancer.. Cloud CDN with a backend bucket caches video content at Google's edge locations, lowering latency and egress for global users. Pairing it with a global external Application Load Balancer and Google-managed SSL certificates delivers HTTPS on a custom domain without the operational burden of managing certificates.

Answer

Enable Object Versioning on the bucket and configure a lifecycle rule to move objects to Nearline after 30 days.

Answer

Set the bucket's default storage class to Standard and enable uniform bucket-level access.

Answer

Generate signed URLs for each video and distribute them to users through the application.

Question 9

You are preparing data in BigQuery for analysis. You have a table `orders` with columns `order_id`, `customer_id`, `order_date`, and `amount`. You need to create a new table that includes all orders, plus a column `prev_order_amount` that contains the amount of the customer's previous order by `order_date`. If there is no previous order, the value should be NULL. Which SQL feature should you use?

Accepted Answer

Use the LAG window function partitioned by `customer_id` and ordered by `order_date`.. The LAG window function is designed to access a previous row's value within a partition. By partitioning by customer and ordering by order date, it returns the amount of the customer's previous order. This is efficient and avoids self-joins. Other window functions like LEAD look forward, and FIRST_VALUE returns the first value in the partition, not the previous row.

Answer

Use the LEAD window function partitioned by `customer_id` and ordered by `order_date`.

Answer

Use a self-join on `customer_id` where the previous order date is the maximum order date less than the current order date.

Answer

Use the FIRST_VALUE window function partitioned by `customer_id` and ordered by `order_date`.

Question 10

You need to load a 2 TB CSV file from Cloud Storage into BigQuery. The CSV has a header row and uses a comma delimiter. You want to minimize cost and ensure the load completes quickly. Which method should you use?

Accepted Answer

Run a `bq load` command with `--source_format=CSV` and `--skip_leading_rows=1`.. For large files in Cloud Storage, the most efficient and cost-effective method is a batch load using the `bq load` command or a load job. It supports CSV format, handles header rows with `--skip_leading_rows`, and parallelizes ingestion. This avoids the limitations of UI uploads and the overhead of streaming or scheduled transfers.

Answer

Stream the data using the BigQuery Storage Write API.

Answer

Use the BigQuery web UI to upload the file directly.

Answer

Use the BigQuery Data Transfer Service to schedule a recurring load.

Question 11

You manage a Cloud Composer environment that runs a critical DAG every hour. The DAG includes a task that calls a Cloud Function to process data. Recently, the Cloud Function started taking longer than expected, causing the DAG to exceed its SLA. You need to detect this delay and automatically retry the task if it fails due to timeout, while minimizing changes to the DAG. What should you do?

Accepted Answer

Set the task's execution_timeout parameter to a value slightly above the expected runtime and configure retries with a delay.. Using the task's execution_timeout parameter allows Airflow to enforce a time limit on the task. If the task exceeds this limit, it fails and can be automatically retried based on the retries and retry_delay settings. This requires only a small change to the DAG's task definition and leverages native Airflow features, effectively addressing both detection of delays and automatic retries.

Answer

Use a Cloud Monitoring alert on the Cloud Function's execution time and manually trigger the DAG upon alert.

Answer

Increase the Cloud Function's timeout setting to the maximum allowed and rely on the DAG's default retry behavior.

Answer

Add a Python operator that polls the Cloud Function's status and raises an exception if it exceeds a threshold, then set retries on that operator.

Question 12

You are using BigQuery to analyze a dataset. You need to create a new table that contains only rows from `sales` where the `region` is 'North America' and the `sale_date` is in the year 2023. You also want to add a column `sale_month` that extracts the month from `sale_date`. Which two actions should you take? (Choose two.)

Accepted Answer

Use the EXTRACT function in the SELECT list to create the `sale_month` column.. To filter rows by region and date, a WHERE clause is required. To add a new column with the month, use the EXTRACT function in the SELECT list. HAVING is for aggregated groups, FORMAT_DATE returns a string, and LIMIT does not filter conditionally. Thus, the correct actions are using a WHERE clause and using EXTRACT.

Answer

Use the FORMAT_DATE function to create the `sale_month` column.

Answer

Use a LIMIT clause to restrict the number of rows to only those in 2023.

Answer

Use a HAVING clause to filter on `region` and `sale_date`.

Question 13

Your team stores IoT sensor readings in a BigQuery table `project.sensors.readings` with columns `sensor_id` (STRING), `reading_time` (TIMESTAMP), and `temperature` (FLOAT64). You need to create a new table that adds a column `avg_temp_7d` containing, for each row, the average temperature of that sensor over the preceding 7 days (including the current row). Which SQL feature should you use to compute this efficiently?

Accepted Answer

A window function with a RANGE-based frame between 7 days preceding and CURRENT ROW. A window function with a RANGE frame based on the timestamp column correctly computes a rolling 7-day average per sensor. It is efficient because BigQuery processes the window in a single pass, and the RANGE clause dynamically adjusts the window based on the actual time values, not row counts. This meets the requirement of including the current row and the preceding 7 days.

Answer

A GROUP BY on sensor_id and a date truncation to week, then joining back to the original table

Answer

A user-defined function (UDF) that loops over the previous 7 days and accumulates temperatures

Answer

A correlated subquery that selects the average temperature where reading_time is between the current row's time minus 7 days and the current row's time

Question 14

You need to load a large CSV file from Cloud Storage into BigQuery. The file has a header row and contains a column with date values in the format 'YYYY-MM-DD'. You want to ensure the date column is correctly recognized as a DATE type. Which method should you use?

Accepted Answer

Define an explicit schema in JSON or inline that specifies the date column as DATE, and provide it during the load job.. Defining an explicit schema that specifies the date column as DATE ensures BigQuery correctly interprets the values during load. This method is reliable and avoids the uncertainty of autodetect or post-load casting, making it the best practice for date columns with a known format.

Answer

Use the bq load command with the --autodetect flag and no schema definition.

Answer

Load the file as a string column, then use a SQL query to cast the string to DATE after loading.

Answer

Convert the CSV to a newline-delimited JSON file with date values as strings, then load with autodetect.

Question 15

A data engineer needs to create a unified table that combines data from Cloud Storage (Parquet files) and BigQuery native tables, with fine-grained access control and governance. Which three Google Cloud features should they use together? (Choose THREE.)

Accepted Answer

BigQuery. BigQuery is correct because it serves as the unified query engine that can read data from both Cloud Storage (via external tables or BigLake) and native BigQuery tables, enabling a single SQL interface for analysis. It also integrates with fine-grained access control through row-level security and column-level access policies, and supports governance via Data Catalog and VPC Service Controls.

Answer

Dataproc

Answer

Cloud SQL