How to Load Data From Shopify to Snowflake Easily?

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. You’ll look at the steps involved in each approach, along with some limitations to consider.

Why Connect 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 and Snowflake. By loading all the relevant data in a data warehouse like Snowflake, the data analysis process won’t involve a time lag.

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

• 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.
• Advanced Analytics: You can use Snowflake’s powerful data processing capabilities for complex queries and data analysis of your Shopify data.

Method 1: Using Hevo Data

Step 1.1: Configure Shopify as Your Source

Step 1.2: Configure Snowflake as Your Destination

For more information, here are the links to the Hevo documentation:

• Shopify source connector
• Snowflake destination connector

Method 2: Steps to Move Data Using Custom Code

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

Step 2.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 request the Event endpoint like this. 

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

This request will pull all the events 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.2: Preparing Data for Snowflake

Snowflake natively supports semi-structured data, which means semi-structured data can be loaded into relational tables without defining 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 it the way you would query structured data. 

Step 2.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 2.4: Create an External Stage

An external stage specifies where the JSON files are stored to load the data 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 2.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 2.6: Validation

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

select count(*) from orders;

select * from orders limit 10;

Limitations of Moving Using Custom Code

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

• Pulling the data correctly from Shopify servers is a single step in defining a Data Pipeline for custom Analytics. You have to consider other issues, such as respecting API rate limits, handling 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 want to load data in real-time, you 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, 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 using custom code is Hevo.

Method 3: Using CSV Files

Step 1: Export Data from Shopify

• Using Shopify Admin:
  • Navigate to the relevant section (e.g., Orders, Products, Customers) and use the “Export” feature to download data as CSV.
• Using the Shopify API:
  • Alternatively, use the Shopify API to extract data and convert the results to CSV format programmatically.

Step 2: Prepare Your CSV Files

• Data Cleaning:
  • Ensure the CSV files have the proper headers and consistent formatting.
• Validation:
  • The data types (e.g., dates, numbers) are correctly formatted to match your Snowflake table schema.

Step 3: Upload CSV Files to a Cloud Storage Stage

• Select a Cloud Storage Provider:
  • Use Amazon S3, Azure Blob Storage, or Google Cloud Storage to host your CSV files.
• Upload Files:
  • Place the CSV files in a designated bucket or container that Snowflake can access.

Step 4: Create a Snowflake Stage

• Define an External Stage: In Snowflake, create an external stage that points to your cloud storage location using the appropriate credentials.

Example:

CREATE OR REPLACE STAGE my_csv_stage URL='s3://my-bucket/path/' CREDENTIALS=(AWS_KEY_ID='YOUR_KEY' AWS_SECRET_KEY='YOUR_SECRET');

Step 5: Load Data into Snowflake Using the COPY Command

• Create a Target Table:
  • Define a table in Snowflake that matches the CSV structure.
• Copy Data:
  • Use the COPY command to load the CSV data into your Snowflake table.
  • Example command

COPY INTO my_target_table FROM @my_csv_stage FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);

Step 6: Validate and Monitor the Load

Check load history and error logs to resolve any issues that may have occurred during the process.

Additional Steps:

Data Verification: Run queries to ensure that the data in Snowflake matches the source data from Shopify.

Error Handling: Check load history and error logs to resolve any issues that may have occurred during the process.

Conclusion

In this article, you understand how 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.

 Sign up for our 14-day free trial and seamlessly migrate your data from Shopify to Snowflake.

FAQs