Week 2 · Clean and trust the data › Day 11

Day 11. Cleaning with Claude Code and open models

1. Why this matters

You saw AI-assisted cleaning on a real team. Done well, it turns a day of parsing work into an hour. Done badly, it produces plausible code that quietly drops rows. The difference is a workflow: the AI proposes, tests prove, you decide.

2. A safe Claude Code loop for data work

1. Context first. A CLAUDE.md in the repo: what the project is, where raw data lives, the hard rules (raw is read-only, never print secrets, every rule gets a test, propose before changing shared things).
2. Ask for a plan, not code. "Read docs/profiles/qsr_pos.md and clean/rules.py. Propose how to handle duplicated POS batches. Don't write code yet."
3. Tests before implementation. "Write failing tests for the duplicate rule using the examples in the profile."
4. Implement until green, then ask it to explain each line you don't understand.
5. Evidence. Row counts before/after every step, saved with the query that produced them.
6. Review the diff yourself. Reject anything that changes a test to make it pass.

<!-- CLAUDE.md (excerpt) -->
## Rules
- data/raw/ is read-only. Never write, move or delete there.
- Every cleaning rule lives in clean/rules.py with a docstring (source, why, date confirmed) and a test.
- Report row counts in/out for every transform. Dropped rows go to data/rejects/ with a reason column.
- Don't print values from columns named *email*, *phone*, *card*. Mask them in output.
- Propose schema changes; don't apply them.

3. LLMs for unstructured data

Some sources aren't tables: supplier invoices as PDFs, guest reviews, free-text delivery notes. An LLM can turn them into rows when you give it a schema and validate the output.

from pydantic import BaseModel, Field
from typing import Literal

class InvoiceLine(BaseModel):
    sku: str | None
    description: str
    qty: float = Field(gt=0)
    unit_price: float = Field(ge=0)

class Invoice(BaseModel):
    supplier: str
    invoice_no: str
    invoice_date: str = Field(description="ISO date YYYY-MM-DD")
    currency: Literal["CAD", "USD"]
    lines: list[InvoiceLine]
    total: float

# Local, free: Ollama with structured output
import ollama
resp = ollama.chat(model="qwen2.5:7b", format=Invoice.model_json_schema(),
                   messages=[{"role": "user", "content": f"Extract this invoice as JSON.\n\n{pdf_text}"}],
                   options={"temperature": 0})
inv = Invoice.model_validate_json(resp.message.content)
assert abs(sum(l.qty * l.unit_price for l in inv.lines) - inv.total) < 0.05, "lines don't add to total"

The same code works with the Claude API (tool use / structured output) when accuracy matters more than cost. Get the PDF text with pdfplumber or pymupdf first; send page images only when layout matters.

4. Choosing the model