New dbt Semantic Layer Spec Proposal

[nhandel]

Summary & Background

It's finally here! Community members have been asking us about the future of the semantic layer and what it will look like since we announced the acquisition two months ago. Since then, we've integrated teams, had an offsite to share context and, and spent a few weeks polishing up what we'll be working on for the foreseeable future. In that vein, this discussion will contain the proposed specs for the new dbt & MetricFlow semantic objects. Before we move any further, these are only proposed specs. They are not finalized - the broad strokes will most likely remain the same, but we may still change them based on community feedback.

Proposal

These specs will not live in dbt-core or MetricFlow - both libraries will depend on these objects, so we plan to create a third repository, dbt-semantic-interfaces containing these protocols. Both libraries will import that repo.

New Objects

• semantic_models

  • Reason: The Semantic Model will be the new foundational building block upon which the Semantic Layer will rest. It contains many object types (such as entities, measures, & dimensions ) that allow MetricFlow to construct the queries for metric definitions.

  • Location: third-repo

  • Description:

    • Each semantic model will be 1:1 with a dbt sql/python model
    • Each semantic model will contain (at most) 1 primary or natural entity
    • Each semantic model will contain zero, one, or many foreign or unique entities used to connect to other entities

  • See properties below

    YML Properties

    semantic_models:
      - name: the_name_of_the_semantic_model
        description: same as always
        model: ref('some_model')
        default:
            agg_time_dimension: dimension_name
    
        entities:
           - see more information in entities
        measures:
           - see more information in measures section
        dimensions:
           - see more information in dimensions section
    
        meta: same as always
        tags: same as always
    

    Property Names

    Property Name	Location	Description
    name	third-repo	Name of the semantic model
    description	third-repo	Description of the semantic model. Needs to support docs
    default	third-repo	Defaults for this model
    entities	third-repo	Entities for the semantic model
    dimensions	third-repo	Dimensions for the semantic model
    measures	third-repo	Measures for the semantic model
    model	dbt-core	Name of the logical model the semantic model belongs to
    meta	dbt-core	Metadata associated with the semantic model
    tags	dbt-core	Tags associated with the semantic model
    config	dbt-core	Configuration for the semantic model (only exists for enabled!)

---

• entities

  • Description: Entities are the join key columns in your semantic model that can be used to join to other semantic models. For example, the user_id entity defined in a semantic model could be a primary type which means that each row in that object represents the entity of a user. Or it could be foreign, which means that it points to an entity of a user in another semantic model.

  • Callout: This is not a new node type as was previously proposed in but a property that lives inside the semantic_model. It corresponds to a key or identifier inside the logical model and represents a single instance of the entity in question. This is the basis of how MetricFlow handles joins.

  • See properties below

    YML Properties

            entities:
               - name: the name # think transaction
                 type: primary # or natural or foreign or unique
                 description: a description of the field or role the entity takes in this table
                 role: used in cases where there are two relationships to the same entity.
                 # ^continued - think buyer/seller id relationship to a user entity
                 entities: link to list of entities if a composite
                 expr: the field that denotes that entity (transaction_id)
                 meta: same as always
                 tags: same as always
    

    Property Names

    Property Name	Location	Description
    name	third-repo	Name of the entity
    type	third-repo	Type of the entity
    description	third-repo	Description of the entity
    role	third-repo	Role of the entity
    entities	third-repo	List of composite sub-entities
    expr	third-repo	Expression of the entity
    meta	dbt-core	Metadata associated with the entity
    tags	dbt-core	Tags associated with the entity

---

• measures

  • Description: A measure is an aggregation that can result in either your final metric, and/or serve as the foundational building blocks for a metric. For those familiar with dbt_metrics, this takes the place of some of the information stored in expression and calculation_method.

  • Callout : The aggregation types that were previously defined in the calculation_method are now defined in the agg property of the measure. The supported aggregation types will be defined inside MetricFlow. This means that the previously supported behavior of overwriting macros to use custom aggregation types will no longer be supported.

  • See properties below

    YML Properties

            measures:
               - name: the name # think transaction_total. If `expr` not present then this is the expected name of the column
                 description: same as always
                 agg: the aggregation type.  #think average, sum, max, min, etc.
                 expr: the field # think transaction_total or some other name you might want to alias
                 create_metric: visible/hidden/no  # defaults to hidden
                 agg_params: specific aggregation properties such as a percentile
                 agg_time_dimension: the time field
                 non_additive_dimension: configs used if needing non-additive dimensions
                 meta: same as always
                 tags: same as always
    

    Property Names

    Property Name	Location	Description
    name	third-repo	Name of the measure
    agg	third-repo	Aggregation type
    description	third-repo	Description of the measure
    expr	third-repo	Expression of the measure
    create_metric	third-repo	Creates a simple or hidden metric from the measure
    agg_params	third-repo	Aggregation parameters
    non_additive_dimension	third-repo	Non-additive dimension parameters
    agg_time_dimension	third-repo	Time dimension to aggregate the measure by
    meta	dbt-core	Metadata associated with the measure
    tags	dbt-core	Tags associated with the measure

