How few rows you actually need

Most questions I get asked about a table are about its middle: typical order value, median time to first purchase, average session. For those a random sample of a few hundred rows gives the same answer as the whole table, and I can show it with a table small enough that you can watch every draw.

What happened at each size

The table is 3,104 self-reported salaries from the ai-jobs.net survey, the file behind the salary map post. The median is $140,000, the mean $144,054, the 95th percentile $249,925. Those are the solid lines.

The first seeded draw of 10 rows has a median of $110,000, off by $30,000. Run the draw 200 times and the medians land anywhere between $72,500 and $199,000. The bootstrap interval from that one sample of ten runs from $68,548 to $169,750, a width of $101,202. Honest, and useless.

At 100 rows the first draw gives $136,250, off by $3,750. Across 200 draws, 176 land within $10,000 of the truth and the middle 90% of them span $20,715. The bootstrap interval is $121,996 to $153,000, a width of $31,004. For a “what do data people earn” question that is already the answer.

At 1,000 rows the first draw gives $141,295, every one of the 200 medians lands within $5,000 of the truth, and the bootstrap width is $5,884. You could not tell this sample from the full table in a meeting.

When sampling is safe

Central estimates. Medians, means, proportions, rates. Anything where every row contributes a little and no single row matters. The error of a sample mean shrinks with the square root of n, so ten times the rows buys about three times the precision, and it flattens fast. The gap between 100 and 1,000 above is “close” versus “closer”. The gap between 10 and 100 is “no idea” versus “close”.

Exploration and development. Looking at a new table, I sample: distributions, null rates, whether the ids are unique. Building a dbt model, I build it against a sample first. The logic does not care whether it saw a million rows or a thousand. The bill does.

When it is not

Tails. At 100 rows only five sit above the 95th percentile, so the estimate is made from five numbers. The middle 90% of 200 estimates spans $56,972 for the 95th percentile against $20,715 for the median, from the same sample size. A p99 is worse again. If the question is about the slowest requests or the largest customers, sample more or do not sample.

Rare groups. A 100-row sample gives analysts 29 rows and a median within $2,480 of the truth. Research scientists get five rows and miss by $36,000. Data managers get zero. Any breakdown by category is a set of separate samples, each with its own n, and the small ones are wrong. The sample size that matters is the smallest group’s, not the table’s.

Joins. A 1% sample of orders joined to an independent 1% sample of customers keeps about 0.01% of the matched pairs. Sample one side, the side with the grain you are counting, and join it to the full other table.

Totals. A sample gives you a rate. Scaling it to a total carries the sampling error with it. Fine for a sanity check, not for finance.

How to sample cheaply in a warehouse

LIMIT 1000 is not a sample. It is the first thousand rows in whatever order the engine found them, usually one partition or one loader batch. Most of the surprises I have seen from “I checked a sample” came from this.

ORDER BY random() LIMIT 1000 is a real sample that costs a full scan plus a sort, which is the thing you were trying to avoid.

TABLESAMPLE is the cheap one, and Snowflake, BigQuery, Postgres, and DuckDB all have a form of it. Block-level sampling reads a fraction of the storage blocks and is nearly free, but rows from the same batch arrive together. Row-level (Bernoulli) sampling still scans but skips the sort. Block for exploration, row when independence matters.

Hashing an id is the one I use most, because it is reproducible and survives joins:

SELECT *
FROM orders
WHERE MOD(ABS(HASH(customer_id)), 100) < 1

That keeps about 1% of customers with every one of their orders, so per-customer metrics stay intact, and the same predicate on any other table keyed by customer_id picks the same customers. Change nothing and you get the same sample next week.

What to do

• Before scanning a large table for a central number, run it on a 1,000-row hash sample first. Then decide whether the full scan is worth it.
• Report a bootstrap interval, or at least two seeds, with every sampled number. The width is the point.
• For percentiles above p90, rare segments, and totals, do not sample, and say so in the query comment.
• Sample one side of every join, on a hashed key, and keep the predicate in one macro so every table agrees on who is in it.
• Build dbt models against a sampled source in dev and the full table only in CI and prod.