BUG - date_add does not exist

[peter-dimov]

Are you a current Fivetran customer?

Peter, Humi :)

Describe the bug

Might just be me misusing but:

On model stripe__balance_transactions getting error:

Postgres error: function pg_catalog.date_add("unknown", integer, timestamp with time zone) does not exist

Weird because dbt_utils was properly installed

Installing dbt-labs/dbt_utils@0.7.4
  Installed from version 0.7.4
  Up to date!

Steps to reproduce

1. Add fivetran/stripe version 0.5.1 to packages.yml
2. dbt deps
3. dbt run --models stripe

Expected behavior

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: 'my_new_project'
version: '0.20.2'
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"

vars:
    stripe_schema: landing_stripe
    stripe_database: warehouse
    stripe_timezone: "America/Toronto"

# 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:
  my_new_project:
      # Applies to all files under models/example/
      example:
          materialized: view
  stripe:
    +schema: _stripe
  stripe_source:
    +schema: _stripe_source

Package Version

  - package: fivetran/stripe_source
    version: 0.4.4
  - package: fivetran/stripe
    version: 0.5.1

Warehouse

- [ ] BigQuery - [x] Redshift - [ ] Snowflake - [ ] Postgres - [ ] Databricks - [ ] Other (provide details below) **Additional context** **Screenshots**

Please indicate the level of urgency

Need to get solved by EOW 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.

[fivetran-joemarkiewicz]

HI @peter-dimov thanks for opening this Bug Report and I am sorry to see you are getting this date_add error. Would you be able to check for me what the datatype of the available_on field within the stripe source balance_transaction is?

I see the date_add macro is being used in the below line and I wonder if the datatype within the source is the cause of this strange error. Additionally, is this the first time you are using the package and seeing this error? Or did this error come out of the blue?

https://github.com/fivetran/dbt_stripe/blob/295b59dca6a4a498dcc8b4f631c9c916976f3e5a/models/stripe__balance_transactions.sql#L74

[peter-dimov]

@fivetran-joemarkiewicz thx for quick reply

Type seems to be alright (timestamp w tz) -- I'm using this directly on top of the fivetran source data

I forgot to add, but I think this may be a compilation error? Here's what I get when trying to compile the model:

Server error: Compilation Error in rpc request (from remote system)
Required var 'balance_transaction' not found in config:
Vars supplied to request = {
"stripe_database": "warehouse",
"stripe_schema": "landing_stripe",
"stripe_timezone": "America/Toronto"
}

> in rpc request (from remote system)
> called by rpc request (from remote system)

[fivetran-joemarkiewicz]

I forgot to add, but I think this may be a compilation error? Here's what I get when trying to compile the model:

Are you using dbt cloud? I have found that dbt cloud does not like compiling dbt package models directly from the dbt modules folder. This is why you are seeing this issue.

Would you be able to confirm in your Redshift instance what the datatype is?

[fivetran-joemarkiewicz]

In the meantime, I wonder if casting the available_on field in the stripe_source package to timestamp would solve this issue? As I have seen in the past the timestamptz data type does not work well with the date_add macro. Would you be able to try the below packages.yml dependency in place of the dbt hub versions and let me know if the compilation error still persists on dbt run?

packages:
  - git: https://github.com/fivetran/dbt_stripe.git
    revision: bugfix/timestamp-tz-fix
    warn-unpinned: false

[peter-dimov]

Type: Timestamp with timezone

Yes, using dbt cloud. That revision worked! Although, some similar errors seem to be coming up on the following models (customer_overview, daily_overview)

function convert_timezone("unknown", timestamp with time zone) does not exist

[fivetran-joemarkiewicz]

Ah ha we are getting closer! I just pushed the same update for the created_at field within balance_transactions. Would you be able to try again?

[peter-dimov]

No luck on that one still getting the same errors in customer_overview and daily_overview

Noticing some of the convert_timezone calls use 'created_at' while others use the macro 'dbt_utils.current_timestamp()'

Logs don't trace which one is causing the issue

[fivetran-joemarkiewicz]

Hmm I would have thought converting created_at in balance_transactions would do the trick?

Would you be able to run dbt deps again to make sure you are bringing in the latest commit I made on the branch?

[peter-dimov]

Yep already reran dbt deps and dbt run stripe_source

[fivetran-joemarkiewicz]

Hi @peter-dimov I attempted the casting locally and it seems to have fixed the issue. Would you be able to share the datatype of the created_at field? I looked through Redshift docs and it seems this issue really only persists if the datatype is varchar?

I doubt that created_at is varchar, but would be interested if that is truly the case in your warehouse.

[peter-dimov]

@fivetran-joemarkiewicz Found a fix!

Taking off

stripe_timezone: "America/Toronto"

from the vars does the trick. Seems everything is fine from a datatype perspective.

P.S. It also breaks if I put "America/New_York" like in your docs

[fivetran-joemarkiewicz]

Thanks for looking into that! It seems we will still want to push a fix to cast the datatype of available_on in the source package since the original issue will persist otherwise.

However, you mentioning the timezone variable is causing the failures makes me think a similar correction needs to be applied to the date_timezone macro. I will look further into that.

[fivetran-joemarkiewicz]

Hi @peter-dimov,

I hope all is well! I wanted to post back as I believe I have found a solution for this issue. When you have a free moment would you be able to test the working branch again? Essentially, I found that for a case in Redshift when we the timestamps are timestamptz we need to explicitly cast the data type to timestamp without time zone.

Let me know if this works!

[peter-dimov]

Just gave it a go on thebugfix/timestamp-tz-fix branch and same date_add error

20:08:36 | 1 of 9 START table model stripe__balance_transactions [RUN]

20:08:37 | 1 of 9 ERROR creating table model stripe__balance_transactions [ERROR in 0.97s]

Database Error in model stripe__balance_transactions (models/stripe__balance_transactions.sql)
  function pg_catalog.date_add("unknown", integer, 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.
  compiled SQL at target/run/stripe/models/stripe__balance_transactions.sql

[fivetran-jamie]

this is in v0.5.1 which should be up on dbt hub shortly!