Data Ingestion for Ecommerce: How to Sync Data Between Any Source Application and Snowflake
Technical Product Marketing Associate
Table of Contents
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.
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.
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.
Let us support you on this journey.
Where are you in your automation journey? Let’s find out.