Scenario practice questions

You are optimizing a BigQuery query that scans 1 TB of data every day. The query joins a large fact table (partitioned by date) with a small dimension table. You notice that the query always scans the entire fact table, even though you only need the last 7 days of data. Which optimization will MOST reduce the bytes scanned?

Trap 1: Create a materialized view that pre-aggregates the data by day.

A materialised view pre-aggregates results but does not prune partitions; the query still reads the full fact table unless a date filter is applied. It is tempting for repeated aggregate queries, where it would be correct, but here the requirement is restricting scanned partitions to the last seven days.

Trap 2: Cluster the fact table on the join key used in the query.

Clustering on the join key speeds up join processing but does not reduce bytes scanned, since clustering prunes only on the clustered columns and the query still reads every date partition. It is tempting for join-heavy queries, where it would be correct, but partition pruning on the date column is what limits scanned bytes.

Trap 3: Change the table to use time-unit column partitioning with a 1-day…

The fact table is already partitioned by date, so redefining partitioning as a time-unit column with a 1-day interval changes nothing about pruning. It is tempting because day-level partitioning is the right pattern for date-filtered scans, but the existing setup already satisfies it; the query needs a date predicate.

A
Create a materialized view that pre-aggregates the data by day.
Why it fails: A materialised view pre-aggregates results but does not prune partitions; the query still reads the full fact table unless a date filter is applied. It is tempting for repeated aggregate queries, where it would be correct, but here the requirement is restricting scanned partitions to the last seven days.
B
Add a WHERE clause that filters on the date column used for partitioning.
Partition pruning only activates when the query filters on the partitioning column. Adding that WHERE predicate lets BigQuery eliminate all but the last seven date partitions, cutting bytes scanned from 1 TB to roughly 7 days' worth.
C
Cluster the fact table on the join key used in the query.
Why it fails: Clustering on the join key speeds up join processing but does not reduce bytes scanned, since clustering prunes only on the clustered columns and the query still reads every date partition. It is tempting for join-heavy queries, where it would be correct, but partition pruning on the date column is what limits scanned bytes.
D
Change the table to use time-unit column partitioning with a 1-day partition interval.
Why it fails: The fact table is already partitioned by date, so redefining partitioning as a time-unit column with a 1-day interval changes nothing about pruning. It is tempting because day-level partitioning is the right pattern for date-filtered scans, but the existing setup already satisfies it; the query needs a date predicate.

Question 2mediummulti select

You are designing a Dataflow pipeline for processing real-time clickstream data. The pipeline must group events into 30-second windows and handle late data up to 5 minutes. You want to output partial results every 10 seconds for low-latency monitoring. Which THREE configurations should you use? (Choose three.)

Trap 1: Use sliding windows of 30 seconds with a 10-second period

Sliding windows of 30 seconds with a 10-second period produce overlapping panes, so events are counted in multiple windows and totals are duplicated. It is tempting because the 10-second period appears to match the latency requirement, and it would be correct for computing moving averages rather than distinct 30-second groupings.

Trap 2: Use a trigger that fires after the end of the window

A trigger firing only at window end emits nothing during the 30-second window, so the required 10-second partial results never appear. It is tempting because end-of-window firing is the default behaviour, and it would be correct when complete, non-speculative results are wanted and latency is unimportant.

A
Use sliding windows of 30 seconds with a 10-second period
Why it fails: Sliding windows of 30 seconds with a 10-second period produce overlapping panes, so events are counted in multiple windows and totals are duplicated. It is tempting because the 10-second period appears to match the latency requirement, and it would be correct for computing moving averages rather than distinct 30-second groupings.
B
Use a trigger that fires after the end of the window
Why it fails: A trigger firing only at window end emits nothing during the 30-second window, so the required 10-second partial results never appear. It is tempting because end-of-window firing is the default behaviour, and it would be correct when complete, non-speculative results are wanted and latency is unimportant.
C
Use fixed windows of 30 seconds
Fixed 30-second windows partition the clickstream into non-overlapping intervals matching the grouping requirement. Combined with allowed lateness and early triggering, they satisfy the windowing constraint while the other settings handle late data and partial results.
D
Set allowed lateness to 5 minutes
Allowed lateness of 5 minutes lets the window retain its state and accept events arriving after the watermark passes, satisfying the stem's requirement to handle late data up to 5 minutes. Without it, late clickstream events would be dropped rather than incorporated into the 30-second window results.
E
Use a trigger with early firings every 10 seconds
Early firings emit speculative panes every 10 seconds before the 30-second window closes, satisfying the low-latency monitoring requirement without waiting for watermark completion. Late data arriving within the 5-minute allowance is still incorporated into subsequent firings, so partial results refresh rather than being discarded.

Question 3mediummulti select

A company is migrating an on-premises PostgreSQL database to Google Cloud. They need a fully managed database that is compatible with PostgreSQL and can handle both transactional and analytical workloads with high performance. Which two database services meet these requirements? (Choose TWO.)

Trap 1: Cloud Spanner

Spanner is not PostgreSQL-compatible; it uses GoogleSQL or PostgreSQL dialect but is a different engine.

Trap 2: BigQuery

BigQuery is not PostgreSQL-compatible and is purely analytical.

Trap 3: Firestore

