Skip to content

Validation and Schema

Joel Natividad edited this page Nov 15, 2026 · 26 revisions

Validation & Schema

Tier: Intermediate Commands covered: validate, schema, sniff (see also profile for standards-based metadata generation)

Note

Per-command flag reference lives in /docs/help/. This page is the workflow layer — when to reach for each command and how they compose.

This is qsv's data quality story. Use schema to infer a JSON Schema 2020-12 spec from representative data, edit the schema by hand to tighten the rules, then validate to enforce it against new data at up to 780,000 rows/sec. Use sniff for fast metadata (delimiter, types, MIME) when you don't need a full schema scan.

For the deep-dive, see docs/Validate.md.

Quick decision table

If you want to… Use Notes
Check a CSV is valid RFC 4180 + UTF-8 validate (no schema) Run before commands that assume well-formed input (especially stats)
Validate against a JSON Schema 2020-12 spec validate <data.csv> <schema.json> Outputs .valid.csv, .invalid.csv, .validation-errors.tsv
Generate a JSON Schema from representative data schema .schema.json sidecar; edit to tighten rules
Get a JSON Schema for a SAS / Stata / SPSS file from its own metadata readstat --dictionary Variable & value labels, declared missing values; enum only when labels cover every value, so validate never rejects the data it describes. No LLM
Generate a Polars schema for sqlp / joinp / pivotp schema --polars .pschema.json sidecar; speeds up Polars commands
Quickly probe an unknown CSV / URL sniff Faster than schema; samples the first N rows
Generate standards-based metadata (DCAT / Croissant / CKAN) profile .metadata.json; FAIRification for data portals & ML registries

validate

JSON Schema Validation Draft 2020-12 with the fastest validator in Rust (jsonschema-rs). Up to 780k rows/sec on NYC 311 with the bundled schema.

Without a schema argument, validate just verifies RFC 4180 conformance and UTF-8 — a cheap sanity check before running stats (which assumes well-formed input).

With a schema, three output files are produced:

  • <input>.valid.csv — rows that passed
  • <input>.invalid.csv — rows that failed
  • <input>.validation-errors.tsv — row_number, field, error for every failure

