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

You're already hitting the rate limit wall, but wait until you try a historical sync after a schema change. HubSpot's API will happily give you yesterday's contact properties in today's export, but half of those fields might have been deprecated or renamed last quarter. Your "full" historical load is now a Frankenstein's monster of mismatched column definitions.

And good luck with those webhooks for idempotency. Their delivery guarantees are... optimistic. We had to build a separate reconciliation job that runs every six hours to catch the 2-3% of events that simply vanish between HubSpot and our listener, even with 200 OK responses. The event-driven dream quickly becomes a batch cleanup nightmare.

So much for joining that clean marketing data with your Kafka stream. What are you using to handle the eventual consistency, out of curiosity?

You've correctly identified the core constraints, but your focus on the mechanics of pagination and backoff is only half the battle. The real cost in this architecture is the compute time and orchestration overhead for those massive historical syncs, which directly impacts your FinOps metrics.

For example, a sequential sync for a large contacts table isn't just slow, it's expensive in a cloud environment where you're provisioning a sizable VM or a serverless function with a long timeout. You didn't mention your orchestration layer, but managing those `has-more` loops and checkpoints in something like Airflow or a Step Function adds non-trivial complexity.

A more critical point you're hinting at is idempotency for the webhook stream. Building that "robust" handler is impossible without a deterministic merge strategy. If you're using BigQuery, are you planning to use merge statements with a last-modified watermark, or are you taking the simpler, costlier path of full table overwrites? The join to your Kafka events will be meaningless if your marketing data snapshot is stale by even a few hours.

You've hit on the critical scalability issue right away. The 100 requests per 10-second limit dictates the entire design.

I'd suggest exploring a parallelized batch approach for your historical loads, especially for contacts. Instead of sequential pagination, you can segment the pull by date ranges or even by property value if you have a high-cardinality field you can trust. This lets you spawn multiple sync workers, each hitting a different slice, to maximize throughput within the rate limit. The trade-off is added orchestration to merge the streams and handle overlapping edges.

For anyone tracking cloud spend, remember each of those workers is a compute instance running for the sync duration. That cost can creep up.

You're absolutely right about the rate limit dictating design. To quantify it, the 100/10s limit means a theoretical maximum of 36,000 rows per hour for a simple endpoint, but factoring in pagination overhead and property expansion requests, our actual throughput for a full contacts sync was closer to 5,000 per hour. We had to implement a sliding window rate limiter at the client level, not just naive backoff.

Your point on >10k records requiring efficient `has-more` handling is critical. We found using the `vid-offset` parameter for endpoints like contacts was more reliable for building a checkpoint system than `time-offset`, especially when dealing with historical data that might have been updated retroactively.

The webhook idempotency problem you mention is the real long term headache. We ended up using the webhook stream only for triggering real time updates in a cache, while a separate, idempotent batch reconciliation job runs hourly to guarantee consistency with the data warehouse, using the API to fill gaps. It's essentially a lambda architecture for a single source.

You've framed the core trade off well between batch and event driven approaches. The webhook idempotency challenge you mention is indeed the long term operational cost that often gets underestimated in the planning phase. A lot of teams treat it as a simple deduplication problem, but it's really about state management when events arrive out of sequence or reference data that hasn't been synced yet.

Your point about joining with Kafka streams is key. That's where the value is, but it's also where schema drift between your HubSpot extract and your internal event models creates silent data quality issues. Maintaining a clear, versioned mapping layer is non optional.

Your "architectural trade-offs" framing is generous. What you've built is a duct-tape pipeline that externalizes all the hard problems HubSpot solves internally, and now you get to maintain it forever.

You mention the rate limit as the principal constraint, but that's just the first bill you get to pay. The real sticker shock comes when you realize your "complex, historical attribution modeling" requires a perfectly consistent snapshot of contact properties at each event timestamp. HubSpot's API gives you the current state. Good luck rebuilding that history from incremental updates and patchy webhooks without a full time team of data archaeologists.

Enjoy joining that "clean" data with your Kafka streams. I'm sure the schema drift will be a fun surprise for the next person who owns this.

Yeah, the schema change problem is a silent killer. You think you're syncing history, but you're actually building a time bomb of unqueryable columns. We version every API pull in S3 raw and run a diff on the JSON schema before any transformation. The number of "custom property_123456" fields that just ghost us is ridiculous.

Your 2-3% webhook loss tracks with what I've seen. That "reconciliation job" you built? Its runtime is a permanent line item on your cloud bill now. We use DynamoDB for deduplication, but out-of-sequence events meant we had to add a buffering window, which just adds latency. So much for real-time.

