How to load data from Magento to Snowflake

Extract your data from Magento

Magento exposes its platform through both a REST and a SOAP interface. Both can be used to pull data from it, which is also the scope of this article but also to interact with the platform. By using these interfaces, developers create rich applications and plugins for Magento. In this post, we will use the REST version of the Magento platform.

As a Web API following the RESTful architecture principles, it can be accessed through HTTP. As a RESTful API, interacting with it can be achieved by using tools like CURL or Postman or by using http clients for your favorite language or framework. A few suggestions:

  • Apache HttpClient for Java
  • Spray-client for Scala
  • Hyper for Rust
  • Ruby rest-client
  • Python http-client

Magento does not publish official SDKs, but it is possible to automatically generate clients that can act as SDKs for your favorite language or platform by using the SOAP interface. For example, in Java, you can create a client in Eclipse by providing the WSDL file that Magento exposes after you setup the platform.

Magento API Authentication

Magento is a self hosted platform, unless you are using the Enterprise cloud edition, so you have much more control over its access than other solutions, but if you want to access its data through the REST API that it has, then you will have to use oAuth for authentication which is supported by Magento.

Magento rate limiting

As a platform hosted on your own premises it doesn’t really impose any rate-limiting. In any case, as you would like to avoid stressing your e-commerce platform that is facing your customers, you should make sure that your pipeline process does not overstress your Magento installation. But this is completely at your discretion.

Endpoints and available resources

Magento exposes the following resources:

  • Products. Retrieve the list of products, create, update, delete a product.
  • Product categories. Retrieve the list of categories assigned to a product, assign and unassign the category from a product.
  • Product images. Retrieve the list of websites assigned to a product, assign, unassign a website to/from a product.
  • Customers. Retrieve the list of customers, create, delete a customer, and update the customer information.
  • Customer Addresses. Retrieve the list of customer addresses, create, update, and delete the customer address.
  • Inventory. Retrieve the list of stock items, update required stock items.
  • Sales Orders. Retrieve the list of sales orders with detailed information on order addresses, items, and comments.
  • Order Addresses. Retrieve information on the specified order comments.
  • Order Items. Retrieve information on specified order items.

The API is possible to return either JSON or XML responses. This is something that you can control by providing the appropriate Accept headercontent type.

For all the above resources, we can request from the Magento platform to pull out a list of results with all the associated data, so ideally, we would like to pull all the data and make sure that we keep them up to date on our analytics platform of choice for further analysis. For this post, we will just see how we can pull data for one resource, the Sales Orders. The process is the same for all other resources.

To pull data for the Sales Orders, we need to execute a get request to the following endpoint:

JAVASCRIPT
http://magentohost/api/rest/orders

As a platform hosted by you, you need to replace the “magentohost” part of the URL with the actual URL of the host that has Magento running. The rest of the URL is the same as the above. The default response is in XML and looks like the following:

JAVASCRIPT
<?xml version="1.0"?>
<magento_api>
 <data_item_1>
   <customer_id>3</customer_id>
   <base_discount_amount>0.0000</base_discount_amount>
   <base_shipping_amount>455.0000</base_shipping_amount>
   <base_shipping_tax_amount>0.0000</base_shipping_tax_amount>
   <base_subtotal>13650.0000</base_subtotal>
   <base_tax_amount>0.0000</base_tax_amount>
   <base_total_paid></base_total_paid>
   <base_total_refunded></base_total_refunded>
   <tax_amount>0.0000</tax_amount>
   <total_paid></total_paid>
   <total_refunded></total_refunded>
   <base_shipping_discount_amount>0.0000</base_shipping_discount_amount>
   <base_subtotal_incl_tax>13650.0000</base_subtotal_incl_tax>
   <base_total_due>14105.0000</base_total_due>
   <total_due>14105.0000</total_due>
   <base_currency_code>USD</base_currency_code>
   <tax_name></tax_name>
   <tax_rate></tax_rate>
   <addresses>
     <data_item>
       <region>Palau</region>
       <postcode>19103</postcode>
       <lastname>Doe</lastname>
       <street>2356 Jody Road Philadelphia, PA 19103</street>
       <city>PA</city>
       <telephone>610-634-1181</telephone>
       <country_id>US</country_id>
       <firstname>John</firstname>
       <address_type>billing</address_type>
       <prefix></prefix>
       <middlename></middlename>
       <suffix></suffix>
       <company></company>
     </data_item>
     <data_item>
       <region>Palau</region>
       <postcode>19103</postcode>
       <lastname>Doe</lastname>
       <street>2356 Jody Road Philadelphia, PA 19103</street>
       <city>PA</city>
       <telephone>610-634-1181</telephone>
       <country_id>US</country_id>
       <firstname>John</firstname>
       <address_type>shipping</address_type>
       <prefix></prefix>
       <middlename></middlename>
       <suffix></suffix>
       <company></company>
     </data_item>
   </addresses>
   <order_items>
     <data_item>
       <sku>Sunglasses_1</sku>
       <price>150.0000</price>
       <base_price>150.0000</base_price>
       <base_original_price>150.0000</base_original_price>
       <tax_percent>0.0000</tax_percent>
       <tax_amount>0.0000</tax_amount>
       <base_tax_amount>0.0000</base_tax_amount>
       <base_discount_amount>0.0000</base_discount_amount>
       <base_row_total>13650.0000</base_row_total>
       <base_price_incl_tax>150.0000</base_price_incl_tax>
       <base_row_total_incl_tax>13650.0000</base_row_total_incl_tax>
     </data_item>
   </order_items>
 </data_item_1>
 <data_item_2>
   <customer_id>3</customer_id>
   <base_discount_amount>0.0000</base_discount_amount>
   <base_shipping_amount>95.0000</base_shipping_amount>
   <base_shipping_tax_amount>0.0000</base_shipping_tax_amount>
   <base_subtotal>3350.0000</base_subtotal>
   <base_tax_amount>0.0000</base_tax_amount>
   <base_total_paid>2445.0000</base_total_paid>
   <base_total_refunded>1845.0000</base_total_refunded>
   <tax_amount>0.0000</tax_amount>
   <total_paid>2445.0000</total_paid>
   <total_refunded>1845.0000</total_refunded>
   <base_shipping_discount_amount>0.0000</base_shipping_discount_amount>
   <base_subtotal_incl_tax>3350.0000</base_subtotal_incl_tax>
   <base_total_due>1000.0000</base_total_due>
   <total_due>1000.0000</total_due>
   <base_currency_code>USD</base_currency_code>
   <tax_name></tax_name>
   <tax_rate></tax_rate>
   <addresses>
     <data_item>
       <region>Palau</region>
       <postcode>19103</postcode>
       <lastname>Doe</lastname>
       <street>2356 Jody Road Philadelphia, PA 19103</street>
       <city>PA</city>
       <telephone>610-634-1181</telephone>
       <country_id>US</country_id>
       <firstname>John</firstname>
       <address_type>billing</address_type>
       <prefix></prefix>
       <middlename></middlename>
       <suffix></suffix>
       <company></company>
     </data_item>
     <data_item>
       <region>Palau</region>
       <postcode>19103</postcode>
       <lastname>Doe</lastname>
       <street>2356 Jody Road Philadelphia, PA 19103</street>
       <city>PA</city>
       <telephone>610-634-1181</telephone>
       <country_id>US</country_id>
       <firstname>John</firstname>
       <address_type>shipping</address_type>
       <prefix></prefix>
       <middlename></middlename>
       <suffix></suffix>
       <company></company>
     </data_item>
   </addresses>
   <order_items>
     <data_item>
       <sku>Sunglasses_1</sku>
       <price>150.0000</price>
       <base_price>150.0000</base_price>
       <base_original_price>150.0000</base_original_price>
       <tax_percent>0.0000</tax_percent>
       <tax_amount>0.0000</tax_amount>
       <base_tax_amount>0.0000</base_tax_amount>
       <base_discount_amount>0.0000</base_discount_amount>
       <base_row_total>1350.0000</base_row_total>
       <base_price_incl_tax>150.0000</base_price_incl_tax>
       <base_row_total_incl_tax>1350.0000</base_row_total_incl_tax>
     </data_item>
     <data_item>
       <sku>Sun_glasses</sku>
       <price>200.0000</price>
       <base_price>200.0000</base_price>
       <base_original_price>200.0000</base_original_price>
       <tax_percent>0.0000</tax_percent>
       <tax_amount>0.0000</tax_amount>
       <base_tax_amount>0.0000</base_tax_amount>
       <base_discount_amount>0.0000</base_discount_amount>
       <base_row_total>2000.0000</base_row_total>
       <base_price_incl_tax>200.0000</base_price_incl_tax>
       <base_row_total_incl_tax>2000.0000</base_row_total_incl_tax>
     </data_item>
   </order_items>
 </data_item_2>
