то есть таблица "/datalake/gold/fct_sales" должна быть партиционирована по дате в локальной таймзоне пользователей?

спорно

в ХД все лучше делать в UTC

если у меня не приехало еще обновление справочника пользователей?

в локаль переводить только в каких-то пользовательских представлениях

вот в этом и вопрос: как спроектировать структуру DIM_DATE чтобы было удобно в BI подтягивать отображение в нужной таймзоне

create table DIM_DATE (
date_utc_pk int,
date_est_pk int,
...
)

???

BI сами должны перевести в локаль, а UTC или в таблицах с настройками или параметром

тут ведь может много вопросов возникнуть, чтобы те же пользователи увидели "одинаковые" отчеты находясь в разных часовых поясах (или неодинаковые)

что больше про аналитику, чем ХД

если они добавляться будут?

кто "они"? point of view?

часовые пояса пользователей

так я о том и спрашиваю )))
как спроектировать

переделывать таблицы в ХД из-за добавления поясов - так себе идея )
лучше такие волатильные моменты ближе к клиенту держать и адаптировать для пользователя в самый последний момент, а ХД - быть максимально универсальным

ну будет в крайнем случае дергаться дополнительная (соседняя) партиция. это прям так страшно, что надо огород городить?)

если фильтры от клиентов поступают в локальном времени - переведут в UTC для поиска в gold-таблицах

Согласен, всегда, всегда храните данные в utc.

всем привет! спасибо за помощь с таймзонами!
сделал в итоге так:

1) DIM_TIMEZONE
case class DimTimeZoneRow(
   tz_key: Int,
   tz_code: String,
   tz_name: String,
   tz_utc_shift: String
)
val dimTimeZone: Seq[DimTimeZoneRow] = List(
 DimTimeZoneRow(1, "UTC", "Coordinated Universal Time", "+0"),
 DimTimeZoneRow(2, "CET", "Central European Time", "+1"),
 DimTimeZoneRow(3, "Europe/Moscow", "Moscow Standard Time", "+3"),
...
)
val dimTimeZoneDF = dimTimeZone.toDF

2) DIM_DATETIMEZONE
case class DimDateTimeZoneRec(
   date_key: Int,
   hour_key: Int,
   tz_fkey: Int,
   datetime_iso: String,
   date_iso: String,
   year: Int,
   month_name: String,
   month_num_of_year: Int,
   month_num_with_year: Int,
   month_name_with_year: String,
   week_num_of_year: Int,
   week_num_with_year: String,
   day_num_of_year: Int,
   day_num_of_month: Int,
   day_num_of_week: Int,
   day_name_of_week: String,
   is_weekend: Boolean,
   hour_hum_of_day: Int,
   time_of_day: String
)
val dimDateTimeZone: IndexedSeq[DimDateTimeZoneRec] = dateHoursUTC.flatMap(dateHourUTC => {
 dimTimeZone.map(tz => {
   val dateHour             = dateHourUTC.withZoneSameInstant(ZoneId.of(tz.tz_code))
   val dateKeyUTC        = dayKeyFormatter.format(dateHourUTC).toInt
   val hourKeyUTC        = hourKeyFormatter.format(dateHourUTC).toInt
...
   DimDateTimeZoneRec(
     dateKeyUTC,
     hourKeyUTC,
     timezoneFKey,
...
   )
 })
})

val dimDateTimeZoneDF = dimDateTimeZone.toDFdimTimeZone.map(tz => {
   val dateHour             = dateHourUTC.withZoneSameInstant(ZoneId.of(tz.tz_code))
   val dateKeyUTC        = dayKeyFormatter.format(dateHourUTC).toInt
   val hourKeyUTC        = hourKeyFormatter.format(dateHourUTC).toInt
...
   DimDateTimeZoneRec(
     dateKeyUTC,
     hourKeyUTC,
     timezoneFKey,
...
   )
 })
})

val dimDateTimeZoneDF = dimDateTimeZone.toDF

3) в фактовых табличках использую партиционирование по "date_utc", "hour_utc"
они же и являются внешними ключами на DIM_DATETIMEZONE,
Dynamic Partition Pruning в Spark 3+ работает!

возник еще вопрос по моделированию и логике формирования DataLakeHouse:
- поскольку мне важны изменения в атрибутах некоторых измерений (через SCD2), использую суррогатные ключи для измерений
- есть несколько гигантских измерений с сотнями миллионов строк, которые не влезают в память Spark-задания (нечего и говорить про broadcast)
- как можно оптимизировать lookup по гигантскому измерению для получения суррогатных ключей в момент инкрементальных заполнений фактовой таблицы???

делать slim-версию измерений с "primary_key, natural_key, effective_from, effective_to" ?