cal-itp/data-infra

Align `end_of_month_date` calculation with Metabase localization

Closed this issue · 0 comments

As described in this Slack thread, the datetime values for fct_payments_settlements and fct_payments_aggregations are in UTC in the warehouse but Metabase is performing automatic localization to convert dates to Pacific Time. This can be helpful for analysis, but is proving the calculation for end_of_month_date to be incorrect.

We need to correct the logic used in end_of_month_date to calculate based on Pacific Time, and rename end_of_month_date_pacific. We can also preserve the original calculation and rename as end_of_month_date_utc.

After, we will need to make sure to fix any metabase questions using this field.

Alternative option considered: remove Metabase automatic localization