Writing a Schema for a CSV With Frictionless Data in Python

A schema is a contract in a form a program can check. It lists the columns, says what type each holds and states the rules a value must obey. Once it exists, validating a file stops being a script you maintain and becomes a file you can hand to the person who produces the data. Frictionless Data is an open framework built around exactly this idea. Its schema format, Table Schema, is a small JSON document, and its Python library can both infer one from a file and validate a file against one. This article writes a schema for two files, checks what inference gets wrong, and measures what the validator finds, as one of the tools in the validation guide.

Start by letting it guess

Frictionless reads a sample of the file and proposes a type for each column. The command is frictionless describe, and it can print the schema as JSON. On the 7 row orders file, which has one bad date and one non-numeric total among otherwise good rows:

frictionless describe lab/data/val/orders-small.csv --json 2>&1 | python -c "import json,sys; d=json.load(sys.stdin); print([(f['name'], f['type']) for f in d['schema']['fields']])"

[('order_id', 'integer'), ('customer_id', 'integer'), ('placed_on', 'string'), ('total', 'string'), ('status', 'string')]

order_id and customer_id came out as integer, which is right. placed_on and total came out as string: a single value (2025-02-30, abc) in each column made the inferred type fall back to text. That is exactly the wrong way round for validation, because the bad value that should be reported was the reason the column lost its type. Inference describes what is in the file, faults included. A schema describes what should be there.

The second example is from 2,000 rows of the clean customer file:

head -n 2001 work/val/customers.csv > work/val/customers-2000.csv
frictionless describe work/val/customers-2000.csv --json 2>&1 | python -c "import json,sys; d=json.load(sys.stdin); print([(f['name'], f['type']) for f in d['schema']['fields']])"

[('id', 'integer'), ('first_name', 'string'), ('last_name', 'string'), ('email', 'string'), ('phone', 'integer'), ('phone_unparsed', 'any'), ('signup_date', 'date'), ('balance', 'number'), ('balance_currency', 'string'), ('units', 'integer'), ('country', 'string'), ('status', 'string')]

This time inference did well on id, signup_date (date), balance (number) and units (integer). But it typed phone as integer. The values look like +13207246850, an international number with a plus sign, and a program that believes the column is an integer is free to drop the plus sign and any leading zeros. phone_unparsed came out as any, because in the sample every value was empty. And country and status, which have four and three allowed values, are plain string, because inference has no way to know that the lists are closed. Treat inference as a draft that saves you typing. The values you decide are the part it cannot guess.

Write the schema

Here is the schema for the orders file:

{
  "fields": [
    {"name": "order_id", "type": "integer", "constraints": {"required": true, "unique": true}},
    {"name": "customer_id", "type": "integer", "constraints": {"required": true}},
    {"name": "placed_on", "type": "date", "constraints": {"required": true}},
    {"name": "total", "type": "number", "constraints": {"required": true, "minimum": 0}},
    {"name": "status", "type": "string", "constraints": {"enum": ["paid", "refunded", "shipped"]}}
  ],
  "primaryKey": ["order_id"]
}

Each field has a name, a type and optional constraints. The ones I use most:

• **required**: the value must not be empty. By default, the specification treats an empty string as missing, so a required field rejects it.

• **unique**: no two rows may have the same value in this field.

• **enum**: the value must be one of the listed ones. Comparison is exact, so PAID is not paid.

• **minimum and maximum**: for numbers and dates.

• **pattern**: a regular expression the value is tested against.

primaryKey declares which field or fields identify a row, and Frictionless checks that no key repeats. I declared order_id as both unique and the primary key, and as the output shows, that gets reported twice.

A note on types. integer and number are about the format of the text, not about whether you should compute with them. A phone number, a postal code, an account number: if you would never add two of them, declare them string and constrain the shape with a pattern. The identifier-as-number trap is the same one pandas falls into.

Validate

The Python call is one line, validate(path, schema=schema), and the report is an object with a list of errors. This script prints them in a compact form, with the row number that Frictionless reports, which is the line in the file:

import json, sys, time
from frictionless import Resource, validate

path, schema = sys.argv[1], sys.argv[2]
t0 = time.perf_counter()
report = validate(path, schema=schema)
secs = time.perf_counter() - t0
print("valid:", report.valid)
for task in report.tasks:
    print(f"stats: {task.stats}")
    for e in task.errors:
        print(f"  {e.type:20} row {getattr(e, 'row_number', '-')!s:>4} col {getattr(e, 'field_name', '-')!s:12} {e.note}")
