Data Quality Engineering

Data Engineering & Modern Data Platforms · Lesson 17 · Data Ingestion, ETL and Orchestration

Lesson Overview

The completion SQL from ETL and ELT Architecture ran on a Tuesday whose progress file had published under the batch job. Four percent of lesson_id values were absent from the lesson dimension. The dimension pointer had not moved, because the catalog pull in API and File-Based Data Ingestion had failed its manifest and left the previous pointer in place. The SQL inner join dropped those events. The job exited zero. The completion rate for one course fell, and there was no quarantine, so the fall looked like learners. The same month, a refund on a course purchase was implemented by deleting the sale line. Tuesday's revenue could not be rebuilt on Wednesday. The line that had been the sale was gone. Both jobs parsed. Neither had a gate that could refuse to publish.

Data quality engineering turns those refusals into checks with a severity, an owner, and a place in the pipeline. A check names a property: the key is unique, the lesson exists in a pinned dimension, the batch is fresh, the price is non-negative, a refund is a new line. Severity says whether a breach fails the publish, quarantines the row, or warns while the previous version stays served. The place is before the curated table becomes the one consumers read. A dashboard filter is not a gate. The bad rows have already been served, and the filter is a second definition.

This lesson builds that gate for two TechLearningPro products. Progress events must pass schema, uniqueness, referential integrity, freshness, and a quarantine cap before completion-v3 replaces a window. Purchase lines must pass grain, a non-negative money rule, a line type that keeps sales in history, and a prohibition on payment tokens. The estate is otherwise the one this module has been using: enrollment and progress in PostgreSQL, a derived completion warehouse, prohibited personal and payment fields, a morning bound of 06:00 UTC. Volumes stay labeled. About fifty million progress events on a peak day means checks are set-based, not a spreadsheet. An enrollment-week spike is not automatically a failed volume check. "The row matches the real world" is mostly not automatable. These checks are the properties you can fail a job on without pretending to know each learner's intention.

Frameworks can host the same rules. This lesson implements them in Python and SQL so the severity stays visible. It does not call a fictional quality API. Change Data Capture Fundamentals still feeds the cohort product through its own apply rule. A captured change is not exempt from a gate. Paging a human when the gate fails belongs to orchestration, lesson 19. The gate comes first.

Learning Objectives

By the end of this lesson you will be able to:

1. Separate schema, uniqueness, referential, freshness, and business-rule checks, and name the failure each one sees.
2. Assign fail, quarantine, and warn so a breach either blocks publish or is counted, and is never a silent drop.
3. Block a completion publish when progress events collide, exceed a quarantine cap, or miss the freshness bound.
4. Define purchase-line checks that keep a sale in history and keep payment tokens out of the warehouse.
5. Explain why an inner join that drops unknown lesson ids is not a quality control.
6. Place checks before the curated publish, and state what the consumer sees when the gate fails.

Prerequisites

• Introduction to Data Engineering: grain, prohibited fields, and the difference between a green job and a true metric.
• Lesson 13: the curated window replace is what the gate protects. If the gate fails, that replace does not run.
• Lesson 14: file-level refusals and quarantined lines. This lesson adds rates, referential checks, and reconciliation.
• Lesson 15: the lesson-dimension pointer a referential check must pin. A stale pointer is a cause, not a reason to skip the check.
• SQL joins and GROUP BY, from /learn/sql and SQL Fundamentals for Data Engineers. Python 3.11 dataclasses, from /learn/python.
• No statistics package. A volume cliff can be a warning. A model of "normal" completion is out of scope, and it misfires during enrollment week.

Detailed Core Concepts

A check is a predicate with a consequence

A quality check is a predicate over a batch or a row, a severity, and the product it applies to. "Lesson ids should be valid" is a wish. "Every lesson_id in this progress batch must appear in manifest dim-2026-03-02; unknowns are quarantined; if they exceed two percent of the batch, the publish fails" is a check. The manifest id matters. The dimension moves hourly. The gate records the pin it used, so a later pointer does not silently rewrite yesterday's verdict.

Three severities carry this module.

Fail means the curated publish does not happen. Consumers keep the previous version and its timestamp. Uniqueness collisions, prohibited fields, a missed freshness bound, a reconciliation miss, and a quarantine rate over the cap are fails in the suites below. Publishing "the good rows" when the rate says the file is not the day is a warn wearing a fail's name.

Quarantine means the row is excluded from this publish and written, with a reason, where the owner will read it. The row stays in raw. Raw is the replay source. One unknown lesson_id in a large batch is a quarantine while the rate stays under the cap. Quarantine is a property of this publish, not a deletion of history.

Warn means the publish proceeds and a person is notified. A warn with no reader is a comment. Use it while you measure a condition you are not yet willing to fail, and put a review date on it. An open-ended warn is how four percent of a course disappeared through an inner join.

What a job can actually observe

