Why a CSV File Has No Types and What That Costs You

A CSV file stores characters and nothing else. The number 007, the ZIP code 00501, the date 03/04/2025 and the id 9007199254740993 are all just text until a reader decides otherwise, and the decision is made again by every tool that opens the file. That decision has a price in time and memory and it has a price in correctness. I measured both on a 2,000,000 row file, and the correctness cost is the one that matters: 190,451 ZIP codes lost a leading zero in a default pandas read. The format guide mentions the finding in passing, and this is the full table.

The test file

The file has 2,000,000 rows and eight columns that mimic an orders export: an integer id, a customer id, a five digit ZIP code with leading zeros preserved, an amount with two decimals, an ISO timestamp, and three low-cardinality labels including a mostly empty note. A seeded generator writes it, so the same bytes come out on every run.

"""Write a 2,000,000 row orders.csv with the usual mix: ids, zero padded ZIP codes, money, timestamps, labels."""
import sys

import numpy as np
import pandas as pd

n = int(sys.argv[2]) if len(sys.argv) > 2 else 2_000_000
rng = np.random.default_rng(42)
df = pd.DataFrame({
    "order_id": np.arange(1, n + 1),
    "customer_id": rng.integers(1, 250_000, n),
    "zip": [f"{z:05d}" for z in rng.integers(500, 99_999, n)],
    "amount": np.round(rng.gamma(2.0, 40.0, n), 2),
    "created_at": pd.to_datetime("2025-01-01") + pd.to_timedelta(rng.integers(0, 365 * 86400, n), unit="s"),
    "status": rng.choice(["paid", "refunded", "pending", "failed", "shipped"], n),
    "country": rng.choice(["US", "DE", "FR", "JP", "BR", "IN", "GB"], n),
    "note": rng.choice(["", "gift", "rush order", "call first", "leave at door"], n),
})
df.to_csv(sys.argv[1], index=False)

The result is 129.9 MB with the header and these first rows:

130 MB
order_id,customer_id,zip,amount,created_at,status,country,note
1,22313,99154,103.1,2025-11-22 13:08:24,shipped,GB,rush order
2,193489,34494,31.59,2025-03-03 08:03:33,failed,BR,leave at door
2000001 orders-2m.csv

Nothing in it is unusual. That is the point: this is a typical export.

What the readers did with it

I read the file six ways and recorded the median time of three runs and, for pandas, the memory of the resulting frame. The first pandas row gives the reader no hints. The next two declare a dtype per column, with and without parsing the timestamp. The fourth reads everything as text. The two DuckDB rows load into a table, once with sniffed types and once with declared columns.

python bench_types.py

reader                              median s   RAM MB
pandas, no hints                        0.18      198
pandas, dtype map                       0.48      110
pandas, dtype map + parse_dates         0.87       72
pandas, everything as text              2.00      242
DuckDB, sniffed types                   0.34         
DuckDB, declared columns                0.32         

what pandas guessed: {'order_id': 'int64', 'customer_id': 'int64', 'zip': 'int64', 'amount': 'float64', 'created_at': 'str', 'status': 'str', 'country': 'str', 'note': 'str'}
ZIP codes starting with 0 in the file: 190451
ZIP codes shorter than 5 characters after the no-hints read: 190451

The table makes three points. The first is that pandas with no hints is the fastest pandas row by a wide margin, 0.18 s, because it is doing the least: it leaves the timestamps as text and skips the work of deciding what the string columns are. It is also the answer with the most wrong in it.

The second is that declaring the types is not free and is not especially expensive. The dtype map took 0.48 s, 0.30 s longer than the default, and it cut the memory from 198 MB to 110 MB because the three label columns became categories and the ids became 32 bit integers. Parsing the timestamps added another 0.39 s and brought the frame to 72 MB, since a datetime64 is smaller than a string in pandas 3. Reading everything as text was the slowest and heaviest at 2.00 s and 242 MB, and it is the right choice for a first look at a file you do not trust, not for analysis.

The third is that DuckDB did about the same work whether it sniffed or was told: 0.34 s sniffed, 0.32 s declared. Its sniffer works from a bounded sample of the file, so inference costs it almost nothing, and the benefit of declaring is correctness, not speed.

What pandas guessed

The last lines of the same run list the dtypes that pandas chose without help:

190,451 of 2,000,000 ZIP codes start with a zero, which is 9.5%, and the number of ZIP codes shorter than five characters after the default read is exactly the same figure. There is no warning. The data is changed at the moment of loading, and the first symptom is a mailing that cannot find 2134 in a table of 02134. Why pandas turns ZIP codes into numbers goes through the mechanism, and the fix is a single dtype={"zip": str}.

