What you will be able to do
- Read the automatic contextual statistics Snowsight shows beside worksheet results, and know which interactions cost compute
- Open a table's Data Profile and say which statistics it reports
- Choose between STDDEV, STDDEV_POP and SKEW, and interpret what they return
- Pick the system data metric function that matches a stated outlier rule (IQR fence or z-score threshold)
Key concept
Statistical summary — A short set of numbers that describes a column: how many rows are filled or empty, its minimum and maximum, its common values, its spread and its shape. You compute it before you draw any chart, so that every outlier or skew you present to the business is backed by a number.
1.Statistics that appear beside every worksheet result
A business case needs numbers you can defend. In Snowsight, many of those numbers appear before you write a single aggregate. When you run a query in a worksheet, the results come back as a table. If you select columns, cells, rows or ranges in that table, an inspector pane opens to its right with statistics about your selection. Snowsight generates these statistics for every column type, on result sets of up to 1 million rows. The column overview shows a preview for each column, and selecting a column header opens the detailed statistics.
The statistics you get depend on the column's type. Every column gets a filled/empty meter, so missing values are visible straight away. Some types, such as email and JSON, also report how many rows are invalid. Date, time and numeric columns get a histogram. You can click a bar or drag across the histogram to select a range, which is a quick way to isolate the tail of a distribution and look at those rows. Categorical columns get a frequency distribution. Snowsight defines a categorical column as a text column where the same values are used more than once. Email columns show the distribution of domains. JSON columns show a key distribution.
Checkpoint 1 of 6· Match them up
Match each statistic in the inspector pane to the columns it is shown for
Tap a term, then the definition that fits it.
Snowsight picks the statistic to fit the column type. Histograms are for continuous types, frequency distributions are for categorical text, key distributions are for JSON, and every column gets a filled/empty meter.
“Displayed for all date, time, and numeric columns.”Source: docs.snowflake.com
Sources1
2.Profiling a whole table from Catalog Explorer
Contextual statistics describe one query's result. If you need a summary of a whole table or view without writing any query, use its Data Profile. To open it, sign in to Snowsight, go to Catalog » Explorer, select the table or view, open the Data Quality tab, and select Data Profile. The profile reports five things: the number of rows, when the table was last updated, how many NULL values each column has, each column's minimum and maximum values, and each column's most common values. These are the facts a stakeholder will ask about first: how much data there is, how recent it is, how complete it is, and what range it covers.
The profile has a cost. Data profiling runs background SQL queries, and by default it uses the current user's default warehouse. Snowflake recommends an X-Small warehouse for these queries. A larger warehouse can help with heavier workloads, but it generally consumes more credits. You can pick a different warehouse from the drop-down list at the top of the page.
Checkpoint 2 of 6· Put it in order
Put the steps for viewing a table's Data Profile in order
- 1.In the navigation menu, select Catalog » Explorer and select the table or view
- 2.Select the Data Quality tab
- 3.Select Data Profile
- 4.Sign in to Snowsight
The Data Profile is under the Data Quality tab of the object you select in Catalog Explorer. It is not in the worksheet results pane.
“In the navigation menu, select Catalog » Explorer, and then select the table or view.”Source: docs.snowflake.com
Sources2
3.Spread and shape with SQL aggregates
When the built-in panes aren't enough, you write the summary yourself in a worksheet. Two aggregate functions matter most here: one for spread and one for shape. For spread, STDDEV returns the sample standard deviation of the non-NULL values. STDDEV_SAMP is simply another name for the same function. If you need the population standard deviation, call STDDEV_POP instead. All of these return DOUBLE. With a single-record input, STDDEV and STDDEV_SAMP both return NULL. Snowflake points out that this is different from Oracle, where STDDEV returns 0 in that case.
{ STDDEV | STDDEV_SAMP } ( [ DISTINCT ] <expr1> )For shape, SKEW returns the sample skewness of the non-NULL records, which measures how asymmetric the distribution is. The documentation's example builds a small table, aggr. Column K holds 1, 2, 2, 2, 2. Column V holds 10, 10, 20, 25, 30. Column V2 has only two non-NULL values. The results are SKEW(K) = -2.236, SKEW(V) ≈ 0.05 and SKEW(V2) = NULL. To read them, look at the data. K has four values bunched at 2 and one lone 1 below them, which gives a strongly negative result. V is spread fairly evenly, so its result is close to zero. V2 doesn't have enough records to compute a skewness.
select SKEW(K), SKEW(V), SKEW(V2) from aggr;Checkpoint 3 of 6· Exam question
A data scientist runs `SELECT * FROM churn_features` in a Snowsight worksheet and wants a fast profile of each column (null share, distinct values, value distribution) before writing any profiling SQL. What should they do?
Correct answer: C — Select a column in the results grid and read its statistics panel, which shows empty/null counts, distinct values and a histogram.
- A. INFORMATION_SCHEMA.COLUMNS describes names, types and nullability, and has no maintained distinct or null count fields.
- B. EXPLAIN returns a logical plan with operators and partition estimates, not value distributions for individual columns.
- C. Snowsight's results pane computes per-column statistics for the returned rows, so a first profile needs no extra SQL.
- D. Charts are built from columns you choose and configure; Snowsight does not auto-plot all columns or flag outliers.
Checkpoint 4 of 6· Check yourself
An analyst's report must state the population standard deviation of order value. Which function should the query use?
STDDEV and STDDEV_SAMP are the same function, and both return the sample standard deviation. The population version is STDDEV_POP.
“See also STDDEV_POP, which returns the population standard deviation (square root of variance).”Source: docs.snowflake.com
4.Naming the outlier rule with system DMFs
Saying "that point looks odd" won't hold up in a business case. Saying "4% of values fall outside 1.5 times the interquartile range" will. Snowflake's system data metric functions (DMFs) include a Statistics category for this. It has summary measures: AVG, MIN, MAX, MEDIAN (exact), STDDEV, VARIANCE, and approximate 25th, 50th and 99th percentiles (APPROX_QUANTILE_25, _50, _99). Next to them is a family of outlier counters, and each one states its rule in its name. The IQR variants use Tukey fences built from the interquartile range. The ZSCORE variants use a z-score threshold. Each counter also has a _PERCENT twin.
| DMF | Flags a value when it… | Percentage twin |
|---|---|---|
| OUTLIER_IQR_COUNT | falls outside the standard Tukey fences (1.5 × IQR) | OUTLIER_IQR_PERCENT |
| EXTREME_OUTLIER_IQR_COUNT | falls outside the extreme Tukey fences (3 × IQR) | EXTREME_OUTLIER_IQR_PERCENT |
| OUTLIER_ZSCORE_COUNT | has a Z-score greater than 3 | OUTLIER_ZSCORE_PERCENT |
| EXTREME_OUTLIER_ZSCORE_COUNT | has a Z-score greater than 4.5 | EXTREME_OUTLIER_ZSCORE_PERCENT |
| OUTLIER_COUNT | falls outside the asymmetric Tukey fences for the column | OUTLIER_PERCENT |
| EXTREME_OUTLIER_COUNT | falls outside the extreme asymmetric Tukey fences | EXTREME_OUTLIER_PERCENT |
Some extreme values aren't statistical outliers at all. They are quality problems. The Accuracy category catches these separately. NEGATIVE_COUNT finds negatives in a numeric column, ZERO_COUNT finds zeros, and NULL_COUNT finds NULLs. A negative quantity or a run of zeros will distort AVG and STDDEV, so check for them before you call anything an outlier. Remember that a count only tells you how many values crossed a fence, not why. To decide whether flagged points are errors or real events, you need to see where they sit, which is the job of a chart.
Checkpoint 5 of 6· Exam question
A table `orders` holds 4 billion rows. An analyst needs the 95th percentile of `order_amount` for a dashboard tile and can accept a small estimation error, while keeping runtime and warehouse cost low. Which approach is MOST appropriate?
Correct answer: D — Call `APPROX_PERCENTILE(order_amount, 0.95)`, which uses a t-digest sketch so the aggregate scales across the table without sorting every value.
- A. PERCENTILE_CONT is exact and must order the values, so it is the more expensive option rather than a sketch-based estimate.
- B. Snowflake has several percentile functions, and moving billions of values into client memory is both slow and unnecessary.
- C. The ratio between the 95th percentile and the median depends on the distribution and is not a constant, so this gives wrong figures.
- D. APPROX_PERCENTILE trades a small, bounded error for far lower memory and runtime, which suits dashboard tiles on very large tables.
Checkpoint 6 of 6· Check yourself
A data steward wants to monitor how many order amounts lie beyond three times the interquartile range. Which system DMF fits that rule?
The extreme IQR variant uses fences at 3 × IQR. OUTLIER_IQR_COUNT uses 1.5 × IQR, and the ZSCORE variants use thresholds of 3 and 4.5 rather than the IQR.
“fall outside the extreme Tukey fences (3 times the interquartile range)”Source: docs.snowflake.com
Sources5
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.STDDEV returns the population standard deviation, and you need STDDEV_SAMP for the sample version.Why is that wrong?
STDDEV and STDDEV_SAMP are aliases, and both return the sample standard deviation. The population figure comes from STDDEV_POP.
Covered in Spread and shape with SQL aggregates
2.Anything you do in the worksheet results grid, sorting included, is a free display change.Why is that wrong?
Sorting applies to all results, not only the rows on screen, and it incurs compute billed to the query's warehouse. Only formatting changes such as separators, percentages, precision and date formats are free.
Covered in Statistics that appear beside every worksheet result
3.EXTREME_OUTLIER_ZSCORE_COUNT uses the same z-score threshold of 3 as OUTLIER_ZSCORE_COUNT.Why is that wrong?
The standard z-score variant flags values with a Z-score above 3. The extreme variant flags only values above 4.5.
Covered in Naming the outlier rule with system DMFs
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Contextual statistics are automatically generated for all column types.”
↩︎ Statistics that appear beside every worksheet result“Categorical columns are text columns where the same values are used more than once.”
↩︎ Statistics that appear beside every worksheet result“view only SQL statements that contain the SQL Text: snowsight_transform_cte”
↩︎ Statistics that appear beside every worksheet result“when you sort a column by ascending or descending order using the column options, the changes affect all of your results”
↩︎ Exam trap 2“Displayed for all date, time, and numeric columns.”
↩︎ Checkpoint - 2.
“Data profiling runs background SQL queries to display information about a table or view.”
↩︎ Profiling a whole table from Catalog Explorer“Snowflake recommends using an X-Small warehouse to run these queries”
↩︎ Profiling a whole table from Catalog Explorer“automatically gathering statistics such as data types, value distributions, counts of NULL values, and uniqueness”
↩︎ Key concept“In the navigation menu, select Catalog » Explorer, and then select the table or view.”
↩︎ Checkpoint - 3.
“Returns the sample standard deviation (square root of sample variance) of non-NULL values.”
↩︎ Spread and shape with SQL aggregates“For single-record inputs, STDDEV and STDDEV_SAMP both return NULL.”
↩︎ Spread and shape with SQL aggregates“STDDEV and STDDEV_SAMP are aliases for the same function.”
↩︎ Exam trap 1“See also STDDEV_POP, which returns the population standard deviation (square root of variance).”
↩︎ Checkpoint - 4.
“Intuitively, skew describes how asymmetric the underlying distribution is.”
↩︎ Spread and shape with SQL aggregates“For inputs with fewer than three records, SKEW returns NULL.”
↩︎ Prediction - 5.
“Determine how many values in a numeric column fall outside the standard Tukey fences (1.5 times the interquartile range).”
↩︎ Naming the outlier rule with system DMFs“Determine how many values in a numeric column have a Z-score greater than 3.”
↩︎ Naming the outlier rule with system DMFs“have a Z-score greater than 4.5.”
↩︎ Exam trap 3“fall outside the extreme Tukey fences (3 times the interquartile range)”
↩︎ Checkpoint