Generating a table for your Lightdash metrics in dbt

5 min readFeb 15, 2024

Lightdash makes managing your business’ metrics from within your dbt project super easy, but sometimes this can lead to them being distributed across a bunch of models, so let’s have an option to bring them all into one place, for a lil’ metric party 📈 🍾 🎉

Just gimme the code

Why is this useful?

Although Lightdash offers multiple ways to explore your dbt project, like browsing tables or searching, sometimes a more direct method of discovery can help your users get straight to what they need, i.e. If your company/ team have North Star Metrics/ KPIs.

This can be particularly useful for employees new to a company or even just to the tooling, as it gives a curated launchpad for getting stuck into all that beautiful Lightdash content you’ve created.

Bringing the results into a table can also assist with checking/ enforcing any consistency guidelines you might have, via dbt tests, i.e. unique metric names, label styling, or empty descriptions.

Often, companies look to something like a data catalog to assist with their discovery, but these can be expensive, take time to setup, or even just be overkill for your current data platform.

So let’s just do something simple for now…

Getting the metric details

First up, we’ve gotta extract all of our metric information from somewhere, so let’s look at where we define them, in dbt!

Finding all the metrics

We start with the classic situation of iterating over the dbt graph, we do this to attempt to identify all blocks of YAML that are Lightdash metrics. There are 2 ways to define Lightdash style metrics in our dbt project:

So we’ll need to ensure we collect both variants on our way through the graph.

{% macro get_lightdash_metrics() %}

{% set metrics = [] %}

  {% for node in graph.get('nodes').values() -%}

    -- Collect the model level metrics
    {% for metric_name, metric in node.get('meta', {}).get('metrics', {}).items() %}
      {% do metrics.append((node.name, metric_name, metric)) %}
    {% endfor %}

    -- Collect the column level metrics
    {% for column_info in node.get('columns').values() %}
      {% for metric_name, metric in column_info.get('meta', {}).get('metrics', {}).items() %}
        {% do metrics.append((node.name, metric_name, metric)) %}
      {% endfor %}
    {% endfor %}

  {% endfor %}

  {{ return(metrics) }}

{% endmacro %}

Tasty, we have an array containing tuples of the form (model_name, metric_name, <metric object>), so we can move on to getting the details off of that metric object itself.

Extracting the metric details

However they’re defined, we get a fairly similar looking YAML block for a metric, that looks something like:

meta:
  metrics:
    <metric name>:
      type: <metric type>
      sql: <some SQL>
      label: <metric label>
      description: <metric description>
      filters:
        <list of filters>
      ...

So in order to extract any details you need from this, whether standard or custom (there’s nothing stopping you adding your own key/ values!), we could access these directly, or use a macro along the lines of:

{% macro get_metric_details(metric) %}
  {{
    return(
      (
        metric.description,
        metric.label,
        metric.type,
        metric.sql
        -- Any extra fields or values you want to store
      )
    )
  }}
{% endmacro %}

Depending on how you prefer to append details to the list in our previous loop, there’s an option to either duplicate this line or pass in the array/ other values to append within this function to save duplicate code.

Uploading it into a table

Create dummy model

In order to setup an empty table for the macro output to be uploaded into, we take some inspiration from the dbt_artifacts package, by creating an empty table via a dummy dbt model, i.e.

SELECT
  NULL::VARCHAR AS MODEL_NAME,
  NULL::VARCHAR AS METRIC_NAME,
  NULL::VARCHAR AS DESCRIPTION,
  NULL::VARCHAR AS LABEL,
  NULL::VARCHAR AS TYPE,
  NULL::VARCHAR AS SQL
  -- Any extra fields or values you want to store
WHERE
  1 = 0

We cast each NULL to be the correct type of the value that’s going to be uploaded. Store this somewhere in your dbt project, for now we’ll call it lightdash_metrics.sql .

Upload via macro

All that macro output is currently just chillin’ in dbt, but we need it in the table created above, luckily dbt provides a simple way to turn our model name into a relation, adapter.get_relation.
For the sake of the example below though, I’m using an even simpler utility from dbt_artifacts, as it only requires the model name.

{% macro upload_lightdash_metrics() %}
  {% if execute %}
    {% set lightdash_metric_table = dbt_artifacts.get_relation('lightdash_metrics') %}
    {% set table_location = lightdash_metric_table.database ~ "." ~ lightdash_metric_table.schema ~ "." ~ lightdash_metric_table.name %}
    -- We clear the table first, but you could just append/ truncate/ merge as relevant
    {% do run_query("DELETE FROM " ~ table_location  ~ ";") %}
    {% do run_query("INSERT INTO " ~ table_location ~ "(MODEL_NAME, METRIC_NAME, DESCRIPTION, LABEL, TYPE, SQL) SELECT * FROM VALUES" ~ get_lightdash_metrics() | join(', ') ~ ";") %}
  {% endif %}
{% endmacro %}

Putting it all together

Taking all of the above and chucking it together (or if you skipped the above, how to do this entirely), we get 3 components:

The Table

{{ config(materialized='table') }}
SELECT
  NULL::VARCHAR AS MODEL_NAME,
  NULL::VARCHAR AS METRIC_NAME,
  NULL::VARCHAR AS DESCRIPTION,
  NULL::VARCHAR AS LABEL,
  NULL::VARCHAR AS TYPE,
  NULL::VARCHAR AS SQL
  -- Any extra fields or values you want to store
WHERE
  1 = 0

The Macros

{% macro get_lightdash_metrics() %}

{% set metrics = [] %}

  {% for node in graph.get('nodes').values() -%}

    -- Collect the model level metrics
    {% for metric_name, metric in node.get('meta', {}).get('metrics', {}).items() %}
      {% do metrics.append((node.name, metric_name, metric.description, metric.label, metric.type, metric.sql)) %}
    {% endfor %}

    -- Collect the column level metrics
    {% for column_info in node.get('columns').values() %}
      {% for metric_name, metric in column_info.get('meta', {}).get('metrics', {}).items() %}
        {% do metrics.append((node.name, metric_name, metric.description, metric.label, metric.type, metric.sql)) %}
      {% endfor %}
    {% endfor %}

  {% endfor %}

  {{ return(metrics) }}

{% endmacro %}

{% macro upload_lightdash_metrics() %}
  {% if execute %}
    {% set lightdash_metric_table = dbt_artifacts.get_relation('lightdash_metrics') %}
    {% set table_location = lightdash_metric_table.database ~ "." ~ lightdash_metric_table.schema ~ "." ~ lightdash_metric_table.name %}

    -- We clear the table first, but you could just append/ truncate/ merge as relevant
    {% do run_query("DELETE FROM " ~ table_location  ~ ";") %}
    {% do run_query("INSERT INTO " ~ table_location ~ "(MODEL_NAME, METRIC_NAME, DESCRIPTION, LABEL, TYPE, SQL) SELECT * FROM VALUES" ~ get_lightdash_metrics() | join(', ') ~ ";") %}
  {% endif %}
{% endmacro %}

The On-Run-End Hook

Last but not least, as this macro requires the full context of our dbt project, and the model to have been created, we add it as an on-run-end hook in our dbt_project.yml, as so:

on-run-end:
  - {{ upload_lightdash_metrics() }}

Which, ultimately, will lead to a database table that looks a little something like this:

Use it in Lightdash!

Now it’s a table, you could use it in Lightdash! Give your users the one stop shop they needed!