Task 2.2: Building the pipelines.

• ASession windows keyed by user, with a 30-minute gap duration

  Correct

  Session windows are assigned per key and close after the gap with no data, so each window is one visit of one user, whatever its length.

• BFixed 30-minute windows keyed by user, with the counts summed later

  Incorrect

  Fixed windows cut time into equal slices, so one visit is split across several windows and two visits can share one.

• CSliding 30-minute windows that start each minute, keyed by user

  Incorrect

  Sliding windows overlap, so each click is counted in many windows; they suit running averages, not visits.

• DA global window keyed by user with a 30-minute processing-time trigger

  Incorrect

  A processing-time trigger fires on the clock, not on gaps in the user's activity, so its panes do not line up with visits.

Apache Beam offers fixed (tumbling), sliding (hopping) and session windows. Session windows group a key's elements by activity: a new window starts when data arrives after the gap duration, which matches the definition of a visit.

• ARaise the maximum number of workers so that autoscaling can add more

  Incorrect

  The extra workers sit idle: the fused stage keeps the parsing tied to the few workers that read the 20 input elements.

• BRun the job with FlexRS so that it uses preemptible and standard VMs

  Incorrect

  FlexRS lowers the price of a delay-tolerant batch job; it does not change how work is distributed inside a fused stage.

• CAdd a Reshuffle transform after the step that expands the prefixes

  Correct

  A Reshuffle prevents fusion of the surrounding transforms, so the millions of object names are redistributed across all workers before parsing.

• DTurn on Dataflow Shuffle so that grouping runs in the service backend

  Incorrect

  Dataflow Shuffle moves shuffle operations off the workers, but this pipeline has no grouping step; the problem is fusion.

Dataflow fuses consecutive steps into one stage to save work. When a step turns a few elements into a huge number, fusion keeps the downstream work on the workers that handled the few inputs. Breaking fusion with a Reshuffle (or a side input or grouping) lets Dataflow spread the expanded elements across the workers it has.

• ASTORAGE_API_AT_LEAST_ONCE, which writes to the default stream

  Incorrect

  This method is cheaper and lower latency, but it gives at-least-once semantics, so duplicate rows are possible.

• BSTORAGE_WRITE_API, with a triggering frequency for the stream

  Correct

  STORAGE_WRITE_API writes through the Storage Write API with exactly-once semantics, and rows become available as each commit lands.

• CFILE_LOADS, with a triggering frequency of a few seconds

  Incorrect

  File loads stage files and run load jobs, which delays availability and would run into load job limits at a seconds-level frequency.

• DSTREAMING_INSERTS, with insert IDs for deduplication

  Incorrect

  Legacy streaming inserts use best-effort deduplication, which can still leave duplicates, and Google does not recommend them for new projects.

BigQueryIO offers four write methods. Only STORAGE_WRITE_API guarantees exactly-once writes; STORAGE_API_AT_LEAST_ONCE trades that for lower cost and latency, FILE_LOADS is batch-oriented, and STREAMING_INSERTS is the legacy best-effort path.

• AA push subscription to the Cloud Run URL with exactly-once delivery on

  Incorrect

  Push and export subscriptions do not support exactly-once delivery; only pull subscriptions do.

• BMessage ordering, with the order ID as the ordering key

  Incorrect

  Ordering keys control the order of delivery within a key; they do not prevent a message from being delivered again.

• CA dead-letter topic with one delivery attempt, so that nothing is retried

  Incorrect

  The maximum delivery attempts for a dead-letter policy is at least 5, and dead-lettering handles messages that are not acknowledged, not redelivery after an acknowledgment.

• DA pull subscription with exactly-once delivery and checked acknowledgments

  Correct

  With exactly-once delivery on a pull subscription, no redelivery happens after a successful acknowledgment; the subscriber uses acknowledge with response to know when an ack failed.

Pub/Sub exactly-once delivery is a subscription property for pull subscriptions, and the guarantee holds when subscribers connect in the same region. It removes redelivery after a successful acknowledgment; it does not remove duplicates created by publishers, which is why the stem rules those out.

• AUse Combine.perKey with withHotKeyFanout on the aggregation

  Correct

  Hot key fanout pre-combines a hot key's values on several workers before the final combine, which is the remedy Google lists for this message.

• BReplace the combine with GroupByKey followed by a summing ParDo

  Incorrect

  GroupByKey sends every value of the hot key to one worker before summing, which makes the skew worse rather than better.

• CMove the job to a worker machine type with more vCPUs and memory

  Incorrect

  A bigger machine makes the one straggling worker somewhat faster but leaves all of the hot key's work on it.

• DAdd a Reshuffle transform before the combine to spread out the rows

  Incorrect

  Reshuffle redistributes elements across workers, but the combine still groups by advertiser, so the hot key ends up on one worker again.

A hot key limits parallelism because every value for that key is processed together. Dataflow's guidance for the "hot key detected" message is to check the data distribution, rekey it, or use Combine.PerKey.withHotKeyFanout in Java (CombinePerKey.with_hot_key_fanout in Python), which spreads the partial aggregation of the key over several workers.

• ASet the dataset's time travel window to seven days for replaying deletes

  Incorrect

  Time travel lets queries read earlier table states; it does not make BigQuery apply incoming changes.

