How to Validate a CSV File Before You Trust the Data

You can only trust a CSV file you have checked. Most files arrive from somewhere else, an export button, a nightly job, a partner's SFTP drop, and what you know about them is what the sender promised. A load that runs on a promise fails in one of two ways. Either it stops on an error in row 140,000 after three minutes, which is annoying, or it finishes without a word and puts a wrong number in a dashboard, which is expensive. Validation is the step that makes the first outcome the only one. It runs before anything is loaded, it reads the file without changing it, and it answers one question: is this the file we agreed to receive?

This guide builds a validator in five layers, runs it against a good file, a file with eight planted faults and a file that was cut off in transit, and explains what each layer is for. The ten articles after it in the series each take one check and go deep. It follows the cleaning series, which is about repairing what you can repair; validation is about deciding whether to proceed with what is left.

Validation is not cleaning

The two jobs sit next to each other and are easy to confuse. Cleaning changes data: it trims, converts, merges and drops. Validation never changes anything. It reads the delivery and says yes or no, with the evidence. The distinction matters because the two have different failure modes. A cleaner that guesses wrong silently alters your data. A validator that guesses wrong either blocks a good file, which someone will notice, or passes a bad one, which nobody will. So a validator should be strict, boring and explainable. It should never fix a value "to be helpful", and every message should say what rule failed and where.

Run validation twice in a pipeline. Once on what arrives, to decide whether to accept the delivery. Once on what you produce, to decide whether to publish it. The same rules usually apply to both, which is a good reason to keep them in one place.

Five layers, in order

The layers are ordered by cost and by dependence. A cheap check that fails makes the expensive ones meaningless, so the validator stops as soon as a layer says the file cannot be read as a table at all.

Bytes first, because everything else assumes the file decoded. Structure second, because a row with the wrong number of fields has no meaningful columns to check. Values and keys last, and only on the rows that survived structure, so one ragged row does not produce a thousand confusing messages about misplaced fields.

The validator

The whole thing is one Python file. It reads the file once as bytes, parses it with the standard csv module, and uses pandas for the column checks. The rules are data at the top of the file, one pattern per column, so that changing a rule is a one line edit and the rules can be read by someone who does not write Python:

"""Validate a CSV delivery in layers: bytes, structure, schema, values, keys. Exit 0 clean, 1 warnings only, 2 errors."""
import csv, collections, hashlib, io, re, sys, time
from datetime import date

import pandas as pd

EXPECTED = ["id", "first_name", "last_name", "email", "phone", "phone_unparsed", "signup_date",
            "balance", "balance_currency", "units", "country", "status"]
RULES = {
    "id": r"\d+",
    "email": r"[^@\s]+@[^@\s]+\.[^@\s]+",
    "phone": r"|\+1\d{10}",
    "signup_date": r"|\d{4}-\d{2}-\d{2}",
    "balance": r"|-?\d+\.\d{2}",
    "balance_currency": r"|USD|EUR",
    "units": r"|-?\d+",
    "country": r"|US|IT|DE|BR",
    "status": r"active|pending|inactive",
}
ALLOWED_FIRST_LINES = 3


class Report:
    def __init__(self):
        self.items = []

    def add(self, level, layer, what, lines):
        self.items.append((level, layer, what, sorted(lines)))

    def print(self):
        for level, layer, what, lines in self.items:
            shown = ", ".join(map(str, lines[:ALLOWED_FIRST_LINES])) + (" ..." if len(lines) > ALLOWED_FIRST_LINES else "")
            print(f"{level:5} {layer:9} {what}" + (f"  [{len(lines)} rows; lines {shown}]" if lines else ""))

    @property
    def code(self):
        levels = {i[0] for i in self.items}
        return 2 if "ERROR" in levels else 1 if "WARN" in levels else 0

Each finding has a level, a layer, a message and the file line numbers of the rows involved. Levels are ERROR (do not load) and WARN (load, but tell somebody). The exit code is the summary of the levels: 0 for clean, 1 for warnings only, 2 for errors. A scheduler or a CI job needs nothing else.

