PDE Storing the Data Practice Test 10 — 15 Questions

Question 1

A data engineer is designing a BigQuery table for a fraud detection system. The table will store 500 TB of transaction records and will be queried constantly by filtering on a transaction timestamp column and a customer_id column. The engineer needs to minimize the amount of data scanned by these queries. What should the engineer do?

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

Accepted Answer

Partition the table by the transaction timestamp column and cluster by the customer_id column.. Partitioning by transaction timestamp and clustering by customer_id is the recommended approach for large BigQuery tables that are frequently filtered on a time column and a high-cardinality key. Partition pruning limits scanned partitions, and clustering sorts data within partitions so that filters on customer_id read fewer blocks, lowering both cost and latency.

Answer

Create a materialized view that pre-aggregates transactions by customer_id and timestamp.

Answer

Use the BigQuery Storage Write API to stream data into the table with a fixed schema.

Answer

Set the table's expiration time to 30 days to limit the amount of historical data stored.

Question 2

A financial services firm stores trade documents in a Cloud Storage bucket. Auditors require that each object be retained for exactly seven years and that no user, including project owners, be able to delete or overwrite the objects during that period. The firm also needs to prove compliance to auditors. Which combination of controls should the data engineer implement?

Accepted Answer

Create a bucket lock on a retention policy of seven years and grant auditors the Storage Object Viewer role.. A locked bucket retention policy enforces object immutability for the specified duration and cannot be removed or shortened, satisfying the requirement that even project owners cannot delete data. Because the lock is a permanent bucket property, auditors can verify it directly. Lifecycle rules, versioning, and IAM conditions are all modifiable controls and therefore cannot guarantee the required immutability.

Answer

Apply IAM conditions that deny storage.objects.delete unless the request comes from an auditor's service account.

Answer

Set an Object Lifecycle Management rule to delete objects after 2555 days and enable uniform bucket-level access.

Answer

Enable Cloud Storage versioning and set a lifecycle rule to keep noncurrent versions for seven years.

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 data engineer needs to choose a storage service for a new application that requires a schemaless document store with automatic multi-region replication, strong consistency for reads, and native mobile SDK support. The application must scale to millions of users without manual sharding. Which Google Cloud service should the engineer select?

Accepted Answer

Cloud Firestore in Native mode. Firestore in Native mode provides a schemaless document model, automatic multi-region replication, strong consistency for reads, and native mobile SDKs, and it scales horizontally without manual sharding. These characteristics match the application's requirements more closely than the relational or wide-column alternatives.

Answer

Cloud Spanner

Answer

Cloud Bigtable

Answer

Cloud SQL for MySQL

Question 5

A retail company stores 500 TB of JSON transaction logs in Cloud Storage. Analysts need to run ad hoc SQL over the data, and the company wants to avoid managing a separate cluster while keeping query cost predictable. The logs are already partitioned into date-based prefixes. What should the data engineer do?

Accepted Answer

Create a BigQuery external table over the Cloud Storage prefixes and use a hive-partitioned layout so queries use partition pruning.. An external table over Cloud Storage gives serverless SQL access to the JSON logs, and hive partitioning on the date prefixes enables pruning so only the relevant date partitions are read. This avoids loading 500 TB, removes cluster management, and keeps cost predictable because bytes billed track the partitions scanned.

Answer

Create a Dataproc cluster with Hive and query the JSON logs through Hive external tables on Cloud Storage.

Answer

Load all 500 TB into a BigQuery native table partitioned by ingestion time and query the native table.

Answer

Use Cloud SQL for PostgreSQL with the foreign data wrapper to read the JSON files from Cloud Storage.

Question 6

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 7

A media company stores final video masters in a Cloud Storage bucket. Regulatory rules require that each object be unalterable for seven years, and the company must be able to prove retention compliance to auditors. Objects are written once and never edited. The data engineer needs the strongest native Cloud Storage control that prevents deletion or overwrite for the required period. What should the engineer configure?

Accepted Answer

A bucket retention policy with a seven-year retention period and a locked retention policy.. A locked bucket retention policy is the native Cloud Storage feature that enforces immutability for a fixed period and cannot be weakened once locked. It directly satisfies the write-once, provable-retention requirement, whereas versioning, signed URLs, and IAM policies only govern recovery or access. For regulatory retention of final masters, the locked retention policy is the correct control.

