Shopify to Snowflake: 2 Easy Methods

Companies want more data-driven insights to improve customer experiences. While Shopify stores generate lots of valuable data, it often sits in silos. Integrating Shopify data into Snowflake eliminates those silos for deeper analysis.

This blog post explores two straightforward methods for moving Shopify data to Snowflake: using automated pipelines and custom code. We’ll look at the steps involved in each approach, along with some limitations to consider.

Method 1: Moving Data from Shopify to Snowflake using Hevo Data

Hevo is the only real-time ELT No-code data pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready with zero data loss.

Here are the steps to connect Shopify to Snowflake:

• Step 1: Connect and configure your Shopify data source by providing the Pipeline Name, Shop Name, and the Admin API Password.

• Step 2: Complete Shopify to Snowflake migration by providing your destination name, account name, region of your account, database username and password, database and schema name, and the Data Warehouse name.

That is it. Hevo will now take charge and ensure that your data is reliably loaded from Shopify to Snowflake in real-time. 

For more information on the connectors involved in the Shopify to Snowflake integration process, here are the links to the Hevo documentation:

• Shopify source connector
• Snowflake destination connector

Here are more reasons to explore Hevo: 

1.  Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
2. Auto-Schema Management: Correcting improper schema after the data is loaded into your warehouse is challenging. Hevo automatically maps source schema with destination warehouse so that you don’t face the pain of schema errors.
3. Ability to Transform Data: Hevo has built-in data transformation capabilities that allow you to build SQL queries to transform data within your Snowflake data warehouse. This will ensure that you always have analysis-ready data.

Method 2: Steps to Move Data from Shopify to Snowflake using Custom Code

In this section, you will understand the steps to move your data from Shopify to Snowflake using Custom code. So, follow the below steps to move your data:

• Step 1: Pull data from Shopify’s servers using the Shopify REST API
• Step 2: Preparing data for Snowflake
• Step 3: Uploading JSON Files to Amazon S3
• Step 4: Create an external stage
• Step 5: Pull Data into Snowflake
• Step 6: Validation

Step 1: Pull data from Shopify’s servers using the Shopify REST API

Shopify exposes its complete platform to developers through its Web API. The API can be accessed through HTTP using tools like CURL or Postman. The Shopify API returns JSON-formatted data. To get this data, we need to make a request to the Event endpoint like this. 

GET /admin/events.json?filter=Order,Order Risk,Product,Transaction

This request will pull all the events that are related to Products, Orders, Transactions created for every order that results in an exchange of money, and Fraud analysis recommendations for these orders. The response will be in JSON.

{
  "transactions": [
    {
      "id": 457382019,
      "order_id": 719562016,
      "kind": "refund",
      "gateway": "bogus",
      "message": null,
      "created_at": "2020-02-28T15:43:12-05:00",
      "test": false,
      "authorization": "authorization-key",
      "status": "success",
      "amount": "149.00",
      "currency": "USD",
      "location_id": null,
      "user_id": null,
      "parent_id": null,
      "device_id": iPad Mini,
      "receipt": {},
      "error_code": null,
      "source_name": "web"
    },
    {
      "id": 389404469,
      "order_id": 719562016,
      "kind": "authorization",
      "gateway": "bogus",
      "message": null,
      "created_at": "2020-02-28T15:46:12-05:00",
      "test": false,
      "authorization": "authorization-key",
      "status": "success",
      "amount": "201.00",
      "currency": "USD",
      "location_id": null,
      "user_id": null,
      "parent_id": null,
      "device_id": iPhoneX,
      "receipt": {
        "testcase": true,
        "authorization": "123456"
      },
      "error_code": null,
      "source_name": "web",
      "payment_details": {
        "credit_card_bin": null,
        "avs_result_code": null,
        "cvv_result_code": null,
        "credit_card_number": "•••• •••• •••• 6183",
        "credit_card_company": "Visa"
      }
    },
    {
      "id": 801038806,
      "order_id": 450789469,
      "kind": "capture",
      "gateway": "bogus",
      "message": null,
      "created_at": "2020-02-28T15:55:12-05:00",
      "test": false,
      "authorization": "authorization-key",
      "status": "success",
      "amount": "90.00",
      "currency": "USD",
      "location_id": null,
      "user_id": null,
      "parent_id": null,
      "device_id": null,
      "receipt": {},
      "error_code": null,
      "source_name": "web"
    }
  ]
}

