DuckDB: query CSV, Parquet and JSON files

For three invoice sources, DuckDB queries data files where they are. SELECT * FROM 'invoices.csv' reads a CSV file as if it were a table, and the same works for Parquet and JSON files, with no import step and no DataFrame in between. Each query reads the file afresh and uses what the file says about itself: a Parquet file stores its column names and types, JSON records are parsed into columns, and for a CSV file a snifferCSV sniffer: A reader process that infers a text table's dialect, column types and header from sampled input. guesses the delimiter, the header and the column types from a sample of rows. When a guess is wrong, read_csv, read_parquet and read_json take options that state the answer instead. This is the quickest way to look at an export before deciding how to load it, to join a file with a table or a DataFrame, and to summarise files larger than the computer’s memory, because DuckDB streams through them.

One query joins three original file formats

A small book distributor keeps its invoice lines in CSV, its catalogue in Parquet and its returns as JSON records, one per line, each with a nested list of returned items:

invoice,sold_on,shop,city,sku,qty,amount
INV-101,2026-07-05,Lakshmi Books,Pune,BK-101,4,1800.00
INV-101,2026-07-05,Lakshmi Books,Pune,BK-102,2,598.00
INV-102,2026-07-12,Page Turners,Delhi,BK-103,3,1950.00
INV-103,2026-07-19,Readers Corner,Chennai,BK-101,10,4500.00
INV-104,2026-07-31,Lakshmi Books,Pune,BK-103,1,650.00
INV-105,2026-08-03,Book Nook,Kolkata,BK-102,6,1794.00
INV-105,2026-08-03,Book Nook,Kolkata,BK-104,2,700.00

{"invoice": "INV-101", "reason": "damaged", "items": [{"sku": "BK-101", "qty": 1}, {"sku": "BK-102", "qty": 1}]}
{"invoice": "INV-105", "reason": "wrong title", "items": [{"sku": "BK-104", "qty": 2}]}

# Three files, three formats, no loading step: each query reads the files where they are.
import duckdb

print(duckdb.sql("SELECT count(*) AS lines, min(sold_on) AS first, max(sold_on) AS last FROM 'invoices.csv'"))
print(duckdb.sql("SELECT sku, title, publisher, price FROM 'catalogue.parquet' ORDER BY sku"))
print(duckdb.sql("SELECT invoice, reason, len(items) AS skus FROM 'returns.json' ORDER BY invoice"))

# One question across all three: revenue per publisher, less the value of returned copies.
print(duckdb.sql("""
    WITH sold AS (
        SELECT c.publisher, sum(i.amount) AS sold
        FROM 'invoices.csv' AS i JOIN 'catalogue.parquet' AS c USING (sku)
        GROUP BY c.publisher
    ), returned AS (
        SELECT c.publisher, sum(r.item.qty * c.price) AS returned
        FROM (SELECT unnest(items) AS item FROM 'returns.json') AS r
        JOIN 'catalogue.parquet' AS c ON c.sku = r.item.sku
        GROUP BY c.publisher
    )
    SELECT publisher, sold, coalesce(returned, 0) AS returned, sold - coalesce(returned, 0) AS net
    FROM sold LEFT JOIN returned USING (publisher)
    ORDER BY publisher
"""))

┌───────┬────────────┬────────────┐
│ lines │   first    │    last    │
│ int64 │    date    │    date    │
├───────┼────────────┼────────────┤
│     7 │ 2026-07-05 │ 2026-08-03 │
└───────┴────────────┴────────────┘

┌─────────┬────────────────────┬─────────────────┬──────────────┐
│   sku   │       title        │    publisher    │    price     │
│ varchar │      varchar       │     varchar     │ decimal(8,2) │
├─────────┼────────────────────┼─────────────────┼──────────────┤
│ BK-101  │ Monsoon Maths      │ Kavya Press     │       450.00 │
│ BK-102  │ River Stories      │ Kavya Press     │       299.00 │
│ BK-103  │ Data at Dawn       │ Northwind Books │       650.00 │
│ BK-104  │ Hill Station Poems │ Northwind Books │       350.00 │
└─────────┴────────────────────┴─────────────────┴──────────────┘

┌─────────┬─────────────┬───────┐
│ invoice │   reason    │ skus  │
│ varchar │   varchar   │ int64 │
├─────────┼─────────────┼───────┤
│ INV-101 │ damaged     │     2 │
│ INV-105 │ wrong title │     1 │
└─────────┴─────────────┴───────┘

┌─────────────────┬────────┬───────────────┬────────┐
│    publisher    │  sold  │   returned    │  net   │
│     varchar     │ double │ decimal(38,2) │ double │
├─────────────────┼────────┼───────────────┼────────┤
│ Kavya Press     │ 8692.0 │        749.00 │ 7943.0 │
│ Northwind Books │ 3300.0 │        700.00 │ 2600.0 │
└─────────────────┴────────┴───────────────┴────────┘

The file names stand in FROM like table names, and DuckDB chooses the reader from the extension. The CSV dates came out as DATE because the sniffer recognised them, the Parquet prices kept the DECIMAL(8,2) type stored in the file, and unnest(items) turned each returned item of the JSON records into a row of its own. The JSON reader also worked out by itself that the file holds one record per line rather than one big list. The report then joins all three: Kavya Press sold 8,692 worth and took back 749, so it nets 7,943. Nothing was copied into a database on the way, so running the script again tomorrow reads tomorrow’s files.

