Metrics: governed KPIs as a first-class object¶

The Metrics module is where you define your organisation's KPIs as governed, reusable objects. A metric has an owner, a description, an integration (which warehouse), a measure (e.g. count(*), sum(amount)), a primary entity table, optional joined tables, dimensions you can group by, and a time-grain semantic. Once defined and published, the same metric powers cards on Insights dashboards, answers HERC's "what was revenue last month?", and shows up as a Data Product asset that contracts can link to.

It's the platform's antidote to "every team's dashboard says a slightly different number" — define a KPI once, govern its lifecycle, consume it everywhere.

This page is the module overview. The deeper guide on how to define a metric — including the multi-table join builder, FK discovery, and the parameter contract — lives at Defining metrics & joining tables.

When to choose this¶

Reach for Metrics when you want to:

Standardise an organisational KPI. Active Customers, Net Revenue, Churn Rate, Average Handle Time — define once, reference everywhere.
Govern the KPI's lifecycle. Draft → under review → approved → published → archived. With a workflow gate for publishing if your tenant requires one.
Surface the KPI on dashboards. A metric-backed Insights card runs the metric's currently-published version on every refresh — no copy-paste SQL drift.
Let HERC answer business questions. "What was active customers last month?" — HERC routes through the metric agent, runs the metric, returns the number with a link.
Wrap the KPI in a Data Product. A metric is a registerable asset; bundle it in a Data Product, attach a Data Contract.
Compare periods automatically. Every metric query supports a compare period (e.g. last 30 d vs prior 30 d) and returns previous + delta % alongside the current value.

You do not need this module for:

Ad-hoc SQL queries (use Insights directly with a data_source='sql' card).
Free-form analytical exploration (use a notebook).
Operational metrics that don't need governance (also fine to build directly in Insights).

What Metrics looks like¶

Surface	Where	What you see
List	`/metrics`	DataTable + filters by status (Draft / Under review / Approved / Archived), domain, owner, tags. Search across name + description.
Detail	`/metrics/{id}`	Definition, version history, current published version, lineage (which Insights cards / dashboards / contracts use it), audit log, governance metadata. Submit for review, Publish, Archive.
Wizard	`/metrics/new` (and edit)	Five-section dense workspace: Basics, Data source, Measure, Preview, Governance. Sub-nav at the top; right rail shows live capabilities preview, query preview, and the Ask AI sidebar.
Suggestion flow	`/metrics/suggest`	HERC reads a Data Contract or your warehouse and suggests metrics with rationale; accept the ones that matter, edit, publish.

Concepts¶

Concept	What it is
Metric definition	The metadata: name, description, owner, integration, governance tags, lifecycle.
Metric version	A specific version of the measure semantics — table, joins, measure expression, dimensions, time column, time grain. Versions auto-increment.
Published version	The single version currently considered the canonical implementation. Insights / HERC / contracts always reach for this.
Measure	The SQL expression that produces the number — `count(*)`, `sum(amount)`, `count(distinct id)`.
Primary table	The entity table the measure is computed over. The metric's grain is determined by this table's primary key.
Joined table	Additional tables joined to the primary via FK relationships. The platform discovers FKs from Unity Catalog and offers them as suggestions.
Dimensions	Columns you can group by — typically from the primary table or its joined neighbours.
Time column	The date / timestamp column the metric uses for time grain (day / week / month / quarter / year) and compare-period logic.
Compare period	The previous window the metric runs against in the same query (e.g. prior 30 days) so you get current + previous + delta % in one round-trip.

Setup — what an admin needs to do once¶

Prereq	Where	Why
Databricks integration	`/admin/integrations` → Databricks	The warehouse the metric runs against. OAuth U2M recommended for per-user RLS.
AI provider key	`/admin/integrations` → AI Provider Keys	Powers the metric suggestion flow and the wizard's Ask AI sidebar.
Roles	`/rolegroups`	`metric.read` to view + run; `metric.steward` for stewards. Granular write roles depending on phase.
Fiscal calendars	`/admin/fiscal-calendars`	If your organisation uses fiscal periods (e.g. fiscal year starts April), seed them so metric queries can group by fiscal grain.
(Optional) Workflow	`/admin/workflows`	A review gate before publishing a new version. Seeded by default.

Lifecycle¶

draft  ─►  under_review  ─►  approved  ─►  published
                                ▲              │
                                │              ▼
                                └─ archived ◄──┘

Draft — author edits freely. No external consumer sees changes.
Under review — submission creates a Tasks inbox item for the reviewer role group. Reviewer approves or rejects with a comment.
Approved — version is signed off; the author can publish.
Published — the version is the live one; Insights cards bound to this metric pick it up automatically.
Archived — the metric is hidden from default lists and pickers; existing references degrade gracefully (Insights cards show a friendly empty state with the metric name).

Only one version of a metric is published at a time. Publishing v3 unpublishes v2.

Defining a metric — the wizard¶