Schema and validity. Required fields exist. Types match. attempt_number is a positive integer. event_type is in the allowed set. unit_price_cents is an integer at least zero. These checks do not know whether the price is the price the learner was shown. They know the value sits in the domain you wrote down.

Uniqueness. The key appears once, and a second payload for that key fails the batch. Progress uses event_id. Purchase lines use (order_id, line_id). Identical retries may already have been collapsed by the loader. SELECT DISTINCT in a mart hides the collision count. It is not the check.

Referential integrity. A key the product depends on exists in the snapshot you name. Progress references lesson_id in the pinned dimension. Purchase lines reference course_id the same way. An event that arrives before the dimension publish is a wait or a quarantine, not a mystery drop. The pin is lesson 15's pointer, stored on the gate result.

Freshness. The batch's commit time is young enough for the consumer bound. For the morning completion job, the checkpoint age must fall inside the window you promised. Freshness does not mean the contents are right. A fresh file of the wrong grain is still fresh. A correct file from last week fails freshness when the contract is "yesterday by 06:00 UTC." You want both failures, because the response differs.

Business rules. Predicates the metric owner signed. A refund has line_type = 'refund', a positive quantity, and does not remove the sale. An attempt number below 1 is invalid. When a rule change moves a number consumers quote, it is a definition change with a version, not a silent edit to a threshold.

Accuracy against the outside world is not a row predicate. You approximate it with reconciliation: distinct source ids versus raw ids, or the net of sale and refund cents versus a ledger total, inside a tolerance finance wrote down. A mismatch fails or warns as that agreement says. It does not prove each row. Two offsetting errors can reconcile and still be wrong. Uniqueness is what makes that harder.

Where the gate sits

For progress, raw is already durable, because lesson 13 replays from it. The gate stands between raw and the curated replace. On failure, the replace does not start and the previous curated window remains. Quarantined rows stay in raw and in the quarantine record. A repaired dimension can replay them. A gate that runs after consumers can read the window is a postmortem. Deleting raw when a check fails removes the replay. Lesson 14 may already have refused a file that contained email. The purchase suite repeats a prohibited-field check because that extract has grown a token column before. Two boundaries, one rule.

The cohort table from lesson 16 gets its own checks: a key on every delete, monotonic LSNs per key, freshness against the slot. That suite does not restate completion, and it does not delete historical facts. A generic "warehouse quality" job will eventually apply the wrong delete. Name the product on the suite.

Rates and the enrollment week

A rate needs a denominator you trust: rows in this batch, not the lifetime of the table. Two percent of a peak day is a large count. Print both. A volume check that fails whenever today is far below yesterday will page you on a holiday and on the day after enrollment week. Those drops are real and often expected. Compare to a calendar if you warn. Do not fail the publish on a spike or a dip until the metric owner will act. This suite fails volume only when the source window was non-empty and the accepted set is empty, or when the quarantine rate crosses the cap. Empty acceptance with a non-empty source is a broken job. A small Tuesday is not.

Ownership

The ingestion squad owns schema and freshness of the landing, because those describe the copy. The metric owner owns business rules and the cap, because those change the number. Referential checks are joint: engineering pins the dimension, analytics agrees that an unknown lesson is quarantined rather than dropped. One suite, one version, one publish bit. A notebook that deletes rows "to clean up" is an unowned fail with no record.

Architecture and Design Diagrams

Progress and purchases do not share a quarantine table. The reasons and the owners differ. They share severities.

mermaidCopy
flowchart LR
    subgraph progress [Progress product]
        rawP[(Raw progress)]
        dim[Pinned lesson dimension]
        gateP[Progress suite]
        qP[(Progress quarantine)]
        sql[completion-v3 replace]
        mart[(Curated completion)]
        rawP --> gateP
        dim --> gateP
        gateP -->|rate under cap| sql --> mart
        gateP -->|row excluded| qP
    end

    subgraph purchases [Purchase product]
        rawO[(Raw order lines)]
        gateO[Order-line suite]
        qO[(Order quarantine)]
        fin[Finance extract]
        rawO --> gateO
        gateO -->|pass| fin
        gateO -->|row excluded| qO
    end

    gateP -.->|fail keeps previous curated window| mart
    gateO -.->|refunds do not delete sales| fin

The fail path does not write an empty day. An empty publish is a successful zero, which reads as "nobody completed anything." Holding the previous window reads as "the new day is not ready," which is the truth when the gate fails.

Checks run in an order the policy can explain. The rate uses the original row count, not the count after you have thrown rows away and forgotten the denominator.

mermaidCopy
flowchart TD
    batch[Named batch and pinned inputs]
    schema[Schema and prohibited fields]
    uniq[Uniqueness of the grain]
    ref[Referential check against the pin]
    biz[Business rules]
    fresh[Freshness of the commit]
    rate{Any fail, or quarantine rate over cap?}
    hold[Keep previous curated version]
    pub[Publish this version]
    batch --> schema --> uniq --> ref --> biz --> fresh --> rate
    rate -->|yes| hold
    rate -->|no| pub