Line numbers deserve a note. A pandas row index counts from 0 and skips the header, so the line in the file is the index plus two. That is a small thing that becomes expensive when it is wrong, because the person you send the report to opens the file in an editor and goes to the line. This validator reports editor lines throughout. The script counts records, not editor lines, so in a file with quoted newlines a reported number is the record number plus one, and it drifts from the editor's line numbers after the first embedded newline. The counting article shows the difference.

The bytes and structure layers:

def validate(path, expect_rows=None, expect_sha=None):
    t0 = time.perf_counter()
    r = Report()
    raw = open(path, "rb").read()

    # 1. bytes
    if expect_sha and hashlib.sha256(raw).hexdigest()[:16] != expect_sha:
        r.add("ERROR", "bytes", "checksum differs from the manifest", [])
    if raw.startswith(b"\xef\xbb\xbf"):
        r.add("WARN", "bytes", "starts with a UTF-8 byte order mark", [])
    try:
        text = raw.decode("utf-8")
    except UnicodeDecodeError as e:
        r.add("ERROR", "bytes", f"not valid UTF-8 (byte {e.start})", [])
        return r, time.perf_counter() - t0
    if not text.endswith("\n"):
        r.add("ERROR", "bytes", "last line has no line terminator (a cut-off file ends like this)", [])
    if "\r\n" in text:
        r.add("WARN", "bytes", "CRLF line endings", [])

    # 2. structure
    rows = list(csv.reader(io.StringIO(text, newline="")))
    header, body = rows[0], rows[1:]
    if header != EXPECTED:
        r.add("ERROR", "structure", f"header differs: missing {sorted(set(EXPECTED) - set(header))}, unexpected {sorted(set(header) - set(EXPECTED))}", [])
        return r, time.perf_counter() - t0
    width = collections.Counter(len(x) for x in body)
    bad_width = [i + 2 for i, x in enumerate(body) if len(x) != len(EXPECTED)]
    if bad_width:
        r.add("ERROR", "structure", f"rows not {len(EXPECTED)} fields wide (widths {dict(width)})", bad_width)
    if expect_rows is not None and len(body) != expect_rows:
        r.add("ERROR", "structure", f"{len(body)} data rows, manifest says {expect_rows}", [])

The checksum comes first, and only when the manifest supplied one. After that the bytes are decoded as strict UTF-8, which fails on the first invalid sequence and reports its position instead of replacing it with ?. Then the two cheap tests that catch a cut-off file (the closing line terminator is part of the format described in RFC 4180, although the RFC allows a file to omit it on the last record, which is why a missing one is a finding here and not a parse error): does the text end with a newline, and does every row have the same number of fields as the header. Then the rest of the layers:

    # 3-5. schema, values, keys on the rows that have the right width
    ok = [(i + 2, x) for i, x in enumerate(body) if len(x) == len(EXPECTED)]
    df = pd.DataFrame([x for _, x in ok], columns=EXPECTED, dtype=str)
    df.index = [n for n, _ in ok]
    for col, pat in RULES.items():
        fail = df.index[~df[col].str.fullmatch(pat)]
        if len(fail):
            r.add("ERROR", "values", f"{col} does not match {pat!r}", fail)
    d = df["signup_date"]
    shaped = d.index[d.str.fullmatch(r"\d{4}-\d{2}-\d{2}")]
    parsed = pd.to_datetime(d[shaped], format="%Y-%m-%d", errors="coerce")
    notreal = parsed.index[parsed.isna()]
    if len(notreal):
        r.add("ERROR", "values", "signup_date has the shape of a date but is not one", notreal)
    future = parsed.index[parsed > pd.Timestamp(date(2026, 1, 1))]
    if len(future):
        r.add("WARN", "values", "signup_date after 2026-01-01", future)
    for key in ("id", "email"):
        dup = df.index[df[key].duplicated(keep=False)]
        if len(dup):
            r.add("ERROR", "keys", f"{key} is not unique", dup)
    return r, time.perf_counter() - t0

