KPI anomaly detection with Snowflake Cortex
Overview
Anomaly detection, sometimes referred to as outlier detection, is the process of observing instances that deviate significantly from the overall pattern of the data.
Identifying anomalies promptly aids in addressing production issues related to system health, security, networking uptime, and data quality. This proactive approach helps prevent serious consequences and ensures smoother operations.
In this article, I will discuss how GumGum uses Snowflake Cortex and Looker to help us detect negative anomalies in revenue and other business-critical KPIs.
Details
We aim to forecast our revenue to identify and address any potential negative anomalies promptly. By receiving alerts when revenue falls below the lower bound of our predictions, we can take swift action to mitigate any issues before they become critical.
Generating a Forecast
The first step is to generate a forecast on the dataset you are trying to detect the anomalies. We used the SNOWFLAKE.ML.FORECAST function, a forecast model that produces a prediction for a measure across a time series.
Let's start with creating the query that will give us the historical sample data of the last 60 days. We will feed this query into the SNOWFLAKE.ML.FORECAST function so it can generate the time-series prediction.
Before getting started we need to make sure our data has the following features:
- A timestamp column, which should have a fixed frequency (for example, hourly, every 5 minutes, and so on)
- A target column representing some quantity of interest at each timestamp
SELECT
DATE_HOUR,
SUM(GROSS_REVENUE) GROSS_REVENUE
FROM GUMGUM_AD_EVENTS_MOCK_TABLE
WHERE DATE_HOUR >= DATEADD(DAY, -60, CURRENT_DATE())
AND DATE_HOUR < CURRENT_DATE()
GROUP BY DATE_HOUR+---------------------+---------------+
| DATE_HOUR | GROSS_REVENUE |
+---------------------+---------------+
| 2024-03-08 00:00:00 | 1500.00 |
| 2024-03-08 01:00:00 | 1250.00 |
| 2024-03-08 02:00:00 | 1300.00 |
| 2024-03-08 03:00:00 | 1400.00 |
| 2024-03-08 04:00:00 | 1350.00 |
| 2024-03-08 05:00:00 | 1450.00 |
| 2024-03-08 06:00:00 | 1550.00 |
| 2024-03-08 07:00:00 | 1600.00 |
| 2024-03-08 08:00:00 | 1650.00 |
| 2024-03-08 09:00:00 | 1700.00 |
| ... | ... |
+---------------------+---------------+Now that we have the historical sample data we can create the FORECASTfunction and feed it with the sample data. Then call the function to predict revenue for the next 24 hours.
-- Create the forecast function
CREATE OR REPLACE SNOWFLAKE.ML.FORECAST EXAMPLE_FORECAST_FUNCTION(
INPUT_DATA => SYSTEM$QUERY_REFERENCE('
SELECT
DATE_HOUR,
SUM(GROSS_REVENUE) AS GROSS_REVENUE
FROM GUMGUM_AD_EVENTS_MOCK_TABLE
WHERE DATE_HOUR >= DATEADD(DAY, -60, CURRENT_DATE())
AND DATE_HOUR < CURRENT_DATE()
GROUP BY DATE_HOUR
'),
TIMESTAMP_COLNAME => 'DATE_HOUR',
TARGET_COLNAME => 'GROSS_REVENUE'
);
-- Call the forecast function
CALL EXAMPLE_FORECAST_FUNCTION!FORECAST(FORECASTING_PERIODS)+-------------------------+----------------+---------------+---------------+
| TS | FORECAST | LOWER_BOUND | UPPER_BOUND |
+-------------------------+----------------+---------------+---------------+
| 2024-05-09 00:00:00.000 | 9420.017200731 | 7047.631843276| 11792.402558185|
| 2024-05-09 01:00:00.000 | 9313.021821555 | 6935.890146440| 11690.153496669|
| 2024-05-09 02:00:00.000 | 10441.137499776| 8059.268964906| 12823.006034645|
| 2024-05-09 03:00:00.000 | 11372.469364371| 8985.873371336| 13759.065357405|
| 2024-05-09 04:00:00.000 | 14309.853449531| 11918.539344161| 16701.1675549 |
| 2024-05-09 05:00:00.000 | 18839.31659797 | 16443.293670887| 21235.339525053|
| 2024-05-09 06:00:00.000 | 19361.542164946| 16960.819652102| 21762.26467779 |
| 2024-05-09 07:00:00.000 | 21136.436335744| 18731.023418957| 23541.849252531|
| 2024-05-09 08:00:00.000 | 21685.535327454| 19275.441134936| 24095.629519973|
| 2024-05-09 09:00:00.000 | 21799.502922142| 19384.736529014| 24214.26931527 |
| ... | ... | ... | ... |
+-------------------------+----------------+---------------+---------------+Storing the forecast results
Now that we have the results let’s store them so we can use them in the next steps where we will compare the forecast to the actuals and monitor for anomalies.
To achieve this we will create a Snowflake stored procedure to (1) re-create the SNOWFLAKE.ML.FORECAST function, (2) call the forecast function, and (3) insert the results into a Snowflake table. We will use a Snowflake task to call the stored procedure on a daily interval.
CREATE OR REPLACE TABLE EXAMPLE_FORECAST_TABLE (
TS TIMESTAMP_NTZ(9),
FORECAST FLOAT,
LOWER_BOUND FLOAT,
UPPER_BOUND FLOAT
);CREATE OR REPLACE PROCEDURE EXAMPLE_STORED_PROCEDURE("START_TIME" TIMESTAMP_NTZ(9))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
EXECUTE AS OWNER
AS '
declare
query1 varchar default ''CREATE OR REPLACE SNOWFLAKE.ML.FORECAST EXAMPLE_FORECAST_FUNCTION(INPUT_DATA => SYSTEM$QUERY_REFERENCE(\\''SELECT DATE_HOUR, SUM(GROSS_REVENUE) GROSS_REVENUE FROM GUMGUM_AD_EVENTS_MOCK_TABLE WHERE DATE_HOUR >= \\\\\\''''|| DATEADD(DAY, -60, START_TIME) || ''\\\\\\'' AND DATE_HOUR < \\\\\\'''' || START_TIME || ''\\\\\\'' GROUP BY DATE_HOUR\\'' ), TIMESTAMP_COLNAME => \\''DATE_HOUR\\'', TARGET_COLNAME => \\''GROSS_REVENUE\\'')'';
query2 varchar default ''CALL EXAMPLE_FORECAST_FUNCTION!FORECAST (FORECASTING_PERIODS => 24)'';
query3 varchar default ''INSERT INTO EXAMPLE_FORECAST_TABLE SELECT ts, forecast, lower_bound, upper_bound FROM TABLE(RESULT_SCAN(-1))'';
begin
execute immediate query1;
execute immediate query2;
execute immediate query3;
return ''Success'';
end;
';Scheduling with Snowflake tasks
The stored procedure needs to be called daily. Below is an example using a Snowflake task, but you can also use your favorite workflow orchestrator (at GumGum we use Airflow).
-- Create the task to call the stored procedure daily at 10 AM PST
CREATE OR REPLACE TASK DAILY_TASK_CALL_EXAMPLE_PROCEDURE
WAREHOUSE = MY_WAREHOUSE
SCHEDULE = 'USING CRON 0 10 * * * America/Los_Angeles'
AS
CALL EXAMPLE_STORED_PROCEDURE(CURRENT_TIMESTAMP());
-- Enable the task
ALTER TASK DAILY_TASK_CALL_EXAMPLE_PROCEDURE RESUME;Joining the forecast data to the actual data in reporting
We now have the forecast and the actual data available in Snowflake tables. In this step, we will join both in a Business Intelligence tool and set up the negative anomaly monitoring.
At GumGum we use Looker as our BI tool. We will create a new view for the EXAMPLE_FORECAST_TABLE Snowflake table. Then join it to the view that has the actual data in the Explore that reports the actual data.
explore: gumgum_actual_data
join: gumgum_prediction_data
type: inner
relationship: one_to_one
sql_on: ${gumgum_actual_data.date_hour} = ${gumgum_prediction_data.date_hour};Create an alert for a negative anomaly
This is where the magic happens. In the explore, we will select the date hour time dimension, the actual revenue, the forecast, and the lower bound because we only care about the negative anomaly. I know this may seem obvious, but it’s important to emphasize that our primary concern is the negative anomalies, as low revenue is detrimental to any business. Then we will create a custom negative anomaly measure. The negative anomaly measure will write the revenue if it is below the lower bound, otherwise it will be null.
Let’s use this explore and create a tile in a Dashboard where we can monitor the negative anomaly.
Tile visualization in Looker
Looker has a feature that allows you to generate alerts if a condition is met in a tile. We will set up the condition to alert us through a Slack message whenever the negative anomaly exceeds 0.
Voilà, our team is now alerted each time our revenue drops below the lower bound of the predicted value.
Summary
Receiving a Slack alert for a negative revenue anomaly helps us act immediately and determine if internal production issues or external factors, such as market shifts or changes in consumer behavior cause the anomaly. It also helps our operations and engineering teams sleep better at night. This quick response ensures we minimize potential losses and adjust our strategies effectively.
Staying alert means staying ahead — nipping problems in the bud before they bloom into crises.
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
We’re always looking for new talent! View jobs.
