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

Question 1

You are designing a BigQuery schema for a new table that will store user clickstream events. The events include a user ID, timestamp, page URL, and a list of products viewed. You want to optimize for query performance and cost. Which two schema design choices should you make? (Choose two.)

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

Accepted Answer

Partition the table by the timestamp column.. For clickstream data, using nested RECORDs for related items like products avoids joins and improves performance. Partitioning by timestamp enables partition pruning for time-based queries, reducing cost. Together, these choices optimize query performance and cost. Other options like separate tables or clustering by user ID are less effective for the primary access patterns.

Answer

Cluster the table by the user ID column.

Answer

Store the list of products as a repeated STRING field.

Answer

Use a separate table for products and join on a product ID.

Question 2

You are preparing data in BigQuery for analysis. You need to create a new table that contains only rows where the `status` column is 'active' and includes a new column `days_since_creation` calculated as the difference between the current date and the `creation_date` column. Which TWO SQL statements or clauses should you use to achieve this? (Choose two.)

Accepted Answer

CREATE OR REPLACE TABLE dataset.new_table AS SELECT *, TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), creation_date, DAY) AS days_since_creation FROM dataset.old_table WHERE status = 'active'. To create a new table with filtered rows and a calculated column, you need a CREATE TABLE AS SELECT statement that includes the filter and the calculation. Both DATE_DIFF with CURRENT_DATE() and TIMESTAMP_DIFF with CURRENT_TIMESTAMP() can compute the difference in days, depending on whether creation_date is a DATE or TIMESTAMP. The other options either do not create a table, miss the calculated column, or alter the original table.

Answer

ALTER TABLE dataset.old_table ADD COLUMN days_since_creation INT64

Answer

CREATE TABLE dataset.new_table AS SELECT * FROM dataset.old_table WHERE status = 'active'

Answer

SELECT *, DATE_DIFF(CURRENT_DATE(), creation_date, DAY) AS days_since_creation FROM dataset.old_table WHERE status = 'active'

Question 3

You are responsible for a BigQuery dataset that receives daily batch loads from an on-premises Oracle database using the Storage Write API. The ingestion pipeline occasionally inserts duplicate rows when the upstream job retries after a network timeout. You need to guarantee that each business transaction appears exactly once in the BigQuery table, without relying on downstream deduplication. What should you do?

Accepted Answer

Use BigQuery's MERGE statement in the pipeline to upsert each batch, matching on the transaction ID, so that retried rows update existing records instead of inserting new ones.. Exactly-once ingestion in BigQuery is best achieved by making writes idempotent. Using MERGE with a unique business key ensures that a retried batch updates existing rows instead of inserting duplicates. Primary key constraints in BigQuery are not enforced, and Storage Write API exactly-once applies only within a stream, not across job retries.

Answer

Create the target table with a PRIMARY KEY constraint on the transaction ID column and enable enforcement using the ALTER TABLE ... ADD PRIMARY KEY ... NOT ENFORCED statement.

Answer

Enable the BigQuery Storage Write API's exactly-once stream mode by setting the write mode to COMMITTED and providing a stream offset for each row.

Answer

Configure the load job with WRITE_TRUNCATE to replace the entire table on each run so duplicates from previous attempts are removed automatically.

Question 4

You maintain a BigQuery dataset with a table named `transactions` that contains a `customer_id` column and a `purchase_date` DATE column. To reduce query cost and improve performance for dashboards that filter by `customer_id` and `purchase_date`, you want to physically cluster the table. You need to create a new table with the same data but clustered on `customer_id` and `purchase_date`. Which DDL statement should you use?

Accepted Answer

CREATE TABLE `project.dataset.transactions_clustered` CLUSTER BY customer_id, purchase_date AS SELECT * FROM `project.dataset.transactions`. To create a clustered table in BigQuery, you use CREATE TABLE with the CLUSTER BY clause specifying the clustering columns, followed by AS SELECT to populate the table. Clustering physically sorts data by the specified columns, improving filter and aggregation performance. Partitioning is a separate concept and cannot be applied to high-cardinality string columns like `customer_id`. The correct syntax places CLUSTER BY before the AS SELECT.

