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
| Step | Role |
|---|---|
| Data Preparation | Data Engineer |
| Feature Engineering | Data Scientist |
| Model Training | Data Scientist |
| Deployment | Data Engineer |
| Consumption | Business / 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