Categories:: Data metric functions

RMSE (system data metric function)¶

Returns the root mean squared error (RMSE) between a column of predicted values and a column of actual values. RMSE is in the same units as the values, and it weighs large errors more heavily than small ones.

This topic provides the syntax for calling the function directly. To learn how to associate the function with a table or view so it runs at regular intervals, see Associate a DMF.

Syntax¶

SNOWFLAKE.CORE.RMSE(<query>)

Arguments¶

query: Specifies a SQL query that projects two columns. The first column contains the predicted values, and the second column contains the actual values.

Allowed data types¶

The two columns projected by the query must have the same data type, which must be one of the following:

FLOAT
NUMBER

Returns¶

The function returns a FLOAT value that is greater than or equal to 0. A value of 0 means that every prediction matches the actual value. The function returns NULL if no row has both a predicted and an actual value.

Usage notes¶

The function computes SQRT(AVG(POWER(predicted - actual, 2))).
The order of the columns matters. The first column is the predicted value and the second is the actual value. When you associate the function with a table or view, specify the columns in the same order: ON (predicted_column, actual_column).
Rows in which either value is NULL are ignored.
You can’t mix data types. For example, you can’t use the function on a NUMBER column of predictions and a FLOAT column of actual values.
To measure the typical size of an error without weighting large errors more heavily, use MAE.

Examples¶

The examples use the following table of demand forecasts. The prediction in the last row is missing, so the function ignores that row:

CREATE TABLE sales.forecasts.store_demand (
  forecast_units FLOAT,
  actual_units FLOAT);

INSERT INTO sales.forecasts.store_demand VALUES
  (110, 100),
  (95, 100),
  (130, 120),
  (80, 90),
  (NULL, 50);

Measure the root mean squared error of the forecasts:

SELECT SNOWFLAKE.CORE.RMSE(
  SELECT
    forecast_units,
    actual_units
  FROM sales.forecasts.store_demand
) AS rmse;

+-------------+
| RMSE        |
+-------------+
| 9.013878189 |
+-------------+

Associate the function with the table, and define an expectation that is violated when the RMSE reaches 10 units:

ALTER TABLE sales.forecasts.store_demand
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.RMSE
    ON (forecast_units, actual_units)
    EXPECTATION rmse_under_10 (VALUE < 10);