Validation that runs without you — Data Cleaning and Validation — Data Analysis

The checks you ran are not the checks you have

Six lessons of checks, and every one of them was run by a person who decided to run it. Next quarter, one of three things happens: you run them again and it takes an afternoon, someone else runs some of them, or the export goes straight into the dashboard because the deadline was Tuesday.

The third is the normal outcome. The only checks that survive are the ones that run whether or not anyone remembers them, and this lesson is how to get there.

A contract, not a script

Start by writing down what a valid export looks like, separately from the code that reads it. That statement is the contract, and everything else follows from it.

CONTRACT = {
    "name": "muac-screening",
    "key": ["child_id"],
    "columns": {
        "child_id":       {"type": "string",  "required": True},
        "commune":        {"type": "string",  "required": True, "allowed": COMMUNES},
        "screening_date": {"type": "date",    "required": True,
                           "min": "2024-01-01", "max": "2024-12-31"},
        "age_months":     {"type": "integer", "required": False, "min": 6, "max": 59},
        "sex":            {"type": "string",  "required": True, "allowed": ["f", "m"]},
        "muac_mm":        {"type": "integer", "required": False, "min": 80, "max": 220,
                           "sentinels": [-99]},
        "oedema":         {"type": "boolean", "required": False},
        "outcome":        {"type": "string",  "required": True, "allowed": OUTCOMES},
    },
    "expected_rows": (3500, 5000),
    "max_missing": {"age_months": 0.10, "muac_mm": 0.05},
}

CONTRACT <- list(
  name = "muac-screening",
  key  = "child_id",
  columns = list(
    child_id       = list(type = "character", required = TRUE),
    commune        = list(type = "character", required = TRUE, allowed = COMMUNES),
    screening_date = list(type = "Date", required = TRUE,
                          min = as.Date("2024-01-01"), max = as.Date("2024-12-31")),
    age_months     = list(type = "integer", required = FALSE, min = 6, max = 59),
    sex            = list(type = "character", required = TRUE, allowed = c("f", "m")),
    muac_mm        = list(type = "integer", required = FALSE, min = 80, max = 220,
                          sentinels = -99),
    oedema         = list(type = "logical", required = FALSE),
    outcome        = list(type = "character", required = TRUE, allowed = OUTCOMES)
  ),
  expected_rows = c(3500, 5000),
  max_missing = list(age_months = 0.10, muac_mm = 0.05)
)

Two fields there are easy to skip and are the ones that catch the surprises.

expected_rows is a range, not a number. An export with 400 rows where you expect four thousand is a truncated download or a filter someone left on, and it will otherwise be discovered when the caseload looks encouraging.

max_missing turns lesson 2’s finding into a limit. Ten percent missing age is tolerable and documented; thirty percent means a form is broken, and the difference between those two is not something you want to notice by eye.

Three severities, and only one of them stops the run

This is the design decision that determines whether the suite gets used or switched off.

Severity	Means	Effect
`error`	The file cannot be analysed as it is	Stop. Nothing downstream runs.
`warning`	Something is wrong with some rows	Flag them, continue, report the count
`note`	Worth knowing, expected to occur	Report only

A missing column is an error: every calculation after it is wrong. Seven implausible MUAC values are a warning: 4,211 rows are still analysable, and stopping the pipeline over 0.17% of them means the pipeline stops every month and somebody removes it.

Errors are about the file. Warnings are about rows. If you find yourself wanting to make a row-level check an error, what you actually want is a threshold on how many rows may fail — which is what max_missing is.

The validator

from dataclasses import dataclass


@dataclass
class Finding:
    check: str
    severity: str
    count: int
    detail: str


def validate(df, contract):
    findings = []

    missing = set(contract["columns"]) - set(df.columns)
    if missing:
        findings.append(Finding("columns-present", "error", len(missing),
                                f"missing columns: {sorted(missing)}"))
        return findings                      # nothing else is meaningful

    low, high = contract["expected_rows"]
    if not low <= len(df) <= high:
        findings.append(Finding("row-count", "error", len(df),
                                f"{len(df)} rows, expected {low}-{high}"))

    duplicated = df.duplicated(subset=contract["key"], keep=False)
    if duplicated.any():
        findings.append(Finding("key-unique", "error", int(duplicated.sum()),
                                f"rows sharing a {contract['key']}"))

    for column, spec in contract["columns"].items():
        values = df[column]
        if spec.get("required") and values.isna().any():
            findings.append(Finding(f"{column}-required", "error",
                                    int(values.isna().sum()), "required column has blanks"))
        if "allowed" in spec:
            unexpected = set(values.dropna().unique()) - set(spec["allowed"])
            if unexpected:
                findings.append(Finding(f"{column}-allowed", "error", len(unexpected),
                                        f"unexpected values: {sorted(unexpected)}"))
        if "min" in spec:
            out = (values < spec["min"]) | (values > spec["max"])
            if out.any():
                findings.append(Finding(f"{column}-range", "warning", int(out.sum()),
                                        f"outside {spec['min']}-{spec['max']}"))

    for column, limit in contract["max_missing"].items():
        share = df[column].isna().mean()
        if share > limit:
            findings.append(Finding(f"{column}-missing", "error", int(df[column].isna().sum()),
                                    f"{share:.1%} missing, limit {limit:.0%}"))

    return findings