Freshness can fail a clean file. Referential misses quarantine until the cap. Uniqueness and prohibited fields fail immediately. A job that publishes and then checks freshness has reordered the diagram.

Technical Explanation

Uniqueness is about payloads

Two lines with the same event_id and the same contract fields are one event. Two lines with the same event_id and different contract fields fail the publish. There is no row you are allowed to pick. Quarantining one and publishing the other chooses a winner. The previous curated day stays, and the producer incident is visible. A primary key on the curated table remains the last latch. It should not be the first time you hear about the collision. Store the collision count on the gate result. A zero is a healthy day. A non-zero fail is the page.

Referential checks name a manifest

Unknown means unknown in the pinned manifest, not unknown forever. If the catalog scan is late, the orchestrator can wait inside the freshness bound, or the gate quarantines and may cross the cap and fail. Failing closed is the default when the alternative is an inner join that shrinks a course. The suite reports the pin and the unknown keys. The join reports a smaller number and a green status. Do not repair referential misses by inserting placeholder lessons. A placeholder becomes a permanent dimension member. Repair the catalog or the event, then replay from raw.

Freshness uses the commit clock

The lesson 14 checkpoint has a commit time. The gate compares now - committed_at in UTC to a maximum age. A daily job owed by 06:00 UTC fails when the commit is older than the bound you publish. Using maximum event_time as freshness confuses a late learner event, which the restatement window allows, with a job that did not run. Event time is the business clock. Commit time is the pipeline clock. A missing checkpoint is a fail. There is nothing to publish.

Money keeps its history

Purchase grain is (order_id, line_id). A sale and a refund are two lines. Quantity is a positive integer. unit_price_cents is an integer at least zero. line_type is sale or refund. The refund does not delete the sale. Deleting the sale is how Tuesday became unreproducible. A check on a file of "current lines" cannot see a sale that was deleted before the extract. The load pattern has to append refunds and leave sales. The suite then refuses negative quantities, refuses a payment token, and reconciles the net of the batch to a ledger total inside a tolerance. An unknown currency is a quarantine, not a default of USD. A default would book the wrong unit and look valid.

Reconciliation has a written band

Sum quantity * unit_price_cents for sales, subtract refunds, and compare to the ledger total finance already trusts, inside a tolerance they signed. Over the band, the publish fails. This catches a lost file and a double load. It does not catch a sale assigned to the wrong course when the total still matches. Keep uniqueness beside it. The ledger total is an input to the function. This lesson does not invent a payments client. Whoever operates the job obtains the total from the ledger's own documented interface and passes the integer in.

Volume warnings that are not fails

A drop of more than two standard deviations will fire when the platform is down, when enrollment week ends, and when a hard course ships. Those weeks deserve a human. They are a bad automatic fail, because the team learns to silence the check. Warn, attach the counts, and leave the baseline for a later lesson if you build one. The automated volume rule in this suite is the empty acceptance of a non-empty source, expressed here as a quarantine rate of one hundred percent, which crosses the cap.

The same predicates in SQL

The Python gate decides. The SQL investigates, and it is what a warehouse-native test should match. If the SQL is stricter than the gate, you have two suites.

sqlCopy
SELECT event_id
FROM raw_progress_event
WHERE source_run_id = %(run_id)s
GROUP BY event_id
HAVING count(*) > 1;

SELECT e.event_id, e.lesson_id
FROM raw_progress_event AS e
LEFT JOIN lesson_dim AS d
    ON d.lesson_id = e.lesson_id
   AND d.manifest_id = %(manifest_id)s
WHERE e.source_run_id = %(run_id)s
  AND d.lesson_id IS NULL;

The manifest id is in the join so "the dimension" is the pin. A view of the latest dimension changes the verdict of an old batch when the catalog moves. Store the pin on the gate result. Replay with a new pin is a deliberate rerun, not an accident of a view.

Practical Implementation or Design Walkthrough

Problem

Decide whether a progress batch may drive the completion replace, and whether purchase lines may be published. Fail on collisions, prohibited fields, staleness, reconciliation misses, and quarantine rates over the cap. Quarantine individual domain and referential breaches under the cap. Leave raw rows in place.

pythonCopy
from __future__ import annotations

from dataclasses import dataclass
from datetime import datetime, timedelta, timezone


@dataclass(frozen=True)
class Finding:
    check: str
    severity: str
    detail: str


@dataclass(frozen=True)
class GateResult:
    publish: bool
    accepted_keys: tuple[str, ...]
    findings: tuple[Finding, ...]


def _rate_fail(
    name: str,
    row_count: int,
    quarantined: int,
    cap: float,
    findings: list[Finding],
) -> bool:
    if row_count == 0:
        return False
    rate = quarantined / row_count
    if rate > cap:
        findings.append(
            Finding(
                "quarantine_rate",
                "fail",
                f"{name} quarantined {quarantined} of {row_count} ({rate:.3f}) over cap {cap}",
            )
        )
        return True
    return False