Firestore is a NoSQL document database, not PostgreSQL-compatible.

A
Cloud Spanner
Why it fails: Spanner is not PostgreSQL-compatible; it uses GoogleSQL or PostgreSQL dialect but is a different engine.
B
Cloud SQL for PostgreSQL
Fully managed, PostgreSQL-compatible, supports OLTP and some analytical queries.
C
BigQuery
Why it fails: BigQuery is not PostgreSQL-compatible and is purely analytical.
D
AlloyDB
PostgreSQL-compatible, with columnar engine for fast analytics, designed for hybrid workloads.
E
Firestore
Why it fails: Firestore is a NoSQL document database, not PostgreSQL-compatible.

Question 4mediummulti select

You are building a data pipeline that ingests data from on-premises into Cloud Storage, then processes it with Dataproc, and finally loads into BigQuery. You need to schedule the pipeline to run daily. The pipeline must handle occasional failures gracefully. Which THREE Google Cloud services should you use together to achieve this? (Choose 3)

Trap 1: Dataflow

Dataflow is a managed Apache Beam runner for stream and batch transformation, not a scheduler or workflow orchestrator, so it cannot sequence the ingest-process-load steps or retry them on failure. It is tempting because it handles the processing stage itself, and would be correct if the pipeline needed autoscaling Beam transforms rather than daily orchestration.

Trap 2: Pub/Sub

Pub/Sub decouples event producers from consumers for streaming ingestion, but this batch pipeline needs scheduling and retry orchestration, which Cloud Composer and Cloud Scheduler provide. Pub/Sub would be tempting for real-time, event-driven ingestion rather than a daily batch load into BigQuery.

A
Cloud Storage
Cloud Storage serves as the ingestion landing zone for on-premises data before Dataproc processing, satisfying the pipeline's first stage. It provides durable, highly available object storage that decouples ingestion from compute, letting Dataproc read source files and write results without data loss during the daily scheduled runs.
B
Dataproc
Dataproc supplies the managed Spark and Hadoop processing layer that transforms the ingested data before loading into BigQuery. It satisfies the stem's processing requirement, running ephemeral clusters that can be retried on failure, while Cloud Scheduler triggers the daily run and Cloud Storage stages the on-premises data.
C
Cloud Composer
Cloud Composer orchestrates the daily pipeline as a directed acyclic graph, with retries and alerting on failed tasks to satisfy the graceful-failure requirement. Its Dataproc, BigQuery and Cloud Storage operators coordinate ingestion, processing and loading across services, which a single scheduler such as Cloud Scheduler cannot manage.
D
Dataflow
Why it fails: Dataflow is a managed Apache Beam runner for stream and batch transformation, not a scheduler or workflow orchestrator, so it cannot sequence the ingest-process-load steps or retry them on failure. It is tempting because it handles the processing stage itself, and would be correct if the pipeline needed autoscaling Beam transforms rather than daily orchestration.
E
Pub/Sub
Why it fails: Pub/Sub decouples event producers from consumers for streaming ingestion, but this batch pipeline needs scheduling and retry orchestration, which Cloud Composer and Cloud Scheduler provide. Pub/Sub would be tempting for real-time, event-driven ingestion rather than a daily batch load into BigQuery.

Question 5hardmultiple choice

A retail company ingests point-of-sale clickstream events into Cloud Pub/Sub at roughly 200,000 messages per second during flash sales. Analysts need near-real-time dashboards that aggregate revenue by product category over sliding 5-minute windows, with results visible in BigQuery within 30 seconds of the event. The pipeline must handle occasional bursts up to 3x the normal rate without dropping messages, and the team wants to minimize operational overhead. Which design should the data engineer use?

Trap 1: Use Cloud Pub/Sub push subscriptions delivering events directly to…

Cloud Run instances are stateless and cannot reliably maintain sliding-window state across concurrent instances or restarts. Aggregating in memory loses state during scale events, and per-minute inserts violate the 30-second visibility requirement while offering no exactly-once guarantees for deduplication during bursts.

Trap 2: Deploy a Dataproc cluster with Spark Structured Streaming reading…

Dataproc Spark Structured Streaming can read Pub/Sub only through the connector, but micro-batch latency and cluster sizing for a 3x burst require manual capacity planning and ongoing tuning. It does not natively autoscale to the burst as Dataflow does, and operating a cluster increases overhead, which the scenario explicitly asks to minimize.

Trap 3: Load raw events into Cloud Storage using a Pub/Sub subscription…

Scheduled queries over Cloud Storage files introduce at least a 5-minute delay plus file-arrival lag, so results will not appear within 30 seconds. This batch-oriented approach also lacks true sliding-window semantics and would reprocess or miss data depending on file visibility timing during the burst.

