most of our analytical queries refer to data of a whole day in local Central European time zone, i.e. CET or UTC+01:00 or CEST or UTC+02:00. However, since the field “derrived_tstamp” is reported in UTC, basically two partitions are queried for a day in local time zone.
So what do you guys think is the right way to create such a partitioning field “derrived_tstamp_local”?
I’d echo this approach - ideally have a data model (dbt or otherwise) that creates and partitions by your converted column rather than having to go back to the events table as much as possible to avoid rescanning those partitions.