How to simplify Inphinity API data ingestion with Snowflake UDTF

Are you struggling to load Inphinity writeback data into your Snowflake environment?

This step-by-step guide shows you how to create a User Defined Table Function (UDTF) in Snowflake that calls the Inphinity API directly. You'll learn how to set up the necessary components and write a Python function that handles the API connection efficiently.

The challenge with Inphinity writeback data

When using Inphinity's default settings, all writeback data is managed through their system. While this provides a convenient API to fetch your data (the same one used with Qlik's REST Connector), getting this information back into Snowflake requires additional steps.

This approach is particularly useful if you:

• Need to consolidate Inphinity form data in your data warehouse
• Want to create a centralised reporting source
• Aim to combine Inphinity data with other business information

Required information from your Inphinity forms

Before creating your UDTF, you'll need two key pieces of information from the Inphinity.app Cloud UI:

1. Your Form ID
2. Your API Token (you may need to generate one using the button in the UI)

For this guide, we'll use one of the destinations from our Ometis higher education planning demos.

Setting up Snowflake for external API access

The first step involves configuring Snowflake to communicate with external services. You'll need to create three components:

Remember to grant USAGE permissions on these objects to the appropriate roles. Make sure you set them up with a role that has the correct permissions.

Creating your Python UDTF

Now for the core functionality. We'll create a User Defined Table Function called GET_INPHINITY_FORM_DATA that:

• Takes one parameter: FORM_ID
• Returns a table with one column containing the raw JSON from the API

The header of our function GET_INPHINITY_FORM_DATA will take one parameter FORM_ID and return a table of one column and one row, The RAW_JSON from the API. We will process the response further using SQL for flexibility. The other settings are setup as shown below.

The main function within our Code is as follows:

The code includes some basic error handling as well. Fundamentally the code does this:

1. Get the Key From the Secret using the form id
2. Make a get request to the Inphinity rest endpoint
3. Returns a snowflake data frame of the raw JSON response.

Putting this all together, we get a framework we can use to ingest our Inphinity data.

You can call it with an ID you have the secret stored for and view the JSON response:

Processing the JSON response with SQL

You can then create a query like the following to process that data:

Since the data is returned in an array of object, we need to LATERAL Flatten the input. We can then do f.value:"COLUMN_NAME" AS xyz to create a table from that data. Below is a sample of the output from this particular endpoint.

Extending this approach to other APIs

While this guide focuses on Inphinity, the same core approach can be applied to virtually any API. The key components remain the same:

• Network rules to allow outbound connections
• Securely stored credentials
• A Python UDTF to handle the API interaction
• SQL for processing the returned data

This pattern creates a flexible framework for bringing external data into your Snowflake environment.

Benefits for your data ecosystem

Implementing this solution offers several advantages:

• Centralises data from multiple sources in one location
• Enables complex analysis by combining datasets
• Creates a single source of truth for reporting
• Reduces manual data transfers
• Allows for automated processing and integration

Get expert help with your Snowflake and Qlik integration

Need help implementing this solution? We've helped over 250 UK businesses get more value from their data. We specialise in Qlik, Snowflake and Inphinity integration.

Book a demo call today to see how we can integrate your Inphinity data with Snowflake.