[CT-3483] Support Semantic Layer timezone configs

[QMalcolm]

Housekeeping

• I am a maintainer of dbt-core

Short description

The Semantic Layer currently uses timezone-agnostic types for all date/time operations, but forcing people to coerce to UTC and then back-convert on render is cumbersome at best and impossible at worst. We have real-world examples of people running into date boundary issues where they have customer data stored in the customer-local timezone, and they want to compute daily customer-specific metrics with the boundaries set for that customer's local day (rather than UTC or whatever).

For situations like that it's natural to store the date/time information in local time with timezone annotations intact, as this makes common query types more natural and also allows for things like audits against local time values.

Currently , the only work-around is to normalize the time stamp in the warehouse (via the underlying dbt model) ahead of time, or else to use the expr field to do it on the fly when the semantic model is constructed. This is somewhat limiting, as it does not allow for re-use of the same measure against different time zones.

To better support timezone information we need to expand the definition of TIME type Dimensions. Specifically a timezone attribute should be added to the Dimension type_params

dimensions:
  - name: date_time
    type: time
    type_params:
      time_granularity: day
      timezone: pst # New

Acceptance criteria

People can specify a timezone property on dimensions.

Impact to Other Teams

• Semantic Layer - The semantic layer can't begin using this until we support it
• Cloud artifacts - This change will change the v12 manifest that goes out with 1.8

Will backports be required?

Not sure if required, but the SL team would prefer a backport to 1.7

Context

This issue should not be considered ready to work on until the work is done in dbt-semantic-interfaces, because the schema we need to implement isn't finalized until that point. Currently there does exist an issue for this work in dbt-semantic-interfaces.

[QMalcolm]

Should this be a location name instead of a timezone abbreviation? @Jstein77

[aranke]

List of timezones natively supported in Python: pytz.all_timezones_set

[dbeatty10]

I'd advocate for using tz database names (like America/Boise) rather than non-standardized abbreviations (like MST).

All of the following return names from the tz database:

• pytz.all_timezones
• pytz.all_timezones_set
• pytz.common_timezones
• pytz.common_timezones_set

The tz database is also known as:

• tzdata
• zoneinfo database
• IANA time zone database
• Olson database