---

• dimensions

  • Description: A dimension is a categorical or time field that can be used to group or filter metric(s). Similar to the dbt metric dimension property except, it now exists as a reusable component of the semantic model with additional metadata.

  • See properties below

    YML Properties

            dimensions:
               - name: the name
                 type: categorical or time
                 type_params: specific type params such as if the time is primary or used as a partition
                 description: same as always
                 expr: the column name or expression
                 meta: same as always
                 tags: same as always
    

    Property Names

    Property Name	Location	Description
    name	third-repo	Name of the dimension
    type	third-repo	Type of the dimension
    type_params	third-repo	A dictionary that takes in specific config properties based on the type
    description	third-repo	Description of the dimension
    expr	third-repo	Expression of the dimension
    meta	dbt-core	Metadata associated with the dimension
    tags	dbt-core	Tags associated with the dimension

---

Altered Objects

• metrics

  • Description: A metric! Same as before but with new properties.

  • Callout: This is a breaking change to the metric spec. Metrics will also only exist on top of the new Semantic Models object instead of sql/python models.

  • Reason: Much of the information currently living inside the metric definition will move to different locations. Some of it will go to the semantic model and some to the component measure(s) it depends on.

  • See properties below

    YML Properties

    metrics:
      - name: metric name
        description: same as always
        type: the type of the metric
        type_params:
            - specific properties for the metric type
        meta: same as always
        tag: same as always
        configs: here for `enabled`
        label: The display name for your metric. This value will be shown in downstream tools.
        filter: |
          {{  dimension('entity','name') }} > 0 and {{ dimension('another entity',' another name') }} is not null
    

    Property Names

    Property	Location	Description
    name	third-repo	Name of the metric
    type	third-repo	Type of the metric. Examples include simple, cumulative, ratio, and derived
    type_params	third-repo	Type parameters for the metric. These parameters change based on the type
    filter	third-repo	WHERE clause constraint applied to a metric.
    description	third-repo	Description of the metric
    meta	dbt-core	Metadata associated with the metric
    tags	dbt-core	Tags associated with the metric
    config	dbt-core	Configuration for the metric

---

Example

This yaml contains:

• The definitions for two semantic objects (customers and transactions)
• The definitions for two entities (transaction and customer)
• The definitions for two metrics(revenue and sales_minus_revenue)

---
semantic_models:
  - name: transaction
    model: ref('fact_transaction')
    description: "Our transaction semantic_model"
    default:
        agg_time_dimension: transaction_date

    entities:
      - name: transaction
        type: primary
        expr: transaction_id

      - name: customer
        type: foreign
        expr: customer_id

    dimensions:
      - name: transaction_date
        type: time
        type_params:
            time_granularity: day

      - name: transaction_location
        type: categorical
        expr: order_country

    measures:
      - name: transaction_total
        description: "The total value of the transaction"
        agg: sum

      - name: sales
        description: "The total sale of the transaction"
        agg: sum
        expr: transaction_total
        create_metric: True

      - name: median_sales
        description: "The median sale of the transaction"
        agg: median
        expr: transaction_total
        create_metric: True

  - name: customers
    model: ref('dim_customers')
    description: "Our customers semantic_object"

    identifiers:
      - name: customer
        type: primary
        expr: customer_id

    dimensions:
      - name: first_name
        type: categorical
---
metrics:
  - name: revenue
    description: "some description"
    type: simple
    type_params:
        measure: transaction_total

  - name: sales_minus_revenue
     type: derived
     type_params:
          expr: sales - revenue
          metrics:
            - sales
            - revenue

FAQ

