Metrics: governed KPIs as a first-class object¶

Define a KPI once — owner, warehouse, measure, primary and joined tables, dimensions and a time grain — govern its lifecycle, and consume the same governed number everywhere: Insights cards, HERC answers and Data Product assets.

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

Consumed byInsights · HERC · Data Products

Published version1 per metric

Join depth3 hops max

Every querycurrent · previous · delta

When to choose this¶

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 when your tenant requires one.
Surface the KPI on dashboards

A metric-backed Insights card runs the currently-published version on every refresh — no copy-pasted SQL drift.
Let HERC answer business questions

"What was active customers last month?" — HERC routes through the metric agent, runs the metric, and 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 and attach a Data Contract.
Compare periods automatically

Every query supports a compare period (e.g. last 30 days vs the prior 30) and returns the previous value and the change alongside the current number.

When not to reach for Metrics

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.

How a governed metric resolves¶

A metric is defined once over its tables and measure. The single published version is the canonical implementation, and every consumer — Insights cards, HERC, Data Products — resolves to it rather than carrying its own copy of the SQL.

flowchart LR
    DEF["Metric definition<br/>owner · integration · tags"] --> SRC["Primary table<br/>+ joined tables (up to 3 hops)"]
    SRC --> MEAS["Measure<br/>count · sum · avg · custom"]
    MEAS --> DIM["Dimensions<br/>+ time grain"]
    DIM --> VER(["Published version<br/>one per metric"])
    VER --> INS["Insights cards<br/>& dashboards"]
    VER --> HERC["HERC answers"]
    VER --> DP["Data Product asset<br/>then Data Contract"]
    classDef accent fill:#ff8400,stroke:#e67700,color:#ffffff,font-weight:600;
    class VER accent

Figure 1 — Define a metric once over its tables, measure, dimensions and time grain; the single published version is the canonical implementation that Insights, HERC and Data Products all resolve to.

Why teams reach for this

Define a KPI once and every dashboard, HERC answer and Data Product reads the same published version — no copy-pasted SQL drifting between teams. Each version is governed, audited and kept in history, and every query returns current, previous and delta in a single round-trip.

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. Datahub discovers FKs from Unity Catalog and offers them as suggestions (up to three hops).
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, plus fiscal quarter / fiscal year) and compare-period logic.
Compare period	The previous window the metric runs against in the same query (for example the prior 30 days), so you get the current value, the previous value and the change in one round-trip.

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.
Builder	`/metrics/new` (and edit)	Dense workspace with every section visible: Basics, Data source, Measure, Preview, Governance. A sticky action bar; a right rail with a 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.

Setup — what an admin does 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 builder'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. A system default ships seeded so fresh tenants don't hit a fiscal error on first use.
(Optional) Workflow	`/admin/workflows`	A review gate before publishing a new version. Seeded by default.

Lifecycle¶

Every metric version moves through a governed lifecycle. Only an approved version can be published, and only one version is published at a time.

stateDiagram-v2
    direction LR
    state "Under review" as UnderReview
    [*] --> Draft
    Draft --> UnderReview: Submit for review
    UnderReview --> Draft: Reject / withdraw
    UnderReview --> Approved: Reviewer approves
    Approved --> Published: Publish
    Published --> Draft: New version (edit)
    Published --> Archived: Archive
    Archived --> [*]
    classDef accent fill:#ff8400,stroke:#e67700,color:#ffffff,font-weight:600;
    class Published accent

Figure 2 — A version's lifecycle: only an approved version can be published, and exactly one version is published at a time — publishing a new version unpublishes the previous one. Archived metrics leave default lists but stay restorable, and the full history is retained.

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

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

Defining a metric — the builder¶

Work top-to-bottom through five sections in a single dense workspace:

Basics. Name, description, owner, governance tags, fiscal calendar (if any). The slug is auto-suggested as you type the name (clickable to accept; never silently auto-applied).
Data source. Pick integration → catalog → schema → primary table. Datahub discovers the table's primary key and FK chains via Unity Catalog, and offers joined tables along those discovered FK chains (multi-hop, up to three hops).
Measure. Pick a measure type (count / sum / avg / count distinct / custom expression). The platform validates the expression syntactically and executes a LIMIT 1 test.
Preview. The 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.

There are two ways in:

Build it yourselfSuggest with HERC

Start at /metrics/new, pick a single table or add joined tables inline in Data source, and work down to Governance. Best when you already know the table and the measure.

Start at /metrics/suggest. HERC reads a Data Contract or your warehouse and proposes metrics with a rationale; accept the ones that matter, edit, and publish. Each accepted proposal becomes a draft you can refine.

