Shopify to Snowflake: 2 Easy Methods

Companies are on a quest to become more data-driven so that they can provide their customers with a seamless and engaging experience. There is a lot of data being generated by Shopify stores and some stores are looking to eliminate their data silos by integrating these massive amounts of data from Shopify to Snowflake.

In this blog, you will be introduced to Shopify and Snowflake. You will also understand the steps to move your data from Shopify to Snowflake using the Custom code method. In addition, you will explore some of the limitations associated with this method, So, before we start discussing the process involved to move data, let us understand a little about these individual platforms.

Introduction to Snowflake

The Snowflake platform allows users to have a petabyte-scale Database and infinite compute scale with no management overhead. Data in Snowflake can only be accessed through SQL query operations. Snowflake manages all aspects of Data Storage: Organization, Structure, Compression, Metadata, Statistics, etc.

Snowflake is well-positioned to assist customers to take advantage of the Cloud because:

• It is a very easy product to learn. You only need to know SQL.
• It provides a great amount of elasticity and flexibility.
• There is a lot of new innovation happening in the Cloud that is not necessarily available on-premise.
• It is an extremely fast product in terms of performance and so customers can adopt Snowflake and put new use cases and workloads into Snowflake very quickly and very easily. This is very different from the past where businesses had to plan for the purchase of hardware and a whole lot of services wrapped around the complexity of Integration of their big data and analytics. 

Introduction to Shopify

Shopify is a managed E-Commerce platform for online and retail POS systems. Shopify enables businesses to set up and manage Online Stores, accept Credit Card Payments, and track and respond to Orders.

Although Shopify provides detailed Analytics on their application, businesses would need to combine this input with data from Marketing or Sales applications that they use. This will allow them to perform deeper Analytics and uncover meaningful insights that grow the business. 

In this post, we will be looking at methods to extract data from Shopify to use it for further analysis in Snowflake. 

Let us discuss both these approaches to move your data from Shopify to Snowflake in detail.

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.

Moving Data from Shopify to Snowflake using Hevo Data

Hevo Data Integration platform can help you overcome the above limitations and move data from Shopify to Snowflake in just a few minutes. Hevo enables the lowest time to production for such copy operations, allowing developers to focus on their core business logic rather than waste time on the configuration nightmares involved in setting these up.

Sign up here for a 14-day free trial to start moving your Shopify data.

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. 

Here are more reasons to explore Hevo: 

1. Low Effort, Low Maintenance: Once you configure the account, Hevo will automatically take care of all the processes in the background. This will also eliminate any additional maintenance overheads and frees up time to focus on other important initiatives.
2. Real-time Data Load: Hevo’s real-time streaming architecture ensures that your Shopify data within your Snowflake Data Warehouse is always up-to-date.
3. Connectors – Hevo supports 100+ integrations (including 40+ Free Sources) to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, MongoDB, TokuDB, DynamoDB, PostgreSQL databases to name a few. 
4. More Data Sources: In addition to Shopify, Hevo can bring data from a large array of sources – Sales and Marketing apps, Databases, Analytics apps, and so on. Hevo will ensure that you have a way to bring any data to Snowflake and derive meaningful insights. 
5. 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.

Conclusion

In this article, you learned about Shopify and Snowflake. You understood the steps to move data from Shopify to 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.

visit our website to explore hevo

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 a 14-day free trial with Hevo. This will give you an opportunity to experience Hevo’s simplicity so that you enjoy an effortless data load from Shopify to Snowflake. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

What are your thoughts on moving data from Shopify to Snowflake? Let us know in the comments.