• Why are semantic_models and entities different things? It seems like they are similar.
  • In some cases, they are, a single table could represent a set of record of a specific entity, such as a users table. However, we want to be able to represent a semantic_model that does not have a primary entity and only contains relationships between foreign entities. By maintaining a difference between semantic models and entities, we can correctly represent semantic_models as containing 1:M connections to entities.
  • We aim to support all modeling approaches with the same common semantic models. In Kimball-style modeling, this has sometimes been referred to as a fact-less fact table. In the data vault world, these objects are called link tables.
  • We are fully aware that this may be a bit confusing. Conversely, some dbt users may be confused by referencing a table as an entity since most organizations do not fully conform to Kimball-style modeling approaches. We believe that the benefits of functionality outweigh any required learning curve to understand these concepts. This will, of course, come with good learning materials!
• How is the join logic handled? What kind of joins are supported?
  • Great question! Unfortunately, that is out of the scope of this discussion - it only focuses on the specs and not the implementation details. For now, the best resource is this Introduction to MetricFlow Concepts.
• When will this be released?
  • Another fantastic question, we’re working towards this goal but don’t have specific dates to share yet. We can guarantee that as soon as we're ready to announce dates, you will hear from us.

[djayatillake]

One small point on needing to allow custom aggregations for metrics and definitions for dimensions, as people do all kinds of strange stuff here which can’t even be covered by composing metrics from others.

[SergiGomez]

@djayatillake do you have any particular examples?
Window functions ( over partition by ). There are plenty of use cases that require them. We need to create metrics with window functions...

[callum-mcdata]

@SergiGomez I think your above comment is raising the need for metrics over window functions? We will support that to some degree with cumulative metrics that apply a lookback. Additionally the team is looking at supporting time based semi-additive metrics.

Over time we will most likely add support for semi-additive metrics that partition on specific dimension values but we'd rather do that in a first class way instead of supporting custom sql given that it would introduce more avenues to make mistakes with the sql generation.

[SergiGomez]

Thanks, @callum-mcdata! I agree that it's best to use first-class metrics rather than relying on custom SQL. In our extended version of dbt-metrics (where we supported custom SQL), we used a custom SQL section as a catch-all for anything that couldn't be accomplished with the built-in dbt metrics. However, we always preferred to use the official dbt constructs whenever possible. I recall that the primary motivation for using custom SQL was the window functions, that's why I brought it up here :)

[djayatillake]

I think usually a semantic layer query to a db contains a group by function, and then window functions aren't possible, at least in one step.

So aren't we really thinking about two-step processes here where there is either a prep step which runs the window function in a CTE before the group by query runs, or the reverse. Lightdash enables the reverse with its table calculations feature.

[callum-mcdata]

@djayatillake that's the beauty of MetricFlow! The query planner & optimizer builds out as many steps necessary to construct the result set that has been asked for. So a nested subquery might have the group by function and then the overarching query would have the window function - or vice versa depending on the structure of the metrics & what has been requested.

[britton-stamper]

THIS IS INCREDIBLE!!! Thank you all for putting the spec together, I can't wait to get my hands on live product.

For the spec:

• Can I have metrics without time dimensions? The example highlights a metric for revenue without a time dimension in the measure or the metric. If the metric does not need a time dimension, are metrics without a time dimension considered aggregated to a single value such as select sum(amount) as revenue from table for the example given?
• What is the value of differentiating between a measure and a metric? I imagine you all put a lot of thought into this, having some explicit part of the description would make it much easier to understand the design decision and implications.
• Is a metric another way of denoting important measures? I see it's easy to flag a measure to become a metric, is the intention to primarily use metrics for downstream applications? Or is the intent to use all the semantic objects: entities, measures, dimensions and then the combination of other objects in metrics.

[britton-stamper]

For context, I loved the enforced date dimension pairing in metrics in the old spec. It made it much easier to know that new users was the combination of the user count measure and the created at date dimension from the user table.

[Jstein77]

Hey @britton-stamper thanks! We're stoked to be getting the spec out in the wild too :)

Taking a stab at your questions:

Can I have metrics without time dimensions? The example highlights a metric for revenue without a time dimension in the measure or the metric. If the metric does not need a time dimension, are metrics without a time dimension considered aggregated to a single value such as select sum(amount) as revenue from table for the example given?

The revenue metric actually does have a time dimension - transaction_date. The metric is defined using the transaction_total measure, which has a transaction_date dimensions defined in the same semantic model. You can absolutely construct a query that does not reference the time dimension, or any other dimension available for the metric - and in that case the generated sql would be similar to what you described i.e select sum(transaction_total) as revenue from fact_transaction