Value rules use fullmatch, so the whole value must match its pattern; match would accept 2025-03-14 and more, as the pattern-checking article showed. A date gets two tests: the shape, then a real parse with format="%Y-%m-%d", because 2025-13-45 has the shape of a date and is not one. Uniqueness is tested on id and on email, with keep=False so that both members of a pair of duplicates are reported. Reporting only the second one tells you something is wrong and sends you looking for the first.

Running it on a good file

The good file is the cleaned customer export, 193,708 rows, checked against the row count and checksum the cleaning step recorded:

python /batches/csv-0001-0200/lab/val/validate.py val/customers.csv rows=193708 sha=8411365bdfdabc3b; echo "exit status: $?"

0 findings in 1.10s, exit 0
exit status: 0

No findings, exit status 0, about a second. A validator that finds nothing on a clean file is only half tested, but it is the half that matters most in production: a validator that cries wolf on good deliveries gets switched off. So when the rules are first written, run them on a file you know is good and read every finding as a bug in a rule, not a problem in the file.

Where did the rules come from? Not from imagination. I took the allowed values from the data itself, with a grouped count:

duckdb -box -c "SELECT status, count(*) AS n FROM read_csv('val/customers.csv', all_varchar=true) GROUP BY 1 ORDER BY 2 DESC"
duckdb -box -c "SELECT country, count(*) AS n FROM read_csv('val/customers.csv', all_varchar=true) GROUP BY 1 ORDER BY 2 DESC"

┌──────────┬───────┐
│  status  │   n   │
├──────────┼───────┤
│ active   │ 97198 │
│ inactive │ 64154 │
│ pending  │ 32356 │
└──────────┴───────┘
┌─────────┬───────┐
│ country │   n   │
├─────────┼───────┤
│ US      │ 97186 │
│ DE      │ 38603 │
│ IT      │ 38574 │
│ BR      │ 19345 │
└─────────┴───────┘

Three statuses and four countries. The validator's rules say exactly that, and nothing else is allowed. This is the trade-off in an enumeration. A strict list catches actve and Atlantis, and it also rejects a fifth country the day the business opens a new market, with a message that says so. That rejection is correct behavior: somebody has to decide that the new value is legitimate and add it to the contract. A lenient rule would have let actve through for the same reason. Pick strict, and keep the list where the data owner can see it.

Running it on a file with eight faults

I planted one fault of each kind in a copy of the file, at known lines:

cat val/customers-bad.faults.txt
python /batches/csv-0001-0200/lab/val/validate.py val/customers-bad.csv; echo "exit status: $?"

102: email
202: status
302: date
402: duplicate id
502: extra field
602: short row
702: country
802: units
ERROR structure rows not 12 fields wide (widths {12: 193706, 13: 1, 10: 1})  [2 rows; lines 502, 602]
ERROR values    email does not match '[^@\\s]+@[^@\\s]+\\.[^@\\s]+'  [1 rows; lines 102]
ERROR values    units does not match '|-?\\d+'  [1 rows; lines 802]
ERROR values    country does not match '|US|IT|DE|BR'  [1 rows; lines 702]
ERROR values    status does not match 'active|pending|inactive'  [1 rows; lines 202]
ERROR values    signup_date has the shape of a date but is not one  [1 rows; lines 302]
ERROR keys      id is not unique  [2 rows; lines 401, 402]
7 findings in 0.96s, exit 2
exit status: 2

The top eight lines are the answer key: line 102 has a bad email, 202 a misspelled status, 302 an impossible date, 402 a repeated id, 502 an extra field, 602 a short row, 702 a country that is not in the list and 802 a units value of 12x. The validator reports seven findings, because two of the faults are the same finding: both ragged rows are reported together as "rows not 12 fields wide", with the widths it saw (193,706 rows of width 12, one of 13, one of 10) and the lines, 502 and 602. Every line number matches the answer key. The duplicate id is reported at lines 401 and 402, since both rows of the pair are named and the planted fault was the second.

