HubSpot to Snowflake: Move Data in Minutes

on Tutorial • February 26th, 2020 • Write for Hevo

HubSpot is one of the most widely used SaaS platforms for online marketing, combining a variety of resources into one package: for CRM, email marketing, social media and web content management, and data analytics, many users have found HubSpot to be an invaluable tool at their disposal. As intuitive and easy-to-use as HubSpot is, however, it is not without its limitations, and those who seek to understand their web traffic in more granular detail would be well-suited to consider integrating HubSpot’s insights into a data warehouse. Snowflake, a data warehouse built on AWS architecture, is one such solution, offering users who are proficient with HubSpot a comparatively simple way to learn more about their online marketing efforts. In this article, we will discuss the process of extracting, loading, and transferring data from a HubSpot portal to a Snowflake warehouse.

METHODS TO MOVE DATA FROM HUBSPOT TO SNOWFLAKE

There are many ways to move data from HubSpot to Snowflake. This blog will discuss two of the popular approaches listed below:

Method 1: Hand code Custom ETL Scripts

HubSpot exposes REST APIs that will allow you to extract data. You would need to build a code to bring data from it, clean and enrich this data to make it analysis-ready and finally, load it to Snowflake. This would involve more steps than what is mentioned here and can prove to be an effort-intensive task.

Method 2: Implement a Platform like Hevo Data, an Official Snowflake Data Integration Partner

Hevo, a Data Integration Platform can move data from HubSpot to Snowflake in a jiffy without having to write any code. This would be a comfortable route to take if you have tight deadlines and scarce engineering resources.

This blog provides an overview of both methods. You can then weigh the pros and cons and pick the path that suits your use case best. 

Method 1: Move Data from HubSpot to Snowflake using Custom Code

Step 1 – Accessing Data on HubSpot

A broad range of data exists on HubSpot, which can be viewed through the HubSpot REST API, accessible via Postman, CURL, or various HTTP clients. Using REST API calls, one can discover everything from contact lists to email subscription events, and more; each of these calls uses an authentication parameter, either an API Key or an OAuth Access Token, depending on the degree of rigor required for authentication and integration. In any case, the HubSpot API outputs JSON data, which works well with Snowflake, since the latter platform is able to incorporate JSON data without the need to create a corresponding schema, as we discuss in the next step.

Step 2 – Transforming HubSpot Data

Snowflake is able to integrate a number of data types without first defining a schema, such as XML, Parquet, or as is the case with HubSpot, JSON data. JSON data maps readily to the spreadsheet format of Snowflake, which describes web traffic events on an ordered table of rows and columns. Each of these columns corresponds directly to a HubSpot API endpoint response, but once again, where other data types must be systematically defined into a particular schema for a successful conversion, the JSON data used by HubSpot’s API has no such limitations. 

Step 3 – Loading HubSpot Data into Snowflake

Loading data from HubSpot to Snowflake is accomplished through one of four basic methods:

  1. COPY INTO – Most often, COPY INTO is the command used to load data into Snowflake, either from a local file system or from Amazon S3 buckets. In either case, this command is invoked on a Snowflake instance in order to move a copy of the data from HubSpot to a data warehouse.
  2. PUT – Alternatively, if a PUT command is invoked, data is placed in a staging area, where it can then be copied to a Snowflake data warehouse.
  3. Direct Upload – In some cases, users might choose to directly transmit data from HubSpot to an Amazon S3 bucket, where Snowflake can then access the data.
  4. Visual Wizard – For those who would like a more user-friendly experience (at the cost of some functionality), Snowflake offers a GUI to copy data from HubSpot with a point and a click.

Regardless of the particular method implemented, it is important to remember that, unlike other technologies, Snowflake does not use separable data schema, but instead, bundles the data schema with the copy query for a more streamlined data loading user experience.

Step 4 – Maintaining Data in the Snowflake Warehouse

In order to ensure that data is as accurate as possible, users must practice a constant and consistent data maintenance schedule. Every update to HubSpot can potentially invalidate pre-existing data fields, making it essential for the end-user to create UPDATE statements in the Snowflake database on a regular basis. Failure to maintain such a schedule can result in flawed, inaccurate, and outright corrupted data, which inevitably leads to much frustration and time lost on the part of the developer.

Of course, maintaining a rigorous system of manual updates can be an equally frustrating and time-consuming task. This brings us to Hevo: a software package that eliminates a great deal of the busywork involved in web traffic analytics.

HubSpot to Snowflake: Limitations of Building Custom Code

Users who are considering using a manual data transferral method, rather than a completely automated platform such as Hevo, face significant drawbacks:

  1. Effort-Intensive: All of the steps involved in moving data from HubSpot to Snowflake require a great deal of effort on the part of the end-user.
  2. Inability to Automate Changes: Any change created in HubSpot requires the user to make a corresponding change in the Snowflake data warehouse.
  3. Lack of Replication: Developers who desire a “one and done” approach to data transfer must look elsewhere, as every time data is moved, each of the steps involved in this article must be repeated once again.
  4. Data Loss/Corruption: Irrevocable data loss and corruption can result from errors in the manual loading process.

Users who are looking for a more headache-free solution may wish to try Hevo, which can get the job done in a few minutes. 

Move Data from HubSpot to Snowflake using Hevo

Users who want the simplest method to facilitate HubSpot to Snowflake ETL may want to try a Data Integration Platform like Hevo, an official Snowflake ETL partner. To understand Hevo’s simplicity, let’s first walk through the steps needed to move data from HubSpot Snowflake warehouse:

  1. Select, Authenticate and Configure HubSpot Data Source
  2. Connect your Snowflake Data Warehouse and watch data move instantly

While you sit back and relax, Hevo will take care of securely and reliably moving your data from HubSpot to Snowflake. Here are some more reasons to try Hevo: 

  1. Low Time to Implementation: Users can quickly move data with only a few clicks.
  2. Completely Managed: Hevo offers a one-stop solution to a number of developer issues. Hevo not only loads your HubSpot data to the warehouse securely but also automatically handles anomalies that may occur so that you don’t have to ever worry about your data. 
  3. Alerts/Notifications: Because changes in data are so crucial to online marketing efforts, Hevo includes a host of alert and notification types to keep users abreast of their data.
  4. No Data Loss: Hevo’s fault-tolerant system ensures that you always have complete, consistent and up-to-date data in your warehouse. 
  5. Scalability: Hevo works well with HubSpot and Snowflake, but it is equally adept at dealing with a broad spectrum of data sources. This makes Hevo a reliable partner to invest in so that your growing data needs are taken care of, on-demand.
  6. Ability to Transform Data: Hevo allows you to clean transform and enrich data both before and after loading to the warehouse. This ensures that you a one-stop solution to help you ready the data for analysis.
  7. 24/7 Support: At any time, if a user runs into an issue with data movement, Hevo staff is on hand to answer any and all of their needs.

Hevo can enable you to make sure that every second you spend is on innovation, not remediation. Hevo offers the perfect combination of ease and power, and with the vast array of data sources compatible with Hevo, users may well find that themselves using Hevo not only to move data from HubSpot to Snowflake but for a variety of other use cases as well. Sign up for a free 14-day trial so that you can determine for yourself if Hevo is right for you.

No-code Data Pipeline for Snowflake