Five sections in a single dense workspace:

Basics. Name, description, owner, governance tags, fiscal calendar (if any). Slug is auto-suggested as you type the name (clickable to accept; never silently auto-applied).
Data source. Pick integration → catalog → schema → primary table. The platform discovers the table's primary key and FK chains via Unity Catalog. Add joined tables via the discovered FK chains (multi-hop, max depth 3).
Measure. Pick a measure type (count / sum / avg / count distinct / custom expression). The platform validates the expression syntactically + executes a LIMIT 1 test.
Preview. Right rail shows the rendered SQL, a sample run, a capabilities preview (which grains / compare-periods are supported given the chosen time column), and the Ask AI sidebar for refinement.
Governance. Domain, department, team, classification, sensitivity flag.

Save → version 1 (or N+1 if editing an existing definition). Submit for review → publish.

The full mechanics (FK discovery, multi-table joins, parameter expansion) live in Defining metrics & joining tables.

Running a metric¶

The query API takes:

the version,
a parameters dict (time grain, compare period, dimension filters, dimension grouping),
a Databricks integration and a caller user.

It returns a stable column contract:

metric_time — the grain bucket (e.g. 2026-04-01).
<measure> — the current-period value.
<measure>__previous — the previous-period value (when compare-period is set).
<measure>__delta_pct — % change.

Plus any additional grouping columns. This contract is what Insights cards and HERC depend on, so unknown columns pass through verbatim — future engine columns don't break consumers.

Result caching¶

Successful runs are cached in PostgreSQL:

The cache key includes the rendered SQL, parameters, integration, and (for OAuth U2M) the caller — so per-user RLS is honoured and there's no cross-user leakage.
TTL cascade: per-version override → per-integration default → 24 h.
Bypass with the Refresh chip on Insights cards; admins can purge the global cache from /admin/cache.

The same cache contract is used by Insights for data_source='sql' cards — see Cached query results.

How HERC uses metrics¶

The metric agent is one of HERC's specialist agents:

list_metrics — returns metrics matching a query string.
get_metric — returns a metric's full definition.
run_metric — runs a metric for a grain / period / compare period.
suggest_metrics — given a contract or a table, propose new metric definitions.
publish_metric_from_draft — escape hatch for the Insights agent to bind a draft metric to a card.

When you ask HERC "what was revenue last month?", this agent searches metrics → runs the right one → returns the number + a link. The metric's lineage badge appears on the response.

Limitations¶

Limit	Why	Workaround
One published version per metric.	Single source of truth.	Archive the old version (kept in history); publish the new one.
The wizard's preview is one cell — not a full chart.	Dashboards are for charts.	Drop the metric onto an Insights card to chart it.
Joined tables capped at depth 3.	Sanity.	Refactor into a view in Databricks, then build the metric on the view.
Custom-expression measures are validated syntactically; not all errors surface until execution.	Some errors only happen at runtime.	The card / HERC will surface the error message with a remediation hint.
Fiscal grains require a seeded fiscal calendar.	The platform doesn't guess.	`/admin/fiscal-calendars` → seed your fiscal years.
`metric_ref` cards on Insights show no preview in the modal.	Compiled SQL is server-only.	The card runs as soon as you save and shows up on the dashboard.

Audit & compliance¶

Question a CISO might ask	Where to look
"Who edited this metric?"	Detail page → version history with author + comment per version. Audit log: `/admin/audit-log` filtered by `metric`.
"Who approved this version?"	Workflow progress card; reviewer + comment.
"What's currently using this metric?"	Detail page → Lineage tab: Insights cards / dashboards / Data Products / Contracts.
"Did this metric run under the user's identity?"	OAuth U2M: yes (per-caller token + caller-user-keyed cache). PAT / SP: under the integration identity.
"Was the SQL ever something destructive?"	Read-only validator blocks DDL / DML at the service layer; every run is logged with the actual SQL.

Troubleshooting¶

Symptom	Likely cause	Fix
Wizard's primary-table dropdown is empty	Catalog not browsable, or Unity Catalog `system.information_schema` not enabled (412 `uc_system_schema_not_enabled`).	Have an admin enable system schemas.
FK suggestions are empty	Unity Catalog has no declared constraints on the table.	Declare PK / FK in Unity Catalog, or build joins manually in a custom expression.
Publish blocked with "no reviewer in role group"	Reviewer role group has no members.	Add members; or cancel and re-submit with a different reviewer.
Insights card bound to this metric shows `METRIC_NOT_FOUND`	The metric was archived.	Restore from `/metrics` archived view, or re-bind the card.
Insights card shows `COMPILED_SQL_NOT_READY`	Recently published; the platform is compiling.	Refresh in a few seconds.
Insights card shows `METRIC_PARAMS_MISSING`	The card's saved params don't include a required dimension.	Edit the card → metric params; pick the missing dimension.
Run errors with "column not found"	Source table changed.	Edit the metric → re-validate; bump version.