Three details of that output are worth noticing.

The ragged rows did not generate noise. The two rows with the wrong number of fields were removed before the value layer ran. Had they been kept, the extra field would have shifted nothing in line 502 (the extra is at the end) but the short row on line 602 would have lost its last two columns, and the validator would have reported a missing status and country as value failures on top of the structure failure. One root cause should produce one message.

The date check did two jobs. The signup_date rule, |\d{4}-\d{2}-\d{2}, passed 2025-13-45, and the parse step caught it. That is the same lesson as the pattern article: shape and validity are different questions, and a regular expression only answers the first.

Everything has a level. Had a signup date been after the cutoff in the script, it would have been a WARN, which loads the file and notifies. Whether a rule is an error or a warning is a decision about consequences. The rule of thumb I use: if loading the row would produce a wrong answer downstream, it is an error. If it would only look odd, it is a warning.

The file that was cut off

The most common failure of a transferred file is not corruption but truncation: a connection drops, a disk fills, a job is killed. I cut the last 37 bytes off the good file, which leaves a final row that stops in the middle of its fields:

python /batches/csv-0001-0200/lab/val/validate.py val/customers-truncated.csv rows=193708 sha=8411365bdfdabc3b; echo "exit status: $?"

ERROR bytes     checksum differs from the manifest
ERROR bytes     last line has no line terminator (a cut-off file ends like this)
ERROR structure rows not 12 fields wide (widths {12: 193707, 7: 1})  [1 rows; lines 193709]
3 findings in 0.98s, exit 2
exit status: 2

Look at what is missing from that output. There is no "row count differs" finding. The truncated file has 193,708 data lines, exactly as many as the manifest says, because a half-written row is still a line. A check that compares the number of rows and nothing else passes this file, and a load of it succeeds, with a final customer whose balance, country and status are gone. The checks that caught it were three: the checksum differed, the text did not end with a newline, and line 193,709 had seven fields where twelve were expected. The truncation article covers the variations, including a cut that lands exactly between two rows and so leaves a file that is valid but short.

That is the case for layers. Any one of the three checks would have done, and which of them fires depends on where the cut falls. Together they cover the ways a file can end badly.

The change that only a checksum finds

A second manifest test. In a copy of the good file I changed one byte on row 4, adding a Z to a first name:

cp val/customers.csv /tmp/redelivered.csv && sed -i '5s/^\([0-9]*\),/\1,Z/' /tmp/redelivered.csv && (diff val/customers.csv /tmp/redelivered.csv | head -4)
python /batches/csv-0001-0200/lab/val/validate.py /tmp/redelivered.csv rows=193708 sha=8411365bdfdabc3b; echo "exit status: $?"

5c5
< 4,Di,Chen,[email protected],+13207246850,,2025-03-16,14453.63,USD,599,US,pending
---
> 4,ZDi,Chen,[email protected],+13207246850,,2025-03-16,14453.63,USD,599,US,pending
ERROR bytes     checksum differs from the manifest
1 findings in 1.18s, exit 2
exit status: 2

The only finding is "checksum differs from the manifest". Every value rule still passed, because ZDi is a perfectly good first name by any pattern, and the row count, header and keys were untouched. Content validation cannot see a change that stays inside the rules. The checksum can, and it can say only that something changed, not what. That is the right level of detail: the next step for the person reading the report is to ask the sender for the file again, which is the correct response to corruption, and not to diff two files by hand.

This depends on the sender publishing a checksum, which brings in the contract: a manifest listing each file's name, byte size, SHA-256 and row count. The contract article describes what to ask for. If the provider gives you none, record one yourself at the moment of receipt. It cannot prove the file was right when sent, but it proves it has not changed since, which is what you need to make a rerun repeatable.

