Data Ingestion: How to Sync Data Between Any Source Application and Snowflake

Integrating data with a data warehouse is crucial for businesses of all sizes. It ensures data quality, enables comprehensive analytics, and streamlines business processes. Recent customer surveys indicate that companies place a high priority on addressing data warehousing and analytics challenges. Our goal is to assist businesses in unlocking and analyzing data stored in data warehouses, preventing data silos. This enhances data quality, improves visibility for reporting, enables operational scalability, and ensures compliance.

Celigo’s template for data warehousing with Snowflake offers a straightforward method to connect your platform and select resources for migration into your data warehouse, facilitating easier data management and analysis.

Data Warehouse Integration Overview

Marketplace

It starts in the marketplace, where you will find the data warehouse template with Snowflake. You can access the template by quickly creating an account. (Then you can browse all all of the other Snowflake integration templates.)

Selecting Data Sources

After installing and configuring a connection to your Snowflake account, you can select between the following ecommerce source applications.

Selecting Resources for Shopify

In this example, we will set up a connection to Shopify and select all the resources available to sync into our data warehouse in Snowflake.

View All Flows

Once the template is installed and all connections are created, they are moved into an integration tile, showing two flows for every connection created. The first flow is to bulk insert resources into a staging table in Snowflake, and the second flow is to merge and delete the staging table.

Creating a Flow Group

Now, to make the integration look more organized, we will create a flow group for each connection, in this case, just one category for Shopify.

Overview of Staging Table Flow

For the first flow in the template, all the selected resources correspond to an individual export step.

In this example, we have three of the five resources shown: Customers, Orders, and Abandoned checkouts.

All of these resources are then synced to a staging table in Snowflake.

Snowflake Import Settings

For the destination table, select the staging table you are using for the Shopify data from the dropdown below.

Snowflake Mapping Settings

Our mapping shows that the JSON serialized data will be mapped into a data variant column along with the resource name, which would be Customers, Orders, etc., and the primary key of the data will be mapped into a column, as well as the timestamp of the data.

Overview of Merge & Delete Flow

The second flow, or the merge & delete flow, will check for existing records with a SQL query, limiting to 1, and merge & clear that data from the staging table.

Changing Table Settings

To specify different staging and production tables, changes can made in the flow settings.

Snowflake Data

After all those changes have been made and the flow is set up to use, we can run the flows and view all of the data in our data warehouse in Snowflake.

Data can be queried with different views as needed; however, all the JSON data is shown in the correct table to be viewed, analyzed, and reported on at any time.

Data Is Now In A Warehouse

Congratulations! You have successfully synced your data warehouse and source application. 

For more information on this template, read our Data Warehouse Automation for Snowflake documentation, which will address any questions, from an overview to the setup and configuration of other applications.