PDE Practice Test 20 — 15 Questions

Question 1

A data engineer is building a Dataflow pipeline that reads newline-delimited JSON files from a Cloud Storage bucket and writes them to BigQuery. The files arrive continuously, some with malformed records, and the team wants the pipeline to keep running while routing bad records to a dead-letter location for later analysis. The team also wants the schema to be inferred from the files during development but fixed in production. Which two approaches should the data engineer use? (Choose two.)

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

Accepted Answer

Define an explicit TableSchema in the pipeline for production and use schema autodetect only in a development branch.. Dead-letter routing is implemented with a side output from a DoFn that catches parse failures, keeping the main pipeline alive. Schema stability in production is achieved by declaring an explicit TableSchema rather than letting autodetect run continuously, with autodetect reserved for development. Together these two choices satisfy both the resilience and schema-governance requirements without changing write dispositions or relying on service-level flags.

Answer

Enable the --enableStreamingEngine flag so that schema inference happens automatically at the service level.

Answer

Use BigQueryIO with withSchemaUpdateOptions set to ALLOW_FIELD_ADDITION and rely on runtime inference for every file.

Answer

Configure BigQueryIO with withCreateDisposition set to CREATE_NEVER and withWriteDisposition set to WRITE_TRUNCATE.

Question 2

A media company ingests thousands of small JSON files per hour into a Cloud Storage bucket and wants to analyze them with BigQuery. Analysts frequently filter by event date and by device type, and they want to minimize query cost. The team wants a managed approach that avoids writing custom transformation code. Which BigQuery feature should the engineer use?

Accepted Answer

Use a BigQuery load job to create a native table partitioned by event date and clustered by device type.. Loading the JSON into a native, partitioned, and clustered table moves the data into BigQuery managed columnar storage, where filters on event date prune partitions and filters on device type benefit from clustering. This reduces bytes scanned and therefore cost, and it requires no custom transformation code. External and federated approaches keep querying the raw small files, which is less efficient and more expensive for repeated analysis.

Answer

Create an external table over the bucket and rely on automatic schema detection.

Answer

Create a federated query that reads the bucket through the Cloud Storage connector.

Answer

Define a BigLake external table with a metadata cache and partition pruning enabled.

Question 3

A logistics company ingests 5 TB of shipment telemetry per day into a BigQuery table named events_raw. Analysts run exploratory queries that scan the full table, which is now 900 TB, and costs are rising quickly. Most queries filter on the event_date column, which is a DATE field, and on the device_id column, but the table is not partitioned or clustered. The company wants to reduce bytes scanned without changing the query patterns or losing any historical events. Which combination of BigQuery table settings should the data engineer implement?

Accepted Answer

Partition events_raw by event_date and cluster by device_id.. Partitioning by a DATE column enables partition pruning whenever queries filter on that column, and clustering on a high-cardinality filter column such as device_id reduces the blocks scanned inside each partition. Because the requirement is to keep all events and not rewrite queries, this physical layout change is the right fit. Aggregation views and expiration policies do not address the full-scan cost while preserving raw history.

Answer

Set a table expiration of 30 days on events_raw and rely on time travel for older data.

Answer

Cluster events_raw by event_date and device_id without partitioning.

Answer

Create a materialized view that aggregates events_raw by event_date and device_id.

Question 4

A company stores JSON-formatted application logs in Cloud Storage. They need to query these logs with SQL, but they want to avoid the cost and latency of loading them into BigQuery. The logs have a consistent schema, and queries will filter on a timestamp field and a few nested fields. Which approach should they use?

Accepted Answer

Create an external table in BigQuery over the Cloud Storage JSON files and enable hive partitioning.. BigQuery external tables allow querying JSON files in Cloud Storage without loading, and hive partitioning enables partition pruning on the timestamp field, reducing scanned data and cost. This meets the requirement to avoid load costs and latency while supporting SQL queries. Loading into a native table, federated queries, or Dataproc all introduce extra cost or complexity and do not match the serverless, direct-query need.

Answer

Create a Dataproc cluster and run Spark SQL queries against the JSON files in Cloud Storage.

Answer

Load the JSON files into a BigQuery native table using schema autodetect and then delete the files.

Answer

Use BigQuery federated queries with a Cloud SQL connection to query the JSON files.

Question 5

A company uses BigQuery for analytics. They have a table that is queried frequently by date range. To reduce costs, they want to ensure queries only scan the relevant partitions. They also want to improve performance for queries filtering on a specific customer_id. Which table design should they use?

Accepted Answer

Partition by date column and cluster by customer_id. To reduce costs by ensuring queries only scan relevant partitions, you should partition by the date column (so date-range filters prune partitions). To improve performance for queries filtering on customer_id, you should cluster by customer_id (so BigQuery can skip blocks within partitions). Therefore, partition by date column and cluster by customer_id is the correct design.

