An Ontology for the Semantic Layer

Semantic layers are usually sold as a place to put metric definitions. Write revenue once, expose it everywhere, stop arguing in meetings.

Then two teams report different revenue anyway, both technically correct, and the argument moves from the dashboard into the definition file.

The definitions were never the hard part.

Metrics are the visible layer, not the load-bearing one

A metric is an aggregation over a set of things. Change what counts as one of those things and the number changes, without the formula changing at all.

sum(amount) is unambiguous. Which amounts is where the disagreement lives:

• Is a cancelled order still an order?
• Is a customer who signed two contracts one customer or two?
• Does an order belong to the date it was placed, paid, or shipped?
• Is a refund a negative order, or a different thing entirely?

None of these are metric questions. They are questions about what an order and a customer are, and whether two records refer to the same real thing. A semantic layer that answers only the metric question inherits every one of these disagreements and gives them nowhere to live.

That missing part is the ontology: not the formulas, but the entities the formulas quantify over.

What an ontology actually commits to

Three things, and each one is a decision somebody has to make explicitly.

Identity. What makes two records the same thing. This is the one most often skipped, and the one that causes double counting for years.

Grain. What one row means. “One row per order” and “one row per order line” are different worlds, and metrics silently break when a model changes from one to the other.

Relationships. How entities connect, and — critically — the cardinality. A fan-out join that nobody declared is the single most common cause of an inflated total.

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER    ||--|{ ORDER_LINE : contains
    ORDER    ||--o{ REFUND : "may be refunded by"
    PRODUCT  ||--o{ ORDER_LINE : "appears in"
    CUSTOMER {
        string customer_id PK
        string account_id "identity: one per legal entity"
    }
    ORDER {
        string order_id PK
        string customer_id FK
        date   placed_at
        date   recognised_at "grain: revenue date"
        string status
    }
    ORDER_LINE {
        string order_line_id PK
        string order_id FK
        numeric amount
    }

Read the diagram as a set of commitments rather than a picture. It says an order has many lines, so any metric summing amount at the order grain is wrong. It says a customer maps to an account, so “number of customers” needs to state which of the two it means. It says revenue has its own date, distinct from when the order was placed.

Each of those is a sentence somebody could have argued about in a meeting. Written down, they stop being re-litigated every quarter.

Making it executable

An ontology that lives in a diagram decays. The version that survives is the one the query engine reads, because then it cannot drift from what the numbers actually do.

entities:
  - name: order
    primary_key: order_id
    grain: "one row per order"
    # Identity is a decision, not a technicality: two rows with the same
    # source id from different systems are the same order only after this
    # rule says so.
    identity:
      resolve_by: [source_system, source_order_id]
    relationships:
      - to: customer
        type: many_to_one
        via: customer_id
      - to: order_line
        type: one_to_many
        via: order_id

  - name: order_line
    primary_key: order_line_id
    grain: "one row per line within an order"

metrics:
  - name: revenue
    description: >-
      Net of refunds, recognised on recognised_at rather than placed_at,
      excluding cancelled orders.
    entity: order_line
    agg: sum
    expr: amount
    filters:
      - "order.status != 'cancelled'"
    time_dimension: order.recognised_at

The entity: order_line line is doing quiet, important work. It declares the grain the sum runs at, which is what stops the engine from fanning out an order-level join and counting the same money twice.

How a question resolves

The value of writing this down is that a question stops being answered by whoever composes the SQL, and starts being answered by the model.

flowchart LR
    Q["revenue by customer,<br/>last quarter"] --> M[metric: revenue]
    M --> G[grain: order_line]
    M --> F["filter: status != cancelled"]
    M --> T[time: recognised_at]
    G --> J{join path}
    J --> O[order]
    O --> C[customer]
    C --> ID["identity: account_id"]
    ID --> R[one row per account]

Two analysts asking the same question now traverse the same path. Not because they agreed, but because the disagreement was settled once, upstream, and encoded.

Where the ontology should live

The hard part is not modelling. It is ownership: an ontology maintained by a central team drifts from the domains it describes, because the people who know what a contract is do not work there.

This is the argument for pushing definitions to the domains that own the data, with the semantic layer as the contract between them rather than the place where meaning is invented.

You do not have to adopt the whole organisational model to take the useful part: the definition belongs where the knowledge is, and the layer’s job is to make it legible everywhere else.

Failure modes worth naming

The pattern across all five: the formula was fine, and the thing underneath it was never written down.

Where to start

Not with a metric catalogue. Start with the five or six entities the business argues about most, and for each one write the three sentences: what makes two of them the same, what one row means, and how it connects to the others.

It is unglamorous, and it takes an afternoon per entity. But those sentences are what every metric quietly depends on, and leaving them unwritten is how a semantic layer ends up being one more place where the same disagreement is stored.