Join the invoice, catalogue and return inputs; reconcile 8,692 sold with 749 returned.

The sniffer separates dialect, type and header

A Parquet file describes itself; a CSV file does not, so read_csv works out its layout before reading it:

The order is the documented one: dialect first, then types, then the header, all from a sample of 20,480 rows by default (CSV auto detection). sniff_csv reports the sampled detection decisions without returning the complete data table:

# What DuckDB decided about a CSV file, and the read_csv call that reproduces it.
import textwrap

import duckdb

found = duckdb.sql("SELECT Delimiter, Quote, HasHeader, Columns, DateFormat FROM sniff_csv('invoices.csv')").fetchone()
delimiter, quote, has_header, columns, date_format = found
print("delimiter:", repr(delimiter), "| quote:", repr(quote), "| header:", has_header, "| date format:", date_format)
for col in columns:
    print(f"  {col['name']:<8} {col['type']}")

# Prompt holds the full call with every setting spelled out: paste it into a script to stop guessing.
prompt = duckdb.sql("SELECT Prompt FROM sniff_csv('invoices.csv')").fetchone()[0]
print(textwrap.fill(prompt, 100, subsequent_indent="    "))

delimiter: ',' | quote: '(empty)' | header: True | date format: %Y-%m-%d
  invoice  VARCHAR
  sold_on  DATE
  shop     VARCHAR
  city     VARCHAR
  sku      VARCHAR
  qty      BIGINT
  amount   DOUBLE
FROM read_csv('invoices.csv', auto_detect=false, delim=',', quote='', escape='', new_line='\n',
    skip=0, comment='', header=true, columns={'invoice': 'VARCHAR', 'sold_on': 'DATE', 'shop':
    'VARCHAR', 'city': 'VARCHAR', 'sku': 'VARCHAR', 'qty': 'BIGINT', 'amount': 'DOUBLE'},
    dateformat='%Y-%m-%d');

The Prompt column is the most useful part: a read_csv call with every setting spelled out and detection switched off. Pasted into a pipeline, it makes the next run read the file exactly as this one did, instead of guessing again from data that may have changed.

Walk from sampled fields through dialect and types to the reproducible reader call.

Parquet separates columns and stores statistics

CSV is a common hand-off format, but parsing and type inference add work and can misread source conventions. Its cost relative to other formats depends on the data, query and physical layout. Parquet is the format to keep data in for analysis: it stores types exactly, compresses well, and lets DuckDB read only the columns and row groups a query needs. JSON sits between them: it keeps nesting that CSV cannot express, but every record has to be parsed.

Compare record parsing with column chunks and ask which ranges the predicate can exclude.

Explicit reader options state the source contract

Real exports break the guesses in a handful of ways, and each has an option or a function that settles it:

• delim, header and types state the delimiter, whether there is a header row, and the type of any column.
• dateformat and timestampformat say how dates are written, such as %d/%m/%Y for day-first dates.
• thousands and decimal_separator read numbers such as 1,234,567.50 or 1.234,50 as numbers.
• store_rejectsreject record: An audit entry identifying a row whose attempted conversion failed, with its location and error. sets each line that fails aside in a table, with its line number and error, while the rest load.
• ignore_errors skips failing lines silently, which is why a report should prefer store_rejects.
• sample_size sets how many rows the sniffer samples; -1 samples the whole file.
• A glob such as 'monthly/*.csv' reads many files as one table, and union_by_nameschema union by name: A combined table schema that aligns fields by their names and fills absent fields with missing values. lines their columns up by name.
• hive_partitioningHive partition: A dataset folder whose key=value path segments identify partition values used while reading its files. turns folder names such as city=Pune into columns that filters can use to skip files.
• encoding reads Latin-1 and UTF-16 files; UTF-8 is the default.
• parquet_metadata() lists a Parquet file’s row groups and each column’s minimum and maximum, without reading rows.
• read_json turns records into rows and nested lists into list columns, which unnest expands into rows.

Choose the reader setting that states each known source convention.

Messy CSV files: overrides, regional number formats and rejected lines Pro: opens with a Pro, Premium or Ultimate pass

Many files in one query: globs, schema drift and Hive partitions Pro: opens with a Pro, Premium or Ultimate pass

Parquet and JSON in depth: row groups, measured speed and nested records Pro: opens with a Pro, Premium or Ultimate pass

Practise the stated query contracts

Interview questions

Key takeaways

• FROM 'file.csv', 'file.parquet' and 'file.json' read files in place; read_csv, read_parquet and read_json take options when the defaults are wrong.
• The CSV sniffer detects the dialect, the types and the header from a sample. sniff_csv shows its decisions, and its Prompt is a read_csv call that repeats them without guessing.
• Parquet stores types and statistics and lets DuckDB read only the columns a query needs; CSV is text to be parsed and guessed; JSON keeps nesting.
• store_rejects sets failing lines aside in a table with their line numbers, where ignore_errors hides them.