Build a Snowflake Notebook Dashboard with AI Anomaly Detection (Week/Month/Quarter) — End-to-End Tutorial

Introduction: What you’ll build

In this tutorial, build a production-ready analytics workflow directly inside Snowflake Notebooks (in Snowsight). Do this in a single place: prepare data with SQL, transform at scale with Snowpark, visualize results, and add AI-powered anomaly detection that automatically adapts to a selected time grain: week, month, or quarter.

You will end with:

• A Snowflake Notebook that queries a time-series metric (example: daily sales).
• A dashboard-like set of visuals (trend line + anomaly markers).
• A parameter-driven time selector (week/month/quarter) for dynamic aggregation.
• An anomaly detection module (Isolation Forest + a simple statistical fallback) that flags unusual periods.
• An optional scheduled execution pattern (so your notebook can refresh regularly).

Why this approach works: Snowflake Notebooks keep compute close to data, reduce data movement, and let you combine SQL + Python + visualization in one reproducible artifact that can be shared and scheduled.

Excerpt / Summary

Create a Snowflake Notebook that powers a dashboard and AI anomaly detection for weekly/monthly/quarterly views. You’ll set up database objects, generate a clean time series, build interactive visuals, detect anomalies with ML, and validate results with tests and troubleshooting guidance.

Prerequisites

• Snowflake account with Snowsight access and permission to create objects.
• Privileges (minimum recommended):
  • USAGE on the target database and schema
  • CREATE TABLE and CREATE VIEW on the schema
  • Ability to run a warehouse (USAGE on warehouse)
  • (Optional for scheduling) CREATE TASK on schema and task execution privileges
• Basic knowledge of SQL and Python (Pandas-level is enough).

Important warning: This tutorial includes DROP statements in an optional cleanup section. Run them only if you are sure you don’t need the objects.

Step 1) Create a dedicated database, schema, and warehouse

Do this to isolate your tutorial assets and make permissions and cleanup straightforward. A dedicated schema prevents accidental clashes with existing production objects.

1.1 Run setup SQL

Open Snowsight → Worksheets (or run from a SQL cell in the Notebook later) and execute:

-- Choose names that match your org conventions
CREATE DATABASE IF NOT EXISTS NOTEBOOK_DEMO_DB;
CREATE SCHEMA IF NOT EXISTS NOTEBOOK_DEMO_DB.DASH_APP;

-- Use an existing warehouse if your org requires it.
-- If you are allowed to create one:
CREATE WAREHOUSE IF NOT EXISTS NOTEBOOK_DEMO_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

Expected result

• Database and schema exist and appear in Snowsight object explorer.
• Warehouse is available and can resume when queries run.

Common errors

• Insufficient privileges: Ask an admin for CREATE DATABASE/CREATE WAREHOUSE or reuse existing objects you already have access to.
• Warehouse won’t start: Check credit limits or resource monitors; try a smaller size (XSMALL) and ensure AUTO_RESUME=TRUE.

Step 2) Create a sample time-series dataset (or point to your real one)

Do this because anomaly detection needs a clean, consistent time series. A common failure mode is missing dates or inconsistent grain (some days missing), which causes misleading anomalies.

2.1 Create a demo table

If you already have a facts table (orders, events, revenue), skip to Step 3 and adjust SQL. Otherwise, create a synthetic dataset so you can test everything end-to-end.

USE DATABASE NOTEBOOK_DEMO_DB;
USE SCHEMA DASH_APP;
USE WAREHOUSE NOTEBOOK_DEMO_WH;

CREATE OR REPLACE TABLE FACT_DAILY_METRIC (
  METRIC_DATE DATE,
  METRIC_VALUE NUMBER(18,2)
);

-- Populate ~18 months of daily data with seasonality + noise
INSERT INTO FACT_DAILY_METRIC
WITH dates AS (
  SELECT DATEADD('day', SEQ4(), '2024-01-01'::DATE) AS d
  FROM TABLE(GENERATOR(ROWCOUNT => 550))
), base AS (
  SELECT
    d AS metric_date,
    /* baseline + weekly pattern + random noise */
    100
    + 10 * SIN(2 * 3.14159 * (DATE_PART('dayofweek', d) / 7))
    + UNIFORM(-8, 8, RANDOM()) AS metric_value
  FROM dates
)
SELECT
  metric_date,
  metric_value
FROM base;