What is the value of differentiating between a measure and a metric? I imagine you all put a lot of thought into this, having some explicit part of the description would make it much easier to understand the design decision and implications.

We think about measures as composable building blocks for metrics. They allow you to define the aggregations you want to perform over a column, without adding too much logic to the definition. You can then use measures to build out more complex metrics. For example, ratio or expression metrics that contain multiple measures, or adding constraints to metrics. This keeps the code DRY, since you are reusing measure components, and still provides a ton of flexibility when defining metrics. We've found if you try to pack too much logic into metrics you run into maintenance challenges and challenges reusing those components as your project grows.

Is a metric another way of denoting important measures? I see it's easy to flag a measure to become a metric, is the intention to primarily use metrics for downstream applications? Or is the intent to use all the semantic objects: entities, measures, dimensions and then the combination of other objects in metrics.

I don't think that's exactly right. You cannot query measures, only metrics. It's a common pattern to define measures without creating them as metrics, then to use those measures to define more complex metrics. However, sometimes the metric definition and the measure definition are the same, i.e if the metric is a simple count of users. In that case we provide the short hand create_metric: true so you don't have write extra YAML :). Currently, If you want to expose something to be queryable in any downstream interface, like a BI tool or a JDBC, it needs to be a metric.

[andreqaugusto]

are filters being left out in this new spec? I find them to be incredibly useful in dbt_metrics. I suppose that they should probably belong to measures in this new spec, since they are the ones related to the semantic model.

[callum-mcdata]

Just metrics. The first release will most likely only allow filtering on dimensions and then we'll add support for filtering on measure values later on.

[nhandel]

Exactly what @callum-mcdata said! The short answer is that it's very intentional that only metrics have the filter functionality.

The slightly more nuanced answer is that part of the beauty of measures and dimensions is they are self-contained and don't depend on other semantic concepts in the semantic_model. This allows us to have clear isolation of logic.

[SergiGomez]

One question, @callum-mcdata or @nhandel: Will we be able to apply a filter to a derived metric with an expression that contains one or more dimensions that are included in the individual metrics? For instance, in the example above, could we add a filter to the metric sales_minus_revenue ? I already brought this up on Slack since it was not possible with dbt-metrics, and told @callum-mcdata about the importance of improving this limitation in future versions of the dbt semantic layer. Thanks in advance! :)

[callum-mcdata]

You would be able to apply filters to derived metrics for dimensions that are a part of both metrics OR define a filter that is part of the metric in the definition of the derived metric. Let's look at an example below. In this example, I'm defining metric_b that is derived from Metric A and a filtered version of Metric A. I can define that filter in the calc to create a sort of "temp" metric that is only used in that calculation. Additionally then I can filter on the entire calculated metric_b if that's what I require.

metric:
  name: metric_b
  type: derived
  type_params:
    expr: metric_a / filtered_metric_a
    metrics:
      - name: metric_a
      - name: metric_a
        filter: |
           {{ dimension('entity', 'dimension_a') }} > 100
        alias: filtered_metric_a
    filter: |
       {{ dimension('some_other_entity', 'dimension_b') }} > 100

[SergiGomez]

Thanks for your reply, @callum-mcdata ! I am very happy to read this, as this is a significant improvement from the current version of the semantic layer IMHO! I was thinking of filters that meet your first condition ("dimensions that are a part of both metrics"), see the example below, but the second one is really cool as well (thanks for the example!) :D

metric:
  name: metric_c
  type: derived
  type_params:
    expr: metric_a / metric_b
    metrics:
      - name: metric_a
      - name: metric_b
    filter: |
       {{ dimension('some_other_entity', 'dimension_b') }} > 100

[jaimefr]

Looks great! Two comments:

1. To me, it looks a little bit weird that measures could be defined as metrics in two ways:

• Using the param is_metric of the measurement
• As a metric with type measure_proxy

Several ways of doing something can generate problems when several people collaborate in the same project. The first is simpler, but I don't have a strong preference for any of the two.

2. What are the available aggregations types for measurements? If not a broad range is covered, could it be an aggregation type that is "custom" with a param that allows to input the explicit SQL aggregation? For instance:

- name: std_dev_transaction_amount
  description: "The Standard Deviation of the transaction total price"
  agg: custom
  agg_params:
     - sql: stddev( expr )
  expr: transaction_total

[callum-mcdata]

additionally the metric is added as a separate entity,

