Ultimate Guide to HubSpot Snowflake Integration: 2 Easy Methods

• June 29th, 2021

Digital marketing is one of the most popular ways used by businesses and Marketers to effectively advertise their products and services in today’s business world. Companies have learned that the Internet is the ideal location to attract new clients for their goods and services. This data is usually stored in Cloud Data Warehouses and for some applications could require to be imported into SaaS applications. Snowflake is one such Cloud Data Warehouse solution and HubSpot is one such SaaS-based CRM service that is used by various organizations and these platforms could require an exchange of data. 

In this article, you will be introduced to Snowflake, HubSpot, and their key features. You will learn about 2 easy methods to implement HubSpot Snowflake integration. 

Table of Contents

Introduction to Snowflake 

Snowflake Logo - HubSpot Snowflake

Snowflake is one of the most powerful Cloud Data Warehouse solutions currently available. It enables users to leverage the Cloud Architecture not only to store but also to distribute data in real-time. The system’s adaptability is assured by using new SQL query engines and bespoke architecture designed specifically for its Cloud Service. Since every activity is done in the Cloud, this Cloud Data Warehouse platform is excellent for organizations that do not want to have dedicated in-house resources for setup, maintenance, and support for in-house servers.

Snowflake delivers best-in-class speed even when dealing with massive amounts of data, and it can simply scale up or down to meet the needs of the user. Snowflake can ingest, store, and query a wide range of Structured and Semi-Structured Data, including CSV, XML, JSON, AVRO, and others. You may query this data in a completely relational way using ANSI, ACID-compliant SQL.

Key Features of Snowflake 

Snowflake houses the following features:

  • Cloud Service Provision: Snowflake Data Warehouse is entirely hosted on Cloud infrastructure provided by Amazon Web Service, Microsoft Azure, and Google Cloud, with the ability to pick the geographic area where the data is kept. You can learn more about Cloud Business intelligence here.
  • High Scalability: The user is given the flexibility to scale up and down quickly in accordance with company needs, making it more cost-effective.
  • Focus on Security: In addition to data storage security features such as encryption, there are a variety of security measures to limit data access, such as restricting certain IP addresses, using two-factor authentication, and so on.
  • Low Administrative Requirements: Snowflake is offered as a Software-as-a-Service [SaaS], therefore there is a minimal need to set up IT infrastructure or maintain an IT administrator for the deployment.
  • Support for Data Sharing: It’s simple to share data with other Snowflake users. Special reader accounts can be created for people who are not on the platform to have access to the shared material.

More information regarding Snowflake can be found here

Introduction to HubSpot 

HubSpot Logo - HubSpot Snowflake

HubSpot offers a set of fully managed apps for managing a company’s whole Customer Journey. It helps companies promote their products, measure how leads interact with marketing materials, enroll leads as customers, manage their purchase experience, and provide customer service as long as they stay.

HubSpot’s main selling point is that it provides an All-in-One Marketing platform that helps clients to expand their businesses and meet evolving marketing needs. HubSpot takes pride in providing one of the best user experiences of any Marketing Automation software since its user interface (UI) is very clean and easy to pick up. HubSpot’s school also provides a number of resources to help users get up to speed. It provides free courses and lessons on Inbound Marketing, SEO, Social Media Marketing, and other topics, as well as how to utilize HubSpot to accomplish its goals.

HubSpot offers three pricing options: Starter, Professional, and Enterprise. Each product offering becomes more powerful as you advance through the tier structure. Large organizations, for example, will benefit from the Enterprise package, which covers everything from hierarchical teams to revenue attribution. Despite this pricing structure, HubSpot offers a free tier with CRM and marketing capabilities for Email Marketing, Ad Management, Landing Pages, and more. Marketing Hub, Sales Hub, and Service Hub are the three apps used by HubSpot to do this.

Key Features of HubSpot

