Percentile Metrics

Calculating Percentiles like p90 and median in BigQuery, Snowflake

In Hashboard, you can calculate percentile metrics like P50(median), P90, P95, and P99, across different databases like BigQuery, Snowflake, PostgreSQL, and DuckDB. These percentile metrics offer valuable insights into the distribution of your data and are instrumental for different business analytics applications.

When to use percentile metrics

Percentiles are useful for understanding distributions of numerical data, such as latencies or durations. Percentile metrics can provide insights into performance, and other key indicators. They're useful when you have a skewed distribution or when it's particularly important to understand outliers. For example, for a website, knowing that the average latency is 200 miliseconds is useful, but it's also useful to know how slow a 5% or 1% of your worst response are. Are they 1 second or 10 seconds?

Setting up Percentile Metrics

  1. Navigate to the Data Model tab within Hashboard and Open the data model you want to edit.
  2. Click Add Custom Measure from the measure section.
  3. Name the measure and add code according to your dialect:

To calculate any percentile in BigQuery use the following sql, replacing 90 with your desired percentile:

approx_quantiles(duration, 100)[OFFSET(90)]