Not entirely sure what you mean here? If you're referring to a measure definition inside of each entity, we enforce global uniqueness at the measure/metric level. Dimensions can have duplicated names but measures have to be unique - this ensures that the issue you're talking about of profit being set to create_metric: true doesn't create two profit metrics. And additionally the query planner always knows what measure/metric it is referring to.

[jaimefr]

Hello, sorry I wasn't clear enough. I meant if the code below would return an error, which from you response I understand it would.

---
semantic_models:
  - name: transaction
  .........

    measures:
      - name: transaction_total
        description: "The total value of the transaction"
        agg: sum
        create_metric: True

  ..........
---
metrics:
  - name: revenue
    description: "The total value of the transaction"
    type: measure_proxy
    type_params:
        measure: transaction_total

[callum-mcdata]

Ah got it. No that would not return an error - it would create two metrics, one called transaction_total and one called revenue. So yes, the potential for duplicative metrics exists but we'd rather give people an easier on-ramp to creating metrics that can then be promoted to first-class definitions instead of with the create_metric flag.

[nhandel]

We could add some validation to ensure that the two metrics don't have the same definition. MetricFlow has validation that you can't create two metrics with the exact same name, which would solve the problem referenced above.

[SergiGomez]

We could add some validation to ensure that the two metrics don't have the same definition. MetricFlow has validation that you can't create two metrics with the exact same name, which would solve the problem referenced above.

This would be really cool, @nhandel !

[mazinesy]

👋 @nhandel

Currently there are 2 libraries, dbt-core and metricflow. When you say both libraries will import this repo are you saying that both metricflow and dbt-core will have a dependency on dbt-semantic-interfaces ?

From my understanding, dbt-semantic-interfaces enables metricflow. If I only have dbt-core and dbt-semantic-interfaces, it does not do much on its own, I would need the metricflow library to actually leverage the semantic layer. Is that correct ?

Will metricflow still work as a standalone solution, with its current spec (pre-dbt) ?

I saw that materializations got removed from metricflow. Are there future important features that won't be part of the open source project. I'm specifically thinking about complete SCD Type 2 joins.

Finally, what will be missing from the dbt-core, metricflow, dbt-semantics-interfaces trio that will be available with the dbt-cloud solution ? Either now or in future features ?

[tlento]

Hi @mazinesy, nice seeing you here!

We just published an announcement in the MetricFlow repo with more concrete details, so you might want to read through that. I can answer your questions here a bit more directly:

Currently there are 2 libraries, dbt-core and metricflow. When you say both libraries will import this repo are you saying that both metricflow and dbt-core will have a dependency on dbt-semantic-interfaces ?

That is correct. The interfaces package allows us to keep MetricFlow and dbt-core largely decoupled while still enabling a cohesive, first class integration between them. This will allow dbt-core to take full advantage of MetricFlow's runtime capabilities without requiring users who do not have metric definitions to install MetricFlow itself.

From my understanding, dbt-semantic-interfaces enables metricflow. If I only have dbt-core and dbt-semantic-interfaces, it does not do much on its own, I would need the metricflow library to actually leverage the semantic layer. Is that correct ?

Correct. There are some low-level technical reasons why the interfaces package will be bundled with dbt-core in the first place. In theory you can make semantic models and so forth without installing MetricFlow, but in practice we expect anybody using the semantic layer to install MetricFlow.

Will metricflow still work as a standalone solution, with its current spec (pre-dbt) ?

Going forward, no, it will not. We are focusing all of our efforts on the integrated dbt + MetricFlow use case, and supporting multiple versions of the spec in open source is challenging at best.

I saw that materializations got removed from metricflow. Are there future important features that won't be part of the open source project. I'm specifically thinking about complete SCD Type 2 joins.

Most of the big changes are listed out in the "What we are breaking" section of our announcement in the MetricFlow repo. As a general rule, metric management and query rendering features (such as SCD Type 2 joins) will be supported (and extended!) going forward. This is one of the big advantages of our integration approach - by using MetricFlow to power dbt metrics we can make features like SCD Type 2 joins available almost immediately.

Finally, what will be missing from the dbt-core, metricflow, dbt-semantics-interfaces trio that will be available with the dbt-cloud solution ? Either now or in future features ?

As a general rule, features focused on processing semantic model and metric definitions and doing query rendering - e.g., metric types, model validation, dimension joins, SQL dialect support, etc. - will be supported via the dbt-core + MetricFlow integration. Features having to do with managing metrics and metric query workloads - e.g., caching, permissioning, metric and semantic model governance, integrations with BI tools, etc. - will be available with the cloud offering.

