Production engineering guide for metric design patterns covering patterns, implementation strategies, and operational best practices.
Metrics are the currency of engineering and data science. A well-designed metric tells a story, enables decisions, and reveals hidden patterns. This reference page details patterns for designing metrics that are reliable, interpretable, and scalable—patterns that emerge when teams move beyond ad-hoc calculations to a shared, principled approach to metric definition and deployment.
When a metric must be computed across millions of users, across multiple product teams, and over long time horizons, the design process must be systematic. Start with a clear, unambiguous definition.
A metric must have a canonical definition that answers: *What is being measured, over what population, and how is it aggregated?*
The definition is expressed as a SQL query with explicit input, output, and context. It is versioned, stored in a central repository, and automatically validated.
-- metrics/active_users_7d.sql
-- Input: events table with columns (user_id, event_type, timestamp, session_id)
-- Output: table with columns (date, active_users_7d)
WITH user_sessions AS (
SELECT
user_id,
DATE(timestamp) AS day,
COUNT(DISTINCT session_id) AS sessions
FROM events
WHERE event_type = 'page_view'
AND timestamp >= '2023-01-01'
GROUP BY user_id, DATE(timestamp)
),
active_users AS (
SELECT
day,
COUNT(DISTINCT user_id) AS active_users_7d
FROM user_sessions
WHERE day >= CURRENT_DATE - INTERVAL '6 days'
GROUP BY day
)
SELECT
day,
active_users_7d
FROM active_users
ORDER BY day DESC;This query is the source of truth. It is executed daily by a cron job, and its output is materialized into a table called metrics.active_users_7d_daily.
Each metric has a version number in its metadata, stored in a metric_definitions table:
CREATE TABLE metric_definitions (
metric_name TEXT PRIMARY KEY,
version INT NOT NULL,
definition TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
owner TEXT NOT NULL,
dependencies TEXT[] NOT NULL,
validation_query TEXT NOT NULL
);A validation_query ensures correctness. For example, for active_users_7d, the validation query checks that the count of users is consistent across two aggregation methods:
-- validation_query for active_users_7d
SELECT
'active_users_7d' AS metric,
(SELECT COUNT(*) FROM events WHERE event_type = 'page_view') AS total_page_views,
(SELECT COUNT(DISTINCT user_id) FROM events WHERE event_type = 'page_view' AND timestamp >= NOW() - INTERVAL '7 days') AS users_in_last_7d,
(SELECT COUNT(DISTINCT user_id) FROM user_sessions WHERE day >= CURRENT_DATE - INTERVAL '6 days') AS users_from_sessions
FROM dual;This query is run nightly. If the counts diverge by more than 1%, the metric is flagged as *stale* in the monitoring dashboard.
A common silent failure: a metric assumes page_view events but overlooks click and scroll events. The system computes active_users_7d using only page_view, but real engagement includes all three.
The fix: define a metric_input table that tracks which event types contribute to each metric.
CREATE TABLE metric_input (
metric_name TEXT REFERENCES metric_definitions(metric_name),
event_type TEXT NOT NULL,
source_table TEXT NOT NULL,
filter_condition TEXT,
PRIMARY KEY (metric_name, event_type)
);A metric is considered *invalid* if any required event type is missing in the source table. The validation job logs:
ERROR: metric active_users_7d missing 12,345 events of type 'click' in events table
- Expected: 98,765
- Found: 86,420
- Missing: 12,345
- Source: events
- Filter: event_type = 'click'This error appears in a centralized alerting system and triggers a data pipeline re-run.
When a base metric is not sufficient, teams build derived metrics—aggregations or transformations over existing metrics.
To compute a metric like *7-day moving average of daily active users*, use a window function with a fixed-size, sliding window.
-- metrics/active_users_7d_moving_avg.sql
WITH daily_active AS (
SELECT
date,
active_users_7d,
LAG(active_users_7d, 6) OVER (ORDER BY date) AS active_users_1d_ago,
LAG(active_users_7d, 1) OVER (ORDER BY date) AS active_users_6d_ago
FROM metrics.active_users_7d_daily
),
moving_avg AS (
SELECT
date,
(active_users_7d + active_users_1d_ago + active_users_2d_ago + ... + active_users_6d_ago) / 7.0 AS avg_7d
FROM daily_active
WHERE date >= CURRENT_DATE - INTERVAL '6 days'
)
SELECT
date,
ROUND(avg_7d, 2) AS avg_7d
FROM moving_avg
ORDER BY date DESC;The key insight: the window is *sliding*, not *fixed*. If the system skips a day, the rolling average is skewed.
A team computes a *30-day rolling engagement score* but forgets to set the window size in the query. The result is inconsistent across days.
The fix: define a window_definition table.
CREATE TABLE window_definitions (
window_name TEXT PRIMARY KEY,
window_size INTERVAL NOT NULL,
window_type TEXT CHECK (window_type IN ('sliding', 'fixed', 'expanding')),
start_offset INTERVAL,
end_offset INTERVAL
);Each derived metric references a window. The system validates that the window size matches the expected duration.
A rate metric measures *events per unit time*. It is defined as:
rate = total_events / total_timeBut the total time is often miscomputed.
The correct approach: use a time-weighted average. For example, *monthly active users* is not just COUNT(DISTINCT user_id) over the month—because users may log in on the first day and never again.
-- metrics/monthly_active_users.sql
WITH user_engagement AS (
SELECT
user_id,
MIN(timestamp) AS first_active,
MAX(timestamp) AS last_active,
COUNT(*) AS total_events
FROM events
WHERE timestamp >= '2023-10-01' AND timestamp < '2023-11-01'
GROUP BY user_id
),
time_weighted AS (
SELECT
user_id,
EXTRACT(EPOCH FROM (last_active - first_active)) AS duration_seconds,
total_events
FROM user_engagement
),
weighted_avg AS (
SELECT
SUM(total_events) AS total_events,
SUM(duration_seconds) AS total_seconds
FROM time_weighted
)
SELECT
total_events / (total_seconds / 3600 / 24 / 30.44) AS monthly_active_users
FROM weighted_avg;This yields a *true* monthly active user count, accounting for user session duration.
A metric computes *daily active users* using DATE(timestamp) but the data comes from multiple time zones. Users in UTC-8 and UTC+2 are not aligned.
The fix: standardize timestamps to UTC, and define a time_zone column in the events table.
The system checks that all metrics use UTC for time boundaries. A validation query flags:
WARNING: metric daily_active_users uses local timestamps but expected UTC
- Found: 2023-10-05 14:30:00-07:00
- Expected: 2023-10-05 21:30:00+00:00
- Timezone mismatch: 7 hours behindWhen a metric must be sliced by user segments, product features, or regions, the design must account for granularity and consistency.
To compute *active users by region*, use a GROUP BY with a dimension table.
-- metrics/active_users_by_region.sql
SELECT
r.region,
COUNT(DISTINCT e.user_id) AS active_users
FROM events e
JOIN users u ON e.user_id = u.user_id
JOIN regions r ON u.region_id = r.region_id
WHERE e.timestamp >= CURRENT_DATE - INTERVAL '7 days'
AND e.event_type = 'page_view'
GROUP BY r.region
ORDER BY active_users DESC;The dimension table regions must be stable and versioned.
A region North America includes Canada and the US in 2022. In 2023, the team adds Mexico. The metric now includes Mexico, but the previous year’s data does not.
The fix: version the dimension. Each region has a valid_from and valid_to date.
CREATE TABLE regions (
region_id SERIAL PRIMARY KEY,
region_name TEXT NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE,
updated_at TIMESTAMPTZ NOT NULL
);The metric computation uses the valid_from and valid_to to assign users to regions at the time of their activity.
For example, a user who joined in January 2023 is assigned to North America from 2023-01-01 to 2023-12-31, even if the region definition changed.
A ratio metric compares two related metrics. It is defined as:
ratio = metric_a / metric_bBut the denominator must be *non-zero*, and the metrics must be aligned in time.
-- metrics/engagement_rate.sql
WITH daily_active AS (
SELECT
DATE(timestamp) AS date,
COUNT(DISTINCT user_id) AS daus
FROM events
WHERE event_type = 'page_view'
GROUP BY DATE(timestamp)
),
daily_sessions AS (
SELECT
DATE(timestamp) AS date,
COUNT(*) AS total_sessions
FROM events
GROUP BY DATE(timestamp)
),
engagement AS (
SELECT
daus.date,
daus.daus,
COALESCE(sessions.total_sessions, 0) AS total_sessions,
(daus.daus * 1.0 / sessions.total_sessions) AS engagement_rate
FROM daily_active daus
LEFT JOIN daily_sessions sessions ON daus.date = sessions.date
)
SELECT
date,
ROUND(engagement_rate, 4) AS engagement_rate
FROM engagement
WHERE total_sessions > 0
ORDER BY date DESC;The key: use LEFT JOIN and COALESCE to handle missing sessions. If total_sessions is zero for a day, the ratio is 0.0, but the metric is *undefined*.
A team computes *conversion rate from sign-up to first purchase* using a 7-day window for sign-up and a 14-day window for purchase. The metric is inconsistent.
The fix: define a metric_window table and a metric_computation table.
CREATE TABLE metric_window (
window_name TEXT PRIMARY KEY,
start_offset INTERVAL,
end_offset INTERVAL
);
CREATE TABLE metric_computation (
metric_name TEXT,
window_name TEXT REFERENCES metric_window(window_name),
base_metric TEXT,
aggregation_function TEXT,
time_alignment TEXT CHECK (time_alignment IN ('exact', 'rolling', 'aligned'))
);The system ensures that all metrics using the same window have consistent time alignment. When a metric is computed, it logs the actual window used.
A metric is not just a query—it is a contract between systems.
Every metric has a contract defined by:
The contract is stored as a JSON document in a shared artifact registry.
{
"metric_name": "active_users_7d",
"version": 2,
"description": "Number of users active over the last 7 days",
"input": {
"table": "events",
"schema": {
"columns": [
{ "name": "user_id", "type": "uuid", "required": true },
{ "name": "event_type", "type": "text", "required": true },
{ "name": "timestamp", "type": "timestamptz", "required": true }
],
"filters": [
{ "column": "event_type", "values": ["page_view", "click", "scroll"] }
]
},
"output": {
"table": "metrics.active_users_7d_daily",
"schema": {
"columns": [
{ "name": "date", "type": "date", "required": true },
{ "name": "active_users_7d", "type": "bigint", "required": true }
]
},
"time_window": "7 days",
"computation_interval": "daily",
"validity": "daily"
},
"validation": {
"query": "SELECT ...",
"thresholds": {
"row_count_change": 0.05,
"value_change": 0.02,
"missing_events": 1000
}
}
}
}The system validates that every execution of the metric satisfies the contract. A failure in any part triggers an alert.
A team changes the events table schema—adding a device_type column—but the metric contract does not require it. The metric runs, but the device_type column is ignored.
The fix: the contract includes a required_columns list. The validation job checks that all required columns exist and have non-null values.
When a metric runs, it logs:
CONTRACT VIOLATION: metric active_users_7d
- Missing required column: device_type in events table
- Expected: 123,456 rows
- Found: 123,456 rows
- But: device_type is NULL in 12,345 rows
- Suggestion: add index on device_type for faster filteringThis leads to a culture of metric governance: teams cannot change data schemas without updating the metric contracts.
This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.
We use cookies for analytics (Google Analytics) and advertising (Google AdSense) to improve your experience and support free content. Privacy Policy