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

With an expanding ecommerce business, data integration with a data warehouse is necessary to have data quality, perform analytics, and integrate with other business processes. Recent surveys collected from our customers show that solving the problem of data warehousing and analytics is the most common business process companies want to solve next with Celigo.

That is why we want to help ecommerce companies start to store data in a data warehouse by tracking and analyzing data. This will improve data quality and visibility for reporting, efficiently scale operations, and help companies stay compliant.

Using Celigo’s template for data warehousing with Snowflake this template allows an easy way to select your ecommerce platform and choose which resources to move into your data warehouse.

Data Warehouse Integration Overview

With Celigo, you can quickly start using a data warehouse with any ecommerce platform.

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 are now successfully using a data warehouse with your ecommerce application(s).

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