Course contents

SnowPro Core COF-C03 Study GuidePart 6: Database objects and platformSampling, Estimation & Context Functions

Sampling, Estimation & Context Functions

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 SEED
SELECT * FROM orders SAMPLE SYSTEM (10) SEED (42);

-- Exactly 500 rows (row count instead of percentage; BERNOULLI only)
SELECT * FROM orders SAMPLE (500 ROWS);
Exam trap

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.

Two ways to take a 25% sample of a table stored in four micro-partitions of six rows each (illustrative). BERNOULLI, the default, flips a coin for every row, so the picked rows are scattered across all partitions. SYSTEM, also called BLOCK, keeps or skips whole micro-partitions: faster on big tables, less random, and repeatable with SEED.SAMPLE BERNOULLI (25) · DEFAULTSAMPLE SYSTEM (25) SEED (42)MP 1MP 2MP 3MP 4MP 1MP 2MP 3MP 4each row: its own coin flipslower: every row is consideredSAMPLE (500 ROWS) works only hereeach micro-partition: in or outfaster on big tables, less randomSEED repeats it on an unchanged table

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

Figure 6.13 BERNOULLI picks rows, SYSTEM picks micro-partitions. Green cells are the rows each method returns from the same table. Only SYSTEM accepts SEED, and only BERNOULLI accepts a fixed row count.

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:

NeedExactApproximate
Distinct countCOUNT(DISTINCT x)APPROX_COUNT_DISTINCT(x) / HLL(x) (HyperLogLog)
Percentiles / medianPERCENTILE_CONTAPPROX_PERCENTILE(x, 0.5) (t-Digest)
Most frequent valuesGROUP BY + ORDER BYAPPROX_TOP_K(x, k) (Space-Saving)
Set similarity between tablesfull join comparisonMINHASH / APPROXIMATE_SIMILARITY

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.

Key takeaways
  • 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.