PDE Storing the Data Practice Test 3 — 15 Questions

Question 1

A company uses BigQuery partitioned tables with daily partitions for log data. They want to automatically delete partitions older than 90 days and ensure that current month data is in a specific dataset with a set expiration. Which TWO actions should they take? (Choose 2)

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

Accepted Answer

Set partition expiration to 90 days on the table. Option A is correct because setting partition expiration to 90 days on the table automatically deletes partitions whose partition time exceeds the 90-day retention window, which directly satisfies the requirement to remove log partitions older than 90 days. Option C is correct because creating the table as a partitioned table by ingestion time makes each day's loaded data land in its own date-based partition, which is the prerequisite for partition-level expiration to work as intended. Option D is not correct as a separate action because it describes the same mechanism as A rather than a distinct required step, and the question asks for two actions. Option B is not correct because clustering on a timestamp column only co-locates related rows for query performance and does not delete old data. Option E is not correct because a Cloud Storage lifecycle rule deletes GCS objects, not BigQuery partitions, and does not address dataset-level expiration.

Answer

Cluster the table on a timestamp column

Answer

Set the table's default partition expiration to 90 days

Answer

Set a lifecycle management rule on Cloud Storage to delete objects older than 90 days

Question 2

A retail analytics team loads a 2 TB CSV dataset into BigQuery once every night and then runs reporting queries the next morning. The analysts want the lowest storage cost for this data while keeping the ability to query it normally with standard SQL. The table is written once and rarely modified after loading. Which BigQuery storage option should the engineer choose?

Accepted Answer

BigQuery physical storage with the long-term storage discount applied automatically.. Because the data is written once and rarely modified, most of it will age past the 90-day modification threshold and automatically receive BigQuery long-term storage pricing. Physical billing charges for compressed bytes, further lowering cost, and standard SQL querying continues to work unchanged. External tables and non-analytical stores introduce retrieval costs or semantic limitations that conflict with daily reporting.

Answer

Load the data into a Bigtable table and query it with SQL through the BigQuery connector.

Answer

BigQuery logical storage with the default active storage billing.

Answer

Store the CSV files in Cloud Storage Nearline and query them with an external table.

Question 3

A data engineer manages a Cloud Storage bucket that receives millions of small log files per day. Analysts run BigQuery queries over these files using an external table, but query performance is poor and costs are high. The engineer wants to improve query performance and reduce cost without changing the file format. What should the engineer do?

Accepted Answer

Load the files into a BigQuery native table using a scheduled query that batches files by hour.. External tables over millions of small files incur per-object listing and read overhead, and queries scan the raw non-columnar data. Loading the files into a native BigQuery table consolidates them into Capacitor columnar storage, which reduces bytes scanned and removes per-file overhead while preserving the existing file format.

Answer

Increase the number of slots in the project's reservation to speed up the external table queries.

Answer

Set a default table expiration on the bucket's external table to remove old files automatically.

Answer

Enable BigQuery BI Engine and point the external table at the BI Engine reservation.

Question 4

A data engineer needs to load data from a Cloud Storage bucket into BigQuery. The source files are CSV, gzip-compressed, and new files arrive every hour. The engineer wants the load to be as fast and cost-effective as possible while minimizing manual intervention. What should the engineer do?

Accepted Answer

Create a Cloud Function triggered by object finalize events that starts a BigQuery load job.. A Cloud Function triggered by object finalize events provides a serverless, event-driven way to start a BigQuery load job as soon as a new CSV file lands in Cloud Storage. This minimizes manual intervention, scales automatically, and avoids the cost of orchestration tools like Cloud Composer. It is more direct and cost-effective than Data Transfer Service for simple hourly loads, and it uses real, correctly named services.

Answer

Use the Storage Transfer Service to move files to a BigQuery-managed bucket and rely on auto-load.

Answer

Schedule a Cloud Composer DAG that runs a bq load command every hour.

Answer

Use the BigQuery Data Transfer Service with a Cloud Storage transfer configuration.

Question 5

A financial analytics team loads daily trade records into a BigQuery table that is partitioned by trade_date. The table is append-only, and late-arriving records occasionally need to replace rows already loaded for a past date. The team wants the replacement to be atomic and wants to avoid full-table rewrites. Which approach should the data engineer recommend?

Accepted Answer

Run a DELETE statement for the affected trade_date followed by an INSERT of the corrected rows inside a single multi-statement transaction.. A multi-statement transaction containing a partition-scoped DELETE and an INSERT replaces the affected date's rows atomically without rewriting the entire table. Partition pruning keeps the DELETE cheap, and atomicity guarantees that concurrent readers either see the old data or the fully corrected data, never an intermediate state.

Answer

Use a MERGE statement that matches on trade_date and updates every row in the target table.

Answer

Use streaming inserts with a unique insert_id for each corrected row so BigQuery deduplicates and overwrites the old values.

Answer