One custom format and two custom keywords beyond stock JSON Schema 2020-12:

  • "format": "currency" — ISO 4217 currency validation (USD, €, ¥, $1,000.00, (€100,00), -USD100.00, …). A custom format, not a keyword.
  • dynamicEnum (keyword) — enum whose values come from a CSV (local, HTTP/HTTPS, dathere://, or ckan://). Use this when the valid-value set is too large to hardcode or it changes over time.
  • uniqueCombinedWith (keyword) — composite-key uniqueness check across columns.

Example: cheap RFC 4180 + UTF-8 sanity check

qsv validate NYC_311_SR_2010-2020-sample-1M.csv
# Exit code 0 = valid; non-zero = problem; details to stderr.

Example: quarantine ragged rows instead of aborting

Without a schema, validate normally aborts on the first "ragged" row — one with the wrong number of columns. --split-ragged is an opt-in mode that keeps going and separates the file instead:

qsv validate --split-ragged data.csv
# data.csv.valid    — the well-formed rows
# data.csv.invalid  — the ragged rows
# data.csv.validation-errors.tsv — a report, appended

It returns a non-zero exit code when any ragged row is found, so it still fails a pipeline while handing you both halves of the file. Only column-count mismatches are diverted — malformed quoting and non-UTF-8 data still abort.

validate has no parser-config options of its own, but it honors the global QSV_COMMENT_CHAR in both modes. Commented lines (including a commented header) are skipped and are not written to the .valid file:

QSV_COMMENT_CHAR='#' qsv validate --split-ragged data.tsv

Example: validate NYC 311 against the bundled schema

qsv validate \
  NYC_311_SR_2010-2020-sample-1M.csv \
  resources/test/311_Service_Requests_from_2010_to_Present-2022-03-04.csv.schema.json
ls NYC_311_SR_2010-2020-sample-1M.*
# .valid.csv  .invalid.csv  .validation-errors.tsv

Example: dynamicEnum against a CSV of valid NYC agencies

{
  "type": "object",
  "properties": {
    "Agency": {
      "type": "string",
      "dynamicEnum": "NYC_agencies.csv"
    }
  }
}

dynamicEnum accepts a local path or a URL — including ckan:// to fetch a resource by CKAN ID.

Numeric columns and empty cells. Values are compared as text, so dynamicEnum also works on "integer" and "number" columns: 1 in the data matches 1 in the reference file. Empty cells are not looked up; whether they are allowed is up to the column's type, e.g. ["string", "null"].

Example: uniqueCombinedWith for a composite primary key

{
  "type": "object",
  "properties": {
    "Created Date":     { "type": "string" },
    "Complaint Type":   { "type": "string" },
    "Incident Address": { "type": "string" }
  },
  "uniqueCombinedWith": ["Created Date", "Complaint Type", "Incident Address"]
}

This enforces that the combination of those three columns is unique across the file. uniqueCombinedWith goes at the top level of the schema, beside properties, because it checks the whole row. Placed inside one column's schema, it sees only that cell, and every row fails with Instance must be an object. Columns can also be given by 0-based index, e.g. [1, 2].

Which duplicate is flagged. Rows are checked in file order: the first row with a combination is valid, and its later duplicates are invalid. To keep that order, a schema using uniqueCombinedWith is validated on a single thread.

Every listed column must exist. A column name missing from the CSV header, or an index past its last column, makes the schema fail to compile with an error naming the column. An index whose header name a later column repeats is also an error, as the row then holds only the later column's value. A schema shared across files with different columns needs a uniqueCombinedWith that fits each file.

Example: currency format validation

{
  "properties": {
    "Sale Price": { "type": "string", "format": "currency" }
  }
}

Example: validate a directory of CSVs against one schema

qsv validate exports/2024/ schema.json

Example: fancy regex (look-around, backreferences) when needed

qsv validate --fancy-regex data.csv strict-schema.json

By default validate uses the same fast regex crate as search. --fancy-regex opts into the slower but more expressive fancy-regex crate for schemas that need it.

Example: refuse external $refs in a schema you don't trust

qsv validate --offline data.csv untrusted-schema.json

With --offline, a $ref to a schema outside the file, whether a URL or a local file, fails to compile instead of being fetched. The schema file itself and dynamicEnum lookups still load as usual.

Unknown formats are named in a warning. JSON Schema treats a "format" it doesn't know as a label, so validate still runs, but it warns about each one, e.g. a typo like "emial", which would otherwise check nothing. validate schema rejects them. Pass --no-format-validation to ignore every format.

Example: data-quality findings in GitHub code scanning, via SARIF (29.0.0)

qsv validate data/sales.csv sales.schema.json --sarif qsv-validate.sarif

--sarif also writes the errors as a SARIF 2.1.0 log, the format code scanning tools read. Each invalid field becomes a result, pointing at the line its row starts on. That line accounts for multi-line quoted fields, CRLF, blank and comment lines, and a sniffed preamble. Each JSON Schema keyword is its own rule (minimum, pattern, format, required, …), as are dynamicEnum, uniqueCombinedWith and ragged-row (with --split-ragged). Upload it in CI and code scanning shows the findings on the CSV files (see the worked example below):

- run: qsv validate --split-ragged data/sales.csv sales.schema.json --sarif qsv-validate.sarif || true
- uses: github/codeql-action/upload-sarif@v4
  with:
    sarif_file: qsv-validate.sarif
    category: qsv-validate

The job needs the security-events: write permission to upload. || true keeps a validation failure from skipping the upload, but it also means invalid data doesn't fail the job. A complete example in resources/examples/validate-sarif does both: a ready-to-copy workflow that uploads the log and then fails the job on invalid data, plus a sample CSV with one error of each kind, its schema, and the SARIF log qsv writes for them.

  • Always written: the file is written even when every row is valid (with no results), so a clean run clears earlier alerts.
  • Result cap: output stops at 25,000 results by default, GitHub code scanning's limit per run. For a tool that accepts more, set --sarif-max-results <n>, or 0 for no limit.
  • File path: the URI is relative to the root of the git checkout the CSV is in, so it is right wherever qsv runs.
  • Where it works: JSON Schema mode with a file input only; RFC 4180 mode and stdin reject --sarif. A compressed or converted input (e.g. .csv.gz) gets results that name only the file, as its rows have no line in it.
  • Runs that stop with an error: they write no SARIF file, and remove one left by an earlier run. That happens on a CSV read error, or on a ragged row when --split-ragged isn't set, which is why the example uses it. --fail-fast is different: it stops after the first batch with an invalid row, but still writes the results for the rows it validated. Invalid UTF-8 doesn't stop a JSON Schema run; it is reported like any other error.
  • Codacy: codacy-cli upload -s reads SARIF too, but it only keeps results from tools Codacy has registered, and qsv isn't one yet.

Worked example: from a bad row to a code scanning alert. This walks through the resources/examples/validate-sarif files; the output below is what qsv 29.0.0 writes for them. sales.csv has one error of each kind. Here are its header and first four rows, with the file's line numbers. Order 1002's notes field is quoted and spans two lines:

 1  order_id,order_date,sku,region,quantity,unit_price,customer_email,notes
 2  1001,2026-10-01,ABC-1234,North,5,$19.99,ana@example.com,
 3  1002,2026-10-02,ABC-1235,South,12,$4.50,ben@example.com,"Gift wrap,
 4  deliver after 5pm"
 5  1003,2026-13-02,ABC-1236,East,3,$7.25,cara@example.com,
 6  1004,2026-10-04,abc-77,West,1,$2.00,dev@example.com,

These are the constraints in sales.schema.json that rows 3 and 4 break:

"order_date": { "type": "string", "format": "date" },
"sku": { "type": "string", "pattern": "^[A-Z]{3}-[0-9]{4}$" },

Run it the way the example workflow does. --split-ragged keeps the file's ragged row from stopping the run:

qsv validate --split-ragged sales.csv sales.schema.json --sarif sales.csv.sarif
# 11 out of 14 records invalid.   (exit code 1)

The usual sales.csv.validation-errors.tsv report has one line per error. Here are the lines for rows 3 and 4, out of 11:

row_number	field	error
3	order_date	"2026-13-02" is not a "date"
4	sku	"abc-77" does not match "^[A-Z]{3}-[0-9]{4}$"

The SARIF log carries the same errors in machine-readable form. Here is the result for row 3 (jq '.runs[0].results[0]' sales.csv.sarif):

{
  "ruleId": "format",
  "ruleIndex": 3,
  "level": "error",
  "message": {
    "text": "Row 3, column \"order_date\": \"2026-13-02\" is not a \"date\""
  },
  "locations": [
    {
      "physicalLocation": {
        "artifactLocation": {
          "uri": "resources/examples/validate-sarif/sales.csv",
          "index": 0
        },
        "region": {
          "startLine": 5
        }
      }
    }
  ]
}
  • ruleId is the JSON Schema keyword that failed. The sku error is rule pattern, and the file's 11 results use 9 of the log's rules: format (three times), pattern, enum, minimum, maximum, type, maxLength, uniqueCombinedWith and ragged-row.
  • startLine is 5, not 4. Row 3 is the third data row, but row 2's quoted field spans lines 3–4, so row 3 starts on line 5. The TSV gives the row number, and SARIF gives the line an editor or code-review UI can jump to.
  • uri is relative to the git checkout's root, here the qsv repo. Run outside a checkout and it is relative to the current directory (sales.csv).

After github-workflow.yml uploads the log, GitHub code scanning lists each result as an alert in the repository's Security → Code scanning page, at that line of sales.csv: 11 alerts for this file, under the tool name qsv. The workflow also runs on pull requests. On a PR that adds an invalid row, code scanning adds a qsv check that reports "1 new alert", and posts a review comment on the new row's line with the message and the rule (Value fails the JSON Schema format keyword). Alerts already on the default branch aren't repeated on the PR. (Checked with qsv 29.0.0 on a public test repository, jqnatividad/qsv-sarif-test. Its PR #1 shows the review comment; the Security tab is visible only to the repository's maintainers. Code scanning on a private repository needs GitHub Advanced Security.)

