PDE Storing the Data Practice Test 9 — 15 Questions

Question 1

A company wants to use BigQuery for analytics. They need to meet compliance requirements by encrypting data at rest with a key they control. Which TWO actions should they take? (Choose 2.)

Accepted Answer

Set the Cloud KMS key as the default encryption key for the BigQuery dataset.. Option D is correct because customer-managed encryption keys (CMEK) in BigQuery require you to first create a Cloud KMS key ring and a cryptographic key (with appropriate rotation and IAM permissions granted to the BigQuery service account) before it can be used. Option A is correct because, once the KMS key exists, you set it as the default encryption key on the BigQuery dataset so that all tables created in that dataset are encrypted at rest with the customer-controlled key. Option B is incorrect because a Cloud Storage bucket is not required to apply CMEK to BigQuery datasets and does not by itself satisfy the BigQuery encryption requirement. Option C is incorrect because VPC Service Controls provide network perimeter controls against data exfiltration, not encryption of data at rest. Option E is incorrect because AEAD functions provide column-level encryption of specific values within queries, not dataset-level encryption at rest with a customer-managed KMS key.

Answer

Create a Cloud Storage bucket and load data there.

Answer

Use VPC Service Controls to restrict access to the dataset.

Answer

Enable BigQuery column-level encryption using AEAD functions.

Question 2

A company has a data lake on Cloud Storage with raw data in the 'raw' bucket, curated data in 'curated', and processed data in 'processed'. They want to implement lifecycle management to reduce costs. Which TWO actions should they take? (Choose 2)

Accepted Answer

Set a lifecycle rule to change storage class from Standard to Nearline after 30 days for the 'raw' bucket.. Option A is correct because a lifecycle rule that transitions objects in the 'raw' bucket from Standard to Nearline after 30 days is a valid cost-reduction strategy for raw data that is accessed infrequently but may still be needed; Nearline is designed for data accessed less than once a month. Option D is correct because setting a lifecycle rule to delete objects older than 365 days in the 'curated' and 'processed' buckets removes stale data that is no longer needed, directly reducing storage costs. Option B is incorrect because enabling object versioning does not automatically delete older versions; versioning preserves them and typically increases storage costs unless combined with a separate lifecycle deletion rule. Option C is incorrect because partition expiration applies to BigQuery table partitions, not to objects in a Cloud Storage bucket, and the scenario is about Cloud Storage lifecycle management. Option E is incorrect because transitioning raw data to Archive after only 30 days is overly aggressive and would incur early-deletion charges and retrieval costs if the data is still needed, making it a poor fit compared with Nearline.

Answer

Enable object versioning on all buckets to automatically delete older versions.

Answer

Set a partition expiration on BigQuery tables that reference data in the 'processed' bucket.

Answer

Set a lifecycle rule to change storage class from Standard to Archive after 30 days for the 'raw' bucket.

Question 3

A company uses BigQuery for analytics on petabyte-scale data. They want to improve query performance by denormalizing schemas and reducing joins. Which TWO BigQuery features should they use? (Choose 2)

Accepted Answer

Clustering on frequently filtered columns. Option E is correct because BigQuery's native support for nested and repeated fields (ARRAY>) lets you store parent-child relationships inside a single table, which is the standard way to denormalize schemas and eliminate joins while preserving relational structure. Option A is correct because clustering on frequently filtered columns physically co-locates related rows within partitions, so BigQuery scans far less data and returns results faster for filtered queries on the denormalized table. Option B is not a BigQuery feature for denormalization; subqueries still execute as separate query blocks and do not remove join costs. Option C is wrong because external tables read data from Cloud Storage and generally offer worse performance than native tables, not better denormalized performance. Option D is wrong because partitioning by date improves pruning on time filters but does not denormalize schemas or reduce joins.

Answer

Using subqueries instead of JOINs

Answer

External tables reading from Cloud Storage

Answer

Table partitioning by date

Question 4

