[Bug] Model asana__task failed to build due to timezone-related function error.

shreveasaurus commented 2 years ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

The asana__task failed to build due to a function error.

Relevant error log or model output

2022-06-27T16:08:09.021458Z: 16:08:09  Postgres adapter: Postgres error: function pg_catalog.date_diff("unknown", timestamp with time zone, timestamp without time zone) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

2022-06-27T16:08:09.021614Z: 16:08:09  On model.asana.asana__task: ROLLBACK
2022-06-27T16:08:09.024195Z: 16:08:09  finished collecting timing info
2022-06-27T16:08:09.024348Z: 16:08:09  On model.asana.asana__task: Close
2022-06-27T16:08:09.024829Z: 16:08:09  Database Error in model asana__task (models/asana__task.sql)
  function pg_catalog.date_diff("unknown", timestamp with time zone, timestamp without time zone) does not exist
  HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
  compiled SQL at target/run/asana/models/asana__task.sql
2022-06-27T16:08:09.025035Z: 16:08:09  Sending event: {'category': 'dbt', 'action': 'run_model', 'label': 'bf6e271a-c2b1-4828-a0e6-718d5819cfa4', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fd198544d60>]}
2022-06-27T16:08:09.025364Z: 16:08:09  23 of 28 ERROR creating table model dev_asana.asana__task....................... [ERROR in 0.14s]
2022-06-27T16:08:09.025479Z: 16:08:09  Finished running node model.asana.asana__task
Database Error in model asana__task (models/asana__task.sql)
  function pg_catalog.date_diff("unknown", timestamp with time zone, timestamp without time zone) does not exist
  HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
  compiled SQL at target/run/asana/models/asana__task.sql

Expected behavior

The model asana__task should build successfully without error(s).

dbt Project configurations

version: '1.0.0'
config-version: 2

profile: 'default'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"
clean-targets:    
  - "target"
  - "dbt_packages"

models:
  my_new_project:
    example:
      materialized: view

vars:
    fivetran_log_using_transformations: false # this will disable all transformation + trigger_table logic
    fivetran_log_using_triggers: false # this will disable only trigger_table logic 
    fivetran_log_using_account_membership: false # this will disable only the account membership logic
    fivetran_log_using_destination_membership: false # this will disable only the destination membership logic
    fivetran_log_using_user: false # this will disable only the user logic

Package versions

packages:

package: fivetran/fivetran_log version: [">=0.6.0", "<0.7.0"]
package: fivetran/klaviyo_source version: [">=0.4.1", "<0.5.0"]
package: fivetran/klaviyo version: 0.4.1
package: fivetran/asana_source version: [">=0.5.0", "<0.6.0"]
package: fivetran/asana version: [">=0.5.0", "<0.6.0"]

What database are you using dbt with?

redshift

dbt Version

1.0.8

Additional Context

No response

Are you willing to open a PR to help address this issue?

[ ] Yes.
[X] Yes, but I will need assistance and will schedule time during our office hours for guidance
[ ] No.

fivetran-joemarkiewicz commented 2 years ago

Hi @shreveasaurus thanks so much for opening this issue. This is a type of issue we have actually seen quite a bit of within a few of our other packages. The primary reason is due to Fivetran syncing the timestamp fields for Redshift warehouses as timestamptz which does is not able to be leveraged within downstream date functions.

Within other packages we have resolved this by casting the timestamp fields in the staging models with {{ dbt_utils.type_timestamp() }} to safely convert to timestamp that may be used in those downstream date functions. See here for how we recently addressed this within our dbt_greenhouse_source package.

I noticed you mentioned you would be open to creating a PR! If that is still of interest to you, then all that would need to be done is to convert the staging models that use timestamp fields with the dbt_utils.type_timestamp() macro. Let me know if you would still be interested in opening a PR! Otherwise, my team can tackle this in our next sprint. Thanks!

shreveasaurus commented 2 years ago

Thanks @fivetran-joemarkiewicz for the thoughtful response and nudging me in the right direction! I tested casting the timestamps as you suggested locally and all models in the package are building as expected. I'll give opening a PR a go and let you know if I have any questions. Thanks again!

fivetran-sheringuyen commented 2 years ago

Hello @shreveasaurus ! I've just cut the release for your PR, you should see it within the next hour or so. Thank you again for your awesome work on this PR and looking forward to new PRs in the future! 🥳

fivetran / dbt_asana_source