[Bug] Removal of deleted record filter resulting in uniqueness test failures

[kcraig-ats]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Updating to version 0.8.0 of the HubSpot package resulted in uniqueness test failures. I read in dbt_hubspot_source #96 that the methods where not coalesce(is_deleted, false) and where not coalesce(_fivetran_deleted, false) were removed from a number of models to allow for greater flexibility. In testing this does appear to be the cause of the failures. The records that are failing have different deleted flag values. Each set of duplicates has one false flag and the remaining flags are true. Note, I have contact merge auditing enabled and tested it to rule it out as the cause. In my case, this was specific to email events but seems like it might apply to other models where the filter was removed.

Relevant error log or model output

No response

Expected behavior

Uniqueness tests should pass.

dbt Project configurations

n/a

Package versions

0.8.0

What database are you using dbt with?

redshift

dbt Version

1.3

Additional Context

Discussion with Jamie in dbt slack

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[bdtoole]

Adding that we're experiencing the same issue on Snowflake, also with email events (models/marketing/email_events/email_events.yml) on the is_contact_deleted column

[fivetran-joemarkiewicz]

Thanks for the note @bdtoole!

Our team is currently working on an update to the downstream tests to account for this change. I will post back once we have an update.

[fivetran-jamie]

@bdtoole @kcraig-ats just to confirm, are y'all only seeing the failures in the models/marketing/email_events models? or anywhere else as well?

[kcraig-ats]

@fivetran-jamie confirming it's just email_events for me, but I also don't have hubspot_deal_enabled enabled and tickets are not in use.

[fivetran-joemarkiewicz]

Hi @kcraig-ats thanks for sharing the additional details here on the tests you are seeing experiencing the failures.

I have been able to open a PR that I believe should address the issue you are experiencing. Would you be able to test the following version of the package (you can replace the normal package dependency in your packages.yml with the following):

packages:
  - git: https://github.com/fivetran/dbt_hubspot.git
    revision: bugfix/uniqueness-test-adjustment
    warn-unpinned: false 

Let me know if using this version of the package addresses the original issue you started seeing after upgrading. Thanks!

[kcraig-ats]

@fivetran-joemarkiewicz Yep, that fixed the issue! TY!!

[bdtoole]

Confirming that the issue seems fixed for us, too!

[fivetran-joemarkiewicz]

Thanks all for your help in raising this issue to our team. We have since been able to address the issue in the latest release of the dbt_hubspot package (v0.8.2). Feel free to upgrade to see the test failures resolved.

As a result, I will close out this issue. Please feel free to reopen if the issue persists.