PDE Storing the Data Practice Test 11 — 15 Questions

Question 1

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?

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

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 2

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 3

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 4

A healthcare company stores patient documents in a Cloud Storage bucket with a retention policy. Auditors require that once an object is written, it cannot be overwritten or deleted by any user, including project owners, for 7 years. The company also wants to minimize storage cost after the first year while preserving immutability. What should the data engineer configure?

Accepted Answer

Apply a bucket lock on a retention policy with a 7-year retention period, then add a lifecycle rule to transition objects to Archive after 365 days.. A locked retention policy is the only Cloud Storage mechanism that enforces immutability against all users, including project owners, for the specified duration. Combining it with a lifecycle rule that transitions objects to Archive after one year satisfies the retention requirement while lowering storage cost for the remaining six years.

Answer

Enable uniform bucket-level access and create a retention policy without locking it, then transition objects to Coldline after 365 days.

Answer

Set an IAM deny policy on storage.objects.delete for all principals and use a lifecycle rule to move objects to Nearline after 365 days.

Answer

Enable Object Versioning and set a lifecycle rule to move objects to Coldline after 365 days.

Question 5

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.

Question 6

A financial services firm stores transaction records in a BigQuery table partitioned by transaction_date. Compliance requires that rows older than seven years be permanently and irreversibly deleted, and the team must prove deletion occurred. The table currently uses the default partition expiration of never. The engineer must implement the retention policy without dropping the whole table. (Choose two.)

Accepted Answer

Verify retention by querying INFORMATION_SCHEMA.PARTITIONS to confirm partitions beyond the retention window no longer exist.. Partition expiration is the native BigQuery mechanism that irreversibly removes whole partitions once they age past the retention window, and it operates without dropping the table so recent data stays available. The compliance proof comes from INFORMATION_SCHEMA.PARTITIONS, which shows exactly which partitions remain. A scheduled DELETE leaves recoverable copies through time travel and fail-safe, Cloud Storage lifecycle rules cannot see BigQuery-managed storage, and key destruction is not selective enough to age out only old rows.

Answer

Use Cloud Key Management Service with a customer-managed encryption key and destroy the key version after seven years.

Answer

Configure a Cloud Storage lifecycle rule on the table's underlying data to remove objects older than seven years.

Answer

Create a scheduled query that runs DELETE FROM the table WHERE transaction_date < DATE_SUB(CURRENT_DATE(), INTERVAL 7 YEAR).

Question 7

A retail company uploads daily sales CSV files to a Cloud Storage bucket. The analytics team wants BigQuery to automatically detect the schema and make new files queryable without running load jobs. The files are added with date-based prefixes, and the team wants to minimize operational overhead. Which BigQuery feature should they use?

Accepted Answer

A BigQuery external table with schema autodetect over the Cloud Storage prefix.. An external table over the Cloud Storage prefix with schema autodetect lets BigQuery read the CSV files directly. New files that match the prefix appear in query results without any load jobs, which precisely fits the requirement to minimize operational overhead while keeping daily files queryable.

Answer

A Cloud Dataflow streaming pipeline that writes rows into BigQuery.

Answer

A BigQuery native table loaded with a recurring scheduled query.

Answer

A BigQuery table partitioned by ingestion time with a load job per file.

Question 8

A healthcare company stores patient records in Cloud SQL for PostgreSQL and needs to run analytical queries on the same data without impacting the production database. The analytics team requires near-real-time replication and wants to use BigQuery for querying. The data must be kept in sync with minimal latency and without writing custom ETL code. Which solution should the data engineer implement?

Accepted Answer

Use Datastream to replicate from Cloud SQL for PostgreSQL to BigQuery with a dataset-level replication. Datastream provides serverless change data capture from Cloud SQL for PostgreSQL to BigQuery. It reads the database's write-ahead log and applies changes to BigQuery with low latency, keeping the analytical store in sync without custom code. This offloads analytics from the production database and meets the near-real-time and no-ETL requirements. Other options either introduce batch latency, require pipeline development, or do not create a separate analytical store.

