cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Bronze to silver › The thirteen stages › 9 Quality

Data PlatformZoom level 3

Stage 9: quality, row rules and what their severity does

The ninth stage tests every row against the dataset's row rules. A row rule is a condition a single row must satisfy, such as a quantity that is never negative. What happens to a row that fails depends on how serious the rule is.

Two simpler designs both go wrong. If every failure stopped the load, one bad record would hold back a whole day of good ones. If every failure were only logged, bad rows would flow into reports. Grading the rules avoids both.

Required columnsfrom the contractContract row rulestranslated by the runner9 Qualityseverity sets the actionGood rowsgo onFlagged rowskept, rule name in _dq_flagsQuarantineset aside with the rule namesCritical countabove zero: the runner holds the batch
The rules come from two places on the left. A row ends in one of the first three boxes on the right. The fourth box is a count that the runner reads.

Severity to action

Every rule has one of three severities. The severity decides the action for a failing row. The table shows the defaults as the code applies them.

SeverityWhat happens to a failing rowWhat happens to the batchMeant for
criticalQuarantined.Held. Nothing of this run is written for the dataset and the bookmark stays.A failure that makes the data wrong to use at all, such as a negative order quantity.
majorQuarantined.The rest of the batch is written.A row that cannot be trusted, such as a plant code that does not exist.
minorKept. The rule's name is added to _dq_flags.Written.A blemish worth knowing about, such as an unknown cycle state on a sensor reading.

A flag and a quarantine differ in where the row ends up. A flagged row is in silver, and anyone who reads silver can filter on the _dq_flags column. A quarantined row is not in silver. It sits in the quarantine table with its reason until someone deals with it.

Where the rules come from

The plan that reaches the stage holds rules from two places.

One: every required column must be filled

src/dataplatform/silver/plan.py:213The plan makes one critical rule per required column.
def _rules(columns: tuple[ColumnPlan, ...], actions: dict[str, Any]) -> tuple[Rule, ...]:
    """Required columns are present (critical). The contract's other row rules reach this stage with the DQ engine
    (W1e); the stage applies whatever rules the plan holds."""
    action = {**QUALITY_DEFAULTS, **{k: str((v or {}).get("rows", QUALITY_DEFAULTS.get(k, "flag"))) for k, v in
                                     actions.items()}}
    bad = sorted(a for a in action.values() if a not in ROW_ACTIONS)
    if bad:
        raise PlanError(f"quality action(s) {', '.join(bad)} are not one of {ROW_ACTIONS}")
    return tuple(
        Rule(f"{c.name}_present", f"{c.name} IS NOT NULL", "critical", action["critical"])
        for c in columns
        if c.required and not c.exclude
    )
src/dataplatform/silver/plan.py:67The default action for each severity, and the three actions a row can get.
QUALITY_DEFAULTS = {"critical": "quarantine", "major": "quarantine", "minor": "flag"}
ROW_ACTIONS = ("quarantine", "flag", "none")

For each contract column that is required and not excluded, the plan makes a rule named <column>_present with the condition <column> IS NOT NULL and the severity critical. Its action is the dataset's action for critical, which is quarantine unless the manifest says otherwise.

Two: the row rules written in the contract

A contract can attach quality rules to a column. The loader stores them in the metadata store with a severity and an action that follows from the severity.

data-platform-catalog/slices/02-tyre/contracts/sales_order_line.odcs.yaml:25A critical rule in the sales order line contract: the quantity is never negative.
      - {name: "ordered_qty", logicalType: "integer", required: true, quality: [{"type": "sql", "description": "ordered_qty is never negative.", "query": "SELECT COUNT(*) FROM ${object} WHERE ordered_qty < 0", "mustBe": 0, "dimension": "accuracy", "severity": "critical"}]}
src/dataplatform/meta/port.py:149The action the store gives a contract rule, by its severity.
DQ_ACTION_OF = {"critical": "stop", "major": "quarantine", "minor": "warn"}  # ADR-009: severity sets the action
src/dataplatform/silver/runner.py:148Just after the plan is built, the runner translates the contract's rules and adds them to the plan.
    contract, untranslated = contract_rules(store.dq_rules(source, dataset))  # DQ-01: the contract's row rules run
    plan = replace(plan, rules=plan.rules + with_overrides(contract, dict(plan.quality_actions)))
    if untranslated:
        store.log_step(StepRecord(run_id, source, dataset, "silver.contract_rules", "succeeded",
                                  {"translated": len(contract)}, "; ".join(untranslated)))