Three ways inference loses information

ZIP codes are the famous one. Two others are worth seeing, because they are subtler.

The first is the large id. A column of 64-bit identifiers fits in int64 exactly. If one cell is empty, pandas needs a way to represent a missing integer, uses NaN, and converts the whole column to float64. A float has 53 bits of precision, so integers above 2 to the power of 53 stop being exact. I made a three row file with two big ids and one empty id:

id,balance
9007199254740993,10
,5
9007199254740995,7
default dtype: float64
default values: [9007199254740992, 9007199254740996]
Int64 dtype:    Int64 [9007199254740993, 9007199254740995]

Read with defaults, the column became float64 and the two ids came back as 9007199254740992 and 9007199254740996, neither of which is in the file. The nullable Int64 type kept both exactly. If the ids are identifiers rather than quantities, declare them text and never do arithmetic on them. The same thing happens at smaller sizes with any column that mixes whole numbers and blanks: it turns into floats, and 12 shows up as 12.0 downstream.

The second is the ambiguous date. 03/04/2025 is the 3rd of April in most of the world and the 4th of March in the United States. Two readers looked at the same two row file and disagreed. pandas, with parse_dates and no format, read 03/04/2025 and 04/05/2025 as March 4 and April 5. DuckDB read the same text as April 3 and May 4. Add a row with 13/05/2025, which can only be day first, and pandas gave up and left the whole column as text, while DuckDB parsed all three as day first:

dates-ambiguous.csv dayfirst: ['2025-04-03', '2025-05-04']
dates-ambiguous.csv default : ['2025-03-04', '2025-04-05']
dates-eu.csv dayfirst: ['2025-04-03', '2025-05-04', '2025-05-13']
dates-eu.csv default : ['03/04/2025', '04/05/2025', '13/05/2025']
duckdb ambiguous: [2025-04-03, 2025-05-04]
duckdb with 13/05: [2025-04-03, 2025-05-04, 2025-05-13]

When every row is ambiguous there is no evidence in the file for either reading, so the answer is whatever the reader's default happens to be, and the defaults differ. Write the format into the declaration: date_format="%d/%m/%Y" in pandas or dateformat='%d/%m/%Y' in DuckDB. As the first and third lines of the output show, an explicit format also removes the surprise of a column staying text.

Declare once and store it with the code

The remedy is to say what the columns are and keep that statement in version control. In pandas it is a dictionary; in DuckDB a columns struct; in PostgreSQL the table definition itself; in SQLite a typed CREATE TABLE before .import. Here is what the declared DuckDB read looks like, followed by what it does when the declaration is too narrow. I declared amount as DECIMAL(4,2), which cannot hold a value above 99.99, and the read stopped on the first row:

Conversion Error: CSV Error on Line: 2
Original Line: 1,22313,99154,103.1,2025-11-22 13:08:24,shipped,GB,rush order
Error when converting column "amount". Could not convert string "103.1" to 'DECIMAL'

Column amount is being converted as type DECIMAL
This type was either manually set or derived from an existing table. Select a different type to correctly parse this column.
* Check whether the null string value is set correctly (e.g., nullstr = 'N/A')

pandas does the same when you ask for a smaller integer than the data needs:

ValueError: cannot safely convert passed user dtype of int8 for int64 dtyped data in column 1

Both messages name the column and the value. That is the behavior you want in a pipeline, a failure at the boundary with a message, in place of a quiet conversion that you discover in a report. A declared type is a test that runs on every file.

For the same reason I like to read once as text, count anomalies, and then load. The validation guide and the article on checking that every date parses turn it into checks.

When letting the reader infer is the right call

Inference is the right default for exploring a file you will read once. Opening a 5 MB export to see what is in it does not merit a dtype map, and the speed and convenience of read_csv without arguments are why people love it. It is also fine when the data really is numeric, like sensor readings, where the worst case is a float that you wanted anyway.

It is the wrong default for anything recurring, for identifiers of any kind (ZIP codes, phone numbers, account numbers, SKUs), for money that must add up, and for any file whose source you do not control. If a file is read the same way every night, spend the five minutes it takes to write the declaration once. And if you find yourself writing declarations for every file you receive, the underlying fix is a format that carries its own types. Converting to Parquet is the usual first step, and the comparison with CSV for repeated analysis shows the size and speed difference.