About this lab
Copperleaf Insurance receives claims from brokers in five countries, and every broker formats them differently: policy numbers in four styles, incident dates in five, amounts in local currency, and free-text adjuster notes. The claims team needs one clean, scored claims table that refreshes itself, plus a queue of claims that need a person to look at them.
In this lab you write a SQL UDF for a simple severity rule, a Python UDF that normalizes policy numbers, a Python UDF that uses the python-dateutil package to parse every date format, and a Python UDTF that scans adjuster notes and returns one row per fraud red flag. You then combine all four in a Snowpark Python stored procedure that processes only new claims using a watermark, merges resubmitted claims instead of duplicating them, rescoring their fraud flags, and audits every run.
You prove the procedure is idempotent, feed it a second batch including a resubmission, schedule it with a task, and finish with the review queue the claims team works from.
Lab details
- Level: Intermediate
- Cloud: Snowflake
- Domain: Data Engineering
- Duration: 90 minutes
What you will learn
- Write a SQL UDF and a Python UDF and know when to choose each
- Use a third-party package from the Snowflake Anaconda channel inside a Python UDF
- Build a Python UDTF that returns many rows per input row and join it to a table
- Write an incremental Snowpark stored procedure with a watermark, MERGE, and an audit log
- Prove the procedure is idempotent and handles resubmitted records
- Schedule the procedure with a task and verify its runs
Concepts covered
- Scalar vs tabular functions
- SQL vs Python UDFs
- Python packages in Snowflake
- Snowpark DataFrames
- Incremental processing with watermarks
- Idempotent jobs
- Task scheduling
Technologies used
- SQL UDFs
- Python UDFs
- Python UDTFs
- Snowpark Python
- Stored Procedures
- Snowflake Tasks
Prerequisites
- Basic SQL (SELECT, JOIN, GROUP BY)
- Basic Python (functions, classes, loops)
- A Snowflake trial account with the ACCOUNTADMIN role
Topics
- Snowflake
- Snowpark
- Python
- UDF
- UDTF
- Stored Procedures
- Tasks
- Data Quality
- SQL
