⚠️ NOTE: The dbt Semantic Layer integration is only available for Mode and dbt Cloud customers using Snowflake. To learn more, check out dbt’s guide to the Semantic Layer and best practices for creating metrics.

Enabling the dbt Semantic Layer beta

Only Admins can enable the dbt Semantic Layer integration for their Workspace. To enable the dbt Semantic Layer beta for your workspace, go to Workspace Settings > dbt Semantic Layer. Toggle the feature set on and save settings. This will allow you to set up a connection to the dbt Semantic Layer and surface functionality for all Workspace Members to browse, query and visualize dbt metrics in Mode.

Once you toggle on the integration, all Workspace Members will see a new screen when creating a Report.

Selecting the SQL Query option will take users to the SQL editor. Selecting the Metric option will allow users to visually analyze dbt metrics.

Disabling the integration will disable the new Report creation experience. Users will continue to be able to query the dbt Semantic Layer connection via the SQL editor, but will be unable to make changes to existing charts or create new ones using Metrics.

IMPORTANT: We strongly discourage disabling the integration if users have already built charts using Metrics.

Connecting dbt to Mode

Before setting up the connection ensure that access to dbt’s Semantic Layer is enabled in dbt Cloud and you have the following:

• dbt Proxy URL
• dbt Job ID
• dbt metadata API token
• Snowflake user/password

Start by connecting a database.

From the dropdown, select dbt Semantic Layer. Add the first part of the dbt Proxy URL to the dbt Proxy field. The URL will look something like this:
account-name.proxy.cloud.getdbt.com. Then, enter your Snowflake information. Make sure the Snowflake user you are using has access to the tables your metrics are defined on.

⭐️ TIP: Setting the connection’s default access to Query is required for any workspace member to query the data via the visual metrics flow. We recommend using a separate Snowflake user with limited roles for your dbt Semantic Layer connection, so you can give query access to your entire workspace.

Once connected, you’ll be taken to the new connection’s settings. On the dbt Integration tab, set up the dbt connection by adding the job ID that runs your job in production and the dbt Metadata Service token.

Your metrics will automatically sync once you set up the credentials. If you need to manually resync metrics from dbt, use the Refresh Metrics button.

Writing metrics queries in Mode

After successfully connecting dbt to your Mode workspace, you can start referencing metrics in your SQL to check the data returned by the metrics.

To write a new metrics query, navigate to the SQL editor and select your Snowflake dbt connection from the database dropdown.

By default, this connection will surface all available schema and tables. To reference a metric within your query, use the following syntax:

{% raw %}
SELECT *
FROM {{ metrics.calculate(
    metric(''),
    grain='',
    dimensions=[''],
) }}
{% endraw %}

To reference secondary calculations, add the following syntax:

