Analytics

Metrics Layer Starter

Define each metric once, with an owner, and point every report at that definition.

GIST stepGroundIterate
Download the PDF

Get the Metrics Layer Starter overview

A short PDF to share with your team. It covers what is in the package, how it works, and a worked example.

The problem

Every report has its own version of net sales.

Each dashboard rewrites the same metric in its own SQL. Teams bring different numbers to the same meeting, the meeting turns into a reconciliation, and nobody can say which figure is right or who owns it.

What's included

One definition per metric, with a name next to it.

01

Metric files

A short YAML file per metric that says what it counts, who owns it, which dimensions it can be sliced by, and how it is tested.

02

SQL compiler and BI exports

Compiles simple, ratio, and derived metrics to SQLite and ANSI SQL. Exports MetricFlow YAML for the dbt Semantic Layer and DAX measures for Power BI.

03

Certification

Someone other than the owner approves each metric. The approval is recorded against a fingerprint of the definition, and a catalog lists the certified metrics for business users.

04

Reconciliation tests

Every metric is checked against the system finance or operations already relies on.

How it works

How a metric gets certified.

  1. Models

    Say where the data comes from, which column holds the date, and which dimensions and measures exist.

  2. Metrics

    Simple, ratio, or derived metrics built on those measures. Ratios are computed after aggregation.

  3. Slicing rules

    A metric can only be sliced by its allowed dimensions, and never below the grain it was defined at.

  4. Tests

    Not null, range, dimensions that sum to the total, and a reconciliation against a source figure.

  5. Certification

    Granted only when every test passes, every dependency is certified, and the certifier is someone other than the owner.

  6. Change control

    Any edit changes the fingerprint and flags the metric until it is certified again.

Worked example

An order warehouse checked against the general ledger.

Gross sales, refunds, net sales, and order counts are each defined once and tested against the ledger.

What the tests show
  • Gross sales, refunds, and order counts pass and are certified
  • Net sales for August is 1.85% below the ledger
  • The cause is a manual journal that has not been reversed yet
Result

Net sales stays uncertified until someone explains the gap, so it cannot reach a board pack before it can be defended.

Where it fits

What it runs on, and where it stops.

Works with

  • Snowflake, Databricks SQL, PostgreSQL, and DuckDB through ANSI SQL
  • dbt Semantic Layer through the MetricFlow export
  • Power BI through DAX measures

Assumptions and limits

  • The compiler handles single-table models. Joins across models go through the dbt export
  • Small ANSI syntax differences get checked on your warehouse
  • Distinct counts do not add up across dimensions, and the tests allow for that
Ask Dex

Ask about the Metrics Layer Starter

Dex answers from our own pages and links to where it found the answer.

Which number starts the argument in your meetings?

Tell us. We will show you what its definition file and reconciliation test would look like.