Skip to content

Query HubSpot data in Snowflake

Last updated: May 31, 2024

Available with any of the following subscriptions, except where noted:

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.

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.

    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_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.


      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, property_createddate
FROM hubspot_share_name.V2_LIVE.objects_deals

LIMIT 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.
  • 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:


SELECT objectTypeId, updatedAt, ingestedAt
FROM objects_deals
ORDER BY objectId
LIMIT 10;

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.

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 OBJECTID and UpdatedAt columns:


SELECT
objectId, 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:
Object name ObjectTypeId
Contacts 0-1
Companies 0-2
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.

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:

 

HubSpot Object type

ObjectTypeId

Description

CONTACT

0-1

Information about individuals interacting with your business

COMPANY

0-2

Information about individual businesses or organizations

DEAL

0-3

Details about revenue opportunities with a contact or company. 

ENGAGEMENT

0-4

Stores data from CRM actions, including notes, tasks, emails, meetings, and calls.

TICKET

0-5

Represent customer requests for help or support.

QUOTE

0-14

Used to share pricing information with potential buyers.

FORM_SUBMISSION

0-15

Details for individual submissions for a HubSpot form.

LINE_ITEM

0-8

Represent a subset of products sold in a deal. When a product is attached to a deal, it becomes a line item.

CONVERSATION

0-11

Details of incoming messages from multiple channels.

LANDING_PAGE

0-25

Details of your landing pages

 TASK

0-27

Stores information about to-do lists

FORM

0-28

Used to collect lead information about your visitors and contacts

MARKETING_EMAIL

0-29

Details about emails from marketing hub

AD_ACCOUNT

0-30

Stores information about your ad accounts on Linkedin, Facebook and Google

AD_CAMPAIGN

0-31

Details about your ad campaigns. An ad campaign can contain one or more ads

AD_GROUP

0-32

Logical grouping of ads within an ad campaign

AD

0-33

Details about individual ads

CAMPAIGN

0-35

Information about related marketing assets and content, so you can easily measure the effectiveness of your collective marketing efforts.

SITE_PAGE

0-38

Data about individual pages on your websites

BLOG_POST

0-39

Data about blog posts

OBJECT LIST

0-45

Information about groupings of object records basedon their properties or activities

CALL

0-48

Calls made by other CRM records e.g. contacts

INVOICE

0-53

Manage and sync invoices with external accounting systems

MEDIA_BRIDGE

0-57

Information about media assets imported into Hubspot

SEQUENCE

0-58

A sequence is a series of targeted, timed email templates to nurture contacts over time

DEAL_SPLIT

0-72

Deal splits are used to share deal credit among multiple users

SALES_DOCUMENT

0-83

Sales documents build a library of content for your entire team to upload and share documents with your contacts.

FEEDBACK_SUBMISSION

0-19

Stores information submitted to a feedback survey.

SUBSCRIPTION

0-69

Subscriptions contain details of recurring payments

COMMERCE PAYMENT

0-101

Contains data about funds collected from customers



Each database object will 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).

UPDATEDAT

Timestamp

The date and time that this object was last updated in HubSpot. 

INGESTEDAT

Timestamp

The date and time that the data was last ingested by Snowflake. 

PROPERTY_*

Varchar

Individual columns containing the latest version of the object’s properties.For each object property that has a date or number type (as specified by property_definitions), there will be two columns: 

  1. A column containing the property value as string. The column name will be property_unparsed_[name] . For example, DEAL object property, deal_amount will have the column property_unparsed_deal_amount
  2. A column containing the value converted to the native type. The column will have the name property_[name].For example, DEAL object property, deal_amount will have the column property_deal_amount. Note that this column will contain null if the conversion to native type fails due to invalid data.

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:

OBJECTID

FIRSTNAME

LASTNAME

LIFECYCLESTAGE

54325098

Hobbes

Baron

lead

96805401

Milo

Gold

lead

6922028

Henry

Riley

lead

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:

OBJECTID

PROPERTY_DEALNAME

PROPERTY_DEALSTAGE

PROPERTY_DEALAMOUNT

38960439

Deal name 1

closedwon

34199

321740651

Test Deal 123

14845019

8383663

98392101

Pop-Up Canopy

closedwon

9309007

 

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:

OBJECTID

PROPERTY_DEALNAME

PROPERTY_DEALSTAGE

PROPERTY_DEALAMOUNT

98392101

Pop-Up Canopy

closedwon

9309007

321740651

Test Deal 123

