stg_hubspot__engagement_email depreciated columns breaking build

[karlthumm]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

There's an error with depreciated fields containing a '-' are breaking the build. Example: property_hs_all-deprecated-127cf554-5d2f-4c78-b3fc-ac0986bce102

Relevant error log or model output

11:49:53  Database Error in model stg_hubspot__engagement_email (models/stg_hubspot__engagement_email.sql)
11:49:53    syntax error at or near "-"
11:49:53    compiled Code at target/run/hubspot_source/models/stg_hubspot__engagement_email.sql

Expected behavior

No error to be thrown and model built.

dbt Project configurations

hubspot_source:

If there is a need to enable a hubspot source, ensure its enabled at both

hubspot_database: "beehive"
hubspot_schema: "fivetran_hubspot"
# Disabled unused hubspot source models
# enable company model for testing temporarily
# hubspot_deal_company_enabled: false
hubspot_deal_contact_enabled: false
hubspot_deal_enabled: false
hubspot_email_event_forward_enabled: false
hubspot_email_event_print_enabled: false
hubspot_engagement_deal_enabled: false
hubspot_engagement_task_enabled: false
stg_hubspot__engagement_email: false

hubspot: hubspot_database: "beehive" hubspot_schema: "fivetran_hubspot"

Disabled unused hubspot models

# enable company model for testing temporarily
# hubspot_company_enabled: false
hubspot_deal_company_enabled: false
hubspot_deal_contact_enabled: false
hubspot_deal_enabled: false
hubspot_email_event_forward_enabled: false
hubspot_email_event_print_enabled: false
hubspot_engagement_deal_enabled: false
hubspot_engagement_task_enabled: false
stg_hubspot__engagement_email: false

email_metrics tracked for hubspot

email_metrics: ['bounces', 'clicks', 'deferrals', 'deliveries', 'drops', 'forwards', 'opens', 'prints', 'spam_reports', 'suppresses', 'processes']

Package versions

• package: fivetran/hubspot version: [">=0.12.0", "<0.13.0"]

What database are you using dbt with?

redshift

dbt Version

1.5.4

Additional Context

No response

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
• [X] No.

[fivetran-joemarkiewicz]

Hi @karlthumm thanks for opening this issue. I imagine we could probably leverage the dbt_utils.slugify() macro here to account for these inconsistencies. Out of curiosity, is the field named like that in HubSpot? Additionally, are there a number of other fields named like this or is this a one off?

[karlthumm]

There are three offending properties: property_hs_all-deprecated-127cf554-5d2f-4c78-b3fc-ac0986bce102 property_hs_all-deprecated-55e03e75-d0f7-4a2f-8235-7b97f1ca64ac property_hs_use-deprecated-580be759-71d3-4a5b-bbf3-201991967b29

[fivetran-joemarkiewicz]

Thanks @karlthumm, is this how these fields are named in the HubSpot UI?

[karlthumm]

Currently I don't have access to the HubSpot UI, however I can try to get access and see what I can find.

[karlthumm]

Are you asking this question for me to go in and disable these properties in the UI? Only reason why I'm asking is it would probably be best to exclude any future column with a '-' similar to the change that you made truncating the 'property_hsall' string.

[fivetran-joemarkiewicz]

@karlthumm I am asking more from an angle of understanding how these fields are shown in HubSpot vs the API. Is this something that was populated in the raw data by Fivetran and needs to be addressed in the connector, or is this something that HubSpot allows and we need to address in the package.

I am also a bit suspicious that the field names themselves have the word deprecated in them and curious if that was done by your team or HubSpot? I am not looking at this from the approach of recommending the fields need to be deselected in HubSpot, but more from understanding the issue as a whole so we can properly address the issue.

[karlthumm]

Got it. Confirming that no one on my team added these fields or made any changes to the model that's failing so it must be coming directly from HubSpot.

[fivetran-joemarkiewicz]

@karlthumm thanks again for working with me through this. After reviewing this further I believe a feasible approach would be to adjust the macro in the package to enclose the explicit selected fields to be in strings. This would ensure this type of error doesn't persist. So the update would look something like this:

, "{{ col.name }}" as {{ col.name[prefix|length:] }}

We will update and validate this prior to release in our upcoming sprint.

[karlthumm]

Thank you @fivetran-joemarkiewicz! Appreciate the support on this.

[fivetran-joemarkiewicz]

Reopening so we can track progress in the upcoming sprint.

[fivetran-catfritz]

@karlthumm To update you, I have picked up this task and aim to have something for you to try in the next week!

[fivetran-catfritz]

Hi @karlthumm, I have created a test branch that you can try that should resolve this issue. You can install it by using the below snippet in your packages.yml in place of your normal hubspot lines.

- git: https://github.com/fivetran/dbt_hubspot.git
  revision: release/v0.15.0
  warn-unpinned: false

If you are able to test it, please let me know your feedback!

[fivetran-catfritz]

This update has been released in the latest version, so closing this issue out!