PDE Storing the Data Practice Test 4 — 15 Questions

Question 1

A financial services firm stores trade records in a BigQuery table. Compliance requires that individual rows containing a customer's account number be permanently unreadable on request, while aggregate analytics over the rest of the table must continue to work and the table must remain queryable by the analytics team. Which approach meets the requirement?

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

Accepted Answer

Use CryptoDestroy with customer-managed encryption keys, deleting the key for the account number to render it unrecoverable.. Cryptographic erasure satisfies deletion-on-request because destroying the customer-managed key makes the protected ciphertext mathematically unrecoverable while the rest of the row and table remain intact for analytics. Access-control and masking techniques only limit who can see values, and they can be reversed, whereas key destruction is permanent and auditable, which is what regulators expect when a customer exercises a right to erasure.

Answer

Use DLP to detect and mask the account number column at query time with a masking rule.

Answer

Apply column-level security with a policy tag on the account number column and grant the analytics team the fine-grained reader role.

Answer

Run a DELETE statement that removes rows matching the customer account number and rely on time travel to audit the change.

Question 2

A data engineer is designing a BigQuery table that will store user events. The table must support efficient queries that filter by event_date and user_id, and the engineer wants to minimize storage costs while maintaining high query performance. The table will be appended continuously and data older than 90 days is rarely queried. What should the engineer do?

Accepted Answer

Create a partitioned table on event_date, cluster by user_id, and use long-term storage pricing for older partitions.. Partitioning by event_date and clustering by user_id enables efficient filtering and reduces scanned data. BigQuery automatically applies long-term storage pricing to partitions that have not been modified for 90 days, lowering storage costs for older, rarely queried data without deleting it, which satisfies both the cost and performance goals.

Answer

Load the data into a single non-partitioned table and create a materialized view for frequent queries.

Answer

Create a partitioned table on event_date, cluster by user_id, and set a partition expiration of 90 days.

Answer

Create a sharded table for each month and use wildcard queries to access the data.

Question 3

A retail company wants to stream point-of-sale events into BigQuery for near real-time dashboards. The events arrive out of order and may contain duplicate transaction IDs because of retries from the publisher. The team needs the table to reflect only the latest state per transaction and wants to avoid manual deduplication in every query. Which two approaches should the data engineer use? (Choose two.)

Accepted Answer

Use the BigQuery Storage Write API with exactly-once semantics to insert rows.. Exactly-once ingestion via the Storage Write API prevents duplicates caused by publisher retries, and a materialized view that computes the latest row per transaction ID provides a continuously refreshed, deduplicated view for dashboards. Together they handle out-of-order and duplicate events without requiring every query to deduplicate manually.

Answer

Partition the table by transaction ID and cluster by event timestamp.

Answer

Enable BigQuery change data capture by setting a max_staleness value on the table.

Answer

Use a MERGE statement in a scheduled query to upsert the latest event per transaction.

Question 4

A startup ingests application logs into a BigQuery table with a streaming insert pipeline. The logs are written continuously and are only queried for the most recent 30 days; anything older is retained for 90 days for occasional forensic searches. The team wants the lowest storage cost while keeping all 90 days queryable. What should they do?

Accepted Answer

Configure the table so partitions older than 30 days are stored using the long-term storage pricing automatically by BigQuery.. BigQuery automatically lowers the storage price for data that has not been modified for 90 consecutive days, applying long-term storage rates without any action from the team. Because the table is partitioned by date, older partitions stop receiving writes and become eligible for that discount while remaining fully queryable, which satisfies both the 90-day forensic requirement and the lowest-cost goal without deleting or exporting anything.

Answer

Set the table's partition expiration to 30 days so older partitions are deleted automatically.

Answer

Enable streaming inserts with a 30-day retention window on the table's time-partitioning field.

Answer

Export partitions older than 30 days to Cloud Storage in Avro format and delete them from BigQuery.

Question 5

A data engineer is loading data into BigQuery from a Cloud Storage bucket that contains CSV files. The files have a header row, use comma delimiters, and include a column named `event_time` in the format `YYYY-MM-DD HH:MM:SS`. The engineer wants to load the data into a partitioned table using `event_time` as the partition column and also wants to ensure that invalid rows are captured for later analysis instead of failing the entire load. The load must be as efficient as possible. What should the engineer do?

Accepted Answer

Load the data into a staging table without partitioning, then use a query to insert valid rows into a partitioned table and write invalid rows to a separate table using a WHERE clause.. The most efficient approach is to load the CSV files into a staging table without partitioning, then use a SQL query to insert valid rows into a partitioned table and route invalid rows to a separate table. This captures invalid rows for analysis and avoids failing the entire load. It leverages BigQuery's processing power for validation and transformation. The partitioned table can be defined with event_time as the partition column, and the query can use PARSE_TIMESTAMP to validate the format. This approach is simpler and more efficient than a Dataflow pipeline for a batch load.