A
Use Cloud Pub/Sub push subscriptions delivering events directly to a Cloud Run service that aggregates in memory and calls the BigQuery streaming insert API once per minute.
Why it fails: Cloud Run instances are stateless and cannot reliably maintain sliding-window state across concurrent instances or restarts. Aggregating in memory loses state during scale events, and per-minute inserts violate the 30-second visibility requirement while offering no exactly-once guarantees for deduplication during bursts.
B
Deploy a Dataproc cluster with Spark Structured Streaming reading from Pub/Sub and writing micro-batches to BigQuery every 10 seconds, sizing the cluster for the 3x burst rate.
Why it fails: Dataproc Spark Structured Streaming can read Pub/Sub only through the connector, but micro-batch latency and cluster sizing for a 3x burst require manual capacity planning and ongoing tuning. It does not natively autoscale to the burst as Dataflow does, and operating a cluster increases overhead, which the scenario explicitly asks to minimize.
C
Create a Cloud Dataflow streaming pipeline in the Apache Beam Java SDK using Pub/Sub as the source, apply windowing with a 5-minute sliding window and a 30-second allowed lateness, and write aggregated results to BigQuery using the Storage Write API.
Dataflow provides exactly-once streaming semantics, autoscaling that absorbs the 3x burst, and native Pub/Sub and BigQuery connectors. Sliding windows with allowed lateness handle out-of-order events, and the Storage Write API gives low-latency, high-throughput BigQuery inserts, satisfying the 30-second visibility requirement without managing clusters.
D
Load raw events into Cloud Storage using a Pub/Sub subscription with a Cloud Storage sink, then run a BigQuery scheduled query every 5 minutes that reads the new files and computes the sliding window aggregates.
Why it fails: Scheduled queries over Cloud Storage files introduce at least a 5-minute delay plus file-arrival lag, so results will not appear within 30 seconds. This batch-oriented approach also lacks true sliding-window semantics and would reprocess or miss data depending on file visibility timing during the burst.

Question 6hardmultiple choice

A logistics company ingests GPS pings from delivery vans into Pub/Sub, and a Dataflow streaming pipeline writes them to BigQuery. Latency requirements are lenient (about 5 minutes), but the finance team needs the pipeline's cost to be predictable and low, and the data volume fluctuates by a factor of ten between day and night. The team wants to minimize per-element cost without losing data. Which configuration should the data engineer choose?

Trap 1: Batch mode with a 5-minute micro-batch schedule that reads Pub/Sub…

Beam batch mode does not natively consume Pub/Sub streams in a way that preserves ordering and exactly-once semantics across micro-batches, and snapshot/seek APIs add operational complexity. Batch also cannot react to traffic within the five-minute window, so latency becomes unbounded. The scenario asks for a streaming pipeline, so this design changes the ingestion contract.

Trap 2: Streaming Engine with autoscaling, a low --maxNumWorkers, and a…

A very low maximum worker count can cause backlog growth during daytime peaks, and Pub/Sub backlog plus subscription retention limits create a real risk of data loss or unprocessed elements. It also does not address the fluctuation cleanly, since the cap is fixed. The team needs elasticity in both directions, not just a smaller ceiling.

Trap 3: Streaming Engine with a fixed worker count sized for peak daytime…

Sizing workers for peak daytime load wastes money during the long low-volume night period, and the finance team explicitly wants predictable low cost. A fixed pool cannot shrink when traffic falls, so the pipeline pays for idle capacity for many hours each day. This fails the cost requirement even though it is technically straightforward.

A
Streaming Engine with autoscaling and a bounded maximum worker count, plus windowing with a 5-minute trigger so BigQuery writes are batched.
Autoscaling lets Dataflow add workers during the daytime surge and shrink at night, matching cost to load while a bounded maximum keeps spend predictable. Windowing with a five-minute trigger batches BigQuery writes, which reduces per-row streaming insert overhead and cost. This satisfies the lenient latency target while keeping finance's cost model stable and avoiding data loss.
B
Batch mode with a 5-minute micro-batch schedule that reads Pub/Sub snapshots and writes to BigQuery.
Why it fails: Beam batch mode does not natively consume Pub/Sub streams in a way that preserves ordering and exactly-once semantics across micro-batches, and snapshot/seek APIs add operational complexity. Batch also cannot react to traffic within the five-minute window, so latency becomes unbounded. The scenario asks for a streaming pipeline, so this design changes the ingestion contract.
C
Streaming Engine with autoscaling, a low --maxNumWorkers, and a streaming trigger that emits results at least every 5 minutes.
Why it fails: A very low maximum worker count can cause backlog growth during daytime peaks, and Pub/Sub backlog plus subscription retention limits create a real risk of data loss or unprocessed elements. It also does not address the fluctuation cleanly, since the cap is fixed. The team needs elasticity in both directions, not just a smaller ceiling.
D
Streaming Engine with a fixed worker count sized for peak daytime load and --maxNumWorkers capped at that value.
Why it fails: Sizing workers for peak daytime load wastes money during the long low-volume night period, and the finance team explicitly wants predictable low cost. A fixed pool cannot shrink when traffic falls, so the pipeline pays for idle capacity for many hours each day. This fails the cost requirement even though it is technically straightforward.

Question 7easymultiple choice

A company is designing a streaming data pipeline to process real-time clickstream events. They need to aggregate events by session window with a 5-minute gap and enable exactly-once processing semantics. Which Google Cloud service should they use?

Trap 1: Cloud Pub/Sub with Cloud Functions

Cloud Functions offers no session-window aggregation with a five-minute gap and no exactly-once guarantees across retries. It is tempting because Pub/Sub plus Functions is a common lightweight event pipeline, and would be correct for simple per-event processing where at-least-once delivery and stateless handling suffice.

Trap 2: Cloud Dataproc with Spark Streaming

