The CSV auto-detector was implemented by Till Döhmen, who did his master thesis on the subject [1] and has actually written a paper about it [2].
Essentially we have a list of candidate types for each column (starting with all types). We then sample a number of tuples from various parts of the file, and progressively reduce the number of candidate types as we detect conflicts. We then take the most restrictive type from the remaining set of types, with `STRING` as a last resort in case we cannot convert to any other type. After we have figured out the types, we start the actual parsing.
Note that it is possible we can end up with incorrect types in certain edge cases, e.g. if you have a column that has only numbers besides one row that is a string. If that row is not present in the sampling an error will be thrown and the user will need to override the type inference manually. This is generally rather rare, however.
You could also use DuckDB to do your type-inference for you!
duckdb -c "DESCRIBE SELECT * FROM taxi.csv"
And if you want to change the sample size:
duckdb -c "DESCRIBE SELECT * FROM read_csv_auto('taxi.csv', sample_size=9999999999999)"
That works and is similar to what DuckDB does for small CSV files. We have the sampling step primarily for larger CSV files, when you might not want to do two passes over the file. This way we can keep the benefits of streamed file reading while offering type inference that "just works" most of the time without a major performance penalty.
