BUG - hubspot_engagment_task CASTING error

[fivetran]

BUG - hubspot_engagment_task CASTING error

[fivetran-joemarkiewicz]

Hi @brettkobo I apologize for the delayed response. I have been able to dive into this a bit more and was able to push a commit to a working branch that I feel should resolve this error. You can give this a try by using the below config in your packages.yml.

packages:
  - git: https://github.com/fivetran/dbt_hubspot.git
    revision: feature/timestamp-fix
    warn-unpinned: false

I am still working with our product team to gain an understanding of why this field may have a different datatype in other warehouses, but this should work in the meantime. Let me know if you have any questions. Thanks!

[fivetran-joemarkiewicz]

Hi @brettkobo thanks so much for raising this issue. This issue is most likely related to this PR from our dbt_hubspot_source package. The legacy version of the hubspot connector synced the completion_date field within the engagement_task table as a string and our package corrected this to convert to a timestamp.

However, it seems that the new history mode for hubspot syncs this field correctly as a timestamp. Would you be able to confirm what the datatype of the completion_date field is within your hubspot source engagement_task table? If this truly is a timestamp then we can combine the efforts of the feature request #54 with this bug fix as this seems it is a result of the history mode for hubspot syncing a different datatype for this field.

[brettkobo]

Looks like it is coming out of Fivetran as a timestamp_tz(9). I am also able to query the raw table from Snowflake with no problem. So I assume it is working correctly from there.

[fivetran-joemarkiewicz]

@brettkobo thanks so much for providing the screenshot, this is extremely helpful! It looks like the datatype for the completion_date has changed in the newer version of the hubspot connector.

Previously this field was synced as a string and our package correctly changed the datatype to a timestamp. However, now it is being synced as a timestamp and understandably snowflake does not want to cast a timestamp to a timestamp.

I will investigate this some more and will let you know here once we have a working branch for you to try out!

[fivetran-joemarkiewicz]

Hi @brettkobo just wanted to check back in if this update ended up working for you?

[fivetran-joemarkiewicz]

@brettkobo the fix for this has been applied to the latest release of the dbt_hubspot_source package! You should be able to re-run your project models at the top of the hour to reflect the changes within dbt_hubspot_sourcev0.4.3 once they are live on the dbt hub.

Please feel free to open a new issue if you have any questions or requests. Thanks!