def gate_progress(
    events: tuple[dict[str, object], ...],
    known_lessons: frozenset[str],
    committed_at: datetime | None,
    now: datetime,
    max_age: timedelta,
    quarantine_cap: float = 0.02,
) -> GateResult:
    """Return whether completion SQL may replace a window from this batch."""
    findings: list[Finding] = []
    quarantined: set[str] = set()
    seen: dict[str, str] = {}
    failed = False

    if committed_at is None or now - committed_at > max_age:
        findings.append(Finding("freshness", "fail", "progress checkpoint is missing or stale"))
        failed = True

    for event in events:
        event_id = str(event.get("event_id") or "")
        if any(key in event for key in ("email", "name", "payment_token")):
            findings.append(Finding("prohibited_field", "fail", f"{event_id} carries a prohibited field"))
            failed = True
            continue
        identity = "|".join(
            (
                event_id,
                str(event.get("learner_id")),
                str(event.get("lesson_id")),
                str(event.get("event_type")),
                str(event.get("attempt_number")),
            )
        )
        previous = seen.get(event_id)
        if event_id and previous is None:
            seen[event_id] = identity
        elif event_id and previous != identity:
            findings.append(Finding("uniqueness", "fail", f"conflicting event_id {event_id}"))
            failed = True
        attempt = event.get("attempt_number")
        lesson_id = event.get("lesson_id")
        domain_ok = (
            isinstance(attempt, int)
            and not isinstance(attempt, bool)
            and attempt >= 1
            and event.get("event_type") in {"checkpoint", "completed"}
            and isinstance(lesson_id, str)
            and lesson_id in known_lessons
        )
        if not domain_ok and event_id:
            quarantined.add(event_id)
            findings.append(
                Finding("row_rule", "quarantine", f"{event_id} failed domain or referential check")
            )

    if _rate_fail("progress", len(events), len(quarantined), quarantine_cap, findings):
        failed = True
    if failed:
        return GateResult(False, (), tuple(findings))
    accepted = tuple(event_id for event_id in seen if event_id not in quarantined)
    return GateResult(True, accepted, tuple(findings))


def gate_order_lines(
    lines: tuple[dict[str, object], ...],
    known_courses: frozenset[str],
    ledger_cents: int,
    tolerance_cents: int,
    quarantine_cap: float = 0.02,
) -> GateResult:
    """Publish purchase lines only when grain, money, and the ledger band hold."""
    findings: list[Finding] = []
    quarantined: set[str] = set()
    seen: dict[str, str] = {}
    failed = False
    net = 0

    for line in lines:
        if "payment_token" in line:
            findings.append(Finding("prohibited_field", "fail", "payment token in purchase batch"))
            failed = True
            continue
        key = f"{line.get('order_id')}:{line.get('line_id')}"
        identity = str(sorted((str(name), str(value)) for name, value in line.items()))
        previous = seen.get(key)
        if previous is None:
            seen[key] = identity
        elif previous != identity:
            findings.append(Finding("uniqueness", "fail", f"conflicting line {key}"))
            failed = True
        quantity = line.get("quantity")
        price = line.get("unit_price_cents")
        course_id = line.get("course_id")
        line_type = line.get("line_type")
        domain_ok = (
            line_type in {"sale", "refund"}
            and isinstance(quantity, int)
            and not isinstance(quantity, bool)
            and quantity >= 1
            and isinstance(price, int)
            and not isinstance(price, bool)
            and price >= 0
            and line.get("currency") in {"USD", "EUR", "INR"}
            and isinstance(course_id, str)
            and course_id in known_courses
        )
        if not domain_ok:
            quarantined.add(key)
            findings.append(Finding("row_rule", "quarantine", f"{key} failed money, type, or course check"))
            continue
        sign = -1 if line_type == "refund" else 1
        net += sign * quantity * price

    if _rate_fail("orders", len(lines), len(quarantined), quarantine_cap, findings):
        failed = True
    if not failed and abs(net - ledger_cents) > tolerance_cents:
        findings.append(
            Finding(
                "reconciliation",
                "fail",
                f"net {net} versus ledger {ledger_cents} outside tolerance {tolerance_cents}",
            )
        )
        failed = True
    if failed:
        return GateResult(False, (), tuple(findings))
    accepted = tuple(key for key in seen if key not in quarantined)
    return GateResult(True, accepted, tuple(findings))


