hubspot-warehouse-sync

What it does

Conveys a workflow that transfers HubSpot CRM data to a data warehouse (BigQuery, Snowflake, or Postgres) including an initial large backfill under a daily rate limit, incremental CDC polling via hs_lastmodifieddate, separate association polling to capture link changes, and schema-drift detection with ALTER TABLE generation. It includes idempotent upsert patterns to avoid duplicate rows on retries and provides guidance for resyncing after schema changes or debugging missing updates or rate-limit burnout.

How it works

• Token bucket rate limiting is used to throttle API calls and prevent exhausting the daily quota, with a daily_budget, burst_limit, and live header adjustments. The code demonstrates a TokenBucket class with acquire and update_from_headers, raising DailyBudgetExhausted when the budget is used up.
• Full backfill uses cursor pagination via the contacts search API (POST /crm/v3/objects/contacts/search) with a 100-record page limit and a 30-day window strategy for large tables. It includes a checkpoint mechanism to resume from the last written cursor, writing a checkpoint after every page.
• Incremental CDC polling uses a property-based poll (hs_lastmodifieddate) to fetch modified contacts, while noting that association changes are not captured by this pass and require a separate association poll.
• Association changes are retrieved via a batch read endpoint (v4/associations/contacts/deals/batch/read) in chunks of up to 100 contacts and compared to warehouse snapshots to detect differences that must be written.
• Schema drift detection includes a fetch of HubSpot properties (crm/v3/properties/contacts), mapping HubSpot types to warehouse types, and a drift check against warehouse columns to identify added and removed columns. It generates ALTER TABLE statements for new columns and comments that removed properties are not dropped in this stage.

When to use it

• Use when building a HubSpot → warehouse pipeline.
• Use when resyncing after a schema change.
• Use when debugging duplicate rows or missing CDC updates.
• Use when recovering from a rate-limit burnout during a backfill.

What it can touch

• Tools declared: Read, Write, Edit, Bash(curl:), Bash(jq:), Bash(python3:*), Grep
• The skill defines Python code for rate limiting, backfill, CDC polling, association polling, and schema drift logic, which interacts with HubSpot APIs and warehouse targets (BigQuery, Snowflake, Postgres). It references endpoints such as /crm/v3/objects/contacts/search, /crm/v3/properties/contacts, and /crm/v4/associations/contacts/deals/batch/read, and uses tokens and rate-limit headers to control access.

Caveats

• The CDC pass explicitly notes that association changes are not captured by the property poll and require a separate association poll.
• Schema drift handling includes adding new columns but explicitly avoids dropping columns as part of drift handling (removal of properties is not dropped here).
• The backfill strategy relies on a checkpoint file to resume; the sample uses a local JSON file at /tmp/hubspot_backfill_checkpoint.json.
• The approach assumes Python packages and environment prerequisites (Python 3.10+, hubspot scopes, and warehouse credentials) are met.