Data quality checks

By the first week of November, three feeds are loading every night: store sales, returns and the Google Ads transfer. On Thursday, November 5, Ravi asks a blunt question: "If one of them goes wrong tonight, who finds out, and when?" At the moment the honest answer is "Finance, at nine in the morning, when the flash report looks odd." This session turns the profile findings from lesson 3 and the rules from lesson 4 into automated checks that find problems first.

The dimensions of data quality

Data quality is easier to manage when it is broken into dimensions, each with its own kind of check. The names vary between books, but the ideas are consistent, and the exam expects you to recognize them.

Accuracy is the hardest dimension to check automatically, because it needs something to compare with. For store sales, the tills produce an end-of-day total that the store manager signs off, and comparing the loaded total with that figure is the strongest single check the team has. Lesson 6's manifest check is a completeness check; this one goes further.

Where checks belong

Checks can run at each layer of the pipeline, and they catch different things. Checks at ingestion catch missing or malformed files before anything is loaded. Checks in staging catch rule violations after cleaning, such as unknown stores or SKUs. Checks on curated tables catch business-level surprises, such as a store's daily sales falling to a tenth of its usual level. A good design has a few checks at each layer rather than many at one.

Four ways to run checks on Google Cloud

The simplest form is a SQL check that fails the job. BigQuery's scripting language has an ASSERT statement: if its condition is false, the script stops with an error, and any scheduled query or orchestration step running it is marked as failed. Placed after the staging build, it stops bad data from flowing into curated tables.

ASSERT (
  SELECT COUNT(*) FROM `bl-data-prod.staging.pos_sales` AS s
  LEFT JOIN `bl-data-prod.reference.products` AS p USING (sku)
  WHERE s.business_date = @run_date AND p.sku IS NULL
) = 0 AS 'Sales lines with an sku missing from reference.products';

ASSERT (
  SELECT COUNT(DISTINCT store_id) FROM `bl-data-prod.staging.pos_sales`
  WHERE business_date = @run_date
) >= (SELECT COUNT(*) FROM `bl-data-prod.reference.stores_open` WHERE open_date = @run_date)
  AS 'Fewer stores loaded than were open';

Dataplex data quality scans

For checks that should run across many tables with a history of results, Birchline uses data quality scans in Dataplex Universal Catalog, the companion of the profile scans from lesson 3. A scan applies a set of rules to a table. Each rule checks one column or the whole table, and has a threshold: the share of rows that must pass for the rule to pass. The common rule types are listed below.

Each scan run produces a pass or fail for every rule and an overall score, and the results can be published so that people browsing the table in the catalog see its quality, and exported to a BigQuery table for history. Scans can be scheduled, filtered to only the newest partition so they read only last night's data, and set to send notifications when a rule fails. Dataplex can also suggest rules from a profile scan, which is a quick way to start: Mateo accepts the suggested non-null and set rules and writes the range and SQL rules himself.

Thresholds are a judgment. A threshold of all rows for sku being non-null means one empty SKU fails the rule, which is right, because an empty SKU means a broken till. A threshold of 995 in 1,000 for the tender set allows the occasional new payment type, such as the voucher found in lesson 4, without paging anyone at night, while still catching a real problem such as a till sending garbage.

# Scoring a night's quality rules against their thresholds.
rows = 31_204
rules = [  # (rule, rows passing, threshold as rows that must pass per 1,000)
    ("sku non-null",           31_204, 1000),
    ("qty in range",           31_190,  999),
    ("tender in set",          31_031,  995),
    ("sku matches pattern",    31_204, 1000),
    ("discount <= line value", 31_203, 1000),
]
for name, passing, need in rules:
    per_1000 = passing / rows * 1000
    status = "PASS" if per_1000 >= need else "FAIL"
    print(f"{name:24s} {per_1000:7.2f} per 1,000 (needs {need}) {status}  failing rows: {rows - passing}")

passed = sum(1 for _, p, n in rules if p / rows * 1000 >= n)
print(f"Score: {passed} of {len(rules)} rules passed")

sku non-null             1000.00 per 1,000 (needs 1000) PASS  failing rows: 0
qty in range              999.55 per 1,000 (needs 999) PASS  failing rows: 14
tender in set             994.46 per 1,000 (needs 995) FAIL  failing rows: 173
sku matches pattern      1000.00 per 1,000 (needs 1000) PASS  failing rows: 0
discount <= line value    999.97 per 1,000 (needs 1000) FAIL  failing rows: 1
Score: 3 of 5 rules passed

The output tells a clear story. Fourteen rows have a quantity outside the range, but that is within the threshold. One hundred and seventy-three rows have an unexpected tender value, which breaks the threshold of 995 in 1,000 and deserves a look in the morning. One row has a discount larger than the line's value, and since that rule allows no exceptions, it fails. Each failing rule comes with a count of rows, which makes the investigation quick.

Reconciliation

The strongest check of all compares the pipeline's output with an independent figure. Each store's till prints an end-of-day total that the manager signs; the store systems team sends those totals as a small file of its own. A nightly query compares the totals in curated.daily_store_sales with the signed totals, store by store, and lists any store that differs by more than a dollar. Most nights the list is empty. When it is not, the difference is usually explained by a duplicate that staging removed, a late file, or a refund processed after the till closed, and each explanation goes into the incident log.

Freshness and volume checks

Two simple checks catch many real problems. A freshness check asks whether the latest data is as recent as it should be: if the newest business_date in staging.pos_sales is not yesterday by 04:00, something upstream has stalled. A volume check compares today's row count with the usual range for that day of the week; a sudden drop or doubling usually means a missing or duplicated file. Both are cheap, because they read only metadata or one partition, and both catch failures that row-level rules miss entirely. A table with zero rows passes every non-null rule.

Checks need testing too. A rule that has never failed may simply be wrong, for example a range rule on the wrong column or a set rule that was copied with a typing mistake. Before trusting the new scans, Mateo copies one night of staging data into a sandbox dataset and breaks it on purpose: he deletes one store's rows, blanks twenty SKUs, adds a tender value of XYZ and duplicates a hundred lines. Then he runs every check against the broken copy and confirms that each problem is caught by the check meant to catch it, with the expected severity. Two checks failed to fire the first time. The volume check compared with the wrong day of the week, and the uniqueness rule used a column list that did not match the staging deduplication. Both were fixed before the checks went live, and the broken copy is kept so that the test can be repeated whenever a rule changes.

When a check fails

A check is only useful if a failure leads to action. Ravi sets up a short playbook. Failures that make the data wrong, such as missing stores or failed reconciliation, stop the pipeline before curated tables are updated, so the morning report shows yesterday's numbers with a note instead of wrong numbers. Failures that are warnings, such as a tender threshold breach, let the pipeline continue and open a ticket. Every failure is recorded in a quality incident log with the date, the rule, the rows affected, the cause and the fix.