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

Question 1

A data engineer needs to implement data lineage tracking for a BigQuery data warehouse. They want to automatically capture column-level lineage from ETL jobs run by Dataform and from manual SQL queries executed in the BigQuery console. Which approach meets these requirements?

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

Accepted Answer

Use BigQuery's built-in column-level lineage, which automatically tracks lineage for all queries, and query it using INFORMATION_SCHEMA.JOBS. BigQuery's column-level lineage is automatically captured for all queries (including Dataform and console) and can be queried via the INFORMATION_SCHEMA.JOBS_BY_USER views. Dataplex lineage requires integration but BigQuery lineage is native.

Answer

Enable Dataplex Universal Catalog and use the Dataplex Lineage API to capture lineage from both sources

Answer

Use the BigQuery Lineage API provided by Data Catalog to register lineage manually

Answer

Export Dataform execution logs to Cloud Logging and use a custom script to extract lineage

Question 2

A company is using Looker to explore their BigQuery data. They have defined a LookML model with an 'explore' that joins two views: 'orders' and 'customers'. The join is a left join. They want to ensure that only customers with orders are shown when exploring. Which LookML parameter should they modify?

Accepted Answer

Change the join type from 'left_outer' to 'inner'. The 'join_type' parameter in LookML defines the join type. Changing it to 'inner' will exclude customers without orders.

Answer

Set the 'required_joins' parameter on the 'orders' view

Answer

Use a derived table with a WHERE clause

Answer

Add a filter in the 'customers' view to exclude nulls

Question 3

A data engineer is building a feature store for ML models using Vertex AI Feature Store. The features are computed daily from BigQuery and need to be available for both online predictions (low latency) and offline training. Which two actions must the engineer take? (Choose TWO)

Accepted Answer

Create an entity type in the feature store. Option D is correct because a Vertex AI Feature Store is organized around entity types, which define the real-world objects (such as users or products) whose features are stored and served; creating an entity type is a mandatory step before features can be ingested and served. Option E is correct because the scenario explicitly requires low-latency online predictions, and online serving must be enabled on the feature store so features can be served at low latency for real-time inference. The daily BigQuery-computed features also support offline training, but that is handled through the feature store's offline store (backed by BigQuery) rather than requiring a separate BigQuery table, so option C is not a required action. Option A is incorrect because deploying a custom TensorFlow model is unrelated to building or configuring the feature store. Option B is incorrect because Cloud SQL is not used to store Vertex AI Feature Store metadata; the feature store manages its own metadata and uses BigQuery for offline storage.

Answer

Deploy a custom TensorFlow model on Vertex AI

Answer

Use Cloud SQL to store feature metadata

Answer

Create a BigQuery table for offline serving

Question 4

An e-commerce company uses BigQuery to analyze customer behavior. They need to compute the number of distinct customers per day, approximate quantiles of purchase amounts, and assign a row number per customer partition by date. Which BigQuery SQL functions should they use? (Choose THREE)

Accepted Answer

APPROX_COUNT_DISTINCT(customer_id). Option A, APPROX_COUNT_DISTINCT(customer_id), is correct because it returns an approximate count of distinct customer IDs using a HyperLogLog++ sketch, which is efficient for large-scale cardinality estimation such as distinct customers per day. Option C, APPROX_QUANTILES(purchase_amount, 100), is correct because it computes approximate quantile boundaries (here 100 quantiles, i.e., percentiles) of purchase amounts without the cost of exact quantile computation. Option D, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date), is correct because it assigns a sequential row number within each customer partition ordered by date, exactly matching the requirement to number rows per customer partition by date. Option B, NTILE(4) OVER (ORDER BY purchase_amount), is not correct because NTILE divides rows into a specified number of buckets (4 quartiles) rather than computing quantiles of purchase amounts as requested. Option E, COUNT(DISTINCT customer_id), is not correct because it computes an exact distinct count, which is more resource-intensive and does not provide the approximate distinct count the scenario calls for.

Answer

NTILE(4) OVER (ORDER BY purchase_amount)

Answer

COUNT(DISTINCT customer_id)

Question 5

