skip to content

In a columnar warehouse, why can't you scale a 1% TABLESAMPLE distinct count up by 100?

level: middleimportance: should knowfreq 45%

answer

  1. some aggregates scale with the sample rate, some do not
  2. a frequent value is in the sample already
  3. a unique value usually is not
  4. the right multiplier depends on unknown frequencies
  5. block sampling draws neighbours, not independent rows

basics

~20 s

Distinct counts do not scale with the sample rate. If values repeat, a 1% sample already sees nearly all of them and multiplying by 100 overcounts massively; if values are unique the raw sample count undercounts. The correct factor depends on an unknown frequency distribution.

solid answer

~50 s

Sampling extrapolates for aggregates that are linear in the rows — `COUNT(*)` and `SUM` scale by the inverse sample rate, and `AVG` needs no scaling at all. Distinct counts are not linear in the rows. Take a user id that appears a thousand times: a 1% row sample almost certainly catches it, so a sample of a table with a million such users returns nearly a million distinct ids, and multiplying by 100 gives a hundred million — a hundredfold overcount. At the other extreme, if every value is unique the sample returns exactly 1% of them and the raw number undercounts by the same factor. The right multiplier lies somewhere between 1 and 100 and depends on the value-frequency distribution you do not know. The same applies to `MIN`, `MAX` and tail percentiles. If you want a cheap distinct count, use a HyperLogLog-style sketch over the full scan, not a sample.

code

sql · 7 lines
sql
-- wrong: no valid scaling factor exists for a distinct count
SELECT COUNT(DISTINCT user_id) * 100 AS users_guess
FROM events TABLESAMPLE BERNOULLI (1);

-- right: full scan, fixed-size sketch, known relative error
SELECT APPROX_COUNT_DISTINCT(user_id) AS users_est
FROM events;

go deeper

for a junior

Recall that counts and sums can be scaled up from a sample but distinct counts, minimums and maximums cannot, and that sampling is for exploration rather than reported metrics.

for a middle

Explain the mechanism with the frequency argument — frequent values are already in the sample, rare ones are missing — and distinguish row-level from block-level sampling and what each saves.

for a senior

Spot the bias in a real pipeline: clustered tables making block samples unrepresentative, vanished small groups, and a metric quietly built on a sampled extrapolation. Offer the sketch as the replacement.

for a principal

Set the rule for where sampling is permitted across the platform — development and profiling yes, published metrics no — and make sure the cheap-but-approximate path people reach for is a sketch pipeline rather than a sample.

