HubSpot to Snowflake: Integration Methods, Data Modeling & Sync Guide

HubSpot data reaches Snowflake through HubSpot's own Snowflake Data Share, a CSV export loaded with COPY INTO, Snowflake's Openflow connector for HubSpot (in preview), or an API pipeline that polls HubSpot's CRM APIs and loads tables you own. The share suits Data Hub Enterprise accounts content with read-only views; a pipeline suits teams that want HubSpot tables in their own schema.

HubSpot has no change log to read, so every pipeline polls. Freshness and cost are set by HubSpot's API limits at one end and Snowflake warehouse time at the other, and deleted or merged records go unnoticed unless the pipeline looks for them. Estuary is one managed option here: its HubSpot connector polls the CRM APIs back to back into durable collections, and Snowflake loads follow a schedule you set.

Choosing a HubSpot to Snowflake method

Whichever tool you shortlist, check how it catches deleted and merged records, whether associations arrive, how it refreshes calculated properties, and how much of the shared API limit it spends.

How HubSpot to Snowflake API syncs work

HubSpot serves current record state over REST, so a sync is a polling loop:

• Backfill pages through each object's list endpoint (GET /crm/v3/objects/{object}), 100 records per request.
• Incremental reads ask the CRM Search API for records whose hs_lastmodifieddate (lastmodifieddate on contacts) is past the cursor, then batch-read them 100 at a time. Search stops at 10,000 results per query, and updates can take a few moments to become searchable, so cursors need overlap.
• Associations, such as a deal's contacts, come inline on list reads or from the associations API.
• Blind spots: deleted (archived) records drop out of list and search results, and calculated properties change without touching hs_lastmodifieddate.

Snowflake then gets staged files merged by record ID, or appended rows.

HubSpot requirements

A sync authenticates with an OAuth app, installed by a user with Super Admin or App Marketplace access permissions, or with the token of a legacy private app a super admin creates under Development → Legacy apps. Grant a read scope per object, such as crm.objects.deals.read, plus matching crm.schemas.*.read scopes. Custom-object scopes are Enterprise-only.

The CRM Search API allows five requests per second per account.

Two rules prevent most incidents:

1. Budget the backfill. One pass over a million contacts is at least 10,000 list requests, before associations.
2. Split search windows by time. Paging past 10,000 results returns a 400, and a bulk update can give thousands of records one timestamp.

Snowflake requirements

Create a database, schema and warehouse, plus a key-pair service user whose role can use them, as Snowflake is phasing out single-factor passwords for service users during 2026.

• An X-Small warehouse with AUTO_SUSPEND = 60 suits CRM loads.
• HubSpot timestamps are UTC, so TIMESTAMP_LTZ or UTC-normalized TIMESTAMP_NTZ keeps them comparable. An explicit TIMESTAMP_TYPE_MAPPING must agree with the loader.
• Keep QUOTED_IDENTIFIERS_IGNORE_CASE off, and quote lowercase property columns exactly.

Setting up HubSpot to Snowflake replication

1. Inventory objects and properties with GET /crm/v3/properties/{object}.
2. Create the credential with read scopes for those objects.
3. Prepare Snowflake as above.
4. Choose the table shape: properties in one VARIANT column, a typed column each, or both; associations as ID arrays or link tables.
5. Backfill each object while watching daily API usage.
6. Sync incrementally on hs_lastmodifieddate with overlap, schedule full re-reads for calculated properties, and set the Snowflake load interval.
7. Validate. Compare row counts with HubSpot. Change a deal amount, wait one load interval, and query it. Delete a test contact, merge two others, and check Snowflake. Add a custom property and confirm it appears.

What happens after the initial load?

• Updates bump hs_lastmodifieddate; the next poll merges the current state by ID. Two edits between polls arrive as one version unless property history is read.
• Deletes move a record to HubSpot's recycle bin for 90 days and out of default list and search results, so polling never sees it go. Its last version stays in Snowflake unless IDs are reconciled.
• Merges fold two records into one with a new record ID by default, so neither original ID survives. The new record's hs_merged_object_ids lists them, separated by semicolons.
• Calculated properties (formulas, rollups) stay stale until the next full re-read.
• New properties become columns in tools that re-read definitions, NULL for earlier rows until reloaded.

HubSpot to Snowflake data type mapping

Property values arrive as strings

HubSpot's API returns property values as strings, so a loader takes types from property definitions or guesses them. Loaded as FLOAT, a double with about 15 significant digits, currency isn't exact; map amounts to NUMBER(p,s). Date and datetime values may be ISO 8601 strings or millisecond epochs, so an inferred type can land as text or integer. Check timestamp columns after the first load, and convert epochs with TO_TIMESTAMP_LTZ(value::number, 3).

