GyaanStack. Blog

Articles / agentspythonprojects

An AI agent team for data cleaning: plans as JSON, pandas does the work

Four agents clean a messy CSV. A profiler, a model that plans in JSON, a whitelisted pandas executor and a validator that rejects bad plans. Real runs shown.

By Raghunath · · 23 min read

Handing a CSV to a language model with the instruction "clean this" fails in predictable ways. The model invents values for blanks, drops rows it finds odd, renames columns, and gives you no record of what it changed. The fix is to stop letting the model touch the data at all. It writes a cleaning plan as JSON, and plain pandas code decides whether that plan is allowed, runs it, and checks the result.

This post builds that as a team of four parts around a synthetic orders file with every common defect: mixed date formats, duplicate rows, misspelt city names, negative and absurd amounts, and missing values. The planner runs two ways. A scripted fake makes the walk-through repeatable, and a real local model (Qwen3 4B through Ollama) shows what a small model does with the same job. The validator rejected every plan the local model wrote, each for a specific, checkable reason.

If you want a model to explore a dataset and explain it, see the EDA agent post. For orchestration frameworks, parallel agents and human approval, see multi-agent workflow automation in Python. This post stays on one narrow job: changing data safely.

The shape of the team#

   orders_messy.csv
          |
          v
  +---------------+    profile (JSON: counts, value lists, IQR bounds)
  |   Profiler    |-------------------------------+
  |  pandas only  |                               |
  +---------------+                               v
                                         +-----------------+
          +----------------------------->|     Planner     |  model, or a scripted fake
          | feedback: what failed        |  returns a JSON |
          |                              |  list of steps  |
          |                              +-----------------+
          |                                       |
          |                                       v
          |                              +-----------------+
          |<------- rejected ------------|   Plan check    |  op on whitelist? column exists?
          |                              +-----------------+
          |                                       | ok
          |                                       v
          |                              +-----------------+
          |                              |    Executor     |  named pandas functions only,
          |                              |                 |  works on a copy, quarantines drops
          |                              +-----------------+
          |                                       |
          |                                       v
          |                              +-----------------+
          +<------- FAIL ----------------|    Validator    |---- PASS ---> clean frame + report
                                         +-----------------+

Only the planner is probabilistic. Everything else is ordinary code that gives the same answer every time, so any result can be replayed and audited. In the terms of multi-agent architecture patterns, this is a pipeline with a critic loop, where the critic is code rather than a second model.

The code below ran on Python 3.15 with pandas 3.1.1 and pytest 9.2.0, in a fresh venv. Only pandas is needed for the main pipeline; the Ollama call uses the standard library.

A dataset with known defects#

Generating the mess yourself has one big advantage over a public dataset: you know the right answer, so you can tell whether the cleaning was correct and not only whether it looked tidy.

import numpy as np
import pandas as pd

rng = np.random.default_rng(7)
n = 500
cities = ["Bengaluru", "Mumbai", "Delhi", "Pune"]
typos = {"Bengaluru": ["bengaluru", "Bangalore", "Banglore", " Bengaluru "],
         "Mumbai": ["mumbai", "Bombay", "Mumbai "],
         "Delhi": ["delhi", "New Delhi", "Dehli"],
         "Pune": ["pune", "PUNE"]}

dates = pd.Timestamp("2026-01-01") + pd.to_timedelta(rng.integers(0, 300, n), unit="D")
fmts = ["%Y-%m-%d", "%d/%m/%Y", "%d %b %Y", "%Y/%m/%d"]
city = rng.choice(cities, n)
df = pd.DataFrame({
    "order_id": np.arange(1001, 1001 + n),
    "order_date": [d.strftime(fmts[i % 4]) for i, d in enumerate(dates)],
    "city": [rng.choice(typos[c]) if rng.random() < 0.25 else c for c in city],
    "amount": rng.lognormal(7, 0.5, n).round(2),
    "quantity": rng.integers(1, 6, n).astype(float),
})
df.loc[rng.choice(n, 6, replace=False), "amount"] = [250000.0, 189000.0, -500.0, 0.0, 999999.0, -120.0]
df.loc[rng.choice(n, 40, replace=False), "quantity"] = np.nan
df.loc[rng.choice(n, 15, replace=False), "city"] = np.nan
df.loc[rng.choice(n, 5, replace=False), "order_date"] = "not a date"
df = pd.concat([df, df.sample(20, random_state=1)], ignore_index=True)  # exact duplicates
df.to_csv("orders_messy.csv", index=False)
(520, 5)
 order_id  order_date      city  amount  quantity
     1001  2026-10-11    Mumbai  550.06       4.0
     1002  07/07/2026    Mumbai 1163.84       2.0
     1003 25 Jul 2026       NaN 3715.36       4.0
     1004  2026/09/27     Delhi 1944.02       1.0