-- Inject a couple of anomalies (spikes and dips)
UPDATE FACT_DAILY_METRIC
SET METRIC_VALUE = METRIC_VALUE + 80
WHERE METRIC_DATE IN ('2024-06-15'::DATE, '2025-02-10'::DATE);

UPDATE FACT_DAILY_METRIC
SET METRIC_VALUE = GREATEST(1, METRIC_VALUE - 60)
WHERE METRIC_DATE IN ('2024-11-25'::DATE);

Expected result

Query the table:

SELECT MIN(METRIC_DATE), MAX(METRIC_DATE), COUNT(*)
FROM FACT_DAILY_METRIC;

You should see ~550 rows with a continuous date range.

Common errors

• Generator rowcount too small/large: Adjust ROWCOUNT to match your desired time horizon.
• Date functions differ: Use Snowflake’s DATEADD, DATE_PART, and TABLE(GENERATOR()) exactly as shown.

Step 3) Create your Snowflake Notebook in Snowsight

Do this to combine SQL + Python + markdown narratives and produce a dashboard-like experience without leaving Snowflake.

3.1 Create the Notebook

1. Go to Snowsight → Projects → Notebooks.
2. Click + Notebook.
3. Select:
   • Database: NOTEBOOK_DEMO_DB
   • Schema: DASH_APP
   • Warehouse: NOTEBOOK_DEMO_WH
4. Name it: Dashboard + Anomaly Detection (W/M/Q)

Screenshot description (what you should see)

A Notebook editor with a left-side cell outline/minimap and an initial cell. The top bar shows selected database/schema/warehouse context.

Expected result

• You can add cells of type SQL, Python, and Markdown.
• Running a SQL cell uses the selected warehouse automatically.

Step 4) Build the “dashboard dataset” with dynamic week/month/quarter aggregation

Do this because your visuals and anomaly detection should operate on a consistent aggregation level. Switching between week/month/quarter should change only one parameter, not require rewriting your logic.

4.1 Create a parameter cell (Python)

Add a Python cell and define the selectable time grain and date window.

from snowflake.snowpark.context import get_active_session

session = get_active_session()

# Change these values to test different dashboard views
TIME_GRAIN = "week"   # allowed: "week", "month", "quarter"
START_DATE = "2024-01-01"
END_DATE = "2025-06-30"

assert TIME_GRAIN in {"week", "month", "quarter"}, "TIME_GRAIN must be week/month/quarter"

Why this is necessary: A single source of truth for time grain prevents mismatched filters across SQL and Python cells. The assertion fails fast if someone types weeks or qtr incorrectly.

4.2 Query aggregated data (SQL cell)

Add a SQL cell. Use the time grain variable by writing three separate queries (one per grain) or by using a CASE pattern. The most robust approach in Snowflake SQL is to branch based on a known value. In a tutorial notebook, keep it explicit:

-- WEEK view
SELECT
  DATE_TRUNC('WEEK', METRIC_DATE) AS PERIOD_START,
  SUM(METRIC_VALUE) AS METRIC_TOTAL
FROM NOTEBOOK_DEMO_DB.DASH_APP.FACT_DAILY_METRIC
WHERE METRIC_DATE BETWEEN '2024-01-01' AND '2025-06-30'
GROUP BY 1
ORDER BY 1;

If you want to switch to month or quarter, duplicate the cell and change WEEK to MONTH or QUARTER.

Why this is necessary: DATE_TRUNC ensures all rows align to a period boundary (start of week/month/quarter), giving you stable buckets for charting and anomaly detection.

Expected result

• A result set with columns PERIOD_START and METRIC_TOTAL.
• Periods are sorted ascending and evenly spaced (weekly/monthly/quarterly).

Common errors

• Weeks start on unexpected day: Weekly truncation uses Snowflake’s definition. If your business defines weeks differently, standardize using a calendar table.
• Gaps in periods: If you switch to a sparse metric, generate a full calendar and left join to fill missing periods with 0.

Step 5) Visualize the trend inside the Notebook (dashboard section)

Do this to turn your notebook into a “narrative dashboard”: a clean headline metric, a trend chart, and later anomaly overlays.

5.1 Load data into Pandas (Python cell)

import pandas as pd

# Map TIME_GRAIN to a Snowflake DATE_TRUNC unit
grain_to_unit = {
    "week": "WEEK",
    "month": "MONTH",
    "quarter": "QUARTER"
}
unit = grain_to_unit[TIME_GRAIN]

