Integration of the dbt semantic layer with Metabase

Feedback and Requests
1

dbt are launching, this October, their "semantic layer".

In short, it'll allow you to define metrics within dbt. Using an example from their GitHub, you'd define a metric within a .yml file like this:

image
image938×740 158 KB
.

This would allow you to constrain a metric by what table it comes from, the aggregation, what time granularities can be used, and what dimensions you can cut it by. You can also do a variety of other things (e.g. specify filters).

You would then effectively create SQL on the fly via:

select * 
from {{ metrics.metric(
    metric_name='average_order_amount',
    grain='week',
    dimensions=[],
) }}

Some of this can already be achieved in Metabase with metrics & segments - but firstly there are benefits to defining the logic further upstream, and secondly there are more constraints (i.e. how you cut the data).

They mention in their docs that "While dbt does not currently provide a BI experience for exploring these metrics, we’re working on a number of integrations with BI partners that will help unlock the full value of the metrics layer."

Are there any plans to integrate dbt metrics into Metabase?

10 Likes
2

Hello there,
I am also looking for this integration regarding a semantic layer with Metabase. In my case, we are using Dataform with Cube. It would be great to integrate it into metrics & segments of Metabase.

Are there any plans to integrate a semantic layer tool into Metabase?

1 Like
3

This would be a highly appreciated feature!

4

+1, it would be a great feature!

1 Like
5

Update - implemented a very hacky BigQuery version using SQL UDFs

Step 1: create a SQL User Defined Function (UDF) in a dbt macro (within a SQL file) to define your metric.

image
image1386×732 77.8 KB

When called, these functions return a BigQuery table (same way that dbt metrics would).

If you have more than one metric, make sure you put them all within the same dbt macro and use ; to separate them.

image
image1434×784 88.8 KB

Step 2: Make sure your UDFs are created/updated whenever you run a dbt command by using an on-run-start hook in your dbt_project.yml file.

image

Step 3: Make sure to run your dbt pipeline / at least 1 model to get the on-run-start step to run

Step 4: Call the UDF within Metabase, and pass variables!

image
image904×724 76.3 KB

I've used a text string for my filters - I haven't tried this yet with field filters (I'm sure there would be a way to make it work).

You can obviously do a lot more than just do dynamic date granularity above - you can pass different filters for text or timestamps etc.

Selecting which columns you'd want to aggregate the metrics by could be a bit trickier. You can do dynamic SQL in BigQuery but again I haven't tested this at all.

1 Like
6

Congrats for this very smart hack! Will definitely try it out.

On my side, I've stopped using Metabase Metrics because they are not well developed: if you want 2 metrics in the same question, and those metrics have filters applied on the same fields, they will clash.

Eg: if you want # users = active and # users = inactive grouped by day, you'll get nothing.

I believe a product like Metabase should work in improving its connection with such an essential tool like dbt.

Let's see if the team picks this up

1 Like
7

Metabase should definitely integrate with DBT, this is very painful not to a have a semantic layer nowadays in your BI tools. We may switch soon to an other tool if it does not come in the road map.

3 Likes
8

@luiggi - if possible, could you please share if this is on the roadmap or being planned? Few other visualization tools already support it.

We'd want as much analytics code as possible versioned, centralized, and tool independent. So it makes sense to build a semantic layer and then use it across visualization tools like Metabase, Tableau etc.

1 Like