Finetype DuckDB extension

Profile, validate and classify text data inside DuckDB with the Finetype community extension: install it, profile a table, and call its scalar functions.

The Finetype DuckDB extension brings semantic type detection into your SQL queries. Profile a table's columns, validate against a JSON Schema, classify individual values, and cast with confidence — all without leaving DuckDB.

Early Release

Finetype is under active development. Expect breaking changes to taxonomy labels, function signatures, and model formats between releases. Pin to a specific version if stability matters for your use case.

Installation

Install from the DuckDB community extension registry:

INSTALL finetype FROM community;
LOAD finetype;

SELECT ft_version();

ft_version() names the Finetype release the extension was built from. The profile and validate outputs below were taken on 2026-11-03 from the extension the registry installed then, which reported Finetype v0.6.64.

Every output on this page was taken on DuckDB 2.0. Model weights are embedded at compile time — no external files or network requests at runtime.

The extension is a different, older Finetype release from the CLI the quick start installs, so its confidences, and some of the DuckDB types it recommends, can differ from the CLI's: here ip_address is recommended as INET, where the CLI's schema keeps it as text.

Function names

Every SQL function is prefixed with ft_ (ft_profile, ft_validate, ft_infer, …). The older un-prefixed names — finetype(), finetype_detail(), finetype_cast(), finetype_unpack(), finetype_validate() and finetype_version() — have been removed, and a call to one now raises Catalog Error.

Four of the six are a rename. Two are not: finetype(col) typed a whole column, so it maps to the ft_profile aggregate rather than to the single-value ft_infer; and finetype_validate maps to ft_validate_text, which returns a STRUCT where the old scalar returned the string 'valid' — so WHERE finetype_validate(v, s) = 'valid' becomes WHERE ft_validate_text(v, s).valid. The migration table is in finetype's CHANGELOG.

Profile a table

ft_profile(table) is a table macro: pass a catalog table name and it returns one row per column with the detected type, confidence, and the recommended DuckDB type to cast to.

CREATE TABLE contacts AS
  SELECT * FROM read_csv('contacts.csv', all_varchar = true);

FROM ft_profile('contacts');

┌─────────────┬───────────────────────────────────────┬────────────────────┬─────────────┐
│ column_name │                 type                  │     confidence     │ duckdb_type │
│   varchar   │                varchar                │       double       │   varchar   │
├─────────────┼───────────────────────────────────────┼────────────────────┼─────────────┤
│ amount      │ representation.numeric.decimal_number │ 0.6000000238418579 │ DOUBLE      │
│ created_at  │ datetime.timestamp.iso_8601           │ 0.9362223148345947 │ TIMESTAMP   │
│ email       │ identity.person.email                 │ 0.5092905759811401 │ VARCHAR     │
│ id          │ representation.identifier.increment   │ 0.9182975888252258 │ BIGINT      │
│ ip_address  │ technology.internet.ip_v4             │                1.0 │ INET        │
│ name        │ identity.person.full_name             │ 0.9874514937400818 │ VARCHAR     │
└─────────────┴───────────────────────────────────────┴────────────────────┴─────────────┘

Because it returns a normal relation, you can filter and join it like any other table — for example, to list only the columns DuckDB should store as something other than VARCHAR:

SELECT column_name, type, duckdb_type
FROM ft_profile('contacts')
WHERE duckdb_type <> 'VARCHAR';

Nested columns

ft_profile reads each column as text. STRUCT and LIST columns can't be classified directly — instead of guessing, Finetype returns a message telling you how to flatten them first:

CREATE TABLE places AS
  SELECT {'lat': 51.5, 'lon': -0.12} AS loc, ['pub', 'garden'] AS tags;

FROM ft_profile('places');

┌─────────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────┬────────────┬─────────────┐
│ column_name │                                                  type                                                  │ confidence │ duckdb_type │
│   varchar   │                                                varchar                                                 │   double   │   varchar   │
├─────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────┼────────────┼─────────────┤
│ loc         │ nested STRUCT(lat DECIMAL(3,1), lon DECIMAL(3,2)) column — unnest / to_json / extract before profiling │       NULL │ NULL        │
│ tags        │ nested VARCHAR[] column — unnest / to_json / extract before profiling                                  │       NULL │ NULL        │
└─────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────────┴─────────────┘

Use unnest(), to_json(), or struct.field extraction to project the nested data into text columns, then profile those.

Validate against a schema

ft_validate(table, schema) checks every column against a Finetype JSON Schema and returns the row count, reject count, and a sample failure message per column. The schema argument accepts an inline JSON literal, a getvariable() reference, or a file path.

FROM ft_validate('contacts', 'schema.json');

┌─────────────┬───────┬─────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ column_name │ total │ rejects │                                                                            sample_message                                                                            │
│   varchar   │ int64 │  int64  │                                                                               varchar                                                                                │
├─────────────┼───────┼─────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ amount      │    11 │       1 │ "N/A" does not match "^-?[0-9]+(\.[0-9]+)?([eE][+-]?[0-9]+)?$"                                                                                                       │
│ created_at  │    12 │       0 │ NULL                                                                                                                                                                 │
│ email       │    12 │       1 │ "not-an-email" does not match "^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$" │
│ id          │    12 │       0 │ NULL                                                                                                                                                                 │
│ ip_address  │    11 │       1 │ "999.999.999.999" does not match "^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$"                                       │
│ name        │    12 │       0 │ NULL                                                                                                                                                                 │
└─────────────┴───────┴─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

A non-zero rejects count points you straight at the offending value and the constraint it broke.

Scalar functions

For ad-hoc, value-level work the scalar functions operate on a single string.

`ft_infer()` — classify a value

SELECT ft_infer('192.168.1.10');
-- → 'technology.internet.ip_v4'

SELECT ft_infer('alice@example.com');
-- → 'identity.person.email'

`ft_detail()` — classification with confidence

Returns a JSON object with the type, confidence, recommended DuckDB type, and the votes behind the decision.

SELECT ft_detail('192.168.1.10');
-- → {"type": "technology.internet.ip_v4", "confidence": 0.701,
--    "duckdb_type": "INET", "samples": 1, "disambiguation": "multi-branch",
--    "votes": {"technology.internet.ip_v4": 0.701}}

`ft_validate_text()` — check one value against a constraint

Returns a struct of valid, the constraint that was checked, a message when it fails, a grade and a note.

SELECT ft_validate_text('not-an-email', '{"pattern":"^[^@]+@[^@]+\\.[^@]+$"}');
-- → {'valid': false, 'constraint': pattern,
--    'message': '"not-an-email" does not match "^[^@]+@[^@]+\\.[^@]+$"',
--    'grade': fails, 'note': NULL}

`ft_cast()` and `ft_unpack()`

ft_cast(value) normalises a value into a form DuckDB will accept for a TRY_CAST. ft_unpack(value) returns the full inference record (type, confidence, DuckDB type, and the normalised value) as JSON:

SELECT ft_unpack('149.99');
-- → {"confidence":0.9643217325210571,"duckdb_type":"DOUBLE",
--    "type":"representation.numeric.decimal_number","value":"149.99"}

DuckDB Extension