Spark Streaming's micro-batch model gives at-least-once semantics by default, and Dataproc requires cluster management for a continuous pipeline. It is tempting because Spark supports session windows, and would be correct for batch-oriented analytics on existing Hadoop workloads rather than a managed exactly-once streaming service.

Trap 3: Cloud Bigtable with Dataflow templates

Bigtable stores and serves data but performs no windowing; Dataflow templates cannot express session windows with a five-minute gap or exactly-once state. It is tempting because Bigtable suits high-throughput clickstream storage, and would be correct as the serving layer beneath a pipeline rather than the processing engine itself.

A
Cloud Pub/Sub with Cloud Functions
Why it fails: Cloud Functions offers no session-window aggregation with a five-minute gap and no exactly-once guarantees across retries. It is tempting because Pub/Sub plus Functions is a common lightweight event pipeline, and would be correct for simple per-event processing where at-least-once delivery and stateless handling suffice.
B
Cloud Dataflow with Apache Beam
Cloud Dataflow with Apache Beam satisfies both constraints: Beam's session windows natively aggregate events separated by a 5-minute gap, and Dataflow's exactly-once processing guarantees deduplicate records end-to-end. Pub/Sub alone lacks windowing, while Dataproc requires manual checkpointing, so neither meets the stated semantics.
C
Cloud Dataproc with Spark Streaming
Why it fails: Spark Streaming's micro-batch model gives at-least-once semantics by default, and Dataproc requires cluster management for a continuous pipeline. It is tempting because Spark supports session windows, and would be correct for batch-oriented analytics on existing Hadoop workloads rather than a managed exactly-once streaming service.
D
Cloud Bigtable with Dataflow templates
Why it fails: Bigtable stores and serves data but performs no windowing; Dataflow templates cannot express session windows with a five-minute gap or exactly-once state. It is tempting because Bigtable suits high-throughput clickstream storage, and would be correct as the serving layer beneath a pipeline rather than the processing engine itself.

Question 8hardmultiple choice

A data engineer manages a Cloud Composer 2 environment. A DAG that downloads a large reference dataset each night occasionally exceeds the default task timeout because the source API is slow. The engineer wants the task to fail fast and be retried automatically rather than hanging for hours, and wants failed runs to be visible for alerting. Which configuration should be applied to the task?

Trap 1: Configure the worker's celery_worker_autoscale setting to add more…

Adding workers increases parallel capacity but does not make a single slow external API call complete faster, nor does it impose a timeout. Autoscaling addresses queue throughput for many tasks, not the wall-clock duration of one task waiting on a remote service. This leaves the hanging task unchanged and adds cost without solving the stated issue.

Trap 2: Set depends_on_past to true and add a retry_exponential_backoff so…

depends_on_past serializes runs relative to the previous run's success, which would delay rather than bound execution. retry_exponential_backoff spaces out retries but only applies after a failure has already been recorded, and it does not impose a maximum task duration. Combined, these settings do not make a hung task fail fast, so they miss the core requirement.

Trap 3: Increase the DAG's schedule interval and add a max_active_runs…

max_active_runs controls concurrency between DAG runs, not the duration of a single task. Adjusting the schedule interval changes how often the DAG is triggered but does nothing to bound a slow API call. Neither setting causes a hung task to fail fast or be retried, so this does not address the timeout problem described.

A
Configure the worker's celery_worker_autoscale setting to add more workers so the slow API call finishes sooner.
Why it fails: Adding workers increases parallel capacity but does not make a single slow external API call complete faster, nor does it impose a timeout. Autoscaling addresses queue throughput for many tasks, not the wall-clock duration of one task waiting on a remote service. This leaves the hanging task unchanged and adds cost without solving the stated issue.
B
Set depends_on_past to true and add a retry_exponential_backoff so the task waits for the prior run before starting.
Why it fails: depends_on_past serializes runs relative to the previous run's success, which would delay rather than bound execution. retry_exponential_backoff spaces out retries but only applies after a failure has already been recorded, and it does not impose a maximum task duration. Combined, these settings do not make a hung task fail fast, so they miss the core requirement.
C
Increase the DAG's schedule interval and add a max_active_runs limit of one so overlapping runs cannot occur.
Why it fails: max_active_runs controls concurrency between DAG runs, not the duration of a single task. Adjusting the schedule interval changes how often the DAG is triggered but does nothing to bound a slow API call. Neither setting causes a hung task to fail fast or be retried, so this does not address the timeout problem described.
D
Set execution_timeout on the task to a bounded value and set retries with a retry_delay so failed attempts are re-queued and surfaced in task state.
execution_timeout caps how long a task may run before Airflow marks it failed, and the retries plus retry_delay settings cause the scheduler to re-queue it automatically. The failure state is recorded and available for alerting, which matches the requirement to fail fast, retry, and remain visible.

Question 9easymultiple choice

You need to schedule a Dataproc Spark job to run at 2 AM every day, and upon completion, trigger a BigQuery load job. Which Cloud Composer operator should you use to run the Spark job?

Trap 1: DataflowPythonOperator

DataflowPythonOperator launches Apache Beam pipelines on Dataflow, not Spark jobs on Dataproc. It is tempting because both are managed data-processing services, and it would be correct when the workload is a Beam pipeline rather than a Spark application.