def _demo() -> None:
    now = datetime(2026, 3, 3, 5, 0, tzinfo=timezone.utc)
    fresh = now - timedelta(hours=2)
    lessons = frozenset({"les_07"})
    good = {
        "event_id": "evt_1",
        "learner_id": "lrn_1042",
        "lesson_id": "les_07",
        "event_type": "completed",
        "attempt_number": 1,
    }
    known_events = tuple(dict(good, event_id=f"evt_{index}") for index in range(1, 100))
    unknown = dict(good, event_id="evt_missing", lesson_id="les_missing")
    passed = gate_progress(known_events + (unknown,), lessons, fresh, now, timedelta(hours=26))
    assert passed.publish
    assert "evt_missing" not in passed.accepted_keys
    assert len(passed.accepted_keys) == 99

    blocked = gate_progress(
        tuple(dict(good, event_id=f"bad_{index}", lesson_id="les_missing") for index in range(3)),
        lessons,
        fresh,
        now,
        timedelta(hours=26),
    )
    assert not blocked.publish
    assert blocked.accepted_keys == ()

    collided = gate_progress(
        (good, dict(good, event_type="checkpoint")),
        lessons,
        fresh,
        now,
        timedelta(hours=26),
    )
    assert not collided.publish

    stale = gate_progress((good,), lessons, now - timedelta(days=3), now, timedelta(hours=26))
    assert not stale.publish

    sale = {
        "order_id": "ord_1",
        "line_id": "ln_1",
        "line_type": "sale",
        "quantity": 1,
        "unit_price_cents": 5000,
        "currency": "USD",
        "course_id": "course_9",
    }
    refund = dict(sale, line_id="ln_2", line_type="refund")
    orders = gate_order_lines((sale, refund), frozenset({"course_9"}), ledger_cents=0, tolerance_cents=0)
    assert orders.publish
    assert set(orders.accepted_keys) == {"ord_1:ln_1", "ord_1:ln_2"}

    negative = gate_order_lines(
        (dict(sale, quantity=-1),),
        frozenset({"course_9"}),
        ledger_cents=0,
        tolerance_cents=0,
    )
    assert not negative.publish

    leaked = gate_order_lines(
        (dict(sale, payment_token="tok_secret"),),
        frozenset({"course_9"}),
        ledger_cents=5000,
        tolerance_cents=0,
    )
    assert not leaked.publish
    assert any(item.check == "prohibited_field" for item in leaked.findings)
    print(
        f"progress_publish={passed.publish} blocked={blocked.publish} "
        f"orders_publish={orders.publish} leaked={leaked.publish}"
    )


if __name__ == "__main__":
    _demo()

What the demo asserts

Ninety-nine known events and one unknown lesson publish, with the unknown key absent and the quarantine rate at one percent, under the two percent cap. Three unknown events and nothing else are a rate of one, so the publish is refused and the accepted set is empty. The previous curated window is what consumers would keep. The same event_id with completed and then checkpoint is a collision and a fail. A checkpoint three days old fails freshness even though the row itself is well formed. A sale of 5000 cents and a refund of 5000 cents reconcile to a ledger of zero and both keys publish. The sale line is still there. A negative quantity refuses the batch. A payment_token refuses the batch. No row is deleted from an imaginary raw table. The function only returns a decision.

How you would run it

bashCopy
python3 quality_gate.py

Expected output: progress_publish=True blocked=False orders_publish=True leaked=False. The standard library is enough. In production the progress function reads the run the checkpoint names and the manifest id the dimension pointer names, then returns the bit the SQL replace must see before it begins a transaction. The order function receives a ledger integer from a job step you control. It does not fetch payments itself.

What this walkthrough does not do

It does not page anyone. Lesson 19 does. It does not compute completion-v3. It does not decide that a quarantined lesson id is permanently invalid. It does not treat a standard-deviation drop as a fail. It does not call a hosted data-quality product. If you later put these predicates in such a product, the severities and the pin stay as written here.

Enterprise Real-World Scenarios

The four percent that looked like learners

The catalog job had failed its manifest. The pointer stayed on the previous hour, which did not yet contain a new lesson id the player had already emitted. About four percent of that day's events for the affected course referenced the new id. Labeled as a planning picture, not a measurement of your platform: on a peak day that course is a visible slice of the fifty million events, and four percent of a slice is enough to move a rate. The inner join dropped them. The curated replace committed. Monday showed a decline. Raw still had the events, which is why the story was recoverable.

The gate, had it been in place, would have pinned the old manifest, quarantined the unknown ids, computed a rate over the cap the metric owner had set at two percent, and refused the publish. The previous curated day would have stayed, with its timestamp. The page would have named the manifest and the unknown id. The repair is to finish the catalog scan, move the pointer, and rerun the gate and the window replace. It is not to insert a fake lesson, and it is not to lower the cap in the incident channel. After the rerun, the quarantined ids publish because they are no longer unknown. The gate result stores both pins, so the first refusal remains explainable.

The consumer sentence is part of the design. Instructor studio sees "Tuesday is not published" rather than a lower rate. Product analytics does not get a partial course with a green check. That sentence is what the inner join failed to provide.

The refund that deleted Tuesday

Finance reconciles course purchases to a ledger total. The operational refund path deleted the sale line and left a credit elsewhere. The warehouse extract of "lines that exist now" no longer contained Tuesday's sale. A rerun of Tuesday produced a smaller sum. The completion mart was untouched, and it was still the wrong week to discover that revenue history was a mutable table.