So the file has 520 rows, 20 of them exact copies, four date formats plus five junk strings, 16 spellings of four cities, six bad amounts and 40 missing quantities.

Agent 1: the profiler#

The profiler is pandas and nothing else. Its output is the only view of the data the planner gets, so what it leaves out, the planner cannot fix.

def profile(df: pd.DataFrame, max_values: int = 50) -> dict:
    out = {"rows": len(df), "duplicate_rows": int(df.duplicated().sum()), "columns": {}}
    for col in df.columns:
        s = df[col]
        info = {"dtype": str(s.dtype), "nulls": int(s.isna().sum()), "unique": int(s.nunique())}
        if pd.api.types.is_numeric_dtype(s):
            q1, q3 = s.quantile([0.25, 0.75])
            iqr = q3 - q1
            lo, hi = q1 - 1.5 * iqr, q3 + 1.5 * iqr
            info.update(min=float(s.min()), median=float(s.median()), max=float(s.max()),
                        iqr_bounds=[round(lo, 2), round(hi, 2)],
                        iqr_outliers=int(((s < lo) | (s > hi)).sum()),
                        negatives=int((s < 0).sum()))
        else:
            vc = s.value_counts()
            info["top_values"] = {str(k): int(v) for k, v in vc.head(max_values).items()}
            info["values_shown"] = f"{min(len(vc), max_values)} of {len(vc)}"
            info["sample_raw"] = [str(v) for v in s.dropna().drop_duplicates().head(8)]
        out["columns"][col] = info
    return out

Part of the output for amount:

"amount": {"dtype": "float64", "nulls": 0, "unique": 500, "min": -500.0,
           "median": 1078.955, "max": 999999.0,
           "iqr_bounds": [-507.69, 2871.33], "iqr_outliers": 24, "negatives": 2}

The IQR rule flags 24 rows. Only three are planted errors (250000, 189000 and 999999). The lower fence sits at -507.69, so the planted -500, -120 and 0 are not flagged at all, and the other 21 flagged rows are ordinary large orders from a skewed distribution. A plan that drops "IQR outliers" would throw away real revenue. That is why the plan format below uses a business threshold (amount above 50,000) and a sign check, not a statistical fence.

The first version of this profiler kept only the top 12 values per column. "Bangalore" appeared 8 times and fell outside the top 12, so the local model never saw it and never mapped it. The validator caught the leftover value on every attempt. Showing every value for low-cardinality columns, plus the values_shown count so the model knows when a list is cut, fixed it.

Agent 2: the planner, behind one method#

The planner gets the profile and, on a retry, the list of checks the last plan failed. It returns text that should be JSON. Nothing else in the system knows whether a model is involved.

class Planner:
    """Returns a JSON string: {"steps": [{"op": ..., "column": ..., ...}]}"""
    def propose(self, profile: dict, feedback: list[str] | None = None) -> str:
        raise NotImplementedError

For a repeatable walk-through, a scripted fake replays three fixed plans: one that tries to run arbitrary code, one that is valid but too aggressive, and a good one.

import json

CITY_MAP = {"bangalore": "bengaluru", "banglore": "bengaluru", "bombay": "mumbai",
            "new delhi": "delhi", "dehli": "delhi"}
DATES = ["%Y-%m-%d", "%Y/%m/%d", "%d/%m/%Y", "%d %b %Y"]

