Data Cleanup and Schema Mapper

Convert messy CSV, TSV, JSON, or copied tables into a validated target schema without silently inventing missing data.

The result you're building

A validated target dataset plus mapping specification, reject file, quality report, provenance record, and repeatable transformation that never invents missing values or silently drops rows.

Use this guide when

• CSV, TSV, JSON, exports, or copied tables must fit a clean target schema.
• Columns, dates, identifiers, categories, and missing-value conventions disagree across sources.
• A human or agent needs deterministic transformation rather than manual spreadsheet edits.

Do not use it as a substitute for

• Guessing personal contact data, product attributes, balances, legal facts, or identifiers that are absent.
• Overwriting the only source file or losing row-level provenance.

Before you change anything

• Collect the items below first. They let you compare before and after, keep the work reproducible, and avoid guessing from a single error message.
• Immutable copy and hash of every source file.
• Target schema with types, required fields, enums, uniqueness, and null rules.
• Field-level business definitions and authoritative reference tables.
• Expected row counts and acceptable reject thresholds.
• Privacy/retention rules and a stable source-row identifier.

Understand the system before fixing it

Parsing comes before cleaning
If a quoted delimiter or encoding is read incorrectly, later transformations operate on corrupted rows. Prove record boundaries and column counts first.

Missing, empty, zero, and unknown are different
Define null semantics per field. Converting all blanks to zero or 'N/A' creates false facts.

Identifiers are strings unless arithmetic is meaningful
ZIP codes, account numbers, SKUs, and phone-like values can have leading zeros and must not be coerced to numeric.

Every transformation needs lineage
Keep source file, row, original value, rule ID, transformed value, and reject reason so the result can be audited and rebuilt.

Evidence-to-decision map

Step-by-step procedure

Work in order. Record the output after each step. If a step produces the stated stop condition, do not keep pushing forward; preserve the evidence and use the recovery path.

Step 01 — Profile raw bytes and records

Why: Spreadsheet display can hide encoding, quoting, newlines, and leading zeros.

Do: Hash the file, detect/confirm encoding and delimiter, count records and fields, and inspect samples from beginning, middle, end, and malformed rows.

sha256sum source.csv
file -bi source.csv
python3 -c "import csv; print(next(csv.reader(open('source.csv', newline=''))))"

Read the result: Stable field counts and correctly decoded text are the parsing gate.

Next: Save parsing parameters with the job.

Step 02 — Write a field mapping contract

Why: Column-name similarity does not prove equal meaning.

Do: Map source field to target field with source type, target type, normalization, null rule, validation, authority, and reject behavior. Mark unmapped fields explicitly.

Read the result: If two sources disagree, define precedence based on authority and date rather than order in the file.

Next: Version the mapping before transforming.

Step 03 — Normalize without changing meaning

Why: Cleanup should remove representation differences, not manufacture facts.

Do: Trim defined whitespace, normalize Unicode where appropriate, standardize case only for case-insensitive fields, parse declared dates/timezones, and preserve identifiers as text.

Read the result: Retain original values in lineage. Reject lossy conversions such as truncated identifiers or impossible dates.

Next: Apply reference mappings only from a versioned dictionary.

Step 04 — Resolve duplicates deterministically

Why: Dropping duplicates by first/last row hides conflicts and depends on file order.

Do: Define a canonical key, group candidates, compare authoritative timestamps and field conflicts, then merge only under explicit rules. Send ambiguous groups to rejects/review.

Read the result: Output row count equals unique accepted entities plus rejects; no source row disappears without disposition.

Next: Record survivor and contributing source rows.

Step 05 — Validate schema and business rules

Why: Syntactic type checks miss impossible or inconsistent records.

Do: Enforce required fields, types, ranges, enums, cross-field rules, uniqueness, referential integrity, and permitted nulls. Produce machine-readable errors per source row.

Read the result: Do not coerce a rejected value merely to make the file load.

Next: Set a release threshold for error rate and critical fields.

Step 06 — Reconcile and package outputs

Why: A clean-looking file can still be incomplete.

Do: Compare source/accepted/rejected/duplicate counts, totals where meaningful, and sampled transformed records. Package clean data, rejects, mapping, quality report, hashes, and command/version.

Read the result: The reconciliation equation must balance and rerun output must be deterministic.

Next: Keep source immutable.

Worked example

Evidence collected

• One spreadsheet import converted 00123 to 123.
• Price values use $1,299.00, 1299, and blank.
• Duplicate SKU rows disagree on quantity and timestamp.
• Blank price cannot be safely inferred.

Decision: SKU parsing is already lossy in one export; price and duplicate rules need explicit authority and reject behavior.

Actions taken

• Re-exported the affected source as CSV preserving text identifiers.
• Mapped SKU to string; parsed prices under declared US format into decimal cents.
• Selected quantity from the newest authoritative inventory export.
• Rejected blank-price products from publishable feed without deleting their source rows.

Why this example matters: The end result includes rejects and lineage. A file with every row forced through would be less trustworthy, not more complete.

Verify, recover, and hand off

Completion tests

• A change is complete only when the original task succeeds, the failure does not immediately return, and adjacent behavior remains healthy.
• Source hashes and parsing parameters are recorded.
• Every target field has a mapping, null rule, validation, and authority.
• No identifiers lost formatting or precision.
• Accepted, rejected, duplicate, and source counts reconcile.
• Critical totals and sampled rows match authoritative sources.
• A second run produces the same outputs from the same inputs/configuration.

Rollback or safe recovery

• Discard derived outputs and rerun from immutable source plus prior mapping version.
• Restore prior reference dictionary when a new mapping causes unexpected category changes.
• Never edit rejects into the clean file without updating the mapping/lineage record.

If the expected result does not appear

Reusable handoff record

• Save this with the project, ticket, or client delivery. It turns the work into a repeatable result instead of a one-time guess.
• Immutable source inventory and hashes.
• Versioned mapping/schema/reference dictionaries.
• Clean dataset and row-level reject file.
• Lineage/provenance and duplicate-merge record.
• Quality, reconciliation, and deterministic rerun report.

Agent delivery contract

Required inputs

Returned output

Agent refusal and escalation rules

• Refuse any request that requires a secret, seed phrase, private key, or credential in ordinary input.
• Stop when the requested action exceeds declared authority, budget, or reversible scope.
• Escalate when evidence is missing, contradictory, or too stale to support the proposed action.

Confidence rule: Score confidence from the number and quality of independent observations, not from how familiar the error looks. Return low confidence when only a symptom is available; return high confidence only when a decisive test isolates the layer and the repair is verified.

Official reference starting points

• https://www.rfc-editor.org/rfc/rfc4180
• https://json-schema.org/specification
• https://www.unicode.org/reports/tr15/