Videos

How to Import HubSpot Data into Snowflake Using Matillion ETL for Snowflake

Matillion-HubSpotMatillion uses the Extract-Load-Transform (ELT) approach to deliver quick results for a wide range of data processing purposes, including everything from customer behaviour analytics, financial analysis, and even reducing the cost of synthesising DNA. The HubSpot Query component presents an easy-to-use graphical interface, enabling you to connect to HubSpot and pull data into Snowflake.

Customers typically use the component to measure the effectiveness of their marketing by combining HubSpot data with other data sources, such as sales data, to get a holistic view.

This self-contained connector is included within the scope of a standard Matillion license, so there are no additional software installations required or associated costs.

Video

Watch our tutorial video for a demonstration on how to set up and use the HubSpot Query Component in Matillion ETL for Snowflake.

 

 

Authentication

The first step in configuring the HubSpot Query component is to provide Matillion with Authentication to HubSpot.

The Matillion HubSpot component requires OAuth to be setup to authenticate access. Clicking on the 3 dots next to the Authentication property will bring a pop up box showing all available HubSpot OAuth set up in Matillion:

 

Matillion-ETL-Hubspot

 

Data Source

Next, identify what data is to be loaded into Snowflake from the Data Source drop down.

 

Matillion-ETL-Hubspot-DataSource

Data Selection

In the Data Selection area choose the required fields from the data source. An example list of the fields available in the Data Source object selected, including any custom objects, is in the image below. This will form the new table which is created in Snowflake.

 

Matillion-ETL-Hubspot-DataSelection

 

Data Source Filter

Use a filter to filter the returned data if required.

Running the HubSpot Query

Before you run the component, give the Target Table a name. This is the name of the new table which will be created to write the data into Snowflake. Additionally, specify a S3 Staging Area; this is a S3 bucket which will temporarily store the results of the query before loading it into Snowflake.

The HubSpot Query component also has a Limit property, which can be used to force an upper limit on the number of records returned.

Once configured correctly, the border on the HubSpot icon will turn green.

To query your data and bring it into Snowflake run the Orchestration job manually or by using the Scheduler.

 

Matillion-ETL-Hubspot-RunQuery

 

Advanced Mode

The HubSpot Query component offers an “Advanced” mode instead of the default “Basic” mode.

 

Matillion-ETL-Hubspot-AdvancedMode

 

In Advanced Mode, you can write a SQL-like query over all the available fields in the data model. This is automatically translated into the correct API calls to retrieve the data requested.

 

Matillion-ETL-Hubspot-Advanced-Sql

 

Transforming the Data

Perform transformation jobs, such as combining with ERP data, once the required HubSpot data is brought into Snowflake.

 

Matillion-ETL-Hubspot-TransformationJob

 

In this way, you can build out the rest of your downstream transformations and analysis, taking advantage of Snowflake’s power and scalability.

Useful Links

HubSpot Query Component in Matillion ETL for Snowflake
Component Data Model
OAuth Set Up
Integration information
Video

Want to try the HubSpot integration component in Matillion ETL for Snowflake? Arrange a free 1-hour training session now, or start a free 14-day trial.