A second opinion in SQL

It is worth checking the same file with a different tool, because two independent implementations rarely share a bug. DuckDB reads the faulty file as text and counts:

duckdb -box -c "
SELECT count(*) AS rows, count(DISTINCT id) AS ids, count(DISTINCT email) AS emails,
       count(*) FILTER (WHERE NOT regexp_full_match(email, '[^@\s]+@[^@\s]+\.[^@\s]+')) AS bad_email,
       count(*) FILTER (WHERE status NOT IN ('active','pending','inactive')) AS bad_status
FROM read_csv('val/customers-bad.csv', all_varchar=true, ignore_errors=true)"

┌────────┬────────┬────────┬───────────┬────────────┐
│  rows  │  ids   │ emails │ bad_email │ bad_status │
├────────┼────────┼────────┼───────────┼────────────┤
│ 193706 │ 193705 │ 193706 │ 1         │ 1          │
└────────┴────────┴────────┴───────────┴────────────┘

It reports 193,706 rows, 193,705 distinct ids, one bad email and one bad status. The duplicate id shows as 193,705 distinct ids against 193,706 rows, and the email and status faults are found too. The row count is the surprise. The file has 193,708 data rows, and DuckDB returned 193,706 because ignore_errors=true skipped the two ragged rows without saying so. That option is the right one for a quick look at a file that does not parse, and the wrong one for validation. For validation, leave it off, let the reader fail, and treat the failure as the finding. The DuckDB bad-rows article shows what it reports when you ask it to keep the rejects.

Use SQL when the file is too large for memory or the checks are easier to write as queries, such as references to a lookup table (the lookup article). Use Python when you want one report with line numbers across all the layers. Either is fine as long as the layers and the answer key exist.

Running it as a gate

A validator earns its place when the load cannot start without it. The simplest wiring is the shell's own logic: run the check, and continue only on success. This loop does that for the good file and the faulty one:

for f in customers.csv customers-bad.csv; do
  if python /batches/csv-0001-0200/lab/val/validate.py val/$f >/tmp/report.txt; then echo "$f: loading"; else echo "$f: stopped, exit $?, $(tail -n 1 /tmp/report.txt)"; fi
done

customers.csv: loading
customers-bad.csv: stopped, exit 2, 7 findings in 1.23s, exit 2

The good file went on to "loading". The faulty file stopped with exit status 2 and the last line of its report, which is what a job log should show: the decision and the headline, with the full report saved beside it. The one-line summary matters more than it seems, since it is what appears in the alert. "7 findings, exit 2" tells the person on call that this is a data problem and not an outage, and the report file tells them where to look.

Keep the reports. A validation report for every delivery, stored under the file's checksum, is a record of the source's quality over time. When the number of findings per delivery goes from 0 to 3 to 40, you learn that the source is degrading before anyone complains about the numbers.

The order inside a layer, and the size of the file

Everything in this article loads the file into memory, 17 MB of it, and that is fine for deliveries of this size. Past a few gigabytes, the layers need different tools. Bytes and structure can stream: a checksum is computed in chunks, and a field count is a pass over the records with the csv module, one row at a time, which holds a single row in memory. Value rules can run in DuckDB, which reads the file in chunks and spills to disk, with the caveat above about ignore_errors. Keys are the expensive layer, since checking uniqueness requires remembering every key. A hash aggregation over a billion ids is a job for a database, not a script.

The layers also tell you what to run first on a large file. Structure fails fast and cheap; if the first million rows have the wrong width, there is no reason to read the other ninety million before reporting it. A validator for large files stops on the first failing layer and reports a sample of the offending rows instead of all of them. The large files series covers the shell tools that make a first pass over a big file cheap.

What to do when a layer fails

A failing check needs a policy, decided before the first failure. There are three honest options, and each belongs to some rules and not others.

