How to determine when snowflake V2_DAILY has been updated

Developer Support APIs & Integrations
1

Is there a way to reliably determine when the V2_DAILY snapshot has been updated for the day?

2

Hey @sblackstone

Thanks for your question! It’s a critical one for anyone relying on the V2_DAILY Snowflake snapshot for their data pipelines and reporting.

I’m tagging a few community members who can help with this, as they often have practical experience with the HubSpot Snowflake Data Share and its nuances:

@SteveHTM @chriswalker @RKon

Hopefully, they can provide some more specific insights or strategies!

3

Hi @sblackstone

Please explain more in detail so that we can have a bigger picture to understand your query.

Thanks!

4

Hubspot offers two snowflake shares, one of which is V2_DAILY which the documentation states is updated once a day as the name suggests.

I would like a reliable metric to determine when this process has completed and all the views and tables within the V2_DAILY schema represent a consistent new snapshot.

5

Hi @sblackstone ,
Based on the available documentation, INGESTEDAT appears to be your best option for determining when the V2_DAILY snapshot has been updated.
According to the docs, INGESTEDAT represents “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.”
A couple of important points to keep in mind:

  • 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
  • If your flow copies data daily, it’s recommended to copy data from the previous two days to account for delays

While this isn’t a perfect solution since it tracks row-level ingestion rather than snapshot-level updates, it’s the closest mechanism available in the current documentation for monitoring data freshness in the V2_DAILY tables.

Hope this helps!

Victor