A company uses Dataplex to manage data quality across multiple BigQuery datasets. They need to define data quality rules that check for null values in critical columns and enforce uniqueness constraints. Which two Dataplex features should they use? (Choose TWO)

Accepted Answer

Dataplex Data Quality Rules. Dataplex Data Quality can define rules (including null check and uniqueness) and schedule them as Data Quality Tasks. Data Quality Rules are defined in YAML and can be attached to entities.

Answer

Dataplex Lake

Answer

Dataplex Data Lineage

Answer

Dataplex Universal Catalog

Question 6

A data engineer is preparing a dataset for ML training in Vertex AI. The dataset includes a timestamp column, a categorical column with high cardinality (1000 distinct values), and a numerical column with outliers. Which two preprocessing steps should they apply? (Choose TWO)

Accepted Answer

Label encode the categorical column. Option B is correct because label encoding maps each of the 1000 distinct categories to a unique integer, which is the practical choice for high-cardinality categorical features; one-hot encoding would explode the feature space into 1000 sparse columns and cause the curse of dimensionality. Option C is correct because Winsorizing caps extreme values at a chosen percentile (e.g., 1st/99th or 5th/95th), reducing the disproportionate influence of outliers on model training while retaining those records. Option A is not required: timestamps can be valuable features (e.g., converted to cyclical or lag features), so dropping the column is not a general best practice. Option D is not the priority here: Z-score normalization rescales but does not address outliers, and it is sensitive to them since mean and standard deviation are themselves distorted by extreme values. Option E is incorrect for the same reason as noted above: one-hot encoding a 1000-value categorical column produces excessive dimensionality and sparsity.

Answer

Drop the timestamp column

Answer

Normalize the numerical column using Z-score

Answer

One-hot encode the categorical column

Question 7

A company wants to use BigQuery ML to train a time-series forecasting model on historical sales data. The data is recorded daily for 3 years. They need to evaluate model accuracy using time-series aware cross-validation. Which two options should they configure in the CREATE MODEL statement? (Choose TWO)

Accepted Answer

Specify a time_series_timestamp_col and time_series_data_col. Option E is correct because BigQuery ML's dedicated time-series model type is ARIMA_PLUS, which is designed for forecasting on historical time-series data and supports time-series-aware evaluation. Option C is correct because ARIMA_PLUS requires specifying the time_series_timestamp_col (the column containing the time points) and time_series_data_col (the column containing the values to forecast) so the model knows which series to learn from. Option A is not a valid CREATE MODEL parameter for this purpose; data frequency is inferred or set via other ARIMA_PLUS options, not data_frequency. Option B, num_trials, is for hyperparameter tuning and does not provide time-series cross-validation. Option D, split_method set to 'time_series', is not a supported BigQuery ML split method; time-series evaluation is handled by ARIMA_PLUS-specific settings rather than a generic split_method value.

Answer

Set the data_frequency parameter to 'daily'

Answer

Use the 'num_trials' parameter for hyperparameter tuning

Answer

Set the 'split_method' to 'time_series'

Question 8

A data engineer needs to build a real-time dashboard in Looker Studio that displays live sales data from BigQuery. The dashboard must refresh every minute. The underlying BigQuery table is updated continuously via streaming inserts. Which two approaches can reduce query cost and latency? (Choose TWO)

Accepted Answer

Use BigQuery BI Engine to accelerate the Looker Studio queries. Option D is correct because BigQuery BI Engine provides an in-memory analysis layer that accelerates Looker Studio queries against BigQuery, dramatically reducing latency for dashboards that refresh frequently, while also lowering query cost since BI Engine can serve queries without full BigQuery scan charges. Option E is correct because partitioning the table by the date column enables partition pruning, so queries that filter on the date (as a live sales dashboard typically does) scan only the relevant partitions instead of the entire table, reducing both bytes processed (cost) and query time (latency). Option A is not appropriate because the BigQuery Data Transfer Service is designed for scheduled batch ingestion from sources like SaaS apps and Cloud Storage, not for reducing query cost or latency on a continuously streaming table. Option B is not suitable because BigQuery materialized views cannot be built over tables that receive streaming inserts in real time; they require base tables that are not actively streamed to, so they would not stay fresh for a one-minute refresh dashboard. Option C is wrong because exporting to Cloud Storage and loading into Cloud SQL introduces significant latency and operational overhead, and Cloud SQL is not the right engine for serving live BigQuery sales data to Looker Studio.

