Skip to content
Reports

Query HubSpot data in Snowflake

Last updated: November 8, 2021

Applies to:

Operations Hub Enterprise

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

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.
  • 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.
  • Events: event data for both standard HubSpot events, such as email opens, and custom behavioral events.
  • Lists: HubSpot contact and company lists (BETA).

Learn more about HubSpot’s CRM data model.  

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.

    snowflake-worksheet-settings0
  • 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_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. 
      • V2_DAILY (BETA): 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.

      snowflake-worksheet-settings-window0

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:


SELECT objectTypeId, objectId
FROM hubspot_share_name.V2_LIVE.object_with_object_properties
WHERE objectTypeId = '0-1'

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.
  • 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.
  • Events: event data for both standard HubSpot events, such as email opens, and custom behavioral events.
  • Lists: HubSpot contact and company lists (BETA).

Learn more about HubSpot’s CRM data model.  

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:


SELECT objectTypeId, objectId, properties
FROM object_with_object_properties
WHERE objectTypeId = '0-1'
ORDER BY objectId
LIMIT 10;

Object queries

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

You can use the following object IDs when querying:

Object name ObjectTypeId
Contacts 0-1
Companies 0-2
Deals 0-3
Engagement 0-4
Tickets 0-5
Line items 0-8

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.

object_with_object_properties

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

This view can return the following columns:

Column name Type Description
OBJECTTYPEID Varchar The ID of the object type (e.g., contacts is 0-1).
OBJECTID Number The ID of the record (e.g., a contact record with an ID of 123752).
PROPERTIES Variant The record's property values.
UPDATEDAT Timestamp The date and time that the record was last updated in HubSpot. Learn more about column data types.
INGESTEDAT Timestamp The date and time that the data When the data was last ingested by Snowflake. 

Example 1

Retrieve all contacts and their properties.

Query:


SELECT objectTypeId, objectId, properties
FROM object_with_object_properties
WHERE objectTypeId = '0-1'


Returns:

OBJECTTYPEID OBJECTID PROPERTIES
0-1 38960439 {"createdate": "1504656970152"...}
0-1 321740651 { "createdate": "1590183081949"...} 
0-1 98392101 { "createdate": "1533566477279"...}
... ... ...

Example 2

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

Query:


SELECT

objectId,
properties:dealname::string AS dealname,
properties:dealstage::string AS dealstage,
properties:amount::number AS dealamount
FROM object_with_object_properties
WHERE objectTypeId = '0-3'
LIMIT 3;


Returns:

OBJECTID DEALNAME DEALSTAGE DEALAMOUNT
38960439 Deal name 1
2021-10-12 13:49:35.219
2021-10-12 13:49:35.219
321740651 Deal name 1
2021-10-12 13:49:35.219
2021-10-12 13:49:35.219
98392101 Deal name 1
2021-10-12 13:49:35.219
2021-10-12 13:49:35.219

objects

This view 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 view can be useful if your account has a large amount of data, leading to object_with_object_properties performing slowly. Using this table with the object_properties table makes it possible to replicate the data in object_with_object_properties, while maintaining control over which properties are returned.

This view can return the following columns:

Column name Type Description
OBJECTTYPEID Varchar The ID of the object type (e.g., contacts is 0-1).
OBJECTID Number The ID of the record (e.g., a contact record with an ID of 123752).
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. Learn more about column data types.

Example 1

Retrieve all contact records.

Query:


SELECT objectTypeId, objectId
FROM objects
WHERE objectTypeId = '0-1'


Returns:

OBJECTTYPEID OBJECTID
0-1 36721864
0-1 103580363
0-1 56047670
... ...

object_properties

This table is a subset of the object_with_object_properties table. 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 apear with an empty string or will not appear at all.

This table can be useful if your account has a large amount of data, leading to object_with_object_properties performing slowly. Using this table with the object_properties table makes it possible to replicate the data in object_with_object_properties, while controlling which properties are returned.

This view can return the following columns:

Column name Type Description
OBJECTTYPEID Varchar The ID of the object type (e.g., contacts is 0-1).
OBJECTID Number The ID of the record (e.g., a contact record with an ID of 123752).
NAME Varchar The name of the property.
VALUE Timestamp The value of the property.
UPDATEDAT Timestamp The date and time that the record was last updated in HubSpot. Learn more about column data types.
INGESTEDAT Timestamp The date and time that the data When the data was last ingested by Snowflake. 

