Not able to retrieve the company id associated to a deal

[pelletierjf]

Currently, the deal model doesn't contain the company id to which the deal is associated. This is probably because the assocaited company id is stored in another table (DEAL_COMPANY with a 1..n relation with DEAL and COMPANY). This other table supposes that a deal could be associated to multiple companies but actually, Hubspot doesn't allow that. So it would be very useful to have the company_id in the deal model.

Otherwise, at least having the deal_company model would be great. Currently, with the package, there is no way to retrieve the company id and I have to make my own staging model querying the source data.

[fivetran-joemarkiewicz]

Hi @pelletierjf your issue has been address within PR #29! Be sure to keep an eye out for the hubspot_source v0.3.0 release to be published to the dbt hub by early next week!

If the issue still persist after the release is live, please open a new issue.

[fivetran-jamie]

hey @pelletierjf -- so we included company_id in the final hubspot__deals model in the most recent release, but this appears to cause testing issues as some folks do indeed have multiple company_id's associated with deals (i'm not quite sure how, as hubspot's API docs also implied that this was 1:1).

we were considering either removing company_id from that deals model (and just keeping the stg_hubspot__deal_company model for users to join with), or aggregating the companies with a string_agg and creating a companies column in the deals model. Interested to get your thoughts -- would the aggregation solution be useful to you? otherwise we'll just remove the logic from the transform package and keep stg_hubspot__deal_company

[pelletierjf]

I guest the best is to stay close to the Hubspot model and keeping stg_hubspot__deal_company.
Thanks