Query a dbt Semantic Layer integration

Sigma supports dbt Semantic Layer integrations, allowing you to leverage your predefined dbt metrics in Sigma workbooks for ad-hoc analysis, recurring reports, and organizational dashboards. This document explains how to query a dbt Semantic Layer in Sigma and how the query flow progresses.

System and user requirements

In Sigma:

• To use this feature, you must be assigned an account type with the permission to write custom SQL.
• You must have the Can use data permission for your entire connection. See Manage data permissions.
• You must have write access configured on your connection. See Set up write access.
• You must have a dbt Semantic Layer integration configured. See Configure a dbt Semantic Layer integration.

In dbt:

• You must have a semantic model and metrics created in dbt. See the dbt documentation on dbt Semantic Layer.

Query flow between Sigma and dbt Semantic Layer

When you enter a Semantic Layer query in Sigma, Sigma compiles your query into an intermediate representation and sends the query parameters to the dbt Semantic Layer JDBC API (step 1 in the following diagram):

dbt then returns the SQL statement in the appropriate dialect to Sigma (step 2). Sigma executes the SQL against your connected data platform, and outputs a table similar to those from your other data platforms (steps 3, 4, and 5. These tables can be used like any other data table in Sigma. If the table is created in a data model, you can reuse the table across workbooks and data models, create and join the tables, build charts, and more.

Query the dbt Semantic Layer Integration

To query the Semantic Layer, do the following:

1. Open a workbook for editing or customizing.
2. In the add element bar, select Data > Table to add a table element. When choosing the source, select SQL to use SQL as the source for your data element.
3. Enter your query. See the dbt documentation on Querying the API for metric metadata for query syntax.
4. Select Run.

Every time a Semantic Layer query is run, Sigma requests the latest definitions, so changes made to the Semantic Layer are reflected in Sigma. Changes made to the Semantic Layer are not reflected unless a new query is run.

Reference Semantic Layer metrics

You can reference your semantic layer metrics using the template syntax of {semantic_layer.metrics()}, with the name of your metric in dbt within the parentheses. See the dbt documentation on Querying the API for metric metadata for more syntax guidance.

Example 1: Surface and group dbt metrics in a Sigma workbook

You can surface your existing dbt metrics in a Sigma workbook, and group them by multiple dimensions. An example query might look like:

SELECT * 
FROM
{{ semantic_layer.query( metrics = ['new_customers', 'transactions', 'revenue_usd'],
group_by = [Dimension('metric_time').grain('month'), 'customer__customer_country'])
}}

The query surfaces three existing metrics ('new_customers', 'transactions', 'revenue_usd') in the example dbt data, and groups them by both country and time (split by month intervals). This query generates the following result in Sigma:

Example 2: Browse Semantic Layer metrics

You can obtain a list of all metrics available in the Semantic Layer in Sigma by running the following custom SQL:

select * from {{semantic_layer.metrics()}}

This produces a table of metric names and additional details, for example:

Example 3: List all dimensions and time grains for a metric

You may want to obtain a list of all dimensions and time grains available for an existing dbt metric. For example, if you had an existing dbt metric named 'transactions', your query might look like:

select *  from {{semantic_layer.dimensions(metrics=['transactions'])}}

Running this SQL in Sigma produces the following result:

Reference control values when querying your dbt Semantic Layer

You can reference control values when querying your dbt Semantic Layer in Sigma, allowing you to take advantage of pre-aggregated data.

Use the following syntax:

{{#formula [control-ID]}}

Example 1: Pass user input to a query

For example, you can allow a user to type the name of a dbt metric to query:

1. Set up a text input control element. Set the control ID to metric-input-control.

2. Add a data element sourced by a SQL query, and type the following query:

   SQL

   select * from {{semantic_layer.metrics( {{#formula [metric-input-control]}})}}

Example 2: Reference different time grains with a segmented control

For example, you can reference different time grains:

1. Set up a segmented control for different time grains: day, month, quarter, and year. Set the control ID to c-time-grain.

2. Add a data element sourced by a SQL query and type the following query:

   SQL

   select * from {{semantic_layer.query(
     metrics = ['revenue'],
     group_by = ['pos_order_number__product_type', Dimension('metric_time').grain({{#formula [c-time-grain]}})])
   }}

3. Test the segmented control and query.