HubSpot has the following features:

  • Actionable Metrics: All of the Reporting and Marketing KPIs are gathered in one place. You find to learn about building actionable Dashboards here.
  • Easy User Interface: HubSpot has an excellent user interface that is particularly user-friendly for beginners.
  • Free Access to Tools: HubSpot offers free access to a wide range of tools as well as platform training. Despite its long history of being linked with small and midsize enterprises, HubSpot is gradually introducing new features that cater to larger clients’  business needs (SMBs).
  • Increased Efficiency: Automated emails and workflows can help you save time and money.
  • Funnel Automation Support: You can set lead nurturing on autopilot with email drip campaigns. Automate your Email Marketing to drive more quality leads deeper into the Sales Funnel. You can learn about Data Automation here.
  • CRM Integration: With HubSpot’s CRM software, you can gain a better understanding of your prospects and approach them more effectively.
  • Integration with Service Hub: HubSpot’s Service Hub integrates with your CRM and lets you manage built-in service requests. HubSpot is a marketing tool that is both straightforward and simple to use. You can learn more about Service Hub and integration with Magento here.

Official documentation about HubSpot can be found here

Ways to Set up HubSpot Snowflake Integration

Method 1: HubSpot Snowflake Integration using Manual Method

Under this method, the initial step is to export data from Snowflake manually using SQL Queries and is then modified and transformed according to the requirements of the HubSpot platform. Subsequently, these files are then imported into HubSpot manually.

Method 2: HubSpot Snowflake Integration using Hevo Activate

Get Started with Hevo for free

Hevo Activate provides a hassle-free solution and helps you directly set up HubSpot Snowflake Integration 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 unique line of code. Hevo Activate’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!

Prerequisites 

  • Active HubSpot Account. 
  • Active Snowflake Account. 
  • Working knowledge of Database Management Systems.
  • Working knowledge of SQL and SQL Queries.

Methods to Implement HubSpot Snowflake Integration 

Method 1: HubSpot Snowflake Integration using Manual Method

  1. HubSpot Snowflake Integration: Requisite Structure of the Import Files 
  2. HubSpot Snowflake Integration: Import File Requirements
  3. HubSpot Snowflake Integration: Import Property Requirements
  4. HubSpot Snowflake Integration: File Structure Considerations for Multiple Objects with the Association
  5. HubSpot Snowflake Integration: Steps for Exporting Records From Snowflake
  6. HubSpot Snowflake Integration: Steps for Importing Records Into HubSpot

1. HubSpot Snowflake Integration Requisite Structure of the Import Files 

Importing helps you generate entries and update your database, whether HubSpot is your first CRM or you’re switching from another. Import files are data spreadsheets that are used to structure the relationships and procedures in your company. Before you begin importing, think about the structure of the files that you might need to import from Snowflake into HubSpot. The details of certain aspects have been provided below: 

  • CRM Object: Contacts, companies, transactions, and tickets are examples of several types of relationships or processes that your company has. An Object is the type of dataset you’re importing into HubSpot.
  • Record: A single instance of an item (for example, a contact record named “Tom Smith”). Each row of your file represents one Object record in a single object import file. If you import several objects into one file, each row represents records that are linked to one another.
  • Property: It is a field established to keep track of your records’ details. During an import, properties will match the column headings in your file.

2. HubSpot Snowflake Integration Import File Requirements

All files that are loaded into HubSpot must meet the following requirements:

  • The format should be a .csv, .xlsx, or .xls file.
  • Contain all the data in only one sheet.
  • It must contain less than 250,000 rows and 1,000 columns.
  • The size of the file should be smaller than 150MB.
  • A header row should be included in the imported file, with each column heading corresponding to a HubSpot attribute. You may check if a default property for your header already exists or add a new property before importing. 
  • If foreign language characters are used, the file format should be UTF-8 encoded.

3. HubSpot Snowflake Integration Import Property Requirements

Your file must have the following columns, depending on the Object(s) you’re importing:

  • Contacts: First name, or Last name, or Email (unique identifier)
  • Companies: Name or Company Domain Name (unique identifier).
  • Deals: Deal name, Pipeline, and Deal stage.
  • Tickets: Ticket name, Pipeline, and Ticket status.
  • Products: Name, and Unit price.
  • Notes: Activity date, and Note body.
  • For properties containing a date, your date values must be formatted as one of:
mm/dd/yyyy (e.g. 10/28/2020)
dd/mm/yyyy (e.g. 28/10/2020) or
yyyy/mm/dd (e.g. 2020/10/28)
  • Unique identifier: It’s a property value used by HubSpot to identify each entry as unique. To associate two separate records (e.g., import “John Smith” and associate him with his firm “Smith Inc.”) or prevent producing duplicate records (e.g., two entries for “John Smith”), you need a unique identifier in an import. When importing data into HubSpot, the following unique IDs are used:
    • Email: It refers to the contact’s email address. When importing and connecting contacts with another Object via an import, this is necessary. Use the Contact ID to update and associate existing contact information if you don’t have contact email addresses.
    • Company Domain Name: It refers to a company’s website domain (e.g., example.com). This is necessary when using an import to associate companies with another item. Use the Business ID to update and associate existing company data if you don’t have company domain names.
    • [Object] ID: HubSpot assigns a one-of-a-kind property value to each entry. Each record will have an [Object] ID if you export existing records from HubSpot. This field is necessary for importing and associating existing deals or tickets with another object type, but it may also be used as a unique identifier for contacts and firms if you don’t have emails or domain names.
    • Map Columns: You’ll be requested to match the column headings in your file to HubSpot properties at this point in the import process.

4. HubSpot Snowflake Integration File Structure Considerations for Multiple Objects with the Association  

Objects can be imported and associated either in a single file or in two distinct files, each representing one Object.

Considerations for Importing Multiple Objects in One File

To import and associate numerous Objects in a single file, including the records you want to associate in the same row. The column headings in these sample files reflect common use cases, but you may mix and match objects by changing them with the attributes of another object.

You can find the sample here.

Considerations for Importing Multiple Objects in Two Files

Each file represents an object when importing and associating two files. Include a common column in both files to specify which records should be connected. Each row in this column should have a unique value in one of your files. Use those values to specify which record each row should be connected to within the other file.

You can find the sample file here

5. HubSpot Snowflake Integration Steps for Exporting Records From Snowflake

The procedure for unloading data into files is the same as the procedure for loading data into files, but in reverse:

HubSpot Snowflake Integration Step 1: To transfer data from your Snowflake database table into one or more files in a Snowflake Instance or external stage, users must use the COPY INTO location> command.

COPY INTO [<namespace>.]<table_name>
     FROM { internalStage | externalStage | externalLocation }