Example 1

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

Query:


SELECT objectTypeId, objectId, name, value
FROM object_properties
WHERE objectTypeId = '0-1'
AND name IN ('firstname', 'lastname', 'city')
ORDER BY objectId;


Returns:

OBJECTTYPEID OBJECTID NAME VALUE
0-1 10401 firstname Brian
0-1 10401 city Dublin
0-1 10401 lastname Gallagher
0-1 23451 firstname Fernando
0-1 23451 lastname Fierro
0-1 25751 firstname Charles
... ... ... ...

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:


SELECT objectId, firstname, lastname, lifecyclestage
FROM
(
SELECT objectId, name, value
FROM object_properties
WHERE objectTypeId = '0-1'
AND name IN ('firstname', 'lastname', 'lifecyclestage')
)
PIVOT(MAX(value) FOR name IN ('firstname', 'lastname', 'lifecyclestage')) AS p(objectId, firstname, lastname, lifecyclestage);

 

Returns:

OBJECTTYPEID OBJECTID FIRSTNAME LASTNAME LIFECYCLESTAGE
0-1 54325098 Hobbes Baron lead
0-1 96805401 Milo Gold lead
0-1 6922028 Henry Riley lead
... ... ... ... ...

object_properties_history

This view 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 view can return the following columns:

Column name Type Description
OBJECTTYPEID Varchar The ID of the object type (e.g., contacts is 0-1).
OBJECTID Number The ID of the record (e.g., a contact record with an ID of 123752).
NAME Varchar The name of the property.
VALUE Timestamp The value of the property.
UPDATEDAT Timestamp The date and time that the data was last updated in HubSpot. Learn more about column data types.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. 

Example 1

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

Query:


SELECT objectId, name, value, timestamp
FROM object_properties_history
WHERE objectTypeId = '0-1'
AND objectId = 585
AND name = 'hs_predictivescoringtier'
ORDER BY timestamp DESC;

   
Returns:

OBJECTTYPEID OBJECTID FIRSTNAME LASTNAME LIFECYCLESTAGE
0-1 54325098 Hobbes Baron lead
0-1 96805401 Milo Gold lead
0-1 6922028 Henry Riley lead
... ... ... ... ...

Example 2

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

Query:


SELECT
objectId,
properties:firstname::string AS firstname,
properties:lastname::string AS lastname
FROM object_with_object_properties
WHERE object_with_object_properties.objectTypeId = '0-1'
AND objectId IN (
SELECT objectId
FROM object_properties_history
WHEREobject_properties_history.objectTypeId = '0-1'
AND object_properties_history.name = 'lifecyclestage'
AND object_properties_history.value = 'subscriber'
);


Returns:

OBJECTID FIRSTNAME LASTNAME
54325098 Hobbes Baron
96805401 Milo Gold
6922028 Henry Riley
... ... ...

Association queries

Below, learn how to query associations, such as the available types of associations between objects and the records that are currently associated. 

association_definitions

This view contains information about all of the available association types between HubSpot objects, with one row per association type. You can join this table with the associations table 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 view can return the following columns:

Column name Type Description
COMBINEDASSOCIATIONTYPEID Varchar The unique identifier of the association definition.
CATEGORY Varchar The source of the association type. One of: HUBSPOT_DEFINED, USER_DEFINED, INTEGRATOR_DEFINED
ID Number The identifier of the association definition within its category.
FROMOBJECTTYPEID Varchar The ID of the object that is the source of the association definition.
TOOBJECTTYPEID Varchar The ID of the object that is the destination of the association definition.
NAME Varchar The association definition's name.
LABEL Varchar The association definition's label.
ISMAINASSOCIATIONDEFINITION Boolean Whether the association definition is the main type. When true, the definition is the one that HubSpot displays on associated records.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. Learn more about column data types.

Example 1 

Retrieve the types of associations available in your account.

Query:


SELECT combinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
ORDER BY category, id

  
Returns:

COMBINEDASSOCIATIONTYPE FROMOBJECTTYPEID TOOBJECTTYPEID NAME
0-1 0-1 0-2 CONTACT_TO_COMPANY
0-2 0-2 0-1 COMPANY_TO_CONTACT
0-3 0-3 0-1 DEAL_TO_CONTACT
0-4 0-1 0-3 CONTACT_TO_DEAL
0-5 0-3 0-2 DEAL_TO_COMPANY
... ... ... ...

Example 2