See also: /docs/help/validate.md, docs/Validate.md — deep dive, schema, Lookup Tables — for dynamicEnum patterns, Recipe: JSON Schema Validation.

schema

Two modes:

  • JSON Schema mode (default) — produces a <input>.schema.json with inferred types, formats, ranges, and enum / pattern constraints. Edit the file by hand to tighten rules (mark required fields, add dynamicEnum lookups, set custom currency formats, …) before using it with validate.
  • Polars Schema mode (--polars) — produces a <input>.pschema.json consumed by sqlp / joinp / pivotp to skip type inference. Edit to change inferred Polars data types where needed.

Both reuse the stats cache when available (run qsv stats --cardinality --infer-dates --stats-jsonl <input> first for max speed).

Example: infer a JSON Schema from NYC 311

qsv stats --cardinality --infer-dates --infer-boolean --stats-jsonl \
  NYC_311_SR_2010-2020-sample-1M.csv
qsv schema NYC_311_SR_2010-2020-sample-1M.csv
# Produces NYC_311_SR_2010-2020-sample-1M.csv.schema.json

The generated schema has inferred types, min/max ranges (for numerics), date formats, enum values (for low-cardinality columns), and regex patterns (when --pattern-columns is used). It does not add the qsv-specific currency format or the dynamicEnum and uniqueCombinedWith keywords; add those by hand where you need them.

