Snowflake-Labs/sfguide-time-series-in-retail

Snowflake-Labs/sfguide-time-series-in-retail

Language: Jupyter Notebook

License: MIT

Stars: 3

Forks: 1

Open issues: 0

Created: 2025-09-11T19:57:22Z

Pushed: 2025-10-13T18:39:55Z

Default branch: main

Fork: no

Archived: no

README:

🏔️ Snowflake Retail Time Series Forecasting Demo

🎯 Objective: Demonstrate Snowflake's industry-leading time series functionality for predicting retail sales using ML forecasting capabilities.

This demo showcases how to use Snowflake's time series features to predict athletic shoe sales, incorporating competitor pricing data and leveraging advanced time series functions including:

• 📈 Time Series Functions - TIME_SLICE, ASOF JOIN, windowed aggregations
• 🤖 ML.FORECAST - Native time series forecasting using Snowflake ML
• 📊 Time Series Analytics - Rolling averages, lag functions, and trend analysis
• 🏪 Retail Scenario - Athletic shoe retailer competing on service vs. discount pricing

🚀 What You'll Build

By the end of this demo, you'll have a comprehensive retail forecasting system that can:

✅ Generate and load realistic retail sales transaction data ✅ Simulate competitor pricing data with irregular update intervals ✅ Aggregate sales data using time series functions like TIME_SLICE ✅ Perform ASOF JOINs to incorporate competitor pricing at the right time points ✅ Create rolling window calculations and lag features for enhanced predictions ✅ Build ML forecasting models using Snowflake's native ML.FORECAST functionality ✅ Visualize time series data and predictions using integrated Python libraries

📋 Repository Structure

├── README.md # This comprehensive guide
├── LEGAL.md # Legal notices
├── LICENSE # License information
├── notebooks/ # Snowflake Notebooks
│ └── 0_start_here.ipynb # Main demo notebook for retail time series forecasting
├── scripts/ # SQL setup files
│ └── setup.sql # Database, schema, and table setup script

🎯 Demo Highlights

This retail time series forecasting demo showcases several key Snowflake capabilities:

Time Series Functions

• TIME_SLICE: Aggregating sales data by day, week, or other time periods
• ASOF JOIN: Point-in-time joins to incorporate competitor pricing at the right moments
• Windowed Aggregations: Rolling averages and trend calculations over time windows
• LAG Functions: Incorporating historical values as features for prediction

Machine Learning Integration

• ML.FORECAST: Native time series forecasting without external tools
• Automated Feature Engineering: Snowflake automatically generates lag and trend features
• Multi-series Forecasting: Predicting sales for multiple products simultaneously

Business Scenario

• Retail Competition: Athletic shoe retailer vs. discount competitor
• Pricing Impact: How competitor price changes affect sales predictions
• Irregular Data: Handling scraped pricing data that arrives at uneven intervals
• Actionable Insights: Using forecasts to plan inventory and marketing

📋 Prerequisites

🔑 Required Access & Permissions

☑️ Snowflake account with appropriate privileges ☑️ SQL execution permissions ☑️ Ability to create databases, schemas, and tables ☑️ Access to Snowflake Notebooks ☑️ Access to Snowflake ML functions (ML.FORECAST) ☑️ Python packages: pandas, matplotlib, numpy, scipy (available in Snowflake Notebooks)

🏗️ Setup Instructions

1️⃣ Step 1: Execute SQL Setup Statements

1.1 Access Snowsight and Open Worksheets

1. Open Snowflake in your web browser (Snowsight interface) 2. Navigate to Projects → Worksheets in the left sidebar 3. Click + Worksheet to create a new SQL worksheet

1.2 Import and Execute the Setup Script

1. Locate the scripts/setup.sql file from this repository 2. Open the file and copy all contents 3. Paste the SQL statements into your new Snowflake worksheet 4. Important: The script uses accountadmin role for initial setup 5. Execute all statements by clicking ▶ Run All or pressing Ctrl+Shift+Enter

1.3 What the Setup Script Creates

The setup.sql script will automatically create:

Security & Access:

• Role: retail_time_series_role with ML.FORECAST capabilities
• Warehouse: retail_time_series_wh (Small, auto-suspend after 60 seconds)
• Grants: Comprehensive permissions for time series analysis and ML functions

Data Infrastructure:

• Database: RETAIL_TIME_SERIES
• Schema: SALES_ANALYTICS
• Tables: Three core tables for the retail forecasting demo:
• purchases - Individual customer purchase transactions
• product_sales_by_day - Daily aggregated sales by product
• competitor_pricing - Competitor price tracking data

1.4 Verify Setup

The setup script includes verification commands at the end. You should see:

-- Verify database and schema creation
SHOW DATABASES LIKE 'RETAIL_TIME_SERIES';
SHOW SCHEMAS IN DATABASE RETAIL_TIME_SERIES;

-- Verify table creation
DESCRIBE TABLE purchases;
DESCRIBE TABLE product_sales_by_day;
DESCRIBE TABLE competitor_pricing;

1.5 Switch to Demo Role (Recommended)

For the remaining steps, you can optionally switch to the demo role:

-- Switch to the demo role for subsequent operations
USE ROLE retail_time_series_role;
USE WAREHOUSE retail_time_series_wh;
USE DATABASE RETAIL_TIME_SERIES;
USE SCHEMA SALES_ANALYTICS;

Note: If you continue with accountadmin, that's fine too - the setup script grants all necessary permissions to both roles.

---

2️⃣ Step 2: Load and Execute the Demo Notebook

2.1 Access Snowflake Notebooks in Snowsight

1. In Snowsight, navigate to Projects → Notebooks in the left sidebar 2. Click + Notebook to create a new notebook 3. Choose Import .ipynb file option

2.2 Import the Demo Notebook

1. Click Browse and select the notebooks/0_start_here.ipynb file from this repository 2. Configure the notebook environment:

• Database: RETAIL_TIME_SERIES
• Schema: SALES_ANALYTICS
• Warehouse: retail_time_series_wh (created in Step 1) or any S/M size warehouse

3. Add Required Packages: In the packages dropdown, add the following packages:

• scipy
• matplotlib

4. Click Create Notebook

2.3 Understanding the Notebook Structure

The 0_start_here.ipynb notebook contains:

• Demo Scenario: Introduction to the athletic shoe retailer competing on service vs. price
• Table Creation: SQL statements to...