[nhandel]

@mazinesy, Tom's response is so thorough, so I will add a heavy +1. Let me know if you have any questions about any of that.

[siljamardla]

Considering the extensive use of S3 as data storage and partition keys being the key to performant queries, do you see a way that partition keys could also be part of semantic model definitions? I imagine these could then be passed from model to measure to metric to allow users to limit their metric queries by the appropriate partition keys.

I guess there are 2 distinct examples: time based partition keys and categorical partition keys.

Time based partition key example:

• The model fact_transactions has transaction_date as a partition key
• User wants to select metric Revenue, but finds the execution slow
• Knowing that reducing data makes queries faster, the user applies a filter for year(transaction_date) = 2023
  Will the constructed query make use of the partition key column to skip other files?

Categorical partition key example:

• The model fact_transactions has country_id as a partition key
• User wants to select metric Revenue, but finds the execution slow
• Knowing that reducing data makes queries faster, the user applies a filter for country_id = 1
  Will this constraint be pushed all the way to the query reading the fact table?

Not sure if these are perfect examples, but I hope you understand the direction of thought. Have you considered any of these questions? Would explicitly defining and showing partition keys help users call the metrics in a better way?

[callum-mcdata]

Hey @siljamardla! To my knowledge, MetricFlow does support time based partitions today. Supporting categorical partition keys in the long run seems like a reasonable idea to me. It wouldn't be something we'd work on in the initial release but you're more than welcome to create an issue for it!

[Padarn]

I think this is an important point to think about. The partitions seem out of scope for the semantic but it would be good to know what the thinking is on how to avoid very inefficient queries.

[siljamardla]

dimensions
Description: A dimension is a categorical or time field that can be used to group or filter metric(s).

This time around, do you really mean time or is it still going to be date?
I would suggest it is important to consider time, as this is going to come up at one point anyway. I imagine the implementation will not be very different. In places you would otherwise do cast(smth as date) you just have to do cast(smth as timestamp).

[callum-mcdata]

👋 @siljamardla ! For the first iterations, the granularities will be very similar to what is supported in dbt today. So in your example above, it would continue to be date. That said, we're definitely open to sub-date grains in the future. One great feature of MetricFlow is that the query planner & optimizer is significantly more aware of time than dbt_metrics was, meaning we have the flexibility to add more time granularities in the future.

[djayatillake]

I feel like we could do with some kind of diagram showing the possible objects in metricflow, semantic-interfaces and dbt-core showing which things are in where and how they relate to each other.

[callum-mcdata]

@djayatillake mind if I dig into this? Not that I think it's a bad question but because all of the objects will be shared between the three repo's. dbt-semantic-interfaces is mainly a technical implementation to define shared Python objects that can be referenced by both so that we don't need to take hard dependencies. Python packaging is a delight 😅

You'll be able to define semantic_objects, metrics, measures, etc etc in your dbt project (as dbt-core inherits those definitions from semantic interfaces) and then MetricFlow can reference those same objects without needing to take a dependency on dbt-core.

Does that make sense or satisfy your questions in this area?

[c42-josh-carlson]

Cool! Overall, this feels like a worthwhile transition that will be short term painful for a much better long term implementation! Excited to see it come together! A few questions...

1. Where does the time_grain from the current DBT metrics spec fit? From what I can infer here, an entity would have to have a dimension with a type = time and a time_param of time_grain = <time_grain_defined>. And then the measures would reference that time dimension? Am I understanding that correctly? Will the time_grain property still accept a list of grains or is the expectation that the desired end granularity would be passed as part of the query parameters similar to how MetricFlow works today? For what it's worth, I have a preference toward the list method as there are sometimes granularities that aren't useful and I don't want a query writer to have the freedom to select those even if the entity granularity data might allow it.

2. @callum-mcdata, could you elaborate on the cumulative metrics definition more? Is that something that will be included in this spec or is that a later time? Where does that fit in? We have a few conversion type metrics that use the window parameter in the current dbt spec to calculate a lookback numerator and lookback denominator and then use derived metrics to calculate conversion.

@SergiGomez I think your above comment is raising the need for metrics over window functions? We will support that to some degree with cumulative metrics that apply a lookback. Additionally the team is looking at supporting time based semi-additive metrics.

