Query HubSpot data in Snowflake

After connecting Snowflake to your HubSpot account, you can use Snowflake's Data Share to query HubSpot data from your Snowflake account.

Data lag and your Snowflake account region

You will have access to the V2_LIVE schema which is updated every 15 minutes, and the V2_DAILY schema which is updated every day.

Please note: the following views in V2_LIVE schema are only updated daily: association_definitions, owners, pipelines, and pipeline_stages.

Set up your Snowflake environment

To query your HubSpot data in Snowflake, you'll need to select the warehouse, database, and schema that you want to query from. You can either set them at the worksheet level to include them automatically in your queries, or you can manually add them.

To select your warehouse, database, and schema at the worksheet level:

• In the top row of the worksheet, click the Worksheet actions bar.
  
  
• In the pop-up window:
  • Click the Warehouse dropdown menu, then select your warehouse.
  • Click the Database dropdown menu, then select your HubSpot database.
  • Click the Schema dropdown menu, then select the schema you want to use:
    • V2_DAILY: the daily schema to query data based on tables. Data in this schema is updated once per day. Using this schema will result in faster queries, and is recommended for querying directly in Snowflake.
    • V2_LIVE: the live schema based on Secure Views of HubSpot data. Data in this schema is updated up to every 15 minutes. If you’re querying a large set of data, queries may be slow. In this case, it’s recommended to copy the data into your own table first with an ETL process, then query that table.
    
    
    

With your environment configured, you can make queries to your HubSpot data without needing to add your database or schema type to queries manually.

To manually add your database and schema type to queries, add the database name followed by the schema at the beginning of the FROM field. For example:

Data overview

Queries are constructed using SQL, and can be used to retrieve HubSpot data such as:

• Objects: standard and custom objects, such as contacts, companies, products. 
• Records: individual records, such as an individual contact or company. 
• Associations: all available association types and currently associated records.
• Events: event data for both standard HubSpot events, such as email opens, and custom behavioral events.
• Lists: HubSpot contact and company lists.
• Owners: users in your HubSpot account, in relation to the records that they're set as the owner of.
• Pipelines: deal and ticket pipelines, including pipeline stages.
• Properties: properties and their values on CRM records, including property history.

Learn more about HubSpot’s CRM data model. 

Please note: As we evolve the data share, we will introduce changes, some of them breaking changes. It is highly recommended to subscribe to HubSpot’s developer change log. These changes will always be posted to the change log in advance of any update.

Limiting and ordering returned data

When constructing queries, you can control how much data gets returned, along with the order in which it's returned, by including LIMIT and ORDER BY in your query. For example, to query all contacts with their properties, but only return three results and order them by contact ID, you could construct your query as follows:

Column data types

When querying data in Snowflake, the columns that are returned depend on the data you're querying. You can view the available columns for a given table or view in Snowflake by selecting the view in the left sidebar.

Please note:for the rest of this doc, we use the term database object when the context applies to both tables and views.

To return a specific column, include that column in the SELECT field of your query. For example, the following query will return only the OBJECTID and UpdatedAt columns:


SELECTobjectId, updatedAt
FROM objects_contacts



Below are general definitions for columns that a query might return. For other definitions not included below, see the individual view definitions in the article above.

• OBJECTTYPEID: a VARCHAR value that represents an object's type. For example, 0-1 represents the contacts object, 0-2 represents the companies object, and 2-1232 might represent a custom object in your account. In lists, this refers to the type of list (e.g., contact or company). You can query all object and event IDs in your account using the object_and_event_type_definitions view.
• OBJECTID: a BIGINT number that represents a single record of a given type of object in your HubSpot account. To identify a record, you need to use both the object type ID and the object ID.
• INGESTEDAT: the timestamp of when a row of data was added to Snowflake, represented by a TIMESTAMP_NTZ value. Not related to the logical timestamp of a create or update operation in HubSpot.
  • Due to HubSpot's internal processing, a row's ingestion timestamp may be updated even if no other columns on the row have changed.
  • When building ETL processes that copy recently changed data, it's recommended to base your queries on the ingestion timestamp. In addition, if your flow copies data daily, it's recommended to copy data from the previous two days to account for delays.
• CREATEDAT and UPDATEDAT: the logical timestamp of when data was created or updated in HubSpot, represented by a TIMESTAMP_NTZ value. These timestamps will match timestamps in HubSpot. For example, the time displayed in HubSpot for a record's create date will be the same as the timestamp in the CREATEDAT column in Snowflake. These are not directly related to the ingestion timestamp, and it's not recommended to base ETL processes on these properties.
• COMBINEDASSOCIATIONTYPE: a VARCHAR value that uniquely identifies an association definition.