sql = f"""
SELECT
  DATE_TRUNC('{unit}', METRIC_DATE) AS PERIOD_START,
  SUM(METRIC_VALUE) AS METRIC_TOTAL
FROM NOTEBOOK_DEMO_DB.DASH_APP.FACT_DAILY_METRIC
WHERE METRIC_DATE BETWEEN '{START_DATE}' AND '{END_DATE}'
GROUP BY 1
ORDER BY 1;
"""

pdf = session.sql(sql).to_pandas()
pdf["PERIOD_START"] = pd.to_datetime(pdf["PERIOD_START"])
pdf.head()

Why this is necessary: Most visualization and ML libraries operate on local DataFrames. For aggregated period data, pulling into Pandas is usually safe and fast (you’re moving tens/hundreds of rows, not billions).

Expected result

A small table preview like:

  PERIOD_START  METRIC_TOTAL
0  2024-01-01        700.12
1  2024-01-08        689.55
...

5.2 Create a line chart (Python cell)

Use a simple Matplotlib chart (works reliably in notebook environments):

import matplotlib.pyplot as plt

plt.figure(figsize=(10,4))
plt.plot(pdf["PERIOD_START"], pdf["METRIC_TOTAL"], marker="o", linewidth=2)
plt.title(f"Metric Trend ({TIME_GRAIN})")
plt.xlabel("Period start")
plt.ylabel("Metric total")
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()

Expected result

• A readable trend line.
• You should spot the injected spike/dip if you used the synthetic dataset.

Screenshot description

A single line chart with dot markers per period. The y-values mostly follow a stable range with one or two visible spikes.

Step 6) Add AI anomaly detection (week/month/quarter aware)

Do this to automatically flag unusual periods without manually choosing thresholds. You’ll implement two methods:

• Isolation Forest (ML-based): good general-purpose anomaly detection.
• Z-score fallback (statistics-based): useful if packages are restricted or for transparency.

6.1 Install/enable packages (Notebook package picker)

In the Notebook UI, open Packages (or the environment/package manager panel) and add:

• scikit-learn
• numpy
• pandas (usually already present)
• matplotlib (usually already present)

Why this is necessary: Snowflake Notebooks run in a managed environment. You must explicitly include non-default Python libraries to avoid ModuleNotFoundError.

Common errors

• Package not allowed: Your account may restrict external packages. Use the Z-score fallback in Step 6.4.
• Version conflicts: Pin packages if needed using the package manager UI; restart the notebook kernel/session if imports still fail.

6.2 Detect anomalies with Isolation Forest (Python cell)

import numpy as np
from sklearn.ensemble import IsolationForest

# Defensive checks
if pdf.empty:
    raise ValueError("No data returned. Check START_DATE/END_DATE and table name.")

X = pdf[["METRIC_TOTAL"]].astype(float)

# Tune contamination based on expected anomaly frequency.
# For weekly data over ~1 year, 0.08 means ~4-5 anomalies.
contamination = 0.08 if TIME_GRAIN == "week" else 0.12 if TIME_GRAIN == "month" else 0.2

model = IsolationForest(
    n_estimators=200,
    contamination=contamination,
    random_state=42
)

pdf["ANOMALY_LABEL"] = model.fit_predict(X)  # -1 = anomaly, 1 = normal
pdf["IS_ANOMALY"] = pdf["ANOMALY_LABEL"] == -1

pdf[["PERIOD_START", "METRIC_TOTAL", "IS_ANOMALY"]].tail(10)

Why this is necessary: Isolation Forest isolates points that are easier to separate from the rest of the distribution. It works well when you don’t have labeled anomalies.

Expected result

• A boolean IS_ANOMALY column.
• Some periods marked True (especially near your injected spikes/dips).

6.3 Visualize anomalies on the chart (Python cell)

plt.figure(figsize=(10,4))
plt.plot(pdf["PERIOD_START"], pdf["METRIC_TOTAL"], marker="o", linewidth=2, label="Metric")

anoms = pdf[pdf["IS_ANOMALY"]]
plt.scatter(anoms["PERIOD_START"], anoms["METRIC_TOTAL"], color="red", s=80, label="Anomaly")

plt.title(f"Anomaly Detection ({TIME_GRAIN})")
plt.xlabel("Period start")
plt.ylabel("Metric total")
plt.grid(True, alpha=0.3)
plt.legend()
plt.tight_layout()
plt.show()

