Need help connecting Hubspot tables in SQL using extracted data from API


I have extracted our Hubspot tables using the API, I have all of the data in a SQL database and I am doing some custom reporting for our team.  I am struggling trying to tie the basic tables together.  I am not using any custom fields at this time just: Companies, Deals, Pipelines and deal stages. I have seen the ERD diagram but it is not much help. 

End goal is to show all deals, amount, deal stage, forecast owner, close date, pipeline as it grows each week. (I know that there aresome built in Hubspot reports we can use but this is going to be part of a larger report that will contain outside data we will be incorporating, so we are doing this outside of hubspot) 

does anyone know of any references on how to manage this data? I can tell it is some sort of star/snowflake schema but without primary/joining keys it is proving difficult. 

Any help is appreciated.  

1 Reply 1
Top Contributor | Diamond Partner

@BCascone  You are tackling a complex challenge.  If it proves to be too time-consuming, there are quite a few options available for what you are trying to do.  We offer an app in the HubSpot App Marketplace called MS SQL Server for HubSpot . The app pulls your HubSpot data into a SQL data warehouse via prebuilt API connectors. A SQL data warehouse in Azure is included in the cost.  You can connect this data warehouse to your reporting software.

Once your HubSpot data is in the data warehouse, it automatically refreshes on a scheduled basis. You can connect the SQL data warehouse to any other application that has a SQL connector. The cost is only $69 a month, so it's a question of how valuable your time is.

Did this post help solve your problem? If so, please mark it as a solution.

John Elmer, Bayard Bradford

John Elmer, CEO
Bayard Bradford

Advanced CRM | HubSpot Integrations | CRM-Driven Marketing for Revenue Growth
HubSpot Diamond Solutions Partner