print(f"{secs:.2f}s")

python lab/val/t0124.py lab/data/val/orders-small.csv lab/val/orders.schema.json

valid: False
stats: {'errors': 5, 'warnings': 0, 'seconds': 0.116, 'md5': '14f21102c8764da5dafdfb3a304c992b', 'sha256': '9bd68b69b8171f56062e12e1d098b3d79dcfdd6ff140f5394f490d87c086b012', 'bytes': 256, 'fields': 5, 'rows': 7}
  unique-error         row    4 col order_id     the same as in the row at position 3
  primary-key          row    4 col -            the same as in the row at position 3
  type-error           row    6 col placed_on    type is "date/default"
  type-error           row    7 col total        type is "number/default"
  constraint-error     row    8 col status       constraint "enum" is "['paid', 'refunded', 'shipped']"
0.15s

Five errors for the 7 row file. Line 4 repeats order 1002, reported as a unique-error and a primary-key error. Line 6 has 2025-02-30 in placed_on, which is shaped like a date and is not one, so type is "date/default". Line 7 has abc where total should be a number. Line 8 has PAID, which is not in the enum. The statistics at the top carry bytes, rows, fields, SHA-256 and MD5 of the file: free byproducts that are the checksum and row count you want in a manifest.

It did not report line 5, an order for customer_id 99999999, which is probably a customer that does not exist. A schema for one file knows nothing about another. Frictionless has foreignKeys for relationships between tables in a data package, which I did not test here. For a reference to a lookup table I would use SQL, as in the lookup article.

The command line gives the same result as a table, and a useful exit status:

frictionless validate lab/data/val/orders-small.csv --schema lab/val/orders.schema.json --json 2>&1 | python -c "import json,sys; d=json.load(sys.stdin); print(d['valid'], [(t['type'], t.get('rowNumber'), t.get('fieldName')) for t in d['tasks'][0]['errors']])"
echo "exit status: ${PIPESTATUS[0]}"
frictionless validate lab/data/val/orders-small.csv --schema lab/val/orders.schema.json >/dev/null 2>&1; echo "cli exit status: $?"
frictionless validate work/val/customers.csv --schema lab/val/customers.schema.json >/dev/null 2>&1; echo "cli exit status on the good file: $?"

False [('unique-error', 4, 'order_id'), ('primary-key', 4, None), ('type-error', 6, 'placed_on'), ('type-error', 7, 'total'), ('constraint-error', 8, 'status')]
exit status: 1
cli exit status: 1
cli exit status on the good file: 0

The orders file exited with status 1 and the good customer file with status 0, so frictionless validate can stand in a pipeline as the gate step without a wrapper.

A schema for the customer file

The customer schema has twelve fields, with unique on id and email, an email format, an enum for status, country and balance_currency, and a pattern for the phone:

{
  "fields": [
    {"name": "id", "type": "integer", "constraints": {"required": true, "unique": true}},
    {"name": "first_name", "type": "string"},
    {"name": "last_name", "type": "string"},
    {"name": "email", "type": "string", "format": "email", "constraints": {"required": true, "unique": true}},
    {"name": "phone", "type": "string", "constraints": {"pattern": "\\+1[0-9]{10}"}},
    {"name": "phone_unparsed", "type": "string"},
    {"name": "signup_date", "type": "date"},
    {"name": "balance", "type": "number"},
    {"name": "balance_currency", "type": "string", "constraints": {"enum": ["USD", "EUR"]}},
    {"name": "units", "type": "integer"},
    {"name": "country", "type": "string", "constraints": {"enum": ["US", "IT", "DE", "BR"]}},
    {"name": "status", "type": "string", "constraints": {"enum": ["active", "pending", "inactive"]}}
  ],
  "primaryKey": ["id"]
}

Note "type": "string" for phone, with a pattern, instead of the integer that inference proposed. And note how little text it is: twelve lines, readable by a person who has never seen Python, and the one place where the allowed values of status and country are written down.

I ran it on the good file and on the copy with eight planted faults (lines 102 to 802, one every 100 lines):