A company is designing a Cloud Bigtable row key for a time-series dataset of device readings. They want to avoid hotspotting (uneven load across tablets). Which TWO row key design patterns are effective? (Choose 2)

Accepted Answer

Reverse the timestamp string. Option C is correct because reversing the timestamp string (e.g., turning a lexicographically increasing timestamp into a decreasing one) prevents new writes from always landing on the last tablet, spreading sequential writes across the keyspace. Option D is correct because prepending a hash of the device ID to the timestamp distributes writes across many distinct key prefixes, so consecutive readings from different devices do not concentrate on a single tablet. Options A and B are incorrect because a monotonically increasing counter and a timestamp as the leading key component both produce sequential, ever-increasing keys that funnel all new writes to the last tablet, causing hotspotting. Option E is incorrect because a secondary index on the timestamp column is not a row key design pattern and Bigtable does not support secondary indexes natively; it would not address hotspotting.

Answer

Use a monotonically increasing counter as row key

Answer

Use timestamp directly as the first part of the row key

Answer

Use a secondary index on the timestamp column

Question 5

A company wants to implement a data lake on Google Cloud. They need to store raw, structured data in open formats and allow querying directly from BigQuery without loading. Which TWO services or features should they use? (Choose 2)

Accepted Answer

Cloud Storage (GCS). Cloud Storage (GCS) [CORRECT] is the right choice because a data lake on Google Cloud stores raw, structured data as objects in open formats (e.g., Parquet, Avro, ORC, JSON) in buckets, providing durable, scalable, and cost-effective storage. BigLake [CORRECT] is also correct because it is a storage engine that lets BigQuery query data directly in Cloud Storage (and other sources) without loading it, using external tables with fine-grained access control and metadata caching. Together, GCS holds the raw files and BigLake exposes them to BigQuery for direct querying, which matches the requirement exactly. Dataproc (B) is a managed Spark/Hadoop service for processing, not the storage or direct-query layer, and Dataflow (C) is a managed Apache Beam pipeline service for stream/batch processing, so neither fulfills the storage-plus-query requirement. Cloud SQL (D) is a managed relational database for transactional workloads, not a data lake storage layer, and it does not provide the open-format object storage or BigQuery external querying described.

Answer

Dataproc

Answer

Dataflow

Answer

Cloud SQL

Question 6

A data engineer is designing a BigQuery table to store e-commerce order events. Each row contains an order_id (STRING, high cardinality), customer_id (STRING, high cardinality), order_timestamp (TIMESTAMP), and order_amount (NUMERIC). Queries frequently filter on order_id for lookups and also scan by order_timestamp ranges for daily reporting. The engineer wants to minimize bytes scanned by both query patterns. What should the engineer do?

Accepted Answer

Partition the table by order_timestamp and cluster by order_id.. Partitioning by order_timestamp restricts daily range scans to the relevant date partitions, while clustering by order_id sorts rows within each partition so that point lookups read only matching blocks. This design targets both query patterns and minimizes bytes scanned without adding a separate pipeline or storage layer.

Answer

Partition the table by order_id and cluster by order_timestamp.

Answer

Create a materialized view that pre-aggregates daily order totals and query the view instead.

Answer

Enable BigQuery BI Engine on the table and rely on in-memory caching for all queries.

Question 7

A data engineer manages a Cloud SQL for MySQL instance that stores order records. Compliance requires that the data be recoverable to any point in time within the last 30 days, and the team wants the smallest possible recovery window. They also want to avoid managing their own backup rotation scripts. Which configuration should the engineer implement?

Accepted Answer

Enable point-in-time recovery (PITR) with a transaction log retention of 30 days alongside automated backups.. Point-in-time recovery in Cloud SQL combines the latest automated backup with continuously archived binary logs, enabling restoration to a chosen timestamp. Extending transaction log retention to 30 days matches the compliance window while keeping the process fully managed. Daily backups alone or nightly dumps cannot achieve arbitrary-timestamp recovery, and read replicas address availability rather than historical restore.