Retrieve the available association types between deals and companies.

Query:


SELECTcombinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
WHERE fromObjectTypeId = '0-3' AND toObjectTypeId = '0-2'
AND isMainAssociationDefinition = true

ORDER BY category, id


Returns:

COMBINEDASSOCIATIONTYPE FROMOBJECTTYPEID TOOBJECTTYPEID NAME
0-5 0-3 0-2 DEAL_TO_COMPANY
0-271 0-3 0-2 DEAL_TO_PRIMARY_COMPANY
0-341 0-3 0-2 DEAL_TO_COMAPNY_UNLABELED

associations

This view 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 table even though the referenced records have been deleted. You can check whether records exist by joining with the objects table.

This view can return the following columns:

Column name Type Description
COMBINEDASSOCIATIONTYPEID Varchar The unique identifier of the association definition.
FROMOBJECTID Number The ID of the source object.
TOOBJECTID Number The ID of the destination object.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. Learn more about column data types.

Example 1

Retrieve all associated deals and contacts.

Query:


SELECT combinedAssociationTypeId, fromObjectId, toObjectId
FROM associations
WHERE combinedAssociationTypeId = '0-5'

  
Returns: 

COMBINEDASSOCIATIONTYPE FROMOBJECTID TOOBJECTID
0-5 6626541373 233620335
0-5 6616436082 3866015468
0-5 6690805943 7132752747
... ... ...

Example 2 

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

Query:


SELECT combinedAssociationTypeId, fromObjectId, toObjectId
FROM associations
WHERE associationCategory = 'HUBSPOT_DEFINED'
AND combinedAssociationTypeId = '0-15'
AND fromObjectId = 3005;


Returns:

COMBINEDASSOCIATIONTYPE FROMOBJECTID TOOBJECTID
0-5 6626541373 233620335

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:


SELECT combinedAssociationType, fromObjectId, toObjectId,
FROM associations
WHERE
combinedAssociationTypeId = (
SELECT combinedAssociationTypeId
FROM association_definitions
WHERE fromObjectTypeId = '0-1'
AND toObjectTypeId = '0-3'
AND isMainAssociationDefinition)
AND fromObjectId = 4464;


Returns:

COMBINEDASSOCIATIONTYPE FROMOBJECTID TOOBJECTID
0-5 6626541373 233620335

owners

This view 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 view can return the following columns:

Column name Type Description
OWNERID Number The user's owner ID.
USERID Number The user's user ID. User's with NULL in this column have been deleted.
EMAIL Varchar The user's email address.
FIRSTNAME Varchar The user's first name.
LASTNAME Varchar The user's last name.
ISACTIVE Boolean Whether the user has been deleted.
CREATEDAT Timestamp The date and time that the user was created in HubSpot. See column data types for more information.
UPDATEDAT Timestamp The date and time that the user was last updated in HubSpot. See column data types for more information.

Example 1 

Retrieve all users. The WHERE isActive = true filter removes deleted users from the returned data.

Query:


SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE isActive = true

  
Returns: 

OWNERID ACTIVEUSERID EMAIL FIRSTNAME LASTNAME
29584574 4621126 233620335 Hobbes Baron
30532717 4874784 3866015468 Pablo Walters
30580321 925511 7132752747 Milo Gold
... ... ... ... ...

Example 2

Retrieve a specific owner by their owner ID.

Query:


SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE ownerId = 29584574;


Returns: 

OWNERID ACTIVEUSERID EMAIL FIRSTNAME LASTNAME
29584574 4621126 233620335 Hobbes Baron

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.


SELECT
contact.objectId contact_id,
contact.properties:firstname::string contact_firstname,
contact.properties:lastname::string contact_lastname,
contact.properties:hubspot_owner_id::number contact_hubspot_owner_id,
owner.firstname owner_firstname,
owner.lastname owner_lastname,
owner.email owner_email
FROM object_with_object_properties contact
inner join owners owner on contact.properties:hubspot_owner_id::number = owner.ownerId
where objectTypeId = '0-1';


Returns: 

CONTACTID CONTACT_FIRSTNAME CONTACT_LASTNAME CONTACT_HUBSPOT_OWNER_ID OWNER_FIRSTNAME OWNER_LASTNAME OWNER_EMAIL
113834202 Richard Greenfield 29584574 Hobbes Baron hobbes.b@business.com
53540801 Ford Karl 30241125 Milo Gold milo.g@business.com
... ... ... ... ... ... ...

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 object_with_object_properties data with pipelines data.