Step 2: Preparing Data for Snowflake

Snowflake natively supports semi-structured data, which means semi-structured data can be loaded into relational tables without requiring the definition of a schema in advance. For JSON, each top-level, complete object is loaded as a separate row in the table. As long as the object is valid, each object can contain newline characters and spaces.

Typically, tables used to store semi-structured data consist of a single VARIANT column. Once the data is loaded, you can query the data like how you would query structured data. 

Step 3: Uploading JSON Files to Amazon S3

To upload your JSON files to Amazon S3, you must first create an Amazon S3 bucket to hold your data. Use the AWS S3 UI to upload the files from local storage.

Step 4: Create an External Stage

An external stage specifies where the JSON files are stored so that the data can be loaded into a Snowflake table.

create or replace stage your_s3_stage url='s3://{$YOUR_AWS_S3_BUCKET}/'
credentials=(aws_key_id='{$YOUR_KEY}' aws_secret_key='{$YOUR_SECRET_KEY}')
encryption=(master_key = '5d24b7f5626ff6386d97ce6f6deb68d5=')
file_format = my_json_format;

Step 5: Pull Data into Snowflake

use role dba_shopify;
create warehouse if not exists load_wh with warehouse_size = 'small' auto_suspend = 300 initially_suspended = true;
use warehouse load_wh;
use schema shopify.public;

/*------------------------------------------
Load the pre-staged shopify data from AWS S3
------------------------------------------*/
list @{$YOUR_S3_STAGE};

/*-----------------------------------
Load the data
-----------------------------------*/
copy into shopify from @{$YOUR_S3_STAGE}

Step 6: Validation

Following the data load, verify that the correct files are present on Snowflake.

select count(*) from orders;

select * from orders limit 10;

Now, you have successfully migrated your data from Shopify to Snowflake.

Limitations of Moving Data from Shopify to Snowflake using Custom Code

In this section, you will explore some of the limitations associated with moving data from Shopify to Snowflake using Custom code.

• Pulling the data correctly from Shopify servers is just a single step in the process of defining a Data Pipeline for custom Analytics. There are other issues that you have to consider like how to respect API rate limits, handle API changes, etc. 
• If you would like to have a complete view of all the available data then you will have to create a much complex ETL process that includes 35+ Shopify resources.
• The above process can only help you bring data from Shopify in batches. If you are looking to load data in real-time, you would need to configure cron jobs and write extra lines of code to achieve that. 
• Using the REST API to pull data from Shopify can be cumbersome. If Shopify changes the API or Snowflake is not reachable for a particular duration, any such anomalies can break the code and result in irretrievable data loss.
• In case you would need to transform your data before loading to the Warehouse – eg: you would want to standardize time zones or unify currency values to a single denomination, then you would need to write more code to achieve this.

An easier way to overcome the above limitations of moving data from Shopify to Snowflake using Custom code is Hevo.

Why integrate Shopify to Snowflake

Let’s say an e-commerce company selling its products in several countries also uses Shopify for its online stores. In each country, they have different target audiences, payment gateways, logistic channels, inventory management systems, and marketing platforms. To calculate the overall profit, the company will use:

Profit/Loss =  Sales – Expenses

While the sales data stored in Shopify will have multiple data silos for different countries, expenses will be obtained based on marketing costs in advertising platforms. Additional expenses will be incurred for inventory management, payment or accounting software, and logistics. Consolidating all the data separately from different software for each country is a cumbersome task.

To improve analysis effectiveness and accuracy, the company can connect Shopify to Snowflake. By loading all the relevant data in a data warehouse like Snowflake, data analysis process won’t involve a time lag.

Here are some other use cases of integrating Shopify to Snowflake:

• Advanced Analytics: You can use Snowflake’s powerful data processing capabilities for complex queries and data analysis of your Shopify data.
• Historical Data Analysis: By syncing data to Snowflake, you can overcome the historical data limits of Shopify. This allows for long-term data retention and analysis of historical trends over time.

Conclusion

In this article, you understood the steps to move data from Shopify Snowflake using Custom code. In addition, you explored the various limitations associated with this method. So, you were introduced to an easy solution – Hevo, to move your Shopify data to Snowflake seamlessly.

Hevo integrates with Shopify seamlessly and brings data to Snowflake without the added complexity of writing and maintaining ETL scripts. It helps transfer data from Shopify to a destination of your choice for free.

FAQ on Shopify to Snowflake