INFO: Our Snowflake connector with Chain.io is currently in Preview and only available in Chain.io Workspaces that are on the Preview release track. Find out how to get our Workspace on the Preview track here.
In this article:
- What Is Snowflake
- What Is the Chain.io Snowflake Connector
- What Is the Chain.io Canonical Model
- How To Integrate Chain.io and Snowflake
-
Adding Your Snowflake Host System or Integration Connection To A Flow Configuration
What Is Snowflake
Snowflake offers a cloud-based data platform that allows organizations to store, manage, and analyze large amounts of data in an efficient and secure way, using cloud computing instead of traditional data centers.
In simple terms, Snowflake provides a place for companies to store their data (like a digital warehouse) and tools to make it easier to access and analyze that data. One of its standout features is that it works across multiple cloud providers like Amazon Web Services (AWS), Microsoft Azure, and Google Cloud, making it versatile and easy to integrate with existing cloud services.
Their platform allows businesses to run complex data queries quickly, share data with others in real-time, and scale their storage needs without having to worry about managing hardware. Snowflake is popular in industries where large-scale data processing and analytics are crucial, such as finance, healthcare, and retail.
The key advantages of Snowflake’s technology include:
- Scalability: It grows with the company's needs
- Data Sharing: Easy to securely share data across different teams or organizations.
- Speed: Optimized for fast processing of data queries, even with huge datasets.
- No Maintenance: Businesses don’t have to handle server upkeep; everything runs in the cloud.
Overall, Snowflake makes it easier for companies to turn their data into actionable insights without the complexity of traditional data management systems.
What is the Chain.io Snowflake Connector
Our connection to Snowflake allows Enterprise shippers to get deeper insights with comprehensive historical shipment data stored within Snowflake. This enables businesses to track the evolution of their shipments over time, uncover trends, and make informed decisions to optimize operations. The ability to preserve each version of shipment records with precise timestamps ensures data integrity, enabling accurate audits and regulatory compliance.
Collaboration across the supply chain is streamlined by providing consistent, version-controlled data to all partners. Teams can access both current and historical shipment data, minimizing miscommunication and improving coordination. Disputes are resolved faster with a complete, transparent history of shipment records, which allows discrepancies to be addressed quickly.
Financial accuracy is also improved through precise audit and payment processes. With Snowflake's detailed shipment data storage, customers can ensure billing aligns with actual records, optimizing freight spend. Additionally, enterprise shippers can integrate CO2 emissions and risk data to make informed decisions that promote sustainability and risk mitigation.
By consolidating all shipment data into a unified system, Snowflake, along with Chain.io, helps shippers create a single version of the truth for decision-making. This centralized system also integrates supply chain data with other organizational information, offering executives a holistic view of operations to drive strategic and efficient decisions.
What is the Chain.io Canonical Model
The Chain.io canonical model streamlines communication between different systems by standardizing data exchange within the Chain.io platform. Rather than dealing with multiple formats, our canonical model ensures that all systems speak a "common language." When files and data structures from different sources are processed, they are converted into this universal format, which simplifies integration and enables efficient data sharing across your logistics network. This unified approach reduces complexity, improves accuracy, and ensures smoother collaboration between partners and systems.
For more information on the Chain.io Canonical Model, please refer to our product documentation here.
Viewing The Chain.io Canonical Model In The Portal
Chain.io provides an area for customers to view our canonical model. If you are using Snowflake along with Chain.io this area will give you a reference of the schema used to create tables in the database.
To view the Chain.io Canonical schema, navigate to your Workspace Settings within the Chain.io portal.
Scroll to the bottom right panel and select Canonical Schema
The following view allows you to browse the Chain.io Canonical Schema, using the objects in the schema on the left and their related fields and links that get displayed on the right.
If you need further information or support about Chain.io’s canonical model and schema please contact support by submitting a request.
How To Integrate Chain.io and Snowflake
Creating Your Database In Snowflake
To prepare Snowflake to receive data, you must first create a user, role, warehouse and empty database within your Snowflake account. Please follow the instructions below to get started.
Log in to your Snowflake account and navigate to Projects > Worksheets and create a new Worksheet by clicking the “+” button at the top right.
Copy and paste the script below into the main Worksheet area.
-- This script creates an empty database with a dedicated role, user, and warehouse to support Chain.io connections.
-- UPDATE THESE VARIABLES WITH YOUR PREFERRED VALUES
SET role_name = 'sample_role';
SET user_name = 'sample_user';
SET user_password = 'changeme';
SET db_name = 'sample_db';
SET db_schema = 'sample_db.public'; -- should match db_name + .public
SET info_schema = 'sample_db.information_schema'; -- should match db_name + .information_schema
SET warehouse_name = 'sample_whse';
SET warehouse_size = 'X-SMALL';
-- Create a new role
CREATE ROLE IDENTIFIER($role_name);
-- Create a new user with the specified password
CREATE USER IDENTIFIER($user_name)
PASSWORD = $user_password
DEFAULT_ROLE = $role_name
MUST_CHANGE_PASSWORD = false;
-- Create a new database with a "public" schema
CREATE DATABASE IDENTIFIER($db_name);
-- Create a new XS sized warehouse
CREATE WAREHOUSE IDENTIFIER($warehouse_name)
WITH WAREHOUSE_SIZE = $warehouse_size
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE;
-- Grant the user access to use the warehouse
GRANT USAGE ON WAREHOUSE IDENTIFIER($warehouse_name) TO IDENTIFIER($role_name);
-- Put the user in the role
GRANT ROLE IDENTIFIER($role_name) TO USER IDENTIFIER($user_name);
-- Grant the role access to create and alter the database & schema
GRANT OWNERSHIP ON DATABASE IDENTIFIER($db_name) TO ROLE IDENTIFIER($role_name);
GRANT OWNERSHIP ON SCHEMA IDENTIFIER($db_schema) TO ROLE IDENTIFIER($role_name);
GRANT ALL ON DATABASE IDENTIFIER($db_name) TO ROLE IDENTIFIER($role_name);
Edit the variables for the role_name, user_name, user_password, db_name, db_schema, info_schema, and warehouse_name using your own values. Take note of the comments in the script to help you set the correct values.
Once all variables are edited, confirm you are on the correct Role for your account and the correct Warehouse instance.
Select ALL of the content of the main script panel(Ctrl+A(Win), Cmd+A(Mac)). Once all content is highlighted, click the Play button in the upper right to execute the script.
When the script is completed, a panel will appear reporting the with a summary of the process.
Refresh your Databases in the Snowflake sidebar and check to see that your new, empty database has been created.
Now that you have an empty database in Snowflake you need to link it to our Chain.io workspace using a Host System or Integration connection. To do this, open https://portal.chain.io/ in another browser tab or window and follow the instructions below.
Adding A Snowflake Host System To Chain.io
To have Chain.io and Snowflake communicate effectively you need to set up a Host System or Integration Connection within your Chain.io Workspace to complete the connection between the two systems. To do this in the Chain.io portal, please follow these steps:
Navigate to your Workspace settings within the Chain.io portal at https://portal.chain.io/ and select the Host Systems link.
Click the Add Host System button at the top of the view
In the modal that appears use the dropdown to select Snowflake
In the form displayed, copy and paste the equivalent values from your Snowflake Worksheet into the fields as shown in the example below. Fields are explained in the diagram below:
Worksheet Variable Diagram:
- Name - The name of this Host System in the Chain.io portal. This can be any label you choose
- User Name - the user_name value for your Snowflake Database as it appears in Line 4 of your Snowflake Worksheet
- Password - the user_password value for your Snowflake Database as it appears in Line 5 of our Snowflake Worksheet
-
Account - This is the account identifier found in your Snowflake profile. It can be found in the flyout illustrated below. This can also be found in the URL of your Snowflake account as the trailing numbers immediately following the .com: https://app.snowflake.com/<organization_name>/<account_name>/*
- NOTE: the format of this required field must have a dash(-) between the organization name and account name
- Role - the role_name value for your Snowflake Database as it appears in Line 3 of your Snowflake Worksheet
- Warehouse - the warehouse_name value for your Snowflake Database as it appears in Line 9 of your Snowflake Worksheet
- Database - the db_name value for your Snowflake Database as it appears in Line 6 of your Snowflake Worksheet
- Schema - This by default needs to be public and corresponds to the info_schema suffix on Line 7 of your Snowflake Worksheet
- Canonical Version - This is the version number, for the Chain.io canonical model, being used with this Snowflake system. Refer to the section above about browsing the Chain.io canonical model for more information. Unless you have a specific reason, leave this at the default value.
Once all fields have been filled and verified, click the Save button to close the modal.
Initialize The Database Connection
Although the Snowflake Host System is saved, you must now initialize the database to complete the connection between the two systems. To do this, expand the panel for your new Snowflake host system and scroll to the bottom.
Click the Initialize Database button and then confirm the initialization by clicking the button labeled, Yes, initialize database in the modal that appears.
To check the status of the initialization, click the Initialization Jobs link.
This will display a modal with the start date and time of your initialization, the status, and any messages related to the initialization.
Note: Initialization could take a few seconds. You may have to click the refresh button a few times to see the initialization message change.
You can confirm the database connection has been successfully initialized when you see a message indicating that the status was successful and the message reads Job Complete, XX queries written
Once you see the message that the Job is completed. Close the modal and click the Save button at the bottom of the panel.
Return to your Snowflake account and expand your previously empty database to reveal the Chain.io datatables created.
You now have a Snowflake Host System ready to use within your Integrations and Flows within the Chain.io portal.
Adding A Snowflake Integration Connection
Navigate to the integration where you wish to add your Snowflake connection
From the Integration Dashboard, select Settings in the main navigation
At the bottom right of the Flow Settings view, in the Connections section click the New button
On the next screen, use the dropdown to select a Connection Type to set up.
Select Snowflake, and in the form displayed, copy and paste the equivalent values from your Snowflake Worksheet into the fields. Use the reference guide in the previous section for assistance if needed.
When all fields have been filled in, click the Save button. You now have an Integration Connection created, however to use it, you must complete the initialization steps in the next section.
Initializing The Database Connection
With your Snowflake Integration Connection saved, you must now initialize the database to complete the connection between the two systems. To do this, click the Edit link in new Integration Connection that you just set up, in your Integration Settings
In the Edit Connection screen scroll to the bottom and click the Initialize Database button.
In the modal that appears confirm you want to initialize by clicking the Yes, initialize the database button.
To check the status of the initialization, click the Initialization Jobs button link.
This will display a modal with the start date and time of your initialization, the status, and any messages related to the initialization.
Note: This could take a few seconds. You may have to click the refresh button a few times to see the initialization message.
You can confirm the database connection has been successfully initialized when you see a message indicating that the status was successful and the message reads Job Complete, XX queries written.
Once you see the message that the Job is completed. Close the modal and click the Save button at the bottom of the panel.
Return to your Snowflake account and expand your previously empty database to reveal the Chain.io data tables created.
You now have a Snowflake Integration Connection ready to use within your Integrations and Flows within the Chain.io portal.
Adding Your Snowflake Host System or Integration Connection To A Flow Configuration
To add your Snowflake related Host System or Integration Connection to your Flow Configuration, start a new Flow or navigate to an existing Flow Configuration.
In the Flow Configuration, Destination File Type select, Snowflake Canonical
Next, in the Destination System/Connection, select the Snowflake system you have set up in the previous steps.
Take note that if you select Snowflake Canonical as your file type, you must use Snowflake as the Destination System/Connection.
Once you have your Flow Configuration completed with Snowflake Canonical as your destination file type and your Snowflake instance as your Destination System or Connection, Save and Deploy the Flow.