EVIL_PLAN = {"steps": [{"op": "run_python", "code": "df = df[df.amount < 1e4]"}]}
BAD_PLAN = {"steps": [
    {"op": "drop_duplicates"},
    {"op": "parse_dates", "column": "order_date", "formats": DATES},
    {"op": "drop_rows_where", "column": "quantity", "condition": "null"},
    {"op": "drop_rows_where", "column": "city", "condition": "null"},
    {"op": "drop_rows_where", "column": "order_date", "condition": "null"},
    {"op": "strip_lower", "column": "city"},
]}
GOOD_PLAN = {"steps": [
    {"op": "drop_duplicates"},
    {"op": "parse_dates", "column": "order_date", "formats": DATES},
    {"op": "drop_rows_where", "column": "order_date", "condition": "null"},
    {"op": "strip_lower", "column": "city"},
    {"op": "map_values", "column": "city", "mapping": CITY_MAP},
    {"op": "fill_missing", "column": "city", "strategy": "constant", "value": "unknown"},
    {"op": "drop_rows_where", "column": "amount", "condition": "zero_or_negative"},
    {"op": "drop_rows_where", "column": "amount", "condition": "greater_than", "value": 50000},
    {"op": "fill_missing", "column": "quantity", "strategy": "median"},
]}

class ScriptedPlanner(Planner):
    """Stands in for the model. Replays fixed plans in order, one per call."""
    def __init__(self, plans):
        self.plans, self.calls = list(plans), 0
    def propose(self, profile, feedback=None):
        plan = self.plans[min(self.calls, len(self.plans) - 1)]
        self.calls += 1
        return json.dumps(plan)

The real model call comes later. Swapping it in changes one constructor argument.

Agent 3: the executor and its whitelist#

The executor never calls exec or eval on anything the model wrote. Each allowed operation is a named Python function, and the plan can only pick among them and fill in arguments.

def _drop_duplicates(df, step):
    return df.drop_duplicates(subset=step.get("subset"), keep="first")

def _strip_lower(df, step):
    c = step["column"]
    df[c] = df[c].str.strip().str.lower()
    return df

def _map_values(df, step):
    c = step["column"]
    norm = lambda v: v.strip().lower() if isinstance(v, str) else v
    mapping = {norm(k): norm(v) for k, v in step["mapping"].items()}  # "Bangalore " == "bangalore"
    df[c] = df[c].map(lambda v: mapping.get(norm(v), norm(v)) if isinstance(v, str) else v)
    return df

def _parse_dates(df, step):
    c = step["column"]
    raw = df[c]
    parsed = pd.Series(pd.NaT, index=df.index, dtype="datetime64[ns]")
    for fmt in step["formats"]:            # first format that matches wins, per row
        todo = parsed.isna()
        parsed[todo] = pd.to_datetime(raw[todo], format=fmt, errors="coerce")
    df[c] = parsed
    return df

def _fill_missing(df, step):
    c, how = step["column"], step["strategy"]
    value = {"median": lambda s: s.median(), "mode": lambda s: s.mode().iloc[0],
             "constant": lambda s: step["value"]}[how](df[c])
    df[c] = df[c].fillna(value)
    return df

CONDITIONS = {"null": lambda s, v: s.isna(), "zero_or_negative": lambda s, v: s <= 0,
              "greater_than": lambda s, v: s > v}

def _drop_rows_where(df, step):
    return df[~CONDITIONS[step["condition"]](df[step["column"]], step.get("value"))]

OPS = {"drop_duplicates": _drop_duplicates, "strip_lower": _strip_lower,
       "map_values": _map_values, "parse_dates": _parse_dates,
       "fill_missing": _fill_missing, "drop_rows_where": _drop_rows_where}
REQUIRED = {"strip_lower": {"column"}, "map_values": {"column", "mapping"},
            "parse_dates": {"column", "formats"}, "fill_missing": {"column", "strategy"},
            "drop_rows_where": {"column", "condition"}}
ALLOWED_FORMATS = {"%Y-%m-%d", "%d/%m/%Y", "%d %b %Y", "%Y/%m/%d", "%m/%d/%Y"}

Two details came from failures. _map_values normalises the mapping keys itself, because the local model wrote keys like "Bangalore" after asking for a lowercase step, so nothing matched. And CONDITIONS holds lambdas instead of precomputed masks: the first version built all three masks eagerly and crashed when s < 0 ran on a string column.