</magento_api>

As we can see, we get back a list of items with each one representing an order, that contains all the information that we would like to use for further analysis. information like the discount that we might have applied, the taxes paid the base price of the order, etc. As we might have many order objects to retrieve, we should paginate through the results. To do that, we need to provide the “page” and “limit” parameters to our GET request.

Now that we have the results from our Magento shop, we can further process them before we are able to load them into the BI platform of our choice.

Magento Data Preparation for Snowflake

The first step, before you start ingesting your data into a Snowflake data warehouse instance, is to have a well-defined schema of your data.

Data in Snowflake is organized around tables with a well-defined set of columns, with each one having a specific data type.

Snowflake supports a rich set of data types. It is worth mentioning that a number of semi-structured data types is also supported. With Snowflake, it is possible to load data directly in JSON, Avro, ORC, Parquet, or XML format. Hierarchical data is treated as a first-class citizen, similar to what Google BigQuery offers.

There are also one notable common data type that is not supported by Snowflake. LOB or large object data type is not supported. Instead, you should use a BINARY or VARCHAR type. But these types are not that useful for data warehouse use cases.

A typical strategy for loading data from Magento to Snowflake is to create a schema where you will map each API endpoint to a table.

Each key inside the Magento API endpoint response should be mapped to a column of that table and you should ensure the right conversion to a Snowflake data type.

Of course you will need to ensure that as the data types from the Magento API might change, you will adapt your database tables accordingly. There’s no such thing as automatic data type casting.

After you have a complete and well-defined data model or schema for Snowflake, you can move forward and start loading your data into the database.

Load data from Magento to Snowflake

Usually, data is loaded into Snowflake in a bulk way, using the COPY INTO command. Files containing the data, usually in JSON format, are stored in a local file system or in Amazon S3 buckets. Then a COPY INTO command is invoked on the Snowflake instance and data is copied into the data warehouse.

The files can be pushed into Snowflake using the PUT command, into a staging environment before the COPY command is invoked.

Another alternative is to upload the data directly into a service like Amazon S3, from where Snowflake can access the data directly.

Finally, Snowflake offers a web interface as a data loading wizard where someone can visually setup and copy the data into the data warehouse. Just keep in mind that the functionality of this wizard is limited compared to the rest of the methods.

Snowflake, in contrast to other technologies like Redshift, does not require a data schema to be packed together with the data that will be copied. Instead, the schema is part of the query that will copy the data into the data warehouse. This simplifies the data loading process and offers more flexibility on data type management.

Updating your Magento data on Snowflake

As you will be generating more data on Magento, you will need to update your older data on Snowflake. This includes new records together with updates to older records that for any reason have been updated on Magento.

You will need to periodically check Magento for new data and repeat the process that has been described previously while updating your currently available data if needed. Updating an already existing row on a Snowflake table is achieved by creating UPDATE statements.

Snowflake has a great tutorial on the different ways of handling updates, especially using primary keys.

Another issue that you need to take care of is the identification and removal of any duplicate records on your database. Either because Magento does not have a mechanism to identify new and updated records or because of errors on your data pipelines, duplicate records might be introduced to your database.

In general, ensuring the quality of the data that is inserted in your database is a big and difficult issue.

The best way to load data from Magento to Snowflake and possible alternatives

So far we just scraped the surface of what can be done with Snowflake and how to load data into it. The way to proceed relies heavily on the data you want to load, from which service they are coming from, and the requirements of your use case.

Things can get even more complicated if you want to integrate data coming from different sources. A possible alternative, instead of writing, hosting, and maintaining a flexible data infrastructure, is to use a product like RudderStack that can handle this kind of problem automatically for you.

RudderStack integrates with multiple sources or services like databases, CRM, email campaigns, analytics, and more.

Sign Up For Free And Start Sending Data
Test out our event stream, ELT, and reverse-ETL pipelines. Use our HTTP source to send data in less than 5 minutes, or install one of our 12 SDKs in your website or app.
Don't want to go through the pain of direct integration? RudderStack's Magento integration makes it easy to send data from Magento to Snowflake.