Magento to Snowflake Integration: Methods Explained Step-by-Step

Do you want to gather fresh insights and analytics from your Magento data? If so, then transferring your data from Magento to a data warehouse like Snowflake is a good way to initiate this process. 

In this blog, we will look at two ways that can help you load data from Magento to Snowflake. You will be able to evaluate both methods and choose the one that best suits your needs.

Introduction to Magento

• Magento is an open-source e-commerce platform that is the solution of choice for more than 100,000 online stores. 
• Having been downloaded more than 2.5 million times, it is one of the most popular e-commerce solutions currently available, accounting for roughly 30% of the total market share.
• Being open-source and built on the Zend framework, Magento’s source code is available under OSL (Open Software License) v3.0. The first general availability release of the software was made available on March 31st, 2008, but Magento 2.0 was released on November 17, 2015, with a number of improvements and in two versions – Magento Open Source and Magento Commerce.

Introduction to Snowflake

• Developed in 2012, Snowflake is a popular, fully managed cloud data warehouse that can be hosted on any cloud service, such as Amazon Cloud Service, Google Cloud Storage, or Microsoft Azure. 
• Snowflake consists of services like data engineering, data lakes, data warehouses, analytics, and more.
• With Snowflake, users can organize their data in an optimized, compressed, and columnar format whenever data is loaded into the platform. Snowflake is a ready-to-use platform that uses SQL queries to perform data operations. You can start using Snowflake with a free trial of 30 days.
• Since Snowflake is a fully managed SaaS platform, users do not need to select, manage, or configure hardware or software. As a result, it is ideal for many organizations that do not want to dedicate resources for setup, maintenance, configuration, etc.

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

Step 1.1: Configure Magento as your Source

Step 1.2: Select Snowflake as your Destination

Advantages of Hevo

• Smooth Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to your schema in the desired Data Warehouse.
• Exceptional Data Transformations: Best-in-class & Native Support for Complex Data Transformation at fingertips. Code & No-code Flexibility is designed for everyone.
• Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
• Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Method 2: Moving Data from Magento to Snowflake using Custom Scripts and APIs

This approach would need you to invest time and effort from the engineering team. The broad steps would look as follows:

• Step 1: Extracting the Data
• Step 2: Data Preparation
• Step 3: Loading the Data

Step 2.1: Extracting the Data

Magento data can be accessed by executing simple GET requests to its API. Generally, you can use the command GET  http://magentohost/api/rest/ and then specify the required endpoint. The data will be returned in either JSON or XML format. For example, GET http://magentohost/api/rest/products. 

Result (in JSON): 

{"1":{"entity_id":"1","type_id":"simple","sku":"dress_test","status":"1","visibility":"4","tax_class_id":"2",
"weight":"1.0000","price":"1500.0000","special_price":"1000.0000","name":"Wedding dress","url_key":"dress",
"country_of_manufacture":"AO","msrp_display_actual_price_type":"2","gift_message_available":"1","news_from_date":"2012-03-21 00:00:00",
"news_to_date":"2012-03-24 00:00:00","special_from_date":"2012-03-21 00:00:00","special_to_date":"2012-03-24 00:00:00",
"description":"White wedding dress"},"2":{"entity_id":"2","type_id":"simple","sku":"black_sunglasses","status":"1","visibility":"4",
"tax_class_id":"2","weight":"0.2000","price":"500.0000","special_price":"300.0000","name":"Sunglasses","url_key":"sunglasses",
"country_of_manufacture":"AR","msrp_display_actual_price_type":"2","gift_message_available":null,"news_from_date":null,"news_to_date":null,
"special_from_date":"2012-03-21 00:00:00","special_to_date":"2012-03-24 00:00:00","description":"Black sunglasses"}}

Explore further information on Magento’s Rest API.

Step 2.2: Data Preparation

Before loading the data into Snowflake, it is necessary to have a well-defined schema. For example, it can be useful to create a schema where data from each Magento end point is mapped to a distinct table in Snowflake. It is also imperative to make sure that the data types from the Magento file are mapped properly to the accepted data types in Snowflake. Snowflake provides support for a wide array of data types. Information on accepted data types in Snowflake can be found here Preparing Your Data Files.

Step 2.3: Loading the Data

The data is loaded into Snowflake with the help of the COPY INTO SQL statement through SnowSQL ( Snowflake’s Command Line Interface). The files can be loaded directly from Snowflake staging tables or from any of Snowflake’s external storage locations. For the purposes of this blog, we will look at loading directly from the Snowflake internal stage types and also through each of Snowflake’s external storage locations. 

Loading from Snowflake Stages

The data can be ingested into a Snowflake internal stage using the PUT command before we use COPY INTO to load it into Snowflake. 

The following are commands to load the data from the stages:

• User Stage Type:
  

  COPY INTO TABLE1 FROM @~/staged file_format=(format_name=’json_format’)

• Table Stage Type:

  COPY INTO TABLE1 file_format=(Type=’JSON’ Strip_outer_array=”TRUE”)

• Created Internal Stage:

  COPY INTO TABLE1 FROM @Stage_Name

Loading Data from External Stages

• Amazon S3:
  You can load data directly from an Amazon S3 bucket but the recommended method is to first create an Amazon S3 external stage. The same applies to Microsoft Azure and GCP buckets.

  COPY INTO TABLE1 FROM s3://bucket CREDENTIALS= (AWS_KEY_ID='YOUR AWS ACCESS KEY' AWS_SECRET_KEY='YOUR AWS SECRET ACCESS KEY') ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY') FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)

• Microsoft Azure:
  

  COPY INTO TABLE1 FROM azure://your account.blob.core.windows.net/container STORAGE_INTEGRATION=(Integration_name) ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY') FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)

• GCS:
  

  COPY INTO TABLE1 FROM 'gcs://bucket’ STORAGE_INTEGRATION=(Integration_name) ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY') FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)

Learn More About:

• Load Data from Magento to MySQL
• Magento Export Products

Limitations of Manually Loading Data from Magento to Snowflake

• Time Consuming Process: It is not ideal for fast-paced organizations since it is very time-consuming. The process also has a dependency on precious developer bandwidth.
• Limited Real-Time Data Load Capability: This method does not enable real-time data updates. This means that you would have to configure cron jobs and write a lot of extra code to enable simple real-time data load functionality.
• Maintenance Issues: Inaccurate data may be generated whenever Magento has issues with its API. Thus, it is necessary to have a lot of resources monitoring the data source to check for problems with consistency. Additionally, you would need to keep availing the engineering team’s help to keep the code up to date with Magento’s changing API.
• Data Transformations: You will also need to write additional code for any data transformations as this method has no built-in functionality to take care of that. This can make the process even more lengthy.

Final Thoughts

• In this Magento to Snowflake migration guide, you have learned how to manually connect Magento to Snowflake. 
• You also came across the various limitations of manually connecting Magento to Snowflake. 
• So, if you are looking for a fully-automated data pipeline, then try Hevo.

Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications such as Magento into your Data Warehouse like Snowflake to be visualized in a BI tool. Hevo is fully automated and, hence, does not require you to code.

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

FAQ