PDE Preparing and Using Data for Analysis Practice Test 9 — 15 Questions

Question 1

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?

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

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 2

You are ingesting streaming data into BigQuery using the Storage Write API. The data arrives with occasional duplicate records due to at-least-once delivery semantics from the source system. You need to ensure that the final BigQuery table contains no duplicates based on a unique event_id column. Which approach should you use?

Accepted Answer

Use the Storage Write API with application-created streams and specify a unique offset for each record to enable exactly-once semantics.. To achieve exactly-once semantics with the Storage Write API, you must use application-created streams and assign a unique offset to each record. This allows BigQuery to deduplicate records on write, ensuring no duplicates even with retries. The default stream does not provide exactly-once semantics, and BigQuery lacks unique constraints. MERGE is a batch alternative but not ideal for streaming.

Answer

Enable the exactly-once delivery semantics in the Storage Write API by using the default stream and specifying a unique offset for each record.

Answer

Use BigQuery's MERGE statement to upsert records into the table based on event_id, running it periodically to deduplicate.

Answer

Create a unique constraint on the event_id column in the BigQuery table to automatically reject duplicates.

Question 3

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 4

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 5

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 6

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 7

A financial services firm stores customer transaction data in a BigQuery table. The table contains a column `customer_id` that is frequently used in WHERE clauses, but the table is not partitioned. Queries filtering on a specific `customer_id` scan the entire table, which is large and costly. The data engineer wants to reduce the bytes scanned for these queries without changing the table's partitioning scheme. What should the engineer do?

Accepted Answer

Create a clustered table on `customer_id` by using a CREATE TABLE ... CLUSTER BY customer_id statement and loading the data into it.. Clustering on `customer_id` organizes the table data so that BigQuery can skip blocks that do not contain the requested customer ID, thereby reducing bytes scanned and improving query performance. Partitioning is not suitable for a high-cardinality identifier like `customer_id`, and materialized views or partition filters do not address the specific need. Clustering is the correct technique for optimizing filters on a non-partitioned table.

Answer

Enable the `require_partition_filter` option on the table and add a partition on a date column.

Answer

Create a materialized view that selects all columns and filters on `customer_id`.

Answer

Add a partition on `customer_id` using a CREATE TABLE ... PARTITION BY customer_id statement.

Question 8

You are loading a CSV file into BigQuery. The file has a column `price` that contains values like `$1,234.56`. You want to load it into a NUMERIC column. What should you do?

Accepted Answer

Preprocess the CSV file to remove the dollar signs and commas before loading, or use a transformation in a data pipeline.. BigQuery requires numeric columns to contain plain numeric values without formatting characters. To load a CSV with values like `$1,234.56` into a NUMERIC column, you must remove the dollar signs and commas, either by preprocessing the file or using a data transformation step. Options that suggest loading as-is or using unrelated options will fail.

Answer

Use the `--null_marker` option to treat the dollar sign as null.

Answer

Specify the schema with `price` as a NUMERIC and set the `--allow_quoted_newlines` option.

Answer

Load the data as a STRING and then use a CAST to NUMERIC in a query to create a new table.

Question 9

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 10

You have a BigQuery table `logs` with a column `message` that contains JSON strings. You need to extract the value of the `user_id` field from each JSON string and return it as a separate column. Which function should you use?

Accepted Answer

JSON_EXTRACT_SCALAR(message, '$.user_id'). The JSON_EXTRACT_SCALAR function is designed to extract scalar values from JSON strings using JSONPath. It returns the value as a STRING, which is ideal for extracting user_id. Other functions either return JSON-formatted strings, rely on brittle regex, or use invalid syntax. For robust JSON parsing in BigQuery, JSON_EXTRACT_SCALAR is the correct choice.

Answer

JSON_EXTRACT(message, '$.user_id')

Answer

REGEXP_EXTRACT(message, r'"user_id":\s*"([^"]+)"')

Answer

PARSE_JSON(message).user_id

Question 11

You are designing a BigQuery table to store clickstream events. Queries will frequently filter by a user_id column and a TIMESTAMP column named event_time, and the table will grow to several petabytes. You want to minimize bytes scanned and cost for these queries. Which TWO actions should you take? (Choose two.)

Accepted Answer

Partition the table by the DATE of event_time using time-unit partitioning, and require queries to include a filter on event_time.. For a petabyte-scale clickstream table filtered by event_time and user_id, partitioning on event_time enables partition pruning, and clustering on user_id enables block-level pruning within partitions. Together they minimize bytes scanned. Materialized views, table expiration, and BI Engine do not provide the same structural cost reduction for arbitrary filters.

Answer

Create a materialized view that pre-aggregates events by user_id and event_time, and query the view instead of the base table.

Answer

