cleandataengineering.aiData Platform, from the big picture to the code
You are here: Concerns › X4 Data quality

Data PlatformZoom level 1

Data quality: what happens to a bad row

Data from a source is never perfect. A quantity is negative, a plant code does not exist, a required value is empty. Data quality is the job of noticing this and deciding what happens next, without stopping the good data and without letting the bad data into a report.

Two simple designs both fail. If every problem stopped the load, one bad record would hold back a day of good ones. If every problem were only logged, bad rows would end up in dashboards. This platform grades each rule by severity, and the severity decides the outcome.

The picture

Follow the top row. Under it, the three places a delivery or a row can be set aside. Click any box to zoom in.

The places quality is decided

PlaceWhat is checkedIf it failsPage
LanderThe file has the contract's columns. The row count equals the count the delivery announced, and sits inside the dataset's expected band.The whole delivery is rejected (rejected_contract or rejected_volume), not loaded, and the reason is logged.Lander
Silver: duplicatesThe same business key twice inside the source.Handled by the dataset's dedup setting.Duplicates
Silver: quality stage, minor ruleA row rule graded minor.The row is kept. The rule's name is added to _dq_flags.Quality
Silver: quality stage, major ruleA row rule graded major.The row is quarantined with the rule's name as the reason. The rest of the batch is written.Quality
Silver: quality stage, critical ruleA row rule graded critical, which includes every required column being filled.The row is quarantined and the whole batch is held. Nothing is written for the dataset and the bookmark stays.Quality

The catalog's rules for this concern

RuleMust or shouldWhat it asksState
DQ-01MUSTData quality rules written with ownersa test names it
DQ-02MUSTWhat happens to a row that fails a rulea test names it
DQ-03MUSTOne poison record does not stop the whole loada test names it
DUP-03MUSTDuplicates already inside the sourcea test names it
GATE-01MUSTNothing passes by absencea test names it
LATE-02MUSTFacts arriving before their reference dataa test names it
LATE-04MUSTTime zones and business datesnothing yet
PUB-01MUSTDerived tables pass their own checks before publisha test names it
QUAR-01MUSTA released row goes through the same checks againnothing yet
SCD-04SHOULDFacts joined to the right version of historynothing yet
SEV-01MUSTA rule's severity decides what happens on failurecode only
JOIN-01MUSTA lookup join never adds or loses rowsa test names it

The state is computed from the repository: a rule is tested when a test names it, built when only code names it. Three MUST rules have nothing yet, and they are listed again under Not built yet at the end.

Request and response, function by function

Each row is one function a reader will meet. Goes in is what it is handed. Comes out is what it gives back. Where a function reports instead of returning, the last column says where.

FunctionGoes inComes outCalled by
_decide lander.py:63One delivery, its rows, the column verdict, the dataset's expected-rows band.A LandOutcome with status loaded, rejected_contract or rejected_volume, and a reason sentence.land in the same file
translate silver/contract_rules.py:34One contract rule from the metadata store: its check, column, severity, action and parameters.A stage Rule (name, SQL condition, severity, row action), or a sentence saying why it is not a row rule.contract_rules
contract_rules silver/contract_rules.py:58All the contract's rules for the dataset.Two things: the row rules, and one reason sentence for every rule left out. Nothing is dropped silently.build_silver, the silver runner
with_overrides silver/contract_rules.py:64The row rules and the manifest's severity-to-action choices.The same rules with the action replaced by the dataset's choice. A dataset may flag what the contract would quarantine.build_silver
quality stages/clean.py:216A frame of rows and the plan with all its rules.An Out of three parts: the good rows (with _dq_flags), the quarantine records, and two counts, flagged and critical.the stage runner
quarantine_rows stages/common.py:41A frame and one column holding the failure reason (empty means good).The good rows, and quarantine records: stage name, reason, batch key, source file, and the whole original row as JSON.quality and other stages
add_flags stages/common.py:96A frame and the pairs (condition, flag name).The frame with the names of failed flag rules appended to _dq_flags.quality
build_silver silver/runner.py:69A dataset's settings and its new bronze batches.A SilverDataset with status succeeded or skipped. If quality.critical is above zero it writes nothing and logs the step silver.quality as failed.run_silver, the day loop

