Multiple Important Updates to Snowflake Data Share

[AJLaPorte]

Below is a list of important updates that are happening to the HubSpot Snowflake Data Share App.

Breaking Change: Timestamp columns in object_with_object_properties table and view are changing

What’s changing?

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 now be updatedat. This is to align with how datetime data is represented in the rest of the data share.

What does this mean?

To make it easier to transition, the new column updatedat has already been added to the table and view. For the next 90 days, both the existing timestamp column and the new column will be available, so you can update your queries at any point during the transition.

After the 90-day transition period ends on April 1, 2023, the column timestamp will be dropped 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.

Now Available: Snowflake Data Share now supports all AWS, GCP, and Azure regions

What’s happening?

Until now, the integration only supported Snowflake data hosted with Amazon Web Services (AWS) in the AWS US_East_1 and AWS EU-Central_1 regions.

Moving forward, customers hosting data in all major AWS regions can install the data share. In addition, the integration now supports Snowflake data with Google Cloud Platform (GCP) and Microsoft Azure. You can view a list of all supported regions on our knowledge base documentation.

Why does this matter?

With a more flexible Snowflake integration, Hubspot customers with Snowflake accounts in any of these regions can now easily onboard the Data Share.

How does it work?

You can now select your data center of choice during the data share installation flow. You can access a knowledge doc with step-by-step instructions to install the data share here.

For more information on how to get started with querying HubSpot data in Snowflake, check out the following knowledge base article.

Now Live: New improved schema

What is it?

Now released to general availability is an improved schema layout for the data share integration, making it easier to query HubSpot data from within Snowflake. With this addition, improved views of all HubSpot objects, properties, and associations can now be found as views within the V2_LIVE and V2_DAILY schemas in Snowflake. The new views better reflect the HubSpot CRM data model, so will be more familiar to anyone who has used HubSpot's APIs.

Why does it matter?

HubSpot users typically use data share to prepare HubSpot data within a Snowflake account before moving it elsewhere for analysis or consumption. This can involve cleaning up data, joining it to other sources, and writing queries on it. The additions to the V2_LIVE and V2_DAILY schemas enable easier and more intuitive data access, providing for more consistent and accurate insights.

The schema improvements have been added to the data share in the form of views. These new views can be found under the V2_LIVE and V2_DAILY schemas. In these schemas, each HubSpot object is now available as a single record, and properties are available as columns within. For instance, data on contact objects and their properties can be retrieved by querying objects_contact:

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

Association data is also now available as separate views. For instance, to return deals associated with contacts:

SELECT contact_objectId, deal_objectId 
FROM associations_contacts_to_deals 
WHERE contactId = 6626541373

These associations views contain details that reduce the explicit need to understand object type metadata to effectively run queries. For example, to return contacts based in the Bogota time zone and the deals associated with those contacts:

SELECT contacts.property_firstname AS contact_firstname, contacts.property_lastname AS contact_lastname, contacts.property_email AS contact_email, deals.property_dealtype AS deal_type, deals.property_amount AS 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'

For more information on how to get started with using the improved schema, check out the KB sections here and here.

Now Available: Lists data

What is it?

Now released to general availability is the addition of HubSpot list data to the Snowflake Data Share. Lists allow you to create different groupings of contacts or companies based on their properties. With this release, you can now query tables and views in your data share to get information about the lists you have created in HubSpot, as well as the membership of records in those lists.

How does it work?

Data on the different lists available can be obtained by querying lists. For instance, to retrieve all contact lists:

SELECT listId, name, size 
FROM lists 
WHERE objectTypeId = '0-1'

Sample Output

LISTID	NAME	SIZE
118131	Weekly outreach	103
66156	Blog subscribers	455

Lists_memberships contain information about the members that make up each list. For instance, to know the IDs of the contacts that make up the list Blog subscribers:

SELECT objectId 
FROM list_memberships 
INNER JOIN lists ON list_memberships.listId = lists.listId 
WHERE lists.objectTypeId = '0-1' AND lists.name = 'Blog subscribers'

Sample Output

OBJECTID
54325098
96805401
6922028

The objectIds returned can then be used to fetch full information about the contacts.

More information on how to query lists data is available in the knowledge base.