BUG - hubspot_engagment_task CASTING error

[brettkobo]

Are you a current Fivetran customer? Hello name name is Brett Kobold and I am currently a customer of Fivetran under the account Retina.AI

Describe the bug When I run the models, I get an error for the hubspot_engagment_table. I have pasted the error below. I am not sure what coloum is failing to CAST and I believe I do not have custom properies on engagments.

Database Error in model stg_hubspot__engagement_task (models/stg_hubspot__engagement_task.sql)
  001065 (22023): SQL compilation error:
  Function TRY_CAST cannot be used with arguments of types TIMESTAMP_TZ(9) and TIMESTAMP_NTZ(9)
  compiled SQL at target/run/hubspot_source/models/stg_hubspot__engagement_task.sql

Steps to reproduce Run FiveTran for hubspot tables, excluding any history tables to redunce number of calls. Install the hubspot DBT package. dbt_run on DBT cloud. Get error that can't TRY_CAST timestamps. Go check Snowflake to see if tables were created.

Expected behavior I expected the engagement tasks table to be created, currently I do not see that in my Snowflake instance.

Project variables configuration

# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'retina_rev_ana'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'default'

# These configurations specify where dbt should look for different types of files.
# The `source-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-paths: ["data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
    - "target"
    - "dbt_modules"

# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/ directory
# as tables. These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  retina_rev_ana:
      # Applies to all files under models/example/
      example:
          materialized: view

vars:
  hubspot_source:
    hubspot_database: PC_FIVETRAN_DB
    hubspot_schema: HUBSPOT
    hubspot__pass_through_all_columns: true
    hubspot__email_event_forward_enabled: false
    hubspot__email_event_print_enabled: false

Package Version

packages:
  - package: fivetran/hubspot
    version: 0.4.1

Warehouse

- [ ] BigQuery - [ ] Redshift - [ X] Snowflake - [ ] Postgres - [ ] Databricks - [ ] Other (provide details below) **Additional context** **Screenshots**  **Please indicate the level of urgency** This isn't critical but I would like to have all the tables built as I will be doing reporting on SDRs this week and their different engagements within Hubspot **Are you interested in contributing to this package?**

• [ ] Yes, I can do this and open a PR for your review.
• [ X] Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• [ ] No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

I just started working with DBT but would be happy to help in the future on development. I know SQL.

[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 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 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!