Statistical Concepts: Distributions, CLT, Z/T Tests, Bootstrap, CIs

1.Normal versus skewed distributions: mean, median and outliers

Every later idea in this lesson assumes you know the shape of your data. A normal distribution is symmetric and bell-shaped. Its mean, median and mode all sit at the centre, and how likely a value is depends only on how far it is from that centre. Snowflake's NORMAL data-generation function lets you see this. You give it a mean and a standard deviation, and many calls trace out the bell curve. The documentation states the empirical rule directly: with mean 0 and standard deviation 1, about 68.2% of values fall between -1 and +1. The rest of the rule, which comes from general statistics rather than this page, is that about 95% of values fall within 2 standard deviations and about 99.7% within 3.

SELECT normal(0, 1, random()) FROM table(generator(rowCount => 5));

Real business columns are rarely that tidy. Income, basket size, session length and claim amounts are usually right-skewed: most values are modest, and a long tail of large values pulls the mean up. The quickest test for skew is to compare the mean and the median. If the mean is well above the median, the data is right-skewed. If it is well below, the data is left-skewed. Snowflake's SKEW aggregate gives you a number for it. It is about 0 for symmetric data, positive for a right tail and negative for a left tail, and it needs at least three non-NULL records. As a rough guide, an absolute value above about 1 means strong skew.

Skew matters because the mean and the standard deviation are both pulled by the tail. A few extreme values can move the mean a long way from a typical row, while the median barely moves. Snowflake's MEDIAN data metric function says this in its own description. The usual responses to a heavily skewed feature are to report the median or a percentile instead of the mean, to apply a log or similar transform before modelling, and to look at the outliers to decide whether they are errors or real, informative extremes. Simply deleting them is not the default.

Sources123

2.The central limit theorem: why sample means behave

If so much real data is skewed, why do confidence intervals and z and t tests rely on the normal curve? The answer is the central limit theorem (CLT). Take repeated random samples of size n from almost any population with a finite variance and compute each sample's mean. As n grows, the distribution of those means gets closer and closer to a normal distribution. Its centre is the population mean and its spread is sigma divided by the square root of n. That spread is the standard error.

Note what the CLT is about: the distribution of a statistic, not the data. The raw column stays exactly as skewed as it was. Only the sampling distribution of the mean becomes approximately normal. A common rule of thumb is that n of about 30 is enough for moderately skewed data. Heavy tails need larger samples. The square root of n also tells you what more data buys: to halve the standard error you need four times as many rows.

You can watch the CLT happen in Snowflake. UNIFORM produces values that are flat across a range, which is clearly not a bell. Generate many rows, put them into groups of, say, 30, take AVG per group and plot those averages. The raw draws stay flat, but the group averages pile up in a bell around the midpoint.

SELECT UNIFORM(1, 10, RANDOM()) FROM TABLE(GENERATOR(ROWCOUNT => 5));

Sources4

3.Z and T tests: standardizing against the right yardstick

Because sample means are approximately normal, you can ask how surprising an observed mean is. Both tests standardize the same way: (observed statistic minus hypothesized value) divided by its standard error. The difference is where that standard error comes from.

A z-test assumes the population standard deviation is known, or the sample is large enough that the sample estimate is effectively exact. The statistic is compared with the standard normal, so the two-sided 5% cut-off is plus or minus 1.96. A t-test is the usual choice when the standard deviation is estimated from the same sample, which is what Snowflake's STDDEV returns. Estimating sigma adds uncertainty, so the t-distribution has heavier tails than the normal and depends on degrees of freedom (n minus 1 for a one-sample test). With 10 rows the two-sided 5% cut-off is about 2.26, not 1.96. As n grows, t approaches z. Common variants are the one-sample test (mean against a target), the two-sample test (A vs. B; Welch's version does not assume equal variances) and the paired test (before vs. after on the same units). In each case a small p-value means the observed difference would be unlikely if the null hypothesis were true. It does not measure how large or how important the effect is.

Don't confuse these tests with a z-score on a single row. Snowflake's OUTLIER_ZSCORE_COUNT and EXTREME_OUTLIER_ZSCORE_COUNT data metric functions standardize each value by the column mean and standard deviation and count values more than 3 or 4.5 standard deviations out. That is a data-quality screen on individual rows. It tests no hypothesis about a mean, and on a skewed column it will flag the legitimate long tail.

Sources567

4.Bootstrapping: a sampling distribution without a formula