One real run

Four sales order lines go through the quality stage with five rules in the plan: the order id and the dealer code must be filled (critical), the quantity is never negative (critical), the plant code is one of two (major), and the press cycle state is one of four known states (minor, flag only). The block below was printed by the code on a local Spark session, not typed. The code that produced it is the same stage function the daily run calls.

Printed by the stageInputs, then the stage's three outputs
GOES IN: four sales order lines
  order_id  dealer_code  plant_code  ordered_qty  cycle_state
  SO-1001   D-1001       P-CHN       40           curing
  SO-1002   D-1002       P-XXX       12           curing
  SO-1003   D-1003       P-HUN       -5           idle
  SO-1004   (empty)      P-HUN       8            resting

THE RULES IN THE PLAN (name, severity, action)
  order_id_present            critical  quarantine
  dealer_code_present         critical  quarantine
  ordered_qty-sql-1           critical  quarantine
  plant_code-invalidValues-1  major     quarantine
  cycle_state-invalidValues-1 minor     flag

COMES OUT 1: good rows (order_id, _dq_flags)
  SO-1001  []

COMES OUT 2: quarantine records (stage, reason, original row)
  quality  plant_code-invalidValues-1  SO-1002  plant P-XXX
  quality  ordered_qty-sql-1           SO-1003  quantity -5
  quality  dealer_code_present         SO-1004  dealer empty

COMES OUT 3: counts
  flagged  = 0
  critical = 2

Read it like this. SO-1001 is clean and has no flags. SO-1002 has a plant that does not exist: major, so it is quarantined. SO-1003 has a negative quantity and SO-1004 has no dealer code: both are critical, so the critical count is 2. The silver runner then holds the whole batch, which means even SO-1001 is not written until the batch is voided or resent. SO-1004's cycle state, resting, is not a known state, but the row was quarantined for the missing dealer, so no flag was added and flagged is 0. Had it been the only problem, the row would have stayed in silver with cycle_state-invalidValues-1 in _dq_flags.

The code

src/dataplatform/stages/clean.py:216The whole stage: build a failure condition per rule, sort the rules by action, quarantine, flag, count.
def quality(df: DataFrame, ctx: StageCtx) -> Out:
    """Row rules; severity sets the action (ADR-009): quarantine the row, or flag it in _dq_flags and keep it."""
    unknown = sorted({r.action for r in ctx.plan.rules if r.action not in ("quarantine", "flag", "none")})
    if unknown:  # a rule whose action the stage does not know must never be silently skipped
        raise StageError(f"{ctx.plan.table}: rule action(s) {', '.join(unknown)} are not quarantine, flag or none")
    fails = [(r, ~F.coalesce(F.expr(r.predicate), F.lit(False))) for r in ctx.plan.rules if r.action != "none"]
    held = [(cond, r.name) for r, cond in fails if r.action == "quarantine"]
    flagged = [(cond, r.name) for r, cond in fails if r.action == "flag"]
    critical = [cond for r, cond in fails if r.action == "quarantine" and r.severity == "critical"]
    good, bad = quarantine_rows(df, reasons(held), "quality")
    good = add_flags(good, flagged)
    tally = {"flagged": good.where(F.size("_dq_flags") > 0)} if flagged else {}
    if critical:  # ADR-009: any row that breaks a critical rule blocks the batch (the runner holds it)
        tally["critical"] = df.where(reduce(lambda a, b: a | b, critical))
    return Out(good, bad, tally)