Answer

Partition by ingestion time and cluster by customer_id

Answer

Use a materialized view that filters by date and customer_id

Answer

Cluster by date column and partition by customer_id

Question 6

A data engineer is designing a BigQuery table that will store billions of rows of application logs. Queries will almost always filter on a log_date column and then join on a user_id column, and the team wants to minimize both storage cost and bytes scanned. The table is append-only and grows by about 50 GB per day. Which two design choices should the engineer make? (Choose two.)

Accepted Answer

Cluster the table by user_id.. Partitioning by log_date lets date-filtered queries prune partitions, and clustering by user_id reduces the blocks read for user_id filters and joins. Together they shrink bytes scanned and lower query cost for the dominant access pattern. Streaming inserts, table expiration, and materialized views do not address the general filtering and join pattern and can add cost or risk data loss.

Answer

Set the table's expiration to 30 days to reduce storage cost.

Answer

Enable streaming inserts for the log ingestion pipeline.

Answer

Create a materialized view that aggregates logs by user_id.

Question 7

Your team stores raw JSON event logs in Cloud Storage. Analysts need to run ad-hoc SQL queries on this data with minimal setup and cost, but they also require the ability to join it with existing BigQuery tables. Which approach should you recommend?

Accepted Answer

Create an external table in BigQuery that points to the Cloud Storage JSON files, and query it using standard SQL.. Querying external data in Cloud Storage via BigQuery external tables provides a serverless, cost-effective way to run ad-hoc SQL without loading data. It supports joins with native BigQuery tables, fulfilling the requirement. Other options involve data movement or unsuitable tools, adding cost and complexity.

Answer

Load the JSON files into a new BigQuery table using the bq load command with --autodetect, then query the table directly.

Answer

Use Cloud Dataflow to read the JSON files, transform them, and write the results to BigQuery for querying.

Answer

Import the JSON files into Google Sheets and use the BigQuery data connector to join with BigQuery tables.

Question 8

A healthcare company stores patient records in a Cloud Storage bucket that must remain in a specific region for data residency. The security team requires that all data be encrypted with keys the company controls and can rotate, and that access to the keys be auditable. The company also wants to avoid managing key material on-premises. Which approach should the data engineer choose?

Accepted Answer

Use Customer-Managed Encryption Keys in Cloud KMS with a key ring in the required region.. Customer-Managed Encryption Keys in Cloud KMS give the company ownership and rotation control while Google operates the key infrastructure, and a regional key ring aligns key storage with the data residency requirement. Cloud KMS also writes key usage to audit logs. Default encryption, customer-supplied keys, and external key managers either remove control or push key management outside Google Cloud.

Answer

Use Customer-Supplied Encryption Keys and store the key material in a local secrets file.

Answer

Use Google-managed encryption keys and rely on Google's default encryption for the bucket.

Answer

Use Cloud External Key Manager to connect to a third-party external key management system.

Question 9

Your company ingests streaming data into BigQuery using the Storage Write API. You need to ensure that duplicate records are not inserted when the streaming job retries due to transient errors. Which feature should you use?

Accepted Answer

Use the Storage Write API's exactly-once semantics by setting a stream name and offset.. The Storage Write API's exactly-once semantics, enabled by specifying a stream name and offsets, guarantee that records are not duplicated even if the writer retries. This is the only option that provides native deduplication for streaming inserts. Other methods either do not apply to the Storage Write API or are not supported in BigQuery.

Answer

Create a unique constraint on the destination table to reject duplicate rows.

Answer

Enable BigQuery's streaming inserts with insertId to deduplicate records.

Answer

Use a MERGE statement to upsert records after each streaming batch.

Question 10

A retail analytics team has a 400 TB BigQuery table partitioned by DATE on a transaction_date column. Analysts almost always filter by a single store_id and a date range, and each store has roughly 12 years of history. Queries currently scan the entire partition for the requested dates. The team wants to reduce bytes billed without changing the table name or rewriting the ingestion pipeline. What should they do?

Accepted Answer

Add a clustering specification on store_id to the existing partitioned table.. Clustering a partitioned table on the frequently filtered column lets BigQuery prune blocks within each partition, so a query that filters on both date and store reads far less data than a full partition scan. The optimization applies transparently to the same table name, so ingestion and existing SQL keep working, and bytes billed drop in proportion to how well the clustering column discriminates rows inside each partition.

Answer

Create a materialized view that pre-aggregates transactions by store_id and date, and point analysts at the view.

Answer

Enable partition expiration set to 30 days so older partitions are dropped automatically.

Answer

Convert the table to use the US multi-region instead of a single region to increase scan parallelism.

Question 11

