Task 1.2: Designing for reliability and fidelity.

• AAttach a Pub/Sub dead-letter topic to the subscription with five delivery attempts

  Incorrect

  Google advises against Pub/Sub dead-letter topics with Dataflow: Dataflow negatively acknowledges messages for its own internal reasons, so good messages can end up in the dead-letter topic, and the failing bundle is still a pipeline problem.

• BCatch the parse error, log it, and drop the event so that the bundle completes

  Incorrect

  Dropping the element unblocks the pipeline but loses the data and prevents it from being inspected and replayed later.

• CSwitch the job to at-least-once streaming mode so that failing events are skipped

  Incorrect

  At-least-once mode allows duplicates in exchange for lower cost and latency; it does not skip elements whose processing throws an exception.

• DCatch the error and emit the raw event to a dead-letter output written to BigQuery

  Correct

  This is the dead-letter queue pattern: a branching output collects the failed elements into their own PCollection, which is written to a table for inspection and replay while good elements continue.

Bad records should not stop a streaming pipeline, and they should not be thrown away. In Dataflow the recommended pattern is to catch the exception in the transform and send the failing element, with the error, to a separate output that is written to a dead-letter destination. Pub/Sub dead-letter topics are the right tool for ordinary subscribers, but Google recommends against them with Dataflow and recommends the in-pipeline pattern instead.

• ACross-region dataset replication, with on-demand queries in the second region

  Incorrect

  The secondary replica is read-only and cannot be promoted while the primary region is down; Google states that replication alone is not meant as a plan for a total region outage.

• BManaged disaster recovery with a failover reservation on Enterprise Plus

  Correct

  Managed disaster recovery adds compute failover to dataset replication: a failover reservation with standby capacity in the paired region and a hard failover that works even when the primary is offline, aiming to keep replicas within 15 minutes.

• CA nightly dataset copy to a second region with the Data Transfer Service

  Incorrect

  A nightly copy can leave a day of data behind and brings no compute or failover with it, so it misses both the 15-minute target and the minutes to recover.

• DHourly exports of each table to a dual-region bucket, ready to be reloaded

  Incorrect

  Exports keep a copy of the data, but recovery means reloading every table in another region, which takes far longer than minutes and is an hour behind at best.

Cross-region dataset replication keeps a read-only copy of a dataset in a second region, but it cannot fail over while the primary region is out. Managed disaster recovery, available with the Enterprise Plus edition, builds on that replication with a failover reservation: after a failover the secondary becomes primary for both storage and compute, with a recovery time of about five minutes and a replication target of 15 minutes.

• AuniqueKey set to order_id, so that no two rows share an order

  Incorrect

  A uniqueKey on order_id alone asserts that no two rows share an order. Because an order legitimately has many lines, it would fail on correct data.

• BrowConditions with the expression line_number > 0 for each row

  Incorrect

  rowConditions evaluates an expression on each row on its own, so it cannot see that two rows repeat the same pair.

• CuniqueKeys set to the combination of order_id and line_number

  Correct

  uniqueKeys checks that no two rows share the same values across the listed column combination, which is exactly the composite key.

• DnonNull set to the order_id and line_number columns of the table

  Incorrect

  nonNull catches missing values, not duplicates; the duplicated lines have both values filled in.

Dataform's built-in assertions run after the table is created and fail if their query returns rows. A uniqueness assertion fails when two rows share the same values for all the columns it lists, so it must list the whole key: uniqueKeys with the combination of order_id and line_number. nonNull checks for nulls and rowConditions checks a per-row expression.

• ADataform, with SQLX files that define each transformation step

  Incorrect

  Dataform manages SQL transformations of data that is already in BigQuery; it does not extract from Oracle, SFTP or SaaS sources, and it is code.

• BDataflow, with pipelines written in the Apache Beam Python SDK

  Incorrect

  Dataflow can do all of this, but the pipelines are code, which the team does not write.

• CBigQuery data preparation, with Gemini suggestions for the cleaning

  Incorrect

  Data preparation cleans data that BigQuery can already reach; it does not pull from on-premises databases, SFTP and SaaS sources.

• DCloud Data Fusion, with pipelines built in its visual designer

  Correct

  Cloud Data Fusion is a managed data integration service where pipelines are built visually from plugins for sources, transforms and sinks, and run on Managed Service for Apache Spark.

Cloud Data Fusion, built on open source CDAP, is the visual integration tool: pipelines are graphs of source, transform, aggregate and sink plugins, and each run provisions an ephemeral Managed Service for Apache Spark cluster. Dataform and BigQuery data preparation work on data inside BigQuery, and Dataflow needs code.

• AA remote model over Gemini that AI.GENERATE_TEXT calls with each question

  Incorrect

  Calling a model from SQL returns generated text inside a query result; it is not an editor feature that writes the query from the user's schema.

• BGemini in BigQuery, which generates SQL from prompts and comments

  Correct

  Gemini in BigQuery turns a natural language prompt or comment in the editor into SQL using the schema and metadata of the user's tables, and Google recommends reviewing the output before running it.

• CA custom agent built with Agent Platform that queries the tables

  Incorrect

  A custom agent is an application to build, host and secure, which the company does not want.

• DBigQuery data canvas with each table pinned as a saved query

  Incorrect

  Data canvas is a visual analysis workspace; pinning saved queries does not generate new SQL from the analysts' questions.

Prompting an LLM to write queries is built into BigQuery Studio: Gemini in BigQuery generates SQL from a prompt or a comment, completes queries and explains them. It grounds the query in the tables' schemas and metadata, and its suggestions should be validated before use.

• AWrap the INSERT and the DELETE in one multi-statement transaction

  Correct

  A multi-statement transaction commits its DML statements across tables atomically, and rolls them all back if any statement fails.

• BRun the INSERT and the DELETE as two steps of a scheduled query

  Incorrect

  Scheduling the statements does not make them atomic; a failure between them still leaves the tables out of step.

• CTake a table snapshot of both tables before each nightly run

  Incorrect

  Snapshots allow a manual restore afterward, but they do not make the two changes atomic.

• DReplace the two statements with a MERGE on the pending table

  Incorrect

  A MERGE statement changes only its one target table, so it cannot insert into the ledger and delete from pending together.

BigQuery supports multi-statement transactions: BEGIN TRANSACTION, several DML statements across tables, then COMMIT TRANSACTION. Either every change is committed or, on error or ROLLBACK, none is, which gives the ACID behaviour a ledger move needs.

• AAdd a subscription filter that drops messages without the order ID

  Incorrect

  A filter applies on a subscription, after the message is accepted by the topic, and it only checks attributes, not the payload's structure.

• BAttach a dead-letter topic to each consumer's subscription

  Incorrect

  Dead-lettering moves messages that consumers fail to acknowledge; the bad message has still been published and has already broken processing.

• CAssociate an Avro or Protocol Buffer schema with the topic

  Correct

  With a schema on the topic, Pub/Sub validates the message data at publish time, and a message that does not conform is not published.

• DValidate each message in a Cloud Run function triggered by the topic

  Incorrect

  A triggered function sees the message only after it has been published, so the publisher never gets the error.

Pub/Sub schemas enforce the format of a message's data field. Once a topic has an Avro or Protocol Buffer schema, every publish is checked against it, and non-conforming messages are rejected with an error to the publisher, which keeps bad data out of the topic altogether.