How To Build Java-Based Stored Procedures And UDFs In Snowflake Using Snowpark APIs For Scalable, and Efficient Data Workflows

|

Snowflake has revolutionized cloud data warehousing by abstracting infrastructure concerns and enabling advanced analytics at scale. As the platform evolved, it introduced powerful developer features—such as Snowpark—which open the door to writing stored procedures and UDFs in languages like Java, Python, and Scala. This unlocks a whole new level of customization, allowing teams to bring their business logic closer to the data.

In this article, we’ll focus on building Java-based stored procedures and UDFs (User Defined Functions) using Snowpark APIs. We’ll explore how to architect scalable and efficient data workflows that incorporate asynchronous processing and file handling, enabling advanced data transformation and automation.

Overview Of Snowpark Java API

Snowpark is a developer framework that allows writing data applications in Java, Scala, or Python. With Java, you can create stored procedures, scalar UDFs, and table functions that run within the Snowflake engine itself, minimizing data movement and latency.

Key Features:

  • Type-safe APIs for interacting with Snowflake tables and views

  • Push-down optimization

  • Support for procedural logic

  • Secure, scalable execution inside Snowflake’s compute engine

Setting Up Your Java Project For Snowpark

Before writing your first stored procedure or UDF, you need to set up a Java Maven project with the Snowpark dependency.

pom.xml Example:

xml
<project xmlns="http://maven.apache.org/POM/4.0.0" ...>
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>snowpark-java-demo</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>com.snowflake</groupId>
<artifactId>snowpark</artifactId>
<version>1.11.0</version>
</dependency>
</dependencies>
</project>

Directory Structure:

css
src/
├── main/
│ ├── java/
│ │ └── com/example/
│ │ └── MyProcedure.java
│ └── resources/

Once the project is set up, you can write your Snowflake logic in Java and compile it to a JAR file for deployment.

Creating a Simple Java-Based Stored Procedure

Here’s an example of a stored procedure that reads from a table, transforms data, and writes the result to another table.

java

package com.example;

import com.snowflake.snowpark.*;
import com.snowflake.snowpark.types.*;
import java.util.Map;

public class MyProcedure {
public static void run(Session session, Map<String, String> args) {
DataFrame df = session.table(“INPUT_TABLE”);

DataFrame transformed = df.filter(Functions.col(“amount”).gt(1000))
.withColumn(“status”, Functions.lit(“high_value”));

transformed.write().mode(SaveMode.Overwrite).saveAsTable(“OUTPUT_TABLE”);
}
}

To deploy this stored procedure:

  1. Package the class as a JAR.

  2. Upload the JAR to a Snowflake stage.

  3. Register and call the procedure in SQL:

sql
CREATE OR REPLACE PROCEDURE my_java_proc()
RETURNS STRING
LANGUAGE JAVA
RUNTIME_VERSION = '11'
PACKAGES = ('com.snowflake:snowpark:1.11.0')
IMPORTS = ('@my_stage/my_procedure.jar')
HANDLER = 'com.example.MyProcedure.run';

CALL my_java_proc();

Implementing Java UDFs With Snowpark

Java UDFs allow you to run custom Java logic on each row of a dataset. Here’s a simple UDF example that masks emails.

java

package com.example;

import java.util.function.Function;

public class MaskEmailUdf implements Function<String, String> {
@Override
public String apply(String email) {
int atIndex = email.indexOf(“@”);
if (atIndex <= 1) return email;
return email.charAt(0) + “*****” + email.substring(atIndex);
}
}

Registering the UDF:

sql
CREATE OR REPLACE FUNCTION mask_email(email STRING)
RETURNS STRING
LANGUAGE JAVA
RUNTIME_VERSION = '11'
IMPORTS = ('@my_stage/mask_email_udf.jar')
HANDLER = 'com.example.MaskEmailUdf';

SELECT mask_email(‘john.doe@example.com’);

Asynchronous Processing In Java Procedures

Snowflake now supports asynchronous execution via its task-based scheduling. While Java doesn’t have built-in async constructs like JavaScript’s Promise, you can simulate async flows using tasks and control tables.

