Sampling a CSV for a Fast First Look at Large Data

Opening a 40 GB CSV to answer "what does this look like" is a poor use of an afternoon. A sample answers it in seconds: a few thousand rows are enough to see the columns, the formats, the typical values and the obvious problems. The skill is in taking the sample so that it tells the truth about the rest, and in knowing what it cannot tell you. This article compares the ways to take one, shows how the error shrinks with the size of the sample using real numbers from a 2 million row file, and says where sampling stops being a good idea. It is the fifth move in more analysis moves for CSV data, and it extends sampling random rows from a huge CSV, which focuses on the mechanics.

Why not just head

head -n 1000 file.csv is the quickest look, and it is a fine way to see the header and the formats. It is a poor sample, because files are not shuffled. They are ordered by time, by customer, by an export's internal key or by the hour a batch ran, and the top of the file may be the oldest data, one customer's data or the data from the weekend before a system change. In the test file, the first 1,000 rows had a mean amount of 80.85. The true mean over 2 million rows is 80.09, so the head overestimated by 0.76, which is more than the error of any random sample of 10,000 rows in the runs below. The first 20 rows contained six failed orders, three paid, five pending, two refunded and four shipped, plausible for this file and a coincidence you cannot rely on for another.

The risk is greater with real files. If the file is sorted by date, the head is the earliest period, before the formats changed. If it is sorted by a category, the head contains one category. And a type-inferring reader that looks at the first rows, as many do, inherits the same bias: that is why zip codes without leading zeros at the top of a file become integers (sampling in type inference).

Random sampling in SQL

DuckDB can sample a file as part of the query, without loading it:

SELECT avg(amount) FROM read_csv('orders-2m.csv')
USING SAMPLE reservoir(10000 ROWS) REPEATABLE (42);

reservoir(10000 ROWS) asks for exactly 10,000 rows chosen uniformly at random by reservoir sampling, which reads the file once and keeps a fixed-size random subset in memory. REPEATABLE (42) fixes the seed, so the same query returns the same sample every time, which is what you need if you quote a number to a colleague and they run it again. Without it, each run gives a different answer. A percentage form, USING SAMPLE 1 PERCENT (bernoulli), includes each row independently with probability 1 percent, giving about, not exactly, 1 percent of the rows. The default method for a percentage in DuckDB is a system sample that picks chunks of rows, which is faster and less random, so name bernoulli when randomness matters.

Sampling inside the query still reads the whole file, because there is no way to pick row 1,234,567 of a CSV without parsing everything before it. The saving is in what follows: the aggregate runs on 10,000 rows and not 2 million. To avoid even the read, take the sample once, write it to a small file and work from that:

COPY (SELECT * FROM read_csv('orders-2m.csv') USING SAMPLE reservoir(100000 ROWS) REPEATABLE (42))
TO 'orders-sample.csv' (HEADER);

Other tools: qsv sample 10000 orders.csv takes a random sample of that many rows, and shuf -n 10000 shuffles lines, which breaks on a CSV with quoted newlines and loses the header unless you handle it. A systematic sample, every hundredth row, is awk 'NR == 1 || NR % 100 == 0', which keeps the header, is fast, and is only safe when the file has no periodic structure that lines up with the step (a file that repeats a pattern every 100 rows would be sampled at the same phase every time).

How big is big enough

A larger sample is more precise, and the gain slows quickly. The standard error of a mean falls as one over the square root of the sample size, so quadrupling the sample halves the error. The measurement on the test file, five runs at each size, with the true mean of 80.09:

sample   1000: 79.13, 77.58, 82.09, 81.28, 80.11   spread 4.50
sample  10000: 80.18, 80.83, 80.14, 80.78, 79.89   spread 0.94
sample 100000: 79.95, 79.88, 80.18, 80.03, 80.06   spread 0.30
first 1000 rows mean 80.85

At 1,000 rows, the five estimates range over 4.50, from 77.58 to 82.09, about 5 percent of the mean, which is enough to see the order of magnitude and not enough to say whether a number moved. At 10,000 rows, the range falls to 0.94, about 1 percent. At 100,000, 0.30, about a third of a percent. A hundredfold increase in sample size bought a fifteenfold gain in precision here, more than the tenfold the square-root rule predicts, which is five runs being a noisy way to measure a spread. For an exploratory look, 10,000 to 100,000 rows is usually plenty. The standard deviation of the column drives the whole calculation: a column with little variation needs a small sample, and a heavy-tailed one needs a large one. Compute the standard error as the standard deviation divided by the square root of n, and you can estimate the sample size you need before taking it.

What a sample cannot tell you

Sampling answers "what is typical" and fails at "what exists". A category that makes up 0.1 percent of the rows appears about once in a sample of 1,000 and is missed entirely in a good share of samples, so a sample of customers will not show your rare country, your unusual plan or the handful of malformed rows. Those are the things a profile of the full file finds, and a validation pass over every row is the only way to claim there are no bad rows (profiling a new CSV file). Similarly, extremes: the maximum of a sample is lower than the maximum of the file, nearly always, so do not report a sample's max as the file's max.

Totals and counts must be computed on the full file. A sample mean times the row count estimates a total, with an error, and a total for a ledger or an invoice cannot carry an error. And sample-based conclusions about small subgroups are weak: 1,000 rows of which 20 are from Japan tell you little about Japan.

Stratified and clustered files

If the file mixes populations, a simple random sample underrepresents the small ones. A stratified sample takes a fixed number from each group, for example 1,000 rows per country, and weights the results back when estimating totals. In DuckDB, that is a window function: row_number() OVER (PARTITION BY country ORDER BY random()), keeping those with a row number at most 1,000. If rows come in clusters, all the orders from one customer together, sampling rows spreads one customer's orders across the sample and understates how correlated they are; sample customers and take all of each one's orders when the question is about customer behavior.

When a sample is not enough

Do not sample when the answer has to be exact, when the event you care about is rare, or when the audience will treat the number as the real one. A first look at structure, formats and typical values is exactly where a sample shines, and the habit to build is to move from the sample to the full file as soon as the question becomes a number someone will act on. Say in the report that a figure came from a sample, with the size and the seed. The DuckDB syntax is described in its sample documentation, and for the underlying statistics, the NIST e-Handbook is a patient reference. For files too big to read at all, handling CSV files that are too big covers the first steps.