Validate a Data Delivery

Use Finetype's profile and validate commands to build a repeatable quality gate for incoming data: save a schema, validate each delivery, inspect the rejects.

Goal: Save a schema from a batch of data you trust, then check every later delivery against it: a repeatable quality gate you can run by hand or in CI.

The problem

You receive data from a partner or an upstream system. The first batch looks fine, and you build a pipeline around it. Then a later batch arrives with dates written another way and an email address that isn't one, and the pipeline breaks or, worse, carries on. You want each delivery checked at the gate, before it reaches the pipeline.

Prerequisites

Tool	Purpose
Finetype	Schema export and validation
DuckDB	`finetype profile` needs the `duckdb` CLI on your `PATH`, and so does `validate` with `--db`; you also query the rejects with it in step 4
A batch you trust	The CSV that sets the expected shape
A new delivery	The CSV to check against it

Steps

1. Save the sample batches

Save the batch you trust as good-batch.csv:

order_id,customer_email,order_date,ship_date,amount,currency,country
ORD-001,alice@example.com,2024-01-15,2024-01-18,149.99,USD,United States
ORD-002,bob@company.org,2024-02-20,2024-02-22,2500.00,GBP,United Kingdom
ORD-004,dan@university.edu,2024-04-12,2024-04-15,1200.00,EUR,Germany
ORD-005,eve@research.net,2024-05-01,2024-05-03,340.75,AUD,Australia

And the new delivery as new-batch.csv. Its second row writes the dates month-first with slashes, and its third has (at) where the @ should be:

order_id,customer_email,order_date,ship_date,amount,currency,country
ORD-006,frank@example.com,2024-06-03,2024-06-05,75.00,USD,United States
ORD-007,grace@company.org,06/10/2024,06/12/2024,410.20,GBP,United Kingdom
ORD-008,heidi(at)startup.io,2024-06-14,2024-06-17,99.95,EUR,France
ORD-009,ivan@university.edu,2024-06-20,2024-06-21,1250.00,AUD,Australia

2. Save a schema from the batch you trust

Run profile with -o json-schema to write a JSON Schema for the batch:

finetype profile -f good-batch.csv -o json-schema > delivery-schema.json

The schema lists every column as required, and gives each its semantic type (x-finetype-label, such as datetime.date.iso or identity.person.email) and what a value must look like: a pattern, a list of allowed values or length limits.

Commit delivery-schema.json to your repository. It's the contract each delivery is checked against.

3. Validate the new delivery

Run validate to check the delivery against the schema. On its own it reports the counts and sets an exit code, and writes nothing:

finetype validate new-batch.csv delivery-schema.json

Validation Report
════════════════════════════════════════════════════════════
  Input:        new-batch.csv
  Schema:       delivery-schema.json
  Mode:         check-only (no .db written)

  Total rows:             4
  Valid rows:             2
  Invalid rows:           2
  Rejects:                3
  Grade:             D
════════════════════════════════════════════════════════════

Two of the four rows fail, with three rejects between them. The grade comes from the share of rows that are valid: A at 95% or more, B at 85%, C at 70%, D at 50% and F below that. Half the rows here are valid, so the grade is D.

The exit code is what a script reads:

0: every row is valid, and the delivery passes.
1: one or more rows were rejected, and the delivery fails.
2: the command hit an error.

4. Read the rejects

To keep the rejects for review, pass --db and --table. Finetype writes the valid rows to a typed table and records every reject in a finetype_reject_errors table, in one pass:

finetype validate new-batch.csv delivery-schema.json --db delivery.duckdb --table new_batch

Each reject names its row (line), the column, what failed and the type the schema expected:

duckdb delivery.duckdb -c "SELECT line, column_name, error_type, constraint_failed, expected_type FROM finetype_reject_errors ORDER BY line, column_idx"

┌───────┬────────────────┬───────────────┬───────────────────┬───────────────────────┐
│ line  │  column_name   │  error_type   │ constraint_failed │     expected_type     │
│ int64 │    varchar     │    varchar    │      varchar      │        varchar        │
├───────┼────────────────┼───────────────┼───────────────────┼───────────────────────┤
│     2 │ order_date     │ SEMANTIC_TYPE │ pattern           │ datetime.date.iso     │
│     2 │ ship_date      │ SEMANTIC_TYPE │ pattern           │ datetime.date.iso     │
│     3 │ customer_email │ SEMANTIC_TYPE │ pattern           │ identity.person.email │
└───────┴────────────────┴───────────────┴───────────────────┴───────────────────────┘

The slash-written dates fail the ISO date pattern, and heidi(at)startup.io fails the email pattern.

5. Gate a CI job on the exit code

In CI you need only a pass or a fail, and the exit code is one:

finetype validate new-batch.csv delivery-schema.json \
  && echo "Quality gate passed: loading data" \
  || echo "Quality gate failed: check the rejects"

On this delivery the report is followed by Quality gate failed: check the rejects. Pass --lenient if rejects should still exit 0; an error still exits 2.

6. Fix the delivery and validate it again

Fix the source data, or update the schema if the change is intended (step 7). Here the fixed delivery, saved as fixed-batch.csv, writes ORD-007's dates as 2024-06-10 and 2024-06-12, and ORD-008's email as heidi@startup.io:

order_id,customer_email,order_date,ship_date,amount,currency,country
ORD-006,frank@example.com,2024-06-03,2024-06-05,75.00,USD,United States
ORD-007,grace@company.org,2024-06-10,2024-06-12,410.20,GBP,United Kingdom
ORD-008,heidi@startup.io,2024-06-14,2024-06-17,99.95,EUR,France
ORD-009,ivan@university.edu,2024-06-20,2024-06-21,1250.00,AUD,Australia

finetype validate fixed-batch.csv delivery-schema.json

Validation Report
════════════════════════════════════════════════════════════
  Input:        fixed-batch.csv
  Schema:       delivery-schema.json
  Mode:         check-only (no .db written)

  Total rows:             4
  Valid rows:             4
  Invalid rows:           0
  Rejects:                0
  Grade:             A
════════════════════════════════════════════════════════════

Every row passes and the command exits 0. To load the delivery, run the same command with --db and --table.

7. Update the schema when the shape changes on purpose

When a change is intended, such as a new column or a source system that now writes dates another way, profile a batch in the new shape and write the schema again:

finetype profile -f <new_shape_batch>.csv -o json-schema > delivery-schema.json

Replace <new_shape_batch> with the name of a batch you trust that is in the new shape, then commit the new schema. Every later delivery is checked against it.

Where to go next

validate reference: every option, the JSON report and appending a later batch to the same database.
Build a Typed DuckDB Pipeline: what the typed table looks like, and querying it.

Validate a Data Delivery

On this page