BUG - Issues with timestamptz data type

[juanbriones]

Are you a current Fivetran customer?
Juan Francisco Briones, Data Engineer, Hopin.

Describe the bug
Apparently some fields from the original Zendesk data are in timestamp with timezone (timestamptz) and that makes the dbt_utils.datediff and dbt_utils.dateadd functions break.

Steps to reproduce
Just run the dbt_zendesk package with date fields in timestamptz.

Expected behavior
You will get the following error in multiple models:

  function pg_catalog.date_diff("unknown", timestamp with time zone, timestamp with time zone) does not exist
  HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

Project variables configuration

name: 'zendesk_streamyard'
version: '1.0'
config-version: 2
vars:
  using_schedules:            False         #Disable if you are not using schedules
  using_domain_names:         False         #Disable if you are not using domain names
  using_user_tags:            False         #Disable if you are not using user tags
  using_ticket_form_history:  False         #Disable if you are not using ticket form history
  using_organization_tags:    False         #Disable if you are not using organization tags
  zendesk_source:
    zendesk_database: data
    zendesk_schema: zendesk_streamyard

# This setting configures which "profile" dbt uses for this project.
profile: 'hopin-redshift-staging'

# 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

# We tell dbt to build all the models in the zendesk_streamyard/ directory
# as tables. These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  zendesk_streamyard:
    zendesk:
      +schema: zendesk_streamyard_modeled
      +materialized: table
    zendesk_source:
      +schema: zendesk_streamyard_modeled
      +materialized: table

Package Version

packages:
  - package: fivetran/zendesk
    version: [">=0.7.0", "<0.8.0"]

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context
I've already had a conversation with Joseph Markiewicz, Renee Li and Sheri Nguyen from Fivetran. They are aware of the issue and we solved it together casting the fields to timestamp without time zone.

Please indicate the level of urgency
Medium priority. We need this information to create a dashboard for Customer Support team.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• 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.
  Joseph from Fivetran can help us as well.
• 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.

[fivetran-joemarkiewicz]

@juanbriones thanks so much for opening this issue and for chatting with myself and the team to help identify the solution to this Redshift bug! 🎉 💯

I was able to apply a bugfix in a working branch. Would you be able to swap your zendesk package dependency in your packages.yml for the below dependency:

packages:
  - git: https://github.com/fivetran/dbt_zendesk.git
    revision: bugfix/redshift-timestamps
    warn-unpinned: false

Let me know if this then works. If it does, we can move forward with merging this into the next release!

[juanbriones]

Hi Joe, how are you? I will test it and let you know if I have any issues. Thank you! Best regards, El lun, 13 dic 2021 a las 13:21, Joe Markiewicz ***@***.***>) escribió:

…

@juanbriones <https://github.com/juanbriones> thanks so much for opening this issue and for chatting with myself and the team to help identify the solution to this Redshift bug! 🎉 💯 I was able to apply a bugfix in a working branch. Would you be able to swap your zendesk package dependency in your packages.yml for the below dependency: packages: - git: https://github.com/fivetran/dbt_zendesk.git revision: bugfix/redshift-timestamps warn-unpinned: false Let me know if this then works. If it does, we can move forward with merging this into the next release! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#55 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA7Q2YEQ5FFLZVNXPKPNKUTUQYMRBANCNFSM5JPS6Y2Q> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

-- *Juan Francisco Briones*

[fivetran-joemarkiewicz]

Hi @juanbriones this fix is now live in the current version of dbt_zendesk! Feel free to use the latest version of the package and you should see the issue has been resolved.

I will be closing this issue as the current version of the package addressed the issue. Thank you again for all your help in assessing what this issue was, and how to solve it. We are extremely grateful for your contribution to this package and the community 😄

Feel free to open another issue if you have any other questions!