3. I'm really excited to see the addition of semi-additive metrics. I assume that will look like the MetricFlow definition too? I have long wanted to build subscription analysis as part of the metrics layer which just isn't really possible today. Will that be supported right away? Huge bonus if it can be supported and managed easily over multiple time grains as we have a desire to do subscription analysis at monthly, quarterly, and yearly levels which the existing spec just doesn't allow for without creating all the metrics three times.

[callum-mcdata]

Let me see if I can answer your questions!

Where does the time_grain from the current DBT metrics spec fit?

There isn't a 1:1 comparison with the new spec. The time_granularity property of a time_dimension sets the default. That way we know what the granularity is without it needing to be defined. If the user decides to switch the granularity to something higher/lower, then that is permitted.

In the first version we won't have restricted granularities but that doesn't mean we're opposed to adding that back in the future. It was a polarizing feature among adopters of the package - there were folks like yourself who liked it and then there were folks who ... didn't 😅 .

could you elaborate on the cumulative metrics definition more?

This is something that will be included in the first release! It's already part of MetricFlow today and is significantly more flexible than the old implementation. I've attached an example below but you can also take a look at the documentation here.

metrics: 
  name: wau_rolling_7
  type: cumulative
  type_params:
    measures:
      - distinct_users
    # the default window is infinity - omitting window will accumulate the measure over all time
    window: 7 days

I'm really excited to see the addition of semi-additive metrics.

Me as well! They will definitely match the MetricFlow definition although we will continue to work on them and increase functionality. I believe that they will be included in the first release. I'm not sure if it would allow for the exact use case that you're describing but if you ran into issues on first release we'd love to chat and figure out if we can support it.

[mistercrunch]

Nice! Very happy to see this. At first glance this addresses the limitations/issues we saw in the previous implementation, namely:

• each metric could only have a single time dimension, resulting in duplicative metrics like ("bookings", "bookings_by_booked_date", "bookings_by_cancelation_date", ...)
• one metric could only live in one model, preventing the implementation of "aggregate awareness" on the BI side, while having the same metric live in various aggregate tables
• an overly prescriptive and limited dimension/metric association, having to state each dimension that could be used with each metric. Dimension had to be local to the current model (no "foreign" dims were allowed in the previous semantical model). Here metrics associate with entities, and entities have dimensions, which seems like the right/traditional-ish approach (Business Objects, MSFT SSAS, Microstrategy, LookML, ...)

Some notes/questions on things I'd like to see:

• having a "primary time dimension" for a metric (and measures?) may be great, so that the tooling can populate good defaults
• similarly having more sort highlighting or most important entity, and most important dimensions within an entity would be great for tooling to highlight objects that may be most useful. No sure if we'd need to provide some ranking here, eventually something that could become usage metrics-informed would be great.
• despite the previous comment in this discussion, I'm curious about filter semantics. In our previous integration we implemented as SUM(CASE WHEN {{...}} ) in Superset/Preset so that we could do a single-pass generation of multiple metrics, where I believe the Jinja/dbt implementation would run multiple queries/subqueries and merge the results. Here ideally we have an approach that's flexible. It sounds like the prescribed approach may reference (and force) a join to a foreign dimension. It'd be great to have more details on this as it has pretty deep implications. Both the filter expressed as a SQL expression SUM(CASE WHEN region='NA' the popluation ELSE 0 END) (presumably local to the current model), vs the more semantical approach of filters: [] (local or remote to the current model) are reasonable.

[Jstein77]

Hey Max,

So happy that the new spec addresses your limitations and lends itself well to creating some powerful BI experiences. Thanks for the great notes.

Digging into your notes:

Having a "primary time dimension" for a metric (and measures?) may be great, so that the tooling can populate good defaults

You will have the ability to specify is_primary: true on a time dimensions, which will be the default for all measures in that semantic model. You can set a different default by specifying the agg_time_dimension for a measure if you want to override the default.

similarly having more sort highlighting or most important entity, and most important dimensions within an entity would be great for tooling to highlight objects that may be most useful. No sure if we'd need to provide some ranking here, eventually something that could become usage metrics-informed would be great.

Great idea, this would take the onus of understanding the data model off the end user, and enable some pretty powerful self-serve experiences. We could consider adding some sort of ranking tag to the spec, but it’s probably not the most ideal user experience. I totally agree that the long term vision is to create ranking of dimensions based off of query patterns.

