Semantic Layers

The metric written once

At one company, 3 teams each publish active customers for the trailing month:

All 3 numbers are correct for their rule, and the business has no way to say which one is the company's. The rules live inside 3 tools, written as SQL by 3 people, and a fourth tool will add a fourth rule. A semantic layer moves the metric definitions out of the tools and into one place that every tool queries.

metrics:
  - name: active_customers
    description: Customers with at least 1 paid order in the trailing 30 days
    table: order_lines_wide
    expression: COUNT(DISTINCT customer_email)
    filter: CAST(ordered_at AS DATE) >= date_add('day', -30, current_date) AND payment_status = 'paid' AND amount_cents > 0
    allowed_breakdowns: [customer_region, category]
  - name: revenue
    table: order_lines_wide
    expression: SUM(amount_cents) / 100.0
    allowed_breakdowns: [customer_region, category, ordered_at.day]
  - name: conversion_rate
    type: ratio
    table: daily_funnel
    numerator: SUM(orders)
    denominator: SUM(sessions)
    allowed_breakdowns: [customer_region, funnel_date.week]

A definition names the metric, the table it reads, the expression that computes it, the filter that scopes it, and the breakdowns a user may group by. When the finance tool asks for active_customers by customer_region, the layer writes the SQL:

SELECT
  customer_region,
  COUNT(DISTINCT customer_email) AS active_customers
FROM order_lines_wide
WHERE CAST(ordered_at AS DATE) >= date_add('day', -30, current_date)
  AND payment_status = 'paid'
  AND amount_cents > 0
GROUP BY 1

The marketing tool asking the same question gets the same SQL and the same 38,950. Changing the rule to 28 days is an edit to 1 line of the definition, and every tool follows on its next query. Nobody searches 40 dashboards for the string 30.

Ratios are summed before they are divided

Conversion rate is orders divided by sessions. Store a daily rate and average it, and the answer is wrong whenever days differ in size:

The 2 answers differ by more than 3 points on 2 days of data. The definition therefore declares conversion_rate as a ratio of 2 sums, and the layer adds up the numerator and the denominator across whatever grouping the user asked for before it divides:

SELECT
  customer_region,
  CAST(SUM(orders) AS DOUBLE) / SUM(sessions) AS conversion_rate
FROM daily_funnel
GROUP BY 1

A new charting tool is adopted and its users want active_customers by category. What happens?

• The tool's authors copy the 30-day rule from the Finance dashboard's SQL into the new tool's query builder.
• The warehouse team creates active_customers_by_category as a new gold table so the tool can read a single column.
• The layer rejects the request because category describes a product and active_customers counts customers.
• The layer checks that category is an allowed breakdown, generates SQL from the one definition, and the tool shows the number Finance sees.

About this lesson

This section is part of the Design Patterns lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.