Connecting Redshift to HubSpot: 2 Easy Methods

on Activate, Amazon Redshift, Amazon S3, Automation, Data Integration, Data Warehouse, ETL, Hevo Activate, HubSpot, SaaS • June 30th, 2021 • Write for Hevo

Feature image representing Data flow from Redshift to HubSpot

In today’s world, customers are expecting companies to deliver their products and services, along with a personalized user experience. To fulfill such standards, multiple companies are relying on online platforms which, along with providing enhanced customer experience, also automate the regular Business and Marketing Operations in the organization. One such online platform is HubSpot which caters to a company’s need of attracting visitors, convert potential customers into leads, and manage multiple customer relationships.

This article covers the 2 ways by which you can Connect Redshift to HubSpot and transfer your data effectively between them. This transfer of data from a Data Warehouse to an online SaaS platform is called Reverse ETL and this process has greatly empowered the use of Operational Analytics in a wide range of businesses. Tools like HubSpot, provide real-time analysis that acts as the backbone for the decision-making process in companies.

In this article, you will be briefly introduced to the Amazon Redshift Data Warehouse and the online SaaS platform HubSpot before moving on to the 2 easy methods using which you can set up a connection between Redshift and HubSpot to ensure a seamless data transfer. Moreover, the article will also help you decide which method will suit your needs better. Read along to find out how you can connect Redshift to HubSpot seamlessly.

Table of Contents

Prerequisites

You can grasp the methods to set up the Redshift to HubSpot connection provided in this article with much more ease if you have priorly covered  the following aspects:

  • Basic knowledge of Data Warehouses.
  • Basic knowledge of SaaS platforms.
  • Some experience with SQL.
  • Basic knowledge of CSV Files.
  • Basic knowledge of the concept: Integration in Data Warehouses.
  • Basic knowledge of Reverse ETL Process.

Also, you will need accounts on both Amazon Redshift and HubSpot. Moreover, you should be clear about what part of your data you need to transfer from Redshift to HubSpot.

Introduction to Amazon Redshift

Amazon Redshift Logo.
Image Source

Amazon Redshift is a Cloud-based Data Warehouse service that provides an efficient way of integrating and storing large quantities of data for analysis and manipulation.

Redshift’s architecture consists of a set of computing resources called Nodes which are further organized into some large groups called Clusters. Redshift’s main advantage is its high scalability and fast processing of queries which make it one of the most popular Data Warehouses even today. You can leverage the AWS Console or Cluster APIs to seamlessly scale up your storage and processing speed requirements by adding Nodes in just a few clicks. It is as simple as that.

To know more about Amazon Redshift visit this link.

Introduction to HubSpot

Image of HubSpot logo.
Image Source

HubSpot is an online platform that provides various functionalities like Performance Tracking, Social Media Sharing, Sales Pipeline Mapping, etc., to the Marketing and Sales team of a company and at the same time ensures that the user’s journey is an enriching experience.