Answer

Use the BigQuery Data Transfer Service to copy data to a separate dataset

Answer

Create a materialized view that pre-aggregates the data

Answer

Schedule a script to export the table to Cloud Storage and load into Cloud SQL

Question 9

A company stores sensitive customer data in BigQuery. They need to implement column-level security to restrict access to personally identifiable information (PII) columns. Which two BigQuery features can they use together? (Choose TWO)

Accepted Answer

Authorized views. Option E, BigQuery column-level access control using policy tags, is correct because policy tags are attached to specific columns via the Data Catalog taxonomy and enforce column-level access control through IAM roles such as roles/datacatalog.categoryFineGrainedReader, which restricts who can query PII columns. Option D, Authorized views, is correct because an authorized view grants users access to query the view's results without granting them direct access to the underlying table, so PII columns can be excluded or masked in the view while the base table remains restricted. Together, policy tags protect the raw PII columns and authorized views provide controlled, filtered access to non-PII data. Option A, row-level security, is not correct because it filters rows via filter predicates, not columns. Option B, foreign key constraints, is not correct because it enforces referential integrity between tables, not access control. Option C, data masking, is not correct because it is a data governance feature that obfuscates values but is not the column-level access control mechanism being asked for here.

Answer

BigQuery row-level security

Answer

BigQuery foreign key constraints

Answer

BigQuery data masking

Question 10

Your team has a BigQuery table `events` with a TIMESTAMP column `event_time` and a STRING column `user_id`. You need to add a new column that assigns each event to a 10-minute time bucket, and you want the bucket start time as a TIMESTAMP. Which SQL expression should you use?

Accepted Answer

TIMESTAMP_SECONDS(DIV(UNIX_SECONDS(event_time), 600) * 600). To assign events to 10-minute buckets, you need to floor the timestamp to the nearest 10-minute boundary. The expression using UNIX_SECONDS, integer division by 600, and multiplication back to seconds achieves this precisely. It works across hour and day boundaries and is efficient. Other options either use non-existent functions or rely on minute extraction that may not handle all edge cases as cleanly.

Answer

TIMESTAMP_TRUNC(event_time, HOUR) + INTERVAL DIV(EXTRACT(MINUTE FROM event_time), 10) * 10 MINUTE

Answer

TIMESTAMP_TRUNC(event_time, MINUTE) - INTERVAL MOD(EXTRACT(MINUTE FROM event_time), 10) MINUTE

Answer

TIMESTAMP_BUCKET(event_time, INTERVAL 10 MINUTE)

Question 11

Your team uses Cloud Dataflow to process streaming data from Pub/Sub and write to BigQuery. You need to ensure exactly-once processing semantics to avoid duplicate records in BigQuery. Which Dataflow feature should you use?

Accepted Answer

Use the BigQueryIO.Write with the STORAGE_WRITE_API method and set withExactlyOnce to true.. To achieve exactly-once processing when writing to BigQuery from Dataflow, use the BigQueryIO.Write transform with the STORAGE_WRITE_API method and set withExactlyOnce to true. This configuration ensures that records are not duplicated, leveraging the Storage Write API's support for exactly-once semantics. Other methods like FILE_LOADS do not provide exactly-once for streaming.

Answer

Enable Streaming Engine.

Answer

Use the BigQueryIO.Write with the STORAGE_WRITE_API method and set the write disposition to WRITE_APPEND.

Answer

Use the BigQueryIO.Write with the STORAGE_WRITE_API method and set withNumStorageWriteApiStreams to 0.

Question 12

You are designing a BigQuery table to store clickstream events. The events have a nested structure: each event has a `user_id`, `timestamp`, and an array of `items` (each item has `product_id` and `price`). You want to optimize query performance for queries that filter on `product_id` and `price` within the array. Which table schema and partitioning/clustering strategy should you use?

Accepted Answer