validate <- function(df, contract) {
  findings <- list()
  add <- function(check, severity, count, detail) {
    findings[[length(findings) + 1]] <<-
      tibble::tibble(check = check, severity = severity, count = count, detail = detail)
  }

  missing <- setdiff(names(contract$columns), names(df))
  if (length(missing)) {
    add("columns-present", "error", length(missing),
        paste("missing columns:", paste(missing, collapse = ", ")))
    return(dplyr::bind_rows(findings))
  }

  if (!dplyr::between(nrow(df), contract$expected_rows[1], contract$expected_rows[2])) {
    add("row-count", "error", nrow(df), sprintf("%d rows, expected %d-%d", nrow(df),
        contract$expected_rows[1], contract$expected_rows[2]))
  }

  dup <- duplicated(df[contract$key]) | duplicated(df[contract$key], fromLast = TRUE)
  if (any(dup)) add("key-unique", "error", sum(dup), "rows sharing a key")

  for (column in names(contract$columns)) {
    spec <- contract$columns[[column]]
    values <- df[[column]]
    if (isTRUE(spec$required) && any(is.na(values))) {
      add(paste0(column, "-required"), "error", sum(is.na(values)), "required column has blanks")
    }
    if (!is.null(spec$allowed)) {
      unexpected <- setdiff(unique(stats::na.omit(values)), spec$allowed)
      if (length(unexpected)) {
        add(paste0(column, "-allowed"), "error", length(unexpected),
            paste("unexpected values:", paste(unexpected, collapse = ", ")))
      }
    }
    if (!is.null(spec$min)) {
      out <- values < spec$min | values > spec$max
      if (any(out, na.rm = TRUE)) {
        add(paste0(column, "-range"), "warning", sum(out, na.rm = TRUE),
            sprintf("outside %s-%s", spec$min, spec$max))
      }
    }
  }

  dplyr::bind_rows(findings)
}

Note the early return after a missing column. Once the shape is wrong, every subsequent finding is noise, and a validator that prints ninety findings when the real problem is one renamed column has failed at its job even though it worked.

Wire it into the read, not into a notebook cell

def load_screening(path, contract=CONTRACT):
    df = read_typed(path, contract)
    findings = validate(df, contract)

    errors = [f for f in findings if f.severity == "error"]
    for finding in findings:
        print(f"[{finding.severity}] {finding.check}: {finding.count} - {finding.detail}")

    if errors:
        raise ValueError(f"{len(errors)} validation errors in {path}")
    return df, findings

load_screening <- function(path, contract = CONTRACT) {
  df <- read_typed(path, contract)
  findings <- validate(df, contract)

  if (nrow(findings)) print(findings, n = Inf)
  if (any(findings$severity == "error")) {
    stop(sprintf("%d validation errors in %s", sum(findings$severity == "error"), path))
  }
  list(data = df, findings = findings)
}

There is now no way to read this file without validating it. That is the whole mechanism. A validation function nobody calls is documentation; a validation function inside the loader is a guarantee.

If you use a library — pandera in Python, pointblank or validate in R — they do the same thing with less code and better reporting. Use one if you can. The reason this lesson writes it out by hand is that the shape matters more than the tool, and the shape is: contract, severities, findings, loader.

The findings are an artefact, like the profile

import json
from pathlib import Path

Path("outputs/validation").mkdir(parents=True, exist_ok=True)
Path("outputs/validation/muac-2024-q4.json").write_text(
    json.dumps([f.__dict__ for f in findings], indent=2)
)

jsonlite::write_json(findings,
  here::here("outputs", "validation", "muac-2024-q4.json"),
  pretty = TRUE
)

Saved alongside the arrival profile from lesson 1, these give you something worth more than either alone: a series. Quarter on quarter, the same checks against the same file, and the movement in the counts is a data quality trend you can report without collecting anything new.

history = pd.concat([
    pd.read_json(p).assign(quarter=p.stem)
    for p in sorted(Path("outputs/validation").glob("*.json"))
])
print(history.pivot_table(index="check", columns="quarter", values="count", fill_value=0))

history <- purrr::map_dfr(
  list.files(here::here("outputs", "validation"), full.names = TRUE),
  ~ jsonlite::read_json(.x, simplifyVector = TRUE) |>
      dplyr::mutate(quarter = tools::file_path_sans_ext(basename(.x)))
)

tidyr::pivot_wider(history, id_cols = check, names_from = quarter, values_from = count)

A warning count that falls after a training visit is evidence the training worked. That is a much better use of this work than a clean table.

What “fail loudly” costs, and why it is still right

A pipeline that stops has a real cost: someone is waiting for the number, and now they are waiting for you. It is worth being honest that this is a trade rather than a free win.

The trade is favourable for one reason. A wrong number that shipped is more expensive than a report that is late, because the wrong number gets quoted, put in a proposal, and compared against next quarter — and the correction, if it ever happens, has to chase it through every document it reached.

So the rule is: stop on anything that makes the output wrong, flag anything that makes some rows wrong, and make the failure message good enough that whoever hits it at 7 a.m. can act on it without you.

[error] commune-allowed: 1 - unexpected values: ['Petite-Riviere']
[error] age_months-missing: 226 - 5.4% missing, limit 5%
ValueError: 2 validation errors in muac-screening-2024-q4.csv

Both of those tell the reader what to do next. AssertionError on line 41 does not.

What comes next

The suite now finds everything and changes nothing — by design, because every change so far has been a flag. The last lesson is what happens to those flags: the decisions, who made them, what each one did to the number, and the log that travels with the report so nobody has to ask.