Basic Metric Query Patterns

Overview

This page covers the most common TimescaleDB SQL patterns for querying Arthur's pre-aggregated metrics store. Before using these patterns, read Metrics Overview & Data Model to understand the schema and the two available views (metrics_numeric_latest_version and metrics_sketch_latest_version).

For quantile queries, sketch function usage, and multi-metric joins, see Advanced SQL Patterns & Sketch Functions.

Discovery Queries

Before building a dashboard, find out which metrics exist for your model.

-- List all numeric metrics for a model
SELECT DISTINCT metric_name
FROM metrics_numeric_latest_version
WHERE model_id = '{{your-model-uuid}}'
ORDER BY metric_name;

-- List all sketch metrics for a model
SELECT DISTINCT metric_name
FROM metrics_sketch_latest_version
WHERE model_id = '{{your-model-uuid}}'
ORDER BY metric_name;

Time-Series Queries

Retrieve the raw 5-minute buckets for a single metric over the last 24 hours:

SELECT
    timestamp,
    value
FROM metrics_numeric_latest_version
WHERE model_id   = '{{your-model-uuid}}'
  AND metric_name = 'inference_count'
  AND timestamp >= NOW() - INTERVAL '24 hours'
ORDER BY timestamp ASC;

Downsampling

TimescaleDB's time_bucket() function lets you re-aggregate the 5-minute data into coarser intervals:

SELECT
    time_bucket(INTERVAL '1 day', timestamp) AS bucket,
    SUM(value)                                AS total_predictions
FROM metrics_numeric_latest_version
WHERE model_id    = '{{your-model-uuid}}'
  AND metric_name  = 'inference_count'
  AND timestamp BETWEEN '{{dateStart}}' AND '{{dateEnd}}'
GROUP BY bucket
ORDER BY bucket DESC;

Rate Calculations

Arthur stores component metrics separately. Divide them at query time to compute rates. Since Arthur reports raw counts (not rates), queries must perform the final division:

WITH hallucination_count AS (
    SELECT
        time_bucket(INTERVAL '1 day', timestamp) AS bucket,
        SUM(value) AS total
    FROM metrics_numeric_latest_version
    WHERE model_id    = '{{your-model-uuid}}'
      AND metric_name  = 'hallucination_count'
      AND timestamp BETWEEN '{{dateStart}}' AND '{{dateEnd}}'
    GROUP BY bucket
),
total_count AS (
    SELECT
        time_bucket(INTERVAL '1 day', timestamp) AS bucket,
        SUM(value) AS total
    FROM metrics_numeric_latest_version
    WHERE model_id    = '{{your-model-uuid}}'
      AND metric_name  = 'inference_count'
      AND timestamp BETWEEN '{{dateStart}}' AND '{{dateEnd}}'
    GROUP BY bucket
)
SELECT
    hallucination_count.bucket AS timestamp,
    CASE WHEN total_count.total = 0 THEN 0
         ELSE hallucination_count.total / total_count.total
    END AS hallucination_rate
FROM hallucination_count
JOIN total_count ON hallucination_count.bucket = total_count.bucket
ORDER BY hallucination_count.bucket DESC;

Filtering and Grouping by Dimension

Filter to a specific dimension slice — for example, only production traffic:

SELECT
    timestamp,
    value
FROM metrics_numeric_latest_version
WHERE model_id                    = '{{your-model-uuid}}'
  AND metric_name                  = 'accuracy'
  AND dimensions->>'environment'   = 'production'
  AND timestamp                   >= NOW() - INTERVAL '24 hours'
ORDER BY timestamp ASC;

Group by a dimension — compare accuracy across model versions:

SELECT
    time_bucket('1 hour', timestamp)     AS hour,
    dimensions->>'model_version'          AS model_version,
    AVG(value)                            AS avg_accuracy
FROM metrics_numeric_latest_version
WHERE model_id    = '{{your-model-uuid}}'
  AND metric_name  = 'accuracy'
  AND timestamp >= NOW() - INTERVAL '7 days'
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC;

Gap Filling

Use time_bucket_gapfill to fill missing time intervals with zero or NULL when some buckets have no data — useful for continuous chart lines:

SELECT
    time_bucket_gapfill(INTERVAL '1 hour', timestamp) AS bucket,
    CASE WHEN SUM(value) IS NULL THEN 0 ELSE SUM(value) END AS total
FROM metrics_numeric_latest_version
WHERE model_id    = '{{your-model-uuid}}'
  AND metric_name  = 'inference_count'
  AND timestamp BETWEEN '{{dateStart}}' AND '{{dateEnd}}'
GROUP BY bucket;

Rolling Averages

Use a window function to smooth noisy metrics:

SELECT
    timestamp,
    value,
    AVG(value) OVER (
        ORDER BY timestamp
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
    ) AS rolling_1h_avg   -- 12 × 5-minute buckets = 1 hour
FROM metrics_numeric_latest_version
WHERE model_id    = '{{your-model-uuid}}'
  AND metric_name  = 'prediction_score_mean'
  AND timestamp >= NOW() - INTERVAL '24 hours'
ORDER BY timestamp ASC;