Introduction to Snowpark
Snowpark is Snowflake's developer framework that enables you to write data pipelines and transformations in Python, Scala, or Java directly within Snowflake. Operations run lazily in Snowflake's engine, pushing transformations to the cloud for optimal performance.
π Why Snowpark?β
- Language Flexibility: Code in Python instead of pure SQL
- Performance: Lazy execution pushed to Snowflake's compute
- Security: Data stays within Snowflake, no egress
- Scalability: Handle large datasets without local limitations
π Prerequisitesβ
- Snowflake account with appropriate roles
- Basic Python knowledge
- Snowpark library:
pip install snowflake-snowpark-python
π Use Casesβ
- ETL Pipelines: Automate data workflows with Python logic
- Data Science: Prepare ML datasets without exporting data
- Real-time Analytics: Query and transform data on-the-fly
- Complex Reporting: Use Python constructs for advanced reports
π οΈ Working Example: Snowflake Python Worksheetβ
This is the standard pattern for Snowflake worksheetsβSnowflake automatically passes the session and invokes the main() handler.
SQL Setup (Run First in Snowflake)β
-- Create demo database and table
CREATE DATABASE IF NOT EXISTS DEMO_DB;
CREATE SCHEMA IF NOT EXISTS DEMO_DB.DEMO_SCHEMA;
CREATE TABLE IF NOT EXISTS DEMO_DB.DEMO_SCHEMA.CUSTOMERS (
ID INT,
NAME VARCHAR(50),
AGE INT,
CITY VARCHAR(50)
);
-- Insert sample data
INSERT INTO DEMO_DB.DEMO_SCHEMA.CUSTOMERS (ID, NAME, AGE, CITY) VALUES
(1, 'Alice Johnson', 28, 'New York'),
(2, 'Bob Smith', 35, 'Los Angeles'),
(3, 'Charlie Brown', 42, 'Chicago'),
(4, 'Diana Prince', 30, 'Houston'),
(5, 'Eve Adams', 25, 'Phoenix');
Python Worksheet Scriptβ
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, concat, lit
def main(session: snowpark.Session):
# Session is automatically provided by Snowflake
# Get current context
current_user = session.sql("SELECT CURRENT_USER()").collect()[0][0]
current_db = session.sql("SELECT CURRENT_DATABASE()").collect()[0][0]
print(f"β Connected as: {current_user} in {current_db}")
# Load table from current session
df = session.table("CUSTOMERS")
# Filter: age > 30
df_filtered = df.filter(col("AGE") > 30)
# Add derived column using concat()
df_result = df_filtered.with_column(
"AGE_GROUP",
concat(col("AGE").cast("string"), lit(" years old"))
)
# Display results
df_result.show()
return df_result
How to use:
- Switch to
DEMO_DB.DEMO_SCHEMAcontext - Create a Python Worksheet in Snowflake
- Copy the script above
- Click Run β session is passed automatically
- View results in Results tab
Pipeline Flow: Medallion Architecture (Bronze β Silver β Gold)β
Demonstrate a multi-table transformation pipeline with JSON parsing and data enrichment using Snowpark.
SQL Setup (Medallion Tables)β
-- Create medallion architecture tables
CREATE DATABASE IF NOT EXISTS DEMO_DB;
CREATE SCHEMA IF NOT EXISTS DEMO_DB.DEMO_SCHEMA;
-- BRONZE: Raw ingestion layer (includes JSON)
CREATE TABLE IF NOT EXISTS DEMO_DB.DEMO_SCHEMA.BRONZE_ORDERS (
ORDER_ID INT,
RAW_DATA VARIANT, -- JSON data
INGESTED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);
-- SILVER: Cleaned, parsed layer
CREATE TABLE IF NOT EXISTS DEMO_DB.DEMO_SCHEMA.SILVER_ORDERS (
ORDER_ID INT,
CUSTOMER_NAME VARCHAR(100),
ORDER_AMOUNT DECIMAL(10, 2),
ORDER_DATE DATE,
PROCESSED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);
-- GOLD: Business layer (aggregated, enriched)
CREATE TABLE IF NOT EXISTS DEMO_DB.DEMO_SCHEMA.GOLD_CUSTOMER_SUMMARY (
CUSTOMER_NAME VARCHAR(100),
TOTAL_ORDERS INT,
TOTAL_SPENT DECIMAL(12, 2),
FIRST_ORDER_DATE DATE,
LAST_ORDER_DATE DATE,
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);
-- Insert sample data into BRONZE (including JSON with VARIANT type)
INSERT INTO DEMO_DB.DEMO_SCHEMA.BRONZE_ORDERS (ORDER_ID, RAW_DATA)
SELECT 1, PARSE_JSON('{"customer": "Alice Johnson", "amount": 150.50, "order_date": "2024-01-15"}')
UNION ALL
SELECT 2, PARSE_JSON('{"customer": "Bob Smith", "amount": 230.75, "order_date": "2024-01-16"}')
UNION ALL
SELECT 3, PARSE_JSON('{"customer": "Alice Johnson", "amount": 89.99, "order_date": "2024-01-17"}')
UNION ALL
SELECT 4, PARSE_JSON('{"customer": "Charlie Brown", "amount": 450.00, "order_date": "2024-01-18"}')
UNION ALL
SELECT 5, PARSE_JSON('{"customer": "Bob Smith", "amount": 175.25, "order_date": "2024-01-19"}');
Script 1: Bronze β Silver (Parse JSON)β
Run this script to parse JSON from BRONZE and load SILVER:
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, to_date
def main(session: snowpark.Session):
print(" SCRIPT 1: Bronze β Silver (JSON Parsing)")
# Load raw data from BRONZE
bronze_df = session.table("BRONZE_ORDERS")
# Parse JSON and extract fields
silver_df = bronze_df.select(
col("ORDER_ID"),
col("RAW_DATA")["customer"].cast("string").alias("CUSTOMER_NAME"),
col("RAW_DATA")["amount"].cast("decimal(10, 2)").alias("ORDER_AMOUNT"),
to_date(col("RAW_DATA")["order_date"]).alias("ORDER_DATE")
)
print("β Parsed data from BRONZE:")
silver_df.show()
# Write to SILVER table
silver_df.write.save_as_table("SILVER_ORDERS", mode="overwrite")
print("β Data loaded into SILVER_ORDERS table")
return silver_df
Script 2: Silver β Gold (Transform & Aggregate)β
Run this script to aggregate SILVER data and load GOLD:
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, sum as spark_sum, count, min as spark_min, max as spark_max
def main(session: snowpark.Session):
print(" SCRIPT 2: Silver β Gold (Aggregation & Transformation)")
# Load cleaned data from SILVER
silver_df = session.table("SILVER_ORDERS")
# Aggregate by customer
gold_df = silver_df.group_by("CUSTOMER_NAME").agg(
count("ORDER_ID").alias("TOTAL_ORDERS"),
spark_sum("ORDER_AMOUNT").alias("TOTAL_SPENT"),
spark_min("ORDER_DATE").alias("FIRST_ORDER_DATE"),
spark_max("ORDER_DATE").alias("LAST_ORDER_DATE")
)
print("β Aggregated customer data:")
gold_df.show()
# Write to GOLD table
gold_df.write.save_as_table("GOLD_CUSTOMER_SUMMARY", mode="overwrite")
print("β Data loaded into GOLD_CUSTOMER_SUMMARY table")
return gold_df
Pipeline Flow Diagramβ
βββββββββββββββββββ
β BRONZE_ORDERS β Raw ingestion (JSON in VARIANT)
β (Raw Layer) β
ββββββββββ¬βββββββββ
β
Script 1: Parse JSON
β
βΌ
βββββββββββββββββββ
β SILVER_ORDERS β Cleaned structured data
β (Clean Layer) β
ββββββββββ¬βββββββββ
β
Script 2: Aggregate
β
βΌ
βββββββββββββββββββββββββββ
β GOLD_CUSTOMER_SUMMARY β Business metrics
β (Business Layer) β
βββββββββββββββββββββββββββ
β° Automate with Snowflake Tasksβ
Run the medallion pipeline automatically on a schedule using Snowflake Tasks with Snowpark stored procedures.
SQL Setup: Create Stored Proceduresβ
-- Create stored procedure for Bronze β Silver
CREATE OR REPLACE PROCEDURE DEMO_DB.DEMO_SCHEMA.BRONZE_TO_SILVER()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, to_date
def main(session: snowpark.Session):
bronze_df = session.table("BRONZE_ORDERS")
silver_df = bronze_df.select(
col("ORDER_ID"),
col("RAW_DATA")["customer"].cast("string").alias("CUSTOMER_NAME"),
col("RAW_DATA")["amount"].cast("decimal(10, 2)").alias("ORDER_AMOUNT"),
to_date(col("RAW_DATA")["order_date"]).alias("ORDER_DATE")
)
silver_df.write.save_as_table("SILVER_ORDERS", mode="overwrite")
return "β Bronze β Silver completed"
$$;
-- Create stored procedure for Silver β Gold
CREATE OR REPLACE PROCEDURE DEMO_DB.DEMO_SCHEMA.SILVER_TO_GOLD()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, sum as spark_sum, count, min as spark_min, max as spark_max
def main(session: snowpark.Session):
silver_df = session.table("SILVER_ORDERS")
gold_df = silver_df.group_by("CUSTOMER_NAME").agg(
count("ORDER_ID").alias("TOTAL_ORDERS"),
spark_sum("ORDER_AMOUNT").alias("TOTAL_SPENT"),
spark_min("ORDER_DATE").alias("FIRST_ORDER_DATE"),
spark_max("ORDER_DATE").alias("LAST_ORDER_DATE")
)
gold_df.write.save_as_table("GOLD_CUSTOMER_SUMMARY", mode="overwrite")
return "β Silver β Gold completed"
$$;
SQL Setup: Create Tasks with Dependenciesβ
-- Create TASK 1: Bronze β Silver (runs every 10 minutes)
CREATE OR REPLACE TASK DEMO_DB.DEMO_SCHEMA.TASK_BRONZE_TO_SILVER
WAREHOUSE = COMPUTE_WH
SCHEDULE = '10 MINUTE'
AS
CALL DEMO_DB.DEMO_SCHEMA.BRONZE_TO_SILVER();
-- Create TASK 2: Silver β Gold (depends on Task 1)
CREATE OR REPLACE TASK DEMO_DB.DEMO_SCHEMA.TASK_SILVER_TO_GOLD
WAREHOUSE = COMPUTE_WH
AFTER DEMO_DB.DEMO_SCHEMA.TASK_BRONZE_TO_SILVER
AS
CALL DEMO_DB.DEMO_SCHEMA.SILVER_TO_GOLD();
-- Enable root task (child tasks auto-enable)
ALTER TASK DEMO_DB.DEMO_SCHEMA.TASK_BRONZE_TO_SILVER RESUME;
π Key Conceptsβ
| Concept | Details |
|---|---|
| Session | Automatically passed to main(session) in worksheets |
| Lazy Execution | Operations queued until .show() or .collect() |
| DataFrame API | Construct SQL queries using Python methods like .filter(), .select() |
| Stored Procedures | Encapsulate Python logic in Snowflake schema to run via Tasks |
β οΈ Common Mistakesβ
- β String concatenation with
+β Useconcat()or|| - β Creating a new session inside worksheet β Session is provided automatically
- β Hardcoding credentials β Use current session context