Answer

Create a read replica of the Cloud SQL instance and point BigQuery federated queries at the replica

Answer

Use Cloud Data Fusion to build a pipeline that reads from Cloud SQL and writes to BigQuery on a schedule

Answer

Schedule a nightly export from Cloud SQL to Cloud Storage and load the files into BigQuery

Question 9

A startup runs an operational analytics dashboard on a Cloud SQL for MySQL instance. The dashboard issues many short read queries that are slowing down the write-heavy transactional workload on the primary instance. The team wants to offload reads without changing application write logic and without re-architecting to a different database engine. What should the data engineer configure?

Accepted Answer

Create one or more read replicas of the Cloud SQL instance and direct the dashboard's read queries to the replica connection.. Read replicas are the intended Cloud SQL feature for scaling read traffic: the dashboard connects to a replica endpoint, the transactional application keeps writing to the primary, and no write-path code changes are required. A high-availability standby is passive and cannot serve reads, vertical scaling keeps both workloads on the same instance, and federated BigQuery queries still hit the source database. The replica approach isolates the dashboard's short read queries from the write-heavy workload.

Answer

Enable high availability on the primary instance so a standby instance can absorb the dashboard's read queries.

Answer

Migrate the dashboard to query a BigQuery federated external table that reads directly from the Cloud SQL instance.

Answer

Increase the number of vCPUs and memory on the primary instance to handle the combined read and write load.

Question 10

A logistics company writes shipment telemetry to a Cloud Bigtable table using a row key of shipment_id, where shipment_id values are monotonically increasing and roughly sequential over time. Write throughput has become uneven, with a small number of tablet servers overloaded while others sit idle. The engineer must improve write distribution without changing the schema of the column families. What should the engineer do?

Accepted Answer

Prepend a hashed or reversed component to the row key so writes spread across the key space instead of concentrating at the high end.. Bigtable distributes rows by row key ranges, so a monotonically increasing key sends every new write to the tablet owning the highest range, overloading one node while others idle. Adding a hash bucket prefix or reversing the key scatters writes across many ranges and tablets, restoring even load without altering column families. Automatic rebalancing, extra column families, and replication clusters all leave the underlying key concentration in place, so the hotspot persists.

Answer

Create a Cloud Bigtable replication cluster in a second zone and write half the traffic to each cluster.

Answer

Increase the number of column families in the table to spread the write load across more storage structures.

Answer

Enable automatic region rebalancing so Bigtable redistributes the hot rows across the cluster.

Question 11

A media company stores video files in a Cloud Storage bucket and wants to serve them to users through a custom domain over HTTPS. The files are not sensitive, but the company wants to reduce egress cost and latency for users distributed globally. The company also wants to avoid managing SSL certificates manually. What should the data engineer recommend?

Accepted Answer

Create a Cloud CDN backend bucket with the Cloud Storage bucket as the origin and enable Google-managed SSL certificates on the load balancer.. Cloud CDN with a backend bucket caches video content at Google's edge locations, lowering latency and egress for global users. Pairing it with a global external Application Load Balancer and Google-managed SSL certificates delivers HTTPS on a custom domain without the operational burden of managing certificates.

Answer

Enable Object Versioning on the bucket and configure a lifecycle rule to move objects to Nearline after 30 days.

Answer

Set the bucket's default storage class to Standard and enable uniform bucket-level access.

Answer

Generate signed URLs for each video and distribute them to users through the application.

Question 12

A media company stores 50 TB of video metadata in Cloud Storage buckets in the Standard class. Legal requires that records for a subset of titles be retained for exactly seven years and that they cannot be altered or deleted by any user, including project owners, during that period. The company also wants to minimize storage cost for a second bucket holding derived thumbnails that are accessed only a few times per year but must be retrievable within seconds. Which two configurations should the data engineer implement? (Choose two.)