Performance, latency and Snowflake cost

Two clocks set freshness: HubSpot polling, limited by API budget, and Snowflake loads, limited by warehouse credits.

HubSpot API budget

• A Search-based poll costs a search per object, then a batch read plus one associations read per related object type for every 100 changed records.
• A calculated-property refresh re-reads every record, so run it daily, not hourly.
• Marketplace OAuth apps get 110 requests per 10 seconds per installing account, Search excluded; HubSpot publishes a daily cap only for private apps.

Snowflake warehouse time

Tune the load interval first: each load resumes the warehouse for at least a billed minute, however few CRM records changed. Append-only data such as email events can skip the MERGE; CRM objects can't, because updates must replace rows.

Production considerations

• Retries. Back off on HTTP 429 and keep errors under 5% of daily requests, as HubSpot asks. Merging by ID absorbs records re-read after a resume.
• Source impact. A private-app backfill can exhaust the account's shared daily limit, which resets at midnight in the account's time zone; start such backfills early in that day.
• Monitoring. Alert on 429 rate, daily usage (Development → Monitoring → API call usage), time since the last Snowflake load, and record-count drift.

Common problems and troubleshooting

429 errors and an exhausted daily API limit

Requests fail with HTTP 429 and "policyName": "DAILY" ("You have reached your daily limit."), or TEN_SECONDLY_ROLLING for bursts. OAuth responses omit the daily-remaining header, so check API call usage in HubSpot, which includes installed third-party apps. Throttle for bursts. For the daily limit, drop unused objects and association types, refresh calculated properties less often, and pause backfills until the reset.

Deleted and merged records still in Snowflake

Snowflake holds more contacts than HubSpot. To find rows absorbed by a merge:

sql
SELECT DISTINCT m.value::number AS absorbed_id
FROM contacts s,
     LATERAL FLATTEN(input => SPLIT(s.properties:hs_merged_object_ids::string, ';')) m
WHERE m.value::number IN (SELECT id FROM contacts);

Fixes, cheapest first: a view excluding absorbed IDs; a scheduled job that reads archived records (archived=true on the list endpoint) and deletes those IDs; a full reload.

Objects or associations missing after authorization

Tickets, line items or custom objects never appear, or calls fail with 403 "This app hasn't been granted all required scopes to make this call." HubSpot grants scopes at install time, so add the scope and re-authorize, or update the private app.

Using Estuary for HubSpot to Snowflake

On this route, the HubSpot capture writes a collection per HubSpot object, and the Snowflake materialization loads each into a table keyed on record ID for CRM objects. Against the concepts above:

• Polling: each CRM object runs two passes. A realtime pass re-reads HubSpot's recent-changes endpoints (search for newer objects, such as custom objects) as soon as the last poll ends, unless the binding sets an Interval. A delayed pass trails one hour behind, mostly on the Search API, and emits anything the fast pass missed.
• API budget: Estuary's OAuth app is listed in HubSpot's App Marketplace, so the 110-per-10-seconds limit applies. On a 429, the connector slows down and retries, up to 5 minutes apart.
• Load interval: Snowflake loads every 30 minutes by default; repeated edits to a record between loads become one merge.
• Deletes: CRM objects never emit deletes, so Hard Delete can't remove deleted or merged-away records. It does apply to full-refresh lists (owners, deal pipelines, properties, forms).
• Calculated properties: a daily re-read of every record (23:55 UTC by default) refreshes them. The partial documents merge on standard bindings; delta-update bindings get rows with other properties NULL.
• Associations: ID arrays on the parent; deals carry contacts, engagements and line_items. Labels aren't kept, and only the first page per record is read.
• Property history:Capture Property History adds propertiesWithHistory and halves the records fetched per request.
• Types: properties land as strings in one PROPERTIESVARIANT column. At Depth 2, each also gets a column such as "properties/amount"; decimal numbers then load as FLOAT, and castToString keeps the exact text. Publishing HubSpot's property list as a schema lifts the field limit from 1,000 to 10,000.

Implementing HubSpot to Snowflake with Estuary

These steps adapt Estuary's general data flow guide to this route.

1. Complete the prerequisites in HubSpot requirements and Snowflake requirements. Estuary also needs:
   • A HubSpot user with Super Admin or App Marketplace access to authorize Estuary's OAuth app; the dashboard has no token option.
   • Snowflake objects from the connector's setup script (role, service user, X-Small warehouse), with the user's public key registered for key-pair authentication.
