topic Re: Best practice for extracting data in APIs & Integrations

Best practice for extracting data

nmfoss — Thu, 25 Jan 2024 09:57:28 GMT

Hi, I am currently developing an extraction tool using the HubSpot APIs to extract data from HubSpot into a data warehouse.

So far I am not quite sure what is the best practice for doing such extractions.

Lets use Deals as an example.

I want to extract all deals, full load. I have then 2 options as far as I know. I can use the GET list endpoint:

/crm/v3/objects/deals

with a maximum limit of 100. I then need to iterate over the paginated results.

Then what I do not understand is that there is also a batch version of this, which I assume should be more apropiate for such large requests. But to my understanding I will then need to first request the same above endpoint to extract all Deal IDs, and then aggregate those into a list and perform the POST call to:

crm/v3/objects/deals/batch/read

which will also return a result of 100 deals per page.

So my confusion is, for a full extraction, what is the best practice? Is there something I am misunderstanding about the batch endpoint? Because I do not see any benefit of using it over the plain GET list endpoint.

Sincerely,

NM Foss

Re: Best practice for extracting data

Jnix284 — Thu, 25 Jan 2024 13:03:27 GMT

Hi @nmfoss, happy to help!

Batch endpoints let you create, read, update, and delete multiple object records in a single request. Each HubSpot standard object has batch endpoints, so you can work with multiple records simultaneously. You can also batch-create associations, pipelines, and pipeline stages with some endpoints. Batch endpoints also have limits.

Caching data, batch APIs, and webhooks can prevent exceeding API rate limits.

Webhooks are a great way to keep data synced between integrations and a HubSpot CRM platform. They're an alternative to scheduled API calls to sync data prone to exceeding rate limits and lapses in synchronization. Webhook subscriptions are specific.

I'm just getting started with APIs, @Jaycee_Lewis can you jump in to help further?

Re: Best practice for extracting data

johnelmer — Fri, 26 Jan 2024 14:02:36 GMT

@nmfoss You are taking on a major project! There are apps in the HubSpot App Marketplace that can do exactly what you want, and will sync all of the objects and properties that are associated with your deals (ie contacts, companies, etc).

For example, we offer an app in the HubSpot App Marketplace called MS SQL Server for HubSpot . The app pulls your HubSpot data into an Azure SQL data warehouse via prebuilt API connectors. A SQL data warehouse in Azure is included in the cost. If you have a data warehouse already, there is another app called Database Sync for HubSpot.

These apps have hundreds of installs by HubSpot users, offer free trials and are very cost effective solutions.

Re: Best practice for extracting data

nmfoss — Mon, 29 Jan 2024 11:56:50 GMT

Hoping for an ansver @Jaycee_Lewis 🙂

Re: Best practice for extracting data

nmfoss — Mon, 29 Jan 2024 12:01:54 GMT

We are creating our own connector so using existing solutions is not an option, therefore it would be nice to know the best practices for using the API. It is not very clear from the documentation. And extracting data I assume is one of the main usecases for an API.

Re: Best practice for extracting data

Jaycee_Lewis — Wed, 31 Jan 2024 21:59:26 GMT

Hey, @nmfoss 👋 Thanks for your question.

In general, the batch endpoint `/crm/v3/objects/deals/batch/read` is an alternative to the “Get by ID” endpoints rather than a direct substitute for “Get all” endpoints.

The primary use of batch endpoints is to efficiently retrieve specific records by their IDs in a single request. This is useful when you already know the IDs of the deals you wish to extract.

Your approach of iterating over the paginated results of the /crm/v3/objects/deals endpoint is the optimal approach, as it does not require you to pre-fetch IDs and can effectively extract all deals data.

Have fun building! — Jaycee

Re: Best practice for extracting data

Jnix284 — Thu, 01 Feb 2024 11:57:46 GMT

Thank you @Jaycee_Lewis, so clear and helpful as always!

Re: Best practice for extracting data

nmfoss — Tue, 06 Feb 2024 14:32:30 GMT

Thank you for the reply!

We are going with the GET list endpoint.

Re: Best practice for extracting data

arlogilbert — Sun, 29 Dec 2024 09:51:08 GMT

If you want your data out and easily queryable in bulk, use the HubSpot native Snowflake connector. It costs nearly nothing to have a tiny snowflake instance if you just use it to pull your data down every X minutes as long as you're properly doing an incremental query and not doing a full table dump. Very very cheap, easy option that also likely makes your project obsolete after you connect snowflake as most warehouse tools have a direct connect import from snowflake so it's point & click at that point.

The services that were mentioned above work well. I've tried them, ultimately not great for our use case for reasons that had nothing to do with the product. It wasn't expensive nad it did what we need.

You can buy StitchETL and set up a one-way sync from HubSpot into the database of your choice.

But the fastest way is to setup an app (I don't recall whether private apps support webhooks now or not)... but either private or a developer account. You don't have to really do any coding in there, you just set up the account, define the app, and then connect it to your business hubspot account. In there you can configure near real time webhook from HubSpot into whatever http endpoint you want. Unlike with a workflow, you can subscribe to all events on all object types.