PDE Designing Data Processing Systems Practice Test 4 — 15 Questions

Question 1

A financial services company has a BigQuery dataset containing sensitive customer data. They need to share a subset of this data (excluding PII columns) with an external analytics partner. The partner should be able to query the data using their own BigQuery account, but the company must maintain full control over the underlying table and ensure the partner cannot see or access the original table. Which approach should they use?

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

Accepted Answer

Create an authorized view in the same dataset, excluding PII columns, and share only the view with the partner's BigQuery account.. An authorized view in BigQuery is a view whose underlying dataset has been granted access to the view's dataset, allowing the view to query source data even when the partner lacks direct table permissions. By sharing only the view (which excludes PII columns) with the partner's account, the company retains full control of the source table and the partner can query results without ever seeing the original data. This is the canonical BigQuery pattern for controlled data sharing across trust boundaries.

Answer

Use dataset-level ACLs to deny the partner access to the original table and grant access to a view.

Answer

Export the filtered data to a new BigQuery dataset and grant the partner access to that dataset.

Answer

Create a materialized view and grant the partner the bigquery.dataViewer role on the dataset.

Question 2

A data engineering team is designing a streaming pipeline using Dataflow to process real-time clickstream data from a website. They need to aggregate user session metrics (e.g., number of sessions, average duration) every 5 minutes. The pipeline must handle late-arriving events (up to 2 minutes late) and ensure exactly-once processing semantics. Which TWO of the following should they configure? (Choose two.)

Accepted Answer

Fixed windows of 5 minutes with allowed lateness of 2 minutes.. The correct answers are B and D. For aggregating session metrics every 5 minutes, Fixed windows of 5 minutes with allowed lateness of 2 minutes (option B) align with the desired interval and handle late data. To guarantee exactly-once processing semantics, the Dataflow pipeline must be configured with exactly-once processing mode (option D). Option E (Pub/Sub exactly-once delivery) is a source-level feature that can help prevent duplicates, but the question asks which two configurations to set for the pipeline itself. Option A (Session windows with a 10-minute gap) would merge sessions up to 10 minutes apart, not producing fixed 5-minute aggregates. Option C (Sliding windows with 5-minute size and 1-minute period) would emit results every minute, which is not required.

Answer

Session windows with a 10-minute gap and allowed lateness of 2 minutes.

Answer

Sliding windows of 5 minutes with a 1-minute period and allowed lateness of 2 minutes.

Answer

Pub/Sub subscription with exactly-once delivery.

Question 3

A company is migrating their on-premises Hadoop/Spark workloads to Google Cloud. They need a fully managed service that supports existing Spark jobs with minimal code changes, allows autoscaling, and provides integration with Cloud Storage and BigQuery. The team also wants to avoid managing cluster infrastructure and pay only for what they use. Which TWO services meet these requirements? (Choose two.)

Accepted Answer

Dataproc Serverless (Spark). Dataproc Serverless (Spark) (A) is correct because it runs existing Spark workloads without requiring code changes, automatically scales resources, integrates natively with Cloud Storage and BigQuery, and eliminates cluster infrastructure management with pay-per-use billing. Dataproc on GKE (B) is also correct because it runs Spark jobs on Google Kubernetes Engine, supports existing Spark applications with minimal changes, autoscales via GKE node pools, integrates with Cloud Storage and BigQuery, and removes the need to manage dedicated Dataproc cluster infrastructure. Standard Dataproc cluster with preemptible workers (C) is not correct because it still requires managing cluster infrastructure and is not fully serverless. Cloud Composer with Spark (D) is not correct because Cloud Composer is a managed Apache Airflow orchestration service, not a managed Spark execution service. Dataflow with Spark Runner (E) is not correct because Dataflow is based on Apache Beam and does not natively run existing Spark jobs without significant rewriting.

Answer

Standard Dataproc cluster with preemptible workers

Answer

Cloud Composer with Spark

Answer

Dataflow with Spark Runner

Question 4

A media company ingests 20 TB of raw video metadata files daily into Cloud Storage. Analysts need to run SQL queries on this data with minimal latency, but the files are not partitioned by date and the team wants to avoid managing a cluster. They also want to minimize query costs. Which Google Cloud solution should they use?

