Validating CSV Columns With Python and pydantic Models
Validate each CSV row against a pydantic model with types, literals, patterns and a cross-field rule: 193,708 rows in 0.95 s, with file line numbers.
A schema file describes columns one at a time. Sometimes you want the rules to read like code: this field is an integer above zero, this one is one of three words, and if the currency is filled in the balance must be too. pydantic is a Python library for validating data against type annotations, and it fits CSV well, since every row is a small record with named fields. This article builds a model for the customer export, validates 193,708 rows against it, and spends most of its time on what pydantic's type conversion accepts, which turned out to be more generous than a validator should be. It is one of the tools in the validation guide.
The model
One class describes one row. The annotations are the schema, and the validators are the rules:
import collections, csv, sys, time
from datetime import date
from decimal import Decimal
from typing import Annotated, Literal, Optional
from pydantic import BaseModel, BeforeValidator, ConfigDict, StringConstraints, ValidationError, model_validator
def blank_to_none(v):
return None if v == "" else v
Blankable = lambda t: Annotated[Optional[t], BeforeValidator(blank_to_none)]
class Customer(BaseModel):
model_config = ConfigDict(extra="forbid")
id: int
first_name: str
last_name: str
email: Annotated[str, StringConstraints(pattern=r"[^@\s]+@[^@\s]+\.[^@\s]+")]
phone: Annotated[str, StringConstraints(pattern=r"|\+1\d{10}")]
phone_unparsed: str
signup_date: Blankable(date)
balance: Blankable(Decimal)
balance_currency: Literal["", "USD", "EUR"]
units: Blankable(int)
country: Literal["", "US", "IT", "DE", "BR"]
status: Literal["active", "pending", "inactive"]
@model_validator(mode="after")
def currency_needs_balance(self):
if self.balance_currency and self.balance is None:
raise ValueError("balance_currency is set but balance is empty")
return selfA few choices in there carry the weight.
**Literal for closed lists.** status is one of three words and country one of four or empty. A typo is a validation error, reported with the allowed values. See the pydantic docs on validators for the other tools.
**StringConstraints(pattern=...) for shapes.** The email and phone fields keep their text and require a pattern. The phone is a string, not an integer, because an international number is an identifier, not a quantity.
**Blankable for optional columns.** A CSV has no null, only empty text, and pydantic treats '' as an invalid integer, date or decimal. The BeforeValidator turns an empty string into None before the type check, so an empty units is allowed and 12x is not. Without it, every empty cell is an error.
**extra="forbid".** A row with more fields than the header gets an error naming the extra values, instead of having them silently ignored.
**model_validator.** The rule that a currency without a balance is an error compares two fields, which is the one thing a per-column schema cannot do.
Getting rows into it
The model validates a dictionary. This generator reads the file with the csv module and builds one, with the file line number:
def records(path):
with open(path, newline="", encoding="utf-8") as f:
r = csv.reader(f)
header = next(r)
for n, row in enumerate(r, start=2):
d = dict(zip(header, row))
if len(row) > len(header):
d["extra_values"] = "|".join(row[len(header):])
yield n, ddict(zip(header, row)) is deliberate. A short row produces a dictionary with fewer keys, and pydantic reports each missing key as missing. A row with extra fields would be truncated by zip, so the surplus goes into an extra_values key that the model forbids. Using csv.DictReader instead would hide both cases: it fills missing values with None and collects extras under a key you have to know about. Building the dictionary yourself keeps the structure errors visible. The line number is enumerate(..., start=2): line 1 is the header. This is a record count, so it matches the editor's line only until the first quoted newline, as the counting article explains.
The validation call is Customer.model_validate_strings(d). It is designed for input whose values are all strings, which is what a CSV gives you. For these inputs it returned the same results as the ordinary model_validate.
What the parser accepts
This is where a validator can go wrong without a sound. Pydantic's job is to convert, and conversion is generous. I fed small models strings and recorded the result:
python /batches/csv-0001-0200/lab/val/t0125b.py I '12' 12
I ' 12 ' 12
I '1_000' 1000
I '1e3' ERROR int_parsing
I '12.0' 12
I '12.5' ERROR int_parsing
I '0x10' ERROR int_parsing
I '' ERROR int_parsing
I '٣' ERROR int_parsing
I '+7' 7
I '-0' 0
B 'true' True
B 'T' True
B 'yes' True
B 'on' True
B '1' True
B 'y' True
B '2' ERROR bool_parsing
B '' ERROR bool_parsing
D '2025-03-14' datetime.date(2025, 3, 14)
D '2025-03-14T10:00:00' ERROR date_from_datetime_inexact
D '2025-02-30' ERROR date_from_datetime_parsing
D '03/14/2025' ERROR date_from_datetime_parsing
D '1741910400' datetime.date(2025, 3, 14)
D '' ERROR date_from_datetime_parsing
N '1,200' ERROR decimal_parsing
N '1200.50' Decimal('1200.50')
N '1e3' Decimal('1E+3')
N 'NaN' ERROR finite_number
N '' ERROR decimal_parsing
N '$5' ERROR decimal_parsingFor integers: 12, 12 (with spaces), 12.0, +7 and -0 were accepted, and so was 1_000, which became 1000. 1e3, 12.5, 0x10, the empty string and an Arabic-Indic digit were rejected. For booleans, true, T, yes, on, 1 and y all became True, and 2 and the empty string failed. For dates, 2025-03-14 parsed, 2025-02-30 and 03/14/2025 were rejected, and 2025-03-14T10:00:00 was rejected as a date with a time. But the string 1741910400 became the date 2025-03-14, because pydantic treats a number as seconds since the Unix epoch. For decimals, 1200.50 and 1e3 parsed, and 1,200, NaN, the empty string and $5 did not.
The surprising ones are the useful lesson. A ten digit order number in a date column would validate as a date. A field containing 1_000 would be accepted as a thousand where the file's author probably meant a typo. Whether that matters depends on the data. When it does, add a pattern before the conversion so the text has to look the way you expect: a BeforeValidator that rejects anything not matching \d{4}-\d{2}-\d{2} for a date, for instance. A type annotation is a conversion rule, and a validator should be stricter than the conversion it performs.
Running it
The script collects errors instead of stopping at the first, keeps the file line, and counts the error types. The good file:
python /batches/csv-0001-0200/lab/val/t0125.py val/customers.csv193708 rows, 0 errors, 1.07s
by type: {}193,708 rows, no errors, 0.95 s. The faulty copy:
python /batches/csv-0001-0200/lab/val/t0125.py val/customers-bad.csv193708 rows, 9 errors, 1.00s
line 102 email string_pattern_mismatch input='not-an-email'
line 202 status literal_error input='actve'
line 302 signup_date date_from_datetime_parsing input='2025-13-45'
line 402 id duplicate input='first seen on line 401'
line 502 extra_values extra_forbidden input='extra'
line 602 country missing input=''
line 602 status missing input=''
line 702 country literal_error input='Atlantis'
line 802 units int_parsing input='12x'
by type: {'string_pattern_mismatch': 1, 'literal_error': 2, 'date_from_datetime_parsing': 1, 'duplicate': 1, 'extra_forbidden': 1, 'missing': 2, 'int_parsing': 1}Nine errors for eight faults, each with its line:
102:
string_pattern_mismatchonemail202:
literal_erroronstatus(actve)302:
date_from_datetime_parsingonsignup_date(2025-13-45)402:
duplicate, first seen on line 401502:
extra_forbiddenonextra_values602:
missingforcountryand forstatus, which is one fault and two errors702:
literal_erroroncountry(Atlantis)802:
int_parsingonunits(12x)
The duplicate on line 402 is not pydantic's. A model validates one row at a time, so it cannot know that an id has appeared before. The script keeps a dictionary from each id to the first line it appeared on, and adds a duplicate error when it meets one again. This is a pattern worth remembering: the model covers the row, and a few lines of ordinary Python cover everything that spans rows (uniqueness, ordering, the count). The duplicate keys article goes deeper on the second kind.
A rule across fields
The cross-field validator is a rule that only code can state. A balance currency of USD with an empty balance:
cd /batches/csv-0001-0200/lab/val && python -c "
from t0125 import Customer
from pydantic import ValidationError
row = dict(id='7', first_name='Ana', last_name='Silva', email='[email protected]', phone='', phone_unparsed='', signup_date='2025-03-01', balance='', balance_currency='USD', units='', country='US', status='active')
try:
Customer.model_validate_strings(row)
except ValidationError as e:
err = e.errors()[0]
print(err['type'], '|', err['msg'], '| loc', err['loc'])
row['balance_currency'] = ''
print(Customer.model_validate_strings(row).balance_currency == '', 'accepted once the currency is empty too')
"value_error | Value error, balance_currency is set but balance is empty | loc ()
True accepted once the currency is empty tooThe model raised value_error with the message the validator wrote, and accepted the row once the currency was empty too. The error's location is empty, because the rule belongs to the whole row, not a field. Rules like this (an end date after a start date, a refund no larger than the order, a required field when another has a value) are the best reason to use a model instead of a schema file. In the cleaned customer file the rule never fired, which is the result you want from a check on data you cleaned yourself and need on data you did not.
Speed and its limits
The faulty file took 0.92 s and the good one 0.95 s for 193,708 rows. That is fast enough that I would not think about it for any file that fits in memory, and it was nearly six times faster than Frictionless on the same file (5.5 s). The comparison is not entirely fair, because the two check slightly different things, but it says a model is not the slow option. For files of tens of millions of rows, validate in chunks, or validate a sample and run the cheap structure checks on everything.
Choosing between a model and a schema
A schema file has one advantage: the provider can read it, and so can people who do not write Python. A model can express rules a schema cannot, can share types with the rest of your application, and gives you precise control over conversion. In practice I keep the data contract as a document and a schema, and use a model when the rules are code-shaped, or when the same class will validate rows from the CSV and from an API. They are not exclusive. The contract states what is promised, and whichever tool enforces it should be easy to read next to that statement.
How to tell it worked
Validate a file you know is clean and expect no errors. Plant one fault per rule and expect exactly one error per fault, at the right line. Pay particular attention to the coercion cases for your own types: try a number in a date column, 1_000 in an integer, yes in a boolean, and see what your model does. Write each of them down as a test. And print the failures with the offending text: a message that says "int_parsing" without 12x makes someone open the file.
What a row model cannot see
It sees one row. It cannot tell that the file is missing its last 400 rows, that a column has changed meaning, that a key appears twice or that today's average balance is a tenth of yesterday's. It does not see the bytes either, so a truncated last line fails as a missing field only if the cut falls inside a row, and a file cut exactly between two rows validates perfectly. Run the row and column counts and a checksum before this step, and the checks across rows after it.