Answer

Uniform bucket-level access with IAM roles limited to a single compliance group.

Answer

Object Versioning on the bucket plus a lifecycle rule to delete noncurrent versions.

Answer

A signed URL with a seven-year expiration that is shared only with the compliance team.

Question 8

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 9

A data engineer is designing a Cloud Storage layout for a data lake that will be queried by BigQuery external tables and by Dataproc jobs. The engineer wants to minimize query cost and improve scan performance across both engines. Which two practices should the engineer follow? (Choose two.)

Accepted Answer

Partition the data in Cloud Storage using a Hive-style key prefix such as dt=YYYY-MM-DD.. Columnar formats let both engines read only referenced columns and compress data efficiently, and Hive-style partitioning lets them prune entire prefixes when filters match the partition key. Together these reduce bytes scanned and listing overhead, which lowers query cost and improves scan performance for BigQuery external tables and Dataproc alike.

Answer

Enable object versioning on the bucket to preserve historical query results.

Answer

Store each row as a separate object to maximize parallelism during scans.

Answer

Use the Standard storage class for all objects to ensure the lowest possible access latency.

Question 10

A data engineer is designing a BigQuery table for a fraud detection system. The table will store 500 TB of transaction records and will be queried constantly by filtering on a transaction timestamp column and a customer_id column. The engineer needs to minimize the amount of data scanned by these queries. What should the engineer do?

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

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 11

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 12

A data engineer is designing a Cloud Storage bucket for a machine learning training pipeline. The pipeline writes many small Parquet files (about 1 MB each) from a Dataflow job, and a downstream training job reads them sequentially. The engineer wants to minimize the number of storage operations and improve read throughput. The bucket uses Standard storage class and has no lifecycle rules. What should the engineer do?

Accepted Answer

Configure the Dataflow job to write larger Parquet files, for example by using a longer window or by grouping writes into 128 MB to 512 MB files.. The core issue is many small files, which increases the number of Cloud Storage operations and reduces read throughput because each file requires separate open, metadata, and read operations. Writing larger Parquet files, such as 128 MB to 512 MB, reduces object count and allows the training job to read contiguous data more efficiently. This is a common Dataflow and Cloud Storage optimization: adjust the pipeline to produce fewer, larger shards. Other options address durability, cost class, or billing, not the operation-count and throughput problem.

Answer

Change the bucket storage class to Nearline and add a lifecycle rule to transition objects after 30 days.

Answer

Enable Requester Pays on the bucket so that the training job pays for egress and operations.

Answer

Enable Object Versioning on the bucket and set a lifecycle rule to delete noncurrent versions after 7 days.

Question 13

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 14

A company stores sensitive data in Cloud Storage and must ensure that data is encrypted at rest with keys that they control and can rotate on demand. They also need to audit key usage and revoke access immediately if a key is compromised. Which Cloud Storage encryption option should they use?

Accepted Answer

Customer-managed encryption keys (CMEK) with Cloud KMS. Customer-managed encryption keys (CMEK) with Cloud KMS allow the company to control and rotate keys, audit key usage through Cloud KMS audit logs, and revoke access by disabling or destroying the key. This satisfies the requirements for customer-controlled encryption, on-demand rotation, and immediate revocation for sensitive Cloud Storage data.

Answer

Google-managed encryption keys

Answer

Customer-supplied encryption keys (CSEK)

Answer

Client-side encryption before uploading to Cloud Storage

Question 15

A financial analytics team ingests trade records into BigQuery every minute. Queries filter almost exclusively on trade_date and account_id, and the table grows by roughly 400 GB per day. Analysts report that monthly reports scanning the last 30 days are slow and expensive. The team wants to reduce bytes scanned without changing the ingestion pipeline. What should they do?

Accepted Answer

Partition the table by trade_date and cluster it by account_id.. Partitioning by the trade_date column allows BigQuery to skip partitions outside the query's date range, and clustering by account_id further prunes blocks within each retained partition. This combination directly reduces bytes scanned for the described monthly reports, improving both latency and cost while leaving the ingestion pipeline untouched.

Answer

Enable BigQuery BI Engine reservation for the reporting dataset.

Answer

Convert the table to a BigQuery external table over Cloud Storage Parquet files.

Answer

Create a materialized view that pre-aggregates daily totals per account.