Update time spine configs for sub-daily granularity & custom calendar

[courtneyholcomb]

Resolves #280

Description

As we work to support sub-daily granularities, users will be able to set up multiple time spines at different granularities, and this updates the YAML spec to support what we'll need for that. It also lays the groundwork for adding custom time spines (for the custom calendar feature). The configs have been designed to look like this:

semantic_layer_project_configs:
     time_spines:
         - name: daily_time_spine
             model: ref('daily_time_spine') #a model in your dbt project
             base_column: base_date #exprs don't work here. This is just a column
             base_granularity: day #takes day, week, month, quarter, year
         - name: hourly_time_spine
             model: ref('hourly_time_spine') #a model in your dbt project
             base_column: base_date #exprs don't work here. This is just a column
             base_granularity: hour #takes day, week, month, quarter, year
         - name: custom_time_spine
             model: ref('custom_time_spine') #a model in your dbt project
             base_column: base_date #exprs don't work here. This is just a column
             base_granularity: hour #takes day, week, month, quarter, year
             custom_granularities:
                - name: fortnight # this is the column
                    offset_window: 14 days
                - name: fiscal_quarter
                    expr: quarter
                    offset_window: 90 days

This PR implements most of those fields, with the exception of the custom_granularities field, for which the design is not finalized (will used for custom calendar).

Note on the changing of field names: Currently, these time spine fields are not configured in YAML (i.e., users don't set them), they are set in core instead. That means we should be able to change the names here as long as we also update them in core and anywhere they are parsed from the manifest and handled downstream, such as in MF or MFS. Similarly, we want project_configs to show up as semantic_layer_project_configs for users, but that change can be handled in core.

Checklist

• [x] I have read the contributing guide and understand what's expected of me
• [x] I have signed the CLA
• [x] This PR includes tests, or tests are not required/relevant for this PR
• [x] I have run changie new to create a changelog entry

[Jstein77]

@graciegoheen, I would love to get your feedback on this spec change. We're exposing a new YAML key, semantic_layer_project_configs. This is already an internal key we use, but we're now allowing users to set project defaults for their semantic layer. Initially, the only thing they can set up are various time spine models that we need for sub-daily granularity and custom time grains. There are other project defaults that we might make configurable in the future, for example, default timezone information.

I'm picturing users defining their semantic_layer_project_configs in the dbt_project.yml. An example configuration would look like:

name: "jaffle_shop"
version: "1.0.0"
config-version: 2
dbt-cloud: 
    project-id: 263759
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["jaffle-data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
....
semantic_layer_project_configs:
     time_spines:
         - name: daily_time_spine
             model: ref('daily_time_spine') #a model in your dbt project
             base_column: base_date #exprs don't work here. This is just a column
             base_granularity: day #takes day, week, month, quarter, year
         - name: hourly_time_spine
             model: ref('hourly_time_spine') #a model in your dbt project
             base_column: base_date #exprs don't work here. This is just a column
             base_granularity: hour #takes day, week, month, quarter, year
         - name: custom_time_spine
             model: ref('custom_time_spine') #a model in your dbt project
             base_column: base_date #exprs don't work here. This is just a column
             base_granularity: hour #takes day, week, month, quarter, year
             custom_granularities:
                - name: fortnight # this is the column
                    offset_window: 14 days
                - name: fiscal_quarter
                    expr: quarter
                    offset_window: 90 days

[graciegoheen]

@Jstein77 When you say "project defaults for their semantic layer" do you mean:

• project defaults, but then you could set overrides for specific metrics, etc. (sorta like model configs?)
• project defaults, but then you could override them at runtime (sorta like project variables?)
• project defaults that apply to your entire project with no additional override ability (sorta like behavior change flags?)

[Jstein77]

Great question! I really like thinking along the dimensions of resource level overrides, query time level overrides and no overrides.

For time spines this is more akin to "project defaults that apply to your entire project with no additional override ability". Once you set up your time spine configurations they are available for you entire project and can't be updated at query time.

[graciegoheen]

Got it! What about dbt-semantic-layer: then (instead of semantic_layer_project_configs:)? IMO "project" and "configs" is redundant, and I like having the dbt prefix (similar to the dbt-cloud: config) so folks don't get confused with the semantic-models: config.

Couple of other questions:

• why is base_column and base_granularity prefixed with base_? To distinguish between custom?
• #exprs don't work here. This is just a column why?
• base_granularity: hour #takes day, week, month, quarter, year does hour work or no?

Notes:

• we have a few other types of configs that deal with naming a column + granularity (example here)

[Jstein77]

Answering your questions in line:

why is base_column and basegranularity prefixed with base? To distinguish between custom?

Two reasons:

• The base column should be the finest grain contained in the time spine table. There will likely be multiple time columns in their model, and we wanted to distinguish which one you should use here. We settled on base column and base granularity.
• To distinguish between custom granularities.

exprs don't work here. This is just a column why?

• We don't want to take arbitration SQL for the base_column. For time spine tables we prefer you push all the logic to the transformation layer and just expose the final time spine model to us. That's why we decided not to allow exprs here.

base_granularity: hour #takes day, week, month, quarter, year does hour work or no?

Yes hour should work here! Sorry we designed this in parallel with sub daily granularity so forgot to update this.

[courtneyholcomb]

Closing this PR in favor of creating a new one. New plan to avoid breaking change:

• Add new field time_spines that uses new PydanticTimeSpine object
• Leave old field & object in place to avoid breaking change
• Consume both in core / MF
• Eventually plan to deprecate old field