Answer

CREATE TABLE `project.dataset.transactions_clustered` PARTITION BY customer_id, purchase_date AS SELECT * FROM `project.dataset.transactions`

Answer

CREATE TABLE `project.dataset.transactions_clustered` AS SELECT * FROM `project.dataset.transactions` CLUSTER BY customer_id, purchase_date

Answer

CREATE TABLE `project.dataset.transactions_clustered` CLUSTER BY customer_id AS SELECT * FROM `project.dataset.transactions` CLUSTER BY purchase_date

Question 5

A healthcare analytics company uses BigQuery to store patient data. They need to share a subset of this data with an external research partner in a way that the partner can query the data but cannot modify it, and the company wants to minimize data duplication and storage costs. The partner uses their own Google Cloud project and has their own billing account. Which TWO approaches should the company use to meet these requirements? (Choose two.)

Accepted Answer

Create a separate dataset in the company's project that contains only the allowed subset of data, and grant the partner's project the BigQuery Data Viewer role on that dataset.. An authorized view and a separate dataset with only the allowed subset both provide read-only access to a specific subset of data without granting full dataset access. The authorized view avoids data duplication by querying the original tables, while the separate dataset can be created with views or a one-time copy. Both approaches ensure the partner cannot modify the data and can query it using their own project and billing.

Answer

Grant the partner's project the BigQuery Data Viewer role on the dataset that contains the patient data.

Answer

Export the subset of data to a Cloud Storage bucket and share the bucket with the partner's project, granting them Storage Object Viewer role.

Answer

Use BigQuery row-level security with a policy tag to filter rows based on the partner's identity, and grant the partner the BigQuery Data Viewer role on the dataset.

Question 6

Your team is using Dataflow to build a streaming pipeline that reads from Pub/Sub and writes to BigQuery. You need to perform a stateful transformation that groups events by a session ID and calculates the number of events per session within a 5-minute window. Which Dataflow feature should you use?

Accepted Answer

Use session windows with a gap duration of 5 minutes and apply a GroupByKey followed by a ParDo to count events per session.. Session windows are designed for grouping events by key with gaps, making them perfect for session-based analysis. After windowing, GroupByKey collects all events for each session, and a ParDo can compute the count. This combination accurately calculates events per session within the specified gap duration, unlike fixed windows which would fragment sessions.

Answer

Use a ParDo transform with a side input containing the session ID mapping.

Answer

Use the GroupByKey transform after windowing the PCollection into 5-minute fixed windows.

Answer

Use the Combine.perKey transform with a windowing strategy of 5-minute fixed windows.

Question 7

You are partitioning a BigQuery table by a DATE column `transaction_date`. You need to query data for the last 7 days, but you want to minimize the amount of data scanned. Which WHERE clause is most efficient?

Accepted Answer

WHERE transaction_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY). To minimize data scanned on a partitioned table, the WHERE clause must allow partition pruning. This requires a direct comparison on the partitioning column with a constant or a constant expression. Using functions on the column disables pruning. The condition `transaction_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)` directly references the partitioning column and uses a constant expression, enabling pruning to the last 7 days.

Answer

WHERE DATE(transaction_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

Answer

WHERE EXTRACT(DAYOFYEAR FROM transaction_date) >= EXTRACT(DAYOFYEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))

Answer

WHERE transaction_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()

Question 8

You are preparing data for analysis in BigQuery. You need to create a new table that combines data from two existing tables, 'orders' and 'customers', and you want to optimize the table for queries that filter on order_date and customer_region. You also need to ensure that the table is partitioned and clustered appropriately. Which two actions should you take? (Choose two.)

Accepted Answer

Create the table using a CREATE TABLE statement with PARTITION BY DATE(order_date) and CLUSTER BY customer_region.. To optimize for filters on order_date and customer_region, partition by DATE(order_date) and cluster by customer_region. This enables partition pruning on order_date and cluster pruning on customer_region. Using CREATE TABLE AS SELECT allows you to combine data and apply these settings in one step. Other options either miss partitioning or reverse the optimal order.