Step-by-step approach:

  1. Your Java stored procedure inserts a job entry into a control table.

  2. A Snowflake task polls this table and calls another procedure to process the job.

  3. The task and worker logic are completely isolated.

Control Table Example:

sql
CREATE OR REPLACE TABLE job_control (
job_id STRING,
status STRING,
payload STRING
);

Procedure that adds a job:

java
public class JobSubmitter {
public static void run(Session session, Map<String, String> args) {
String jobId = java.util.UUID.randomUUID().toString();
session.sql("INSERT INTO job_control VALUES (?, 'PENDING', ?)")
.bind(jobId, args.get("payload"))
.collect();
}
}

Worker Procedure (executed by a task):

java
public class JobWorker {
public static void run(Session session, Map<String, String> args) {
DataFrame jobs = session.sql("SELECT * FROM job_control WHERE status = 'PENDING'");
for (Row row : jobs.collectAsList()) {
String jobId = row.getString(0);
String payload = row.getString(2);
// Process payload...
session.sql("UPDATE job_control SET status = 'COMPLETED' WHERE job_id = ?")
.bind(jobId)
.collect();
}
}
}

Schedule the worker:

sql
CREATE OR REPLACE TASK job_worker_task
WAREHOUSE = compute_wh
SCHEDULE = '1 MINUTE'
AS
CALL job_worker_proc();

Java File Handling With Snowflake Stages

Java-based stored procedures can access external files using Snowflake stages. This is ideal for parsing CSVs, reading config files, or writing logs.

Reading A File From A Stage:

java
public class FileReaderProc {
public static void run(Session session, Map<String, String> args) throws Exception {
String filePath = "@my_stage/config.json";
InputStream is = session.getFileStream(filePath);
BufferedReader reader = new BufferedReader(new InputStreamReader(is));

StringBuilder content = new StringBuilder();
String line;
while ((line = reader.readLine()) != null) {
content.append(line);
}
session.sql(“INSERT INTO logs VALUES (?)”)
.bind(content.toString())
.collect();
}
}

Upload File To Stage (CLI or UI):

bash
snowsql -q "PUT file://config.json @my_stage AUTO_COMPRESS=FALSE"

This enables configuration-driven logic or one-time data ingestion via external files.

Combining UDFs, Procedures, Async, and File Handling

Here’s a practical architecture using all the above:

  1. File Upload Trigger: External app uploads a file to @upload_stage.

  2. Submit Job Procedure: Inserts a job entry into job_control with file metadata.

  3. Async Worker Task: Picks up job, reads the file, processes each row via a UDF, and writes transformed data to a table.

  4. Final Logging Procedure: Updates job status and logs execution metadata.

This pattern enables a fully serverless, scalable, and auditable pipeline using only Snowflake components and Java logic.

Best Practices

  • Minimize DataFrame to Local Conversion: Use .collect() sparingly to avoid pulling large datasets to the Java VM.

  • Use Snowflake Tasks for Orchestration: Schedule polling workers to simulate event-driven flows.

  • Leverage Stages For File Management: External stages with Snowflake’s file APIs streamline configuration and bulk loading.

  • Monitor Using Query History: All Java procedure invocations appear in query history for easy tracing.

Conclusion

Building Java-based stored procedures and UDFs in Snowflake with the Snowpark API unlocks high-performance, maintainable, and scalable data workflows. You can co-locate your business logic with your data, reduce latency, and use Java’s full programming capabilities—including error handling, file processing, and control flow.

When combined with Snowflake’s features like tasks, streams, and stages, this approach enables:

  • Asynchronous, event-driven processing

  • Custom data transformation pipelines

  • External configuration and file ingestion

  • Clean separation of orchestration and execution logic

This allows data teams to build truly powerful ETL/ELT systems entirely inside Snowflake’s secure and scalable environment—with the added flexibility of modern Java.

As Snowflake continues to mature as a data development platform, now is the time to invest in reusable, modular, Java-based data workflows. Whether you’re transforming billions of records, running compliance checks, or implementing ML scoring pipelines, Java + Snowpark can handle it all—cleanly and efficiently.