Fixing DuckDB CSV Type Detection That Guessed Wrong

The sniffer is right most of the time, and when it is wrong the damage is rarely loud. A ZIP code that becomes a number loses its leading zero and nothing complains. A 20 digit identifier that becomes a floating point value loses its last digits and nothing complains either. This article uses one small file with eight columns that are all easy to misread and shows what DuckDB made of each, then three ways to change the answer. The mechanics of the sniffer are in the sniffing article, and the reason a CSV cannot carry types at all is in why a CSV has no types.

The test file

Three rows, eight columns, each chosen because a reader could go either way:

id,zip,flag,answer,seen,ratio,big,when_ts
001,02134,T,Y,2025-03-04,10%,12345678901234567890,2025-03-04 10:15:00
002,90210,F,N,2025-03-05,15%,98765432109876543210,2025-03-05 11:30:00
003,00501,T,Y,2025-03-06,20%,1,2025-03-06 12:45:00

id and zip have leading zeros. flag is T or F, answer is Y or N. seen is an ISO date and when_ts an ISO timestamp. ratio carries a percent sign. big holds two 20 digit numbers and a 1. Read it with no options:

duckdb -box -c "DESCRIBE SELECT * FROM 'types-guess.csv'"

┌─────────────┬─────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key  │ default │ extra │
├─────────────┼─────────────┼──────┼──────┼─────────┼───────┤
│ id          │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ zip         │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ flag        │ BOOLEAN     │ YES  │ NULL │ NULL    │ NULL  │
│ answer      │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ seen        │ DATE        │ YES  │ NULL │ NULL    │ NULL  │
│ ratio       │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ big         │ HUGEINT     │ YES  │ NULL │ NULL    │ NULL  │
│ when_ts     │ TIMESTAMP   │ YES  │ NULL │ NULL    │ NULL  │
└─────────────┴─────────────┴──────┴──────┴─────────┴───────┘

Column by column, this is what happened:

The ZIP result is the one people expect to be wrong and is not: DuckDB declines to turn a value with a leading zero into an integer, because it would not read back as the same text. The sniffer's BOOLEAN for T/F is the one I did not expect. DuckDB accepted the single letters as boolean values in this test, and I did not look for the full list of accepted spellings; a column that was meant to hold the text T and F, say a status code, would come back as true and false. Look at the values the query returns, not only at the type:

duckdb -box -c "SELECT id, zip, flag, answer, seen, ratio, big FROM 'types-guess.csv' LIMIT 3"

┌─────┬───────┬───────┬────────┬────────────┬───────┬──────────────────────┐
│ id  │  zip  │ flag  │ answer │    seen    │ ratio │         big          │
├─────┼───────┼───────┼────────┼────────────┼───────┼──────────────────────┤
│ 001 │ 02134 │ true  │ Y      │ 2025-03-04 │ 10%   │ 12345678901234567890 │
│ 002 │ 90210 │ false │ N      │ 2025-03-05 │ 15%   │ 98765432109876543210 │
│ 003 │ 00501 │ true  │ Y      │ 2025-03-06 │ 20%   │ 1                    │
└─────┴───────┴───────┴────────┴────────────┴───────┴──────────────────────┘

flag shows true, false, true. The 20 digit big column arrived intact as HUGEINT, which is a 128 bit integer and holds 38 digits. That is correct for these values, and fragile in the same way as any inferred type: if a later row has a 40 digit value, the column's type changes.

Fix 1: override the columns that matter

The smallest repair is to name the columns whose type you want to set and leave the rest to the sniffer:

duckdb -box -c "DESCRIBE SELECT * FROM read_csv('types-guess.csv', types={'id': 'VARCHAR', 'zip': 'VARCHAR', 'big': 'VARCHAR'})"

┌─────────────┬─────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key  │ default │ extra │
├─────────────┼─────────────┼──────┼──────┼─────────┼───────┤
│ id          │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ zip         │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ flag        │ BOOLEAN     │ YES  │ NULL │ NULL    │ NULL  │
│ answer      │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ seen        │ DATE        │ YES  │ NULL │ NULL    │ NULL  │
│ ratio       │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ big         │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ when_ts     │ TIMESTAMP   │ YES  │ NULL │ NULL    │ NULL  │
└─────────────┴─────────────┴──────┴──────┴─────────┴───────┘

id, zip and big are now VARCHAR, and every other column kept its detected type: flag is still BOOLEAN, seen is DATE, when_ts is TIMESTAMP. The types parameter takes a struct from column name to type. The data types reference lists what is available, and the same struct form appears in the read_csv options article. I use the struct form, since the name makes the intent obvious to the next reader and a reordered file does not break it.

The overall approach to files you query in place is in the DuckDB CSV guide. Choose VARCHAR for identifiers that you never do arithmetic on. A 20 digit order number, an account number and a ZIP code are labels, and labels are text, even when every digit is a digit.

Fix 2: read it all as text, then convert

When the file is dirty enough that you want to see every conversion, ask for text everywhere and convert in SQL:

duckdb -box -c "SELECT id, zip, TRY_CAST(replace(ratio, '%', '') AS DOUBLE) / 100 AS ratio, TRY_CAST(big AS HUGEINT) AS big_hugeint, flag = 'T' AS flag_bool FROM read_csv('types-guess.csv', all_varchar=true)"