python lab/val/t0124.py work/val/customers.csv lab/val/customers.schema.json
python lab/val/t0124.py work/val/customers-bad.csv lab/val/customers.schema.json

valid: True
stats: {'errors': 0, 'warnings': 0, 'seconds': 5.479, 'md5': 'b13670080de9e2903ba7a5da11468d66', 'sha256': '8411365bdfdabc3b40d89f72a4d25c64ea1271ef159f0e6b3fa65bdcbbc23334', 'bytes': 17297141, 'fields': 12, 'rows': 193708}
5.51s
valid: False
stats: {'errors': 10, 'warnings': 0, 'seconds': 5.27, 'md5': '2e2d8e35a93a16e9653a34d90b7400eb', 'sha256': 'afc24dcb82397ebcdb948c29c8584391bb3a71d7f412829d9103a1d517dad229', 'bytes': 17297132, 'fields': 12, 'rows': 193708}
  type-error           row  102 col email        type is "string/email"
  constraint-error     row  202 col status       constraint "enum" is "['active', 'pending', 'inactive']"
  type-error           row  302 col signup_date  type is "date/default"
  unique-error         row  402 col id           the same as in the row at position 401
  primary-key          row  402 col -            the same as in the row at position 401
  extra-cell           row  502 col              
  missing-cell         row  602 col country      
  missing-cell         row  602 col status       
  constraint-error     row  702 col country      constraint "enum" is "['US', 'IT', 'DE', 'BR']"
  type-error           row  802 col units        type is "integer/default"
5.31s

The good file came back valid: True in 5.5 s, with the byte count (17,297,141), the 193,708 rows and the SHA-256 beginning 8411365bdfdabc3b. The faulty one reported 10 errors in 5.3 s, and each fault is accounted for at its line:

• line 102: type-error on email, the string not-an-email failing the email format

• line 202: constraint-error on status, actve not in the enum

• line 302: type-error on signup_date, 2025-13-45

• line 402: unique-error on id and a primary-key error, the same fault twice

• line 502: extra-cell, a row with 13 fields

• line 602: two missing-cell errors, for country and status, the two fields the short row did not have

• line 702: constraint-error on country, Atlantis

• line 802: type-error on units, 12x

Ten errors for eight faults: the duplicate id is reported by two rules, and the short row is missing two fields. When you count faults, count by line, not by error. The row numbers agree with the file's lines, which is the number you give the data owner.

What it costs

5.5 s for 193,708 rows is about five times what the hand-written Python validator in the guide needed (about a second for the same file). The speed difference is the price of generality: every cell goes through a type parser and each constraint, in Python. For deliveries of this size that is irrelevant, and for files with tens of millions of rows you would validate a sample, split the file, or move the checks into DuckDB. What the cost buys is that the rules are data. Changing enum is a one-line edit to a JSON file, and no one reads Python to review it.

Keeping the schema honest

Write the schema next to the validator and treat it as code: in version control, changed in a commit with a reason. Run it against every delivery. When a delivery fails because the source added a legitimate new value, the fix is a deliberate edit to the schema that goes through review, and the commit message is the record of when the contract changed. Run it also against a file you know is good, in your tests, so that an over-strict edit is caught before it blocks a real delivery.

Share the schema with the provider. A JSON schema is the most precise way to say "here is what we expect", and it ends many arguments before they begin. The contract article covers what else goes next to it, in particular the parts a schema cannot hold: encoding, delimiter, how often files arrive.

How to tell it worked

Validate a file you know is good and expect valid: True. Validate a copy with a fault in each column and expect an error at each line. Change one enum value in the schema and confirm that a row using it is now rejected. Compare the report's SHA-256 and row count against sha256sum and a record count from another tool. And try the file in the shape the provider will really send, because a schema written from a hand-built sample often fails on the first real delivery for reasons that are worth knowing early.

What a Table Schema cannot express

It checks one value at a time, plus uniqueness, so any rule about a relationship between fields has no place in it. "End date is after start date", "total equals the sum of the line items", "if country is US the phone starts with +1" are all outside it. They need a check you write yourself, as in the pydantic article, where a row-level model can compare fields. Rules across files, such as an order with no matching customer, need foreign keys or SQL. And a schema says nothing about the file around the data: encoding, a trailing footer, a truncated last line. Those belong in the structure and bytes checks that run before the schema does.