The repair has two parts, and the suite only covers the second. The load appends a refund line and does not delete the sale. That is the lesson 13 window rule applied to money: a refund is a new fact. The suite then requires line_type in sale or refund, quantity at least one, price at least zero, an allow-listed currency, a known course, no payment token, and a net inside the tolerance finance signed. A batch that still contains a negative quantity, from an old producer, fails. A batch that matches the ledger and keeps both lines publishes. The tolerance is an integer count of cents, not a percentage invented in the notebook. Offsetting errors remain possible. The uniqueness check on (order_id, line_id) is what stops a double load from hiding inside a total that someone "fixed" by deleting a row.

Payment tokens never enter the published keys. A column named payment_token fails the batch even when the sum would have matched. The landing file is private and is deleted under the retention rule from lesson 13. The finance extract reads accepted keys only.

Best Practices

• Write the predicate, the severity, the denominator, and the pin. A check without a consequence is a comment.
• Fail the publish on collisions, prohibited fields, staleness, reconciliation misses, and quarantine rates over the cap.
• Quarantine individual referential and domain breaches under the cap, and keep those rows in raw.
• On failure, serve the previous curated version. Do not publish an empty day.
• Pin the dimension manifest on the gate result. Do not join "latest" and call it the same check.
• Use commit time for pipeline freshness and event time for the business window.
• Keep sale lines when refunds arrive. Represent the refund as its own line.
• Reconcile to a ledger total with a written tolerance, beside a uniqueness check, not instead of one.
• Print rate and count. Choose the cap with the metric owner.
• Warn on calendar-driven volume swings. Fail on empty acceptance of a non-empty source.
• Name the product on the suite. Cohort deletes and completion facts do not share a gate.
• Store the gate result: pin, counts, severities, publish bit. A log line alone will rotate away before the argument does.
• Prefer a visible predicate in SQL or Python over a wrapper whose severity you cannot show in review.

Common Mistakes and Anti-Patterns

The inner join as a quality filter. Unknown lessons disappear, the query succeeds, and the metric moves. The repair is a counted quarantine, a cap, and a failed publish that leaves the previous window served.

Distinct as a uniqueness strategy. Collisions become one row and the evidence is gone. The repair is a fail on conflicting payloads and a metric of how many identical duplicates were collapsed upstream.

Deleting raw when the check fails. The next definition cannot replay, and the incident has no source rows. The repair is to keep raw, quarantine the publish, and fix the producer or the pin.

Freshness taken from event time. A job that did not run looks fresh because yesterday's events are still "recent," or a legitimate late event looks like a stale pipeline. The repair is commit time for the gate and event time for the business window.

A default currency or a default lesson. The row becomes valid by invention. The repair is quarantine or fail, then a contract change if the new value is real.

Refunds that delete sales. History cannot be restated. The repair is an append-only refund line and a gate that rejects negative quantities.

One generic suite for every table. A delete check written for the cohort table runs against completions. The repair is a named suite per product, with the lesson 16 rule explicit: cohort deletes do not remove completion facts.

An anomaly fail on enrollment week. The check pages every day, then it is muted, including on the day it would have mattered. The repair is a warn with counts, or a fail only for the empty-batch case, until a calendar-aware rule has an owner.

A warn nobody reads. The four percent was logged. The join still published. The repair is a severity that changes the publish bit, and a quarantine store with an owner and a review date.

Troubleshooting and Design Review

Symptom: Tuesday's completion rate dropped for one course, and the SQL job exited zero.

Likely cause: an inner join to a stale lesson dimension dropped events. There was no quarantine count.

Investigation: compare distinct lesson_id in raw for that run with the manifest the job actually joined. Compute the rate. Read whether a gate result exists.

Corrective action: hold the previous curated window, pin the manifest, rerun the gate after the dimension pointer moves, then replace the window. Do not backfill by hand in the mart. Do not lower the cap to make Tuesday green.

Symptom: The gate fails every morning with a freshness error, and the files look complete.

Likely cause: the check uses maximum event time, or it uses a commit time that is never written, or the clock is naive and the comparison shifts by a time zone.

Investigation: print committed_at, now, and max_age in UTC. Confirm the checkpoint update is the lesson 14 write that happens after publish.

Corrective action: store a timezone-aware commit time. Fail only when that age exceeds the bound. Keep event-time lateness in the restatement window, not in this predicate.

Symptom: Finance cannot rebuild Tuesday after a refund, and the quality job is green.

Likely cause: the extract contains only lines that still exist, the sale was deleted, and the suite never required the sale key to remain. A total can still match a ledger that also deleted it. Then both sides are wrong together.

Investigation: compare Tuesday's first published keys with today's extract. Look for sale keys present in raw history and absent now.

