Sampling, Estimation & Context Functions
Domain 4 ("Performance Optimization, Querying and Transformation") includes a small family of functions that are quick to learn and easy to mix up. Ten minutes here is enough.
SAMPLE / TABLESAMPLE
SAMPLE and TABLESAMPLE are synonyms. There are two methods:
- BERNOULLI (or ROW): every row gets an independent probability of inclusion. Statistically clean, slower, because every row is considered. This is the default.
- SYSTEM (or BLOCK): whole micro-partitions are included or excluded. Much faster on big tables, less random, and it can be biased on small ones. A SEED isn't supported on views or subqueries.
-- ~10% of rows, row-level randomness (default method)SELECT * FROM orders SAMPLE (10);-- ~10% by micro-partition blocks, fast on huge tables, repeatable with SEEDSELECT * FROM orders SAMPLE SYSTEM (10) SEED (42);-- Exactly 500 rows (row count instead of percentage; BERNOULLI only)SELECT * FROM orders SAMPLE (500 ROWS);
Adding a SEED makes a sample repeatable, but only for SYSTEM/BLOCK sampling on an unchanged table, and never on a view or subquery. And a fixed row count (SAMPLE (500 ROWS)) works only with BERNOULLI/ROW. If an option pairs SYSTEM with a row count, it's wrong.
SAMPLE BERNOULLI (25) · DEFAULT
each row: its own coin flip
slower: every row is considered
SAMPLE (500 ROWS) works only here
SAMPLE SYSTEM (25) SEED (42)
each micro-partition: in or out
faster on big tables, less random
SEED repeats it on an unchanged table
Estimation functions
Exact answers on billions of rows are expensive. Snowflake ships approximate versions that trade a small error (about 1.6% on average for distinct counts) for large speed and memory savings:
| Need | Exact | Approximate |
|---|---|---|
| Distinct count | COUNT | APPROX_ / HLL (HyperLogLog) |
| Percentiles / median | PERCENTILE_ | APPROX_ (t-Digest) |
| Most frequent values | GROUP BY + ORDER BY | APPROX_ (Space-Saving) |
| Set similarity between tables | full join comparison | MINHASH / APPROXIMATE_ |
The pattern to recognize: "fastest way to get an approximate number of unique visitors" → APPROX_COUNT_DISTINCT, and "HLL" is the same thing wearing its algorithm's name.
Context functions and RESULT_SCAN
Context functions return session facts: CURRENT_ROLE(), CURRENT_USER(), CURRENT_WAREHOUSE(), CURRENT_ACCOUNT(), CURRENT_REGION(). You've already met them inside masking and row access policies in Part 3, which is where they matter most.
RESULT_SCAN(query_id) re-reads the result set of a previous query as if it were a table, with LAST_QUERY_ID() as its usual companion. The classic use is post-processing the output of a SHOW command, since SHOW output can't be filtered with WHERE directly:
SHOW TABLES IN SCHEMA analytics.core; SELECT "name", "rows"FROM TABLE(RESULT_ SCAN( LAST_ QUERY_ ID( ))) WHERE "rows" > 1000000;
LAST_QUERY_ID takes an optional argument whose sign changes the counting direction, a detail that's easy to get backward. A positive n counts from the start of the session: LAST_QUERY_ID(1) is the first query you ran, LAST_QUERY_ID(2) the second. A negative n counts backward from now: LAST_QUERY_ID(-1) is the most recent (the default, same as no argument), LAST_QUERY_ID(-2) the one before it. So "the second query executed in the current session" is LAST_QUERY_ID(2), while "the second most recent" is LAST_QUERY_ID(-2); don't confuse the two.
Two more worth knowing: GET_DDL('TABLE', 'db.schema.t') returns the CREATE statement for an existing object, and GENERATOR(ROWCOUNT => n) fabricates rows for test data.
- BERNOULLI/ROW sampling is per-row and the default; SYSTEM/BLOCK is per-micro-partition and faster. SEED makes SYSTEM repeatable (not on views); row counts need BERNOULLI.
- APPROX_COUNT_DISTINCT is HLL. Approximate functions trade a small error (about 1.6% on average for HLL) for speed and memory on huge data.
- RESULT_SCAN + LAST_QUERY_ID lets you query the output of the previous statement, including SHOW commands.
- Context functions like CURRENT_ROLE() are the glue inside masking and row access policies.