┌─────┬───────┬───────┬──────────────────────┬───────────┐
│ id  │  zip  │ ratio │     big_hugeint      │ flag_bool │
├─────┼───────┼───────┼──────────────────────┼───────────┤
│ 001 │ 02134 │ 0.1   │ 12345678901234567890 │ true      │
│ 002 │ 90210 │ 0.15  │ 98765432109876543210 │ false     │
│ 003 │ 00501 │ 0.2   │ 1                    │ true      │
└─────┴───────┴───────┴──────────────────────┴───────────┘

ratio is 0.1, 0.15 and 0.2: replace(ratio, '%', '') removes the sign, TRY_CAST(... AS DOUBLE) makes the number and dividing by 100 turns a percentage into a fraction. flag = 'T' produces a real boolean. The IDs and ZIP codes stay as written, and big is 12345678901234567890 as text, with the value in HUGEINT for the cast. This is the right shape for any column that needs more than a type name to read correctly. The sniffer cannot know that 10% is one tenth, and neither can any other tool; you say it once, in a query you can read.

The extra effort buys a property I value: a TRY_CAST that fails returns NULL, and counting those NULLs tells you how many values did not parse. As the sniffing article notes, this pattern behaved best when the file was first loaded into a table, then converted.

The combined result with the overrides and a conversion for the percentage column looks like this:

duckdb -box -c "SELECT id, zip, flag, seen, TRY_CAST(replace(ratio,'%','') AS DOUBLE)/100 AS ratio, big FROM read_csv('types-guess.csv', types={'id':'VARCHAR','zip':'VARCHAR','big':'VARCHAR'}) LIMIT 3"

┌─────┬───────┬───────┬────────────┬───────┬──────────────────────┐
│ id  │  zip  │ flag  │    seen    │ ratio │         big          │
├─────┼───────┼───────┼────────────┼───────┼──────────────────────┤
│ 001 │ 02134 │ true  │ 2025-03-04 │ 0.1   │ 12345678901234567890 │
│ 002 │ 90210 │ false │ 2025-03-05 │ 0.15  │ 98765432109876543210 │
│ 003 │ 00501 │ true  │ 2025-03-06 │ 0.2   │ 1                    │
└─────┴───────┴───────┴────────────┴───────┴──────────────────────┘

And a boolean from the Y/N column, where the sniffer left text:

duckdb -box -c "SELECT answer = 'Y' AS answer_bool FROM read_csv('types-guess.csv')"

┌─────────────┐
│ answer_bool │
├─────────────┤
│ true        │
│ false       │
│ true        │
└─────────────┘

Fix 3: narrow the candidate types, with care

auto_type_candidates restricts the ladder of types the sniffer may choose from. It is the tool to reach for when you want a column never to become a date or boolean, without naming the column. Here I allowed only BIGINT, DOUBLE and VARCHAR:

duckdb -box -c "DESCRIBE SELECT * FROM read_csv('types-guess.csv', auto_type_candidates=['BIGINT', 'DOUBLE', 'VARCHAR'])"

┌─────────────┬─────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key  │ default │ extra │
├─────────────┼─────────────┼──────┼──────┼─────────┼───────┤
│ id          │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ zip         │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ flag        │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ answer      │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ seen        │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ ratio       │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ big         │ DOUBLE      │ YES  │ NULL │ NULL    │ NULL  │
│ when_ts     │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
└─────────────┴─────────────┴──────┴──────┴─────────┴───────┘

flag, seen and when_ts are text now, because the date and boolean types are off the list. That is the intent. The surprise is big: with HUGEINT unavailable, the sniffer chose DOUBLE, the narrowest listed type that fits a 20 digit number. The value is where the harm shows:

duckdb -box -c "SELECT big::DECIMAL(38,0) AS as_double, (SELECT big FROM read_csv('types-guess.csv', all_varchar=true) LIMIT 1) AS original FROM read_csv('types-guess.csv', auto_type_candidates=['BIGINT','DOUBLE','VARCHAR']) LIMIT 1"

┌──────────────────────┬──────────────────────┐
│      as_double       │       original       │
├──────────────────────┼──────────────────────┤
│ 12345678901234567168 │ 12345678901234567890 │
└──────────────────────┴──────────────────────┘

12345678901234567890 in the file became 12345678901234567168 after the trip through a DOUBLE. A double carries about 15 to 17 significant digits, so the last digits are changed to the nearest value it can represent, and nothing raises an error. For identifiers and money, that is the failure to design against. If you narrow the candidates, keep VARCHAR on the list and check every numeric column with SUMMARIZE, or give the wide numbers an explicit types entry.

What the error looks like when the guess is too narrow

The other direction of failure is loud. If the sample suggests BIGINT and a later row holds text, the query stops with Could not convert string "n/a" to 'BIGINT', and the message ends with the same list of options. The message names the three fixes: override the type for the column, raise the sample size, or set nullstr. The sniffing article has that case on a 40,000 row file. The pattern for both directions is the same: find the column, decide what its values are, and say so.

How to tell it worked

Run DESCRIBE and compare each type with what the data means, not with what it looks like. Select a few values from every column you overrode and compare them with the file by eye. For identifiers, check that count(DISTINCT col) equals the number of distinct values in the source, which a lossy conversion would reduce. And keep the final read_csv call, with its types, in your script, so that the next file is read the way this one was.

When the guess is the right answer

If the types match the meaning of the data, leave them alone: a DATE column is better than a VARCHAR you have to parse, and a BIGINT sorts and sums correctly where text does not. For analysis you do once on a file you understand, the sniffer's types save time. For a pipeline that runs unattended, prefer declared types, since an inferred type is a decision that can change when the data does. And when the types are wrong because the values are dirty, as in $1,200.50 or n/a, fix the values with replace, nullstr or a cleaning step rather than widening a column to text and carrying the mess through.