Answer

Use a materialized view that joins orders and customers and is partitioned by order_date and clustered by customer_region.

Answer

Partition the table by customer_region and cluster by order_date.

Answer

Create the table without partitioning but with clustering on both order_date and customer_region.

Question 9

You have a BigQuery table `sales.transactions` with a column `amount` of type NUMERIC and a column `transaction_date` of type DATE. You need to create a new table that contains only transactions from the year 2023 and adds a column `amount_usd` that converts `amount` from EUR to USD using a fixed exchange rate of 1.1. Which SQL statement should you use?

Accepted Answer

SELECT *, amount * 1.1 AS amount_usd FROM sales.transactions WHERE EXTRACT(YEAR FROM transaction_date) = 2023. The correct statement uses EXTRACT(YEAR FROM transaction_date) to filter for 2023 and multiplies amount by 1.1 to convert EUR to USD. This is the most efficient and readable approach in BigQuery, and it allows partition pruning if the table is partitioned by transaction_date. The other options either cause errors or are less efficient.

Answer

SELECT *, amount * 1.1 AS amount_usd FROM sales.transactions WHERE DATE_TRUNC(transaction_date, YEAR) = '2023-01-01'

Answer

SELECT *, amount * 1.1 AS amount_usd FROM sales.transactions WHERE FORMAT_DATE('%Y', transaction_date) = '2023'

Answer

SELECT *, amount * 1.1 AS amount_usd FROM sales.transactions WHERE transaction_date = 2023

Question 10

You are designing a BigQuery table to store IoT sensor readings. Each reading includes a `device_id` (STRING), `reading_time` (TIMESTAMP), and `temperature` (FLOAT64). The table will receive millions of rows per day, and most queries will filter by a specific `device_id` and a time range within the last 7 days. You want to optimize query performance and minimize cost. What should you do?

Accepted Answer

Partition the table by `reading_time` and cluster by `device_id`.. Partitioning by `reading_time` enables partition pruning for time-range filters, which is critical for cost and performance. Clustering by `device_id` then optimizes filters on device. This combination is the standard best practice for time-series data in BigQuery. Partitioning by a STRING column is not allowed, and clustering alone does not provide partition-level pruning. A view does not materialize data and thus does not improve scan efficiency.

Answer

Partition the table by `device_id` and cluster by `reading_time`.

Answer

Create a view that filters the last 7 days and cluster the base table by `device_id`.

Answer

Cluster the table by `reading_time` and `device_id` without partitioning.

Question 11

A data analyst needs to load a 500 GB CSV file from a Cloud Storage bucket into a BigQuery table. The file has a header row and uses comma delimiters. The analyst wants the load to complete as quickly as possible and wants to avoid schema inference errors. What should the analyst do?

Accepted Answer

Split the CSV into multiple smaller files and load them in parallel using a single bq load command with a wildcard URI, specifying an explicit schema.. For a large CSV, splitting into multiple files and loading with a wildcard URI enables parallel load jobs, and specifying an explicit schema avoids autodetect errors. The Storage Write API is for streaming, and external tables are for in-place querying, not fast bulk ingestion.

Answer

Load the CSV using the BigQuery Storage Write API in COMMITTED mode to achieve the fastest possible ingestion.

Answer

Use the bq load command with --skip_leading_rows=1 and --autodetect to let BigQuery infer the schema from the data.

Answer

Create an external table over the CSV file in Cloud Storage and query it directly without loading data into BigQuery.

Question 12

A media company streams clickstream events into BigQuery using the Storage Write API. The events are stored in a table `clicks` with a TIMESTAMP column `event_time` and a STRING column `user_id`. The data arrives continuously, and the table is partitioned by day on `event_time`. Analysts need to query the most recent data with low latency, but they also need to run historical analyses over the past year. The data engineer wants to optimize query performance for both use cases while minimizing storage costs. What should the engineer do?

Accepted Answer