Reject the delivery. Nothing is loaded and the sender is told. Right for bytes and structure: a checksum mismatch, invalid encoding, the wrong header, a truncated file. These mean the file is not the one that was agreed, and nothing downstream should be asked to guess.

Quarantine the rows. The good rows load, the failing rows go to a rejects file with the reason and the line number, and the count is reported. Right for value rules where a small number of bad rows is normal, such as an unknown status. Set a ceiling: if more than, say, 1% of rows are quarantined, reject the whole delivery, because a rate that high means the rule or the source changed. The cleaning hub has a threshold example, a pipeline that stops with exit status 2 when too many dates fail to parse.

Warn and load. For things that look odd but cannot corrupt a result: a byte order mark, CRLF line endings, a date a little in the future. The warning goes in the log and the report, and it is read.

Whatever the policy, write it next to the rules. A validator with no policy produces a log that nobody reads, and one with a policy produces a decision that is the same every time.

Where to put the rules

The rules in this script are a Python dictionary. That is fine for one file and awkward for twenty. There are two ways to keep them out of code.

A schema file that a library reads. Frictionless Data describes a table as JSON, with field types and constraints, and validates against it. That is a good choice when you want a standard, portable description and a report in a known format. A typed model, like pydantic, keeps the rules in Python where they can use real types and custom checks. And in a warehouse, the rules often live as tests in the transformation layer. The choice follows your stack more than any technical difference: put the rules where the rest of your pipeline's rules already live.

Whatever you choose, the file's full description belongs in one place that the sender can also read. That is the point of a contract: the validator enforces it, and the contract is what you point to when a delivery fails.

The remaining checks in the series

The validator above touches each of these, briefly. Each deserves a closer look:

• Counts. Row and column counts, and why wc -l is not a row count when a field contains a newline.

• Duplicates. Finding duplicate keys before a primary key constraint does it, and badly.

• Dates. Checking that every date parses, with the ambiguity that no parser can resolve.

• References. Validating against a lookup table in SQL, and the NOT IN trap with nulls.

• Drift. Detecting columns that changed type from one delivery to the next.

• Truncation. Testing for truncated last lines.

Testing the validator itself

A validator is code, and code that decides whether to load data deserves a test. The faulty file in this article is the test: a file with known faults at known lines, and an assertion that the report names exactly those lines. I keep it beside the script. Whenever a delivery fails in production for a reason the validator did not catch, I add that fault to the file first, watch the test fail, and then add the rule. Over a few months the faulty file becomes a list of how your sources break, which is more useful than any list of rules written in advance.

Two more habits. Keep every rule's message specific enough to act on. "Invalid data in column 7" sends someone to a spreadsheet to guess. "status does not match active|pending|inactive, 1 row, line 202" sends them to line 202. And keep the rules next to a sample of every delivery that ever failed: the failures are the specification.

How to tell it worked

Run the validator on a file you know is good and confirm zero findings. Run it on the faulty copy and compare every reported line against the answer key, both ways: every planted fault found, and nothing reported that was not planted. Cut a file at a few different byte offsets and confirm each one fails. Change a single byte in the middle and confirm the checksum catches it. Check the exit status from the shell, because a validator that prints errors and exits 0 will be ignored by every scheduler in the world. And run it from the job that does the load, as a step that must succeed, not from a person's habit.

What validation cannot prove

Validation proves that a file matches the rules you wrote. It does not prove that the data is true. A well-formed email may belong to nobody. A status of active may be wrong for that customer. A balance can be a valid two-decimal number and still be off by a factor of ten because the sender switched currencies. The checks catch what is structurally broken, and the checks you add after each incident catch what has broken before.

The rest you handle by comparing the numbers with something independent: the sender's own totals, last month's distributions, a count from the source system. A column whose average moves from 40 to 400 between deliveries is invalid by no rule at all and is the most important thing the report could show you. So alongside the pass or fail, keep a few summary numbers per delivery and compare them. Validation says the file is well-formed. Whether it is right is a question for people who know what the data is supposed to say.