awb-clean

Clean one dataset

Run this skill's commands through the awb MCP tools when the host offers them, as the project's AGENTS.md Commands maps them; the command lines below are the fallback.

Work on one dataset at a time: a canonical view, a publication, or a landed acquisition. This skill and its references hold the data and analysis guides' cleaning rules, so a cleaning pass reads neither guide. Corrections go into view definitions or a new publication; landed originals and existing publications are never modified.

Run the bundled scripts as black boxes, by path, with README's Project interpreter (the scan needs duckdb). A typical pass is six tool calls: scan, read the view definition and the view's catalog row in one call, edit the view, rescan, stale, record.

1. Scan

python3 <skill-directory>/scripts/scan_dataset.py <project-root> <dataset> [--key COLS] [--ref COL=TARGET.COL] [--range COL=MIN:MAX] [--columns COLS] [--lineage COLS]

<dataset> is usually the canonical view; --help lists the other targets. Pass the grain from the dataset's catalog row (the view's row for a canonical view) as --key when you know it, as one comma-separated value for a composite grain; otherwise the scan proposes candidate keys. Each --key is checked as a separate key, so repeat it only to test alternative keys. Pass the joins the question uses as --ref, and known valid ranges and the reporting period as --range; --ref and --range are each repeatable. For a wide dataset, --columns limits per-column checks to the columns in scope. For a dataset combining sheets, files or partitions, pass the columns saying where each row came from, part column first, such as sheet,sheet_row, as --lineage unless foundation/datasets.toml declares them; the scan separates duplicate-rows:across-parts from remaining duplicate-rows, ignoring lineage.

Stdout prints the scan path (its stem is <scan-name> in step 6) and lists up to 25 unrecorded issues as S<n> with key, count, and three examples; more_issues counts the rest, which are in the scan file's issues. Open the scan file only for those, or when a decision needs more than three examples. Issues under recorded already have a quality row: reopen one whose status is corrected or whose count differs from its recorded_count; leave the rest as recorded. Read checks.md when a likely false positive or a check's threshold bears on a decision.

2. Decide

The active question bounds the work: decide issues in the columns the active investigation uses, unless the user asked for broader cleaning; the rest stay in the scan file. Put each in-scope issue in one class; the keys are the checks each class usually takes:

• Shared cleaning rule (whitespace, variants, near-variants, sentinel, parse, date-formats, text-typed, duplicate-rows:across-parts, duplicate-rows, duplicate-key, ancient-date, range as a validity rule): an error in the data itself, wrong for every investigation, such as a placeholder meaning missing. Both duplicate issues are repeats to explain, not known copies: propose their treatment without asserting why they repeat.
• Shared limitation (null-key, orphans, all-null): a real defect no rule can fix, such as orphans from a partial extract, null keys, or a column the source never fills. Record it with status open; the view stays as it is.
• Investigation-local choice (range as the reporting period, extreme): correct data the question chooses to exclude or treat specially, such as test accounts, a period, or real outliers. Name the exclusion for that investigation's settings in the report. When the property is worth knowing project-wide, also record it with treatment "none in the foundation; investigations decide".
• Not an issue (often negative, future-date, extreme): real values that only look odd, such as refunds or due dates. Record one only when a user decision or a non-obvious reason would otherwise be asked again.

Decide representation fixes yourself: whitespace, text-typed, case- or punctuation-only variants, a clear parse format error, a date-formats mix with one reading, duplicate-rows under a recorded grain with no lineage column in it (repeats then share one grain key), and duplicate-rows:across-parts when the user or source documentation says the parts are re-extracts, keeping one part's copy — except in code or identifier columns, where case, padding, and numeric form can carry meaning. Ask when examples or source context leave meaning ambiguous. Record all-null as an open limitation yourself. The user decides everything else: placeholder meanings (missing, zero, or real), canonical labels, other repeated rows or keys and which row wins, whether other parts' shared rows are copies (part names can be data, such as stores or months), out-of-range values, meaningful parsed values such as <5, orphans, null keys, and ambiguous day-month order. Ask all of these in one message, as a table: issue, observation with count and examples, proposed treatment, alternative. Ask sequentially only when one answer changes later questions, such as the grain deciding which repeated keys are issues.

When duplicate-rows:across-parts spans more than two parts, count each pair's shared rows on non-lineage columns in a saved query run by python3 src/awb.py sql <path>. Rows shared by parts the user or the source's documentation calls re-extracts are yours to remove; an overlap you observe, or what you already know of a dataset, is not the source saying so. Otherwise ask, for example: "Sheets north and south share 12 identical rows. Are they exports of the same rows, or separate stores whose rows happen to match?"

