Has anyone tried syncing HubSpot marketing data to a custom data warehouse?

I have recently completed a migration of our marketing event pipeline from a purely SaaS-based analytics setup to a custom data warehouse (BigQuery) with HubSpot as the primary source. The objective was to enable complex, historical attribution modeling and join marketing engagement data with application-level events from our Kafka ecosystem. While the sync is operational, the architectural trade-offs and data integrity nuances are substantial and worth documenting for anyone considering a similar endeavor.

The primary methodologies for extraction are the HubSpot REST API and the HubSpot Webhook system. Each presents distinct challenges:

* **REST API (for full/partial historical syncs):** Rate limiting is the principal constraint. The standard tier allows for 100 requests per 10 seconds, which must be carefully managed via exponential backoff. For large datasets like `contacts` or `deals`, you must handle the `has-more` pagination and the 10k record limit per query efficiently. A naive sequential sync will not complete in a reasonable timeframe for sizable instances.
* **Webhooks (for near-real-time incremental updates):** This is more event-driven but requires robust idempotency and ordering guarantees in your consumer. HubSpot does not guarantee exactly-once delivery for webhooks. You will receive duplicates, especially during their internal maintenance events. Furthermore, webhooks only cover a subset of object types and properties.

The data model transformation is non-trivial. HubSpot's API returns nested JSON structures with property keys in the format `{custom-field-name}`. A direct ingestion results in a schema that is difficult to query. You must flatten and type-cast these properties into a relational model. Consider this simplified example of a contact sync using a Python-based loader:

```python
# Example of transforming a HubSpot API contact object
raw_contact = {
"id": "123",
"properties": {
"firstname": {"value": "Jane"},
"lastname": {"value": "Doe"},
"custom_property": {"value": "some_value"},
"createdate": {"value": "1640995200000"} # Unix epoch milliseconds
}
}

# Flattened record for warehouse insertion
flattened_contact = {
"contact_id": raw_contact["id"],
"firstname": raw_contact["properties"].get("firstname", {}).get("value"),
"lastname": raw_contact["properties"].get("lastname", {}).get("value"),
"custom_property": raw_contact["properties"].get("custom_property", {}).get("value"),
"createdate": epoch_ms_to_timestamp(raw_contact["properties"].get("createdate", {}).get("value"))
# Requires parsing all properties dynamically for a full sync
}
```

For reliable orchestration, I recommend a two-layer approach: a batch historical sync using the REST API (leveraging incremental endpoints where possible, like `contacts/v2/list/updated/recent?count=`), coupled with a streaming layer that consumes webhooks and publishes them to a durable message queue (e.g., Kafka, Google Pub/Sub) for eventual warehouse insertion. This provides a replay mechanism for any downstream processing failures.

Key benchmarks from our implementation: initial full sync of ~2 million contacts took approximately 18 hours with careful parallelization and rate limit adherence. The webhook pipeline processes an average of 500 events per minute with a P99 latency of 4.2 seconds from HubSpot event to warehouse table. The largest ongoing issue is schema drift; new or changed custom properties in HubSpot do not automatically reflect in the warehouse tables, requiring a monitoring process for the `property_groups` endpoint.

My central question for the community is regarding schema management strategies. Have you implemented a generic, self-adapting pipeline that can detect new HubSpot properties and alter warehouse tables accordingly, or is a manual curation step still necessary for production reliability? Additionally, for those who have moved beyond simple contact/company sync, what has been your experience with syncing complex objects like Engagements (notes, tasks, emails) and their relationships in a query-efficient star schema?