Dates get explicit formats tried in order, not format="mixed". Here is why, on pandas 3.1.1:

s = pd.Series(['2026-10-11', '07/08/2026', '25 Jul 2026', '2026/09/27', 'not a date'])
pd.to_datetime(s, format='mixed', errors='coerce')
pd.to_datetime(s, format='mixed', dayfirst=True, errors='coerce')
[2026-10-11, 2026-07-08, 2026-07-25, 2026-09-27, NaT]
[2026-11-10, 2026-08-07, 2026-07-25, 2026-09-27, NaT]

Without dayfirst, the Indian date 07/08/2026 becomes 8 July. With it, the ISO date 2026-10-11 becomes 10 November. Neither setting is right for this file. The pandas docs warn that dayfirst is a preference, not a strict rule. A fixed list of strftime formats tried in a known order removes the guessing.

Before any step runs, a static check reads the plan:

def check_plan(plan_text: str, df: pd.DataFrame) -> tuple[list[dict], list[str]]:
    """Static checks before anything touches the data."""
    errors = []
    try:
        steps = json.loads(plan_text)["steps"]
    except (json.JSONDecodeError, KeyError, TypeError) as exc:
        return [], [f"plan is not valid JSON with a 'steps' list: {exc}"]
    for i, step in enumerate(steps):
        op = step.get("op")
        if op not in OPS:
            errors.append(f"step {i}: op '{op}' is not on the whitelist")
            continue
        missing = REQUIRED.get(op, set()) - step.keys()
        if missing:
            errors.append(f"step {i}: {op} needs {sorted(missing)}")
        col = step.get("column")
        if col is not None and col not in df.columns:
            errors.append(f"step {i}: unknown column '{col}'")
        bad_fmt = set(step.get("formats", [])) - ALLOWED_FORMATS
        if op == "parse_dates" and bad_fmt:
            errors.append(f"step {i}: date formats not allowed: {sorted(bad_fmt)}")
        if op == "drop_rows_where" and step.get("condition") not in CONDITIONS:
            errors.append(f"step {i}: unknown condition")
        if op == "fill_missing" and step.get("strategy") not in {"median", "mode", "constant"}:
            errors.append(f"step {i}: unknown fill strategy")
    return steps, errors

def execute(df: pd.DataFrame, steps: list[dict]):
    out, log, removed = df.copy(), [], []
    for step in steps:
        before = out
        out = OPS[step["op"]](out.copy(), step)
        gone = before.loc[before.index.difference(out.index)]
        if len(gone):
            removed.append(gone.assign(_reason=f"{step['op']}:{step.get('column', '*')}"))
        log.append(f"{step['op']:<16} {step.get('column', '*'):<11} rows {len(before)} -> {len(out)}")
    quarantine = pd.concat(removed) if removed else df.iloc[0:0]
    return out, log, quarantine

execute works on copies and never deletes a row outright. Every dropped row lands in a quarantine frame tagged with the step that removed it, so a human can review it later.

Agent 4: the validator#

The validator does not read the plan. It compares the raw frame with the cleaned one against rules you write once per dataset. A plan can look sensible and still do damage, so the validator judges only the output.

RULES = {
    "max_row_loss": 0.08,                 # beyond exact duplicates
    "no_nulls": ["order_id", "order_date", "city", "amount", "quantity"],
    "allowed": {"city": {"bengaluru", "mumbai", "delhi", "pune", "unknown"}},
    "ranges": {"amount": (0.01, 50000), "quantity": (1, 100)},
    "dtypes": {"order_date": "datetime64", "amount": "float", "quantity": "float"},
    "date_range": ("2025-01-01", "2026-11-22"),
}

