Snowflake SQL Patterns

SAMPLE vs LIMIT

LIMIT is the cheapest handful of rows. SAMPLE is a method-based subset. They answer different questions.

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:

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

Verification

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.

Official docs