This page is the module overview. The full mechanics — the multi-table join builder, FK discovery, and the parameter contract — 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. Every query resolves the metric's published version, checks a result cache, and returns a stable column contract.

flowchart LR
    REQ["Query request<br/>grain · compare period · filters"] --> RES["Resolve published version<br/>then compiled SQL"]
    RES --> CACHE{"Cached?<br/>SQL + params + integration + caller"}
    CACHE -->|"hit · within TTL"| OUT(["metric_time__month · value<br/>previous · delta"])
    CACHE -->|miss| RUN["Run on Databricks<br/>caller identity · per-user RLS"]
    RUN --> STORE["Cache the result<br/>TTL: version then integration then 24h"]
    STORE --> OUT
    classDef accent fill:#ff8400,stroke:#e67700,color:#ffffff,font-weight:600;
    class OUT accent

Figure 3 — Running a metric: every query resolves the metric's published version, checks a result cache keyed by SQL, parameters, integration and caller, and returns the same stable column contract that Insights cards and HERC depend on.

The stable column contract of a metric-backed Insights card is:

metric_time__<grain>: the grain bucket, named for the grain (for example metric_time__month holding 2026-04-01).
<measure>: the current-period value.
<measure>__previous: the value of the matching period in the compare window, at the metric's grain. A period belongs to a date range when it starts inside that range.
- Year over year: the same day, month, quarter, year, fiscal quarter or fiscal year one year earlier. For weeks it is the week 52 weeks earlier, which starts on the same weekday.
- Month over month, quarter over quarter and week over week: one month, one quarter or one week back.
- Previous period: as many periods of the metric's grain back as start inside your date range. That works the same for days, weeks, months, quarters, years, fiscal quarters and fiscal years.
- Custom compare window: the first period that starts inside your date range pairs with the first period that starts inside the compare window, the second with the second, and so on.
The column is there whenever a compare period is set. It is empty when that period has no data, has no counterpart (such as 29 February a year earlier), or would be shared by two periods.

Two known gaps, until the compare window is aligned to whole periods:
- Previous period and fiscal year over year: the earliest row in the result can be empty. These two comparisons size their compare window by day count rather than by whole periods, and a request that doesn't start exactly on a period boundary can leave that window one day short at the start. For example, a previous-period query for 31 January to 27 February 2021 (one month) needs the whole of January 2021 to compare against, but the compare window it computes only reaches back to 3 January, so the one row in the result has no previous value.
- Weekly, calendar-aligned year over year: the most recent row can be empty when the request ends on a Monday. It can also be empty when the request ends on a Tuesday, for about a year at a stretch: every Tuesday from shortly after one 29 February through the following February, because the year-back comparison spans that leap day for all of them. A calendar year is 52 weeks plus one or two days, so the year-back window used to size the request can land a day short of the most recent week.
- <measure>__delta_pct: the change against that value as a percentage. It divides by the size of the previous value and ignores its sign, (current - previous) * 100 / |previous|, so a rise from -40 to 150 is +475 %. A value of 15 means a rise of 15 %. Empty when there is no previous value or it is zero.

Which surface shows which change: the metric query API and the Metrics module show delta, the absolute difference between the current and the previous value of each period. On a metric-backed Insights card, the <measure>__delta_pct column is a percentage, but a card's KPI badge doesn't read that column: it computes its own change from <measure>__previous, as a percentage or an absolute difference depending on how the card is set up. That's why a badge in percentage mode can show +0.0% against a zero previous value, a case where <measure>__delta_pct itself is empty.

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.

Time grains run from day to year, plus fiscal quarter and fiscal year when a fiscal calendar is seeded. Compare kinds include period-over-period, week-over-week, month-over-month, quarter-over-quarter, year-over-year (calendar- or fiscal-aligned), and a custom window.

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.

How per-caller caching stays safe

The cache key includes the rendered SQL, the parameters, the integration and — for OAuth U2M — the calling user. Two people who see different rows under row-level security get different cache entries, so a cached value is never served across identities. The same cache contract backs Insights 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 builder'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 three hops.	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.
A metric query runs only on a Databricks or Microsoft Fabric connection.	The compiled metric SQL targets those engines.	On a PostgreSQL or Azure SQL connection, use a saved query or SQL card instead.

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
Builder'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.
The builder or Let HERC suggest metrics says "The warehouse is starting" above the catalog, schema and table pickers	The connection's Databricks SQL warehouse had stopped. The pickers ask once, then say so instead of showing an empty list.	Click Try again after a minute; it asks again only for the lists that were waiting.
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.

Arriving from a domain's detail page narrows this list to that domain automatically — a banner names it, with a one-click way back out. See Domains.