Load the corrected rows into a new table and use ALTER TABLE to swap the new table into place.

Question 6

An IoT company ingests sensor readings into Cloud Bigtable. Each row key currently starts with a device identifier followed by a reverse timestamp. Write throughput is uneven, and some tablet servers are hot while others are idle. The team wants to distribute writes evenly across nodes without changing the query patterns, which retrieve the latest readings per device. What should they do?

Accepted Answer

Use a row key that starts with a salted hash of the device identifier, then the device identifier, then the timestamp.. Salting the row key with a hash of the device identifier spreads writes across the key space, which reduces hotspotting on tablet servers. Retaining the device identifier and timestamp after the salt preserves the ability to scan per-device readings and fetch the latest values. This addresses the uneven write distribution while keeping the query pattern unchanged.

Answer

Add a hash prefix to the row key before the device identifier and store the original device identifier in a column.

Answer

Increase the number of Bigtable nodes in the cluster to absorb the write load.

Answer

Reverse the timestamp and place it before the device identifier in the row key.

Question 7

A data engineering team is migrating an on-premises Apache HBase cluster to Google Cloud. The workload requires a wide-column NoSQL store with low-latency reads and writes, and the team wants a fully managed service that supports the HBase API. They also need to minimize operational overhead. Which two Google Cloud services should they consider? (Choose two.)

Accepted Answer

Bigtable HBase client for Java. Cloud Bigtable is the managed wide-column NoSQL service that supports the HBase API, and the Bigtable HBase client for Java provides the compatibility layer for existing HBase applications. Together they allow a migration from on-premises HBase with low operational overhead while preserving the low-latency access patterns required by the workload.

Answer

Firestore

Answer

BigQuery

Answer

Cloud Spanner

Question 8

A data engineer manages a BigQuery dataset that receives a continuous stream of events. Analysts frequently run point lookups on a single `event_id` column and also need to retrieve the most recent 100 events per `device_id`. The table is partitioned by `event_timestamp` and currently has no clustering. Queries filtering only on `event_id` scan the entire table. The engineer wants to reduce bytes scanned and improve performance for these two access patterns without changing the partitioning scheme. What should the engineer do?

Accepted Answer

Add a clustered column list on device_id, event_timestamp to the table.. Clustering on device_id followed by event_timestamp is the most effective single change for the per-device latest-events pattern because BigQuery can prune blocks by device_id and use the sorted order of event_timestamp to satisfy ORDER BY ... LIMIT efficiently. It does not replace partitioning; the table remains partitioned by event_timestamp, preserving time-range pruning. For event_id point lookups, the engineer should consider adding event_id as a trailing cluster column, but the device_id pattern is the stronger driver for the leading column choice.

Answer

Change the table to use ingestion-time partitioning on event_timestamp and remove the existing column partitioning.

Answer

Create a materialized view that selects all columns and filters on event_id, then query the view instead of the base table.

Answer

Add a clustered column list on event_id, device_id to the table.

Question 9

A media company stores millions of video files in Cloud Storage and needs to organize them for analytics and lifecycle management. They want to efficiently list and filter files by attributes such as content type, production year, and licensing status, and they want to apply different lifecycle rules per category. They also want to avoid maintaining a separate database for metadata. Which two Cloud Storage features should they use? (Choose two.)

Accepted Answer

Object tags with lifecycle conditions that match specific tag values.. Prefix-based object naming enables efficient listing and filtering by category and year, and it lets lifecycle rules target specific prefixes. Object tags provide key-value attributes that lifecycle conditions can match, supporting per-category policies such as licensing status. Together they deliver organization and lifecycle management at scale without maintaining a separate metadata database, satisfying both stated requirements.

Answer

Bucket labels to group objects by category for billing and organization.

Answer

Object metadata with custom key-value pairs on each object.

Answer

A Cloud SQL for MySQL instance that stores object metadata and pointers to Cloud Storage paths.

Question 10

A data engineer needs to store JSON documents in Google Cloud and query them with SQL-like syntax. The documents have varying schemas, and the engineer wants a fully managed, serverless database that automatically scales and integrates with other Google Cloud services. Which service should the engineer choose?

Accepted Answer

Firestore in Datastore mode. Firestore in Datastore mode is a serverless document database that automatically scales and supports GQL, a SQL-like query language. It is designed for schemaless JSON-like entities and integrates with Google Cloud services, matching the requirements for storing and querying JSON documents with varying schemas without managing infrastructure.

Answer

BigQuery

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Firestore in Native mode

Question 11

A startup stores customer profile documents in Firestore in Native mode and needs to run a nightly export of all documents to Cloud Storage for archival and downstream analytics. The export must include all collections and be usable by BigQuery later. Which approach should the data engineer use?

Accepted Answer

