What you will be able to do
- Open the Snowsight data profile for a table and say which statistics it gathers
- Start a Jupyter notebook that works with Snowpark Python
- Choose the correct sample or population STDDEV/VARIANCE function and predict how NULLs affect aggregates
- Write window functions that return a statistic on every row, including moving averages with explicit frames
- Use TOP n so that it returns the same rows every time
Key concept
Aggregate vs. window evaluation — An aggregate statistic such as AVG or STDDEV turns a group of rows into one row. Add an OVER clause and the same function becomes a window function: it computes the statistic over a partition and attaches the result to every input row.
1.First look: the Snowsight data profile
Exploratory data analysis begins with profiling: a quick look at what a table contains before you decide how to clean or model it. Snowflake has this built into Snowsight. The data profile describes a table or view by automatically gathering statistics such as data types, value distributions, counts of NULL values, and uniqueness. It covers the row count, when the table was last updated, the number of NULLs per column, the minimum and maximum values per column, and the most common values in each column.
The profile is not a stored artefact. It runs background SQL queries against the table, so it needs a warehouse. Snowflake recommends an X-Small warehouse for these queries, though heavier workloads may run faster on a larger one, which costs more credits. If you pick nothing, the profile uses your user's default warehouse. You can choose a different one from the drop-down list at the top of the page.
Each statistic in the profile also exists as a SQL function you can run yourself, at any granularity: COUNT, MIN and MAX, and frequency estimation for the most common values. The rest of this page covers those functions.
Checkpoint 1 of 6· Put it in order
Put the steps for viewing a table's data profile in order.
- 1.Select Data Profile
- 2.Sign in to Snowsight
- 3.In the navigation menu, select Catalog » Explorer and pick the table or view
- 4.Select the Data Quality tab
You find the profile through the catalog explorer: first the object, then its Data Quality tab, then Data Profile.
“In the navigation menu, select Catalog » Explorer, and then select the table or view.”Source: docs.snowflake.com
Sources1
2.Exploring from a Jupyter notebook with Snowpark
Many data scientists prefer to explore data from a notebook. Snowpark Python works in Snowflake Notebooks and also in an external Jupyter environment on your own machine. The Snowpark setup guide gives a short procedure. First install Jupyter, then start it, then open a new Python 3 notebook from the top-right corner of the page that opens. The last step happens inside the notebook: in a cell, you create a Snowpark session, and that session is your connection to Snowflake. The main Snowpark API classes are in the snowflake.snowpark module. The same guide says Snowpark also works with an IDE such as Visual Studio Code.
pip install notebookCheckpoint 2 of 6· Check yourself
You have installed and started Jupyter and opened a new Python 3 notebook. What does the setup guide say to do next before you can query Snowflake from it?
The final step of the Jupyter procedure is creating a Snowpark session in a notebook cell. The VS Code extension belongs to the IDE workflow, not to Jupyter.
“In a cell, create a session.”Source: docs.snowflake.com
Sources2
3.MIN, MAX, AVG, STDDEV and VARIANCE
Descriptive statistics in SQL come from aggregate functions. These run mathematical calculations across rows, such as sum, average, counting, minimum/maximum values, standard deviation, and estimation. An aggregate takes many rows and returns exactly one row, even when the input has zero rows. Besides MIN, MAX and AVG, the general aggregation list includes MEDIAN, MODE, PERCENTILE_CONT and PERCENTILE_DISC. The statistics category adds KURTOSIS and SKEW.
The spread functions are where most mistakes happen, because several names are aliases. The exam guide writes 'STDEV', but Snowflake spells it STDDEV.
| Function | Documented alias | Sample or population |
|---|---|---|
| STDDEV | STDDEV_SAMP | Sample |
| STDDEV_POP | (none listed) | Population |
| VARIANCE | VARIANCE_SAMP, VAR_SAMP | Sample |
| VARIANCE_POP | VAR_POP | Population |
NULLs also need care. Some aggregates simply skip NULL values: AVG of 1, 5 and NULL is (1 + 5) / 2 = 3, because the NULL is left out of both the numerator and the denominator. If every input value is NULL, the result is NULL. When you pass more than one column, the whole row is skipped if any of those columns is NULL. In the documented example, four rows contain NULLs in different places and the count comes back as 1.
SELECT COUNT(x, y) FROM test_null_aggregate_functions;Checkpoint 3 of 6· Match them up
Match each function name to the function it is an alias of.
Tap a term, then the definition that fits it.
STDDEV and VARIANCE without a suffix are the sample versions. To get the population versions you have to ask for them with _POP.
“STDDEV and STDDEV_SAMP are aliases.”Source: docs.snowflake.com
Sources3
4.Window functions: statistics on every row
The OVER clause is what turns AVG, SUM, MIN, MAX, STDDEV or VARIANCE into a window function. It has three optional parts: PARTITION BY, ORDER BY, and a window frame. During exploration, PARTITION BY lets you place each row next to its group statistic, so you can see how far a value sits from its category's mean.
SELECT menu_category,
AVG(menu_cogs_usd) OVER(PARTITION BY menu_category) avg_cogs
FROM menu_items
ORDER BY menu_category
LIMIT 15;Add an ORDER BY inside OVER plus a frame and you get rolling statistics such as moving averages. The ORDER BY inside OVER only sets the order in which the window function processes rows. It is separate from the query's final ORDER BY. Ranking functions such as RANK and NTILE require it. For some functions, an ORDER BY also implies a window frame you did not write, so Snowflake recommends declaring frames explicitly. Make the ordering deterministic as well: when rows tie on the ORDER BY columns, add tiebreaker columns. In the example below, menu_cogs_usd plays that role.
SELECT menu_category, menu_price_usd, menu_cogs_usd,
AVG(menu_cogs_usd) OVER(PARTITION BY menu_category
ORDER BY menu_price_usd, menu_cogs_usd ROWS BETWEEN CURRENT ROW and 2 FOLLOWING) avg_cogs
FROM menu_items
ORDER BY menu_category, menu_price_usd, menu_cogs_usd
LIMIT 15;Frames come in two modes. ROWS counts a physical number of rows. RANGE defines a logical set of rows based on the ORDER BY value. Only some functions accept RANGE BETWEEN with explicit offsets: COUNT, SUM, MIN, MAX, AVG, the STDDEV and VARIANCE families, COUNT_IF, FIRST_VALUE, LAST_VALUE and ARRAY_AGG. Their DISTINCT versions do not.
Checkpoint 4 of 6· Check yourself
Which statement about an ORDER BY clause inside OVER(...) is correct?
The two ORDER BY clauses are independent. AVG works with PARTITION BY alone, and an ordinal like 2 inside OVER is read as the constant 2, not as column 2.
“An ORDER BY clause within an OVER clause controls only the order in which the window function processes rows”Source: docs.snowflake.com
5.TOP n: the first n rows, in a reliable order
A common profiling question is 'what are the top n?'. TOP n caps how many rows a statement or subquery returns. n must be a non-negative integer constant, and TOP n is equivalent to LIMIT. ORDER BY is not required, but without it the result is non-deterministic, because the rows in a result set have no guaranteed order. So a TOP n without ORDER BY can return different rows on each run. Put TOP n and ORDER BY at the same query level too: when they are at different nesting levels, results can be unpredictable.
Ordering also helps speed. On queries that have both ORDER BY and LIMIT, Snowflake can apply top-K pruning: it stops scanning once it knows none of the remaining rows can make it into the K-row result. Large tables gain the most.
Checkpoint 5 of 6· Fill the gap
Which keyword completes this query so that it returns at most four rows?
SELECT ? 4 c1 FROM testtable;TOP n goes directly after SELECT. LIMIT and FETCH limit rows too, but they go after FROM and ORDER BY, not in the select list.
Source: docs.snowflake.comCheckpoint 6 of 6· Exam question
A data scientist must profile a 400M-row `CUSTOMERS` table with 30 columns before building a churn model, scanning the table as few times as possible. Select TWO approaches that fit this goal.(Select 2)
Correct answers: B, D — Run one SELECT that returns COUNT(*) alongside COUNT(col) or COUNT_IF(col IS NULL) for every column, then derive per-column null ratios from that single scan.; Add APPROX_COUNT_DISTINCT(col), MIN(col) and MAX(col) for each column to the same aggregate query to get cardinality and value ranges without extra scans.
- A. The first rows are not a random sample, so null ratios from them can be badly biased and say nothing reliable about the full table.
- B. A single aggregate query computes row count and non-null counts for all columns together, so completeness for every column comes from one pass over the table.
- C. Pulling 400M rows to the client defeats pushdown, risks running out of memory, and is slower than letting the warehouse aggregate.
- D. These aggregates can share the same scan, and APPROX_COUNT_DISTINCT gives a cheap cardinality estimate, so cardinality and ranges arrive with the null counts.
- E. DISTINCT collapses duplicates and counts a NULL as one value, so the result cannot give null ratios and costs thirty separate scans.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.VARIANCE and STDDEV return population statistics.Why is that wrong?
Without a suffix, both are the sample versions: VARIANCE is an alias for VAR_SAMP and STDDEV for STDDEV_SAMP. For population statistics, use VARIANCE_POP / VAR_POP or STDDEV_POP.
Covered in MIN, MAX, AVG, STDDEV and VARIANCE
2.COUNT(x, y) counts every row where at least one of x or y has a value.Why is that wrong?
When an aggregate receives several columns, it skips any row in which any one of those columns is NULL.
Covered in MIN, MAX, AVG, STDDEV and VARIANCE
3.TOP n on an aggregated query always returns the n largest groups.Why is that wrong?
TOP n only limits the row count. Without an ORDER BY at the same query level, which rows you get is non-deterministic.
Covered in TOP n: the first n rows, in a reliable order
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“by automatically gathering statistics such as data types, value distributions, counts of NULL values, and uniqueness.”
↩︎ First look: the Snowsight data profile“Snowflake recommends using an X-Small warehouse to run these queries”
↩︎ First look: the Snowsight data profile“In the navigation menu, select Catalog » Explorer, and then select the table or view.”
↩︎ Checkpoint - 2.
“To get started using Snowpark with Jupyter Notebooks, do the following:”
↩︎ Exploring from a Jupyter notebook with Snowpark“The main classes for the Snowpark API are in the snowflake.snowpark module.”
↩︎ Exploring from a Jupyter notebook with Snowpark“In a cell, create a session.”
↩︎ Checkpoint - 3.
“Aggregate functions operate on values across rows to perform mathematical calculations such as sum, average, counting, minimum/maximum values, standard deviation, and estimation”
↩︎ MIN, MAX, AVG, STDDEV and VARIANCE“Some aggregate functions ignore NULL values. For example, AVG calculates the average of values 1, 5, and NULL to be 3”
↩︎ MIN, MAX, AVG, STDDEV and VARIANCE“Alias for VAR_SAMP.”
↩︎ Exam trap 1“In these instances, the aggregate function ignores a row if any individual column is NULL.”
↩︎ Exam trap 2“STDDEV and STDDEV_SAMP are aliases.”
↩︎ Checkpoint - 4.
“Because behavior that is implied rather than explicit can lead to results that are difficult to understand, Snowflake recommends declaring window frames explicitly.”
↩︎ Window functions: statistics on every row“For a window function, the input is each row within a partition, and the output is one row per input row.”
↩︎ Key concept“Note that the function returns an average for each row in each partition and resets the calculation when the partitioning column value changes.”
↩︎ Prediction“An ORDER BY clause within an OVER clause controls only the order in which the window function processes rows”
↩︎ Checkpoint - 5.
“The following subset of window functions support the RANGE BETWEEN syntax with explicit offsets:”
↩︎ Window functions: statistics on every row - 6.
“TOP n and LIMIT count are equivalent.”
↩︎ TOP n: the first n rows, in a reliable order“without an ORDER BY clause, the results are non-deterministic because results within a result set are not necessarily in any particular order.”
↩︎ Exam trap 3 - 7.
“Snowflake stops scanning when it determines that none of the remaining rows can be in a result set that consists of K records.”
↩︎ TOP n: the first n rows, in a reliable order