Answer

Configure a read replica in a second zone and promote it if the primary fails.

Answer

Enable automated backups with a 30-day retention period and rely on the daily backup only.

Answer

Export nightly mysqldump files to a Cloud Storage bucket with a 30-day lifecycle rule.

Question 8

A retail analytics team stores transaction data in a BigQuery table partitioned by day on a DATE column named transaction_date. Analysts repeatedly run queries that filter on transaction_date and also on a high-cardinality STRING column named store_id. These queries scan far more data than expected, and slot usage is high. The team wants to reduce bytes processed without changing query results. What should they do?

Accepted Answer

Create a clustered table on store_id, so BigQuery can prune blocks within each transaction_date partition.. Clustering a partitioned table on the frequently filtered high-cardinality column lets BigQuery prune storage blocks within each partition. Partition pruning on transaction_date still happens, and cluster pruning further limits scanned data. This directly reduces bytes processed and slot consumption for the described queries without altering results, making it the appropriate storage optimization.

Answer

Create a materialized view that precomputes all transactions grouped by store_id and transaction_date.

Answer

Export the table to Cloud Storage as Avro and query it with an external table that applies store_id predicates.

Answer

Change the table to require a partition filter, forcing analysts to always specify transaction_date.

Question 9

A data engineer manages a BigQuery dataset holding a 40 TB partitioned table of point-of-sale transactions. Analysts frequently run queries that filter on a region column and a sale_date column together, but each query scans the full table because the region predicate is not reducing bytes billed. The engineer wants to reduce bytes scanned without changing the analytical queries or the write pipeline. What should the engineer do?

Accepted Answer

Create a clustered table on the region column, because clustering co-locates rows with similar values inside each partition so block pruning can skip irrelevant data.. Clustering is the right lever because it physically sorts data within each partition by the region column, enabling block pruning when region predicates are present. Partitioning already handles sale_date, so adding clustering on the other frequently filtered column reduces scanned bytes while leaving the queries and ingestion pipeline untouched.

Answer

Define a materialized view that aggregates the table by region and sale_date and let the optimizer rewrite all analyst queries to use it.

Answer

Enable the require_partition_filter option on the table so that every query must include a sale_date predicate.

Answer

Convert the table to an external table over Cloud Storage so that only the matching Parquet row groups are read at query time.

Question 10

A data engineer is designing a BigQuery table to store customer order records. Queries frequently filter by order_date and customer_id, and the dataset grows by about 5 TB per day. The engineer wants to minimize query cost and improve performance for these filtered queries. What should the engineer do?

Accepted Answer

Partition the table by order_date and cluster by customer_id.. Partitioning by order_date enables partition pruning, so queries with date filters scan only relevant partitions, cutting cost and improving speed. Clustering by customer_id sorts data within partitions, making customer-filtered queries more efficient. Together they match the described query patterns and scale well with daily 5 TB loads, unlike materialized views, wildcard tables, or BI Engine, which do not address base-table scan efficiency.

Answer

Create a materialized view that pre-aggregates orders by customer_id and order_date.

Answer

Enable BigQuery BI Engine and reserve memory for the dataset.

Answer

Use a wildcard table over daily sharded tables named orders_YYYYMMDD.

Question 11

A data engineer must store JSON documents for a product catalog in Cloud Firestore. Some documents contain nested arrays of product variants that can exceed 1 MiB when serialized. The engineer wants to keep the catalog queryable with strong consistency and low latency. What should the engineer do?

Accepted Answer

Store each variant as a separate document in a subcollection and keep only summary fields in the parent document.. Firestore imposes a hard 1 MiB limit per document, so large nested arrays must be split across documents. Moving variants into a subcollection keeps each document within the limit while preserving strong consistency and low-latency queries, since subcollection documents are first-class documents in the same database.

Answer

Move the entire catalog to Cloud Bigtable and use row keys to model variant relationships.

Answer

Store the variants as a base64-encoded string field inside the parent document.

Answer

