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.

Goal: Read the semantic type of every column in a Parquet file, with the Finetype CLI or from inside DuckDB.

The problem

Parquet carries a type for each column, but a column written as text says only that: it holds strings. Dates, email addresses and currency codes all look the same from the file's own schema. You want to know what the strings hold, without converting the file to CSV first.

Prerequisites

Tool	Purpose
Finetype	Semantic type detection
DuckDB	`finetype profile` needs the `duckdb` CLI on your `PATH`; DuckDB also writes the sample file and runs the extension in step 3
A `.parquet` file	Your own, or the one step 1 makes

Steps

1. Make a Parquet file

To follow along with a file whose columns are all text, 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

Then write it to Parquet, every column as text:

duckdb -c "COPY (FROM read_csv('shipped_orders.csv', all_varchar = true)) TO 'shipped_orders.parquet'"

With your own file, use its path in place of shipped_orders.parquet in the steps below.

2. Profile it with the CLI

Run profile on the Parquet file, the same command you would use for a CSV:

finetype profile -f shipped_orders.parquet

FineType Column Profile — "shipped_orders.parquet" (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

Every column the file stored as text now has a semantic type and the DuckDB type it casts to. The dates read as ISO dates, the form four of the five rows use, and amount reads low: its name suggested a currency amount, but its values carry no currency symbol, so a veto, amount_bare_number_veto in brackets, turned it back into a plain decimal number and capped its confidence at 0.6; Reading the confidence, on the overview, explains the band.

3. Or profile it inside DuckDB

The Finetype DuckDB extension does the same from inside a query. Install and load it:

INSTALL finetype FROM community;
LOAD finetype;

The DuckDB extension page lists every function it adds, and says which DuckDB release its outputs were taken on.

Load the Parquet file as a table, then pass the table's name to ft_profile:

CREATE TABLE orders AS SELECT * FROM read_parquet('shipped_orders.parquet');

SELECT column_name, type, round(confidence, 3) AS confidence, duckdb_type
FROM ft_profile('orders');

┌────────────────┬───────────────────────────────────────────┬────────────┬─────────────┐
│  column_name   │                   type                    │ confidence │ duckdb_type │
│    varchar     │                  varchar                  │   double   │   varchar   │
├────────────────┼───────────────────────────────────────────┼────────────┼─────────────┤
│ amount         │ representation.numeric.decimal_number     │        0.6 │ DOUBLE      │
│ country        │ geography.location.country                │        1.0 │ VARCHAR     │
│ currency       │ finance.currency.currency_code            │        1.0 │ VARCHAR     │
│ customer_email │ identity.person.email                     │      0.665 │ VARCHAR     │
│ order_date     │ datetime.date.iso                         │        1.0 │ DATE        │
│ order_id       │ representation.identifier.alphanumeric_id │      0.898 │ VARCHAR     │
│ ship_date      │ datetime.date.iso                         │        1.0 │ DATE        │
└────────────────┴───────────────────────────────────────────┴────────────┴─────────────┘

ft_profile returns one row per column: the type, the confidence and the DuckDB type to cast to. The query rounds the confidence to three places, since its last digits can vary from run to run. On this file it names the same types as the CLI, with different confidences. The extension is built from its own Finetype release, which SELECT ft_version(); names; this output came from the extension at Finetype v0.6.64.

The result is an ordinary table, so you can filter it, here to the columns worth a typed cast:

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

┌─────────────┬───────────────────────────────────────┬─────────────┐
│ column_name │                 type                  │ duckdb_type │
│   varchar   │                varchar                │   varchar   │
├─────────────┼───────────────────────────────────────┼─────────────┤
│ amount      │ representation.numeric.decimal_number │ DOUBLE      │
│ order_date  │ datetime.date.iso                     │ DATE        │
│ ship_date   │ datetime.date.iso                     │ DATE        │
└─────────────┴───────────────────────────────────────┴─────────────┘

Where to go next

Build a Typed DuckDB Pipeline: turn the profile into a schema, validate the data against it and write a typed table. validate reads Parquet as well as CSV.
profile reference: every option and output format.