Reference

Metric Layer Design

Use this page as a reference for building a governed metric layer on top of modeled data.

Goal:

centralize business metric definitions
reduce KPI drift across dashboards
support versioning and controlled change management

What a Metric Layer Solves #

Without a metric layer, teams define the same KPI differently in many tools.
A metric layer standardizes:

formula logic
allowed dimensions
filter semantics
grain assumptions
ownership and SLA metadata

Metric Contract Template #

For each metric define:

metric name and business meaning
canonical SQL expression
base grain and supported rollups
numerator/denominator for ratio metrics
exclusion and null-handling policy
owner, review cadence, and version history

Reference SQL View for Metric Logic #

sql

CREATE OR REPLACE VIEW metric_base_order AS
SELECT
  order_date,
  customer_segment,
  SUM(net_amount) AS revenue,
  SUM(cost_amount) AS cost,
  SUM(net_amount - cost_amount) AS gross_margin,
  COUNT(DISTINCT order_id) AS order_count
FROM mart_order_performance
GROUP BY order_date, customer_segment;

sql

CREATE OR REPLACE VIEW metric_kpi_daily AS
SELECT
  order_date,
  customer_segment,
  revenue,
  gross_margin,
  gross_margin / NULLIF(revenue, 0) AS gross_margin_pct,
  revenue / NULLIF(order_count, 0) AS avg_order_value
FROM metric_base_order;

Versioning Strategy #

Treat metric changes as versioned contracts:

maintain v1, v2 during migration windows
publish breaking changes with deprecation dates
compare historical impact before switching default version

Validation Checks #

sql

-- 1) KPI denominator safety
SELECT COUNT(*) AS bad_rows
FROM metric_kpi_daily
WHERE revenue = 0 AND gross_margin_pct IS NOT NULL;

-- 2) aggregate reconciliation
WITH base AS (
  SELECT ROUND(SUM(net_amount), 2) AS source_revenue
  FROM mart_order_performance
),
metric AS (
  SELECT ROUND(SUM(revenue), 2) AS metric_revenue
  FROM metric_base_order
)
SELECT source_revenue, metric_revenue
FROM base, metric
WHERE source_revenue <> metric_revenue;

Operating Model #

Assign explicit metric owners by domain.
Enforce review/approval workflow for definition changes.
Track downstream consumers for impact analysis.
Keep a metric catalog with examples and caveats.

Senior Engineer Playbook #

Query-plan diagnostics: inspect expensive metric definitions for repeated scans and overuse of DISTINCT.
Failure postmortem pattern: root-cause KPI drift by tracing semantic version changes and ungoverned dashboard formulas.
Migration playbook: move metric logic from BI tools into centralized semantic contracts with staged version rollout.

Interactive SQL Check #

Run this metric-contract query to compute product-line loss ratio from policy-level pre-aggregates.

Loading SQL editor...

Tip

Metric layer quality is measured by consistency under change, not by number of metrics published.