How to debug a metric that returns the wrong number in dbt (check the compiled SQL)

A metric that returns a number nobody expected is harder than an error: nothing fails. The Semantic Layer is deterministic, though, and every number comes from SQL you can read. Three steps narrow the problem down, and a short list of causes covers most cases.

Copy and paste

1. Read the definition the Semantic Layer is using, from the manifest, not from your editor:

dbt parse
python -c "import json; m=json.load(open('target/semantic_manifest.json')); \
print(json.dumps([x for x in m['metrics'] if x['name']=='revenue_ytd'], indent=2))"

2. Get the SQL and run it yourself:

dbt sl query --metrics revenue_ytd --group-by metric_time__month --compile   # dbt platform
mf query --metrics revenue_ytd --group-by metric_time__month --explain       # dbt Core

3. Reconcile against the model with a query you write by hand:

select date_trunc('month', order_date) as month, sum(amount) as revenue
from analytics.orders
group by 1
order by 1;

If step 3 agrees with what people expect and step 2 doesn’t, the definition is the problem. If both agree with each other but not with expectations, the expectation or the data is.

Causes that turned up in testing

• A cumulative metric without period_agg: last. Grouped by month, the default takes the first day’s value: YTD for March shows the total up to 1 March, one month behind. See how to define a YTD or MTD metric.
• The current month runs to the end of the time spine. A YTD or rolling total for this month includes every day of the month in the spine. Filter metric_time to today.
• The metric is counted by another date. agg_time_dimension decides whether an order placed on 20 May and shipped on 2 June counts in May or June; see how to set the default time dimension.
• Filters stack. A query --where on Germany combined with a metric defined on Italy returns nothing. Read the metric’s filter in step 1.
• --start-time with offsets. Last year’s YTD came back empty with --start-time, and right with --where.
• fill_nulls_with ignored. On dbt Core 1.12.6 the parser leaves it out of the manifest; step 1 shows "fill_nulls_with": null. See how to show zero instead of NULL.
• A conversion rate above 100%. The conversion metric counted orders, not customers; see how to define a conversion metric.
• Approximate percentiles. On some engines a continuous percentile compiles to an approximate function; step 2 shows which.

Causes outside the Semantic Layer

• Duplicate keys in the model. MetricFlow assumes a primary entity is unique and doesn’t check it. A model with two rows per order doubles every sum. Test the key in dbt:

      - name: order_id
        data_tests:
          - unique
          - not_null
        entity:
          type: primary
          name: order

• Totals in the BI tool. A total row that adds up distinct counts, averages or ratios is not the metric. Query the total from the Semantic Layer instead.
• Old data. The Semantic Layer reads the tables of its environment as they are now, or a cache built by the last job. A model that didn’t rebuild, or a dashboard opened before the job finished, shows yesterday’s number.
• Dates and time zones. MetricFlow doesn’t convert time zones: a timestamp in UTC truncated to a day can move late-evening orders into the next day compared with a report in local time.

Keep the answer

When you find the cause, add a test that would have caught it: a unique test on the key, a saved query with a known value checked in CI, or a description on the metric that states its date and filters. The next person who doubts the number will read the definition before rerunning step 3.

The causes were reproduced with dbt Core 1.12, dbt v2 2.0.9 and MetricFlow 0.213 on DuckDB.