Snowflake to HubSpot Reverse ETL: 2 Easy Ways

on Reverse ETL • June 20th, 2022 • Write for Hevo

Snowflake to HubSpot - Featured Image

Acting as a one-stop solution for sales, marketing, and service teams across businesses globally, HubSpot has become one of the popular CRM platforms. However, to get a complete picture of your customer and to better understand their needs, it is essential to consolidate your HubSpot Data from your data stored in Data Warehouses like Snowflake. You can manually set up the Snowflake to HubSpot connection using CSV files or automate the process using third-party tools.

In this article, you will learn how to easily set up the Snowflake to HubSpot Connection in 2 simple methods.   

Table of Contents

Why Connect Snowflake to Hubspot?

HubSpot’s multiple functionalities it is widely used as a CRM, Sales platform, inbound marketing platform, or even for content management, marketing automation, email marketing, and social media marketing. But to get to a more real-time & complete view of your customer, you need all the data stored in your Data Warehouse like Snowflake. By setting up Snowflake HubSpot Connection, you can empower your Support, Sales & Marketing Teams with insightful data stored in Snowflake. This includes the unique data models you built-in Snowflake, Product Usage Data, and the Events data. 

How to Set Up Snowflake to HubSpot Connection? 2 Easy Ways

You can set up Snowflake to HubSpot Connection using the following 2 methods:

Method 1: Manually set up Snowflake to HubSpot Connection Using CSV Files 

If you rarely need to extract data from Snowflake, you can manually set up the Snowflake to HubSpot Connection. You can export your Snowflake Data as a CSV file and upload it to HubSpot. To achieve this, you can follow the simple steps given below:

Step 1: Export Snowflake Data as a CSV File

Snowflake HubSpot Integration Via COPY INTO & GET Command

Snowflake allows you to execute a simple SQL Command to bulk export your data to a Cloud Storage that includes a Snowflake Storage, Amazon S3 & Azure. For this article, Snowflake storage is selected. 

  • Step 1: You can consider the following COPY INTO command syntax to achieve this:
COPY INTO { internalStage }
     FROM { [<namespace>.]<table_name> | ( <query> ) }
[ PARTITION BY <expr> ]
[ FILE_FORMAT = ( { FORMAT_NAME = '[<namespace>.]<file_format_name>' |
                    TYPE = { CSV | JSON | PARQUET } [ formatTypeOptions ] } ) ]
[ copyOptions ]
[ VALIDATION_MODE = RETURN_ROWS ]
[ HEADER ]

Where,

internalStage ::=
    @[<namespace>.]<int_stage_name>[/<path>]
  | @[<namespace>.]%<table_name>[/<path>]
  | @~[/<path>]

Consider the following example, 

copy into @new_stage/result/data_ from (select * from products)
   file_format=(format_name=CSV  compression='gzip');

Here, data is being replicated to an internal stage named new_stage from the products table in a CSV format. 

  • Step 2: You can now use the GET command to download the CSV file into your local system. You can execute the GET command by following the syntax given below:
GET internalStage file://<path_to_file>/<filename>
    [ PARALLEL = <integer> ]
    [ PATTERN = '<regex_pattern>'' ]

Where:

internalStage ::=
    @[<namespace>.]<int_stage_name>[/<path>]
  | @[<namespace>.]%<table_name>[/<path>]
  | @~[/<path>]

Consider the following example that uses the GET command to download files to your local directory(for windows users). 

get @new_stage/result/data_ file://C:tempload  
Snowflake to HubSpot Integration using SnowSQL

SnowSQL is a Command-Line tool that you can use to interact with Snowflake. You can simply run SQL queries and execute all DDL, DML, loading, and unloading operations. SnowSQL is suitable if you only need to download CSV files of less than 50 Mb i.e around 250K rows. If not done already, you can follow the simple instructions provided on the Official Snowflake website to install SnowSQL on Linux, macOS, or Windows

  • You can now login into SnowSQL using your Snowflake account credentials.
$ snowsql -a <account_identifier> -u <user_name>
  • After logging in successfully, you can execute the following command to export your data in a CSV file. Here, you need to provide your details such as connection, database, schema name, and query from which you are extracting the data.
snowsql -c, new_database_connection, -d new_database_name -s public -q "SELECT * FROM new.public.people", -o output_format=csv -o header=false -o timing=false -o friendly=false  > new_file.csv

Step 2: Upload CSV File into HubSpot

For setting up Snowflake HubSpot Connection, you can manually import data from .csv files. HubSpot can automatically detect the following header rows from your files: 

  • Contacts: First name, Last name, and Email.
  • Companies: Company domain name.
  • Deals: Deal name, Pipeline, Deal stage.
  • Tickets: Ticket name, Pipeline, Ticket status.
  • Products: Name, Product price.
  • Notes: Activity date, Note body.

To complete this process to set up Snowflake HubSpot Connection, you can follow these easy steps to upload your CSV file data:

  • Step 1: Log in to your HubSpot account and navigate to Contacts > Import.
  • Step 2: Click on the Start an Import button.
Snowflake HubSpot - Start an Import button
Image Source
  • Step 3: Select the File from Computer option to import data from your CSV files.
  • Step 4: Now, select the option according to the number of files you want to upload. Choosing the multiple file options means you have different files for contacts, deals, products, companies, etc.
  • Step 5: Click on the Single Object or Multiple Objects option based on your data file and click on the Next button.
  • Step 6: Select the Objects you want to upload.
Snowflake HubSpot - Select Objects
Image Source
  • Step 7:  Upload the file from your system & click on the Next button.  
Snowflake HubSpot - Upload Your Files Logo
Image Source
  • Step 8: Now, you can check if HubSpot correctly recognized your field properties and mapped the data accurately. Click on the Next button once you are done.
  • Step 9: You can now Name your import and then, click on the Finish Import button to complete the process to set up Snowflake HubSpot Connection.

Empower business teams with data driven actionables using Hevo Activate

Hevo Activate syncs data from your warehouse – Snowflake, Redshift, BigQuery – into Business Applications – HubSpot, Salesforce, Zendesk, Google sheets and many more. Check out our growing list of integrations.

Hevo Activate features –  SQL Query Editor, Custom Data Synchronization, Incremental Updates, Intelligent Data Type Conversion -helps business teams get accurate data without writing complicated Python Scripts. 

Make faster and smarter decisions by operationalizing your product and customer data together – analyzing user journeys, and creating truly personalized experiences for your customers.

Enrich your applications with trusted data in your warehouse. Get started with Hevo Activate for reverse ETL today!

Method 2: Using Hevo Activate to Set Up Snowflake to HubSpot Connection

Snowflake HubSpot - Hevo Banner
Image Source

Hevo Activate helps you directly transfer data from Snowflake to HubSpot in just a few clicks.  You can seamlessly replicate data from Data Warehouses like Google BigQuery, Amazon Redshift & Postgres to CRMs such as Salesforce, HubSpot, various SaaS applications like Google Sheets, 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 sources but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. 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 more 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 Sync: Hevo Activate 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 Marketing & SaaS applications, databases, etc. such as HubSpot, Marketo, Salesforce, Redshift, 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
Sign up here for a 14-Day Free Trial!

With Hevo Activate, you can easily set up Snowflake to HubSpot Connection in 2 simple steps:

  • Step 1: To set up Snowflake to HubSpot Connection, you can first configure your Snowflake Warehouse by providing your Snowflake database credentials such as your authorized Database User and Password, along with information about your Account Name & Region. You will also need to provide a unique name for your Warehouse. 
Snowflake HubSpot - Configure Your Snowflake Warehouse
Image Source
  • Step 2: To complete the Snowflake to HubSpot Integration, you can choose Hubspot as your Target
Snowflake HubSpot - Select HubSpot as Target
Image Source
  • Step 3: Click on the + ADD HUBSPOT ACCOUNT button connecting snowflake and HubSpot.
Snowflake HubSpot - Add Hubspot Account
Image Source
  • Step 4: Now, select your HubSpot Account and click on the Choose Account button. Click on the Connect App button and confirm the authorization to proceed ahead to integrate snowflake with HubSpot.
Snowflake HubSpot - Connecting Hevo to HubSpot
Image Source
  • Step 5: Finally, provide a unique Target Name for HubSpot and click on the Continue button.
Snowflake HubSpot - Add Hubspot target
Image Source

This completes your simple process to set up Snowflake to HubSpot Connection using Hevo Activate.

Conclusion

In this article, you learned how to effectively set up Snowflake to HubSpot Connection using 2 different methods. For a 360 customer profile, you can set up a Snowflake HubSpot Connection and allow your business teams to leverage the power of Operational Analytics. If you rarely need to import data into HubSpot from Snowflake, you can use the CSV files. However, if you need real-time data transfer with complex transformations, you can opt for a Cloud-Based Reverse ETL tool like Hevo Data!  

Visit our Website to Explore Hevo

Hevo Activate helps you directly transfer data from a source of your choice such as Google BigQuery, Snowflake, Amazon Redshift, etc., to any SaaS application like Google Sheets, CRMs like HubSpot, Salesforce, etc., in a fully automated and secure manner without having to write the code repeatedly. It will make your life easier and make data migration hassle-free. It is user-friendly, reliable, and secure.  

Hevo, with its strong integration with CRM Tools like HubSpot, and Salesforce, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of setting up the Snowflake to HubSpot Connection! Share your thoughts with us in the comments section below.

Sync your Data to HubSpot Seamlessly