Increase the Firestore document size limit by enabling the Enterprise edition of Firestore.

Question 12

A financial services company must retain trade records for seven years in Cloud Storage. Regulators require that no object can be deleted or overwritten before its retention period expires, even by project owners, and that the policy cannot be removed. The company also needs to prove compliance during audits. Which combination of controls should they implement?

Accepted Answer

Set a bucket retention policy and lock it, then upload objects with a retention period covering seven years.. A locked bucket retention policy enforces WORM semantics: objects cannot be deleted or overwritten until their retention period elapses, and the lock prevents removal or reduction of the policy. Uploading records with a seven-year retention period satisfies the regulatory timeline and creates auditable proof. Other controls either can be changed, do not block deletion, or address confidentiality rather than immutability.

Answer

Enable Object Versioning and apply an Organization Policy that denies storage.objects.delete for all principals.

Answer

Configure a lifecycle rule that transitions objects to Archive storage after 30 days and deletes them after seven years.

Answer

Use Customer-Managed Encryption Keys in Cloud KMS and revoke key access after each upload.

Question 13

A financial services company stores transaction logs in Cloud Storage. Regulatory requirements mandate that each object be retained for exactly seven years and that no user, including project owners, can delete or modify the objects during that period. The company wants to enforce this with minimal administrative overhead. What should they do?

Accepted Answer

Create a bucket with a retention policy of seven years and lock the policy.. A locked retention policy on a Cloud Storage bucket enforces a minimum retention period and prevents deletion or modification of objects, even by project owners. Locking the policy makes it permanent, satisfying the seven-year WORM requirement. Lifecycle rules, IAM restrictions, or a nonexistent 'bucket policy' cannot provide the same immutable guarantee, making the locked retention policy the correct and minimal-overhead solution.

Answer

Enable Uniform Bucket-Level Access and grant only the Storage Object Viewer role to all users.

Answer

Configure a Cloud Storage bucket lock using a bucket policy with a seven-year retention period.

Answer

Use Object Lifecycle Management to transition objects to Archive storage after 30 days and delete after seven years.

Question 14

A media company stores 900 TB of video master files in a Cloud Storage bucket in the US multi-region. Legal requires that the objects be retained for seven years and cannot be deleted or overwritten by any user, including project owners, during that period. The company also wants to minimize storage cost for objects that are rarely accessed after the first 90 days. The data engineer must implement a compliant configuration. (Choose two.)

Accepted Answer

Add an Object Lifecycle Management rule that transitions objects to Coldline storage 90 days after creation.. A locked retention policy is the only Cloud Storage mechanism that prevents even project owners from deleting or overwriting objects, providing the required WORM guarantee for seven years. Pairing it with a lifecycle transition to Coldline after 90 days meets the cost objective because Coldline targets infrequently accessed data without conflicting with retention.

Answer

Set the bucket's default storage class to Standard and rely on the multi-region location for durability.

Answer

Enable Uniform bucket-level access and grant the storage.objectViewer role to analysts.

Answer

Add a lifecycle rule that deletes objects 90 days after creation to control cost.

Question 15

A data engineering team needs to run SQL analytics on a large BigQuery dataset from a Looker Studio dashboard. The dashboard must return results quickly, and the underlying data changes only once per day through a batch load. The team wants to minimize query cost and latency for repeated dashboard queries. What should they do?

Accepted Answer

Create a materialized view that aggregates the dataset and configure it to refresh on a daily schedule.. Materialized views precompute and store query results, and with a daily refresh schedule they match the batch update cadence. Repeated dashboard queries hit the stored results, cutting bytes scanned and latency. This directly targets the cost and performance goals, unlike caching layers or partitioning that do not precompute aggregations.

Answer

Enable the BigQuery BI Engine reservation and rely on it to cache all dashboard queries automatically.

Answer

Export the dataset to Cloud Storage as CSV and have Looker Studio query the files directly.

Answer

Partition the table by ingestion date and require all dashboard queries to filter on that column.