Expected result

Red markers appear on top of the line where anomalies are detected.

6.4 Add a transparent Z-score fallback (Python cell)

Use this if ML packages are blocked, or if you want an explainable baseline:

# Z-score method: flag points more than k standard deviations from mean
k = 2.5
mu = pdf["METRIC_TOTAL"].mean()
sigma = pdf["METRIC_TOTAL"].std(ddof=0)

pdf["Z_SCORE"] = (pdf["METRIC_TOTAL"] - mu) / (sigma if sigma else 1)
pdf["IS_ANOMALY_Z"] = pdf["Z_SCORE"].abs() >= k

pdf.loc[pdf["IS_ANOMALY_Z"], ["PERIOD_START", "METRIC_TOTAL", "Z_SCORE"]]

Why this is necessary: Z-score is easy to explain to stakeholders and provides a strong sanity check against more complex models.

Step 7) Add a “dashboard control” for week/month/quarter selection

Do this to make your notebook behave like a dashboard: the user changes one selector and everything refreshes.

Snowflake Notebooks can vary by account features. If you have Streamlit integration available, use it for UI widgets. If not, keep it simple with a variable cell that users edit.

Option A: Simple selector via a single variable (works everywhere)

In Step 4.1, change:

TIME_GRAIN = "month"

Then rerun the notebook cells (or “Run all”).

Option B: Streamlit-powered selector (if enabled in your environment)

Add packages: streamlit. Then in a Python cell:

import streamlit as st

TIME_GRAIN = st.selectbox("Select time grain", ["week", "month", "quarter"], index=0)
START_DATE = st.date_input("Start date", value=pd.to_datetime("2024-01-01")).isoformat()
END_DATE = st.date_input("End date", value=pd.to_datetime("2025-06-30")).isoformat()

st.write("Current settings:", {"TIME_GRAIN": TIME_GRAIN, "START_DATE": START_DATE, "END_DATE": END_DATE})

Why this is necessary: A UI selector reduces friction for business users and encourages exploration without editing code.

Expected result

• Changing the dropdown updates the query, chart, and anomaly detection output after rerun.

Step 8) Persist results back to Snowflake (so dashboards can be shared)

Do this so other tools (Snowsight charts, BI tools, or a Streamlit app) can read a stable table/view of anomaly flags without requiring them to run your notebook.

8.1 Write anomalies to a table (Python cell)

from snowflake.snowpark import Row

# Create a Snowpark DataFrame from Pandas and write it back
spdf = session.create_dataframe(
    pdf[["PERIOD_START", "METRIC_TOTAL", "IS_ANOMALY"]].assign(TIME_GRAIN=TIME_GRAIN)
)

target_table = "NOTEBOOK_DEMO_DB.DASH_APP.ANOMALY_RESULTS"

# Overwrite for simplicity; change to append for history
spdf.write.mode("overwrite").save_as_table(target_table)

session.sql(f"SELECT * FROM {target_table} ORDER BY PERIOD_START DESC LIMIT 20").show()

Why this is necessary: Persisting results decouples compute (the notebook run) from consumption (dashboards). It also enables scheduling, auditing, and downstream alerting.

Expected result

• A new table ANOMALY_RESULTS exists.
• It includes TIME_GRAIN so you can store multiple grains if you change to append mode.

Warning (destructive behavior)

mode("overwrite") replaces the table contents. If you need history, use mode("append") and include a run timestamp column.

Step 9) Schedule refresh (optional, but recommended)

Do this if you want anomalies recalculated automatically (daily/weekly) and written to ANOMALY_RESULTS.

Scheduling mechanics vary by Snowflake account configuration and notebook features. Use the Notebook’s built-in scheduling UI if available. If your environment prefers tasks, create a task that calls a stored procedure (advanced pattern).

9.1 Notebook UI scheduling (recommended when available)

1. In the Notebook, find Schedule or Run on a schedule.
2. Set frequency (e.g., daily at 06:00).
3. Ensure the notebook writes results to ANOMALY_RESULTS (Step 8).

Expected result

• The notebook runs at the configured time.
• ANOMALY_RESULTS updates automatically.

Common errors

• Schedule fails due to permissions: Ensure the run context role has warehouse usage and table write privileges.
• Long runtime: Reduce data range, aggregate earlier in SQL, or increase warehouse size temporarily.

