HubSpot + Snowflake integration

A warehouse team and a revenue team usually meet over the same complaint from opposite directions. The warehouse holds the truth about accounts, usage and revenue, and nobody in sales can see it without asking. Sales holds the truth about who said what and when, and nobody in analytics can join it to anything without an export. Both teams have been promised a single view for years.

The outbound half of that is solved and costs nothing to run. HubSpot’s own Snowflake share puts CRM data in the warehouse for querying, which answers the analyst’s question and none of the seller’s. What a seller needs instead is the account’s health score on the company record, the open renewal as a deal, and a workflow that raises a task when usage drops.

That is the inbound half, and it is where the work is. Warehouse rows have to land on the right HubSpot record without creating duplicates, associate to records that may not exist yet, and repeat every hour without eating the portal’s request budget. None of it is difficult, but all of it is exacting, and a scheduled table load leaves that part to you.

The decision worth making early is which fields the warehouse owns. A property written by a pipeline should be read-only for humans in the CRM, and a property humans edit should never be in the pipeline’s mapping. Where both sides write the same field, the sync becomes an argument that runs hourly.

Four inbound streams cover most of what revenue teams ask for. Accounts become companies, keyed on the warehouse account identifier held in a unique HubSpot property. Users or contacts become contacts, keyed on a source identifier with email only as a tie breaker. Subscriptions or orders become deals, one way. Usage and health metrics become numeric properties on the company, rather than a custom object, because properties are what workflows and lists can act on.

Product events are the fifth, and they are different in kind. They belong on the timeline as custom behavioural events, deduplicated against a ledger of what has already been sent, and they count against the account’s monthly event allowance, so the pipeline filters to events a human would care about.

In the other direction, the HubSpot tables inside the share are read only. They are excellent for joining CRM history to warehouse facts, and they are not a write path.

The shape is polling on a watermark. A scheduled job asks the warehouse for rows changed since a stored position, either by reading a stream or by filtering on a modified timestamp, then batches the results into HubSpot upserts of a hundred records, then runs associations as a separate pass once the parents exist.

State lives in two places. The watermark, which says where each stream has read up to, and a ledger of what was sent, which makes a replay idempotent rather than duplicative. Both sit in the warehouse, because that is the platform the data team already backs up and monitors.

Failures are expected rather than exceptional. A batch that fails goes to a dead letter table with its payload and the API response, an operations endpoint replays a window on demand, and a reconciliation step compares row counts on both sides after each run and alerts when they disagree.

Start with the mapping, not the tool. A table or view that maps straight onto HubSpot objects, even several of them with associations defined in the mapping, is what HubSpot’s beta direct sync covers on Data Hub Enterprise, and buying a build for that is waste. Add a rule that decides which rows qualify, and the direct sync, which cannot filter, needs warehouse views that exist only to feed it. Add timeline events or a write-back, and it is out of the picture.

Volume decides as much as shape does. Tens of thousands of changed rows an hour is comfortable for any route. Millions is a conversation about batching and about the daily request budget, which every private app in the account shares, while the 10 second burst limit is counted per app.

Ownership is the part people forget. A reverse ETL subscription needs an owner as much as a service does, and it bills by the row while it waits.

1. Scope. We profile the tables, count changed rows per day, and check each one for a stream or a reliable modified timestamp.
2. Architect. We agree which side owns each field, the unique identifier property on each object, and where the watermark and ledger will live.
3. Build. A scheduled job with batched upserts, a separate associations pass, a dead letter table and a replay endpoint.
4. Validate. A full load into a HubSpot sandbox portal from a warehouse clone, with counts compared object by object.
5. Operate. Reconciliation after every run, alerts on a count difference, and a quarterly review of the mapping as warehouse models change.