Corrective action: stop deleting sale lines. Append refunds. Restate from the raw history if you still have it. Add the line-type and positive-quantity checks. If both the warehouse and the ledger deleted the sale, the reconciliation will not save you. The retained raw file will.

Symptom: A peak day fails the quarantine cap on lesson ids the catalog published an hour later.

Likely cause: the gate ran against the previous pointer, and the rate is honest.

Investigation: read the pin on the gate result and the pointer's manifest time. List a handful of unknown ids and find them on the next manifest.

Corrective action: wait and rerun if the freshness bound allows, or fail and keep the previous day. Do not insert placeholder lessons. Consider waiting in the orchestrator when the catalog run is in progress. Do not disable the referential check.

Symptom: Quarantine grows every day and the publish still succeeds.

Likely cause: the cap is too loose, or findings are warn severity in practice because the publish bit ignores them.

Investigation: read the rate and the cap on the stored gate result. Confirm the publish bit is false when the rate exceeds the cap.

Corrective action: lower the cap with the metric owner, or fix the producer so the rate falls. A successful publish with an unread quarantine is the warn-nobody-reads failure. Assign an owner to the quarantine count.

A design review asks: What is the product and the pin? Which predicates fail, which quarantine, and what is the cap's denominator? What does the consumer read if the gate returns false? Where do quarantined rows live, and who reviews them? Which clock is freshness? What does a refund do to a prior sale? Which reconciliation tolerance is signed, and what can it miss? How does this suite refuse to delete completion history? A review that adds a quality tool without those answers has added a dashboard.

Interview Questions and Answers

Q1. What is a data quality check, as opposed to a validation comment in a pull request?

A check is a predicate with a severity and a place in the run. It either blocks the curated publish, quarantines specific rows and counts them, or warns a named person. The result is stored: pin, counts, and the publish bit. A comment that says "we should watch lesson ids" does none of that. I want the inner join, which used to drop unknown lessons silently, replaced by a rate against a pinned manifest. If the rate is over the cap, Tuesday's previous curated window stays the one consumers see.

Q2. How do you choose among fail, quarantine, and warn?

Fail when publishing would make the metric indefensible: conflicting keys, prohibited fields, a stale commit, a reconciliation miss, a quarantine rate over the agreed cap. Quarantine when a single row is unfit and the rest of the batch is still the day, and keep the row in raw. Warn when you are measuring a condition, such as a volume swing on enrollment week, and a human will look before you are willing to stop the publish. A warn with no owner and no review date is a fail you were afraid to ship. I would rather fail closed than teach consumers to distrust a green job.

Q3. Why is an inner join to the lesson dimension not a referential-integrity control?

The join removes unknown lessons from the result and returns success. The completion rate moves, and nothing records the ids or the manifest that was used. A control counts the unknowns against a named manifest, quarantines them under a cap, and fails the publish above the cap so the previous window remains. The unknown ids stay in raw for a replay after the catalog pointer moves. The join can still be the transform. It cannot be the only place the loss is visible.

Q4. What is wrong with using SELECT DISTINCT as the uniqueness check?

Distinct collapses conflicting payloads into one row, or it collapses identical retries without telling you which. The collision count is the incident. I fail the publish when one event_id or one (order_id, line_id) appears with two payloads. Identical retries are collapsed upstream and counted. The curated primary key is a backstop if the gate is bypassed. It is a poor explanation, because the job has already failed in front of the consumer.

Q5. How should freshness be defined for a daily completion publish?

As the age of the checkpoint commit in UTC, compared with the bound that says yesterday's raw file is in before the SQL replace. Event time is the wrong clock for that question. Late events are a restatement concern. A job that never wrote a checkpoint is a fail, not a fresh file. I store a timezone-aware timestamp at the lesson 14 step that runs after the bytes are durable. Checking maximum event time will either page you for legitimate lateness or hide a job that did not run.

Q6. A refund arrives for Tuesday's course purchase. What should the quality rules allow?

A new line with line_type = 'refund', a positive quantity, and a non-negative price, while the sale line remains. The net of sales and refunds is what reconciles to the ledger. Deleting the sale makes Tuesday unreproducible, and a gate that only sees today's remaining rows will not notice if the ledger also dropped it. I refuse negative quantities and payment tokens. I do not require the suite to understand every tax rule. I require the grain and the sign convention the finance owner signed.

Q7. Where should the gate sit relative to raw and curated?

After raw is durable, before the curated replace becomes readable. Raw has to survive a failed gate so you can replay. Curated should not update when the gate returns false. Consumers then see the previous version and its time, not a half-day and not a fabricated zero. Quarantine records point at raw keys. They are not a second mart. Running the check only in a dashboard means the bad window was already served.

Q8. How do you set a quarantine cap without starving a peak day or hiding a bad one?

With the metric owner, as a rate and an absolute count, on this batch's row count. I print both. Two percent is a starting point you revisit when you see real unknown-lesson rates, not a law. One unknown lesson in a hundred-row fixture is under two percent and may publish. A batch that is mostly unknown fails. I do not raise the cap during an incident to force a publish. I fix the pin or the producer and rerun. Enrollment-week volume belongs in a separate warn, not in this cap.