An Object in HubSpot refers to the type of dataset that you are trying to import from an external source. Six kinds of Objects are supported in HubSpot: Contacts, Companies, Deals and Tickets, Products, and Notes. Each of these has its own set of columns which your Data Warehouse should contain too, then only can it be sent to HubSpot. For each Object, the columns required to fulfill the minimum requirements are:

  • Object  Contacts: First name; Last name; email ( This acts as a unique Id
  • Object Companies: Name; Company domain name( This acts as a unique Id)
  • Object Deals: Deal name; Pipeline; Deal stage
  • Object Ticket: Ticket name; Pipeline; Ticket status
  • Object Product: Name; Unit price
  • Object Note: Activity date; Note body

One thing to remember is that data can be imported into HubSpot using two different ways. The data can be imported in individual files for individual Objects: For example, a single file can be imported for Object Contacts. The second way is to import one file which contains data associated with multiple Objects: For example, data related to two different Objects Contact and Company are imported together in a single file. Both of these structures are shown in the below images.

Image representing the use of individual file for individual Objects in HubSpot.
Image Source
Image representing the use of single table for multiple Objects.
Image Source

When data has to be transferred from a Data Warehouse like Redshift to a SaaS platform such as HubSpot, the data must be structured according to certain rules and properties. This involves transforming the data from your Data Warehouse into a format supported by HubSpot. Following are the guidelines that must be followed by all files coming into HubSpot:

  • The files must be in one of the following formats: CSV, XML, or XLSX.
  • The files must contain only one sheet of data.
  • The files must be of a size less than 150 MB.
  • The files must have less than 1000 columns and 25000 rows.
  • The files must be UTF-8 encoded.
  • The files must contain a header row in which each column header matches with a property in the HubSpot object.

To know further about HubSpot visit the following link.

Ways to Connect Redshift to HubSpot

Method 1: Using CSV Files to Manually Connect Redshift to HubSpot

This method involves manually converting your Redshift data into CSV Files by using certain SQL commands. The data present in the CSV Files then has to be modified according to the format specified by HubSpot. Afterward, the data can be imported and mapped directly into HubSpot. It is a 12-step process that will also require troubleshooting certain errors manually.

Method 2: Connecting Amazon Redshift to HubSpot using Hevo Activate

Get Started with Hevo for free

Hevo Activate provides a hassle-free solution and helps you directly Connect Redshift to HubSpot without any intervention in an effortless manner. Hevo Activate is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Hevo’s pre-built integration with various data sources such as Amazon Redshift, Snowflake, Salesforce, HubSpot, etc., will take full charge of the data transfer process, allowing you to focus on key business activities. It helps transfer data from HubSpot to a destination of your choice for free.

Sign up here for a 14-day Free Trial!

Methods to Connect Redshift to HubSpot

Now, since you have an idea about both of these tools, it’s time to move on to the two methods through which you can Connect Redshift to HubSpot. You can easily connect Amazon Redshift to HubSpot by using any of the following two methods:

Method 1: Using CSV Files to Manually Connect Redshift to HubSpot

The journey of data from a Data Warehouse like Redshift to an Operational SaaS platform like HubSpot can be implemented manually by following 12 steps:

Step 1: Login to your AWS account and enable the Amazon S3 console. After that click on “Create Bucket”.

Step 2: Name the bucket according to your wish keeping in mind that the S3 bucket must have a unique name globally. After that enter a region and uncheck the box “Block all public access”. Now, click on “Create bucket”.

Image representing the process of creating a Bucket in Redshift.
Image Source

Step 3: Copy the URL for your Amazon S3 Bucket and store it in a safe location.

Step 4: Data is stored in Redshift using Clusters. So, select the Amazon Redshift Cluster from which you want to export your data. SQL clients like SQL Workbench/J, Jetbrains Datagrip, etc. are used for this export.

Step 5:  You should use the “UNLOAD” command which can help extract the results  of your SQL query into one or more S3 files. The “UNLOAD” command has a syntax similar to the following code:

UNLOAD ('select_statement')
TO 's3://object_path_prefix'
[ WITH ] CREDENTIALS [AS] 'aws_access_credentials'
[ option [ ... ] ]
where option is
{ MANIFEST
| DELIMITER [ AS ] 'delimiter_char'
| FIXEDWIDTH [ AS ] 'fixedwidth_spec' }
| ENCRYPTED
| GZIP
| ADDQUOTES
| NULL [ AS ] 'null_string'
| ESCAPE
| ALLOWOVERWRITE
| PARALLEL [ { ON | TRUE } | { OFF | FALSE } ]
UNLOAD ('SELECT id,email FROM users')
TO 's3://bucket_name/file_prefix_'
CREDENTIALS 'aws_access_key_id=KEY1;aws_secret_access_key=KEY2'
ESCAPE
PARALLEL OFF;

Step 6: You may now download this exported data in the CSV format.

Image representing the process of exporting CSV Files from Redshift.
Image Source

Now as you have the data in CSV File format, you can move on to transporting this data to HubSpot.

Step 7: Login to your HubSpot account and select the type of Objects to be imported. For example Contacts, Products, etc.

Step 8: Click on “import which is present in the upper right corner. After that click on “start an import.

Step 9: Click “file from computer”, then click on “one file and then click on “one objector “multiple objects as per the requirement.

Step 10: You can now select the type of Object that you want to import.

Image representing the options of choosing Object in HubSpot.
Image Source


Step 11: Now, click on “Choose a filewhich will allow you to browse a file stored in your system and open it in the file browser. The HubSpot Import block will now appear on your screen as follows:

Image representing File upload process in HubSpot.
Image Source

Because of the prior modification in your Data, the columns of the Data Warehouse files will now be easily mapped to the destination files in HubSpot and will look something like this:

Image representing Data Mapping in HubSpot
Image Source

Step 12: In case certain column headers don’t match the properties of HubSpot, you can either remove that column while importing or you can create new columns at the destination. Once this is over, click on “Nextand Finish import”.

After this step, all of your required data will be available in HubSpot and this will mark the completion of your data transfer process. So by using the above steps, you can manually Connect Redshift to HubSpot and transfer your required data.

Method 2: Using Hevo Activate to Connect Redshift to HubSpot

Hevo Data Logo
Image Source

Hevo Activate helps you directly transfer data from Snowflake, Amazon Redshift, etc., and various other sources to CRMs such as HubSpot, Salesforce, various SaaS applications, and a lot more, in a completely hassle-free & automated manner. Hevo Activate is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. It helps transfer data from HubSpot to a destination of your choice for free. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo Activate takes care of all your data preprocessing needs and lets you focus on key business activities, and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination.

Check out what makes Hevo Activate amazing:

  • Real-time Data Transfer: Hevo Activate, with its strong Integration with various sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
  • Secure: Hevo Activate has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Tremendous Connector Availability: Hevo Activate houses a large variety of connectors and lets you bring in data from numerous Data Warehouses and load it into Marketing & SaaS applications, such as Salesforce, HubSpot, Zendesk, Intercom, etc. in an integrated and analysis-ready form.
  • Simplicity: Using Hevo Activate is easy and intuitive, ensuring that your data is exported in just a few clicks.
  • Completely Managed Platform: Hevo Activate is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
  • Live Support: The Hevo Activate team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Limitations of using CSV Files to Connect Redshift to HubSpot Manually

The method of using CSV Files to connect Redshift to HubSpot although effective comes with its own set of limitations such as:

  • The method consists of steps that are extremely error-prone and thus your transfer process can often fail. As a result, you will be spending money on developers who have the experience of working on Redshift and HubSpot. You can check out the types of errors here that usually occur when data is imported into HubSpot.
  • HubSpot data can’t change dynamically so the changes done in the CSV File or Redshift data will not be automatically reflected in your HubSpot account and so you will need to manually add the new changes regularly for every subsequent update.
  • There will be an issue of Ambiguous Enumeration in HubSpot. This occurs when an incoming record matches one or more existing records. You will be troubleshooting this error manually every time you update your data.
  • You will have to devote a reasonable amount of time, resources, and effort to this data transfer.

Conclusion

This article provided you with two detailed approaches using which you can successfully Connect Redshift to HubSpot for Operational Analytics. Both of these methods are effective, but as mentioned above, there are various challenges and limitations that you will face if you are to take up the manual process. If you are willing to invest your time and resources in this process and have no qualms about manually troubleshooting the errors, go for the first method. Otherwise, you can opt for Hevo Activate to seamlessly transfer data from Amazon Redshift to HubSpot.

Visit our Website to Explore Hevo

Hevo Activate will automate your data transfer process, hence allowing you to focus on other aspects of your business like analytics, customer management, etc.  This platform allows you to transfer data from Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc to various CRMs and SaaS applications. It will provide you a hassle-free experience and make your work life much easier. It helps transfer data from HubSpot to a destination of your choice for free.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of learning about connecting Redshift to HubSpot in the comments section below.

Sync Amazon Redshift to HubSpot seamlessly