dbt MetricFlow - semantic layer 2.0

TL;DR

dbt’s first go at a semantic layer fell short of data community expectations, they wisely decided to reboot with MetricFlow and this time it’s looking very good folks. If you skipped the previous version (dbt 1.5 and earlier), now is the time to give it another look.

At Steep, we are convinced that the semantic layer is the future of BI and business analytics. So when dbt - the de facto standard for data transformation - was also pushing the semantic layer idea, we were thrilled. Being able to define metrics in code right next to all your other data models is an appealing concept indeed, and we could see that serious data teams would go all-in on this setup.

Unfortunately the first version that dbt launched in late 2022 failed to live up to the hype. A lot of teams looked at it but not enough found it worth their time. Two big issues was the lack of joins and a not-so-strong reusability of definitions.

To dbt lab’s credit they realised this and quickly changed course. Instead of their in-house developed v1 spec, they moved to acquire Transform, the team behind open source project MetricFlow, and decided to go all-in on MetricFlow as the new dbt standard (1.6 and on).

We’ve been working with MetricFlow since summer and we like it a lot folks. Kudos to the Transform team for the great work. If you are curious about semantic layer and like your definitions in code - this new spec is likely worth your time.

Here's what a basic example looks like:

semantic_models:
  - name: orders
    defaults:
      agg_time_dimension: ordered_at
    description: |
      Orders fact table. One row per order.
    model: ref('orders')

    measures: 
      - name: order_total
        description: The total revenue for each order.
        agg: sum
      - name: cancellations
        description: Total canceled amount
        agg: sum

    dimensions:
      - name: country
        type: categorical
      - name: is_food_order
        type: categorical
      - name: ordered_at
        type: time
        type_params:
          time_granularity: day 
  
metrics:
  - name: cancellation_rate
    owners:
      - johan@steep.app
    type: ratio
    type_params:
      numerator: cancellations
      denominator: order_total

Things we like about MetricFlow

• Support for both semantic models (measures, dimensions) and metrics
• Easy metric definitions with less boilerplate
• Dynamic joins (entity relationships)
• Generally a well-structured spec

Things we still want

• Segments/slices - saved filters for (non-trivial) segments is helpful for end users. A nice feature that is both in Cube and Steep.
• Time grain restrictions - for some bespoke metrics, say MRR, it can be useful to control exactly which time grains are allowed. Right now MetricFlow only supports specifying the minimum allowed time grain.

MetricFlow and Steep

We believe that having all your models and metric definitions in dbt and publishing your metrics with Steep is a great combo. That’s why you'll be able to connect Steep directly to your dbt project and use MetricFlow for your semantic layer. With this setup, the role of the data team can truly be to build the data model and the semantic layer, and ship the metrics catalog to all users. No more repetitive dashboard work!

The road ahead

The semantic layer field is moving fast and becoming much easier to work with end-to-end. In my view, it would be good for all of us to work towards a standardization here. Just like you can learn SQL once and use it all your career, it would be great if we all can learn one general way to do semantic layer.

The fact that MetricFlow is actually pretty close to Cube spec is great and an important step toward that standardization. And whatever dbt bets on will have a lot of impact. Next week Coalesce 2023 is happening and I look forward to continued discussion with the main players and the community at large.

Links

• About MetricFlow
• Getting started (dbt docs)