Trap 2: BigQueryOperator

BigQueryOperator runs BigQuery jobs such as load or query tasks; it cannot submit a Spark job to a Dataproc cluster. It is tempting because the stem's downstream step is a BigQuery load, and it would be correct for that second task, not the Spark submission.

Trap 3: DataprocClusterCreateOperator

DataprocClusterCreateOperator only provisions a cluster; it neither submits the Spark job nor triggers the downstream BigQuery load, so the 2 AM schedule would create idle clusters. It is tempting because cluster creation is a genuine prerequisite step, and it would be correct within a workflow that first builds a cluster before a DataprocSubmitJobOperator runs the job.

A
DataflowPythonOperator
Why it fails: DataflowPythonOperator launches Apache Beam pipelines on Dataflow, not Spark jobs on Dataproc. It is tempting because both are managed data-processing services, and it would be correct when the workload is a Beam pipeline rather than a Spark application.
B
BigQueryOperator
Why it fails: BigQueryOperator runs BigQuery jobs such as load or query tasks; it cannot submit a Spark job to a Dataproc cluster. It is tempting because the stem's downstream step is a BigQuery load, and it would be correct for that second task, not the Spark submission.
C
DataprocClusterCreateOperator
Why it fails: DataprocClusterCreateOperator only provisions a cluster; it neither submits the Spark job nor triggers the downstream BigQuery load, so the 2 AM schedule would create idle clusters. It is tempting because cluster creation is a genuine prerequisite step, and it would be correct within a workflow that first builds a cluster before a DataprocSubmitJobOperator runs the job.
D
DataprocSubmitJobOperator
DataprocSubmitJobOperator submits a Spark job to a Dataproc cluster and waits for completion, so it fits the scheduled 2 AM run. Its downstream task can then trigger the BigQuery load job within the same DAG.

Question 10mediummultiple choice

A company uses Cloud Spanner and needs to store a parent-child relationship where the child table is frequently queried together with the parent. The parent has millions of rows and the child billions. Which Spanner feature optimizes performance for this pattern?

Trap 1: Partitioned tables

Partitioned tables split large tables into smaller segments for manageability and query pruning on partition keys, but they do not co-locate child rows with their parent. Interleaved tables physically store child rows adjacent to the parent key, which is what this parent-child query pattern requires.

Trap 2: Secondary indexes

Secondary indexes accelerate lookups on non-primary-key columns; they do not co-locate child rows with their parent. Interleaving is the feature that physically stores child rows adjacent to the parent key, which is correct when parent and child are queried together.

Trap 3: Change streams

Change streams emit a log of row-level data modifications for downstream consumption; they do not affect how child rows are stored or read alongside parents. Change streams are correct when the requirement is capturing and streaming database changes to other systems.

A
Partitioned tables
Why it fails: Partitioned tables split large tables into smaller segments for manageability and query pruning on partition keys, but they do not co-locate child rows with their parent. Interleaved tables physically store child rows adjacent to the parent key, which is what this parent-child query pattern requires.
B
Interleaved tables
Interleaved tables physically co-locate child rows with their parent row in the same storage split, so parent-child joins avoid network hops. This satisfies the pattern where the child table is queried alongside its parent, since Spanner reads both from one locality rather than performing distributed lookups.
C
Secondary indexes
Why it fails: Secondary indexes accelerate lookups on non-primary-key columns; they do not co-locate child rows with their parent. Interleaving is the feature that physically stores child rows adjacent to the parent key, which is correct when parent and child are queried together.
D
Change streams
Why it fails: Change streams emit a log of row-level data modifications for downstream consumption; they do not affect how child rows are stored or read alongside parents. Change streams are correct when the requirement is capturing and streaming database changes to other systems.

Question 11mediummulti select

A company needs to stream real-time user activity data from their application into BigQuery for immediate dashboarding. They want to minimize latency (under 5 seconds) and ensure exactly-once delivery. Which TWO options should they consider? (Choose 2)

Trap 1: Use Cloud Functions to receive events and call the BigQuery REST API

Invoking the BigQuery REST API from Cloud Functions uses streaming inserts, which provide at-least-once delivery, so duplicate rows remain possible and exactly-once is unmet. It is tempting because Cloud Functions offers simple event-driven ingestion for low-volume workloads where occasional duplicates are tolerable.

Trap 2: Use BigQuery legacy streaming inserts directly from the application

Legacy streaming inserts offer at-least-once semantics, so duplicate rows can appear and exactly-once delivery is not guaranteed, failing the stated requirement. It is tempting because inserts give low-latency row-level availability, making them suitable when approximate results and immediate dashboarding matter more than deduplication.

Trap 3: Use Apache Kafka on Dataproc and write to BigQuery via the BigQuery…

Kafka on Dataproc with the BigQuery Kafka connector provides at-least-once delivery by default, so exactly-once is not guaranteed without extra deduplication. It is tempting because Kafka handles high-throughput streaming well, and it is correct when the requirement is durable ordered streaming rather than exactly-once BigQuery writes.

