The Sniffer Is Usually Right, and That's the Problem

Three formats cover almost everything you will be handed. CSV, because everyone can produce it. JSON, because every API emits it. Parquet, because anything that cares about analytics has settled on it.

DuckDB reads all three with no configuration, and it is very good at guessing what is in them. That is the appeal, and also the trap. A guess that is right 95% of the time is a guess you stop checking, and the 5% arrives as a wrong number rather than an error.

This chapter is about what the guessing does and when it is wrong. Then how to take control of the parts that matter, without describing every column by hand.

Everything here was run against DuckDB 1.5.5.

What the sniffer decides

Give DuckDB a CSV and it reads a sample, guesses the delimiter, the quoting, the header, and a type per column. Here is a deliberately awkward file:

id,code,when,amount,note
1,0042,2026-01-05,10.50,ok
2,0117,2026-01-06,,
3,00A9,not-a-date,7.25,"has, comma"

describe select * from 'load/messy.csv';

id      BIGINT
code    VARCHAR
when    VARCHAR
amount  DOUBLE
note    VARCHAR

Four of the five are right, and one is a compromise worth understanding.

code came back VARCHAR, not an integer. The values 0042 and 0117 are all digits, and a naive reader would call that a number and destroy the leading zeros. DuckDB does not. 00A9 in the sample is not numeric, and even without it, a leading zero signals an identifier rather than a quantity. This is the single most common CSV data-loss bug in analytics, and DuckDB gets it right by default.

when came back VARCHAR, not DATE. Two of the three values are ISO dates; the third is not-a-date. Faced with one value it cannot parse, the sniffer widens the column to text rather than failing. Nothing is lost, but nothing is checked either. You now have a date column that will not compare or sort correctly, and no error told you.

That second one is the shape of the real problem. The sniffer’s failure mode is silent widening, not a crash.

You can ask what it decided without loading anything:

select Columns from sniff_csv('load/messy.csv');

[{'name': 'id', 'type': 'BIGINT'}, {'name': 'code', 'type': 'VARCHAR'},
 {'name': 'when', 'type': 'VARCHAR'}, {'name': 'amount', 'type': 'DOUBLE'},
 {'name': 'note', 'type': 'VARCHAR'}]

sniff_csv also returns the delimiter, quoting and header it detected, and a ready-made read_csv call with everything spelled out. It is the right first move on an unfamiliar file.

Overriding one column, not all of them

The usual advice is “declare your schema explicitly”. It is good advice that people don’t follow, because writing out thirty columns to fix one is miserable.

DuckDB lets you override by name and leave the rest inferred:

select typeof(amount)
from read_csv('load/messy.csv', types={'amount': 'DECIMAL(10,2)'});

DECIMAL(10,2)

That is the practical middle ground. Pin the columns where a wrong guess would hurt: money, identifiers, anything you will join on. Let the sniffer handle the rest. DECIMAL over DOUBLE for money is the usual first override.

The error behaviour that disagrees with itself

Now force the date column and watch what happens:

select * from read_csv('load/messy.csv', types={'when': 'DATE'});

Conversion Error: CSV Error on Line: 4

Good — that is the sniffer’s silence turned into a real failure. Loading stops, you look at line 4, and you decide what to do.

The usual next move is ignore_errors, and this is where it gets interesting:

select count(*) from read_csv('load/messy.csv', types={'when':'DATE'}, ignore_errors=true);

3

select id, "when" from read_csv('load/messy.csv', types={'when':'DATE'}, ignore_errors=true);

(1, 2026-01-05)
(2, 2026-01-06)

Three rows by count(*), two rows when you select the column. Same file, same options, same query engine, in the same session.

Neither is wrong. count(*) does not need the when column to answer, so DuckDB never parses it. That is projection pushdown, the columnar optimisation that makes the engine fast, doing exactly its job. Nothing fails, so nothing is dropped. Ask for the column and the parse happens, row 3 fails conversion, and ignore_errors drops it.

The lesson is not that this is a bug. It is that ignore_errors means “silently drop rows I cannot parse”, and how many that is depends on which columns your query touches. A row count taken before a transformation and one taken after can legitimately differ. If you use ignore_errors on data that matters, count the column you care about, not *.

JSON, and why nesting survives

JSON needs no special handling, and the interesting part is what it produces:

describe select * from read_json('load/e.json');

id    BIGINT
tags  VARCHAR[]
geo   STRUCT(c VARCHAR)

tags did not become a string containing ["a","b"]. It became a real list of strings. geo became a struct with a typed field. DuckDB read the shape of the JSON and produced native nested types, which you can then query with ordinary field access rather than JSON functions.

That is a genuine difference from Postgres, where the natural landing place is jsonb and you reach through it with operators. Here the nesting is in the type system, and a later chapter is about what you can do with that.

Parquet, and the reason it wins

Parquet needs no inference at all, because the file already knows:

copy (select * from read_json('load/e.json')) to 'load/e.parquet' (format parquet);
select typeof(tags), typeof(geo) from 'load/e.parquet';

('VARCHAR[]', 'STRUCT(c VARCHAR)')

The list stayed a list and the struct stayed a struct, through a write and a read, with nothing declared. Parquet carries its schema, so there is nothing to guess and nothing to get wrong.

This is the whole argument for Parquet as an interchange format. State it plainly against the CSV round-trip: write those same nested columns to CSV and they come back as text. The types are not in the file, so the reader has to invent them, and it will invent something flat.

Everything else about Parquet — the compression, the column pruning, the row-group statistics — is a performance story a later chapter picks up. The type fidelity is the correctness story, and it is the one that should decide your choice of format.

Choosing, in practice

Take Parquet when you are offered it. No inference, no ambiguity, nested types intact, and much smaller.

Treat CSV as untrusted input. Run sniff_csv first and pin the columns that matter with types=. Reach for ignore_errors only when dropping unparseable rows is genuinely acceptable, and then count the column, not *.

Let JSON give you structure. Do not flatten it on the way in out of habit; DuckDB’s nested types are more useful than the strings you would replace them with.

Final thoughts

DuckDB’s inference is unusually good — good enough that the leading-zero bug which has cost the industry countless hours simply does not happen here by default.

That quality is exactly why it deserves suspicion. A sniffer that is usually right trains you not to look, and its failure mode is not an error but a column quietly widened to text. So: sniff_csv to see the guess, types= to override what matters, and a real count on a real column when you have told it to ignore errors.

Next: A Directory Is a Table — querying a directory of Parquet without loading any of it, and the partition pruning you can watch happen.