src/dataplatform/silver/contract_rules.py:34One contract rule in; one stage rule or one reason out.
def translate(rule: DqRule) -> Rule | str:
    """The quality-stage rule, or the reason it is not one."""
    p = rule.params
    action = ROW_ACTION.get(rule.action)
    if action is None:
        return f"{rule.rule_id}: action '{rule.action}' has no row meaning, not translated"
    if rule.check in HANDLED_ELSEWHERE and not rule.column:
        return f"{rule.rule_id}: a table rule, kept by {HANDLED_ELSEWHERE[rule.check]}"
    if rule.check == "invalidValues" and rule.column and _IDENT.match(rule.column):
        valid = (p.get("arguments") or {}).get("validValues")
        if isinstance(valid, list) and valid and int(p.get("mustBe", 0)) == 0:
            allowed = ", ".join(_quote(v) for v in valid)
            return Rule(rule.rule_id, f"{rule.column} IS NULL OR {rule.column} IN ({allowed})", rule.severity,
                        action)
    if rule.check == "sql":
        m = _COUNT_WHERE.match(str(p.get("query") or ""))
        if m and int(p.get("mustBe", -1)) == 0:
            cond = m.group("cond")
            if _UNSAFE.search(cond):
                return f"{rule.rule_id}: the condition is not a plain row condition, not translated"
            return Rule(rule.rule_id, f"coalesce(NOT ({cond}), true)", rule.severity, action)
    return f"{rule.rule_id}: check '{rule.check}' is not a row rule this stage can run, not translated"
src/dataplatform/silver/runner.py:175The runner reads the critical count before it writes anything.
    critical = int(result.counts.get("quality.critical", 0))
    if critical:  # ADR-009: a critical rule broken holds the whole batch; the bookmark stays, so it stays blocked
        stages.release(result)                       # until the batch is voided or resent
        names = ", ".join(r.name for r in plan.rules if r.severity == "critical" and r.action == "quarantine")
        msg = (f"held: {critical} row(s) broke a critical rule ({names}); nothing written, the bookmark stays; void "
               "or resend the batch to go on (ADR-009)")
        store.log_step(StepRecord(run_id, source, dataset, "silver.quality", "failed", dict(result.counts), msg))
        store.finish_dataset_run(run_id, source, dataset, "skipped", dict(result.counts), message=msg)
        return SilverDataset(source, dataset, "skipped", dict(result.counts), reason=msg)

The full walk-through of the stage, the translation and the hold is on 9 Quality. The lander side is on Landing to bronze.

The settings that drive it

SettingWhat it doesAllowed valuesDefault
required on a contract columnMakes a critical rule that the column is filled.true or falsefalse
quality entries on a contract columnThe row rules, each with a severity.critical, major, minormajor
quality.actions.<severity>.rows in the dataset manifestThe action for that severity, laid over what the contract says.quarantine, flag, nonequarantine for critical and major, flag for minor
volume.expected_rows_per_delivery in the dataset manifestThe row-count band a delivery must fall in.min and maxno limit

How it is tested

tests/dataplatform/test_silver_stages.py has a table of cases for the stage and a test that one poison record does not stop its batch. tests/dataplatform/test_contract_rules.py has a table of rules that must translate and a table of rules that must not.

        ("a good row passes", None, 1, [], []),
        ("a missing required value is quarantined (critical)", None, None, ["v_present"], []),
        ("flagged and kept when the dataset says flag", {"actions": {"critical": {"rows": "flag"}}}, None, [],
         ["v_present"]),
Not built yet, or known gaps
  • QUAR-01: a released row should go through the same checks again. Nothing in the code does this yet.
  • LATE-04: time zones and business dates have no rule or test yet.
  • SCD-04: facts joined to the right version of history is not tested yet.
  • The data quality results table is never written in a normal run. Rule verdicts are in the run log, not in a table of their own.
  • Declarative-engine expectations are described on Declarative engine, and are separate from the stage above.