Task 2.1: Planning the data pipelines.

• ALaunch the job with --no_use_public_ips and --subnetwork set to the subnet

  Correct

  This pipeline option makes Dataflow create workers with internal IP addresses only, in the subnet that is named.

• BReserve static external IP addresses for the workers instead

  Incorrect

  A static external address is still an external IP address, which the organization policy forbids on VMs.

• CCreate a Cloud NAT gateway for the subnet so that workers reach Google APIs

  Incorrect

  Cloud NAT gives workers a path to the internet, which the security team does not allow; Google APIs can be reached without it.

• DGrant the worker service account the Compute Network Admin role

  Incorrect

  A broader network role does not change how workers get IP addresses or how they reach Google APIs.

• ETurn on Private Google Access for the subnet

  Correct

  Workers without external IPs can reach Cloud Storage and BigQuery APIs only if Private Google Access is enabled on their subnet.

Dataflow workers without external IP addresses are set with usePublicIps=false (Java) or --no_use_public_ips (Python and Go) plus the network or subnetwork. They then reach Google APIs through Private Google Access on that subnet. Cloud NAT is only needed when workers must reach the internet, for example to download packages, which this design forbids.

• ALoad each 30-second batch into its own table and query them with a wildcard

  Incorrect

  Thousands of new tables a day avoids the per-table limit but makes every query scan a wildcard of small tables, which Google advises against in favour of one partitioned table.

• BWrite the events into the table with the BigQuery Storage Write API

  Correct

  The Storage Write API streams rows into the table with low latency and is not subject to the per-table load job limit, which 2,880 loads a day would exceed.

• CRun the load jobs from a second project so that they draw on a separate quota

  Incorrect

  The 1,500 limit is counted per table per day, so jobs submitted from another project into the same table still hit it.

• DKeep the plan and retry each failed load job with exponential backoff

  Incorrect

  Failed load jobs count toward the per-table limit too, so retrying makes the table reach the limit sooner.

BigQuery allows 1,500 load jobs per table per day, failed ones included. A load every 30 seconds is about 2,880 a day, so micro-batching through load jobs fails partway through the day. Streaming with the Storage Write API is the ingestion path for continuous, low-latency writes into one table.

• ANothing more, because Cloud Interconnect encrypts its traffic by default

  Incorrect

  Google states that Cloud Interconnect does not encrypt traffic by default, so the audit requirement is not met as things stand.

• BSend the replication through Private Service Connect endpoints instead

  Incorrect

  Private Service Connect gives private endpoints for services; it does not add encryption to traffic crossing the Interconnect.

• CUse customer-managed encryption keys on the destination databases

  Incorrect

  CMEK protects data at rest in the destination, not data in transit on the link.

• DEnable MACsec for Cloud Interconnect, or run HA VPN over the Interconnect

  Correct

  MACsec encrypts traffic between the on-premises router and Google's edge routers, and HA VPN over Cloud Interconnect encrypts what the VLAN attachments carry; both work below the applications.

Traffic on Cloud Interconnect is private but not encrypted by default. When encryption on the link is required for every application, the options are MACsec for Cloud Interconnect on supported circuits or HA VPN tunnels over the Interconnect attachments; neither needs changes to the applications.

• APub/Sub notifications on the bucket for OBJECT_FINALIZE events

  Correct

  The bucket publishes a message to Pub/Sub when each new object is created, so the processor reacts within seconds instead of polling.

• BA Cloud Scheduler job that lists the bucket once a minute instead

  Incorrect

  Listing every minute is still polling, adds list operations and can miss the one-minute target under load.

• CObject Versioning on the bucket to track the newest generations

  Incorrect

  Versioning keeps older generations of overwritten objects; it does not tell anything that a new file arrived.

• DA Storage Transfer Service job that copies new files each hour

  Incorrect

  A scheduled transfer copies files on its schedule and adds an hour of delay.

Event-driven ingestion starts from Pub/Sub notifications for Cloud Storage: an OBJECT_FINALIZE message is sent for each new object. Delivery is at least once and unordered, so the processor should tolerate duplicates, for example by checking the object's generation.

• ATransform in Dataflow before loading, with Beam pipelines in Java

  Incorrect

  Transforming in Dataflow before load needs code in Java or Python and moves the joins away from the BigQuery tables they use.

• BTransform on Managed Service for Apache Spark clusters with PySpark

  Incorrect

  Spark jobs are Python code on clusters, and the dimension data would have to be read out of BigQuery for every run.

• CELT, loading raw data first and transforming it in BigQuery with Dataform

  Correct

  Dataform manages the transformation step of ELT in BigQuery with SQL, Git version control, dependencies and assertions, which suits a SQL team.

• DRun the SQL by hand in BigQuery Studio after each hourly load

  Incorrect

  Manual runs are neither repeatable nor tested, and they do not scale to an hourly schedule.

When the data already lands in BigQuery and the logic is relational, ELT keeps processing where the data is. Dataform lets analysts develop, test, version and schedule SQL workflows in BigQuery, so the team can own the transformations without writing pipeline code.

• APublish each event with the account ID as its ordering key

  Correct

  Messages that share an ordering key are delivered in order, and keying by account keeps the order where it is needed.

• BCreate a single topic per account so that each has its own queue

  Incorrect

  Topics are not ordered queues; without ordering keys, messages on one topic are still delivered out of order.

• CTurn on exactly-once delivery on the subscription

  Incorrect

  Exactly-once delivery does not order messages; the order of delivery comes from ordering keys and the subscription's ordering setting.

• DEnable ordering on the subscription and publish each account in one region

  Correct

  Ordering must be enabled when the subscription is created, and messages with the same ordering key must be published in the same region.

• EUse a push subscription so that messages arrive one at a time

  Incorrect

  Push delivery does not by itself impose an order; ordering comes from ordering keys and the subscription setting.

Pub/Sub orders messages per ordering key. The publisher sets the key, the subscription must have message ordering enabled at creation, and messages with the same key must be published in the same region. Each key is limited to about 1 MBps, and a redelivery replays the later messages of that key too.

• ARun the change query each minute instead of each five minutes

  Incorrect

  A deleted row no longer exists in the source, so no query on updated_at can find it, however often it runs.

• BRead changes from the MySQL log with Datastream instead of querying

  Correct

  Log-based change data capture sees every DML change, deletes included, and Datastream applies them to BigQuery.

• CAdd an index on updated_at so that the change query is faster

  Incorrect

  An index speeds the query up but still cannot return rows that were deleted.

• DSwitch the query to a BigQuery federated query on the database

  Incorrect

  Federated queries read the current table; deleted rows are missing from it just the same.

Query-based extraction only sees rows that still exist, so hard deletes are lost unless the application soft-deletes. Log-based CDC reads the database's change log, which records inserts, updates and deletes; Datastream does this for MySQL and other sources and writes the changes to BigQuery.