A
Use Cloud Functions to receive events and call the BigQuery REST API
Why it fails: Invoking the BigQuery REST API from Cloud Functions uses streaming inserts, which provide at-least-once delivery, so duplicate rows remain possible and exactly-once is unmet. It is tempting because Cloud Functions offers simple event-driven ingestion for low-volume workloads where occasional duplicates are tolerable.
B
Use BigQuery Storage Write API in committed mode
BigQuery Storage Write API in committed mode provides exactly-once semantics through stream-level offsets, so duplicate records are discarded on retry. It supports sub-second, real-time ingestion directly into BigQuery, satisfying the under-five-second latency requirement without intermediate staging. This makes it ideal for streaming live user activity into dashboards.
C
Use BigQuery legacy streaming inserts directly from the application
Why it fails: Legacy streaming inserts offer at-least-once semantics, so duplicate rows can appear and exactly-once delivery is not guaranteed, failing the stated requirement. It is tempting because inserts give low-latency row-level availability, making them suitable when approximate results and immediate dashboarding matter more than deduplication.
D
Use Apache Kafka on Dataproc and write to BigQuery via the BigQuery Kafka connector
Why it fails: Kafka on Dataproc with the BigQuery Kafka connector provides at-least-once delivery by default, so exactly-once is not guaranteed without extra deduplication. It is tempting because Kafka handles high-throughput streaming well, and it is correct when the requirement is durable ordered streaming rather than exactly-once BigQuery writes.
E
Stream data to Pub/Sub, then use Dataflow to write to BigQuery with exactly-once guarantees
Pub/Sub decouples ingestion from processing, while Dataflow's streaming engine supports exactly-once semantics when writing to BigQuery, satisfying the stem's delivery constraint. Its low-latency pipeline keeps end-to-end delay under five seconds, unlike batch loads, making it suitable for immediate dashboarding of real-time activity.

Question 12mediummulti select

You want to optimize BigQuery costs for a large dataset that is frequently queried by time range. You also need to ensure that predictable workloads have dedicated slot capacity. Which TWO strategies should you combine? (Choose 2)

Trap 1: Use query caching

Query caching only reuses identical repeated results, so it cannot reserve dedicated slot capacity for predictable workloads; that requires committed-use or reservations. It is tempting because caching genuinely cuts bytes billed for repeated identical queries, and would be the right cost lever in a workload dominated by duplicate queries.

Trap 2: Create a materialized view for the entire table

A materialised view spanning the entire table precomputes all rows, incurring full storage and refresh costs without exploiting the time-range filter pattern. Partitioning by the time column prunes scanned data for range queries; materialised views suit repeated aggregations over stable subsets, not whole-table scans.

Trap 3: Enable autoscaling slots

Autoscaling slots add capacity dynamically as demand rises, which suits unpredictable or spiky query workloads. Predictable workloads needing dedicated capacity require committed-use or reservations instead, since autoscaling provides no guaranteed slot allocation and cannot satisfy that requirement.

A
Use query caching
Why it fails: Query caching only reuses identical repeated results, so it cannot reserve dedicated slot capacity for predictable workloads; that requires committed-use or reservations. It is tempting because caching genuinely cuts bytes billed for repeated identical queries, and would be the right cost lever in a workload dominated by duplicate queries.
B
Partition the table by date
Partitioning by date restricts each query to the relevant date range, so BigQuery scans only matching partitions rather than the whole table. This directly cuts bytes processed for time-range queries, satisfying the cost-optimisation requirement in the stem.
C
Purchase committed use reservations for baseline capacity
Committed use reservations provide dedicated slot capacity at a discounted rate for predictable baseline workloads, satisfying the requirement for guaranteed capacity. This complements partitioning, which reduces bytes scanned by limiting queries to relevant date ranges.
D
Create a materialized view for the entire table
Why it fails: A materialised view spanning the entire table precomputes all rows, incurring full storage and refresh costs without exploiting the time-range filter pattern. Partitioning by the time column prunes scanned data for range queries; materialised views suit repeated aggregations over stable subsets, not whole-table scans.
E
Enable autoscaling slots
Why it fails: Autoscaling slots add capacity dynamically as demand rises, which suits unpredictable or spiky query workloads. Predictable workloads needing dedicated capacity require committed-use or reservations instead, since autoscaling provides no guaranteed slot allocation and cannot satisfy that requirement.

Question 13hardmulti select

You are designing a data pipeline for ML training with Vertex AI. You need to split time-series data into train/validation/test sets without leaking future data. Which THREE practices should you follow?

Trap 1: Use Looker to generate the splits automatically.

Looker is a business intelligence and dashboarding product with no role in generating ML dataset splits, so it cannot enforce temporal ordering. It is tempting because it sits within Google Cloud's analytics portfolio, and it would be correct if the requirement were visualising pipeline metrics rather than partitioning time-series data.

Trap 2: Randomly assign rows to each split to ensure statistical…

Random row assignment lets future observations enter training, leaking information across the temporal boundary and inflating validation scores. It is tempting because random splitting is standard for independent, identically distributed tabular data, and it would be correct if the records had no time ordering.

