Reliability, Data Quality and ACID Choices

What it is

Fidelity means the data is correct: cleaned, validated and true to its source. Reliability means the system keeps delivering that data through failures, with the right recovery point and recovery time. The exam asks you to pick tools for preparation and validation, and to trade strong consistency (ACID) against availability and scale.

How it works

Preparing and cleaning data

An LLM can draft a query from a plain-language prompt, but the output must be reviewed, tested and run with a dry run first. Treat generated SQL like code from a new colleague: useful, never trusted blindly.

Data validation

Validate at every boundary: on ingestion, after each transformation and after migration. In Dataform, assertions run after the table is built and fail the workflow when rows break a rule.

config {
  type: "table",
  assertions: {
    uniqueKey: ["order_id"],
    nonNull: ["order_id", "customer_id"],
    rowConditions: ["amount >= 0"]
  }
}
SELECT order_id, customer_id, amount FROM ${ref("stg_orders")}

Other options are Dataplex data quality scans with rules on BigQuery tables, a dead-letter output in Dataflow for records that fail parsing, and row counts and checksums compared between source and target.

Monitoring and orchestration

Cloud Composer or Workflows run steps in order with retries. Cloud Monitoring alerts on pipeline health, for example Dataflow data freshness and system lag or the age of the oldest unacknowledged Pub/Sub message. An alert on data that stops arriving catches silent failures that a job-status alert misses.

Disaster recovery and fault tolerance

Start from the RPO (how much data you may lose) and RTO (how long you may be down).

ACID compliance versus availability

Bigtable replication with multi-cluster routing favours availability; reads from another cluster can briefly be stale. Use single-cluster routing when an application needs read-your-writes behaviour.

A worked example

A finance team's nightly load sometimes doubles rows when a step is retried, and the dashboard shows inflated revenue. The engineer makes the load idempotent with a MERGE on the business key and adds a uniqueKey assertion in Dataform. Composer retries the task safely, and a Cloud Monitoring alert fires if the table is not refreshed by 06:00.

How the exam asks about this

• "Analysts transform data in BigQuery with SQL and need tests, dependencies and Git history." Dataform with assertions.
• "Recover a BigQuery table to its state four hours ago after a bad update." Query it with FOR SYSTEM_TIME AS OF and write the result to a new table, using time travel.
• "Inventory updates across regions must never oversell stock." Spanner, for globally consistent transactions.