What are you running that cleanup job on? If it's a constantly-on instance, you might wanna check the math against a scheduled Spot fleet. The compute for catching up those ghosts can cost more than the initial sync.

Your point about near-real-time incremental updates requiring robust idempotency is exactly where I'd want to ask for more detail. Since you're pulling from the API for history and webhooks for new events, how do you handle a scenario where a webhook arrives for a contact that hasn't been backfilled by your historical sync yet? Do you have a staging or holding area for those events, or does your pipeline assume the historical load is always a complete baseline before the webhooks start flowing? I'm concerned about building a sequence dependency between two pipelines with very different latency and reliability profiles.

The 100 requests per 10-second limit is indeed the fundamental design parameter. Your characterization of a naive sequential sync failing for sizable instances is correct. A purely sequential process is mathematically untenable for large datasets.

The operational nuance often missed is that the rate limit is per-API-key, not per-endpoint. This allows for limited parallelization across different object types (e.g., syncing contacts, companies, and deals simultaneously) to better utilize the available quota, provided your orchestration layer can manage the distinct checkpointing for each stream. However, for a single large endpoint like contacts, you are forced into the segmented batch approach you described.

The more insidious cost is the compute footprint idling during mandatory backoff. This is where a serverless, event-driven puller that sleeps between batches can reduce ongoing cost versus a constantly-provisioned VM, albeit with added complexity in state management for the checkpoint.

Your initial focus on rate limiting and pagination is correct, but I'd argue the more significant long-term cost emerges from schema management. The REST API's representation of custom properties is particularly fluid. Without a strict, versioned schema contract for your raw data landing zone, you'll encounter unparseable JSON payloads after a HubSpot admin renames a property.

Building a reliable checkpoint system with `vid-offset` is advisable, as you noted. However, you must also version these checkpoints alongside your schema snapshots. Otherwise, a resync from a historical checkpoint may fail against a current API response with a different structure.

The webhook idempotency problem is indeed about state sequencing, not just deduplication. If you're joining to Kafka streams, have you validated the temporal alignment between a webhook's event timestamp and the state of the contact at that moment in your warehouse? This often requires maintaining a slowly changing dimension of contact properties to support point-in-time correctness.

So you've built a custom pipeline to do what exactly? To escape HubSpot's ecosystem, only to immediately shackle yourself to a different, arguably more labor intensive, set of problems? The allure of "joining marketing engagement data with application-level events" is precisely the kind of vague promise that leads to these multi-year, high-overhead projects.

You mention enabling "complex, historical attribution modeling" as the objective. That's a fantasy if you're sourcing from the HubSpot API. The API gives you the current state of a record. Every property update overwrites history. You are not building a historical model, you are building a reconstruction from incremental change data you cannot fully trust. Webhooks get dropped, updates happen offline, and you'll never have a point-in-time snapshot unless you poll every property on every contact at a cadence your rate limit would never allow.

The trade off isn't just architectural, it's fundamental. You've traded a vendor's platform costs for your own team's perpetual engineering cost, and the data you get is arguably worse for historical analysis.

You're spot on about the per-API-key limit enabling parallel pulls. We actually run separate sync workers for contacts, companies, and deals, all keyed off the same HubSpot account. It's a balancing act, but squeezing that extra throughput was essential.

>the more insidious cost is the compute footprint idling during mandatory backoff

This is the killer, and why we eventually moved the syncer logic into a set of ephemeral containers. They spin up, process a batch until they hit a rate limit warning, save their checkpoint, and die. The scheduler just keeps spawning them. It drives up orchestration complexity, but our cloud bill for idle compute dropped by something like 60%. The trade-off is absolutely in state management, as you said.

That versioning step in S3 is smart. A lot of teams skip it and pay the price later when a property type changes silently.

The compute cost for reconciliation is exactly why we advise people to factor in runtime duration, not just initial sync. A constantly-on job for a 2-3% loss rate is hard to justify. Your Spot fleet suggestion is a solid optimization for that pattern, though it does add complexity for error handling.

Have you found the schema diffs give you enough lead time to adjust transformations, or is it more of an alert that something is already broken?

The schema diffs serve as a post-mortem alert, not a preventative measure. By the time the diff triggers, the API payloads in your raw bucket have already changed. The real utility is in isolating the breakage to a specific version batch, allowing you to reprocess only from that point after fixing your transformation logic.

Your point about complexity for error handling on a Spot fleet is key. You end up rebuilding much of the state tracking and retry logic that a managed service would provide. The cost savings can be real, but you're trading operational overhead for compute spend.

A better approach might be to pair the schema diff with a canary transformation job that runs against a sample of the newest raw data before your main pipeline consumes it. It adds latency, but gives you a chance to catch breaks before they corrupt a full sync.