Answer

Create a partitioned table with event_time as the partition column, use a load job with a schema that defines event_time as TIMESTAMP, and set the maximum bad records to 0.

Answer

Use a Dataflow job to read the CSV files, validate each row, write valid rows to a partitioned BigQuery table, and write invalid rows to a dead-letter Cloud Storage bucket.

Answer

Use a load job with schema autodetect, set the partition column to event_time, and set the maximum bad records to a positive number.

Question 6

A media company stores video metadata and user watch events in BigQuery. Compliance requires that raw watch events older than 90 days be automatically deleted, while aggregated daily view counts must be retained for seven years. The team wants to enforce these rules declaratively without custom deletion scripts. Which two actions should they take? (Choose two.)

Accepted Answer

Set a partition expiration on the raw watch events table to 90 days.. Partition expiration on the raw events table declaratively deletes partitions older than 90 days, while leaving the aggregate table without expiration preserves it for the seven-year requirement. Together these storage-level settings enforce both retention rules without custom deletion jobs or scripts.

Answer

Enable BigQuery time travel on the raw events table to remove expired rows.

Answer

Set a default table expiration on the aggregated daily view counts dataset to seven years.

Answer

Create a scheduled query that deletes raw events older than 90 days each night.

Question 7

A financial analytics team stores trade records in a BigQuery table partitioned by trade_date (DATE) and clustered by trader_id. A compliance officer frequently runs a query filtering on trader_id and trade_date but does not include a partition filter. The query scans the entire table and is expensive. The team wants to reduce cost while preserving the ability to query arbitrary traders. What should they do?

Accepted Answer

Keep the existing partitioning and clustering, and have the compliance officer add a trade_date filter to the query so partition pruning and block pruning both apply.. Partition pruning requires a filter on the partitioning column, and clustering then prunes blocks inside the surviving partitions. Because the table already uses trade_date partitioning and trader_id clustering, the only missing element is the partition predicate. Supplying that predicate enables both layers of pruning, cutting scanned bytes and cost while keeping arbitrary trader lookups possible. Schema changes or new objects do not fix a query that omits the partition column.

Answer

Add a require_partition_filter=true table option and instruct the compliance officer to always supply a trade_date filter.

Answer

Recreate the table with partitioning on trade_date and clustering on trader_id, and ensure the compliance query includes a trade_date predicate so clustering can prune blocks.

Answer

Create a materialized view that pre-aggregates trades by trader_id and trade_date, then have the compliance officer query the view.

Question 8

A logistics company runs an operational application on Cloud SQL for MySQL that must remain highly available within a single region. The team also wants to offload read-only reporting queries from the primary instance without changing the application's write path. They want a managed Google Cloud option that keeps the reporting replica in the same region and requires minimal operational effort. What should they do?

Accepted Answer

Configure a high-availability Cloud SQL instance with a regional persistent disk and a read replica in the same region.. Cloud SQL high availability with a regional persistent disk gives automatic failover within the region, and a same-region read replica offloads reporting reads from the primary. Both are managed features, so the design satisfies availability, read offloading, and minimal operational effort simultaneously.

Answer

Create a Cloud SQL cross-region read replica and point reporting queries at it.

Answer

Migrate the database to Cloud Spanner and use read-only replicas for reporting.

Answer

Enable Cloud SQL high availability only and direct reporting queries to the standby instance.

Question 9

A media company stores 400 TB of video files in a Cloud Storage bucket. Editors in the same region access files daily, while archived projects are read a few times per year. The company wants to minimize storage cost without changing the object names or application code, and without retrieval fees for the daily-access files. Which Cloud Storage configuration should they use?

Accepted Answer

Set the bucket's default storage class to Standard, and create an Object Lifecycle Management rule to transition objects to Nearline after 30 days and to Archive after 365 days.. The bucket default class of Standard keeps daily editor access free of retrieval charges. Object Lifecycle Management transitions objects in place to Nearline after 30 days and to Archive after 365 days, matching access patterns to the cheapest suitable class without renaming objects or modifying code. Nearline's 30-day minimum and Archive's low storage price align with the described usage, and no retrieval fees apply to the Standard-resident daily files.

Answer

Set the bucket's default storage class to Archive, and rely on Autoclass to move objects to Standard when they are accessed.

Answer

Create a second bucket in Coldline and use a Cloud Storage transfer job to copy archived objects there, updating application paths to point to the new bucket.

Answer

Set the bucket's default storage class to Standard, and create an Object Lifecycle Management rule to transition objects to Coldline after 90 days of no access.

Question 10

