Snowflake AI Data Connector

Introduction

The Snowflake AI Data Connector allows you to use data from Snowflake in your Mendix application and enrich your app with the AI capabilities that Snowflake provides.

Typical Use Cases

The Snowflake AI Data Connector supports the following:

• Authentication:

  • Authentication with an RSA key pair according to PKCS #8 standard
  • Authentication with OAUTH through an OIDC provider

• Functionality: Execute SQL statements on Snowflake via REST calls from your Mendix application. These statements allow you to perform the following tasks:

  • Read data from Snowflake.

  • Write data to Snowflake.

  • Trigger the following Snowflake Cortex ML functions:

    • Forecasting – Predicts future metric values from past trends in time-series data.
    • Anomaly Detection – Flags metric values that differ from typical expectations.
    • CLASSIFY_TEXT – Given a piece of text, classifies it into one of the categories that you define.
    • EXTRACT_ANSWER – Given a question and unstructured data, returns the answer to the question if it can be found in the data.
    • PARSE_DOCUMENT – Given an internal or external stage with documents, returns an object that contains extracted text content using OCR mode, or the extracted text and layout elements using LAYOUT mode.
    • SENTIMENT – Returns a sentiment score, from -1 to 1, representing the detected positive or negative sentiment of the given text.
    • SUMMARIZE – Returns a summary of the given text.
    • TRANSLATE – Translates given text from any supported language to any other.
    • EMBED_TEXT_768 – Given a piece of text, returns a vector embedding of 768 dimensions that represents that text.
    • EMBED_TEXT_1024 – Given a piece of text, returns a vector embedding of 1024 dimensions that represents that text.

  • Use Snowflake Cortex Analyst – This Snowflake Cortex feature is used to get information/insights out of structured data sets using natural language instead of sql.

  • Synchronous execution of calls

  • Query your Cortex Search services

For more use cases and examples for Snowflake Cortex LLM functions, written by the Head of Snowflake Tech Consulting, see Karthik S Raman's Medium profile.

Prerequisites

The Snowflake AI Data Connector requires Mendix Studio Pro version 9.18.0 or above.

To use the Snowflake AI Data Connector, you must also install and configure the following modules from the Mendix marketplace:

• Community Commons – This module is a required dependency for the Snowflake AI Data Connector.
• Encryption – This module is a required dependency for the Snowflake AI Data Connector. The EncryptionKey constant must be set up in your application settings.
• From version 4.0.0 and up the Snowflake AI Data Connector is no longer dependent on the GenAI Commons module.

Licensing and Cost

This connector is available as a free download from the Mendix Marketplace, but the services in Snowflake to which is connects may incur a usage cost. For more information, refer to the Snowflake documentation.

Depending on your use case, your deployment environment, and the type of app that you want to build, you may also need a license for your Mendix app. For more information, refer to Licensing Apps.

Installation

Follow the instructions in How to Use Marketplace Content in Studio Pro to import the Snowflake AI Data Connector into your app.

Configuration

After you install the connector, you can find it in the App Explorer, in the Marketplace modules section. The connector provides a domain model and several activities that you can use.

Configuring Snowflake Authentication

To use the capabilities of Snowflake in a Mendix app with the Snowflake AI Data Connector, you must use either OAUTH authentication or RSA key-pair authentication.

Configuring OAUTH Authentication

To find out how configure the OAUTH Authentication method, see Role-based Access Control.

When using an OAuth token to authenticate REST calls, use the JWT_GetCreate microflow from the Utils folder to get or create a JWT object and set your OAuth token and expiration date on the Token and ExpirationDate attributes of the returned JWT object. In the POST_v1_ExecuteStatement and CortexAnalyst operations, the JWT is retrieved from the ConnectionDetails and used for authentication. Be aware that GET_v1_RetrievePartition should be edited when using OAuth for authentication. Further instructions on what to change are included in the microflow annotations.

Configuring Key-Pair Authentication in Snowflake

To configure RSA key-pair authentication for your account in Snowflake, perform the following steps:

1. Generate the private key.
2. Generate a public key.
3. Assign the public key to a Snowflake user.

For more details about each step, refer to the official Snowflake documentation.

Setting up the Key-Pair Authentication in a Mendix App

To make it easier for users to configure the key-pair authentication in a Mendix app, the Snowflake AI Data Connector includes pages and microflows that you can simply drag and drop them into your own modules.

To configure the authentication, perform the following steps:

1. In the App Explorer, under the SnowflakeAIDataConnector section, find the SNIPPET_SnowflakeConfiguration snippet and drag and drop it into a page in your module.

   

2. Assign the module role SnowflakeAIDataConnector.Administrator to the application role that will be used to set up the configuration, so that the added logic will be usable.

3. Run the application and go to the page where you added the snippet.

4. Click New.

5. On the Connection details page, fill out all fields with the details of your Snowflake account.

6. In the Snowflake console, click Copy account URL. This URL will be used as the Account URL parameter for Connection details.

   

7. In the Snowflake console, click Copy account identifier. Before using it inside Mendix, you must replace the . separator with a -. The final string will be used as the Account identifier parameter for the Connection details.

   