A
Use a sliding window validation approach for hyperparameter tuning.
Sliding-window validation trains on an expanding or rolling past window and validates on the immediately following period, so every fold respects chronological order. This directly prevents future leakage during hyperparameter tuning, satisfying the stem's requirement to split time-series data without exposing the model to later observations.
B
Ensure that all data points for a given time period are in the same split.
Grouping all records from one timestamp or period into a single split prevents the same moment appearing in both training and validation. This preserves temporal integrity, since leakage often arises when contemporaneous rows straddle splits, directly satisfying the stem's constraint against future data contaminating training.
C
Use Looker to generate the splits automatically.
Why it fails: Looker is a business intelligence and dashboarding product with no role in generating ML dataset splits, so it cannot enforce temporal ordering. It is tempting because it sits within Google Cloud's analytics portfolio, and it would be correct if the requirement were visualising pipeline metrics rather than partitioning time-series data.
D
Randomly assign rows to each split to ensure statistical distribution.
Why it fails: Random row assignment lets future observations enter training, leaking information across the temporal boundary and inflating validation scores. It is tempting because random splitting is standard for independent, identically distributed tabular data, and it would be correct if the records had no time ordering.
E
Use a date column to define the split boundaries.
A date column provides the explicit chronological ordering needed to place split boundaries at points in time rather than at random rows. This guarantees training data precedes validation and test data, satisfying the stem's requirement to avoid leaking future observations into earlier splits.

Question 14mediummultiple choice

You are building a binary classification model using AutoML Tables on Vertex AI. The dataset has a severe class imbalance (1% positive class). Which strategy should you use to handle the imbalance?

Trap 1: Oversample the minority class using SMOTE before training.

SMOTE is not directly integrated with AutoML Tables; you would need to preprocess separately, but AutoML can handle imbalance internally.

Trap 2: Do nothing; AutoML Tables automatically handles class imbalance.

AutoML Tables does apply some techniques but setting class_weight explicitly gives better control.

Trap 3: Downsample the majority class to match the minority class size.

Downsampling can discard valuable data and may reduce model performance; AutoML handles imbalance better.

A
Oversample the minority class using SMOTE before training.
Why it fails: SMOTE is not directly integrated with AutoML Tables; you would need to preprocess separately, but AutoML can handle imbalance internally.
B
Do nothing; AutoML Tables automatically handles class imbalance.
Why it fails: AutoML Tables does apply some techniques but setting class_weight explicitly gives better control.
C
Downsample the majority class to match the minority class size.
Why it fails: Downsampling can discard valuable data and may reduce model performance; AutoML handles imbalance better.
D
Use a weight column to assign higher weights to the minority class.
AutoML Tables allows setting class weights to address imbalance; it will adjust the loss function accordingly.

Question 15hardmulti select

You are building a Dataflow pipeline that reads from Pub/Sub, applies transformations, and writes to BigQuery. The pipeline must handle late-arriving data and ensure that the windowing and triggering are correct. Which THREE configurations should you consider? (Choose 3)

Trap 1: Enable Dataflow Streaming Engine for exactly-once processing.

Streaming Engine offloads pipeline execution to the Dataflow service and improves autoscaling and latency, but exactly-once correctness comes from the runner's checkpointing and Pub/Sub acknowledgement model, not from enabling it. It is tempting because the feature is often recommended for streaming pipelines, yet it is not a windowing or triggering configuration.

Trap 2: Use side inputs to enrich streaming data with static data.

Side inputs join a streaming pipeline against a slowly changing reference dataset; they do not address windowing, triggers, or allowed lateness. The option is tempting because enrichment is a common streaming need, but the question asks specifically about handling late data and correct windowing semantics.

A
Enable Dataflow Streaming Engine for exactly-once processing.
Why it fails: Streaming Engine offloads pipeline execution to the Dataflow service and improves autoscaling and latency, but exactly-once correctness comes from the runner's checkpointing and Pub/Sub acknowledgement model, not from enabling it. It is tempting because the feature is often recommended for streaming pipelines, yet it is not a windowing or triggering configuration.
B
Use side inputs to enrich streaming data with static data.
Why it fails: Side inputs join a streaming pipeline against a slowly changing reference dataset; they do not address windowing, triggers, or allowed lateness. The option is tempting because enrichment is a common streaming need, but the question asks specifically about handling late data and correct windowing semantics.
C
Use the BigQuery Storage Write API with committed mode to ensure exactly-once writes.
The BigQuery Storage Write API's committed mode provides exactly-once semantics through write-stream offsets, preventing duplicate rows when retries occur after late data triggers additional pane firings. This satisfies the pipeline's requirement for correct windowing and triggering, since speculative or repeated emissions from allowed lateness would otherwise insert duplicates into BigQuery.
D
Set an allowed lateness duration to handle late-arriving data.
Setting an allowed lateness duration lets the pipeline retain window state past the watermark, so late-arriving records are still assigned to their correct window and trigger a refined pane rather than being dropped. This directly satisfies the stem's requirement to handle late data while keeping windowing and triggering correct.
E
Configure a triggering frequency to control how often results are emitted.
Configuring a triggering frequency controls when pane results are emitted from a window, satisfying the requirement to handle late-arriving data. After-watermark triggers emit once the watermark passes the window end, while repeated early or late triggers emit speculative panes, letting downstream BigQuery writes reflect updates as further late elements arrive.

Question 16mediummulti select

A company wants to build a reporting pipeline where data is collected from IoT devices, stored raw in Cloud Storage, and then processed into BigQuery for analytics. They need to ensure data is encrypted at rest using customer-managed keys. Which THREE steps should they take? (Choose 3 correct options)