despite the previous comment in this discussion, I'm curious about filter semantics. In our previous integration we implemented as SUM(CASE WHEN {{...}} ) in Superset/Preset so that we could do a single-pass generation of multiple metrics, where I believe the Jinja/dbt implementation would run multiple queries/subqueries and merge the results. Here ideally we have an approach that's flexible. It sounds like the prescribed approach may reference (and force) a join to a foreign dimension. It'd be great to have more details on this as it has pretty deep implications. Both the filter expressed as a SQL expression SUM(CASE WHEN region='NA' the popluation ELSE 0 END) (presumably local to the current model), vs the more semantical approach of filters: [] (local or remote to the current model) are reasonable.

Taking a stab at this one, if i understand you correctly, the current implementation in superset is to wrap the metric filter in a SUM(CASE WHEN {{...}} ) statement, so you can aggregate the metric correctly, without having to run multiple queries and merge the results.

I can provide a bit more detail on how filters are currently implemented in MetricFlow. There are two ways to apply filters:

1. Define filters on the metric itself.
2. Apply filters at query time.

Filters can reference any valid dimensions for that metric. However, if a metric contains measures from two different semantic models, you can only reference the shared dimensions between the two.

For case 1, take this metric as an example

metric:
  name: mql_queries_created
  type: measure_proxy
  type_params:
    measure: mql_queries_created
  filter: |
    {{ dimension(org,is_internal) }} = false
    AND {{ dimension(query_id,is_internal_mode) }} = false

This will filter the mql_queries_created metric by the is_internal dimension, which does need to be joined from another semantic model, and the is_internal_mode dimension, which is defined in the same semantic model as the measure.

For case 2, filters can also be applied dynamically at query time, and the implementation is similar to a filter defined on a metric.

filters: [] (local or remote to the current model) are reasonable.

Wanted to dig in a bit here, are you asking for the ability to apply filters as a list? This seems pretty doable as long as you have the filterable dimensions for a metric - which we will expose. This list could be parsed and applied as a filter at query time.

Does this help you plan your implementation of filters?

[nhandel]

As an update to this issue, we now have a very early version of our third repo with the semantic interfaces here. Let us know what you think!

Through building this, we’ve updated a few elements of the spec. Specifically:

• [Spec Update] Rename measure_proxy metric type to simple
• [Spec Update] Remove EXPR metric types
• [Spec Change] Update RATIO metric types to ingest metrics instead of measures
• [Spec Update] Move default_agg_time to be in default struct within SemanticMode
• [Spec Update] Redesign create_metric: default True and dict value to support create_metric_display_name
• [Spec Update] Add a hidden property to metric properties

This is also now reflected in the examples in the top post!

Nick

[ataft]

We're trying to upgrade from old metrics to the new semantic interfaces, but noticed that measures and dimensions do not have the meta attribute that is in your original proposal. I see that meta was recently added to semantic models, but nothing else. We heavily rely on meta for extra semantic information (synonyms, formatting, natural language info, etc.). I suppose we can put meta in the model YAML and the rest as semantic measures, but I was hoping for a more "universal" one-place for all semantic information. I definitely echo this discussion about having too many places to put YAML, and the new semantic interfaces seems to be another disparate YAML definition.

[nhandel]

Noted! We can add meta properties to the rest of these objects, no problem. We'll open an issue to track the progress there.

On the yaml bit, why do you think this feels disparate? Semantic models can reference models or sources (for companies who don't use dbt for transformation) and so it doesn't make sense to include them in either of those objects. I see this as a chunk of logic that is self-contained and has clear distinctions between other objects in the dbt ecosystem. It's reusable across metrics, separates the physical from the logical and it's very DRY. I definitely see how not being able to attach meta properties would lead to disparate logic so maybe that's the problem?

Another somewhat related thought here is that yaml is really just a format for containing information. The spec defines the structure of information and could be contained in any file type(JSON, custom, etc.). yaml is just the most commonly used in the data tools ecosystem and the broad acceptance of it IMO outweighs the downsides.

[ataft]

Thanks! Yes, my disparate comment was referring to having to put meta in the model YAML, and all my other semantic attributes in the semantic interfaces YAML. This would solve that.

I noticed that there was already an issue opened:
dbt-labs/dbt-semantic-interfaces#195

[tadas-subonis]

How will it be possible to use that with PowerBI? dbt_metrics could just end up being a VIEW that would be easy to query.

[nhandel]

We've had some early conversations here. We hear the resounding need for this to work with PowerBI. Once we have a better sense, we'll probably open up some discussions in the dbt slack to discuss this.

[ialdg]

Hi @nhandel .
Where is the definition of "identifiers"?

Thanks.
Regards.
IL.