Accepted Answer

Load the data into a partitioned and clustered BigQuery table, then query it with SQL.. BigQuery's serverless nature and support for partitioning and clustering make it ideal for this scenario. Partitioning by date and clustering by frequently filtered columns minimizes data scanned, reducing costs and latency. Other options either require cluster management or fail to optimize query costs due to lack of partitioning.

Answer

Load the data into BigQuery as an external table and query it directly.

Answer

Create a Dataproc cluster with Hive and query the data using Spark SQL.

Answer

Use Cloud Data Fusion to build a pipeline that transforms and loads the data into BigQuery partitioned tables.

Question 5

A retail company streams point-of-sale transactions into Cloud Pub/Sub. The data engineering team must build a pipeline that enriches each transaction with customer loyalty data stored in Cloud BigQuery, then writes the enriched records to Cloud Bigtable for low-latency lookup by a mobile app. The enrichment must not block the streaming pipeline and must handle BigQuery API quota limits gracefully. Which design should they use?

Accepted Answer

Use Dataflow with BigQueryIO.read() as a side input to load the entire loyalty table, then join in memory.. The correct design uses a BigQuery side input in Dataflow to load the loyalty table once and join it with streaming transactions in memory. This avoids per-element API calls, prevents blocking, and naturally respects BigQuery quotas. It also keeps the pipeline scalable and serverless. The other options either block on each element, use batch-oriented tools, or add unnecessary cluster management.

Answer

Use Cloud Dataproc with a Spark Streaming job that queries BigQuery via the Spark-BigQuery connector for each micro-batch.

Answer

Use Dataflow with a DoFn that performs a synchronous BigQuery lookup for each element.

Answer

Use Cloud Data Fusion to create a pipeline with a BigQuery source and Bigtable sink, and enable the 'Use cache' property on the BigQuery source.

Question 6

An analytics team maintains a BigQuery dataset where a daily ELT pipeline truncates and reloads a 2 TB fact table at 02:00 UTC. Analysts report that ad-hoc queries run against the table in the early morning sometimes fail with a 'table not found' error, while the same query succeeds later in the day. The pipeline uses a single CREATE OR REPLACE TABLE statement. Which change should you make to eliminate the query failures while keeping the data fresh?

Accepted Answer

Switch the pipeline to write to a staging table and use an atomic ALTER TABLE ... SWAP WITH statement to replace the production table.. The intermittent 'table not found' errors come from CREATE OR REPLACE TABLE dropping the production table before recreating it. An atomic swap using ALTER TABLE ... SWAP WITH exchanges the staging and production tables in a single metadata operation, so the production table is always visible. This preserves the daily reload pattern while eliminating the window in which analysts cannot query the table.

Answer

Convert the table to a partitioned table and load each day's data into a new partition with a WRITE_TRUNCATE disposition.

Answer

Increase the table's expiration time and enable query caching on the dataset.

Answer

Add a streaming buffer hint to the CREATE OR REPLACE TABLE statement so the table is never dropped.

Question 7

A media company ingests 4 TB of newline-delimited JSON log files into Cloud Storage every day and needs to query them with standard SQL immediately, without loading them into a native table and without paying for storage twice. Analysts will query only a few fields per record and should not pay for columns they never read. Which BigQuery design should you choose?

Accepted Answer

Create an external table over the Cloud Storage bucket using Parquet files, so that only the referenced columns are read and billed.. A columnar format is what makes selective field access cheap, and newline-delimited JSON cannot provide column pruning. Converting the files to Parquet and exposing them through an external table keeps the data in Cloud Storage, allows standard SQL, and bills only for referenced columns, matching both the no-copy and cost constraints.

Answer

Create an external table over the Cloud Storage bucket using the JSON format, and rely on BigQuery to push down column selection to the reader.

Answer

Load the JSON into a native table partitioned by ingestion time, clustered by the frequently filtered field.

Answer

Create a BigLake external table over the Cloud Storage bucket with the JSON format and enable row-level security on the table.

Question 8