8. Enter the passphrase and upload your private key file in .p8 format.

   

9. Click Save to save the connection, or click Save and test connection to generate a JSON Web Token (JWT) and validate your connection.

Configuring a Microflow for the Service

After you configure the authentication for Snowflake, you can implement the functions of the connector by using the provided activities in microflows. An extended microflow has been implemented and added to the Snowflake AI Data Connector as an example for users that would like to retrieve a list of objects from an existing table in Snowflake. In the SnowflakeAIDataConnector module, see the EXAMPLE_ExecuteStatement microflow and the ExampleObject domain model entity to learn how the TransformResponsesToMxObjects operation can be used to easily convert the data received in HttpResponse objects into Mendix objects.

Asynchronous Query Execution

At present, the Snowflake Snowflake AI Data Connector does not provide any out of the box operation microflows for making asynchronous calls. However, you can still configure asynchronous calls manually using the connector.

To set this up:

1. Locate the POST_v1_ExecuteStatement microflow.
2. Open the Call REST (POST) action within this microflow and examine the Location field. Note a query parameter named async that is set to false.
3. Duplicate the Call REST (POST) action, modify the async query parameter to true, and use this as a basis to build your custom asynchronous microflows.

Your custom microflows should consist of the following:

• A request call microflow – Sends the request and returns the request ID.
• A polling microflow – Uses the request ID to check if the response is available, and then returns the response once it is ready.

This approach allows you to achieve asynchronous behavior while leveraging the Snowflake AI Data Connector.

Programmatic Triggering of Ingestion Jobs

The Snowflake AI Data Connector can be used to trigger data ingestion jobs with an SQL statement. For more information, see Mendix Data Loader: Programmatically Triggering an Ingestion Job From a Mendix App.

Binding Variables in Snowflake using the Snowflake AI Data Connector

When executing SQL statements from Mendix into Snowflake, binding variables is a critical concept that improves security, performance, and maintainability of your database interactions.

What Is Variable Binding?

Instead of putting values directly into your SQL, you can use ? placeholders and provide the values separately. This is called binding. It works in a similar way to using parameters in a Mendix microflow, that is, it keeps your logic and data separate.

In Mendix, you do this by creating a Statement entity with a SQL query that includes ?. Then, you add Binding entities to provide the values for those placeholders.

Each Binding is linked to the Statement. The order of the bindings matters — the first binding fills the first ?, the second fills the second ?, and so on.

Make sure the number of bindings matches the number of ? placeholders in your SQL. Otherwise, the execution will fail due to mismatched parameters.

Benefits of Variable Binding

Binding variables provides the following benefits:

• Security - Prevents SQL injection attacks by treating values as data, not code.
• Reusability - You can reuse the same SQL template with different values.
• Performance - Snowflake can cache and reuse execution plans for bound SQL.
• Clarity - Keeps SQL readable and separates logic from values—easier to debug and maintain.

Sample SQL for Variable Binding

The following is an example of unbound SQL:

INSERT INTO db.sch.Persons (PersonID, LastName, FirstName, Address, City, IsActiveUser) 
VALUES (12, 'Doe', 'Jane', 'Main Street 123', 'Metroville', true);

The following is an example of bound SQL using ? placeholders:

INSERT INTO db.sch.Persons (PersonID, LastName, FirstName, Address, City, IsActiveUser)
VALUES (?, ?, ?, ?, ?, ?);

The following values are provided separately in a bindings structure:

"bindings": {
    "1": { "type": "FIXED", "value": "12" },
    "2": { "type": "TEXT", "value": "Doe" },
    "3": { "type": "TEXT", "value": "Jane" },
    "4": { "type": "TEXT", "value": "Main Street 123" },
    "5": { "type": "TEXT", "value": "Metroville" },
    "6": { "type": "BOOLEAN", "value": "true" }
}

Mendix Attribute Type to Snowflake Data Type Mapping

This table maps Mendix attribute types to Snowflake data types, along with common binding types and usage notes.

Proxy Usage

Since version 3.1.0, the Snowflake AI Data Connector supports using a proxy to make your REST calls. You can use the new ProxySettings entity to enable a proxy by performing the following steps:

1. Create a ProxySettings object.
2. Set the Host and Port attributes to the host of the proxy and the port on which the proxy is listening.
3. Optional: Set the User and Password to authenticate against your proxy.
4. Associate the ProxySettings to your ConnectionDetails object (when using the POST_v1_ExecuteStatement operation), or the CortexConnection object (when using the CortexAnalyst operation).

Once configured, the connector automatically detects the ProxySettings object and routes REST requests through the specified proxy.

Technical Reference

To help you work with the Snowflake AI Data Connector, the following sections of this document list the available activities that you can use in your application.

Activities

Activities define the actions that are executed in a microflow or a nanoflow.

ExecuteStatement

The ExecuteStatement activity allows you to execute a command in Snowflake using the SQL statement and the configuration details given in a Statement and ConfigurationDetails objects and returns a list of HttpResponse objects. Make sure that a JWT object containing your KEYPAIR_JWT or OAuth token is associated to your connection details before using the ExecuteStatement activity.