secondary_calculations=[
	  metrics.secondary_calc_1(aggregate=””, period=””, alias=””)
  metrics.secondary_calc_2(aggregate=””, period=””, alias=””)

Here’s an example query:

{% raw %}
SELECT *
FROM {{ metrics.calculate(
  metric('average_order_amount'),
  grain='day',
  dimensions=['customer_status','region'],
) }}
{% endraw %}

NOTE: Mode’s parameter features are not supported within metric queries

More information on how to query dbt Metrics

Using dbt metrics in a Report

Metrics are centralized definitions of key business topics (ex. signups or customer engagement) that you can use to answer questions in Mode.

To analyze a metric, create a new Report by clicking the green plus button in the top right corner.

From your new Report, select the dbt Metric tile.

Choose which metric you’d like to analyze from the metrics browser. If you don’t see any metrics in the list, make sure your workspace’s dbt metadata API credentials were entered correctly in Connection settings and that at least one metric has already been created in dbt.

Next, select how you’d like to chart your metric’s data.

Visually explore your metrics

Each metric comes with its own set of fields that you can visually explore, and two required fields that must always be used— a quantitative measure, like average_order_amount, and a date field, like ordered_at, to track your measure over time.

NOTE: Quickly find your two required fields by looking for the ✱ icon

There are three ways to visually display your metrics in Mode today: a Table, a Line Chart, or a Bar chart.

Table

To understand the shape of your data, try starting with a table. By default, the Table will surface all available fields as columns. You can remove any of the non-required fields, sort the data, and format the column values.

Line & Bar — Visual Explorer

The Line and Bar options allow you to aggregate your data over time by using Mode’s Visual Explorer. By default, your metric chart will display the primary metric and default date granularity. In the example below, that’s revenue by week.

To facet and slice your data, add additional dimensions to Columns and Rows. In the example below, you can see revenue by week, sliced by customer_status. Use the Data view below the chart canvas to view or export the aggregated chart data.

Use Layers to update the mark type (i.e. from bar to area) or add fields to one of the Layers dropzones to add dimensionality to your metric chart.

Running a metric chart

When you visually explore metrics, Mode queries your database directly to pull your data and visualize the results at the same time. When editing your metric, save and view the changes by clicking the Run Metric button. Changes to a metric chart that require new data must be applied using this action. Formatting changes, including updating the layer type, do not require re-running. You can also use the run button to pull in fresher data.

TIP: When there are unsaved changes, an indicator will be shown next to the Run Metric button and on top of the metric chart.

NOTE: If you navigate away from your metric visualization before applying unsaved changes, you’ll need to run again to see the visualization appear

Create additional metric charts

To analyze additional metrics within the same Report, or to add a Metric to an existing Report, click the green button in the left side navigation and select Choose Metric. From there, you can insert additional metrics.

NOTE: Currently, there’s no way to duplicate a metric or change its display type from a table to a line or bar. If you need to change the way you’re displaying a metric, add the metric again and select a different starting point

Add metric charts to Reports

Add metric charts to Reports by selecting Add to Report Builder from the metric chart’s menu. Once you add the chart to the Report Builder, you can format it, add more charts and save the layout to share with other users.

NOTE: Some actions are not supported on metric charts in Reports, including Explore, Drilldowns and toggling the date granularity on the chart.

All metric charts can be identified in Reports via the dbt Metrics badge. Use metric charts in Reports to provide consistent data and reporting across your organization.

FAQs

How is charting a metric different from charting a query result?

A metric chart is essentially a visual representation of a query. Charting a metric requires pulling new data from the data warehouse, whereas charting on a query result uses a dataset that has already been run.

For example, if you have a quantitative measure, like average_order_amount values, and a date field, like ordered_at, to track your measure over time, but want to change the granularity of the date field from day to week, the metric chart will need to be run so that the average_order_amount can be re-calculated accurately by week.

This workflow ensures that users are unable to create chart configurations with metrics that are inaccurate. It also means that certain features that are available for charts created from query results are not available for metric charts (e.g. calculated fields), since the metric is already pre-defined in dbt.

What is a required field?

In order to return valid data for a metric, dbt requires a date field and the primary metric (e.g. quantitative measure) to be included in the query. Required fields cannot be removed from the visualization.

Can I switch from a line/bar to a table after creating my initial metric?

No, switching to a different visualization type is not currently available. You can use the green button in the left side navigation and select Choose a Metric to select the same metric and a different visualization type.

Can I analyze multiple metrics in the same Report?

Yes, to analyze a different metric, click the green button in the left side navigation and select Choose a Metric. You’ll then see a list of all available metrics to choose from. It is not currently possible to analyze multiple metrics within the same visualization.

How do dbt Metrics impact warehouse and Helix usage?

Mode runs your dbt metric logic directly through dbt’s SQL proxy and server to ensure the data is aggregated correctly. Changing the data used in a metric chart requires re-running it, meaning actions like adding or removing fields or changing a metric’s date grain require a run. Like Query runs, Metric runs will contribute to your overall Helix usage.

Mode’s visualization system also takes advantage of Helix for any stylistic and formatting changes to avoid round trips to the database whenever possible.

Can I create/define new dbt metrics in Mode?

No. dbt metrics can only be defined in dbt. You can write new queries using dbt's metric language, but they always rely on metrics that are controlled in dbt. By abstracting metrics calculations out of pre-aggregated tables or specific business intelligence tools like Mode, dbt metrics can be defined once and used everywhere.

How are dbt Metrics different from Mode Definitions?

Definitions are SQL snippets that allow you to write logic in one place and reference that logic across multiple Queries. dbt Metrics allow you to define logic in dbt and use that logic to visualize data in Mode, without having to use SQL.

Can I use dbt Metrics in Notebooks?

Yes. Since metrics charts are SQL Queries under the hood, their results are made available to the Notebook and appear as data frames alongside all other Query results in a given Report

What are the limitations of charts built using dbt Metrics?

Because charts built using dbt Metrics are queries under the hood, any new data or re-aggregation of the data requires a manual run. When exploring the metric, the following features are not available. Many of these require support in the dbt Semantic Layer so that the metrics data can be aggregated and displayed correctly.

• Calculated Fields
• Filters
• Quick calculations

The following features are not supported for metric charts added to Reports, since the visualizations need to be manually run to ensure metrics data is accurately displayed.

• Exploration
• Toggling date granularity
• Drilldowns

Troubleshooting

Issue	In Mode	In dbt	in data warehouse
No metrics in metrics browser	Check that the dbt Semantic Layer data source is configured with the correct proxy URL Connecting dbt to Mode. Check that the dbt integration is set up with the dbt job id and an active metadata service token from dbt Resync metrics from the connection settings. There’s a timestamp for when the metrics were last refreshed. When initially connecting, it takes a couple minutes for the metrics to be synced. Metrics are then synced on a daily basis	Ensure the associated dbt job includes the tables that the metric references Ensure the dbt job has been run at least once. Can also try re-running the job in dbt manually	n/a
No data returned for a metric in Mode. Usually due to a SQL compilation error	n/a	Ensure metrics package is updated to the latest version	Ensure data or data table exists in Snowflake
"Database DB_NAME does not exist or not authorized."	Ensure you’ve set up the Semantic Layer connection with the correct Snowflake credentials	n/a	Double check Snowflake permissions