SAMPLE vs LIMIT
The problem
You want a “quick look” at a 2-billion-row table. LIMIT 100 returns in milliseconds. It is also a terrible random sample: official Snowflake documentation says that LIMIT returns the specified number of rows in the fastest way possible, while SAMPLE / TABLESAMPLE returns rows according to a sampling method.
If you then compute an average on those 100 rows and treat it as a profile of the table, you have profiled whatever micro-partitions Snowflake could reach first — often the newest data, not the population.
A working example
Official SAMPLE syntax (not executed on Snowflake):
-- Each row included with probability 10% (default method is BERNOULLI / ROW)
SELECT * FROM events SAMPLE (10);
SELECT * FROM events TABLESAMPLE BERNOULLI (20.3);
-- Fixed-size sample (up to 1,000,000 rows). If the table is smaller, you get the whole table.
SELECT * FROM events SAMPLE (10 ROWS);
-- Block sampling; often faster. Official docs: sample may be biased, especially on small tables.
SELECT * FROM events SAMPLE SYSTEM (3) SEED (82);
Official method pairs: SAMPLE = TABLESAMPLE; BERNOULLI = ROW; SYSTEM = BLOCK; REPEATABLE = SEED. If you omit the method, the default is BERNOULLI. Fraction probability is 0–100, not 0–1.
Official notes worth taking literally:
- Fraction-based
BERNOULLIhas expected size(p/100) * n, but because it is probabilistic the count is not exact. SYSTEM/BLOCKsamples storage-level units (in most cases, a micro-partition). On small tables the sample can be biased. Official docs say the difference should be negligible on very large tables, and that for very small tables sampling may operate at a finer granularity than a full micro-partition.SYSTEM,BLOCK, andSEEDare not supported with fixed-sizeROWSsampling. Official examples of errors:SAMPLE SYSTEM (10 ROWS)andSAMPLE ROW (10 ROWS) SEED (99).SEEDis not supported on views or subqueries.- A seed on an unchanged table should reproduce the same
SYSTEMsample. Sampling a copy of the table may not, even with the same seed and probability. - Sampling the result of a join is allowed only for row-based sampling without a seed, and it happens after the join. It does not reduce join cost.
SAMPLEattached to one table in aJOINapplies only to that table, not to the whole join expression.
LIMIT still has a job: preview a pipeline, paginate a UI, or take the top 10 after an ORDER BY. Combine them when you mean “a sample, but cap the payload”:
SELECT *
FROM events SAMPLE BERNOULLI (1)
LIMIT 1000;
That is “up to 1000 rows from a ~1% Bernoulli sample,” not “a statistically exact 1000-row sample.” Official docs note that fixed-size sampling can be slower than fraction-based sampling because it prevents some optimizations.
A portable demonstration that limit and sample are not the same operation was executed in DuckDB 2.0.0 on 1000 rows (USING SAMPLE BERNOULLI(10%) returned 93 rows; USING SAMPLE 10 returned 10 rows). DuckDB’s TABLESAMPLE syntax is not Snowflake’s. This does not describe Snowflake’s micro-partition behavior.
The gotcha
SAMPLE on a join line does not mean “sample the join result.” Official docs give this contrast: FROM t1 INNER JOIN t2 SAMPLE (50) samples t2 only. To sample the result, they show wrapping the join in a subquery and sampling the subquery — and that subquery sample cannot use SEED.
SYSTEM (1) on a table with very few micro-partitions can return roughly “all or nothing” chunks, which looks like a broken percentage if you expected 1% of rows. Official docs already warn about bias on small tables.
LIMIT without ORDER BY is not “random”; it is “cheap.”
When not to use this
- You need the top N by some measure —
ORDER BY … LIMIT N(orQUALIFY ROW_NUMBER() … <= N). See QUALIFY. - You need a reproducible, unbiased sample of a small table —
SYSTEMis the wrong method; evenBERNOULLIis a probability, not a promise. - You hoped
SAMPLEwould make a join cheaper. Official docs say it will not, if you sample after the join. - You are computing a metric you will publish. Sample first only if you are prepared to describe the method and its bias.
Snowflake SAMPLE methods, defaults, seed restrictions, join behavior, LIMIT-vs-SAMPLE wording, and small-table bias notes are from official Snowflake documentation and were not executed on Snowflake. DuckDB 2.0.0 was used only to show that a 10% Bernoulli sample and a 10-row sample are different operations; that is not a Snowflake result.