The plan is frozen, so the runner makes a copy with the translated rules added after the required-column rules. Every rule that could not be translated is written to the run log under the step silver.contract_rules, with the reason. A rule is never dropped silently.

How a contract rule is translated

src/dataplatform/silver/contract_rules.py:20The patterns the translation uses, and the map from the store's actions to row actions.
_COUNT_WHERE = re.compile(r"^\s*SELECT\s+COUNT\(\*\)\s+FROM\s+\$\{object\}\s+WHERE\s+(?P<cond>.+?)\s*;?\s*$",
                          re.IGNORECASE | re.DOTALL)
_UNSAFE = re.compile(r";|--|/\*|\bselect\b|\bfrom\b|\bjoin\b|\bdrop\b|\binsert\b|\bupdate\b|\bdelete\b", re.IGNORECASE)
_IDENT = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$")
HANDLED_ELSEWHERE = {"duplicateValues": "the duplicates stage", "rowCount": "the lander's volume check"}
# The store's actions are per rule (ADR-009: critical quarantines the rows and blocks the dataset, major quarantines,
# minor keeps and flags); at row level `stop` means the rows are quarantined, the dataset block is the run's job.
ROW_ACTION = {"quarantine": "quarantine", "stop": "quarantine", "warn": "flag"}
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"
  1. Map the action. The store knows three actions: stop, quarantine and warn. For a row, stop and quarantine both mean quarantine, and warn means flag. Holding the batch for a critical rule is not the stage's job. The runner does it.
  2. Leave table rules to their owners. A rule on the whole table with no column, such as no duplicate keys or a row count above zero, is not a row rule. The function returns a sentence saying which part of the platform keeps it.
  3. Valid values. A rule with the check invalidValues and a list of valid values becomes column IS NULL OR column IN ('a', 'b'). Each value is quoted, and a quote inside a value is doubled. A missing value passes on purpose, because the required-column rule is the one that judges missing values.
  4. A counting query. A rule of type sql is accepted only in one shape: count the rows of the table where a condition holds, and the count must be zero. The pattern _COUNT_WHERE captures the condition. The row rule is then coalesce(NOT (condition), true): a row passes when the bad condition is false, and also when it cannot be decided.
  5. Refuse anything that is not a plain condition. The pattern _UNSAFE looks for a statement separator, a comment mark, or one of the words select, from, join, drop, insert, update and delete. A condition that contains one is not translated.
  6. Say why. Anything else returns a sentence naming the rule and the reason it is not a row rule.
Contract ruleBecomes this row rule
SELECT COUNT(*) FROM ${object} WHERE ordered_qty < 0, must be 0, criticalcoalesce(NOT (ordered_qty < 0), true), critical, quarantine
valid values of plant_code: P-CHN, P-HUN, majorplant_code IS NULL OR plant_code IN ('P-CHN', 'P-HUN'), major, quarantine
valid values of cycle_state on press readings, minorthe same shape of condition, minor, flag
no duplicate values of order_id, line_no, change_seqNot translated. Kept by the duplicates stage.
row count above zeroNot translated. Kept by the lander's volume check.

The stage itself

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)
  1. Refuse an unknown action. If any rule has an action other than quarantine, flag or none, the stage stops with an error. Skipping a rule it does not understand would hide a problem.
  2. Build a failure condition per rule. For every rule whose action is not none, the rule's condition is run as an expression. coalesce(..., False) makes an undecided condition count as not satisfied. The sign ~ in front turns satisfied into failed.
  3. Sort the rules into three lists. held is the rules that quarantine. flagged is the rules that flag. critical is the rules that quarantine and are critical.
  4. Quarantine. quarantine_rows sets aside every row that fails a rule in held. The reason is the names of the failed rules.
  5. Flag. add_flags appends the names of failed flag rules to _dq_flags on the rows that stay.
  6. Count the flagged. If there are flag rules, the tally flagged is the kept rows whose list of flags is not empty.
  7. Count the critical. If there are critical rules, the tally critical is the rows of the incoming frame that fail any of them. The conditions are joined with OR. This count is what the runner looks at.