A retail analytics team stores point-of-sale transactions in Cloud Storage as newline-delimited JSON, arriving continuously throughout the day, and loads them into BigQuery with a Dataflow streaming pipeline. Analysts complain that ad-hoc queries on the raw landing table are slow and that duplicate transaction IDs appear after pipeline restarts. The team wants the target table to automatically deduplicate on transaction_id and to serve fast aggregate queries without analysts writing MERGE logic. Which BigQuery design should the data engineer implement?

Accepted Answer

Create a BigQuery table partitioned by transaction date and clustered by transaction_id, then query it through a logical view that selects the latest row per transaction_id using a window function.. A partitioned and clustered native table combined with a deduplicating logical view delivers automatic duplicate suppression and fast scans. Partition pruning limits bytes read to the relevant dates, clustering co-locates rows sharing a transaction_id, and the window-function view hides duplicates from analysts. Alternatives either rely on unenforced constraints, add external-read overhead, or push MERGE maintenance back onto the team, which the scenario rules out.

Answer

Use a BigQuery streaming buffer with a MERGE statement scheduled every minute against a staging table to remove duplicates.

Answer

Load the raw JSON into a BigQuery external table over Cloud Storage and query it directly with aggregate SQL.

Answer

Create a BigQuery table with a transaction_id column as the primary key and enable automatic duplicate rejection on insert.

Question 9

A retail company wants to ingest point-of-sale events into BigQuery with minimal operational overhead. The events arrive continuously and must be queryable within seconds. The team has no existing message broker and wants to avoid managing servers. Which ingestion approach should they choose?

Accepted Answer

Use the BigQuery Storage Write API with a default stream to write events directly from the application.. The Storage Write API default stream lets applications send records directly to BigQuery with exactly-once semantics, and the data becomes immediately queryable. It requires no brokers or servers to manage, which matches the requirement for minimal operational overhead and low latency. Other options either add operational burden or introduce unacceptable delay.

Answer

Deploy a self-managed Kafka cluster on Compute Engine and use the Kafka Connect BigQuery sink connector.

Answer

Write events to Cloud SQL and create a federated query from BigQuery to the Cloud SQL instance.

Answer

Schedule a recurring Cloud Dataflow batch job that reads from Cloud Storage and writes to BigQuery every five minutes.

Question 10

A financial services firm is designing a data processing system on Google Cloud. They need to ingest sensitive customer data from an on-premises Oracle database into BigQuery for analytics. The data must be encrypted at rest with customer-managed encryption keys (CMEK) and must comply with strict audit requirements. The ingestion must occur daily and minimize data movement costs. Which approach should they use?

Accepted Answer

Use BigQuery Data Transfer Service with an Oracle connector, configure the destination dataset with CMEK, and enable Cloud Audit Logs for BigQuery.. BigQuery Data Transfer Service with an Oracle connector provides a fully managed, automated daily ingestion path. It supports CMEK on the destination dataset and integrates with Cloud Audit Logs, satisfying encryption and audit requirements. It also minimizes data movement costs by using Google's network and avoids manual steps. The other options either use unsupported services, add complexity, or fail to meet CMEK and audit needs.

Answer

Use Cloud SQL for Oracle as a staging area, then use federated queries from BigQuery to access the data directly without moving it.

Answer

Use Cloud Data Fusion to replicate the Oracle database to BigQuery, configuring CMEK on the BigQuery dataset and enabling audit logging.

Answer

Export the Oracle data to CSV files, upload to Cloud Storage with CMEK, and use a Dataflow batch job to load into BigQuery with CMEK.

Question 11

A retail company ingests point-of-sale events into Pub/Sub and lands them in Cloud Storage using a Dataflow streaming pipeline. Analysts query the raw files with BigQuery external tables, but queries are slow and expensive because the files are small JSON objects. You need to redesign the storage layer so that BigQuery can query the data efficiently while preserving the ability to reprocess raw events. What should you do?

Accepted Answer

Configure the Dataflow pipeline to write Parquet files partitioned by event date into Cloud Storage, and create a BigQuery external table over the partitioned path.. Converting the landing format to partitioned Parquet in Cloud Storage gives BigQuery columnar compression and partition pruning, which reduces bytes scanned and speeds up external queries. Keeping raw events in Cloud Storage satisfies the reprocessing requirement. Simply scaling workers or changing Avro options does not address the root cause, and streaming into a native table while deleting raw files breaks the reprocessing constraint.