The input and output for this service are shown in the table below:

TransformResponsesToMxObjects

The TransformResponsesToMxObjects activity allows you to transform the list of HttpResponse objects into objects of the entity of your choice.

It requires a list of HttpResponse objects and the entity of the objects that you would like to create with the received information. It returns a list of Mendix objects of the entity given in the input.

To showcase this, we have created an example entity in the domain model of the connector:

This entity is only an example. You must review properties such as the naming of the attributes, what datatypes they have or in which order they are added, and configure them according to your needs. This information is important after the entity has been decided on and the data will be received from a Snowflake account. The order in which you receive the columns from a Snowflake table, the name of these columns as well as the datatypes of these values must match the entity that you have selected.

For example, a table in Snowflake may contain multiple columns named column1, column2,.......,column8. To retrieve data from the column, create ExampleObject objects and display them on a page, you must execute an SQL statement that would retrieve the table columns with the name of my attributes and have the same datatypes. After making sure that the datatypes in Snowflake and Mendix match, you can execute a statement such as the one shown in the following example:

SELECT 
     column1 as ATTR_TXT,
     column2 as ATTR_INT,
     column3 as ATTR_LONG,
     column4 as ATTR_BOOL,
     column5 as ATTR_DECI,
     column6 as ATTR_ENUM
FROM your_table 

This statement returns data from a Snowflake table with the columns named as specified with the as **NewColumnName**" part of each line. If the attribute names, datatypes and their order match, the TransformResponsesToMxObjects activity automatically converts the retrieved data into Mendix objects.

Configuring Snowflake Cortex Analyst

Snowflake Cortex Analyst is a fully-managed, LLM-powered Snowflake Cortex feature that helps you create applications capable of reliably answering business questions based on your structured data in Snowflake.

Prerequisites

• Make sure that you have access to Cortex Analyst. For more information, refer to the Snowflake Cortex Analyst documentation.
• Create the semantic model for Cortex Analyst. For more information, refer to Creating Semantic Models for Snowflake Cortex Analyst in the Snowflake Cortex Analyst documentation.
• Set up one of the following supported authentication methods for Cortex Analyst:
  • OAUTH
  • KEYPAIR_JWT

Configuration

To configure your Mendix app for Snowflake Cortex Analyst, perform the following steps:

1. Create a microflow and retrieve your ConnectionDetails object.
2. When using KEYPAIR_JWT as your authentication type use the Generate JWT action from the Toolbox to generate a JWT object. When using OAuth as authentication type please use the Get or Create JWT action from the Toolbox to create a JWT object and set your OAuth token and expiration date on that object.
3. Add the Cortex Analyst: Create Request action from the Toolbox, and then configure the Request to contain the path to the Snowflake semantic model file and your question/prompt for the model.
4. Add the Snowflake Cortex Analyst action from the Toolbox and provide the following information:
   • ConnectionDetails – The connection details that you configured
   • Request – The request that you configured for the Cortex Analyst: Create Request action
5. To get the response message from the response, add the Response: Get Cortex Analyst Response Message action from the Toolbox, and then add the Response entity as a parameter. The message contains the following information:
   • Content – This is the content of the response message. It includes the text and the SQL, or the suggestions if no SQL is returned
   • Cortex Role – The entity that produced the message; possible values are user or analyst
   • SQLText – The returned SQL suggestion
6. To get the Cortex Analyst Response entity, add the Response: Get Cortex Analyst Response action from the Toolbox, and then add the Response entity as a parameter. The response contains the following information:
   • Request_ID – The returned RequestId

Configuring Snowflake Cortex Search

Snowflake Cortex Search is a fully-managed, ML-powered Snowflake Cortex feature that helps you retrieve data relevant to a prompt to help an LLM generate informed answers (Retrieval Augmented Generation).

Prerequisites

• Make sure that you have access to Cortex Search. For more information, refer to the Snowflake Cortex Search documentation.
• Create the Cortex Search service. For more information, refer to Creating Snowflake Cortex Search service in the Snowflake Cortex Search documentation.
• Set up one of the following supported authentication methods for Cortex Analyst:
  • OAUTH
  • KEYPAIR_JWT

Configuration

To configure your Mendix app for Snowflake Cortex Search, perform the following steps:

1. Create a microflow and retrieve your ConnectionDetails object.
2. When using KEYPAIR_JWT as your authentication type use the Generate JWT action from the Toolbox to generate a JWT object. When using OAuth as authentication type please use the Get or Create JWT action from the Toolbox to create a JWT object and set your OAuth token and expiration date on that object.
3. Create a CortexSearchRequest object, and then configure the Request to contain the path to the Snowflake Cortex Search service, your query/prompt for the model and what indexed columns the Cortex Search service should return.
4. Add the Snowflake Cortex Search action from the Toolbox and provide the following information:
   • ConnectionDetails – The connection details that you configured
   • Request – The request that you configured for the Cortex Search action
5. Make the Cortex Search results available to your LLM.

Example Implementation

The Snowflake showcase app contains example implementations of the Analyst, ANOMALY DETECTION, COMPLETE and TRANSLATE functionalities. For more information, see Snowflake Cortex Analyst.