How to calculate a percentile measure in dbt

A percentile answers “below which value do 90% of orders fall?”. It describes a distribution better than an average when a few large values pull the mean up. In the Semantic Layer it is a simple metric with agg: percentile, a percentile value and a type.

Copy and paste

    metrics:
      - name: p90_order_amount
        type: simple
        agg: percentile
        expr: amount
        percentile: 0.9                # a fraction: 0.9, not 90
        percentile_type: discrete      # or continuous
      - name: median_order_amount
        type: simple
        agg: median
        expr: amount

mf query --metrics p90_order_amount,median_order_amount --group-by metric_time__year

metric_time__year      p90_order_amount    median_order_amount
-------------------  ------------------  ---------------------
2025-01-01T00:00:00                 150                    105
2026-01-01T00:00:00                 300                    110

A fraction, not a percentage

percentile: 0.9 is the 90th percentile. An example in the dbt documentation writes percentile: 95.0; with dbt 1.12 and MetricFlow 0.213 that value parses, but the warehouse rejects the query:

ERROR: Runtime Error
  Binder Error: PERCENTILEs can only take parameters in the range [0, 1]

mf validate-configs (or dbt sl validate) runs a test query and catches it before anyone else does.

Discrete or continuous

• discrete returns a value that exists in the data: the first one at or above the percentile. In 2026 the seven orders are 50, 80, 100, 110, 120, 130 and 300, and the discrete 90th percentile is 300.
• continuous interpolates between the two nearest values, so the result may be an amount nobody paid: for the same orders it is 198.

Discrete suits counts and prices you want to quote as real values; continuous suits durations and amounts where in-between values make sense.

Check the SQL your warehouse gets

Engines implement percentiles differently, and MetricFlow picks the function per adapter. On DuckDB, with a continuous and a discrete version of the metric plus the median, the generated SQL was:

  , approx_quantile(amount, 0.9) AS p90_order_continuous
  , PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY (amount)) AS p90_order_discrete
  , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY (amount)) AS median_order

The continuous percentile became approx_quantile, an approximation: 266 instead of the exact 198 for 2026. The dbt documentation has a table of what each engine supports: Snowflake has exact continuous and discrete percentiles, BigQuery only approximate ones, Databricks and Redshift a mix. Run the query with --explain (--compile with dbt sl) once on your warehouse and read the function before you publish a percentile.

Percentiles don’t add up

A percentile is computed from the rows of each group. The p90 of a year is not the average or the maximum of the monthly p90s. Query it at the grain you report, and don’t total it in a BI tool.

Tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB.