BUG - Issues with timestamptz data type

juanbriones commented 2 years ago

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 - [x] 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.
[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. 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 commented 2 years ago

@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 commented 2 years ago

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 https://github.com/fivetran/dbt_zendesk/issues/55#issuecomment-992642451, 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 commented 2 years ago

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!

fivetran / dbt_zendesk

BUG - Issues with timestamptz data type #55