Set the table's expiration time to 30 days so that older partitions are automatically deleted, reducing the data volume.

Answer

Enable the BigQuery BI Engine reservation and route all queries through it to cache results in memory.

Question 12

You are designing a BigQuery schema for a table that will store user profile data. The data includes a unique user ID, a list of email addresses (each with a type and address), and a timestamp of last update. You need to support efficient queries that retrieve all email addresses for a given user and also filter users by email type. Which schema design should you use?

Accepted Answer

Store emails as a repeated RECORD with fields 'type' and 'address' within the user table, using dot notation and UNNEST to query.. Using a repeated RECORD for emails allows BigQuery to store multiple emails per user with type and address fields. Queries can UNNEST the array to filter by email type and use dot notation to access fields. This denormalized design is efficient, avoids joins, and aligns with BigQuery's strengths for nested data.

Answer

Store emails as a single STRING column with a delimiter, and use SPLIT and REGEXP_CONTAINS to query.

Answer

Store emails as a JSON STRING column and use JSON functions to extract values.

Answer

Create a separate table for emails with a foreign key to the user table, and join when needed.

Question 13

You are using Cloud Dataflow to stream data from Pub/Sub into BigQuery. The incoming messages are JSON strings with a field `event_time` in ISO 8601 format (e.g., "2024-03-15T14:30:00Z"). You need to write the data to a BigQuery table with a column `event_timestamp` of type TIMESTAMP. Which transformation should you apply in your Dataflow pipeline?

Accepted Answer

Use a ParDo to parse the JSON and convert `event_time` to a BigQuery TIMESTAMP using the appropriate library for your pipeline language (e.g., Instant.parse in Java or datetime.fromisoformat in Python).. The correct approach is to parse the JSON and convert the ISO 8601 string to a native timestamp type within the Dataflow pipeline. This ensures that the data written to BigQuery matches the TIMESTAMP column type. BigQueryIO expects the input PCollection to contain TableRow objects with fields matching the destination schema, so converting the string to a timestamp before writing is necessary.

Answer

Use a SQL query in BigQuery to convert the string to TIMESTAMP after the data is loaded into a staging table.

Answer

Configure BigQueryIO to write the `event_time` field as a STRING and rely on BigQuery's automatic schema detection to convert it to TIMESTAMP.

Answer

In the Dataflow pipeline, use a WithTimestamps transform to assign the event time as the element timestamp, and then write the original string to BigQuery.

Question 14

You have a BigQuery table `sales` with columns `order_id`, `customer_id`, `order_date` (DATE), and `amount` (NUMERIC). You need to create a new table that contains, for each customer, the total sales amount and the date of their most recent order. The result should include `customer_id`, `total_amount`, and `last_order_date`. Which SQL query achieves this correctly?

Accepted Answer

SELECT customer_id, SUM(amount) AS total_amount, MAX(order_date) AS last_order_date FROM `project.dataset.sales` GROUP BY customer_id. To compute per-customer aggregates, you use GROUP BY on `customer_id` and aggregate functions SUM and MAX. The SUM function totals the amounts, and MAX retrieves the latest order date. Other approaches using window functions or incorrect grouping do not produce the required single-row-per-customer result. The correct query is straightforward and uses standard SQL aggregation.

Answer

SELECT customer_id, SUM(amount) AS total_amount, order_date AS last_order_date FROM `project.dataset.sales` GROUP BY customer_id, order_date

Answer

SELECT customer_id, SUM(amount) AS total_amount, LAST_VALUE(order_date) AS last_order_date FROM `project.dataset.sales` GROUP BY customer_id

Answer

SELECT customer_id, SUM(amount) AS total_amount, MAX(order_date) OVER (PARTITION BY customer_id) AS last_order_date FROM `project.dataset.sales`

Question 15

Your team uses Cloud Dataflow to stream events from Pub/Sub into BigQuery. Some events arrive late, up to 10 minutes after their event timestamp. You need the pipeline to produce correct aggregations per 5-minute window, including late data, while keeping latency low for on-time events. What should you do?

Accepted Answer

Set the windowing strategy to fixed windows of 5 minutes and configure allowed lateness to 10 minutes with a trigger that emits early results and updates them on late arrivals.. To correctly aggregate late events in Dataflow, you must use event-time windows with allowed lateness so state is retained for late arrivals, and triggers to emit early and updated results. Sink configuration and subscription settings do not affect windowing semantics.

Answer

Set the pipeline's default expansion service to use the BigQuery Storage Write API and enable exactly-once semantics for the sink.

Answer

Configure the Pub/Sub subscription to have a 10-minute acknowledgment deadline and increase the Dataflow worker count to handle the backlog.

Answer

Use a global window with a repeating trigger every 5 minutes and set the accumulation mode to DISCARDING.