[Bug] livemode_predicate generates invalid SQL on Redshift: cannot cast type character varying to boolean

[sbailliez]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

livemode generates invalid sql and it fails with cannot cast type character varying to boolean

the predicates generated is

where cast(livemode as BOOLEAN ) = True

whereas redshift would unfortunately need something like

where decode(livemode, 'true', true, 'false', false) = true

Relevant error log or model output

03:57:16  Database Error in model stg_stripe__transfer (models/stg_stripe__transfer.sql)
03:57:16    cannot cast type character varying to boolean
03:57:16    compiled Code at target/run/stripe_source/models/stg_stripe__transfer.sql

Expected behavior

Should generate valid SQL

dbt Project configurations

Not relevant for this model

Package versions

• package: fivetran/stripe version: 0.12.0

What database are you using dbt with?

redshift

dbt Version

1.5.4

Additional Context

No response

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

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

[fivetran-reneeli]

Hi @sbailliez, we've noted that the livemode should be syncing as boolean instead of character varying in the Fivetran connector, which should then help resolve this. Our team's made been aware of this bug.

To also substantiate this, I would imagine the other models that contain livemode are running successfully, given the field's synced correctly as boolean in those respective objects-- can you confirm all other models are ran successfully (stg_stripe__invoice, stg_stripe__credit_note_line_item, stg_stripe__payout, etc)?

[sbailliez]

@fivetran-reneeli I don't use invoices or credit note but no problem for payout. If the data type can be changed in the fivetran etl that would indeed be a lot better! I'm sticking to stripe 0.9.0 for now.

Are you planning a release to allow override?

(also apologies but noticed I made a mistake and should have opened that in stripe_source)

[fivetran-reneeli]

Thanks @sbailliez! And yes we will aim to work on this in our upcoming sprint that starts mid next week. Just a note we will be at a company offsite so may be slow the first few days to respond.

However, if you are interested in contributing a PR we would be happy to review the PR sooner. We would just plan to modify the livemode_predicate macro in the source package to use the adapter dispatch (like we see in our other package specific macros ).

[fivetran-catfritz]

Hi @sbailliez. To update you, our engineering team has just released a connector update (release notes) to change the datatype of livemode to boolean, which should fix the issue. If you drop the TRANSFER table and re-sync it, that should resolve the error.

Because of this I'm going to mark this ticket as something we won't fix, but please let us know whether this resolves your error!

[fivetran-catfritz]

Closing this issue out since the fix was made at the connector, however if anyone runs into this issue again, please feel free to comment in this thread.