14845019

8383663

38960439

Deal name 1

closedwon

34199

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:

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. 

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:


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 closedlost 7488939
321740651 Deal name 1 closedwon 9292029
98392101 Deal name 1 closedlost 62626528

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:

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

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

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, updatedAt
FROM object_properties_history
WHERE objectTypeId = '0-1'
AND objectId = 136493192
AND name = 'hs_predictivescoringtier'
ORDER BY UPDATEDAT DESC;

 
Returns:

OBJECTID NAME VALUE UPDATEDAT

136493192

hs_predictivescoringtier

tier_1

2022-06-22 22:45:05.931

136493192

hs_predictivescoringtier

tier_2

2021-09-18 08:20:56.622

136493192

hs_predictivescoringtier

tier_3

2019-11-31 18:20:22.851

...

...

...

...

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

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:

Column name

Type

Description

COMBINEDASSOCIATIONTYPEID

Varchar

The unique identifier of the association definition.

ASSOCIATIONCATEGORY

Varchar

The source of the association type. One of: HUBSPOT_DEFINED, USER_DEFINED, INTEGRATOR_DEFINED

ASSOCIATIONTYPEID

Varchar

A unique identifier of an association definition within a specific association category, i.e. no two definitions in an association category will have the same associationtypeid. 

FROMOBJECTTYPEID

Varchar

The objecttypeid that is the source of the association definition.

FROMOBJECTTYPE

Varchar

The name of the objecttype that is the source of the association definition.

TOOBJECTTYPEID

Varchar

The objecttypeid that is the destination of the association definition.

TOOBJECTTYPE

Varchar

The name of the objecttype that is the destination of the association definition.

NAME

Varchar

The association definition's name.

LABEL

Varchar

The association definition's description.

ISMAINASSOCIATIONDEFINITION

Boolean

Whether the association definition is the main type. When true, the definition is the one that HubSpot displays on associated records

[FROMOBJECTTYPE_OBJECTID] e.g. for ASSOCIATIONS_CONTACTS_TO_DEALS view, this will be CONTACT_OBJECTID

Number

The Object ID of the source object.

[TOOBJECTTYPE_OBJECTID] e.g. for ASSOCIATIONS_CONTACTS_TO_DEALS view, this will be DEAL_OBJECTID

Number

The Object ID of the destination object.

INGESTEDAT

Timestamp

The date and time that the data was last ingested by Snowflake.

Example 1

 

Retrieve all deals associated with a specific contact.

Query:


SELECT
contact_objectId, deal_objectId

FROM associations_contacts_to_deals

WHERE contactId = 6626541373

 

Returns:

CONTACT_OBJECTID

DEAL_OBJECTID

6626541373

233620335

6626541373

90253678

Example 2

 

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


SELECT contacts.property_firstname contact_firstname,

contacts.property_lastname contact_lastname,
contacts.property_email contact_email,
deals.property_dealtype deal_type, deals.property_amount deal_amount
FROM objects_contacts contacts
JOIN associations_contacts_to_deals assoc on
assoc.contact_objectid=contacts.objectid
JOIN objects_deals deals on deals.objectid=assoc.deal_objectid
where contacts.property_hs_timezone='america_slash_bogota'

 

CONTACT_FIRSTNAME

CONTACT_LASTNAME

CONTACT_EMAIL

DEAL_TYPE

DEAL_AMOUNT

David

Magalhães

dmagalhaes@example.com

newbusiness

74848

Fernanda

Rodrigues

fmoreira@example.com

newbusiness

253530

Example 3

 

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


SELECT SUM(deals.property_hs_closed_amount) AS
total_closed_amount, contacts.property_email contact_email
FROM objects_contacts contacts
JOIN associations_contacts_to_deals assoc on
assoc.contact_objectid=contacts.objectid
JOIN objects_deals deals on deals.objectid=assoc.deal_objectid
GROUP BY contact_email
ORDER BY total_closed_amount DESC

 

Returns:

TOTAL_CLOSED_AMOUNT

CONTACT_EMAIL

98474793

johndoe@riverview.com

849488

sallymae@longford.com

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:

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

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: 

COMBINEDASSOCIATIONTYPEID 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 combinedAssociationTypeId, 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:

COMBINEDASSOCIATIONTYPEID FROMOBJECTID TOOBJECTID
0-5 6626541373 233620335

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:

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.
ARCHIVED 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.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake.

Example 1 

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

Query:


SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE archived = false

 
Returns: 