def validate(raw: pd.DataFrame, clean: pd.DataFrame, rules=RULES) -> list[str]:
    problems = []
    if list(clean.columns) != list(raw.columns):
        problems.append(f"schema changed: {list(raw.columns)} -> {list(clean.columns)}")
    expected = len(raw) - int(raw.duplicated().sum())
    loss = 1 - len(clean) / expected
    if loss > rules["max_row_loss"]:
        problems.append(f"dropped {loss:.1%} of non-duplicate rows (limit {rules['max_row_loss']:.0%})")
    if clean["order_id"].duplicated().any():
        problems.append("order_id is not unique")
    for c in rules["no_nulls"]:
        if c in clean and clean[c].isna().any():
            problems.append(f"{c} still has {int(clean[c].isna().sum())} nulls")
    for c, allowed in rules["allowed"].items():
        bad = set(clean[c].dropna().unique()) - allowed
        if bad:
            problems.append(f"{c} has values outside the allowed set: {sorted(bad)}")
    for c, (lo, hi) in rules["ranges"].items():
        n = int(((clean[c] < lo) | (clean[c] > hi)).sum())
        if n:
            problems.append(f"{c}: {n} values outside [{lo}, {hi}]")
    lo, hi = (pd.Timestamp(d) for d in rules["date_range"])
    if pd.api.types.is_datetime64_any_dtype(clean["order_date"]):
        n = int(((clean["order_date"] < lo) | (clean["order_date"] > hi)).sum())
        if n:
            problems.append(f"order_date: {n} dates outside {rules['date_range']}")
    for c, kind in rules["dtypes"].items():
        if not str(clean[c].dtype).startswith(kind):
            problems.append(f"{c} dtype is {clean[c].dtype}, expected {kind}")
    return problems

The 8% row-loss budget is a choice for this file. Pick yours from how much data the downstream report can afford to lose, and write it down before you see any plan.

The loop that ties them together#

def run(df, planner, max_attempts=3, verbose=False):
    prof = profile(df)
    feedback = None
    for attempt in range(1, max_attempts + 1):
        print(f"\n=== attempt {attempt} ===")
        plan_text = planner.propose(prof, feedback)
        if verbose:
            print("plan:", plan_text)
        steps, errors = check_plan(plan_text, df)
        if errors:
            print("plan rejected before execution:", *errors, sep="\n  ")
            feedback = errors
            continue
        try:
            clean, log, quarantine = execute(df, steps)
        except Exception as exc:          # a step that crashes is a bad plan, not a crash
            print("execution failed:", repr(exc))
            feedback = [f"execution failed: {exc!r}"]
            continue
        print(*log, sep="\n")
        problems = validate(df, clean)
        if not problems:
            print("validator: PASS")
            print(report(df, clean))
            print("quarantined rows by reason:", quarantine["_reason"].value_counts().to_dict())
            return clean
        print("validator: FAIL", *problems, sep="\n  ")
        feedback = problems
    print("giving up; raw data left untouched")
    return None

The before/after report is a small helper:

def report(raw, clean):
    rows = []
    for c in raw.columns:
        rows.append({"column": c, "nulls_before": int(raw[c].isna().sum()),
                     "nulls_after": int(clean[c].isna().sum()),
                     "unique_before": int(raw[c].nunique()), "unique_after": int(clean[c].nunique())})
    return pd.DataFrame(rows).to_string(index=False)

Run 1: scripted planner#

=== attempt 1 ===
plan rejected before execution:
  step 0: op 'run_python' is not on the whitelist

=== attempt 2 ===
drop_duplicates  *           rows 520 -> 500
parse_dates      order_date  rows 500 -> 500
drop_rows_where  quantity    rows 500 -> 460
drop_rows_where  city        rows 460 -> 447
drop_rows_where  order_date  rows 447 -> 443
strip_lower      city        rows 443 -> 443
validator: FAIL
  dropped 11.4% of non-duplicate rows (limit 8%)
  city has values outside the allowed set: ['bangalore', 'banglore', 'bombay', 'dehli', 'new delhi']
  amount: 6 values outside [0.01, 50000]

=== attempt 3 ===
drop_duplicates  *           rows 520 -> 500
parse_dates      order_date  rows 500 -> 500
drop_rows_where  order_date  rows 500 -> 495
strip_lower      city        rows 495 -> 495
map_values       city        rows 495 -> 495
fill_missing     city        rows 495 -> 495
drop_rows_where  amount      rows 495 -> 492
drop_rows_where  amount      rows 492 -> 489
fill_missing     quantity    rows 489 -> 489
validator: PASS
    column  nulls_before  nulls_after  unique_before  unique_after
  order_id             0            0            500           489
