Springboot Integration With Snowflake

Snowflake recently rebranded its platform as Unistore, and the general availability of Unistore was announced at the annual developer conference in November 2024. Unistore introduces new capabilities that allow users to integrate both analytical and transactional data within a single platform. Previously, the common approach for handling data in Snowflake involved moving it from different sources using ETL (Extract, Transform, Load) processes or Snowpipes. However, with the introduction of Unistore, it is now possible to connect applications directly to Snowflake for transactional data operations as well. In this article, we will explore how to connect a Spring Boot application to Snowflake and use JPA (Java Persistence API) to interact with Snowflake database tables via standard JPA repositories.

Snowflake Dependencies

1. Snowflake JDBC Driver

Snowflake provides a JDBC driver to connect your Java application to the Snowflake database. You need to include the following dependency in your pom.xml

<dependency>
    <groupId>net.snowflake</groupId>
    <artifactId>snowflake-jdbc</artifactId>
    <version>3.21.0</version>
</dependency>        

2. Hibernate Dialect

The Hibernate dialect is responsible for translating Hibernate's generic HQL (Hibernate Query Language) and criteria queries into the native SQL dialect of the target database. Currently, Hibernate does not include a dialect for Snowflake. You can refer to the Hibernate documentation for available dialects: Hibernate Dialect Documentation.

However, a Snowflake dialect is available in the Snowflake GitHub repository at: Snowflake Hibernate Dialect.

Steps to Use the Snowflake Dialect:

• Check out the dialect code from the above repository.
• Build it locally using the command:

    mvn clean package -Dmaven.test.skip=true         

• Install the JAR file into your local .m2 folder using:

./mvnw org.apache.maven.plugins:maven-install-plugin:3.1.1:install-file -Dfile=./target/snowflake-hibernate.jar -DpomFile=./pom.xml        

• After Installation, you can include it as a dependency in your project.

<dependency>
    <groupId>net.snowflake</groupId>
    <artifactId>snowflake-hibernate</artifactId>
    <version>0.0.2-SNAPSHOT</version>
</dependency>        

2. Spring Boot Configuration

Your application.yml should be configured as follows

server:
  port: 5000
  jpa:
    properties:
      hibernate:
        dialect: net.snowflake.hibernate.dialect.SnowflakeDialect
  datasource:
    password: <Your Snowflake Account Password>
    driver-class-name: net.snowflake.client.jdbc.SnowflakeDriver
    username: <Your Snowflake Account Username>
    url: jdbc:snowflake://<Your Snowflake Account or Server URL>/?useSSL=false&db=DEMO&schema=USER_MANAGEMENT&warehouse=COMPUTE_WH        

The account server URL you can obtain from the Snowflake dashboard refer to the image below

The warehouse name can be obtained from the Snowflake Query workbench, refer to the image below

3. Snowflake Tables

You can create tables directly in Snowflake using the Snowflake query dashboard. I prefer using the DBWeaver SQL client, which can connect to multiple databases, including Snowflake.

The connection properties in DBWeaver will look something like this,

Once connected to Snowflake Database, open SQL editor from DBWeaver and you can create tables with standard SQL syntax such as.

CREATE TABLE person (
  id BIGINT PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL
);

CREATE SEQUENCE person_sequence START = 1 INCREMENT = 1;        

While Snowflake supports auto-increment columns for primary keys similar to PostgreSQL, there may be challenges when working with JPA and the provided dialect. Using sequences is an effective alternative for systems with a high volume of inserts, as they provide better performance.

With the setup described above, you can interact with the Snowflake database using Spring Boot. However, there are some important takeaways to consider:

1. Constraints on Standard Tables: While Snowflake standard tables allow you to define constraints, such as unique keys and foreign keys, they do not enforce these constraints. For example, if you create a unique constraint on the email column, Snowflake will still accept multiple records with the same email address without raising any errors.
2. Using Snowflake as a Transactional Store: You might wonder how Snowflake can be used as a transactional store if constraints are not enforced. This is where Snowflake's new feature comes into play: instead of using standard tables, you should define hybrid tables. The syntax for creating these tables is the same; you simply need to include the HYBRID keyword.
3. Note that hybrid tables are not supported in trial accounts.
4. Enforcing Constraints with Hybrid Tables: Hybrid tables enforce constraints at the time of creation, meaning you can define unique keys and other constraints. However, keep in mind that once a hybrid table is created, you cannot alter it to add constraints later.
5. Data Loading with Snowpipe: One of the key features for loading data quickly into Snowflake tables is Snowpipe. However, as of now, Snowpipe does not support hybrid tables, which may affect your data-loading strategies.

Code for above implementation can be found at https://github.com/dipurane/snowflake-springboot