• BSend the changes through the legacy tabledata.insertAll API instead

  Incorrect

  CDC ingestion works through the Storage Write API default stream; the legacy streaming API is the wrong path.

• CDeclare a primary key on the destination table

  Correct

  BigQuery uses the table's primary key, up to 16 columns, to match each change to the row it upserts or deletes.

• DSet require_partition_filter to true on the destination table

  Incorrect

  Requiring a partition filter is not part of CDC, and tables that use runtime merges cannot use requirePartitionFilter.

• ESet the _CHANGE_TYPE pseudocolumn to UPSERT or DELETE on each row

  Correct

  _CHANGE_TYPE tells BigQuery whether each streamed row is an upsert or a delete; it accepts only those two values.

BigQuery change data capture ingestion applies streamed changes to a table for you. The table needs a declared primary key, and each row written through the Storage Write API default stream carries the _CHANGE_TYPE pseudocolumn, optionally with _CHANGE_SEQUENCE_NUMBER for custom ordering.

• AA JavaScript UDF that loads the library's code from a Cloud Storage path

  Incorrect

  JavaScript UDFs can load JavaScript libraries from Cloud Storage, but they cannot run a Python library.

• BAn imported BigQuery ML model that wraps the library's logic in ONNX

  Incorrect

  Imported models run trained ML models in formats such as ONNX; an address library with a reference file is not a model that can be imported.

• CA remote function backed by a Cloud Run service that runs the library

  Correct

  Remote functions call code on Cloud Run or Cloud Run functions from GoogleSQL like any UDF, in any supported language, with rows sent in batches.

• DA stored procedure for Apache Spark that runs the library on the table

  Incorrect

  A Spark stored procedure is invoked with CALL as its own statement; it cannot be used as an expression inside a SELECT.

When logic needs a language or library that SQL and JavaScript UDFs cannot provide, a remote function puts it behind an HTTP endpoint on Cloud Run and exposes it to GoogleSQL through a Cloud resource connection. BigQuery sends rows in batches, so the endpoint should handle a batch per request.

• ASwitch the windows from fixed one-hour windows to session windows

  Incorrect

  The window type is not the problem; the elements carry the wrong timestamps, so any window groups them by publish time.

• BOrder the messages with the sensor ID as the ordering key

  Incorrect

  Ordering affects delivery order, not the timestamps that windows use, and Google does not recommend ordering with Dataflow.

• CUse a processing-time trigger that fires once per hour

  Incorrect

  Processing time is when the pipeline handles the data, which is even further from when the reading was taken.

• DRead the event timestamp from the reading_ts attribute in the connector

  Correct

  The Pub/Sub connector can take each element's timestamp from a message attribute and use it for watermarking, so windows group readings by when they happened.

By default the Pub/Sub connector uses the message publish time as the event timestamp. When the true event time travels in an attribute, configuring the connector to read it makes windowing and watermarks follow event time, which is what late-published sensor data needs.

• AQUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1

  Correct

  Numbering the rows of each customer by recency and keeping row 1 returns exactly the newest version of every customer.

• BSELECT DISTINCT over the full set of columns in the raw table

  Incorrect

  Versions differ in at least updated_at, so DISTINCT keeps all of them.

• CGROUP BY customer_id with MAX(updated_at) as the second output column

  Incorrect

  This returns the latest timestamp but none of the other columns of that version, so a join back would still be needed.

• DAPPROX_TOP_COUNT(customer_id, 1) computed over the whole raw table

  Incorrect

  APPROX_TOP_COUNT returns the most frequent values approximately; it does not pick each customer's latest row.

Deduplicating to the latest record per key is a window-function task. The QUALIFY clause filters on the result of ROW_NUMBER() partitioned by the key and ordered by the version timestamp, in a single query.

• AA streaming Dataflow job that polls the API inside a DoFn

  Incorrect

  A streaming job runs workers around the clock to make one call an hour.

• BA Managed Service for Apache Spark cluster with a cron job on it

  Incorrect

  A cluster is far more infrastructure than one small hourly call needs, and it costs money while idle.

• CA Cloud Run job that fetches the data, triggered on a schedule

  Correct

  A Cloud Run job runs a container to completion and can be executed on a schedule, so nothing runs or bills between calls.

• DA Compute Engine VM with a crontab entry for the script

  Incorrect

  A VM runs and is paid for all the time and must be patched for a task that takes minutes an hour.

Not every source needs a pipeline engine. A small periodic pull is a batch task that runs to completion: Cloud Run jobs execute a container until it finishes and can run on a schedule, which leaves nothing running between executions.

• AA second topic, with the publishers changed to publish payments to it

  Incorrect

  A new topic works but changes every publisher, which a filter makes unnecessary.

• BA filter on the fraud subscription that matches the product attribute

  Correct

  Subscription filters deliver only messages whose attributes match; the other messages are acknowledged automatically and are not delivered to the subscriber.

• CA Single Message Transform on the topic that drops the other products' events

  Incorrect

  A topic SMT runs before messages are stored for the topic, so dropping the other products' events there would remove them for every subscription, not only for the fraud consumer.

• DA dead-letter topic that collects the events the consumer rejects

  Incorrect

  The consumer would still receive every message and reject 95% of them.

Pub/Sub can filter messages per subscription on their attributes. Messages that do not match the filter are not delivered to that subscription's subscribers, so the consumer sees only its 5% and other subscriptions on the topic are unaffected.