Troubleshooting: Common issues and solutions

1) “ModuleNotFoundError: No module named sklearn”

• Add scikit-learn in the Notebook package manager.
• If packages are restricted, use Step 6.4 (Z-score) instead.

2) “No data returned” or empty charts

• Verify table name and schema: NOTEBOOK_DEMO_DB.DASH_APP.FACT_DAILY_METRIC.
• Check date range: ensure START_DATE/END_DATE overlap the data.
• Run: SELECT COUNT(*) FROM ... WHERE METRIC_DATE BETWEEN ...

3) Anomalies look wrong (too many or none)

• Tune contamination (Isolation Forest). If you expect 1–2 anomalies per year, lower it.
• Switch to Z-score and compare; if both disagree strongly, inspect missing periods or outliers in raw data.
• Ensure you’re detecting anomalies on the right metric (sum vs average).

4) Notebook runs slowly

• Aggregate in SQL first (as shown). Do not pull raw daily-level detail into Pandas for long ranges.
• Use a warehouse size that matches your workload and enable auto-suspend.

5) Week definitions don’t match business reporting

• Create a date dimension table with a BUSINESS_WEEK_START column and group by that.
• Do not rely on default truncation if your finance calendar is custom (4-4-5, etc.).

Testing: Verify everything works

Run these checks after building the notebook. Do this before you share it.

Test 1: Data continuity

-- Confirm daily dataset has no gaps (demo data should be continuous)
WITH d AS (
  SELECT METRIC_DATE
  FROM NOTEBOOK_DEMO_DB.DASH_APP.FACT_DAILY_METRIC
), r AS (
  SELECT MIN(METRIC_DATE) AS min_d, MAX(METRIC_DATE) AS max_d
  FROM d
), cal AS (
  SELECT DATEADD('day', SEQ4(), (SELECT min_d FROM r)) AS dt
  FROM TABLE(GENERATOR(ROWCOUNT => 2000))
  QUALIFY dt <= (SELECT max_d FROM r)
)
SELECT COUNT(*) AS missing_days
FROM cal
LEFT JOIN d ON cal.dt = d.metric_date
WHERE d.metric_date IS NULL;

Expected result: missing_days = 0 (for the synthetic dataset).

Test 2: Aggregation sanity

# Switching TIME_GRAIN should change row counts:
# week > month > quarter
print(TIME_GRAIN, len(pdf))

Expected result: weekly returns the most rows; quarterly returns the fewest.

Test 3: Persistence

SELECT TIME_GRAIN, COUNT(*)
FROM NOTEBOOK_DEMO_DB.DASH_APP.ANOMALY_RESULTS
GROUP BY 1;

Expected result: at least one row for the latest run’s grain.

Next Steps: Extend this into an “amazing dashboard”

• Add multiple KPIs: repeat the pipeline for revenue, orders, active users, conversion rate. Store each in a long format table: (time_grain, period_start, metric_name, metric_value, is_anomaly).
• Use forecasting-based anomalies: fit a forecasting model on historical periods, compute prediction intervals, and flag periods outside bounds. This often reduces false positives compared to distribution-only models.
• Fill missing periods: build a calendar spine table and left-join facts to guarantee continuous periods (critical for time-series quality).
• Create a Streamlit app: move from “notebook dashboard” to a full interactive app embedded in Snowflake for business users.
• Add alerting: write anomalies to a table and trigger notifications externally (email/Slack) via your orchestration tool. Keep Snowflake as the source of truth.
• Harden security: run notebook/schedules under a least-privilege role and use separate schemas for dev vs prod.

Conclusion

You now have a complete Snowflake Notebook tutorial pattern: set up a clean time series, build dashboard-ready aggregations, visualize trends, and apply AI anomaly detection that adapts to weekly/monthly/quarterly reporting. Persist results back to Snowflake to make them shareable and schedule runs to keep insights fresh.

To continue, focus on (1) calendar correctness, (2) multi-metric modeling, and (3) operationalizing outputs (tables + schedules + alerts). Those three upgrades turn a great tutorial into a real analytics product.

Optional cleanup (run only if you want to delete everything)

Warning: This permanently deletes objects.

-- WARNING: Destructive cleanup
DROP DATABASE IF EXISTS NOTEBOOK_DEMO_DB;
DROP WAREHOUSE IF EXISTS NOTEBOOK_DEMO_WH;