2. Create the HubSpot capture. On the Sources page, click New Capture, choose Hubspot Real-time (not HubSpot (deprecated)), name it, and fill in the endpoint properties:
   • Authentication: click the authenticate button and approve HubSpot's consent screen. Eight read scopes are required; optional ones such as tickets and crm.objects.custom.read decide which objects are discovered.
   • Capture Property History: off by default.

3. Select objects. Click Next. Output Collections lists one binding per object your scopes allow, custom objects as custom_<name>.
   • Remove unused objects; each costs API calls.
   • Interval is zero on CRM objects (continuous polling); longer intervals spend fewer calls.
   • Calculated Property Refresh Schedule defaults to 55 23 * * *; clear it where calculated properties don't matter.
   • Keep Automatically keep schemas up to date on.

Click Save and publish. Backfills start, with polling alongside.

4. Create the Snowflake materialization. From the HubSpot capture's details page, click Materialize, pick Snowflake, and complete its endpoint properties:
   • Host (Account URL): the hostname ending in .snowflakecomputing.com, without https://.
   • Database, Schema, Warehouse and Role: as created by the script.
   • Snowflake Timestamp Type: TIMESTAMP_LTZ or TIMESTAMP_NTZ (normalize to UTC).
   • Authentication: Private Key (JWT) with User and Private Key; User Password remains but is deprecated.
   • Hard Delete: matters only for full-refresh lists.

5. Choose the sync schedule.Sync Frequency, under Sync Schedule, starts at 30 minutes (options 0s to 4h). For frequent loads in office hours only, set Timezone, Fast Sync Start Time, Fast Sync Stop Time, and Fast Sync Enabled Days; outside the window, loads run every 4 hours.

6. Shape each table. Click Next, choose a naming convention, and review each binding under Source Collections:
   • Field Depth: Depth 1 (default) keeps properties in PROPERTIES; Depth 2 adds a column per property (field selection).
   • Delta Updates: only for append-only data such as email_events.
7. Publish. Click Save and publish. Snowflake gets one table per object, and the backfill loads immediately, not at the next scheduled sync.
8. Verify that data is landing.
   • The capture details page shows PRIMARY in its Shard Information card; open a collection from the Collections list, and its Data Preview shows records with a properties object.
   • The materialization details page shows Connector Status and charts Data Read rising as the backfill lands.
   • In Snowflake, run SELECT id, properties:dealstage::string, properties:amount::number(18,2) FROM deals LIMIT 10;, then the checks in Setting up HubSpot to Snowflake replication.

How Estuary handles API budget, rebuilds and extra destinations

Each HubSpot object lands in a collection, kept as JSON files in cloud storage: your own bucket, or Estuary's trial storage. Destinations load from those files, not from HubSpot, so a second materialization (an operational database, say) reads the collections' history and follows changes while HubSpot is polled once.

What a rebuild costs in HubSpot calls

A rebuild reaches back only as far as collection retention: about 20 days of HubSpot documents on trial storage, all of them in your own bucket. A dataflow reset refills collections from HubSpot and truncates the Snowflake tables, which stay incomplete until the read finishes.

New properties and custom objects

With Automatically keep schemas up to date on, a new custom object becomes a new binding and a new property widens the schema. At Depth 2, it becomes a nullable column, NULL for earlier rows until the binding is backfilled.

When another approach may make more sense

• Data Hub Enterprise, querying in place: HubSpot's Snowflake Data Share needs no pipeline or API budget, drops deleted records from its objects view, and keeps the last 45 values of each contact property (20 for other objects). HubSpot suggests copying large data sets out for query speed.
• Snowflake-only, preview acceptable: the Openflow Connector for HubSpot authenticates with a private-app token and merges incremental loads by update timestamp; its documentation doesn't describe deletes.
• One-time migration: a CSV export of current values (by default up to 1,000 associated IDs per column) and COPY INTO, with no history or refresh.
• Deletes needed within minutes: a private app's deletion and merge webhooks report what polling can't. Estuary's HTTP Ingest connector can collect them beside the HubSpot capture for an anti-join in Snowflake.

Related resources

• Estuary: HubSpot capture connector, Snowflake materialization, sync schedules, backfills and dataflow resets
• HubSpot: API usage guidelines, CRM search, merging records, Snowflake Data Share
• Snowflake: Openflow Connector for HubSpot, key-pair authentication

To try the route, follow Estuary's data flow guide or create a HubSpot capture in the Estuary dashboard.