An in-scope problem the scan missed, such as one a saved query shows, is decided the same way and recorded as a new issue. Decide is done when every in-scope S<n>, including any beyond the 25 that stdout lists, has a class and every question asked has an answer or is recorded as open.

3. Correct

Implement accepted shared rules in the canonical view under foundation/views/ that consumers already read. Correct values and keep rows, except rows invalid as a rule, such as exact duplicates or a decided key rule's losing rows. Read correction-views.md before writing a pattern new to this project's views, or when a view cannot express the correction (the publish branch). If the pass changed no view or publication, skip steps 4 and 5.

4. Confirm

python3 <skill-directory>/scripts/scan_dataset.py <project-root> <dataset> --rescan

Every scan after the edit uses --rescan, which keeps the pre-cleaning baseline and its options; a plain scan starts a new baseline. Stdout reports each issue's before and after counts, the row count, and null-rate and type changes. Expect addressed issues resolved, rows unchanged unless duplicates were removed, and null increases that match placeholders converted to null. Explain any other change before going on. Use python3 src/awb.py sql on a saved query only when the delta leaves a change unexplained.

5. Flag affected findings

Run python3 src/awb.py stale. Flag the findings whose stale entry names the corrected view or publication, or a view built on it; findings stale lists for other reasons keep their status. runtime_mismatches flag nothing; report them as a rerun decision for the user. A version mismatch on every result at once usually means run.py recorded them under an interpreter other than README's Project interpreter, not an upgrade; say so in the report. A requirements not read entry in an entry's details ends with a line whose package version went unrecorded, not a change; report it with that line, quoted as given.

6. Record

Record decided issues, corrections, flags, and catalog changes in one call:

python3 <skill-directory>/scripts/record_cleaning.py <project-root> <scan-name> <<'EOF'
{"issues": {"S1": {"impact": "...", "treatment": "...", "status": "corrected",
                   "correction": "rule and rationale", "change": "`foundation/views/02_orders.sql` trims dept"},
            "S4": {"impact": "...", "treatment": "...", "status": "open"}},
 "new": [{"object": "view `orders`", "observation": "March has no west-region orders", "status": "open",
          "evidence": "investigations/churn/exploration/march-gap.sql", "impact": "...", "treatment": "..."}],
 "flags": [{"investigation": "churn", "result": "r-003", "reason": "{S1} merges department labels"}],
 "catalog": {"name": "orders", "cells": {"Preparation rules": "...", "Quality constraints": "..."}}}
EOF

Flags go through the project's src/provenance.py state flag helper. A helper predating findings.json refuses before writes; run awb-init repair (install_helpers.py --upgrade).

When the pass changed a view or publication and stale named no affected finding, add "findings": "none: stale listed no finding reading <view>". The script fills new rows' IDs, observations, counts, and markers, keeps scan links and recorded counts and dates in foundation/quality.json, fills flagged statuses, and turns {S1} into that issue's quality ID and {N1} into the first new issue's; new IDs are DQ-NNN unless the record uses another prefix. A new issue's evidence is a saved query's path or a reference in words; it takes correction and change like a scan issue, and its row has no scan link. Reopening updates judgment cells and the link; the observation stays as first written. A decision on a recorded issue the scan no longer lists uses its quality ID, such as "DQ-004", as the key; it revises judgment cells while keeping the observation and link. To correct or withdraw what a recorded observation claims, add "amend": {"observation": "<corrected text>", "reason": "<why>"} to its decision; the row keeps the replaced text after was:. {DQ-004} placeholders also resolve. To cite a declared publication check that establishes a row, such as a key check confirming a decided grain, add "check": {"publication": "data/parquet/<dataset>/<publication-id>/", "name": "key:order_id"} to its decision; the script confirms that publication.json records the check and appends the citation to Current treatment. Record shared vocabulary in the glossary yourself.

Read the existing catalog row before setting any of its cells; when a publication row shares the dataset's name, record_cleaning.py sets the view's row (Kind canonical view), so read that one. A cell you set replaces the whole cell: give its complete new text, keeping what still holds and citing quality IDs for rationale.

Report

Report the dataset, the decided issues by class, the corrections and changed views, the rescan's before-and-after counts, the recorded issues in plain words, their quality IDs kept in the files, each flagged finding with its reason, the in-scope issues still open, and any exclusion named for an investigation's settings. Say that no analysis was rerun. Commit foundation/quality.json together with foundation/quality.md when you commit as README's Commits: line allows, never while its Code history: line says none; the JSON holds no source values. When foundation/sources.md restricts copying the source's values, confirm before committing foundation/quality.md: its rows quote up to three example values.