Answer

Load all events into a BigQuery native table with streaming inserts and delete the Cloud Storage files to avoid duplication.

Answer

Increase the number of Dataflow workers to write larger JSON files, and query them with BigQuery external tables using the same schema.

Answer

Create a BigQuery external table over the existing JSON files and enable the 'use_avro_logical_types' option to improve performance.

Question 12

A retail analytics team must run a nightly batch pipeline that reads from BigQuery, applies Python transformations with pandas, and writes results back to BigQuery. The team has no Kubernetes expertise, wants minimal operational overhead, and needs the pipeline to run on a schedule with retries and email alerts on failure. Which Google Cloud service should they use?

Accepted Answer

A Cloud Run job containing the pandas code, triggered on a schedule by Cloud Scheduler.. A containerised batch job on Cloud Run jobs gives the team a serverless, no-Kubernetes runtime that can be sized for pandas, while Cloud Scheduler supplies cron triggering and Cloud Monitoring supplies failure alerting. This combination meets the scheduling, retry, and notification needs without cluster operations.

Answer

Cloud Scheduler invoking a Cloud Function that performs the pandas transformations.

Answer

A Dataproc cluster with a PySpark job submitted on a cron schedule.

Answer

Cloud Composer with a PythonOperator that runs the pandas code on the Airflow worker.

Question 13

You are designing a data pipeline that ingests data from multiple sources (streaming and batch) into BigQuery. You need to ensure that the data is partitioned and clustered for optimal query performance and cost. Which two strategies should you use? (Choose two.)

Accepted Answer

Partition the table by a DATE column and cluster by a high-cardinality column.. Partitioning by ingestion time or a DATE column and clustering by frequently filtered columns are the two recommended strategies. These techniques enable partition pruning and block skipping, which reduce the amount of data scanned and improve query performance while lowering costs.

Answer

Use streaming inserts for all data and rely on BigQuery's automatic optimization.

Answer

Create a separate table for each day and use wildcard table queries.

Answer

Cluster the table by all columns to maximize query performance.

Question 14

A financial services firm is designing a Dataflow pipeline that reads from Pub/Sub and writes to BigQuery. The pipeline must handle late-arriving events and produce accurate aggregations per account. The team wants to minimize data loss and duplication. Which two design choices should they make? (Choose two.)

Accepted Answer

Use a Pub/Sub subscription with exactly-once delivery and set the pipeline's windowing to use allowed lateness with accumulating mode.. Handling late-arriving events and minimizing duplication requires two things: a delivery guarantee that reduces duplicates and a windowing strategy that accepts late data. Pub/Sub exactly-once delivery plus allowed lateness with accumulating mode addresses both. Enabling the streaming engine and using non-deterministic triggers further supports accurate, timely per-account aggregations.

Answer

Set the Pub/Sub subscription to at-least-once delivery and rely on BigQuery's insertId for deduplication.

Answer

Use a GlobalWindow with a default trigger and no allowed lateness to simplify the pipeline logic.

Answer

Configure the BigQuery sink to use FILE_LOADS with a fixed number of workers and disable autoscaling.

Question 15

A media company streams video playback events into Pub/Sub. They need to design a Dataflow pipeline that computes session windows per user and writes aggregated results to BigQuery. The pipeline must handle late-arriving events up to 24 hours and must not drop any data. Which two design choices should they make? (Choose two.)

Accepted Answer

Configure the session window with a allowed lateness of 24 hours and use accumulating mode for the trigger.. Session windows with a gap duration correctly model user sessions. Allowed lateness of 24 hours ensures late events are processed, and accumulating mode preserves full aggregates so no data is lost. Firing after the watermark plus allowed lateness ensures late events are included before final emission. Fixed or global windows and discarding mode fail the late-data and no-drop requirements.

Answer

Use a global window with a single trigger that fires once at the end of the pipeline run.

Answer

Use a fixed window of 1 hour with a default trigger and no allowed lateness.

Answer

Set the pipeline to use discarding mode for the trigger to reduce output size.