Use the managed export feature to export all documents to a Cloud Storage bucket in a format that BigQuery can load.. The managed export feature is the supported, code-free way to copy all Firestore documents, including subcollections, into Cloud Storage in a format that can be loaded into BigQuery. It handles scale and consistency internally, so the nightly archival job needs only scheduling rather than custom application logic.

Answer

Run a nightly gcloud firestore export command to a Cloud Storage bucket and rely on the default output format.

Answer

Enable the Firestore to BigQuery extension and stream changes into BigQuery in real time.

Answer

Write a Cloud Function that queries each collection and writes JSON files to Cloud Storage on a nightly schedule.

Question 12

A data engineer is migrating an on-premises PostgreSQL database to Google Cloud. The database is used for OLTP workloads with high read throughput and requires automatic failover across zones. The engineer needs to choose a managed database service that supports PostgreSQL and meets these requirements. (Choose two.)

Accepted Answer

Cloud SQL for PostgreSQL with a regional instance and a read replica.. Cloud SQL for PostgreSQL with a regional instance and read replica provides managed PostgreSQL, automatic failover across zones, and read scaling. Cloud Spanner with PostgreSQL dialect offers a globally distributed, strongly consistent relational database with automatic failover and high throughput. Both are valid choices for the OLTP and failover requirements. BigQuery, Cloud SQL for MySQL, and Firestore do not meet the PostgreSQL compatibility or OLTP needs.

Answer

Firestore in Datastore mode with multi-region replication.

Answer

Cloud SQL for MySQL with a cross-region replica.

Answer

BigQuery with the PostgreSQL-compatible interface.

Question 13

A financial services firm ingests trade events into Pub/Sub and needs to land them in BigQuery with exactly-once semantics, low latency, and no custom deduplication logic in downstream queries. The operations team wants the simplest managed path that preserves message ordering per trading account and supports schema evolution without manual DDL for every new field. Which ingestion approach should they choose?

Accepted Answer

Use a BigQuery subscription on the Pub/Sub topic with dead-letter handling and write metadata enabled. A BigQuery subscription is the managed, serverless way to move Pub/Sub messages into BigQuery without running your own pipeline. It gives exactly-once delivery into the destination table, supports dead-letter topics, and can write message attributes as columns. Enabling schema evolution on the table lets new Pub/Sub fields be added automatically, which removes the need for manual DDL and keeps latency low for trade events.

Answer

Use a Dataflow streaming pipeline with Pub/Sub source and BigQueryIO with STORAGE_WRITE_API and deterministic deduplication

Answer

Use a Cloud Function triggered by Pub/Sub to call the BigQuery streaming insert API with insertId values

Answer

Use the BigQuery Storage Write API with the default stream and application-level deduplication keys

Question 14

A financial analytics team ingests trade events into a BigQuery table where the `trade_date` column is a DATE and the table is partitioned by that column. Most queries filter on a single trading day, but a small set of month-end reconciliation queries scan an entire month and must remain cost-efficient. You want to minimize bytes scanned for both query patterns while keeping the table manageable. What should you do?

Accepted Answer

Keep the table partitioned by `trade_date` and additionally cluster by a high-cardinality column such as `symbol`.. Partitioning by the DATE column prunes data for single-day filters, which is the dominant pattern. Clustering on a high-cardinality column such as symbol further reduces bytes scanned inside each partition for month-end queries that also filter or group by symbol. Together they address both access patterns while keeping a single managed table.

Answer

Convert the table to a partitioned table using integer range partitioning on `trade_date`.

Answer

Create a clustered table on `trade_date` and rely on automatic partitioning.

Answer

Create a materialized view for each day and let BigQuery route queries to the appropriate view.

Question 15

A data engineer is configuring a BigQuery dataset that contains a table with sensitive customer records. The table is partitioned by `ingestion_date` and clustered by `customer_id`. The engineer needs to grant a group of analysts the ability to query only rows where `region = 'EU'` and only columns `customer_id`, `order_total`, and `ingestion_date`, while preventing them from seeing other columns or rows. The analysts use the BigQuery console and the bq command-line tool. Which two approaches should the engineer use? (Choose two.)

Accepted Answer

Create an authorized view on the dataset, grant the analysts BigQuery Data Viewer on the view, and revoke their access to the base table.. The requirement is to restrict both columns and rows for a group of analysts. A logical view that selects only the allowed columns and filters region = 'EU' can enforce both restrictions, and granting access only to the view prevents base table access. An authorized view is a stronger variant because it allows the view to read the base table even when the analysts lack direct access, and it is the recommended pattern for sharing restricted data. Together, these two approaches satisfy the column and row restrictions without exposing the base table.

Answer

Grant the analysts BigQuery Data Viewer on the dataset and use a policy tag on the region column to hide non-EU rows.

Answer

Use a column-level IAM policy on the base table to allow only the three columns, and a row access policy to filter region = 'EU'.

Answer

Create a row access policy on the base table that allows access only when region = 'EU', and create a policy tag on the sensitive columns with fine-grained access control.