Approximate Functions and Linear Regression in Snowflake SQL

1.Why approximate, and which function to use

On billions of rows, exact answers to questions like 'how many distinct users?' or 'which values are most frequent?' cost a lot of time and memory. Snowflake's aggregate catalogue includes estimation families that give a close answer for much less work. Each family is built on a named algorithm, and the name tells you what the family is for.

Each family has the same structure. The main function returns an estimate. An _ACCUMULATE function returns the intermediate state instead. A _COMBINE function merges states, and an _ESTIMATE function turns a state into a number. _ESTIMATE is not an aggregate function: it takes a single state value as input.

Sources1

2.Distinct counts with HyperLogLog

Snowflake estimates distinct counts with a bias-corrected HyperLogLog. Snowflake recommends it when the input may be large and an approximate result is acceptable. The average relative error is 1.62338%. In other words, where COUNT(DISTINCT ...) would return 1,000,000, HLL typically returns something between 983,767 and 1,016,234. Memory stays small: at most 4096 bytes per aggregation group, and as little as about 32 bytes when the input is sparse.

State is what makes HLL useful for rollups. Distinct counts cannot be added together: if a user visits on Monday and again on Tuesday, adding the two daily counts counts that user twice. HLL_ACCUMULATE instead stores the HyperLogLog state for each group. HLL_COMBINE merges stored states into one, and HLL_ESTIMATE turns a state into a count. HLL_EXPORT and HLL_IMPORT convert a state between BINARY and an OBJECT that can be exported as JSON. In the documented example, aggregating stored HLL structures took 1.3 seconds, against 149 seconds for running HLL over the base data.

Sources2

3.Most frequent values with APPROX_TOP_K

For the most frequent values, which is the SQL version of the profile's most-common-values panel, use APPROX_TOP_K. It uses the Space-Saving algorithm to return an approximation of the most frequent values together with their approximate frequencies. It works as an aggregate and also as a window function with an optional PARTITION BY.

APPROX_TOP_K( <expr> [ , <k> [ , <counters> ] ] )

k is the number of values you want counts for, up to a maximum of 100,000. If you leave k out, you get only the single most frequent value. counters is the maximum number of distinct values the algorithm tracks at any time during estimation, also capped at 100,000. The result is not a table. It is a JSON array of arrays: each inner array holds a value and its estimated frequency, and the outer array lists k items in descending order of frequency. Like HLL, this family has ACCUMULATE, COMBINE and ESTIMATE variants for working with stored states.

Sources3

4.Slope and intercept with REGR_SLOPE and REGR_INTERCEPT

After profiling and descriptive statistics, the next step is a relationship between two columns. Snowflake's linear-regression aggregates fit a least-squares line in plain SQL. REGR_SLOPE returns the slope, computed as COVAR_POP(x,y) / VAR_POP(x). REGR_INTERCEPT returns the intercept, computed as AVG(y) - REGR_SLOPE(y,x) * AVG(x). Both use only non-null pairs: a row is dropped if either x or y is NULL. The same family includes REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SXY and REGR_SYY. Each is both an aggregate and a window function.

CREATE OR REPLACE TABLE aggr(k INT, v DECIMAL(10,2), v2 DECIMAL(10, 2));
INSERT INTO aggr VALUES(1, 10, null);
INSERT INTO aggr VALUES(2, 10, 11), (2, 20, 22), (2, 25, null), (2, 30, 35);

SELECT k, REGR_SLOPE(v, v2) FROM aggr GROUP BY k;

Read the output pair by pair. Group 1's only row has a NULL v2, so it has no usable pair and returns NULL. In group 2, the (25, NULL) row is skipped, and the line is fitted through the other three pairs. The result is FLOAT, or DECFLOAT if any input is DECFLOAT.

SELECT k,  ? (v, v2) FROM aggr GROUP BY k;

Sources45

5.Getting dependent and independent variables right

To check a regression, first check the roles. The signature is REGR_SLOPE(y, x), and REGR_INTERCEPT follows the same order: the dependent variable, the thing you predict, comes first, and the independent variable comes second. The formulas show why the order matters. The slope divides by VAR_POP(x), so if you swap the arguments you divide by the variance of the other column and fit a different line.

The window form has its own limits. REGR_SLOPE(y, x) OVER (PARTITION BY ...) gives a separate slope for each partition, but you cannot put an ORDER BY inside the OVER clause or use an explicit window frame, so a rolling regression is not possible this way. DISTINCT is not supported either.

Sources4

Exam traps

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

1. 1.REGR_SLOPE takes x first and y second, as in plotting order.Why is that wrong?

   The dependent variable (y) is the first argument and the independent variable (x) the second, for both REGR_SLOPE and REGR_INTERCEPT.

   Covered in Getting dependent and independent variables right

2. 2.APPROX_TOP_K(col) returns a top-ten list by default.Why is that wrong?

   k defaults to 1, so leaving it out returns only the single most frequent value. Pass k explicitly.

   Covered in Most frequent values with APPROX_TOP_K

3. 3.HLL_ESTIMATE can be used like an aggregate over raw rows, e.g. HLL_ESTIMATE(user_id) with GROUP BY.Why is that wrong?

   HLL_ESTIMATE is a scalar function. It reads a state produced by HLL_ACCUMULATE or HLL_COMBINE. To aggregate raw values, use HLL or APPROX_COUNT_DISTINCT.

   Covered in Distinct counts with HyperLogLog

Practise it for real

Fit a regression line in Snowflake SQL and confirm how NULLs and argument order change the result.

1. 1.Run the CREATE OR REPLACE TABLE aggr and two INSERT statements from the REGR_SLOPE reference.

   Why: The data includes a group with no complete pair and a row with a NULL x value.

   You should see: Table aggr with one row for k = 1 and four rows for k = 2.

2. 2.Run SELECT k, REGR_SLOPE(v, v2) FROM aggr GROUP BY k;

   Why: Shows the slope with v as the dependent variable and v2 as the independent variable.

   You should see: NULL for k = 1 and 0.831408776 for k = 2.

3. 3.Run SELECT k, REGR_INTERCEPT(v, v2) FROM aggr GROUP BY k;

   Why: Gives the second parameter of the same line.

   You should see: NULL for k = 1 and 1.154734411 for k = 2.

4. 4.Swap the arguments: SELECT k, REGR_SLOPE(v2, v) FROM aggr GROUP BY k;

   Why: Checks that the dependent/independent roles matter.

   You should see: A slope for k = 2 that differs from 0.831408776, because the formula now divides by the variance of v instead of v2.