How to see the SQL generated by the dbt Semantic Layer (--compile)

The Semantic Layer is a SQL generator. When a number looks wrong, or you want to know what a metric costs to run, read the SQL it sends to the warehouse. One option prints it, and you can paste the result into a worksheet and run it yourself.

Copy and paste

# dbt platform (Cloud CLI, Studio IDE) and dbt v2
dbt sl query --metrics return_rate --group-by customer__country --compile

# dbt Core with MetricFlow
mf query --metrics return_rate --group-by customer__country --explain

# with the dataflow plan, which shows the steps MetricFlow chose
mf query --metrics return_rate --group-by customer__country --explain --show-dataflow-plan

With mf, --explain printed the SQL and no result table. For API clients, dbt’s documentation mentions a compile=true flag that returns the compiled SQL in the same way.

Reading the SQL

return_rate is a ratio of returned_orders (with a filter on status) to order_count. The generated SQL, shortened:

WITH sma_10003_cte AS (            -- orders: one CTE per semantic model
  SELECT customer_id AS customer, status AS order__status,
         1 AS __order_count, 1 AS __returned_orders
  FROM "shop"."main"."orders" orders_src_10000
)
, sma_10002_cte AS (               -- customers
  SELECT customer_id AS customer, country
  FROM "shop"."main"."customers" customers_src_10000
)
SELECT
  customer__country AS customer__country
  , CAST(returned_orders AS DOUBLE) / CAST(NULLIF(order_count, 0) AS DOUBLE) AS return_rate
FROM (
  SELECT
    COALESCE(subq_10.customer__country, subq_18.customer__country) AS customer__country
    , MAX(subq_10.returned_orders) AS returned_orders
    , MAX(subq_18.order_count) AS order_count
  FROM (
    SELECT customer__country, SUM(returned_orders) AS returned_orders
    FROM ( ... orders LEFT OUTER JOIN customers ... ) subq_6
    WHERE order__status = 'returned'                 -- the metric's filter
    GROUP BY customer__country
  ) subq_10
  FULL OUTER JOIN (
    SELECT sma_10002_cte.country AS customer__country,
           SUM(sma_10003_cte.__order_count) AS order_count
    FROM sma_10003_cte LEFT OUTER JOIN sma_10002_cte ON ...
    GROUP BY sma_10002_cte.country
  ) subq_18
  ON subq_10.customer__country = subq_18.customer__country
  GROUP BY COALESCE(subq_10.customer__country, subq_18.customer__country)
) subq_19

Read it from the inside out:

One CTE per semantic model, with entities renamed to their entity names (customer) and dimensions prefixed (order__status).
One subquery per input metric, each with its own joins, filter and GROUP BY. The filter of returned_orders appears only in its subquery, so it doesn’t affect order_count.
A full outer join of the subqueries on the group-by columns, with COALESCE on the keys, so a country present in only one subquery is kept.
The final expression: here the ratio, with NULLIF on the denominator so that a zero count returns NULL instead of an error.

What to look for

Joins: which tables, on which keys, LEFT OUTER or FULL OUTER. A dimension from the wrong model or a missing join path shows up here first.
Filters: where each WHERE sits. A metric filter is inside that metric’s subquery; a query --where is applied to every metric.
Time: cumulative metrics and offsets join to the time spine; look for the date range they cover.
Table names: the database and schema come from the environment. With dbt platform, the SQL reads the tables of the deployment environment the Semantic Layer is configured on, not your development schema.

Running it yourself

Paste the SQL into the warehouse console and run it. If it returns the same number, the definition is the problem; if it doesn’t, the data changed between runs or a cache answered the query. Step-by-step debugging is in how to debug a metric that returns the wrong number.

The SQL was generated by MetricFlow 0.213 with dbt Core 1.12 on DuckDB; --compile comes from the dbt documentation.

References

MetricFlow commands