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.

Connecting dbt to Mode

Before setting up the connection ensure that you have whitelisted the IPs listed in dbt's documentation, access to dbt’s Semantic Layer is enabled in dbt Cloud and that the "generate docs" setting is enabled for the dbt job you're using.

You will also need the following to set up the connection in Mode:

• 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.

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 %}

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.

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.

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.

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.

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

Q: 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.

Q: 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.

Q: 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.

Q: 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.

Q: 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.

Q: 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.

Q: 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.

Q: 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

Q: 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