pipelines

This view 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:

Column name Type Description
PIPELINEID Varchar The pipeline's ID.
OBJECTTYPEID Varchar The ID of the object that the pipeline can contain (e.g., deals or tickets).
LABEL Varchar The pipeline's name in HubSpot.
DISPLAYORDER Number The order in which the pipeline is displayed in HubSpot.
ARCHIVED Boolean Whether the pipeline has been deleted.
CREATEDAT Timestamp When the pipeline was created.
UPDATEDAT Timestamp The date and time that the data was last updated in HubSpot. Learn more about column data types.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. 

Example 1

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

Query:


SELECT objectTypeId, pipelineId, label
FROM pipelines
WHERE objectTypeId = '0-3'
AND not archived
ORDER BY displayorder;

  
Returns: 

OBJECTTYPEID PIPELINEID LABEL
0-3 1bed503c-37f7-4f51-9d40-7598902673f4 Sales Pipeline
0-3 12659678 Licenses Pipeline
0-3 75e28846-ad0d-4be2-a027-5e1da6590b98 New Business
... ... ...

Example 2

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

Query:


SELECT

deals.objectId deal_id,
deals.properties:dealname::varchar deal_name,
pipelines.label pipeline_name
FROM object_with_object_properties deals
INNER JOIN pipelines
ON deals.objectTypeId = pipelines.objectTypeId
AND deals.properties:pipeline::string = pipelines.pipelineid
WHERE deals.objecttypeId = '0-3'
AND pipelines.objectTypeId = '0-3';


Returns: 

DEAL_ID DEAL_NAME PIPELINE_NAME
605140072 Meowmix Global Signup Sales Pipeline
605457408 Friskies Rebrand Sales Pipeline
604362473 Fresh Step App Build Sales Pipeline

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 table can return the following columns:

Column name Type Description
PIPELINEID Varchar The pipeline's ID.
OBJECTTYPEID Varchar The ID of the object that the pipeline can contain (e.g., deals or tickets).
LABEL Varchar The pipeline's name in HubSpot.
DISPLAYORDER Number The order in which the pipeline is displayed in HubSpot.
ARCHIVED Boolean Whether the pipeline has been deleted.
UPDATEDAT Timestamp The date and time that the data was last updated in HubSpot. Learn more about column data types.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. 

Example 1 

Retrieve pipeline stages for a deal pipeline named Sales pipeline.

Query:


SELECT pipelineid, stageId, label
FROM pipeline_stages
WHERE not archived
AND pipelineid = (
SELECT pipelineid
FROM pipelines
WHERE objectTypeId = '0-3'
AND not archived
AND label = 'Sales Pipeline' LIMIT 1)
ORDER BY displayorder;

  
Returns: 

PIPELINEID STAGEID LABEL
941650 941651 Contacted
941650 941652 Appt scheduled
941650 941653 Invoice sent
... ... ...

Example 2

Retrieve all deals and their respective pipeline stages.

Query:


SELECT

deals.objectId deal_id,
deals.properties:dealname::varchar deal_name,
pipelines.label pipeline_name,
pipeline_stages.label pipeline_stage_name
FROM object_with_object_properties deals
INNER JOIN pipelines
ON deals.objectTypeId = pipelines.objectTypeId
AND deals.properties:pipeline::string = pipelines.pipelineid
INNER JOIN pipeline_stages
ON deals.objectTypeId = pipeline_stages.pipelineid
AND deals.properties:pipeline::string = pipeline_stages.pipelineid
AND deals.properties:dealstage::string = pipeline_stages.stageId
WHERE deals.objecttypeId = '0-3';


Returns: 

DEAL_ID DEAL_NAME PIPELINE_NAME PIPELINE_STAGE_NAME
605140072 Meowmix Global Signup Sales Pipeline Contract sent
605457408 Friskies Rebrand Sales Pipeline Appt scheduled
604362473 Fresh Step App Build Sales Pipeline Contract signed
... ... ... ...

property_definitions

This view 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 view can return the following columns:

Column name Type Description
OBJECTYPEID Varchar The ID of the object that the event relates to.
NAME Varchar The property's internal name.
LABEL Varchar The property's label.
DESCRIPTION Varchar The property's description.
TYPE Varchar The property's type (e.g., string, number, datetime).
FIELDTYPE Varchar The property's field type. Defines how the property displays in HubSpot and on forms (e.g., checkbox, select).
DISPLAYORDER Number The property's display order.
GROUPNAME Varchar The property's group name.
OPTIONS Variant For enumeration properties, an array of the property's options, including each option's attributes (e.g., display order, label).
CREATEDUSERID Number The ID of the user who created the property.
REFERENCEDOBJECTTYPE Varchar The type of object that the property references. Owner properties will return a value of OWNER.
CALCULATED Boolean Whether the property is a calculated property.
EXTERNALOPTIONS Boolean Whether the property's options are defined in an external system.
HASUNIQUEVALUE Boolean Whether the property's values are unique. Can only be set when creating a property through the API.
HIDDEN Boolean Whether the property is hidden in HubSpot.
SHOWCURRENCYSYMBOL Boolean Whether a number property value is formatted as a currency.
FORMFIELD Boolean Whether this property can be used in forms.
READONLYDEFINITION Boolean Whether the property can be edited in HubSpot.
READONLYVALUE Boolean Whether the property's value can be edited in HubSpot.
HUBSPOTDEFINED Boolean Whether the property was created by HubSpot, versus created by a user.
ARCHIVED Boolean Whether the property has been deleted.
CREATEDAT Timestamp The date and time that the property was created in HubSpot.
UPDATEDAT Timestamp The date and time that the data was last updated in HubSpot. Learn more about column data types.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. 

Example 1

Retrieve the names and descriptions for all contact properties.

Query:


SELECT name, label, description
FROM property_definitions
WHERE objectTypeId = '0-1'

   
Returns:

NAME LABEL DESCRIPTION
entered_sql_stage Entered SQL stage Start count SQL stage days.
first_conversion_event_name
First Conversion The first form this contact submitted.
address Street address The contact's address.

Event queries

Events include standard HubSpot events and custom behavioral events. Each event has its own view, labeled as Events_eventname.

Available events include:

  • Ad click events (events_ad_clicked)
  • Email opens and clicks (events_opened_email_V2events_clicked_link_in_email_v2, etc.)
  • Web analytics (events_visited_page)

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:

Column name Type Description
EVENTTYPEID Varchar The pipeline's ID.
ID Varchar The ID of the object that the pipeline can contain (e.g., deals or tickets).
OBJECTTYPEID Varchar The type of object that completed the event. For example, an email link click would display 0-1 in this column, representing the contacts object.
OBJECTID Number The ID of the record that completed the event.
OCCUREDATDATEINT Number A number representing when the event occurred. Contains the same data as the OCCUREDAT column, but formatted as a number and used as a part of the cluster key of the table. In most cases, you can use the OCCUREDAT timestamp instead of this number.
OCCUREDAT Timestamp When the event occurred.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. Learn more about column data types.
PROPERTY_* Varchar Individual columns containing the event properties.

Example 1

Retrieve the 50 most recent email click events.

Query:


SELECT eventTypeId, objectTypeId, objectId, occuredAt, hs_click_raw_url
FROM events_clicked_link_in_email_V2
ORDER BY occuredAt

LIMIT 50;


Returns: 

EVENTTYPEID OBJECTTYPEID OBJECTID OCCUREDAT HS_CLICK_RAW_URL
4-666288 0-1 45318579 2016-08-04 21:06:43.245 http://website.com?utm_campaign=Launch&utm_source=hs_email&utm_medium=email&utm_content=32520579
4-666288 0-1 80701 2016-08-29 13:31:28.622 https://cta-image-cms2.hubspot.com/ctas/v2/public/cs/ci/?pg=f0f6585a-08fc-4273-8422-49cfaddcf8d8&pid=1976760&ecid=ACsprvtkRbakerw4BiWlaDL7xFAqau1aW9m1_PAqSfQiWarY69X7Ds42zHGYTCfhHJLrXUP86Nxu
4-666288 0-1 26146013 2017-11-30 14:27:11.528 https://hubs.ly/H097a37a?utm_source=hs_email&utm_medium=email&utm_content=58251290
... ... ... ... ...

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:


SELECT
click.timestamp click_timestamp,
click.property_hs_click_raw_url click_url,
contact.properties:firstname::varchar contact_firstname,
contact.properties:lastname::varchar contact_lastname
FROM events_clicked_link_in_email_V2 click
LEFT JOIN object_with_object_properties contact
ON click.objectTypeId = contact.objectTypeId
AND click.objectId = contact.objectId
LIMIT 3;


Returns: 

