HubSpot to Snowflake: how to load CRM data your team can actually model

How do you load HubSpot into Snowflake?

You load HubSpot into Snowflake by authenticating to the HubSpot CRM API v3 with a private app token, reading each object with its properties and associations, landing the batch in a staging table, and running a MERGE into target tables keyed on the object id. HubSpot ships no native Snowflake driver, so a managed connector or a scheduled job has to sit in the middle and move the data.

The pipeline has three moving parts. First, auth: a private app (or OAuth app) scoped to the CRM objects you need, which gives you the token every request carries. Second, the read side: the v3 API exposes contacts, companies, deals, tickets, and engagements or events, each with a properties model that mixes HubSpot's default fields with the custom properties your team has added. You ask for the properties you want by name and page through results with a paging cursor. Third, the write side: a Snowflake warehouse and role with insert and merge rights on the destination schema, plus a stage if you load files before the MERGE.

Watch the limits while you read. HubSpot enforces both a daily call cap and a per-second burst limit, and it returns results in pages you walk with an opaque after cursor rather than an offset. A production job requests only the properties it needs, backs off when it hits a 429, and checkpoints the cursor so a mid-run failure resumes instead of restarting. The managed path is the HubSpot to Snowflake integration, which handles the token, pagination, the watermark, staging, the MERGE, and retries. The shape is the same one you would use for a Salesforce to Snowflake load, just with a different CRM API in front of it.

How do you map HubSpot properties to Snowflake types?

Number properties such as a deal amount map to Snowflake NUMBER, datetime properties (which arrive as epoch milliseconds) map to TIMESTAMP_NTZ stored in UTC, enumeration and dropdown properties map to VARCHAR, and the object id stays a VARCHAR natural key. Get the timestamp cast right at load time or every date filter and cohort report will be off.

The subtlety with HubSpot is the properties model. Every object carries default properties plus custom ones your admins created, and the API hands most values back as strings inside a properties object. You decide the real type on the way in. Money and counts become NUMBER, dates become timestamps, and single-select or multi-select enumerations become VARCHAR (a multi-select is a semicolon-delimited string you can split later). Keep the raw payload in a VARIANT column too, so a property you did not model today is still recoverable without a re-pull.

HubSpot object / property	HubSpot type	Snowflake type	Note
deal.amount	number	NUMBER(38,2)	Pipeline value. Use NUMBER, never FLOAT, so revenue rollups do not drift
deal.closedate, contact.createdate	datetime (epoch millis)	TIMESTAMP_NTZ	Divide the millis to seconds and cast in UTC. This is what cohort and velocity models read
hs_lastmodifieddate	datetime (epoch millis)	TIMESTAMP_NTZ	Store in UTC. This is the incremental watermark, so treat it as an absolute instant
deal.dealstage, contact.lifecyclestage	enumeration	VARCHAR	Internal stage id, not the label. Join to a pipeline reference table for display names
contact.email, company.name	string	VARCHAR	Plain text. Email is a natural dedupe key for contacts but is not the primary id
id (vid / object id)	string	VARCHAR	Opaque HubSpot id. Keep as VARCHAR and MERGE on it; do not cast to a number
hs_object_source, multi-select props	enumeration (multi)	VARCHAR	Semicolon-delimited. Store raw, split into an array downstream if you need it normalized

Cluster large object tables on the column you filter on most (often hs_lastmodifieddate or a stage), and keep the VARIANT raw column beside the typed columns. That way a new custom property is a schema addition, not a historical backfill.

How do you load HubSpot incrementally?

You load HubSpot incrementally by using hs_lastmodifieddate as a watermark: filter the CRM Search API with a lastmodifieddate greater-than the last stored value, sort ascending on that field, and page through the changes. Store the high watermark after each successful run, overlap the window by a few minutes, and MERGE on the object id so retries stay idempotent.

The contrast with a full sync is stark. A full sync reads every contact, company, and deal on every run, which burns your daily call budget and grows slower as the CRM does. An incremental load asks the Search API for only the objects whose hs_lastmodifieddate moved since last time, so a run that finds a few thousand changed records finishes in seconds. You still want a periodic full reconciliation (say weekly) to catch anything a watermark can miss, but the day-to-day path should be incremental. Overlap the window slightly because modification times can commit a little out of order, and keep the MERGE idempotent so re-reading a handful of rows costs nothing.

Choosing an extraction method is really a freshness-versus-effort decision. Here is how the three common approaches trade off.

Extraction method	Pros	Cons
Full sync (read every object each run)	Simple to reason about, self-correcting, catches deletes by absence	Slow, burns daily rate limit, scales badly as the CRM grows
hs_lastmodifieddate watermark (Search API)	Moves only changed rows, fast, cheap on rate limit, easy to checkpoint	Misses hard deletes on its own, needs window overlap for out-of-order commits
Webhooks / events	Near real-time, push-based, low steady-state cost	Delivery is not guaranteed, needs an endpoint and a backfill path, easy to miss during downtime

