About this lab
Six months after Part 1, Copperleaf Insurance receives a million broker claims a month, and the Python that cleans them still lives as strings inside SQL worksheets. Last week someone edited a fraud rule in a worksheet, nobody reviewed it, and nobody could tell which version had scored which claim. The claims director's verdict: this code is production software now, so treat it that way.
You move every rule into a versioned Python package with unit tests, then test the Snowpark pipeline itself on your laptop with the Snowpark local testing framework, no account needed. A CI-style script deploys the code with the Snowflake CLI as a key-pair service user, but only after every test passes, and integration tests prove the deployed functions return exactly what your local code returns on a million real rows. You benchmark row UDFs against vectorized (pandas) UDFs and measure how Snowflake batches rows, build a vectorized UDTF that spots policies claiming again and again, and write a Python UDAF that runs Benford's law over each broker's amounts.
The production pipeline procedure quarantines bad claims instead of guessing, survives reruns and resubmissions, and writes logs and trace spans to a dedicated event table you query with SQL. You give a claims adjuster the least privilege that works and see owner's rights and caller's rights procedures behave differently, then watch a Monte Carlo reserve simulation run out of memory on a standard warehouse, succeed on a Snowpark-optimized one, and finally fit on the small warehouse after a code fix. Everything runs on a Snowflake trial account.
Lab details
- Level: Advanced
- Cloud: Snowflake
- Domain: Data Engineering
- Duration: 150 minutes
What you will learn
- Package Snowpark UDF, UDTF and procedure code as a versioned Python module and test it with pytest and the Snowpark local testing framework
- Deploy with the Snowflake CLI from a CI-style script as a key-pair service user, gated by tests and verified by integration tests
- Benchmark row vs vectorized UDFs, measure Snowflake's batch sizes, and write a vectorized UDTF and a Python UDAF
- Build an idempotent pipeline procedure that quarantines bad records and emits logs and trace spans to an event table
- Apply least privilege and explain owner's rights vs caller's rights procedures with a real role
- Decide between a Snowpark-optimized warehouse and a code fix for a memory-heavy procedure
Concepts covered
- Code as a versioned package, not worksheet strings
- Unit, local and integration testing for Snowpark
- CI/CD with the Snowflake CLI and key-pair service users
- Vectorized UDFs and batch sizing
- Partitioned vectorized UDTFs
- User-defined aggregate functions
- Idempotent pipelines and quarantine
- Observability with event tables
- Owner's vs caller's rights and least privilege
- Snowpark-optimized warehouses and memory
Technologies used
- Snowpark Python + local testing
- Snowflake CLI
- Vectorized UDFs and UDTFs
- Python UDAF
- Event tables, logging and tracing
- Access control and warehouses
Prerequisites
- Part 1 of this lab, or equivalent experience writing Python UDFs, UDTFs and Snowpark stored procedures (Part 1 is not required to run this lab)
- Comfortable with Python (functions, classes, pandas basics) and SQL
- Python 3.11 or newer on your laptop, and a terminal
- A Snowflake account (Enterprise trial works) where you can use the ACCOUNTADMIN role
- About 150 minutes; the lab uses an X-Small warehouse plus about one minute of a Medium Snowpark-optimized warehouse
Topics
- Snowflake
- Snowpark
- Python
- UDF
- UDTF
- UDAF
- Vectorized UDF
- Stored Procedures
- Snowflake CLI
- CI/CD
- pytest
- Local Testing
- Event Tables
- Logging
- Tracing
- RBAC
- Snowpark-Optimized Warehouse
- Insurance
- Fraud Detection