How constraints are chosen (#4830). Each column is judged on its own: only columns at or under --enum-threshold get an enum (with --ignore-case, String columns are counted case-insensitively), every single-value column gets const, and --strict-formats checks every String column.

Generated schemas can be strict. A const locks a column to the one value it held in the input, so validate rejects later data where it changes (a new phone number or URL, say). Generate the schema from a representative dataset, then loosen or remove const/enum constraints where values are expected to change. --strict-formats keeps every distinct value of each String column in memory while it checks them, which costs more on large files with many distinct text values.

--ignore-case schemas validate their own data (#4831). --ignore-case lowercases the values it collects, and validate is case-sensitive, so a String column's enum/const (e.g. ["false","true"]) would reject the source's True/TRUE. With --ignore-case, schema writes them as an equivalent case-insensitive regex instead, e.g. "pattern": "^(?i:false|true)$". Values are escaped so that ., |, ( etc. match literally, and the Turkish İ is accepted alongside the i̇ it lowercases to. Integer enums stay enums.

The inline (?i:…) modifier is regex syntax that qsv's validate understands, as do recent JavaScript engines (tested with AJV 8 on Node 26). Other or older JSON Schema validators may not support it. If a schema must work outside qsv, generate it without --ignore-case.

Example: re-validate the source data — schema should always pass for the data it was inferred from

qsv validate \
  NYC_311_SR_2010-2020-sample-1M.csv \
  NYC_311_SR_2010-2020-sample-1M.csv.schema.json
# Exit code 0; no .invalid.csv or .validation-errors.tsv produced

Example: Polars schema for speeding up sqlp

qsv schema --polars NYC_311_SR_2010-2020-sample-1M.csv
# Produces NYC_311_SR_2010-2020-sample-1M.pschema.json

# Now sqlp / joinp / pivotp pick it up automatically
qsv sqlp NYC_311_SR_2010-2020-sample-1M.csv \
  "SELECT Borough, COUNT(*) FROM nyc311 GROUP BY Borough"

Example: validate a manually-fine-tuned schema is itself valid JSON Schema

qsv validate schema fine-tuned-schema.json
# the same options as `validate` apply, e.g. check its patterns compile with fancy-regex
qsv validate schema --fancy-regex fine-tuned-schema.json

validate schema compiles the schema exactly as validate would, so qsv's own currency format is recognized and the regex and --offline options apply.

See also: /docs/help/schema.md, validate, JSON Schema 2020-12 spec, stats, Stats Cache & Caching.

sniff

(Also covered in Selection & Inspection → sniff. Mentioned here because it's the lightweight cousin of schema for validation workflows.)

sniff samples the first N rows (default 1,000) to infer delimiter, header row, preamble rows, quote character, encoding, average record length, total row count, and per-field types & names. Works on local files and remote URLs (without downloading the whole file).

With the magika feature compiled in, it also identifies 200+ file types via Google's AI-based content detector — useful for CKAN harvesting.

Example: probe a remote dataset before downloading

qsv sniff https://opendata.cityofnewyork.us/data.csv
# Reports delimiter, headers, content length, estimated row count, field types

Example: MIME-type-only probe for fast URL health checks (CKAN-style)

qsv sniff --no-infer https://example.com/data.xlsx

Example: stricter sampling for an oddly-shaped CSV

qsv sniff --sample 0.20 --delimiter '|' weird.csv

For guaranteed (non-sampled) type inference, use stats or schema — they scan the entire file.

See also

Home · Why qsv? · Tier legend

Quick links

Get Started

Command Reference

Cookbook

Tuning & Internals

Ecosystem

Reference

Legacy

Clone this wiki locally