There are two layers of handling for a condition that cannot be decided. A translated contract rule wraps itself so that undecided means pass. The stage's own wrapper makes undecided mean fail for any rule that does not do that. The required-column rules are never undecided.

A critical failure holds the whole batch

src/dataplatform/silver/runner.py:175The runner reads the 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)
  1. Read the count. quality.critical is the number of rows that broke a critical rule.
  2. Release. The stage result is released. The write edge is never called.
  3. Record. The step silver.quality is logged as failed, with all the counts and a message that names the critical rules. The dataset run is finished with the status skipped and the same message.
  4. Leave the bookmark. No bookmark is passed, so it stays where it was. The next run takes the same batches again and is held again, until someone acts.

Three consequences are worth spelling out. The good rows of the run are not written either. The quarantine records of the run are not written, because writing them is part of the write edge. And the hold covers every new batch of the dataset that this run picked up, because the stages ran once over all of them together.

A worked example

Four sales order lines. The contract's rules in play are the critical rule on ordered_qty, the major rule on plant_code and the required-column rule on dealer_code.

order_iddealer_codeplant_codeordered_qtyWhat the stage does
SO-1001D-1001P-CHN40Goes on.
SO-1002D-1002P-XXX12Quarantined. Reason: plant_code-invalidValues-1.
SO-1003D-1003P-HUN-5Quarantined. Reason: ordered_qty-sql-1. Counted as critical.
SO-1004missingP-HUN8Quarantined. Reason: dealer_code_present. Counted as critical.

The stage returns one good row, three quarantine records and a critical count of two. Because that count is above zero, the runner writes nothing, not even SO-1001. Had only SO-1002 been bad, SO-1001 would have been written and SO-1002 would have gone to the quarantine table.

For a flag, take a press reading whose cycle_state is resting, which is not one of the four valid states. That rule is minor. The reading is kept in silver and its _dq_flags holds [cycle_state-invalidValues-1].

The settings that drive it

SettingWhat it meansAllowed valuesDefault
The contract's required on a columnMakes a critical rule that the column is filled.true or falsefalse
The contract's quality entries on a columnThe row rules. Each has a severity.critical, major, minor. An unknown or missing severity counts as major.major
quality.actions.critical.rows in the dataset manifestThe action for the required-column rules.quarantine, flag, nonequarantine
quality.actions.major.rows and quality.actions.minor.rows in the dataset manifestThe action for the contract's rules of that severity. It lays over what the store says: a dataset may flag what the contract would quarantine.quarantine, flag, nonequarantine for major, flag for minor
data-platform-catalog/slices/02-tyre/manifest/platform.yaml:7The tyre platform manifest states the same three defaults for the whole domain.
quality_defaults:                                     # ADR-009: severity sets the action
  critical: {rows: quarantine, dataset: block, page: true}
  major: {rows: quarantine, dataset: alert_owner, page: false}
  minor: {rows: flag, dataset: digest, page: false}

This stage has no switch.

How it is tested

tests/dataplatform/test_silver_stages.py has three cases for the stage and a separate test that one bad record does not stop the good rows of its batch. tests/dataplatform/test_contract_rules.py has a table of rules that must translate and a table of rules that must not, each with the sentence expected. tests/dataplatform/test_silver_runner.py has the test for the hold.

tests/dataplatform/test_silver_stages.py:242The three stage cases. The third changes the critical action to flag in the manifest.
        ("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"]),
tests/dataplatform/test_silver_stages.py:257One bad row in three: the other two go on.
def test_one_poison_record_does_not_stop_the_load(spark):
    """DQ-03: the bad row is set aside with its reason; the good rows of the same batch go on."""
    p = plan(required=("v",))
    df = run("project", frame(spark, "id string, v long, seq long", [("a", 1, 1), ("b", None, 1), ("c", 3, 1)]), p).df
    out = run("quality", df, p)
    assert reasons(out) == ["v_present"]
    assert rows(out.df, "id") == [("a",), ("c",)]
