Skip to main content

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:

  1. Switch to DEMO_DB.DEMO_SCHEMA context
  2. Create a Python Worksheet in Snowflake
  3. Copy the script above
  4. Click Run β€” session is passed automatically
  5. 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​

ConceptDetails
SessionAutomatically passed to main(session) in worksheets
Lazy ExecutionOperations queued until .show() or .collect()
DataFrame APIConstruct SQL queries using Python methods like .filter(), .select()
Stored ProceduresEncapsulate Python logic in Snowflake schema to run via Tasks

⚠️ Common Mistakes​

  • ❌ String concatenation with + β†’ Use concat() or ||
  • ❌ Creating a new session inside worksheet β†’ Session is provided automatically
  • ❌ Hardcoding credentials β†’ Use current session context