MeridianMERIDIAN

Build a Typed DuckDB Pipeline

Go from raw CSV to a fully typed DuckDB database — profile, export a schema, validate, materialise, and query with proper types.

Goal: Turn a raw CSV into a typed DuckDB table: profile it, export a JSON Schema, then validate and materialise the table in one pass.

The problem

You load a CSV into DuckDB and the dates arrive as text, so you can't subtract one from another. Worse, one row writes its dates another way, and nothing tells you until a query gives a wrong answer. You want a table whose dates are dates, with the odd row out caught on the way in rather than found later.

Prerequisites

ToolPurpose
FinetypeSemantic type detection, schema export and validation
DuckDBAnalytical SQL engine

Steps

1. Save the sample data

Save this as shipped_orders.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-003,carol@startup.io,03/08/2024,03/10/2024,89.50,USD,United States
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

The dates don't agree. Rows 1, 2, 4 and 5 write them as ISO dates (2024-01-15); row 3 writes them month-first with slashes (03/08/2024).

2. Profile the CSV

Run profile to read the semantic type of every column:

finetype profile -f shipped_orders.csv
FineType Column Profile — "shipped_orders.csv" (5 rows, 7 columns)
════════════════════════════════════════════════════════════════════════════════

  COLUMN                    TYPE                                      BROAD   CONF
  ──────────────────────────────────────────────────────────────────────────────
  order_id                  representation.identifier.alphanumeric_id  VARCHAR  95.6%
  customer_email            identity.person.email                   VARCHAR 100.0%
  order_date                datetime.date.iso                          DATE 100.0%
  ship_date                 datetime.date.iso                          DATE 100.0%
  amount                    representation.numeric.decimal_number    DOUBLE  60.0% [amount_bare_number_veto:amount] ⚑ low
  currency                  finance.currency.currency_code          VARCHAR 100.0%
  country                   geography.location.country              VARCHAR 100.0%

7/7 columns typed, 5 rows analyzed

Finetype reads order_date and ship_date as ISO dates, the form four of the five rows use. The slash-written row doesn't show here; it surfaces when you validate in step 4.

amount reads low. Its name suggests money, but its values carry no currency symbol, so Finetype reads it as a plain decimal number and flags it for you to check. Reading the confidence, on the overview, explains the band.

3. Export a JSON Schema

Export the profile as a JSON Schema, the file validate checks the data against:

finetype profile -f shipped_orders.csv -o json-schema > shipped_orders.schema.json

Each column in the schema carries its semantic type (x-finetype-label) and what a value must look like: a pattern, a list of allowed values or length limits. order_date, for instance, must match ^\d{4}-\d{2}-\d{2}$.

4. Validate and materialise the typed table

Run validate with --db and --table to check the data and write the valid rows to a typed DuckDB table in the same pass:

finetype validate shipped_orders.csv shipped_orders.schema.json --db shipped_orders.duckdb --table orders
Validation Report
════════════════════════════════════════════════════════════
  Input:        shipped_orders.csv
  Schema:       shipped_orders.schema.json
  Output DB:    shipped_orders.duckdb
  Target table: orders
  Scan ID:      1

  Total rows:             5
  Valid rows:             4
  Invalid rows:           1
  Rejects:                2
    SEMANTIC_TYPE:        2
    TRANSFORM_FAILED:     0
  Grade:             C
════════════════════════════════════════════════════════════

The slash-written row, ORD-003, fails the ISO date pattern on both order_date and ship_date: two rejects in one invalid row, and the command exits with code 1. shipped_orders.duckdb now holds an orders table with the four valid rows, and a finetype_reject_errors table recording each reject.

5. See the difference

Read the CSV directly and DuckDB leaves the dates as text:

duckdb -c "SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM read_csv('shipped_orders.csv'))"
┌────────────────┬─────────────┐
│  column_name   │ column_type │
│    varchar     │   varchar   │
├────────────────┼─────────────┤
│ order_id       │ VARCHAR     │
│ customer_email │ VARCHAR     │
│ order_date     │ VARCHAR     │
│ ship_date      │ VARCHAR     │
│ amount         │ DOUBLE      │
│ currency       │ VARCHAR     │
│ country        │ VARCHAR     │
└────────────────┴─────────────┘

Now check the typed table:

duckdb shipped_orders.duckdb -c "SELECT column_name, column_type FROM (DESCRIBE orders)"
┌────────────────┬─────────────┐
│  column_name   │ column_type │
│    varchar     │   varchar   │
├────────────────┼─────────────┤
│ order_id       │ VARCHAR     │
│ customer_email │ VARCHAR     │
│ order_date     │ DATE        │
│ ship_date      │ DATE        │
│ amount         │ DOUBLE      │
│ currency       │ VARCHAR     │
│ country        │ VARCHAR     │
└────────────────┴─────────────┘

The dates are DATE now. amount is a DOUBLE either way, the type for the plain decimal number Finetype read it as.

6. Query with proper types

Open the database with duckdb shipped_orders.duckdb. With the dates typed, subtracting one from another gives a whole number of days:

-- Orders shipped within 2 days
SELECT order_id, customer_email, order_date, ship_date,
       ship_date - order_date AS shipping_days
FROM orders
WHERE ship_date - order_date <= 2;
┌──────────┬──────────────────┬────────────┬────────────┬───────────────┐
│ order_id │  customer_email  │ order_date │ ship_date  │ shipping_days │
│ varchar  │     varchar      │    date    │    date    │     int64     │
├──────────┼──────────────────┼────────────┼────────────┼───────────────┤
│ ORD-002  │ bob@company.org  │ 2024-02-20 │ 2024-02-22 │             2 │
│ ORD-005  │ eve@research.net │ 2024-05-01 │ 2024-05-03 │             2 │
└──────────┴──────────────────┴────────────┴────────────┴───────────────┘

Only the four valid rows are in the table. ORD-003 was rejected at validation, so it never reaches your queries:

-- Total by currency
SELECT currency, sum(amount) AS total, count(*) AS orders
FROM orders
GROUP BY currency
ORDER BY total DESC;
┌──────────┬────────┬────────┐
│ currency │ total  │ orders │
│ varchar  │ double │ int64  │
├──────────┼────────┼────────┤
│ GBP      │ 2500.0 │      1 │
│ EUR      │ 1200.0 │      1 │
│ AUD      │ 340.75 │      1 │
│ USD      │ 149.99 │      1 │
└──────────┴────────┴────────┘

Where to go next

  • Validate a Data Delivery: keep the schema and check every later batch against it, reading the rejects table to see what failed.
  • validate reference: check-only and materialise modes, appending a later batch, and the exit codes.

Profile a Parquet File

Profile data stored in Parquet with Finetype, directly with the CLI or inside DuckDB with the community extension, and read the semantic type of each column.

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.

On this page

The problem
Prerequisites
Steps
1. Save the sample data
2. Profile the CSV
3. Export a JSON Schema
4. Validate and materialise the typed table
5. See the difference
6. Query with proper types
Where to go next