[Feature] Setting multiple timezones for unioned multiple connectors

TomaszE commented 8 months ago

Is there an existing feature request for this?

[X] I have searched the existing issues

Describe the Feature

We are using multiple shopify connectors, each of them runs in different timezone - atm I am able to assign only one timezone per package - it would be great to be able to assign a particular timezone to particular connector ( ie Europe/London -> shopify_uk , Europe/Paris - > shopify_fr. Otherwise unioning multiple connections have sense only if they are in the same timezone.

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

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

Anything else?

No response

fivetran-jamie commented 8 months ago

hey there! this is convenient timing - we were just discussing making our unioning macro more flexible and potentially adding source-specific timezones (and maybe other metadata, like Shop currency?)

for the time being however, i wonder if you could dynamically set the timezone based on the source_relation for each shop. does something along these lines compile?

vars:
  shopify_timezone: "case when source_relation = 'shopify_uk' then 'Europe/London' when source_relation = 'shopify_fr' then 'Europe/Paris' else <other zone> end"

TomaszE commented 8 months ago

Hi Jamie, thank you for your answer, and sugguestion - i've tried that earlier , and this is unfortuantely failing, i am unable to run first stg_shopify models as i belive it's not able to manage case when statement in the timezone section:

20:43:29 Snowflake adapter: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 776 at position 62 unexpected 'shopify_uk'.
syntax error line 776 at position 72 unexpected '' then "Europe/London" when source_relation = ''.
syntax error line 777 at position 13 unexpected '('.
syntax error line 777 at position 25 unexpected 'as'.
syntax error line 777 at position 39 unexpected 'as'.
20:43:29 Timing info for model.shopify_source.stg_shopify__order (execute): 20:43:29.632116 => 20:43:29.786197
20:43:29 On model.shopify_source.stg_shopify__order: Close
20:43:30 Database Error in model stg_shopify__order (models/stg_shopify__order.sql)
  001003 (42000): SQL compilation error:
  syntax error line 776 at position 62 unexpected 'shopify_uk'.
  syntax error line 776 at position 72 unexpected '' then "Europe/London" when source_relation = ''.
  syntax error line 777 at position 13 unexpected '('.
  syntax error line 777 at position 25 unexpected 'as'.
  syntax error line 777 at position 39 unexpected 'as'.
  compiled Code at target/run/shopify_source/models/stg_shopify__order.sql
20:43:30 23 of 30 ERROR creating sql table model dbt_teitner_staging.stg_shopify__order . [ERROR in 1.08s]

fivetran-jamie commented 8 months ago

Ah yeah I see in the dbt_date.convert_timezone macro we use, the entire timezone argument gets wrapped in quotes, so it can't include the case statement. We'll likely have to create our own version of the macro that could work with dynamic timezones.

This is something we will potentially fold into the coming enhancements to our unioning functionality. If any other users would find this helpful please feel free to chime in and we can prioritize this further!

fivetran / dbt_shopify