Letting DuckDB Sniff the Delimiter and Types of a CSV

When a DuckDB query names a CSV file and nothing else, something decides how to read it. That something is the sniffer: it reads the start of the file, tries combinations of delimiter, quote, escape character and line ending, and keeps the combination that gives rows of a consistent width. It then looks at the values to decide whether there is a header and what type each column is. Most of the time you never think about it, which is the point. This article is for the other times: when you want to see what it decided, when it decided something you did not expect, and when it was right about the first twenty thousand rows and wrong about the rest. The wider picture is in the DuckDB CSV guide, the sniffer itself is described in the auto detection reference, and every parameter is in the CSV import overview.

What the sniffer decides

The order is roughly this: the dialect first (delimiter, quote, escape, newline), then how many lines to skip before the data starts, then whether the first row is a header, then the type of each column, and last the date and timestamp formats if any column looks like one. It judges each option by how consistently it splits the sample into rows of the same width, which is why a file with a title line on top can still be read, and why a file with ragged rows can defeat it.

Each decision can be seen. sniff_csv is a table function that runs the sniffer and returns one row of results instead of the data:

duckdb -line -c "SELECT Delimiter, Quote, Escape, SkipRows, HasHeader, DateFormat, TimestampFormat FROM sniff_csv('customers.csv')"

      Delimiter = ,
          Quote = "
         Escape = (empty)
       SkipRows = 0
      HasHeader = true
     DateFormat = %Y-%m-%d
TimestampFormat = NULL

For the customers file it found a comma delimiter, a double quote, no separate escape character, zero skipped rows, a header and the date format %Y-%m-%d. TimestampFormat is NULL since no column holds a timestamp. The same function returns more columns, including the detected types as a list, which is what DESCRIBE formats for you; I select only the dialect here because it fits on a screen.

The Prompt column

The most useful column of sniff_csv is the last one, Prompt. It contains a complete read_csv call that gives the same result with detection turned off:

duckdb -list -noheader -c "SELECT Prompt FROM sniff_csv('customers.csv')"

FROM read_csv('customers.csv', auto_detect=false, delim=',', quote='"', escape='', new_line='\n', skip=0, comment='', header=true, columns={'id': 'BIGINT', 'name': 'VARCHAR', 'email': 'VARCHAR', 'signup_date': 'DATE', 'balance': 'DOUBLE'}, dateformat='%Y-%m-%d');

That line says auto_detect=false, the delimiter, quote, empty escape, newline, no skip, no comment character, a header, and a columns struct that names every column with its type, ending with the dateformat. It is the sniffer's answer written as code. Copy it into a script or a view definition and the file is read the same way every time, regardless of what a future version of the sniffer would guess or what the next file looks like. I treat it as the starting point for any recurring job: run sniff_csv once by hand, read the types, fix the ones that are wrong, and keep the result. The next article, on type detection that guessed wrong, covers the fixing.

How types are chosen

For each column the sniffer tries a ladder of types, from the narrowest to VARCHAR, and keeps the first one that parses every value in the sample. The candidates are, in order: NULL, BOOLEAN, BIGINT, DOUBLE, TIME, DATE, TIMESTAMP and VARCHAR. A column of 1, 2, 3 is BIGINT. A column of 1, 2.5 is DOUBLE. A column with one value that is not a date is VARCHAR. Two properties follow from this and they matter later. A column is judged by the values it holds in the sample, not by its name or the meaning of the data. And the choice is made once, before the query runs, from the sample only.

The customers table shows the ladder at work: id is BIGINT, balance is DOUBLE because one value has a decimal point, and signup_date is DATE because every value matched %Y-%m-%d. If the balance column also held a value like n/a, the whole column would be VARCHAR, which is the sniffer being careful rather than clever.

The sample is a sample

By default the sniffer reads 20,480 rows. For most files that is plenty, since the structure of a CSV usually shows in the first few hundred lines. The weakness is a file whose columns look uniform at the top and change later: an export that appends rows from another system, a column that is numeric until a placeholder shows up, a date column in which the first ten thousand rows use one format. To test this I generated a file of 40,000 rows with columns id, qty and label, where qty is a number on every row except line 30,001, which holds n/a. The first twenty thousand rows are clean, so the sniffer has no reason to doubt qty:

for n in 1000 20480 -1; do
  printf 'sample_size=%-6s ' $n
  duckdb -list -noheader -c "SELECT column_type FROM (DESCRIBE SELECT * FROM read_csv('late-text.csv', sample_size=$n)) WHERE column_name = 'qty'"
done

sample_size=1000   BIGINT
sample_size=20480  BIGINT
sample_size=-1     VARCHAR

With sample_size of 1,000 and with the default 20,480, qty is BIGINT. With sample_size=-1, which reads the whole file before deciding, it is VARCHAR. The two answers are both correct for the rows each one looked at. Only one is correct for the file.

What the failure looks like

The query on the default settings starts, reads on past the sample and then stops partway through:

duckdb -c "SELECT count(*), sum(qty) FROM read_csv('late-text.csv')" 2>&1 | head -8

Conversion Error: CSV Error on Line: 30001
Original Line: 
30000,n/a,item 30000
Error when converting column "qty". Could not convert string "n/a" to 'BIGINT'

Column qty is being converted as type BIGINT
This type was auto-detected from the CSV file.
Possible solutions:

Conversion Error: CSV Error on Line: 30001, the original line 30000,n/a,item 30000, and the message Could not convert string "n/a" to 'BIGINT'. The error also says the type was auto-detected, which is the useful part, since it tells you where the type came from. There is a list of suggested fixes after that, and the output ends with the settings DuckDB used, all marked Auto-Detected. Read that list when you hit this error. It is a compact record of what the sniffer decided, and it includes the sample size it used, 20,480.

The nasty form of this is a job that has run for months on clean files and fails one morning because a single row contains a marker. Nothing about the code changed. The data did.

Three ways out

The first is a bigger sample. It is the least effort and the most expensive in time, since the sniffer now reads the whole file before the query starts:

duckdb -box -c "SELECT count(*) AS rows, sum(qty) AS total FROM read_csv('late-text.csv', sample_size=-1)" -c "DESCRIBE SELECT * FROM read_csv('late-text.csv', sample_size=-1)"

Binder Error: No function matches the given name and argument types 'sum(VARCHAR)'. You might need to add explicit type casts.
	Candidate functions:
	sum(arg DECIMAL) -> DECIMAL
	sum(arg BOOLEAN, /) -> HUGEINT
	sum(arg SMALLINT, /) -> HUGEINT
	sum(arg INTEGER, /) -> HUGEINT
	sum(arg BIGINT, /) -> HUGEINT
	sum(arg HUGEINT, /) -> HUGEINT
	sum(arg DOUBLE, /) -> DOUBLE
	sum(arg BIGNUM, /) -> BIGNUM


LINE 1: SELECT count(*) AS rows, sum(qty) AS total FROM read_csv('late-text.csv', sample_size...
                                 ^^^^^^^^
hint: -agent renders errors as JSON and results compactly for AI coding agents (duckdb -help lists all options)

With sample_size=-1 the column is VARCHAR, so sum(qty) is a Binder Error that says no function matches sum(VARCHAR). That is a correct refusal. The sniffer has told the truth about the file, and the next step is yours: decide what n/a means.

The second is to say what it means. If n/a is a missing value, name it, and declare the type so that nothing is left to the sample:

duckdb -box -c "SELECT count(*) AS rows, sum(qty) AS total, count(*) FILTER (WHERE qty IS NULL) AS nulls FROM read_csv('late-text.csv', nullstr='n/a', types={'qty': 'INTEGER'})"

┌───────┬────────┬───────┐
│ rows  │ total  │ nulls │
├───────┼────────┼───────┤
│ 40000 │ 980000 │ 1     │
└───────┴────────┴───────┘

All 40,000 rows loaded, the sum of qty is 980,000, and exactly one value is NULL. Remember from the options article that nullstr replaces the default empty-string NULL, so add '' to the list if the file also has empty cells. This is the fix I use when the marker is known.

The third is to keep the column as text and convert on your terms, which is the right shape when you want to see the bad values rather than hide them:

duckdb -box -c "CREATE TEMP TABLE raw AS SELECT * FROM read_csv('late-text.csv', all_varchar=true); SELECT count(*) AS rows, sum(TRY_CAST(qty AS INTEGER)) AS total, count(*) FILTER (WHERE TRY_CAST(qty AS INTEGER) IS NULL) AS not_numbers FROM raw"

┌───────┬────────┬─────────────┐
│ rows  │ total  │ not_numbers │
├───────┼────────┼─────────────┤
│ 40000 │ 980000 │ 1           │
└───────┴────────┴─────────────┘

That loaded the file with every column as VARCHAR into a temporary table and then converted with TRY_CAST: 40,000 rows, a sum of 980,000 and one value that is not a number. A second query on the same table lists it:

duckdb -box -c "CREATE TEMP TABLE raw AS SELECT * FROM read_csv('late-text.csv', all_varchar=true); SELECT id, qty FROM raw WHERE TRY_CAST(qty AS INTEGER) IS NULL"

┌───────┬─────┐
│  id   │ qty │
├───────┼─────┤
│ 30000 │ n/a │
└───────┴─────┘

Row 30,000 with qty equal to n/a. One caution from my runs: the same TRY_CAST written directly against read_csv(..., all_varchar=true) and combined with a FILTER (WHERE TRY_CAST(...) IS NULL) in one aggregate query failed with the same conversion error as the original. The simple form, a sum over TRY_CAST straight from the file, worked. I did not chase the cause. Loading into a table first, so that the reader finishes before the conversion starts, worked every time, and it is the pattern I would recommend anyway.

How to tell it worked

Run DESCRIBE on the final form of the query and check every type against what you know about the data. Run SUMMARIZE and read the null percentage of every column you cleaned. Compare the row count with the line count of the file, less the header. The check that matters most is the count of rows that were NULL or failed to convert, because that number is the size of the problem the sniffer would have hidden.

When to stop trusting the sniffer

For a one-off look at a file you have never seen, trust it, and read DESCRIBE. For a file that arrives on a schedule, do not: take the Prompt, edit the types you know, and keep it in the script. For a file that comes from several sources or is appended to by hand, expect surprises past the sample and use sample_size=-1 at least once to see them. For a file with fewer than a few hundred rows, the sample is the whole file and none of this applies. And for a file whose structure the sniffer cannot find at all, with ragged rows or a delimiter that changes, it will quietly fall back to one wide text column. That is the signal to fix the file, with the tools in the format rules hub, rather than to keep adding options.