[Feature] Update spec to include `custom_granularities`

Jstein77 commented 3 months ago

Is this your first time submitting a feature request?

[X] I have read the expectations for open source contributors
[X] I have searched the existing issues, and I could not find an existing issue for this feature
[X] I am requesting a straightforward change to existing dbt-semantic-interfaces functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Its currently possible to add a time_spine configuration to a model configuration in order to make this model available to Metricflow to use in time based joins.

We want to extend this spec to allow users to define custom granularities that a present in the time spine model, like fiscal quarter or 4-5-4 retail months. These custom granularities will then be accesible for Metrciflows built in time operations, like offsets, windows and dynamic granularity calculations.

The proposed spec for adding a custom granularity is below:

models:
     - name: daily_time_spine
       description: "a daily time spine with custom granularities"
       columns:
         - name: date_day
           granularity: day
             - name: quarter
         - name: fortnight
           description: "the fortnight marker the day belongs to"
         - name: retail_month
           description: "the retail month marker the day belongs to"
       time_spine:
              standard_granularity_column: date_day
          custom_granularity_columns: #[NEW] A list of columns strucs to use as custom granularities
              - name: fortnight #[NEW] References a custom granularity column defined in the models `columns`
              - name: retail_month
              - name: fiscal_quarter 
                column_name: quarter #[NEW] If the column name is diffrent then the granularity name you want to expose in metricflow, we allow aliasing by referencing the column name here, and the name to expose in metricflow via name.

Describe alternatives you've considered

Use a semantic model.

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

ChenyuLInx commented 3 months ago

I have two questions:

for models -> columns -> granularity -> name(value is quarter in this example), why need it when there's a name at the granularity level?
is the indentation for time_spine config correct? Feels like it should be a top level thing?

@dbeatty10 Wondering if you have more context on this?

courtneyholcomb commented 3 months ago

I have two questions:

for models -> columns -> granularity -> name(value is quarter in this example), why need it when there's a name at the granularity level?

is the indentation for time_spine config correct? Feels like it should be a top level thing?

@ChenyuLInx

There's nothing nested under granularity, it just takes a string!
You're right, that was incorrect. I updated the example above so it should be correct now.

Also for context, we've already gotten full approval on everything (and it's all released to versionless) except the custom_granularity_columns key and anything nested under that.

Jstein77 commented 3 months ago

@graciegoheen brining the conversation here from slack:

column_name is similar to identifier https://docs.getdbt.com/reference/resource-properties/identifier

identifier refers to the table ID. In the time_spine config I think identifier maps more closely to standard_granularity_column which is the column we will join to to do custom granularity lookups.

column_name in the proposed spec is to provide an option to alias the custom granularity if needed.

graciegoheen commented 3 months ago

Got it - so then really these are custom_granularities where you specify the name of the granularity and a column_name if the column is named something different from the granularity name?

In that case, should the config be called custom_granularities instead of custom_granularity_columns?

       time_spine:
              standard_granularity_column: date_day
          custom_granularities: 
                - name: fiscal_quarter 
                  column_name: quarter

Or maybe it's just removing the redundant "column" like:

       time_spine:
              standard_granularity_column: date_day
          custom_granularity_columns: 
                - name: fiscal_quarter 
                  identifier: quarter

Jstein77 commented 3 months ago

Correct! I like the first option. Removing columns and specifying the column_name if needed for each custom granularity.

       time_spine:
              standard_granularity_column: date_day
          custom_granularities: 
                - name: fiscal_quarter 
                  column_name: quarter

dbt-labs / dbt-semantic-interfaces