## What sampling gives you `TABLESAMPLE` asks the engine to process a fraction of the table. The SQL standard distinguishes two methods and the distinction matters enormously in an analytical engine. **BERNOULLI** (row-level) includes each row independently with the stated probability. The sample is statistically clean — every row has the same chance, and rows are independent. But the engine still has to read every row to decide, so in a columnar store the bytes scanned are essentially unchanged. You save downstream CPU (join, aggregate, sort), not I/O. **SYSTEM** (block-level) includes or excludes whole storage blocks or files. This genuinely reduces bytes read, because skipped blocks are never fetched — which is why it is the cheap option. But it is a **cluster sample**: the rows inside a block are not independent draws, they were written together. ## Which aggregates extrapolate An aggregate can be estimated from a uniform random row sample if it is (roughly) linear in the rows. - `COUNT(*)` — divide by the sample rate. Unbiased. - `SUM(x)` — divide by the sample rate. Unbiased, though variance is high if `x` is heavy-tailed and the big values are rare. - `AVG(x)` — use directly, no scaling. Unbiased. - Central quantiles — reasonably estimated from a decent-sized sample. And the ones that do not: - `COUNT(DISTINCT x)` — no valid scaling factor exists without knowing the frequency distribution. - `MIN` / `MAX` — extremes are by definition rare, so a sample almost always misses them and reports a value that is too high / too low. - Extreme percentiles — p999 over a 1% sample is estimated from a hundredth of the tail evidence. - Anything involving deduplication, existence or anti-joins. ## Why distinct counts break, precisely Consider a table of N rows with D distinct values, sampled at rate r. Whether a particular value appears in the sample depends on how many rows carry it. A value occurring `f` times is missed with probability roughly `(1−r)^f`. If `f` is large — say each user has a thousand events and `r = 0.01` — the miss probability is negligible, so the observed distinct count is nearly `D` itself. Multiply by 100 and you report `100 × D`. If `f = 1` — every value unique — the observed distinct count is about `r × D`, and multiplying by 100 recovers `D` correctly. Real data sits between these, and typically it is a mixture: a handful of very frequent values plus a long tail of one-offs, which is the worst case for any single scaling factor. Statisticians have studied distinct-count estimation from samples extensively, and the standing result is discouraging: no estimator is reliably accurate across frequency distributions without assumptions about the tail. This is why warehouses ship sketches for distinct counts rather than telling you to sample. ## Cluster-sample bias with block sampling Block sampling adds a second, subtler problem. Blocks are written together, so their contents are correlated with anything that correlates with load order or table clustering. If a table is sorted or clustered by date, a block sample is effectively a sample of *date ranges*, not of rows — take 1% of blocks and you may get whole days in and whole days out. Any measure that varies over time is then skewed, and the sample's variance is far larger than a row sample of the same size would suggest. The same happens with any column correlated with the sort order: region, tenant, source system. When a block sample is used for something that must be representative, sample at a rate high enough that many blocks are drawn from every part of the table, or use row-level sampling and accept the full scan. ## Small groups disappear A `GROUP BY` over a sample silently loses rare categories. A dimension value with 50 rows in the table has a good chance of contributing zero rows to a 1% sample, so the category is absent from the result — not shown as zero, absent. Anyone reading the output sees a complete-looking list that is missing its long tail. Grouped queries over samples need explicit care about which groups are large enough to survive. ## Where sampling is genuinely the right tool Sampling earns its keep in query development — checking that a transformation runs and produces plausible shapes before paying for a full scan; in data profiling where approximate distributions are all you need; in visual exploration such as scatter plots that cannot render a billion points anyway; and in expensive downstream steps where a `BERNOULLI` sample after a join reduces the sort or model-training cost. What it is not is a substitute for a sketch. For a cheap distinct count, scan everything and hash into a HyperLogLog: full accuracy over the population with a known error bound, instead of an unknown bias from a sample.

  • Which sampling method actually reduces bytes read in a columnar engine, and what does it cost you?
    Block-level (SYSTEM) sampling, because whole blocks or files are skipped and never fetched. Row-level (BERNOULLI) sampling still reads everything and only discards rows afterwards, so it saves downstream CPU rather than I/O. The price of block sampling is correlation: rows in a block were written together, so the sample is clustered rather than independent.
  • Why can a 1% sample make a GROUP BY result look complete when it is not?
    Because rare groups contribute zero sampled rows and therefore vanish from the output entirely rather than appearing with a zero. The result set looks well-formed, so nothing signals the omission. Any grouped query over a sample needs a stated minimum group size, or the long tail should be read from the full table.
  • Is there any situation where sampling beats a sketch for distinct counts?
    Only when you cannot afford to touch every row at all — for instance a quick profiling pass over a table you are about to model, where you want a rough sense of whether a column is near-unique or highly repeated. Even then, treat the number as a shape indicator, not an estimate of the population cardinality.

Tasting a spoonful of soup tells you the average saltiness reliably, but not how many distinct ingredients went in — the common ones are all in your spoon and the rare ones are all missing.

saying these in an interview costs you the question

  • Scales a sampled distinct count by the inverse sample rate
  • Believes row-level sampling reduces bytes scanned
  • Treats a block sample as a uniform random row sample
  • Estimates MAX or a p999 from a small sample
  • Reports a grouped sample result without noting missing rare groups

context

resolving links…