Z and t tests work because there is a formula for the standard error of a mean. For a median, a ratio, a 90th percentile or a model metric, that formula is either messy or doesn't exist. Bootstrapping replaces the formula with computation. Treat your sample of n rows as a stand-in for the population. Draw n rows from it with replacement, so some rows appear twice and others not at all. Compute the statistic, and repeat this B times (often 1,000 to 10,000). The spread of the B results estimates the standard error. The 2.5th and 97.5th percentiles of those results give a 95% percentile confidence interval.

Bootstrapping needs no normality assumption, which suits skewed data. It cannot fix a sample that is unrepresentative or very small, though, because every resample only reuses the rows you already have. With replacement is essential: without it, each resample of size n would just be the original sample in a different order.

That coin flip is why Snowflake's SAMPLE / TABLESAMPLE clause is not a bootstrap by itself. BERNOULLI (ROW) sampling includes each row with probability p/100. A row is either in or out, so it can never appear twice, and the result is a random subset smaller than the table. That is useful for exploring a large table or building a train/validation split, but it is sampling without replacement. A real bootstrap needs draws with replacement: either random row positions generated with UNIFORM and RANDOM and joined back to the data, or, more commonly, a resampling loop in Python over data pulled from Snowflake. For reproducible results, fix the seed. SAMPLE accepts one only for SYSTEM/BLOCK sampling.

SELECT * FROM testtable SAMPLE  ?  (3) SEED (82);

Sources8

5.Confidence intervals: estimate plus or minus the standard error

Everything so far comes together here. A confidence interval is a point estimate plus or minus a critical value times the standard error. For a mean, that is the sample mean plus or minus z* (or t*) times s divided by the square root of n. For a 95% interval z* is 1.96, and t* is a little larger when n is small. In Snowflake, s is STDDEV and n is COUNT, so the half-width is 1.96 * STDDEV(x) / SQRT(COUNT(x)). If you leave out the division by the square root of n, you get an interval for where individual rows fall, not for where the mean is. It will be far too wide.

CREATE TABLE t1 (c1 INTEGER);
INSERT INTO t1 (c1) VALUES
  (6),
  (10),
  (14);
SELECT  ? (c1) FROM t1;

Interpret the interval correctly. "95% confident" describes the procedure: if you repeated the sampling many times, about 95% of the intervals built this way would contain the true parameter. It does not mean 95% of the data lies inside the interval, and it does not mean there is a 95% probability that this particular interval contains the parameter. Three things control the width: higher confidence makes it wider, more spread (larger s) makes it wider, and a larger n makes it narrower in proportion to the square root of n. When no simple formula exists, a bootstrap percentile interval from the previous section answers the same question.

Snowflake reports confidence intervals itself when you monitor models. MODEL_MONITOR_PERFORMANCE_METRIC can return a CI_VALUE column holding a 95% interval as lower and upper bounds. It uses a method suited to each kind of metric: the Wilson interval for proportion-style classification metrics and the Wald interval for error metrics. A wide CI_VALUE on a small monitoring window is a warning not to over-read a change in the metric.

Sources910

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A 95% confidence interval for a mean is AVG(x) plus or minus 1.96 * STDDEV(x).Why is that wrong?

   STDDEV measures how spread out individual values are. The interval for the mean uses the standard error, STDDEV(x) / SQRT(COUNT(x)). Without that division, the interval is wider by a factor of the square root of n.

   Covered in Confidence intervals: estimate plus or minus the standard error

2. 2.Running SAMPLE (n) repeatedly on a table is a bootstrap.Why is that wrong?

   Bernoulli sampling flips a coin for each row, so it returns a subset without replacement and no row ever appears twice. A bootstrap draws n rows with replacement from the sample.

   Covered in Bootstrapping: a sampling distribution without a formula

3. 3.The mean is the best single summary of a typical value, whatever the column's shape.Why is that wrong?

   In a skewed column the tail pulls the mean away from a typical row. The median, or a log transform before modelling, gives a more representative centre.

   Covered in Normal versus skewed distributions: mean, median and outliers

4. 4.A non-zero OUTLIER_ZSCORE_COUNT is a significance test showing that the data contains errors.Why is that wrong?

   The function only counts values more than three standard deviations from the mean. It is a screen on individual rows, not a z-test of a hypothesis, and on skewed data it flags real tail values.

   Covered in Z and T tests: standardizing against the right yardstick