Integration of the dbt semantic layer with Metabase

[jack_c]

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:

.

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?

[marcelmello]

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?

[aga_devries]

This would be a highly appreciated feature!

[giamo]

+1, it would be a great feature!

[jack_c]

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.

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.

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.

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!

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.

[marco]

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

[felici1b]

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.