OWNERID USERID 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 USERID 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 contactid,
contact.property_firstname contact_firstname,
contact.property_lastname contact_lastname,
contact.property_hubspot_owner_id contact_hubspot_owner_id,
owner.firstname owner_firstname,
owner.lastname owner_lastname,
owner.email owner_email
FROM objects_contacts contact

JOIN owners owner ON contact.property_hubspot_owner_id = owner.ownerId


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
... ... ... ... ... ... ...

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:

Column name Type Description
TEAMID Number The team ID.
TEAMNAME Varchar The team name.
PARENTTEAMID Number The team ID of the parent team.
CHILDTEAMIDS Array The team IDs of the child teams.
PRIMARYOWNERMEMBERS Varchar The owner IDs of the team primary members.
SECONDARYOWNERMEMBERS Boolean The owner IDs of the team secondary members.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. 

Example 1

Retrieve all teams.

Query:

SELECT teamId,teamName, primaryOwnerMembers

FROM teams

Returns:

TEAMID TEAMNAME PRIMARYOWNERMEMBERS
29584574 Sub Team [233620335, 1234553]
30532717 Super Team [3866015468, 126221873]
3058032 A Team [7132752747]

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:

Column name Type Description
TEAMID Number The team ID.
OWNERID Number The user's owner ID. 
ISTEAMPRIMARY Boolean Whether this team is the primary team for this owner.
INGESTEDAT Timestamp The date and time that the data was last ingested by Snowflake. 

Example 1

Retrieve all owners for a given team.

Query:

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

Returns

TEAMID TEAMNAME ISTEAMPRIMARY
29584574 233620335 TRUE
29584574 1234553 TRUE 

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:

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.property_dealname deal_name,
pipelines.label pipeline_name
FROM objects_deals deals
INNER JOIN pipelines
ON deals.objectTypeId = pipelines.objectTypeId
AND deals.property_pipeline = pipelines.pipelineid
WHERE 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

 

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.


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

Column name Type Description
PIPELINEID Varchar The pipeline's ID.
STAGEID Varchar The stage's ID.
LABEL Varchar The stage's name in HubSpot.
DISPLAYORDER Number The order in which the pipeline is displayed in HubSpot.
METADATA Variant A JSON formatted description of the stage including whether it is open or closed
ARCHIVED Boolean Whether the pipeline stage has been deleted.
CREATEDAT Timestamp When the pipeline stage was created.

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

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 database object, labeled as events_eventname.

Name  
EVENTS_AD_INTERACTION Details of an Ad and contacts that interacted with it by clicking on the ad or submitting a form after engaging with it.
EVENTS_AD_METRICS_IMPORTED_V0

Details of an Ad and and its performance metrics.
EVENTS_CALL_MENTIONED_KEYWORD Details of keywords mentioned by any participants in calls.
EVENTS_CLICKED_LINK_IN_EMAIL_V2

Links clicked in marketing emails by contacts.
EVENTS_CLICKED_LINK_IN_TRACKED_INBOX_EMAIL_V8 Links clicked in Conversations Inbox emails by contacts.
EVENTS_COOKIE_BANNER_CLICKED Contacts who clicked on cookies.
EVENTS_COOKIE_BANNER_VIEWED Contacts who viewed cookies.
EVENTS_DOCUMENT_SHARED_V2 Sales Documents shared with contacts via an external link.
EVENTS_DOCUMENT_VIEWED_V2 Sales Documents and contacts that have viewed by them by opening the external link.
EVENTS_DOCUMENT_COMPLETED_V2 Sales Documents and contacts that have closed them out.
EVENTS_HS_SCHEDULED_EMAIL_V2 Emails submitted to the HubSpot email servers to be sent.
EVENTS_MTA_DELIVERED_EMAIL_V2 Emails successfully delivered to the recipients by HubSpot email servers.
EVENTS_MTA_BOUNCED_EMAIL_V2 Email delivery attempts that were rejected by the recipient’s Email server.
EVENTS_OPENED_EMAIL_V2 Email opens by contacts.
EVENTS_REPORTED_SPAM_EMAIL_V2 Emails marked as spam by recipients.
EVENTS_UPDATED_EMAIL_SUBSCRIPTION_STATUS_V2 Changes to email email subscriptions by recipients.
EVENTS_OPENED_TRACKED_INBOX_EMAIL_V8 Conservations Inbox email opens by contacts.
EVENTS_MB_MEDIA_PLAYED Video playback activity by contacts.
EVENTS_V2_CONTACT_BOOKED_MEETING_THROUGH_SEQUENCE Contact booked a meeting through sequence.
EVENTS_V2_CONTACT_ENROLLED_IN_SEQUENCE Contact enrolled from sequence.
EVENTS_V2_CONTACT_FINISHED_SEQUENCE Sequence finished.
EVENTS_V2_CONTACT_REPLIED_SEQUENCE_EMAIL Contact replied to email (or incoming email which unenrolled sequence) sent through sequence.
EVENTS_V2_CONTACT_UNENROLLED_FROM_SEQUENCE Contact unenrolled from sequence.
EVENTS_V2_CONTACT_UNENROLLED_MANUALLY_FROM_SEQUENCE User manually unenrolled the contact.
EVENTS_V2_CONTACT_UNSUBSCRIBED_SEQUENCE_EMAIL Contact unsubscribed, unenrolling the sequence.
EVENTS_V2_SEQUENCE_EMAIL_BOUNCED Sequence email bounced.
EVENTS_V2_SEQUENCE_ERRORED Sequence errored causing unenrollment.
EVENTS_V2_SEQUENCE_STEP_EXECUTED Sequence step executed.
EVENTS_VISITED_PAGE Website page visits by contacts.
EVENTS_<INTEGRATION_EVENT_NAME>_<UNIQUE_IDENTIFIER> Integration events (Timeline V3 API)

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:

Please note: HubSpot has added two new columns to all event tables and views:

  • occurredat
  • occurredatdateint

These columns will be replacing the occuredat and occuredatdateint columns on November 24, 2022. On that date, the old columns will no longer be available, and attempts to query a database object or view containing events using those columns will fail. Learn more about breaking changes for Snowflake data shares.



Column name Type Description
EVENTTYPEID Varchar The ID of the event type, similar to OBJECTTYPEID
You can query all eventype IDs in your account using the object_and_event_type_definitions view.
ID Varchar This is unique per event type. No two records will have the same ID within a given event type.
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.
OCCURREDAT Timestamp When the event occurred.

OCCURREDATDATEINT

 

Number A number representing when the event occurred. Contains the same data as the OCCURREDAT 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 OCCURREDAT timestamp instead of this number.

OCCUREDATDATEINT

Number

A number representing when the event occurred. Contains the same data as the OCCURREDAT 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 OCCURREDAT timestamp instead of this number.

Please note: this column will be deprecated on November 24, 2022 and replaced with OCCURREDATDATEINT.

OCCUREDAT Timestamp

When the event occurred.

Please note: this column will be deprecated on November 24, 2022 and replaced with OCCURREDAT.

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, property_hs_click_raw_url AS 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.occuredat 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_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:

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-8 LINE_ITEM
0-11 CONVERSATION
... ...

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:

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.
NAME Varchar The name of the list.
SIZE 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, name, size
FROM lists
WHERE objectTypeId = '0-1';

 
Returns: 

LISTID NAME SIZE
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, name, size 
FROM lists
WHERE objectTypeId = '0-1'
AND
listName = 'Top subscribers 2021';


Returns: 

LISTID NAME SIZE
771852 Top subscribers 2021 37

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:

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 list.

Query:


SELECT

contact.objectId AS objectid,
contact.property_firstname AS firstname,
contact.property_lastname AS lastname
FROM objects_contacts contact
INNER JOIN list_memberships ON contact.objectId = list_memberships.objectId
INNER JOIN lists ON list_memberships.listId = lists.listId
WHERE lists.objectTypeId = '0-1'
AND lists.name = 'Top Subscribers 2021'


Returns: 

OBJECTID

FIRST NAME

LAST NAME

54325098

Hobbes

Baron

96805401

Milo

Gold

6922028

Henry

Riley

...

...

...

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:


SELECT * FROM property_definitions

WHERE label = [column_name]

 

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:


SELECT campaigns.property_hs_name campaign_name,
forms.property_hs_form_id form_id
FROM objects_campaigns campaigns
JOIN associations_campaigns_to_forms assoc ON
assoc.campaign_objectid=campaigns.objectid
JOIN objects_forms forms ON forms.objectid=assoc.form_objectid
GROUP BY campaign_name

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:

STATUS MESSAGE UPDATEDAT
OK NO KNOWN INCIDENT AT THE MOMENT 2023-04-10 09:21:20.123

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. 

Was this article helpful?
This form is used for documentation feedback only. Learn how to get help with HubSpot.