Metrics
Define aggregations and calculations. Model-level metrics aggregate data, graph-level metrics combine them with formulas and automatic dependency detection.
Metrics define how to aggregate and calculate measures. Sidemantic distinguishes between model-level metrics (aggregations) and graph-level metrics (formulas/references).
Runnable Example (Graph-Level Metric via Python API, Executed in CI)
Graph-level metrics are easiest to exercise via the programmatic API.
Doctest fixture (models/semantic_layer.yml)
models:
- name: orders
sql: |
select * from (values
(1, 120.00),
(2, 80.00),
(3, 50.00),
(4, 200.00)
) as t(id, amount)
primary_key: id
metrics:
- name: revenue
agg: sum
sql: amount
- name: order_count
agg: count
metrics:
- name: avg_order_value
type: ratio
numerator: orders.revenue
denominator: orders.order_count
from pathlib import Path
from sidemantic import SemanticLayer
from sidemantic.loaders import load_from_directory
layer = SemanticLayer()
load_from_directory(layer, Path("./models"))
res = layer.query(metrics=["avg_order_value"])
rows = res.fetchall()
print(f"{rows[0][0]:.2f}")
112.50
Model-Level Metrics
Model-level metrics are aggregations defined on a single model. These become building blocks for graph-level metrics.
Simple Aggregations
Basic aggregations on a single column:
models:
- name: orders
metrics:
- name: revenue
agg: sum
sql: amount
- name: order_count
agg: count
- name: avg_price
agg: avg
sql: price
- name: max_amount
agg: max
sql: amount
Available aggregations: sum, count, count_distinct, avg, min, max, median
Filtered Metrics
Apply filters to specific metrics:
models:
- name: orders
table: orders
primary_key: order_id
metrics:
- name: completed_revenue
agg: sum
sql: amount
filters: ["status = 'completed'"]
- name: us_orders
agg: count
filters: ["country = 'US'", "amount > 0"]
Filters are ANDed together.
SQL Expressions
Metrics support full SQL expressions:
models:
- name: order_items
table: order_items
primary_key: id
metrics:
- name: total_value
agg: sum
sql: "quantity * price * (1 - discount_pct)"
Graph-Level Metrics
Graph-level metrics are defined at the top level and can reference model-level metrics or other graph-level metrics. Dependencies are auto-detected!
Metric References
The simplest graph-level metric references a model-level metric:
models:
- name: orders
table: orders
primary_key: order_id
metrics:
- name: revenue
agg: sum
sql: amount
metrics:
# Dependencies auto-detected from sql!
- name: total_revenue
sql: orders.revenue
description: "Total revenue from all orders"
No type field needed - dependencies are automatically detected from the sql expression.
Ratio Metrics
Divide one metric by another:
models:
- name: orders
table: orders
primary_key: order_id
metrics:
- name: revenue
agg: sum
sql: amount
- name: cost
agg: sum
sql: cost
- name: completed_revenue
agg: sum
sql: amount
filters: ["{model}.status = 'completed'"]
- name: profit
type: derived
sql: "revenue - cost"
metrics:
- name: conversion_rate
type: ratio
numerator: orders.completed_revenue
denominator: orders.revenue
- name: profit_margin
type: ratio
numerator: orders.profit
denominator: orders.revenue
Results in: numerator / NULLIF(denominator, 0)
Derived Metrics
Formula-based calculations with automatic dependency detection:
models:
- name: orders
table: orders
primary_key: order_id
metrics:
- name: revenue
agg: sum
sql: amount
- name: cost
agg: sum
sql: cost
metrics:
# Dependencies auto-detected!
- name: profit
type: derived
sql: "orders.revenue - orders.cost"
- name: margin_pct
type: derived
sql: "(orders.revenue - orders.cost) / orders.revenue * 100"
No need to manually list dependencies - they're extracted from the SQL expression!
Cumulative Metrics
Running totals and period-to-date:
models:
- name: orders
table: orders
primary_key: order_id
metrics:
- name: revenue
agg: sum
sql: amount
metrics:
# Rolling window
- name: last_7_days_revenue
type: cumulative
sql: orders.revenue
window: "7 days"
# Month-to-date
- name: mtd_revenue
type: cumulative
sql: orders.revenue
grain_to_date: month
# Year-to-date
- name: ytd_revenue
type: cumulative
sql: orders.revenue
grain_to_date: year
Requires a time dimension in the query.
Time Comparison Metrics
Year-over-year, month-over-month changes:
models:
- name: orders
table: orders
primary_key: order_id
metrics:
- name: revenue
agg: sum
sql: amount
metrics:
- name: total_revenue
sql: orders.revenue
# Year-over-year growth
- name: yoy_revenue_growth
type: time_comparison
base_metric: total_revenue
comparison_type: yoy
calculation: percent_change
# Month-over-month
- name: mom_revenue
type: time_comparison
base_metric: total_revenue
comparison_type: mom
calculation: difference
# Custom offset
- name: vs_last_quarter
type: time_comparison
base_metric: total_revenue
time_offset: "3 months"
calculation: ratio
Calculation types:
percent_change:(current - prior) / prior * 100difference:current - priorratio:current / prior
Conversion Metrics
Track funnel conversions:
models:
- name: events
table: events
primary_key: event_id
dimensions:
- name: user_id
type: categorical
sql: user_id
- name: event_name
type: categorical
sql: event_name
metrics:
- name: signup_to_purchase
type: conversion
entity: user_id
base_event: signup
conversion_event: purchase
conversion_window: "7 days"
Calculates: users who did conversion_event within window after base_event / total users who did base_event
Graph-level time_comparison and conversion metrics are supported in native YAML with the fields shown above.
Metric Properties
Model-Level Metrics
- name: Unique identifier
- agg: Aggregation function (sum, count, avg, etc.)
- sql: SQL expression to aggregate (defaults to
*for count) - filters: WHERE conditions for this metric
- description: Human-readable description
- label: Display name (defaults to name)
Graph-Level Metrics
Untyped (Metric References)
- name: Unique identifier
- sql: Reference to model.metric (e.g., "orders.revenue")
- description: Human-readable description
Typed Metrics
- type: Metric type (ratio, derived, cumulative, time_comparison, conversion)
- Type-specific properties (see sections above)
Null Handling
models:
- name: orders
table: orders
primary_key: order_id
metrics:
- name: revenue
agg: sum
sql: amount
fill_nulls_with: 0 # Return 0 instead of NULL
Python API
from sidemantic import Metric
# Model-level: Simple aggregation
revenue = Metric(name="revenue", agg="sum", sql="amount")
# Model-level: Filtered
completed = Metric(
name="completed_revenue",
agg="sum",
sql="amount",
filters=["status = 'completed'"]
)
# Graph-level: Reference (dependencies auto-detected!)
total_revenue = Metric(
name="total_revenue",
sql="orders.revenue"
)
# Graph-level: Ratio
margin = Metric(
name="margin",
type="ratio",
numerator="orders.profit",
denominator="orders.revenue"
)
# Graph-level: Derived (dependencies auto-detected!)
profit = Metric(
name="profit",
type="derived",
sql="revenue - cost"
)
# Graph-level: Cumulative
running_total = Metric(
name="running_total",
type="cumulative",
sql="orders.revenue",
window="7 days"
)
Best Practices
Start Simple
Define basic aggregations first, then build complex metrics on top:
- Model-level metrics (revenue, count)
- Filtered metrics (completed_revenue)
- Graph-level references (total_revenue)
- Ratios (conversion_rate = completed / total)
- Derived (margin = profit / revenue)
Reusability
Define metrics once, use everywhere:
- In SQL queries:
SELECT orders.revenue FROM orders - In Python:
layer.query(metrics=["orders.revenue"]) - In other metrics:
sql: "revenue - cost"