Accepted Answer

Create a bucket-level retention policy with a seven-year retention period and lock it.. A locked bucket retention policy delivers the immutability legal demands because it cannot be removed once locked and blocks deletion or overwrite by any principal for the full seven years. For the thumbnails, Nearline matches an access pattern of a few reads per year with second-level retrieval and a lower price than Standard, avoiding the longer minimum-duration and slower-access profile that Coldline or Archive would impose.

Answer

Enable Object Versioning on the metadata bucket to preserve prior versions of each object.

Answer

Set an Object Lifecycle Management rule to move the metadata objects to Archive storage after one year.

Answer

Create the thumbnails bucket in the Coldline storage class and enable Turbo Replication.

Question 13

A data engineer is loading a 2 TB CSV dataset from Cloud Storage into a partitioned BigQuery table. The load job fails with an error indicating too many errors during parsing. The source files have inconsistent column counts and some rows contain embedded newlines. The engineer wants to load the data reliably with minimal preprocessing. What should the engineer do?

Accepted Answer

Convert the CSV files to newline-delimited JSON or Parquet, then load the converted files into BigQuery.. CSV parsing is delimiter-based and breaks when rows contain embedded newlines or inconsistent column counts. Converting to newline-delimited JSON or Parquet removes delimiter ambiguity and lets BigQuery load the data reliably, addressing the root cause rather than suppressing errors or increasing throughput.

Answer

Load the CSV files into a single external table and query it with the ignoreUnknownValues option.

Answer

Set the maxBadRecords option to a high value and rerun the load job with the same CSV files.

Answer

Increase the load job's maximum parallelism by splitting the CSV files into smaller objects in Cloud Storage.

Question 14

A healthcare analytics team must store patient records in BigQuery and prove to auditors that no one can read or modify the data without an explicit, logged authorization. They want to enforce column-level access so that only the billing team can see insurance_id, while the research team sees only de-identified columns. Which BigQuery feature should they implement?

Accepted Answer

Policy tags in a data taxonomy applied to the insurance_id column, with column-level access granted to the billing team.. Policy tags in a data taxonomy provide column-level access control in BigQuery. Applying a policy tag to insurance_id and granting the fine-grained reader role only to the billing team ensures the research team cannot query that column even though they can access the table. BigQuery logs policy tag enforcement, supporting the auditor's requirement for explicit, logged authorization. Other features address rows, encryption, or views but not column-level authorization.

Answer

Authorized views that join the patient table with a lookup table to mask insurance_id.

Answer

Row-level security policies that filter rows based on the SESSION_USER() function.

Answer

Customer-managed encryption keys stored in Cloud KMS and rotated every 90 days.

Question 15

A logistics company runs Cloud SQL for MySQL for its order management system. The database is 2 TB and growing 100 GB per month. They need to run analytical queries without impacting transactional performance, and they want to minimize operational overhead. They also require the analytics data to be no more than 15 minutes stale. Which storage approach should they use?

Accepted Answer

Configure a Datastream stream from Cloud SQL to BigQuery with a change data capture policy and query the replicated tables in BigQuery.. Datastream provides serverless change data capture from Cloud SQL for MySQL into BigQuery, keeping the analytics copy within minutes of the source. BigQuery isolates analytical scans from the transactional instance, so order management performance is unaffected. The managed service minimizes operational overhead compared to maintaining replicas or custom export jobs, and it satisfies the 15-minute freshness requirement through continuous replication.

Answer

Export the Cloud SQL database to a Cloud Storage bucket nightly with mysqldump, then load the files into BigQuery each morning.

Answer

Enable the Cloud SQL federated query feature in BigQuery and query the Cloud SQL instance directly from BigQuery.

Answer

Create a read replica of the Cloud SQL instance and run analytical queries directly against the replica.