Transforming Data Types

Sometimes, data is returned from a query with VARCHAR columns when it would be desirable to have them as a different type. In instances like this, it is recommended to convert the VARCHAR datatype to the desired types using Snowflake functions. For instance, properties of an object record are stored as VARCHAR datatypes in most database objects, but sometimes contain number and datetime values. Functions like:
try_to_number, try_to_timestamp_ntz can be used to convert these VARCHAR data to their NUMBER and DATETIME types respectively:


SELECT objectid, value AS amount_str, try_to_number(value) AS amount
FROM object_properties
WHERE objecttypeid='0-3' AND name='amount'
LIMIT 1



Object queries

Below, learn how to query data from specific HubSpot objects and records, along with the data that gets returned. 

Object data of different types are organized and available in two ways: 

• Individual: Each object type is stored in its own view and contains only records of the same object type, e.g. objects_contacts contains only contact records. This eliminates the need to specify an objecttypeid while querying. These views are easier to query since you don’t need to know ObjectTypeIds. They also return data in a more usable format with each row corresponding to an object and the current version of its properties. As long as you do not experience slow queries it is advisable to use these views to retrieve object data. See more in Objects_X views below. 
• Combined: All records for all object types are combined into one database object. Thus, when querying, you need to specify a filter like “WHERE objectTypeId=’0-1’. For example, you can use the following objecttypeids when querying:

Custom objects will be assigned an ID at the time of creation, and will be identified as 2-unique_ID. For example, 2-12345. Learn more about object_and_event_type_definitions.

These database objects are generally suitable if you want better query performance than what is obtainable from objects_x views. See more details in object_with_object_properties, object_properties, objects and object_properties_history below.

objects_x views

In this database object, each available object type, including custom objects, has its own specific view with each view containing only records for objects of that specific object type. These views are named following the format OBJECTS_<object type name> where <object type name> is the plural form label of the objecttype.(Note that if the object type has no plural form, then <object type name> will simply be the singular form label of the object type name concatenated with “S”).

Queries to this view return one row for each record within an object with each property of the object available as a separate column.

See below the comprehensive list of available HubSpot(that is, non-custom) object types, a brief description of the data they represent and their corresponding views:

Each database object will return the following columns:

Example 1

Retrieve the first name, last name, and lifecycle stage for all contacts.

Query:


SELECT objectId, property_firstname AS firstname, property_lastname AS lastname,property_lifecyclestage AS lifecyclestage
FROM objects_contact



Returns:

Example 2

Retrieve the deal name and stage of up to three deals.

Query:


SELECT objectId, property_dealName, property_dealStage,
property_amount AS dealAmount

FROM objects_deals

LIMIT 3

Returns:

Example 3

Retrieve the deal name and stage of the top three deals by amount.

Query:

Since deal amount data is available as a number datatype, it can be used for ordering without the need to do conversion or casting.


SELECT objectId, property_dealName, property_dealStage,
property_amount AS dealAmount

FROM objects_deals
ORDER BY property_amount DESC
LIMIT 3




Returns:

object_with_object_properties

This database object contains data from your HubSpot records. Queries to this database object return one row for each record within an objecttype (e.g., all contact records in the contacts objecttype). All properties are stored in the Properties column, which is a Snowflake Variant / JSON column. 

This view can return the following columns:

Please note: On January 25, 2022, the column, timestamp in object_with_object_properties table and view, which represents the date and time that the object record was last updated in HubSpot, will be updatedat. This is to align with how datetime data is represented in the rest of the data share.

As this is a breaking change, the new column, updatedat has been added to the table and view. For the next 90 days, these two new columns will coexist with timestamp, the columns they are seeking to replace. This gives you an opportunity to switch to using the new columns.

After 90 days, on January 25, 2022, the column timestamp will be removed from the table and view. After this date, any attempt to query the object_with_object_properties table or view using timestamp columns will fail.

Example 1

Retrieve all contacts and their properties.

Query:

Returns:

Example 2

Retrieve the deal name and stage of up to three deals.

Query:

Returns:

objects

This database object is a subset of the data in the object_with_object_properties database object. Each returned row returns the object ID for each record. This database object only returns existing records. When a record is deleted in HubSpot, it will be removed from this database object. This database object is a subset of the data in the object_with_object_properties table. Each returned row returns the object ID for each record. This view only returns existing records. When a record is deleted in HubSpot, it will be removed from this view. 

This database object can be useful if your account has a large amount of data, leading to object_with_object_properties and objects_x views performing slowly. Using this database object with the object_properties database object makes it possible to replicate the data in object_with_object_properties, while maintaining control over which properties are returned.

This database object can return the following columns:

Example 1

Retrieve all contact records.

Query:

Returns:

object_properties

This database object is a subset of the object_with_object_properties database object. Each row contains the current property value set on a given record. For example, a contact will have one row for its First name property and one row for its Last name property. If a record doesn't have a value for a property, the row will either appear with an empty string or will not appear at all.

This database object can return the following columns:

Example 1

Retrieve the first name, last name, and city properties for all contacts.

Query:

Returns:

Example 2

Retrieve the first name, last name, and lifecycle stage for all contacts, and combine them into one row per contact. This example uses the SQL PIVOT syntax.

Query:

Returns:

object_properties_history

This database object contains historical property values. For contacts, it contains the most recent 45 values. For all other objects, it contains the most recent 20 values. Displays a row for each historical property value. The current value of a property is determined by the most recent UPDATEDAT timestamp in the property's history.

This database object can return the following columns:

Example 1

Retrieve the history of a single property for a single contact.

Query:

 
Returns:

Example 2

Retrieve the current name of all contacts who have ever had a lifecycle stage of Subscriber.

Query:

Returns:

Association queries

Like objects data, associations data is organized in two ways:

• Individual: Each association type is stored in its own view and contains only records of that type. For example, associations_contacts_to_deals contain only association records between contact and deal objects. This eliminates the need to specify an associationtypeid while querying. These views are quite easy to query since they don’t require familiarity with the CRM data model in order to use them. They also return an enriched description of the association record between two objects which can be easily used in joins with objects_x views. As long as you do not experience slow queries, it is advisable to use these views to retrieve association data. See more in associations_x_to_y views below. 
• Combined: All records for all association types are combined into one database object. Thus, when querying, you need to specify a filter like “WHERE combinedassociationtypeid=’0-32’ ”. See more in associations and association_definitions below.

associations_x_to_y views

Each association type has its own view, labeled as associations_<association type name> e.g. associations_contacts_to_deals. Each view is a subset of the data in the associations database object and contains only records of a specific combinedassociationtypeid.

Each database object will return the following columns:

Example 1

Retrieve all deals associated with a specific contact.

Query:

Returns:

Example 2

Retrieve all contacts based in the Bogota timezone and their deals.

Example 3

Retrieve all contacts and the total sum of deal closed amount for each contact.

Returns:

association_definitions

This database object contains information about all of the available association types between HubSpot objects, with one row per association type. You can join this database object with the associations database object to get record-specific association information.

There can be multiple types of associations between the same objects. The main association type, which is the type displayed in HubSpot when records are associated, use the association where ISMAINASSOCIATIONDEFINITION is true.

This database object can return the following columns:

Example 1 

Retrieve the types of associations available in your account.

Query:

 
Returns:

Example 2

Retrieve the available association types between deals and companies.

Query:

Returns:

associations

This database object contains information about the associations between specific records. Each row represents an association from one object to another object. Rows may be present in this database object even though the referenced records have been deleted. You can check whether records exist by joining with the objects table.

This database object can be useful if your account has a large amount of data, leading to associations_x_to_y views performing slowly.

This database object can return the following columns:

Example 1

Retrieve all associated deals and contacts.

Query:

 
Returns: 

Example 2 

Retrieve all tickets associated with a specific contact (contact ID: 3005).

Query:

Returns:

Example 3 

Retrieve all deals associated with a specific contact (contact ID: 4464). Instead of hard-coding combinedAssociationTypeId, this example joins with association_definitions.

Query:

Returns:

Owner queries

owners

This database object contains information about the users in your HubSpot account. Users can be set as record owners, and will contain both a user ID and an owner ID, depending on their context. Both IDs can be used to identify the same user. 

This database object can return the following columns:

Example 1 

Retrieve all users. The WHERE archived = false filter removes deleted users from the returned data.

Query:

 
Returns: 

Example 2

Retrieve a specific owner by their owner ID.

Query:

Returns: 

Example 3

Retrieve all contacts and their owners. This query joins records to owners with object_with_object_properties and the contact property hubspot_owner_id.

Returns: 

Team queries

teams

Teams in HubSpot let you organize your users into groups for organizational and reporting purposes. You can have a main team and extra teams.

This database object contains information about the teams in your HubSpot account. 

This database object can return the following columns:

Example 1

Retrieve all teams.

Query:

Returns:

owner_team

This database object contains information about which owners correspond to which teams in your HubSpot account. 

This database object can return the following columns:

Example 1

Retrieve all owners for a given team.

Query:

SELECT teamId, ownerId, isTeamPrimary
FROM teams
WHERE teamId = 29584574

Returns

Pipeline queries

Below, learn how to query deal and ticket pipeline data, such as the available pipelines in your account and their stages.