Create a clustered table on `user_id` in addition to the existing partitioning on `event_time`.. Clustering the table on `user_id` in addition to partitioning by day on `event_time` enables BigQuery to prune both partitions and blocks, improving performance for queries that filter on user and time. This approach supports both recent low-latency queries and historical analyses without deleting data or adding significant storage overhead. Other options either delete data, add latency, or do not address storage costs.

Answer

Set the table's partition expiration to 30 days so that older partitions are automatically deleted, reducing storage costs.

Answer

Use the BigQuery BI Engine to cache the results of recent queries and accelerate dashboard performance.

Answer

Create a materialized view that aggregates clicks by day and user_id, and query the materialized view for recent data.

Question 13

You are using Cloud Data Fusion to build a pipeline that reads data from a Cloud Storage bucket, performs transformations, and writes to BigQuery. The data contains sensitive information, and you need to mask credit card numbers before writing to BigQuery. The pipeline must be reusable and the masking logic should be applied consistently. Which approach should you use?

Accepted Answer

Use the Data Fusion Masking plugin to obfuscate the credit card numbers, and include it in the pipeline before the BigQuery sink.. The Data Fusion Masking plugin is designed to obfuscate sensitive data such as credit card numbers. Including it in the pipeline before the BigQuery sink ensures consistent masking across all records. This approach is reusable and leverages built-in functionality, avoiding custom development. Other options either rely on incorrect features or are less maintainable.

Answer

Write a custom plugin using the Data Fusion Plugin API to mask credit card numbers, and deploy it to the Data Fusion instance.

Answer

Use the Data Fusion BigQuery sink plugin's built-in masking feature to automatically mask sensitive columns.

Answer

Use the Data Fusion Wrangler to apply a transformation that replaces credit card numbers with a masked pattern, and save the transformation as a directive.

Question 14

You need to load a large CSV file (approximately 2 TB) from Cloud Storage into a BigQuery table. The CSV has a header row and uses comma delimiters. You want to ensure the load job is efficient and minimizes errors. Which approach should you take?

Accepted Answer

Use the bq load command with --source_format=CSV and --skip_leading_rows=1, and specify the schema using --autodetect.. For loading a large CSV file into BigQuery, the bq load command with CSV format and skipping the header row is standard. Autodetect simplifies schema definition and is sufficient for most cases. While providing an explicit schema is valid, it is not required for efficiency or error minimization in this scenario. Other flags like max_bad_records or allow_quoted_newlines are for specific data issues not mentioned here.

Answer

Use the bq load command with --source_format=CSV and --skip_leading_rows=1, and set --allow_quoted_newlines to handle multiline fields.

Answer

Use the bq load command with --source_format=CSV and --skip_leading_rows=1, and explicitly provide a schema file.

Answer

Use the bq load command with --source_format=CSV and --skip_leading_rows=1, and set --max_bad_records=1000 to ignore malformed rows.

Question 15

A logistics company uses BigQuery to analyze shipment data. The main table `shipments` contains a nested and repeated field `events` that records each scan event with a timestamp and location. Analysts frequently need to filter shipments based on the latest event's location. Currently, queries use a correlated subquery with UNNEST to find the latest event, which is slow and expensive. The data engineer wants to improve query performance by precomputing the latest event location for each shipment and storing it in a separate column. Which approach should the engineer use?

Accepted Answer

Create a new table using a CREATE TABLE AS SELECT statement that includes a column computed with a window function or ARRAY expression to extract the latest event location, and replace the original table.. Creating a new table with a precomputed column for the latest event location using CREATE TABLE AS SELECT physically stores the result, so analysts can filter on that column without re-running the expensive UNNEST and subquery. This approach is efficient and supported in BigQuery. Views and materialized views either do not store the result or do not support the required SQL constructs, and UPDATE statements are not suitable for this transformation.

Answer

Use a scheduled query to periodically run an UPDATE statement that sets a new column `latest_location` based on a subquery that finds the latest event.

Answer

Create a view that uses a window function to rank events by timestamp and selects the latest location, and have analysts query the view.

Answer

Create a materialized view that selects the shipment ID and the latest event location using a window function, and query the materialized view.