Model scoring with Snowflake user-defined functions
Overview
Snowflake user-defined functions (UDFs) provide flexibility and customization options for integrating model scoring into Redpoint Interaction (RPI) workflows.
UDFs allow you to define custom functions that encapsulate your model scoring logic and seamlessly integrate them into RPI attribute calculations. This allows for real-time scoring of models based on the input data available within RPI, enabling dynamic decision-making and personalized experiences.
Some key advantages of using UDFs for on-demand model scoring in RPI include:
Flexibility: UDFs provide a flexible way to incorporate complex model scoring logic into RPI attributes, allowing users to tailor the scoring process to their specific requirements.
Reusability: Once defined, you can reuse UDFs across multiple attributes and RPI workflows, promoting code reusability and maintainability.
Performance: UDFs enable on-demand model scoring, allowing for real-time processing of input data and generating scores as needed, optimizing performance and resource utilization.
Integration: By using UDFs, you can seamlessly integrate model scoring into your existing RPI workflows, ensuring a smooth and cohesive experience.
To implement UDFs for on-demand model scoring in RPI, follow these general steps:
Define the UDF: Write the function that encapsulates the model scoring logic, specifying the input parameters and return values.
Register the UDF: Register the UDF within the RPI environment, making it available for use in attribute calculations.
Configure the attribute: Configure the RPI attribute to invoke the UDF, passing the necessary input data and retrieving the scored result.
Test and validate: Thoroughly test and validate the UDF and the associated RPI attribute to ensure accurate and reliable model scoring.
By following these steps and leveraging the power of UDFs, you can effectively incorporate on-demand model scoring into your RPI workflows, enabling personalized and data-driven experiences for their users.
Define the UDF
We’ll use the data and predictive model provided in the Snowflake quickstart Getting Started with Snowpark for Machine Learning. Here is an overview of the data and model:
In this use case you will build a binary model based on the ‘Machine Predictive Maintenance Classification' dataset from Kaggle. We supplement this dataset with data from the Snowflake data marketplace.
The use case uses information related to machine diagnostics (torque, rotational speed) and environmental features (air temperature, humidity) to predict the likelihood of a failure.
The result of the quickstart exercise is a UDF in Snowflake named PREDICT_FAILURE
:
![](../__attachments/4053139457/image-20231211-162618.png?inst-v=c3007bf4-8f91-43d3-9633-90859273c945)
Here we see the execution of the model UDF using SQL in a Snowflake worksheet. Arguments to the UDF come from the table maintenance_hum
.
![](../__attachments/4053139457/image-20231211-162859.png?inst-v=c3007bf4-8f91-43d3-9633-90859273c945)
Next, we’ll use this UDF from within RPI.
Call the model UDF in RPI
The setup involves:
Configuring Snowflake as an AuxDB in RPI, pointing to the database with the model input table.
Running a catalog sync to read the table definitions into RPI.
Creating a SQL Custom Attribute in the catalog which executes the UDF.
![](../__attachments/4053139457/image-20231211-163418.png?inst-v=c3007bf4-8f91-43d3-9633-90859273c945)
With the custom SQL attribute created, next we’ll use the attribute in a selection rule.
![](../__attachments/4053139457/image-20231211-163617.png?inst-v=c3007bf4-8f91-43d3-9633-90859273c945)
The rule executed without error.
Test: Performance impact of UDF attribute
To get a rough sense of the impact of a calling a model backed UDF from RPI, we’ll run some tests against a 100K record set where we…
Run a selection rule (SR) and then audience using a non-UDF attribute
Repeat using the UDF attribute
Also run a file export interaction exporting all attributes except the UDF attribute
Repeat exporting all attributes including the UDF attribute
We’ll compare run times between the two to see what kind of impact the UDF attribute adds. This is on RPI running on an AWS EC2 instance, and the primary warehouse is Snowflake.
Description | Runtime |
---|---|
SR without Model UDF | < 1 second |
SR with Model UDF | 2 seconds |
Audience without Model UDF | 2 seconds |
Audience with Model UDF | 4 seconds |
Interaction (file export) without Model UDF | 16 seconds |
Interaction (file export) with Model UDF | 21 seconds |