Trap 1: Delete the Cloud KMS key after data is loaded to BigQuery

Deleting the Cloud KMS key destroys the key material, making the CMEK-encrypted data permanently unreadable and unrecoverable. Key deletion is a deliberate crypto-shredding action, not a pipeline step. Retaining the key is required so BigQuery can decrypt data at query time; deletion would be the choice only when intentionally destroying all associated data.

Trap 2: Use Google-managed encryption keys

Google-managed encryption keys are the default at-rest protection and give the customer no control over key rotation, access or revocation, so they fail the customer-managed key requirement. They are the correct choice when the organisation has no key-management obligation and simply wants encryption without operational overhead.

A
Delete the Cloud KMS key after data is loaded to BigQuery
Why it fails: Deleting the Cloud KMS key destroys the key material, making the CMEK-encrypted data permanently unreadable and unrecoverable. Key deletion is a deliberate crypto-shredding action, not a pipeline step. Retaining the key is required so BigQuery can decrypt data at query time; deletion would be the choice only when intentionally destroying all associated data.
B
Enable CMEK on the Cloud Storage bucket by specifying the KMS key
Specifying a Cloud KMS key on the Cloud Storage bucket enforces customer-managed encryption at rest for the raw IoT data, satisfying the stem's CMEK constraint at the storage layer. Without this, Google-managed keys apply by default, so the raw landing zone would fail the customer-managed key requirement before BigQuery processing begins.
C
Configure the BigQuery dataset to use a CMEK key
Configuring the BigQuery dataset with a customer-managed encryption key (CMEK) ensures the processed analytics data is encrypted at rest under your own Cloud KMS key, satisfying the stem's customer-managed key requirement for the BigQuery stage of the pipeline.
D
Use Google-managed encryption keys
Why it fails: Google-managed encryption keys are the default at-rest protection and give the customer no control over key rotation, access or revocation, so they fail the customer-managed key requirement. They are the correct choice when the organisation has no key-management obligation and simply wants encryption without operational overhead.
E
Create a key ring and key in Cloud Key Management Service
Cloud KMS key rings and keys provide the customer-managed encryption keys (CMEK) that satisfy the stem's encryption-at-rest requirement. Creating the key here is the prerequisite step: without a key ring and key, no CMEK configuration can be applied to Cloud Storage buckets or BigQuery datasets.

Question 17hardmultiple choice

A company's Dataflow pipeline uses the PubSubIO source to read messages and writes to BigQuery via the BigQueryIO sink. The pipeline is running in Streaming mode with exactly-once semantics enabled. Occasionally, duplicate rows appear in BigQuery. What is the most likely reason?

Trap 1: The pipeline is using the WriteResult method with WRITE_APPEND in…

The stem specifies streaming mode with exactly-once semantics, so batch WRITE_APPEND retry behaviour is not the mechanism at play. It is tempting because WRITE_APPEND genuinely can duplicate rows under retries in batch pipelines, making it the right answer when exactly-once is disabled or the job runs in batch.

Trap 2: The pipeline is experiencing the 'dataflow streaming log…

No such Dataflow bug exists; streaming log processing is a normal pipeline stage, not a duplication source. It is tempting because vague platform defects can appear to explain intermittent symptoms, but the actual cause lies in BigQueryIO's insertId-based deduplication window or Pub/Sub redelivery, not a fabricated logging fault.

Trap 3: The PubSubIO source is configured with a dead-letter queue and…

Dead-letter queues receive messages that repeatedly fail processing; they do not cause redelivery of successfully processed messages into the main subscription. It is tempting because DLQs do involve redelivery mechanics, and would be relevant when diagnosing poison messages, but Pub/Sub at-least-once redelivery is the actual duplication source here.

A
The user-provided record ID for deduplication in BigQuery's streaming inserts is not being set for all messages, leading to duplicate rows.
BigQueryIO's streaming exactly-once deduplication relies on a deterministic record ID per message; when that ID is absent or non-unique, BigQuery cannot deduplicate retried inserts, so duplicates appear. The pipeline's exactly-once guarantee depends on this ID being set for every message.
B
The pipeline is using the WriteResult method with WRITE_APPEND in batch mode, which can cause duplicates if retries happen.
Why it fails: The stem specifies streaming mode with exactly-once semantics, so batch WRITE_APPEND retry behaviour is not the mechanism at play. It is tempting because WRITE_APPEND genuinely can duplicate rows under retries in batch pipelines, making it the right answer when exactly-once is disabled or the job runs in batch.
C
The pipeline is experiencing the 'dataflow streaming log processing' bug, causing duplicate logs to be written.
Why it fails: No such Dataflow bug exists; streaming log processing is a normal pipeline stage, not a duplication source. It is tempting because vague platform defects can appear to explain intermittent symptoms, but the actual cause lies in BigQueryIO's insertId-based deduplication window or Pub/Sub redelivery, not a fabricated logging fault.
D
The PubSubIO source is configured with a dead-letter queue and messages are being redelivered without proper deduplication.
Why it fails: Dead-letter queues receive messages that repeatedly fail processing; they do not cause redelivery of successfully processed messages into the main subscription. It is tempting because DLQs do involve redelivery mechanics, and would be relevant when diagnosing poison messages, but Pub/Sub at-least-once redelivery is the actual duplication source here.