order_date             0            0            410           246
      city            16            0             16             5
    amount             0            0            500           489
  quantity            40            0              5             5
quarantined rows by reason: {'drop_duplicates:*': 20, 'drop_rows_where:amount': 6, 'drop_rows_where:order_date': 5}

The code-execution plan never reached the data. The aggressive plan ran on a copy and failed on three separate rules. The good plan kept 489 of 500 unique orders (2.2% loss) and every removed row is in quarantine with its reason. order_date shows 0 nulls before cleaning because the five junk values were strings, not blanks; they only became visible as nulls after parsing.

Run 2: a real local model#

The same loop with Qwen3 4B Instruct, served by Ollama 0.40.16 on a laptop, temperature 0. Ollama's /api/chat accepts a JSON schema in the format field and constrains decoding to it (structured outputs docs, chat API).

import json, urllib.request
from team import Planner, ALLOWED_FORMATS

OP_GUIDE = """Allowed ops (anything else is rejected):
- drop_duplicates {}
- strip_lower {column}
- map_values {column, mapping: {old: new}}   applied after strip_lower, so keys are lowercase
- parse_dates {column, formats: [strftime strings]}  tried in order per row.
  Allowed formats: %Y-%m-%d, %Y/%m/%d, %d/%m/%Y, %d %b %Y, %m/%d/%Y
- fill_missing {column, strategy: median|mode|constant, value?}
- drop_rows_where {column, condition: null|zero_or_negative|greater_than, value?}
Target: city values in {bengaluru, mumbai, delhi, pune, unknown}; amount in (0, 50000];
quantity has no nulls; drop at most 8% of rows beyond exact duplicates. Dates are Indian (day first)."""

SCHEMA = {"type": "object", "required": ["steps"], "properties": {"steps": {
    "type": "array", "items": {"type": "object", "required": ["op"], "properties": {
        "op": {"enum": ["drop_duplicates", "strip_lower", "map_values", "parse_dates",
                        "fill_missing", "drop_rows_where"]},
        "column": {"enum": ["order_id", "order_date", "city", "amount", "quantity"]},
        "formats": {"type": "array", "items": {"enum": sorted(ALLOWED_FORMATS)}},
        "mapping": {"type": "object"}, "strategy": {"enum": ["median", "mode", "constant"]},
        "condition": {"enum": ["null", "zero_or_negative", "greater_than"]},
        "value": {}}}}}}

class OllamaPlanner(Planner):
    def __init__(self, model="qwen3:4b-instruct", url="http://localhost:11434/api/chat"):
        self.model, self.url = model, url
    def propose(self, profile, feedback=None):
        msg = f"{OP_GUIDE}\n\nProfile:\n{json.dumps(profile)}\n\nReturn a cleaning plan as JSON."
        if feedback:
            msg += "\n\nYour previous plan failed these checks; fix them:\n- " + "\n- ".join(feedback)
        body = {"model": self.model, "stream": False, "format": SCHEMA,
                "options": {"temperature": 0},
                "messages": [{"role": "user", "content": msg}]}
        req = urllib.request.Request(self.url, json.dumps(body).encode(),
                                     {"Content-Type": "application/json"})
        with urllib.request.urlopen(req, timeout=300) as r:
            return json.loads(r.read())["message"]["content"]

To use a hosted model instead, keep the class shape and replace the body of propose with your provider's call. No API key is needed for anything in this post.

The first attempt used a loose schema ("type": "string" for every field). All three plans were rejected before execution, each for the same invented format:

=== attempt 1 ===
plan rejected before execution:
  step 3: date formats not allowed: ['%d/%m/%y']
=== attempt 2 ===
plan rejected before execution:
  step 3: date formats not allowed: ['%d-%m-%Y', '%d/%m/%y']
=== attempt 3 ===
plan rejected before execution:
  step 3: date formats not allowed: ['%d/%m/%y']
giving up; raw data left untouched

The feedback named the bad format and the model wrote it again. Moving the whitelist into the schema as enum values made that error impossible to produce. After that change, plus the full value list in the profile and key normalisation in the executor, the run looked like this (step logs and plans trimmed; the validator lines are verbatim):