A media analytics company ingests 8 TB of new JSON event logs into BigQuery every day and keeps all history for 5 years. Analysts almost never filter on the raw event payload column and only occasionally select it, but they frequently filter on event_date and user_id. Storage cost is the top concern, and query performance on the frequently filtered columns must stay fast. What should the data engineer do?

Accepted Answer

Store the raw event payload in a Cloud Storage bucket and keep only the structured, frequently queried columns in the BigQuery table, referencing the object path.. The workload filters on structured columns but almost never touches the large payload, so the payload is dead weight in the columnar store. Keeping only the structured columns in BigQuery preserves fast partitioned and clustered filtering, while relocating the payload to Cloud Storage removes the dominant storage and scan cost. Partitioning, clustering, the JSON type, and long-term storage all leave that large column inside the table, so none of them eliminate the core expense.

Answer

Store the raw event payload in a BigQuery column of type JSON so it is parsed at query time and billed as a separate data type.

Answer

Partition the table by event_date and cluster it by user_id, then rely on the default columnar storage to avoid scanning the payload.

Answer

Enable the BigQuery long-term storage pricing tier so data older than 90 days is billed at a lower rate automatically.

Question 12

You need to load a 500 GB CSV file from Cloud Storage into BigQuery. The file has a header row and uses comma delimiters. You want to load it as quickly as possible without transforming the data. Which approach should you use?

Accepted Answer

Use the `bq load` command with `--source_format=CSV` and `--skip_leading_rows=1`.. A direct load job using `bq load` with CSV format and skipping the header is the fastest and most straightforward way to load a large CSV into BigQuery without transformation. It uses BigQuery's native loading capabilities, which are optimized for bulk data.

Answer

Create an external table pointing to the CSV and then run `CREATE TABLE AS SELECT * FROM external_table`.

Answer

Use `bq query` with an `EXTERNAL_QUERY` function to read the CSV from Cloud Storage.

Answer

Use the `gcloud storage cp` command to copy the CSV into BigQuery.

Question 13

A healthcare company stores patient imaging metadata in Cloud Firestore in Native mode. The application must run a query that returns all documents where the status field equals 'pending' and the region field equals 'us-east', ordered by created_at descending, and it must paginate through potentially millions of matching documents. Which Firestore capability should the engineer use to satisfy this requirement efficiently?

Accepted Answer

A composite index on status, region, and created_at, combined with cursor-based pagination.. Firestore automatically indexes single fields but requires a composite index when a query combines multiple equality filters with an orderBy on another field. Cursor-based pagination via startAfter scales to large result sets because it resumes from the last document rather than skipping offsets, making it the efficient, correct approach for this query pattern.

Answer

A collection group query across all subcollections with an aggregation count.

Answer

A single-field index on created_at with offset-based pagination using the skip parameter.

Answer

A distributed counter shard to track pending documents per region.

Question 14

A retail analytics team uses BigQuery to store point-of-sale transactions in a partitioned table. They need to optimize query performance for dashboards that filter on store_id and product_category, and they want to minimize the amount of data scanned. The table is partitioned by transaction_date and has a clustered column of store_id. Queries frequently filter on product_category as well. Which change should the data engineer make to improve performance?

Accepted Answer

Add product_category as a second clustering column after store_id. BigQuery clustering sorts data within partitions by the specified columns, and queries that filter on those columns can skip blocks. Adding product_category as a second clustering column after store_id lets the engine prune more effectively when both columns appear in filters. The partitioning column remains transaction_date, which is ideal for date-range dashboards. This change reduces bytes scanned and improves latency without altering the partitioning scheme.

Answer

Create a materialized view that pre-aggregates sales by store_id and product_category

Answer

Change the partitioning column to product_category and keep store_id as the clustered column

Answer

Convert the table to use time-unit partitioning on transaction_date with a daily granularity and remove clustering

Question 15

A data engineer needs to store JSON documents in a Google Cloud database that must scale horizontally, support automatic multi-region replication, and provide strong consistency for reads. The application performs many small reads and writes keyed by a document ID, and the team wants to avoid managing servers. They also need the ability to run SQL-like queries on the documents. Which Google Cloud service should the engineer choose?

Accepted Answer

Firestore in Native mode with multi-region location.. Firestore in Native mode is the Google Cloud document database that provides automatic multi-region replication, strong consistency, horizontal scaling, and a SQL-like query API. It is serverless, so the team does not manage servers. The other options either lack strong consistency across regions, are relational rather than document-oriented, or are designed for different workloads. Firestore's document ID key model and small read/write performance align with the application's access pattern.

Answer

Cloud SQL for PostgreSQL with a read replica in each region.

Answer

Cloud Spanner with a multi-region configuration.

Answer

Bigtable with a single cluster in one region and a replication policy to a second region.