Q9. What can a ledger reconciliation prove, and what can it miss?

It can prove the published net sits inside a signed tolerance of a total finance already trusts, which catches a lost file or a blatant double load if uniqueness did not already fail. It misses two errors that offset, and it misses a sale booked to the wrong course when the money still adds up. I keep the uniqueness check and the course referential check beside it. The tolerance is an integer in minor currency units. "Close enough" is not a tolerance. The function receives the ledger total. It does not embed a private payments client.

Q10. How do you stop a quality job from deleting historical completions when an enrollment CDC delete arrives?

By not putting those tables on the same suite. The cohort gate checks keys, LSN order, and slot freshness, and it may remove a current-state key. The completion gate checks progress batches and a pinned dimension, and it replaces a window only when that gate passes. A delete of an enrollment is not an input to the completion predicate. Lesson 16's rule is a quality rule once someone tries to "standardize checks." I would reject a framework configuration that applies one expectation bundle to every table in the schema.

Q11. When would you not automate a fail?

When the predicate is a statistical surprise you have not given an owner or a calendar: completion fell because a holiday, a launch, or a hard lesson shipped. I emit a warn with counts. I automate fails for empty outputs, contract breaches, stale commits, and rates the owner agreed make the day unusable. A fail that is muted every Monday is worse than a warn, because the mute covers the real incident too. I also do not automate "accuracy" as a claim about a learner's mind. I automate the contracts we can recompute.

Q12. What evidence shows the gate is working a month later?

A drilled unknown-lesson rate over the cap leaves the previous curated window in place and records the manifest id. A conflicting event_id does not publish. A refund batch still contains the sale line, and the net matches the ledger inside tolerance. A file with a payment token does not publish. Quarantine counts are reviewed by a named owner, and the cap has not been raised in an incident channel. The completion SQL is not the first control. A green inner join with no gate result is the old incident, still running.

Hands-On Exercises

Exercise: Gate a progress batch and a purchase batch

Problem statement. Implement a publish decision for one progress batch and one purchase-line batch. Use your own cap and tolerance, and label them as choices.

Tasks.

1. List the predicates for progress and for purchase lines. Mark each as fail, quarantine, or warn, and name the owner.
2. Pin a lesson manifest and a course set. State what "unknown" means.
3. Implement the gate so a quarantine rate over your cap does not publish, and so accepted keys exclude quarantined rows.
4. Include a conflicting key, a stale commit time, a negative quantity, a refund that keeps the sale, and a prohibited field.
5. Write the SQL for duplicate event_id and for lesson ids missing from a named manifest.
6. State what the consumer sees when publish is false. Include the previous-version rule.
7. Add a reconciliation with an explicit tolerance. Say one error class it will not catch.
8. Explain why this suite must not apply enrollment deletes to completion facts.

Expected deliverables. The program, the predicate list, the SQL, and the consumer sentence.

Evaluation rubric.

You pass when a reviewer can point to the bit that stops the SQL replace, and to the sale line that a refund is not allowed to erase.

Lesson Summary

A quality gate is a set of predicates that can stop a curated publish. Schema, uniqueness, referential integrity against a pinned dimension, commit-time freshness, and signed business rules cover what this module can honestly automate. Failures leave the previous version served. Quarantine keeps the row in raw and counts it. A rate over the cap becomes a fail. Warn is for conditions a person will actually review.

TechLearningPro's completion replace waits on the progress gate: conflicting event ids, a stale checkpoint, prohibited fields, and too many unknown lessons do not become Monday's number. Purchase lines publish when sales remain, refunds are their own positive lines, the net sits inside a finance tolerance, and no payment token is present. An inner join is not a control. A deleted sale is not a refund.

The next lesson in this module, Data Cleansing and Transformation, is what you may do to a row you have decided to keep. Cleansing is not a place to hide a failed gate. The gate remains the publish bit.

Further Reading

• SQL primer and PostgreSQL documentation: aggregate functions — GROUP BY and HAVING for the duplicate-key check. Lesson 6 of this course is the longer treatment.
• Python dataclasses — the gate result. Primer: /learn/python.
• Joe Reis and Matt Housley, Fundamentals of Data Engineering — quality as a lifecycle undercurrent, not as a tool you bolt on after the mart exists.
• Ralph Kimball and Margy Ross, The Data Warehouse Toolkit — fact-table grain and the auditability of measures. The refund rule is that grain applied to money.
• Great Expectations documentation, if you host these predicates in that library. Map each expectation to a severity and a publish bit. Do not treat a green suite as a substitute for the pin and the cap.
• dbt tests documentation, if the business SQL already lives there. A test that only warns in a pull request is not the gate unless the job refuses to publish.
• Continue this course: Data Cleansing and Transformation, then Data Pipeline Orchestration Fundamentals.