tests/dataplatform/test_contract_rules.py:23Six rules that translate, each with the exact row rule expected.
        ("sql count-where becomes a row condition", NEG,
         Rule("ordered_qty-sql-1", "coalesce(NOT (ordered_qty < 0), true)", "critical", "quarantine")),
        ("critical 'stop' quarantines the rows (ADR-009)",
         DqRule("q-sql-1", "q", "sql", {"query": "SELECT COUNT(*) FROM ${object} WHERE q < 0", "mustBe": 0},
                "critical", "stop"),
         Rule("q-sql-1", "coalesce(NOT (q < 0), true)", "critical", "quarantine")),
        ("minor 'warn' flags the rows",
         DqRule("q-sql-2", "q", "sql", {"query": "SELECT COUNT(*) FROM ${object} WHERE q > 9", "mustBe": 0},
                "minor", "warn"),
         Rule("q-sql-2", "coalesce(NOT (q > 9), true)", "minor", "flag")),
        ("valid values become an IN list, nulls left to the required rule", PLANT,
         Rule("plant_code-invalidValues-1", "plant_code IS NULL OR plant_code IN ('P-CHN', 'P-HUN')", "major",
              "quarantine")),
        ("a condition across columns", REJECT,
         Rule("rejection_reason-sql-1",
              "coalesce(NOT ((line_status = 'rejected') <> (rejection_reason IS NOT NULL)), true)", "major",
              "quarantine")),
        ("a quote in a valid value is escaped",
         DqRule("c-invalidValues-1", "c", "invalidValues", {"arguments": {"validValues": ["o'k"]}, "mustBe": 0}),
         Rule("c-invalidValues-1", "c IS NULL OR c IN ('o''k')", "major", "quarantine")),
tests/dataplatform/test_contract_rules.py:52Six rules that do not translate, each with the words the reason must contain.
        ("table duplicates: kept by the duplicates stage", DqRule("table-duplicateValues-1", "", "duplicateValues"),
         "duplicates stage"),
        ("table row count: kept by the lander", DqRule("table-rowCount-2", "", "rowCount"), "volume check"),
        ("a subquery is not a row condition",
         DqRule("x-sql-1", "x", "sql", {"query": "SELECT COUNT(*) FROM ${object} WHERE x IN (SELECT y FROM t)",
                                         "mustBe": 0}), "not a plain row condition"),
        ("a statement separator is refused",
         DqRule("x-sql-2", "x", "sql", {"query": "SELECT COUNT(*) FROM ${object} WHERE x < 0; DROP TABLE t",
                                         "mustBe": 0}), "not a plain row condition"),
        ("a count that must not be zero is not a row rule",
         DqRule("x-sql-3", "x", "sql", {"query": "SELECT COUNT(*) FROM ${object} WHERE x < 0", "mustBe": 3}),
         "not a row rule"),
        ("an unknown check is reported", DqRule("x-range-1", "x", "range", {}), "not a row rule"),
tests/dataplatform/test_silver_runner.py:134The hold: nothing written, bookmark unmoved, the quality step failed.
def test_a_critical_rule_broken_holds_the_batch():
    """ADR-009: nothing written, the bookmark stays, the dataset is skipped (blocked) and the quality step failed."""
    store = store_with([("b1", "loaded")])
    report, stages, tables = silver(store, FakeStages(critical=2))
    assert report.datasets[0].status == "skipped"
    assert "critical" in report.datasets[0].reason
    assert tables.written == []
    assert BOOKMARK not in store.bookmarks("src", "things")
    assert stages.released == 1
    steps = {s["step"]: s for s in store.run_report("silver-1")["steps"]}
    assert steps["silver.quality"]["status"] == "failed"
Not built yet, or known gaps
  • The dataset view does not carry the domain's quality defaults, so the defaults are written in the plan code.
  • The other parts of an action in the manifest, alerting the owner and paging, are not read by this stage.
  • Only two shapes of contract rule are translated: a list of valid values, and a counting query that must give zero. Every other check is reported in the run log and not run here.
  • A list of valid values is written as quoted text even when the values are whole numbers, as for the material's rim size. The engine converts when it compares. I did not find a test for that on both engines.
  • If a dataset changes the critical action to flag, the required-column rules no longer count as critical and no batch is held for them.
  • The count quality.flagged counts kept rows with any flag at all, so it includes rows whose only flag is an emptied value from stage 4.
  • The hold message tells the operator to void or resend the batch. I did not verify how a resent batch replaces the held one. That is on the ingestion side.
  • The hold exists on the job engine only. See the two engines compared.