Most teams land on a hybrid: an hs_lastmodifieddate watermark on a schedule for the bulk of the work, optional webhooks for the objects that need to be fresh within minutes, and a weekly full reconciliation to true-up deletes. That is the same pattern behind our walkthrough of how to sync Salesforce to Snowflake, because CRM sources share the same incremental problems.

How do you handle HubSpot associations in the warehouse?

You handle HubSpot associations by loading each object into its own table and storing the links (deal-to-contact, contact-to-company) in a separate bridge, or junction, table. Because associations are many-to-many, a bridge table with the two object ids and the association type is what lets attribution and pipeline models join contacts to deals without duplicating or losing rows.

HubSpot exposes associations through the associations API and inline on each object read. A single deal can associate with several contacts, and a contact can sit under one company but touch many deals. If you flatten those links onto the object rows, you either fan out the deal amount across contacts (and double-count revenue) or you drop links. The clean model is three object tables (contacts, companies, deals) plus a deal_contact bridge and a contact_company bridge, each holding the pair of ids and the association type. Attribution then joins deals to contacts through the bridge, and pipeline reporting joins deals to companies the same way.

Two things keep the model honest. First, idempotency: land every batch in staging, collapse to one row per id keeping the latest hs_lastmodifieddate, then MERGE on id into the target so a re-run after a timeout produces the same state rather than duplicates. Second, deletes: HubSpot archives records instead of hard-deleting them, and an archived contact simply stops appearing in a plain watermark query. Read the archived flag (or reconcile against a full list) and either soft-delete the row with a _deleted_at stamp or remove it, so a contact deleted in the CRM does not linger as a ghost in Snowflake. Once the objects and bridges are in place, a RevOps analyst or a non-technical teammate can ask questions of that data in plain English instead of waiting on a SQL queue.

Should you build this or use a managed integration?

Build it yourself when you have engineers who want to own the token refresh, pagination, watermark, MERGE, and delete handling, and time to maintain them as HubSpot's API evolves. Use a managed integration when you would rather map the objects once and let the connector run the incremental sync, retries, and per-record logging. The API work is not hard, but it is ongoing.

A hand-rolled pipeline is a real amount of code: OAuth or private-app token handling, cursor-based pagination, the daily and burst rate-limit backoff, the hs_lastmodifieddate watermark with window overlap, the staging-plus-MERGE for idempotency, association bridges, and archived-record handling. None of it is exotic, and a capable data engineer can stand it up in a sprint. The cost shows up later, in the maintenance: a new custom property, a changed rate limit, a schema drift, or a silent switch from incremental back to full refresh. Someone has to own that.

A managed connector does the same work behind a mapping UI, with scheduled incremental sync, automatic retries, and per-record logs so you can see exactly which row failed and why. Our pricing is flat by plan (Starter $49, Growth $149, Scale $399) rather than metered per row, so a launch month that touches millions of records costs the same as a quiet one. See pricing for the full breakdown, or read how we think about any data integration platform. To see the HubSpot object mapping against your own portal, start a demo.

Questions

How do you connect HubSpot to Snowflake?

You connect HubSpot to Snowflake by authenticating to the HubSpot CRM API v3 with a private app token, reading each object with its properties and associations, landing the batch in a staging table, and running a MERGE into target tables keyed on the object id. HubSpot ships no native Snowflake driver, so a managed connector or a scheduled job sits in the middle.

How do you load HubSpot into Snowflake incrementally?

You load incrementally by using hs_lastmodifieddate as a watermark: filter the CRM Search API for records changed since the last stored value, sort ascending, and page with the cursor. Store the high watermark, overlap the window by a few minutes, and MERGE on the object id so retries stay idempotent. Add a periodic full reconciliation to catch deletes.

How do HubSpot properties map to Snowflake types?

Number properties like deal amount map to NUMBER, datetime properties arrive as epoch milliseconds and map to TIMESTAMP_NTZ in UTC, enumeration and dropdown properties map to VARCHAR, and the object id stays a VARCHAR natural key. Keep the raw payload in a VARIANT column so a property you did not model today is still recoverable without a re-pull.

How do you handle HubSpot associations in Snowflake?

Load each object (contacts, companies, deals) into its own table and store the links in a bridge table holding the two object ids and the association type. Because deal-to-contact and contact-to-company associations are many-to-many, the junction table is what lets attribution and pipeline models join without double-counting revenue or dropping links.

How do you handle deleted HubSpot records in the warehouse?

HubSpot archives records rather than hard-deleting them, and an archived contact simply stops appearing in a plain watermark query. Read the archived flag or reconcile against a full list, then soft-delete the row with a deleted_at stamp or remove it. Otherwise a contact deleted in the CRM lingers as a ghost row in Snowflake and skews counts.

What are the HubSpot API rate limits to plan around?

HubSpot enforces both a daily call cap and a per-second burst limit, and it returns results in pages you walk with an opaque cursor rather than an offset. A production job requests only the properties it needs, backs off when it hits a 429, and checkpoints the cursor so a mid-run failure resumes instead of restarting from zero.