Flatten the items array into separate rows, partition by `timestamp`, and cluster on `product_id`.. To optimize filtering on product_id and price within an array, flattening the array into separate rows enables clustering on product_id, which is a top-level column. Partitioning by timestamp further reduces scanned data for time-based queries. This schema change allows BigQuery to prune data using both partition and cluster keys, significantly improving performance for the specified filters.

Answer

Use a nested RECORD column for `items` and create a search index on `items.product_id`.

Answer

Use a nested RECORD column for `items`, partition the table by `timestamp`, and cluster on `user_id`.

Answer

Use a nested RECORD column for `items` and cluster the table on `items.product_id`.

Question 13

Your team is using BigQuery to analyze a large dataset of user events. You need to optimize a query that filters on a nested field 'user_details.country' and also joins with a smaller dimension table. The query currently performs a full table scan. You want to reduce the amount of data processed and improve performance. Which TWO actions should you take? (Choose two.)

Accepted Answer

Create a clustered table on the nested field 'user_details.country' to improve filter performance.. Partitioning by event timestamp and clustering on the nested field 'user_details.country' both reduce the amount of data scanned by enabling partition pruning and block pruning respectively. These actions directly address the full table scan and improve performance for filters on nested fields, making them the correct choices.

Answer

Use the UNNEST operator on the nested field to flatten it before filtering, then apply the filter on the flattened column.

Answer

Enable the 'use_legacy_sql' option to allow more efficient processing of nested fields.

Answer

Increase the number of slots in the project to speed up the query.

Question 14

A retail company ingests point-of-sale events into a BigQuery table named `sales_raw` with a TIMESTAMP column `transaction_ts` and a FLOAT64 column `amount`. Analysts frequently run reports that aggregate sales by calendar month. To reduce query cost and improve performance, a data engineer wants to create a new table `sales_monthly` that stores only the month (as a DATE truncated to the first day) and the total sales amount per month. Which approach should the engineer use?

Accepted Answer

Create a new table using a CREATE TABLE AS SELECT statement that groups by DATE_TRUNC(transaction_ts, MONTH) and sums amount.. Creating a new table with CREATE TABLE AS SELECT physically stores the pre-aggregated monthly totals, so subsequent queries read far less data and run faster. A view or materialized view does not provide the same static, pre-computed result in this scenario, and partitioning alone does not aggregate data. The CREATE TABLE AS SELECT approach directly satisfies the need to reduce cost and improve performance for monthly sales reports.

Answer

Partition `sales_raw` by the DATE_TRUNC(transaction_ts, MONTH) expression and rely on partition pruning for monthly aggregations.

Answer

Create a materialized view on `sales_raw` that groups by DATE_TRUNC(transaction_ts, MONTH) and sums amount.

Answer

Create a view on `sales_raw` that groups by DATE_TRUNC(transaction_ts, MONTH) and sums amount.

Question 15

You are designing a Dataflow pipeline that reads from Pub/Sub and writes to BigQuery. The pipeline must handle late-arriving data and ensure that all data is processed exactly once, even in the event of worker failures. You need to choose a windowing strategy and triggering mechanism. Which combination should you use?

Accepted Answer

Use fixed windows with allowed lateness and a single trigger that fires at the end of the window plus allowed lateness, and enable exactly-once by using BigQueryIO write with withMethod(FILE_LOADS).. To achieve exactly-once processing with late data, use fixed windows with allowed lateness and a single trigger that fires after the lateness period. This ensures each window is emitted once. BigQueryIO with FILE_LOADS provides exactly-once semantics by using atomic file loads. Other windowing types or triggers may cause multiple emissions, and STREAMING_INSERTS does not support exactly-once.

Answer

Use session windows with a gap duration and enable exactly-once by using BigQueryIO write with withMethod(STREAMING_INSERTS) and withTriggeringFrequency.

Answer

Use sliding windows with allowed lateness and late triggers, and enable exactly-once by using BigQueryIO write with withMethod(FILE_LOADS) and withNumFileShards.

Answer

Use fixed windows with allowed lateness and early triggers, and enable exactly-once processing by using the BigQueryIO write with withMethod(FILE_LOADS) and withTriggeringFrequency.