Building ETL Pipelines with Snowpark
While querying data interactively in a Jupyter Notebook is useful for Data Scientists, Data Engineers need to build robust, automated pipelines. Snowpark allows you to write end-to-end ELT (Extract, Load, Transform) pipelines entirely in Python, utilizing Snowflake's compute engine.
Topics Covered
- Designing a Multi-Step Pipeline
- Extract: Reading External Files (CSV/JSON/Parquet)
- Transform: Applying Business Logic
- Load: Writing to Target Tables (Append vs Overwrite)
- Logging Pipeline Metadata
1. The Multi-Step Pipeline Architecture
A typical ELT pipeline in Snowpark involves three logical functions:
extract_data(): Pulls raw data from an internal or external stage.transform_data(): Cleans, joins, and aggregates the data.load_data(): Writes the final DataFrame into a target table and logs the execution.
By wrapping these steps in a Python class or a master function, you make the pipeline reusable and testable.
2. Extract: Reading Files Directly
You don't need a COPY INTO SQL statement to load files. Snowpark provides a native reader to read CSV, JSON, or Parquet files directly from a Snowflake stage into a DataFrame.
Let's assume an HR department in Bengaluru drops employee performance files into an internal stage named @HR_RAW_STAGE.
from snowflake.snowpark.types import StructType, StructField, IntegerType, StringType, FloatType
def extract_data(session):
"""Reads raw CSV files from the HR stage into a DataFrame."""
# Define the schema of the CSV file
schema = StructType([
StructField("EMP_ID", IntegerType()),
StructField("NAME", StringType()),
StructField("RATING", FloatType()),
StructField("CITY", StringType())
])
# Read the CSV from the stage
df_raw = session.read.schema(schema) \
.option("skip_header", 1) \
.csv("@HR_RAW_STAGE/employee_ratings_2023.csv")
return df_raw
3. Transform: Applying Business Logic
Now that we have the raw DataFrame, we apply our business logic. For example, filtering out invalid ratings and standardizing city names.
from snowflake.snowpark.functions import col, initcap, trim, when
def transform_data(df):
"""Applies cleaning and transformation rules to the DataFrame."""
df_clean = df.filter(
(col("RATING") >= 1.0) & (col("RATING") <= 5.0) # Drop invalid ratings
).with_column(
"CITY", initcap(trim(col("CITY"))) # Standardize city formatting
).with_column(
"BONUS_ELIGIBLE",
when(col("RATING") >= 4.5, True).otherwise(False) # Add a new derived column
)
return df_clean
4. Load: Writing to Target Tables
Snowpark offers multiple mode options when writing data to tables:
overwrite: Drops the target table and creates a new one. (Dangerous for historical data).append: Inserts the new rows into the existing table. (Standard for incremental loads).errorifexists: Fails the pipeline if the target table already exists.
def load_data(df, target_table_name):
"""Writes the DataFrame to the target table and returns the row count."""
# Save the data
df.write.mode("append").save_as_table(target_table_name)
# Return the count of rows processed for logging
return df.count()
5. Orchestrating and Logging Pipeline Metadata
A professional pipeline must log its execution. If a job fails at 3:00 AM, the DataOps team needs to know exactly what happened.
We will create a master function that orchestrates the Extract, Transform, and Load steps, and logs the metadata to a control table.
import time
from datetime import datetime
def run_hr_pipeline(session):
start_time = time.time()
status = "SUCCESS"
rows_processed = 0
error_msg = None
try:
print("Starting Extract...")
df_raw = extract_data(session)
print("Starting Transform...")
df_clean = transform_data(df_raw)
print("Starting Load...")
rows_processed = load_data(df_clean, "SILVER_HR_RATINGS")
except Exception as e:
status = "FAILED"
error_msg = str(e)
print(f"Pipeline Failed: {error_msg}")
finally:
# Calculate duration
duration_sec = round(time.time() - start_time, 2)
# Log the metadata to the control table
log_df = session.create_dataframe([[
"HR_RATINGS_PIPELINE",
datetime.now(),
status,
rows_processed,
duration_sec,
error_msg
]], schema=["PIPELINE_NAME", "RUN_TIME", "STATUS", "ROWS_PROCESSED", "DURATION_SEC", "ERROR_MSG"])
log_df.write.mode("append").save_as_table("PIPELINE_AUDIT_LOGS")
print(f"Pipeline finished with status: {status}")
# Execute the pipeline
# run_hr_pipeline(session)
Summary
- Snowpark allows you to read external files (CSV, JSON, Parquet) from stages directly into DataFrames using
session.read. - Pipelines should be modularized into Extract, Transform, and Load functions for testability.
- Use
.write.mode("append").save_as_table()to securely write data into production tables. - Always implement a
try/except/finallyblock to capture errors and log execution metadata (duration, rows processed) into an audit table.