[ FILES = ( '<file_name>' [ , '<file_name>' ] [ , ... ] ) ]
[ PATTERN = '<regex_pattern>' ]
[ FILE_FORMAT = ( { FORMAT_NAME = '[<namespace>.]<file_format_name>' |
                    TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]

HubSpot Snowflake Integration Step 2: After that, you’ll need to download the file from the stage, which may vary depending on the Cloud Data Warehouse platform you’re using:

  • Use the GET command to get the data file from a Snowflake stage (s).
  • To obtain the data file from Amazon S3, utilize the interfaces/tools supplied by Amazon S3 (s).
externalLocation (for Amazon S3) ::=
  's3://<bucket>[/<path>]'
  [ { STORAGE_INTEGRATION = <integration_name> } | { CREDENTIALS = ( {  { AWS_KEY_ID = '<string>' AWS_SECRET_KEY = '<string>' [ AWS_TOKEN = '<string>' ] } } ) } ]
  [ ENCRYPTION = ( [ TYPE = 'AWS_CSE' ] [ MASTER_KEY = '<string>' ] |
                   [ TYPE = 'AWS_SSE_S3' ] |
                   [ TYPE = 'AWS_SSE_KMS' [ KMS_KEY_ID = '<string>' ] |
                   [ TYPE = NONE ] ) ]
  • To obtain the data file from Azure, utilize the interfaces/tools supplied by Microsoft Azure (s).
externalLocation (for Microsoft Azure) ::=
  'azure://<account>.blob.core.windows.net/<container>[/<path>]'
  [ { STORAGE_INTEGRATION = <integration_name> } | { CREDENTIALS = ( [ AZURE_SAS_TOKEN = <string> ] ) } ]
  [ ENCRYPTION = ( [ TYPE = { 'AZURE_CSE' | NONE } ] [ MASTER_KEY = '<string>' ] ) ]

6. HubSpot Snowflake Integration Steps for Importing Records Into HubSpot 

HubSpot Snowflake Integration Step 1: Navigate to your contacts, companies, deals, or tickets in your HubSpot account.

Click Import in the top right corner.

HubSpot Snowflake Integration Step 2: You need to start an import by clicking the Start button.

HubSpot Snowflake Integration Step 3: Subsequently, Click Next after selecting File from Computer and click Next after selecting one file. Further, you can also narrow down your selection by clicking Next after selecting one thing. In this section, you can also choose an item from your import file.

HubSpot Snowflake Integration Step 4: Users must choose your import file in the dialogue box. If you’re utilizing the Object ID to deduplicate your contacts, companies, deals, tickets, goods, or custom objects, choose the checkbox. Click Next in the bottom right corner.

Uploading files window - HubSpot Snowflake
Image Source: https://knowledge.hubspot.com/crm-setup/import-objects

HubSpot will match the columns in your file to the properties of your selected Object based on the header and the property name on the Map columns in your file to [object] properties screen. In the Glimpse Information column, you’ll see a preview of the property values from your spreadsheet.

HubSpot Snowflake Integration Step 5: Click the corresponding selection menu in the HubSpot Property column if any columns do not match an existing property. You can choose from the following options:

Columns of a imported object or file - HubSpot Snowflake
Image Source: https://knowledge.hubspot.com/crm-setup/import-objects
  • Select an existing property to which the column should be mapped.
  • Click “Create a New Property” to create a new custom property, then configure it in the right panel. This new custom attribute will now be mapped to the data in the column.
  • Select the “Don’t Import Column” to avoid importing this column. Select the “Don’t Import Data” in the unmatched columns checkbox in the bottom right to avoid importing data from all unmatched columns.

HubSpot Snowflake Integration Step 6: If you’re attempting to deduplicate your contact or company data using email or a corporate domain name, follow these steps:

  • If you don’t want the import to overwrite records with existing values for a particular property, click the “Don’t Override Existing Value” checkbox.
  • Select the checkbox at the top of the table to prevent the import from overwriting all properties contained in the import.
  • The import will not update that property for any records that have the value if the checkbox is selected. New records will be created, as well as existing records that have no value for that property.
item properties - HubSpot Snowflake
Image Source: https://knowledge.hubspot.com/crm-setup/import-objects

Click Next once all columns have been mapped to property or set to Don’t import column.

Enter an Import name on the details screen.

HubSpot Snowflake Integration Step 7: If you’re importing contacts, follow these steps:

  • Select the Create a list of contacts from this import checkbox to automatically create a list when the contacts are imported.
  • Select the checkbox to accept that contacts expect to hear from you and that your import file does not include a bought list. More information about HubSpot’s acceptable use policy may be found here.
  • Use the “Set the Legal Basis for Processing a Contact’s Data” dropdown box to pick a lawful basis of processing if you’ve enabled General Data Protection Regulation (GDPR) capability in your account.
  • Use the “Date Format” dropdown menu to ensure how the date values in your spreadsheet are formatted if you’re importing a file containing a Date attribute.
  • After you’ve chosen your import choices, click Finish import in the bottom right corner.

Limitations of  HubSpot Snowflake Integration using Manual Method

  • In this method that data requires manual extraction and importing which would mean a separate hardware setup would be required to be operated and managed locally or on the Cloud. 
  • The transfer of data will not be in real-time and would require manual effort for repeating the process whenever some updates are applied to the source. 
  • Technical knowledge is required for carrying out the process manually and the chances of error due to human intervention are high.
  • The structure of Objects being imported needs to comply with HubSpot requirements which could require additional processing and transformations. 

Method 2: HubSpot Snowflake Integration using Hevo Activate

Hevo Banner - HubSpot Snowflake

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.

Conclusion

In this article, you learned about Snowflake, its key features, HubSpot, its key features, and 2 easy steps to implement HubSpot Snowflake integration. Prerequisites for the process and the limitations of using the manual method were also provided. It also provided in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently.

Visit our Website to Explore Hevo

These methods, however, can be challenging especially for a beginner & this is where Hevo Activate saves the day. Hevo Activate. It helps you directly transfer data from a source of your choice, such as Snowflake, Amazon Redshift, etc., to any SaaS application, CRMs, 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. It helps transfer data from HubSpot to a destination of your choice for free.

If you are interested in learning about implementing Marketing Automation in Hubspot, you can find the guide here.

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

Share your experience of learning about the HubSpot Snowflake! Let us know in the comments section below!

Sync your Data to HubSpot Seamlessly