Skip to main content

DataFrame-based ML Workflows in Snowflake with Snowpark

Snowflake’s Snowpark enables teams to build end-to-end Machine Learning (ML) workflows directly inside Snowflake using familiar DataFrame-based programming (similar to Pandas).

This approach:

  • Minimizes data movement
  • Improves security and governance
  • Leverages Snowflake’s scalable compute

πŸ”Ά Why This Matters (Business Perspective)

In traditional ML workflows:

  • Data is exported to external tools ❌
  • Pipelines become slow and complex ❌
  • Governance becomes difficult ❌

With Snowpark:

  • Data stays inside Snowflake βœ…
  • ML runs closer to data βœ…
  • Outputs are immediately available for reporting βœ…

1. What are DataFrame-based ML Workflows?

These workflows use DataFrames (similar to Pandas) to:

  • Prepare and transform data
  • Train and evaluate ML models (scikit-learn, XGBoost, etc.)
  • Store predictions back in Snowflake

πŸ”‘ Benefits​

  • No data egress β†’ Data never leaves Snowflake
  • Scalable β†’ Uses Snowflake warehouses
  • Python-native β†’ Easy for data scientists
  • Unified platform β†’ Data + ML in one place

2. Step 1 – Data Preparation using Snowpark

Before training ML models, data needs to be cleaned and transformed.

import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col

def main(session: snowpark.Session):

print("πŸ”Ά Step 1: Data Preparation")

df = session.table("CUSTOMER_DATA")

df_transformed = (
df.filter(col("AGE") > 30)
.with_column("AGE_SQUARED", col("AGE") * col("AGE"))
)

df_transformed.show()

return df_transformed

πŸ’‘ What Happens Here?​

  • Data is processed inside Snowflake
  • No data is downloaded
  • This step represents feature engineering

2.5 Step – Sample Dataset Setup (Run Before ML Script)

Before running the ML workflow, we need a sample dataset.

This simulates a Silver layer table (SALES_DATA) used for model training.


SQL Setup​

-- Set context
USE DATABASE DEMO_DB;
USE SCHEMA DEMO_SCHEMA;

-- Create table
CREATE OR REPLACE TABLE SALES_DATA (
YEAR INT,
SALES FLOAT
);

-- Insert sample data
INSERT INTO SALES_DATA (YEAR, SALES) VALUES
(2015, 15000),
(2016, 18000),
(2017, 21000),
(2018, 25000),
(2019, 30000),
(2020, 28000),
(2021, 35000),
(2022, 40000),
(2023, 42000),
(2024, 46000);

πŸ” Verify Data​

SELECT * FROM SALES_DATA ORDER BY YEAR;

πŸ’‘ Business Context​

  • YEAR β†’ Time dimension
  • SALES β†’ Business metric (Revenue)

πŸ‘‰ In real-world systems, this data comes from:

  • ERP / CRM systems
  • Transactional systems
  • Aggregated reporting tables

πŸ” Optional: Incremental Data (Simulate Real Pipeline)​

INSERT INTO SALES_DATA VALUES (2025, 50000);

3. Step 2 – Full ML Workflow (End-to-End)

This example demonstrates a complete ML pipeline:

  • Data extraction
  • Model training
  • Prediction
  • Storage in Snowflake

🎯 Use Case: Sales Prediction​

Predict future sales based on historical trends


Snowpark ML Script (Worksheet Ready)​

import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col
import pandas as pd
from sklearn.linear_model import LinearRegression

def main(session: snowpark.Session):

print("ML Workflow: Sales Prediction")

# Step 1: Extract data (SILVER layer)
df = session.table("SALES_DATA").select("YEAR", "SALES")
df.show()

# Step 2: Convert to Pandas for ML
df_pd = df.to_pandas()

# Step 3: Train model
model = LinearRegression()
model.fit(df_pd[["YEAR"]], df_pd["SALES"])

# Step 4: Predict future value
future_year = 2025
prediction = model.predict([[future_year]])

print(f"Predicted Sales for {future_year}: {prediction[0]}")

# Step 5: Save results (GOLD layer)
prediction_df = pd.DataFrame({
"YEAR": [future_year],
"PREDICTED_SALES": prediction
})

session.write_pandas(
prediction_df,
"GOLD_SALES_PREDICTION",
auto_create_table=True,
overwrite=True
)

print("βœ“ Prediction stored in GOLD_SALES_PREDICTION")

return session.table("GOLD_SALES_PREDICTION")

4. Workflow Breakdown

1. Extract​

  • Load data using Snowpark DataFrame
  • Data comes directly from Snowflake

2. Transform​

  • Perform feature engineering
  • Use Snowpark or Pandas

3. Train​

  • Use Python ML libraries (scikit-learn, XGBoost)

4. Predict & Store​

  • Generate predictions
  • Store results back in Snowflake

5. End-to-End Architecture

Raw Data (Bronze)
↓
Clean Data (Silver)
↓
Snowpark ML Workflow
↓
Predictions (Gold)
↓
Dashboards / BI / Applications

6. Business Value

  • Predictions available in real-time dashboards
  • No external ML dependency
  • Faster decision-making
  • Centralized governance

7. Where This Fits in Teams

StepRole
Data PreparationData Engineer
Feature EngineeringData Scientist
Model TrainingData Scientist
DeploymentData Engineer
ConsumptionBusiness / BI Teams

8. Best Practices

  • Use Snowpark for large-scale transformations
  • Use Pandas for small to medium datasets
  • Store intermediate datasets in Snowflake
  • Automate using Stored Procedures + Tasks

9. Common Pitfalls

  • Moving large data to Pandas ❌
  • Ignoring Snowflake compute ❌
  • Training outside governance ❌
  • Not persisting predictions ❌

10. Summary

DataFrame-based ML workflows in Snowpark enable:

  • Data preparation using Snowflake compute
  • Model training using Python libraries
  • Prediction storage inside Snowflake

πŸ‘‰ Result: A secure, scalable, and production-ready ML pipeline fully integrated within Snowflake