CLICK_TIMESTAMP CLICK_URL CONTACT_FIRSTNAME CONTACT_LASTNAME
2019-01-04 22:35:41.264 https://help.website.com/articles/tutorial/how-to-use-email-tool?utm_source=hs_automation&utm_medium=email&utm_content=68042217 Hobbes Baron
2018-11-08 09:37:41.400 https://www.website.net/webinar-case-study?utm_source=hs_automation&utm_medium=email&utm_content=55200164 Milo Gold
2018-11-08 09:37:41.426 https://www.website.net/contact?utm_source=hs_automation&utm_medium=email&utm_content=55200164 Harlow Villari
... ... ... ...

object_and_event_type_definitions

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

This view can return the following columns:

Column name Type Description
OBJECTTYPEID Varchar The ID of the object that the pipeline can contain (e.g., deals or tickets).
FULLYQUALIFIEDNAME Varchar The name of the object or event.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. Learn more about column data types.

Example 1

Retrieve all objects and events available in your HubSpot account.

Query:


SELECT objectTypeId, fullyQualifiedName
FROM object_and_event_type_definitions;

  
Returns: 

OBJECTTYPEID FULLYQUALIFIEDNAME
0-1 CONTACT
0-2 COMPANY
0-3 DEAL
0-4 ENGAGEMENT
0-5 TICKET
0-8 LINE_ITEM
0-11 CONVERSATION
... ...

Lists queries (BETA)

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

lists

This view 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 view can return the following columns:

Column name Type Description
OBJECTTYPEID Varchar The type of records in the list (e.g., contact or company).
LISTID Number The ID of the list. Joins to the LISTID column in the list-memberships table.
CLASSICLISTID Number An ID specific to contact lists that matches the ID in the URL of the list in HubSpot.
LISTNAME Varchar The name of the list.
LISTSIZE Number The number of records in the list.
CREATEDBYUSERID Number The ID of the user who created the list.
CREATEDAT Timestamp When the list was created in HubSpot.
UPDATEDBYUSERID Number The ID of the user who most recently updated the list.
UPDATEDAT Timestamp The date and time that the date was last updated in HubSpot. Learn more about column data types
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake.

Example 1

Retrieve all contact lists.

Query:


SELECT listId, listName, listSize
FROM lists
WHERE objectTypeId = '0-1';

  
Returns: 

LISTID LISTNAME LISTSIZE
118131 Weekly outreach 103
66156 Blog subscribers 455
771852 Top subscribers 2021 37
... ... ...

Example 2

Retrieve a specific contact list named Top subscribers 2021

Query:


SELECT listId, listName, listSize 
FROM lists
WHERE objectTypeId = '0-1'
AND
listName = 'Top subscribers 2021';


Returns: 

LISTID LISTNAME LISTSIZE
771852 Top subscribers 2021 37

list_membership

This view 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 table will return 500 rows. 

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

This view can return the following columns:

Column name Type Description
LISTID Varchar The ID of the list. Joins to the HS_LIST_ID column in the lists table.
OBJECTID Varchar The ID of the record in the list.
UPDATEDAT Timestamp The date and time that the data was last updated in HubSpot. Learn more about column data types.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. 

Example 1

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

Query:


SELECT objectId
FROM list_memberships
INNER JOIN lists
ON list_memberships.listId = lists.listId
WHERE lists.objectTypeId = '0-1'
AND lists.name = 'Top Subscribers 2021';

  
Returns: 

OBJECTID
54325098
96805401
6922028
...

Example 2

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

Query:


SELECT
contact.objectId AS objectid,
contact.properties:firstname::string AS firstname,
contact.properties:lastname::string AS lastname
FROM object_with_object_properties contact
INNER JOIN list_memberships ON contact.objectId = list_memberships.objectId
INNER JOIN lists ON list_memberships.listId = lists.listId
WHERE
contact.objectTypeId = '0-1'
AND lists.objectTypeId = '0-1'
AND lists.listName = 'Top Subscribers 2021'


Returns: 

OBJECTID FIRST NAME LAST NAME
54325098 Hobbes Baron
96805401 Milo Gold
6922028 Henry Riley
... ... ...

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.

snowflake-sidebar-view-column-details0


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


SELECT
fromObjectTypeId, toObjectTypeId
FROM association_definitions

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 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 property.
  • COMBINEDASSOCIATIONTYPE: a VARCHAR value that uniquely identifies an association definition.