Deal ID duplication in hubspot_deals model

[dwallace0723]

We recently attempted an upgrade to 0.3.0 and noticed that during our CI run, a schema test was suddenly failing:

Failure in test unique_hubspot__deals_deal_id (models/sales/sales.yml)
  Got 5 results, expected 0.
  compiled SQL at target/compiled/hubspot/models/sales/sales.yml/schema_test/unique_hubspot__deals_deal_id.sql

After digging into the test failure, we noticed that the deal_id duplication was stemming from these lines of code. It looks like this logic is a new addition in the 0.3.0 release.

It seems like there is an assumption baked in to the modeling logic here that a Hubspot Company and a Hubspot Deal have a 1:1 relationship. In reality, we have multiple instances where a deal_id is assigned to multiple companies. Could this logic be updated to reflect this reality?

[fivetran-joemarkiewicz]

Hi @dwallace0723 thank you for opening this issue!

This is extremely helpful as it is a relationship we had not experienced with our testing dataset. I will take some time to update this logic to reflect the reality you have shown us. I will respond back here with a working branch dependency you can use to test if my updates resolve the schema test failure on your end.

[fivetran-joemarkiewicz]

@dwallace0723 through some digging I noticed we added these lines of code as a result of an Issue which was opened in the dbt_hubspot_source repo.

Our approach to rectify the test failure you are seeing would be to do a string_agg on the company for each deal. However, I am unsure if this would provide much value. I am interested to get your thoughts. Would the above solution be useful to you? Otherwise we would remove the join logic which is causing the issue.

Thanks!

[fivetran-joemarkiewicz]

It looks like ^ @fivetran-jamie was able to get a response from the original issue creator from the source package and it will make the most sense to remove this logic from the transform package.

Thanks again for opening this issue and I will update this logic to be removed in the next release. I will post here once I am able to cut a new release with these changes reflected.

[dwallace0723]

excellent - thanks @fivetran-joemarkiewicz!

[fivetran-jamie]

hey there, just reverted this code in version 0.3.1! The release should be up on dbt hub tomorrow or so 🙂

[dwallace0723]

Thanks @fivetran-jamie and @fivetran-joemarkiewicz! Really appreciate the quick responses here 🙂