You can join this data with object data for a full view of your pipelines. For example, you can query deals and their pipelines by joining the objects_deals_view or the object_with_object_properties data with pipelines data.

pipelines

This database object contains information about the deal and ticket pipelines in your account. Each returned row represents a single pipeline.

This table can return the following columns:

Example 1

Retrieve all available deal pipelines, ordered by their order in HubSpot.

Query:

 
Returns: 

Example 2

Retrieve deals within a specific pipeline. Joins object_with_object_properties and pipelines.

Query:

Returns: 

Example 3

Same as Example 2 above, retrieve deals within a specific pipeline. But, instead of using objects_deals in joins, use object_with_object_properties.

Returns: 

pipeline_stages

This view contains information about individual stages within a pipeline. Identify stages by combining object type ID, pipeline ID, and stage ID. When joining with pipelines, use the object type ID and pipeline ID.

This database object can return the following columns:

Example 1 

Retrieve pipeline stages for a deal pipeline named Sales pipeline.

Query:

 
Returns: 

Example 2

Retrieve all deals and their respective pipeline stages.

Query:

Returns: 

Property queries

property_definitions

This database object contains information about object properties and event properties in your HubSpot account. Event properties include standard events, such as email opens and clicks, and custom behavioral event properties. This information can be helpful for defining values found in object_properties and events data.

You can identify specific properties by combining their object type ID and name. You can retrieve similar information using the properties API.

This database object can return the following columns:

Example 1

Retrieve the names and descriptions for all contact properties.

Query:

 
Returns:

Event queries

Events include standard HubSpot events and custom behavioral events. Each event has its own database object, labeled as events_eventname.

You can identify events by combining their event type ID and ID. The columns that get returned depend on the event, with one column for each event property. All events will return the following columns:

Example 1

Retrieve the 50 most recent email click events.

Query:

Returns: 

Example 2

Retrieve all email click events by the contacts who clicked. This query get contact data by joining with object_with_object_properties.

Query:

Returns: 

Object_and_event_type_queries

object_and_event_type_definitions

This database object contains definitions for all objects and events available in your HubSpot account.

This database object can return the following columns:

Example 1

Retrieve all objects and events available in your HubSpot account.

Query:

 
Returns: 

Lists queries

Below, learn how to query your lists, such as the available contact and company lists and the records included in those lists.

lists

This database object contains information about HubSpot contact and company lists. Returns one row per list, and columns include list details such as list name, size, and the object type of the records it contains. Does not contain information about the individual records within each list (see list_membership). 

This database object can return the following columns:

Example 1

Retrieve all contact lists.

Query:

 
Returns: 

Example 2

Retrieve a specific contact list named Top subscribers 2021. 

Query:

Returns: 

list_membership

This database object contains information about which records are members of which lists, with one row per record in the list. For example, if you have a Blog subscribers list with 500 contacts, the database object will return 500 rows. 

Only contains list IDs and object IDs. For more information, join with the lists database object.

This database object can return the following columns:

Example 1

Retrieve the IDs of all contacts in the Top subscribers 2021 list.

Query:

 
Returns: 

Example 2

Retrieve the first and last names of details of contacts in the Top subscribers 2021 list.

Query:

Returns: 

FAQs

I want to generate data similar to a report from the Hubspot app, how do I go about that?

Generally, you would want to look at the columns present in the report you are comparing with. If the report visualization is not tabular, converting it to a table will show the columns the report contains. These columns are usually properties of an object type or an event type. Next step is to figure out the object type property a column corresponds to. You can do this by querying property_definitions:

There might not always be an exact match because some property names are usually resolved to a more descriptive name on the app. In cases like this, doing a fuzzy search using the LIKE operator on the label(or even the description) column in property_definitions can help.

After determining which objecttype the column belongs to, it’s trivial to determine what to query. You can simply search for the object_x view that corresponds to the type.

How do I link this data type to another, e.g how to retrieve forms for a campaign?

If you need to link two or more different records to each other, take a look at associations. Associations represent the relationships between objects in the HubSpot CRM. All different associations possible between two types are listed in association_definitions. In the example above, to know all the forms a campaign is linked to, you need to query ASSOCIATIONS_CAMPAIGNS_TO_FORMS:

How do I know the status of the HubSpot-Snowflake integration?

To receive real-time status updates about outages and issues for the HubSpot-Snowflake integration, query the data_share_status view:

SELECT * FROM hubspot.public.data_share_status
LIMIT 1

The results will be displayed as:

This view provides updates on the integration status in reverse chronological order. Integrating the results of this query into a dashboard will help you track the integration's performance and detect any downtime or outages.