Defining metrics

A metric definition is a small contract with five parts. Skimp on any of them and the ambiguity you evicted returns through the side door:

• Name — one canonical name, no synonyms in the layer itself. If marketing says 'sales' and finance says 'net revenue' for the same thing, pick one; aliases can point at it, but the definition has one address.
• Description — one plain-language sentence a non-analyst can read. This doubles as documentation for AI agents: it's the text an LLM sees when deciding which metric answers the user's question. Write it for that reader.
• Measure + table — the SQL aggregate and the table it runs against: SUM(qty * unit_price) on fct_order_lines. The measure references the modeled mart — never raw or staging.
• Mandatory filters — the business rules baked in: status = 'completed'. These are the part people forget when hand-writing SQL, which is precisely why they live in the definition.
• Owner + certification — a named human accountable for the definition, and a certified flag consumers can trust. An unowned metric is a rumor with a YAML file.

Edge cases are the definition

The five-part skeleton is easy. Real definitional work is deciding the edges before they're disputes: Do refunds subtract from revenue in the month of sale or the month of refund? Is a cancelled-then-reordered purchase one order or two? Does 'active customer' mean purchased-in-90-days or logged-in-in-90-days? None of these have technically correct answers — they have decided answers. The metric definition is where the decision is recorded, and the description field is where its reasoning lives.

version: 1
metrics:
  net_revenue:
    description: Revenue from completed order lines. Excludes cancelled and refunded orders entirely.
    owner: finance@harborlane.example
    certified: true
    table: fct_order_lines
    measure: "SUM(qty * unit_price)"
    filters:
      - "status = 'completed'"

  refund_rate:
    description: Share of orders refunded, by count, among completed + refunded orders.
    owner: finance@harborlane.example
    certified: false        # proposed; finance hasn't signed off
    table: fct_order_lines
    measure: "COUNT(DISTINCT CASE WHEN status = 'refunded' THEN order_id END) * 1.0 / COUNT(DISTINCT order_id)"
    filters:
      - "status IN ('completed', 'refunded')"

dimensions:
  order_date: "order_date"
  channel: "channel"
  category: "p.category"

joins:
  category: "JOIN dim_product p USING (sku)"

Notice what the format achieves: the refund-rate measure is genuinely tricky SQL (DISTINCT CASE WHEN inside a ratio, with a scoping filter) — exactly the kind an agent or analyst would botch freelancing. Written once, reviewed by finance, it's correct everywhere it's used, forever, and certified: false tells every consumer it's still a proposal.