An IoT platform writes sensor readings to BigQuery through the Storage Write API at roughly 500,000 rows per second into a table partitioned by ingestion date and clustered by device_id. Downstream dashboards read the most recent two hours with filters on device_id. The team notices that the streaming buffer makes recently written rows unavailable to some query patterns and wants to ensure the freshest data is queryable with the lowest latency. What should they do?

Accepted Answer

Use the Storage Write API with exactly-once semantics via stream offsets and rely on BigQuery's ability to query the streaming buffer for the newest rows.. The Storage Write API's exactly-once mode with stream offsets eliminates duplicate rows on retry, which matters at half a million rows per second where retries are common. Because BigQuery makes rows in the streaming buffer visible to queries right away, dashboards filtering the last two hours by device_id see the newest readings without waiting for a load job or a buffer flush.

Answer

Enable the BigQuery Storage Read API for the dashboards and increase the number of slots allocated to the project.

Answer

Switch ingestion to batch load jobs that run every 15 minutes so data lands in managed storage.

Answer

Keep using the Storage Write API with the default at-least-once semantics and query the table directly.

Question 11

A retail company ingests point-of-sale events into Pub/Sub and wants to store them in BigQuery for analytics. They need the table to automatically accept late-arriving events, deduplicate by a transaction_id field, and expire rows after 90 days to control storage. Which two BigQuery table configurations should they use? (Choose two.)

Accepted Answer

Create the table with a partition expiration set to 90 days on an ingestion-time partitioned table.. A change data capture enabled BigQuery table with a primary key on transaction_id allows the Pub/Sub BigQuery subscription to perform upserts, deduplicating repeated events by transaction_id. Setting partition expiration to 90 days on an ingestion-time partitioned table automatically removes partitions older than 90 days, satisfying row-level retention. Together these configurations handle late events, deduplication, and storage control without manual intervention.

Answer

Create the table with a primary key on transaction_id and enable upsert mode in the Pub/Sub BigQuery subscription.

Answer

Enable the Pub/Sub BigQuery subscription with a dead-letter topic to handle late events.

Answer

Configure the table's default table expiration to 90 days instead of partition expiration.

Question 12

A company needs a fully managed, globally distributed relational database with strong consistency, external consistency, and 99.999% SLA for a financial transaction processing system. Which Google Cloud service should they use?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it is a fully managed, globally distributed relational database service that provides strong consistency, external consistency (true serializable transactions across regions), and a 99.999% SLA. These features are essential for a financial transaction processing system that requires ACID compliance and global scalability without sacrificing consistency.

Answer

Firestore

Answer

Bigtable

Answer

Cloud SQL

Question 13

A data engineer needs to store raw sensor data in Cloud Storage and automatically transition it to a lower-cost storage class after 30 days, then delete it after 365 days. What should they configure?

Accepted Answer

Configure a lifecycle rule with SetStorageClass to Nearline after 30 days and Delete after 365 days.. Cloud Storage lifecycle management rules allow you to automatically transition objects to a lower-cost storage class (such as Nearline) after a specified number of days and then delete them after another period. This is the native, serverless way to manage object lifecycle without external scripts or compute resources.

Answer

Use Cloud Pub/Sub notifications to trigger a Cloud Function that moves objects.

Answer

Use gsutil rewrite command in a cron job.

Answer

Set a bucket retention policy with a retention period of 365 days.

Question 14

An e-commerce company uses Cloud Spanner for order processing. They need to query orders by customer ID and retrieve all order items. Which schema design pattern should they use for optimal performance?

Accepted Answer

Denormalize by storing order items as a repeated field in the orders table.. For this access pattern, the optimal Spanner design is to denormalize order items as a repeated field in the Orders table. A repeated field stores child rows inline with the parent row, so a query by customer_id (with a secondary index on customer_id) retrieves the order and all its items in a single lookup without a join or extra network round-trip. Interleaved tables co-locate child rows with the parent, but they only help when the query uses the parent's primary key prefix; querying by customer_id would still require a secondary index and then a join-like lookup to fetch interleaved children, so option A is not the best fit here.

Answer

Use interleaved tables where Orders is the parent and OrderItems is an interleaved child table with the same primary key prefix.

Answer

Store all data in a single table with nullable columns for order item attributes.

Answer

Create two separate tables with a secondary index on customer_id in the orders table and a secondary index on order_id in the order_items table.

Question 15

A mobile app needs an offline-first NoSQL database that syncs data across devices when connectivity is available. Which Google Cloud database meets these requirements?

Accepted Answer

Firestore. Firestore is a NoSQL, serverless, offline-first database that automatically syncs data across devices when connectivity is restored. It provides built-in offline persistence and real-time synchronization, making it ideal for mobile apps that need to work offline and sync later.

Answer

Memorystore

Answer

Cloud SQL

Answer

Bigtable