[Feature] Create a standard to support measures with Slowly Changing Dimension tables

[alex-fliegel]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing metricflow functionality, rather than a Big Idea better suited to a discussion

Describe the feature

The current Metric Flow configuration is built to support standard dimensions with actual primary keys, but has issues properly supporting Slowly Changing Dimension tables. (See this dbt documentation on SCD tables today)[https://docs.getdbt.com/docs/build/dimensions#scd-type-ii]. Ideally, establishing a standard will allow users to implement measures and metrics alongside SCD tables, adhering to best-in-practice performance layouts for certain tables.

Describe alternatives you've considered

I have tried implementing the semantic layer including SCD tables using the standard implementation without measures. I have tried implementing semantic layer while filtering scd tables to "latest record" which allowed measures (scd config no longer needed) but did now allow to show records over time.

Who will this benefit?

Any user of the semantic layer who leverages SCD Type II tables to manage the performance of the Kimball model, preparing optimized data for AI solutions

Are you interested in contributing this feature?

Sure

Anything else?

No response

[linear]

SL-4270

[alex-fliegel]

A few other thoughts:
Should metricflow have an opinion on time window standards? i.e. date >= effective_at, < effective_thru or date > effective_at and date <= effective_thru etc.)

There should be special time considerations for time granularity on "window" timestamps (effective_at/thru)

• Should be alignment of time granularity between fct table/time spine and SCD tables
• block defaults:
  agg_time_dimension: ****** should not be required as no data is being "aggregated" - it is being selected off a time window.
• Support past dbt snapshots are required. While snapshots are great for T=0 SCD performance, there are often instances where SCD tables must be built off of history.
• Remove requirement for natural key setting. Enable the ability to set up a primary key where SCD logic based on effective_at/thru columns are handled for end user.

Ideally, a development preference would be to include all three fields in the entities block, with relative flags for effective_at/thru. Current layout requires me to add effective_at/thru as dimensions, which is misleading for the dev experience.