=== attempt 1 ===
  ... 9 steps, rows 520 -> 479
validator: FAIL
  order_date still has 5 nulls
  quantity still has 38 nulls

=== attempt 2 ===
  ... 8 steps, rows 520 -> 457
validator: FAIL
  dropped 8.6% of non-duplicate rows (limit 8%)
  order_date still has 4 nulls
  city still has 13 nulls
  city has values outside the allowed set: ['bombay']
  amount: 3 values outside [0.01, 50000]

=== attempt 3 ===
  ... 11 steps, rows 520 -> 450
validator: FAIL
  dropped 10.0% of non-duplicate rows (limit 8%)
giving up; raw data left untouched

Attempts did not improve steadily. Attempt 2 cleared the quantity nulls from attempt 1 by deleting those rows, then left the city nulls in place, dropped bombay from its mapping and stopped removing amounts above 50,000. The model's mapping in attempt 3 was correct for every city, but it chose to drop the 39 rows with missing quantity instead of filling them, which alone blew the budget. And it filled missing cities with "strategy": "mode", which turns 15 unknown cities into "pune", the most common value. The validator passed that part, which leads to the next section.

A 4B model on a laptop is a weak planner for this job. That is useful to know: the safety of the system did not depend on the model being good, and the raw file was never modified.

What the validator cannot catch#

The rules check the shape of the output. They cannot tell a correct value from a plausible wrong one, and this run had both kinds:

  • Mode imputation on city writes a valid value that is wrong for most of those rows. Every rule passes. If the city split matters downstream, add a rule that the share of each city may move by at most a point or two after cleaning.
  • Of the 124 unique rows in dd/mm/yyyy form, 64 have a day of 12 or less, so %m/%d/%Y would also parse them, silently, to a different date. Both are in the allowed list. A plan that puts the US format first passes every check. The only defence is knowing the source: remove %m/%d/%Y from the whitelist for Indian data, or check parsed dates against a second field such as a ship date.
  • Six bad amounts were planted, and the good plan quarantines exactly six. On real data you will not know the true count, which is why the quarantine table exists.

Testing the team#

Every bad plan you meet in production becomes a regression test. Three tests cover the main promises: known plans keep their verdicts, unknown columns are refused, and the raw frame is never mutated.

def outcome(plan):
    steps, errors = check_plan(json.dumps(plan), df)
    if errors:
        return "rejected"
    clean, _, _ = execute(df, steps)
    return "pass" if not validate(df, clean) else "fail"

def test_known_plans():
    assert outcome(GOOD_PLAN) == "pass"
    assert outcome(BAD_PLAN) == "fail"
    assert outcome(EVIL_PLAN) == "rejected"

def test_unknown_column_is_rejected():
    assert outcome({"steps": [{"op": "strip_lower", "column": "town"}]}) == "rejected"

def test_raw_frame_is_never_modified():
    before = df.copy()
    execute(df, check_plan(json.dumps(GOOD_PLAN), df)[0])
    pd.testing.assert_frame_equal(df, before)
...                                                                      [100%]
3 passed in 0.76s

For the model side, save each real plan the local model writes, with the profile it saw, and score them against the validator as a fixed set. That gives a pass rate you can compare across models and prompt changes instead of judging one run by eye.

Failure modes, in order of how often they showed up here#

  1. Invented arguments (date formats, conditions). Fixed with enum in the decoding schema, kept as a check in code too.
  2. Missing context. A truncated value list hid "Bangalore". Fixed in the profiler.
  3. Format mismatch between steps. The model wrote mixed-case keys for a lowercase column. Fixed by normalising in the executor.
  4. Over-deletion. Dropping rows is the easiest way to make nulls go away. Caught by the row-loss budget.
  5. Plausible wrong values (mode fill, ambiguous dates). Not caught by shape rules; needs distribution checks or domain rules.
  6. Executor crashes on a bad combination of arguments. Caught by the try around execute and returned to the planner as feedback.

The next step for this design is to raise the planner's pass rate with a bigger model or a few example plans in the prompt, and to add a distribution rule for city. The whitelist, quarantine and validator stay as they are.

Sources#