Module 5Semantic & metrics layers 20 min

Lab: A metrics layer that compiles to SQL

Write metrics.yaml and the 40-line compiler that turns any (metric, dimensions, filters) request into correct SQL — the exact interface an AI agent calls.

Commercial semantic layers are big software, but the core mechanism fits in one lab: definitions in YAML, a compiler that assembles SQL, consumers that never write SQL themselves. You'll build both halves and then answer real questions through the layer — the same interface the Conversational Analytics Agent course hands to an LLM.

First, metrics.yaml. This is the file from the 'Defining metrics' lesson with a third metric added — orders (COUNT(DISTINCT order_id), no filters, certified). Save the whole thing as-is; YAML is indentation-sensitive, so copy it exactly rather than retyping (two-space indents, no tabs):

metrics.yamlyaml

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')"

  orders:
    description: Count of distinct orders, all statuses. The denominator for most order-level rates.
    owner: finance@harborlane.example
    certified: true
    table: fct_order_lines
    measure: "COUNT(DISTINCT order_id)"
    filters: []

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

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

Then the compiler:

metrics.pypython

'''Compile governed metric definitions into SQL. The one door to the numbers.'''
import sys
import yaml
import duckdb

SPEC = yaml.safe_load(open('metrics.yaml'))

def compile_metric(name, by=None, extra_filters=None):
    m = SPEC['metrics'][name]
    dims = by or []
    for d in dims:
        if d not in SPEC['dimensions']:
            raise ValueError(f'unknown dimension: {d}')   # agents get told no

    select = [f"{SPEC['dimensions'][d]} AS {d}" for d in dims]
    select.append(f"{m['measure']} AS {name}")
    joins = sorted({SPEC['joins'][d] for d in dims if d in SPEC.get('joins', {})})
    filters = list(m.get('filters', [])) + list(extra_filters or [])

    sql = 'SELECT ' + ', '.join(select) + ' FROM ' + m['table']
    if joins:
        sql += ' ' + ' '.join(joins)
    if filters:
        sql += ' WHERE ' + ' AND '.join(f'({f})' for f in filters)
    if dims:
        keys = ', '.join(str(i + 1) for i in range(len(dims)))
        sql += f' GROUP BY {keys} ORDER BY {keys}'
    return sql

if __name__ == '__main__':
    metric = sys.argv[1]
    by = sys.argv[2].split(',') if len(sys.argv) > 2 else None
    sql = compile_metric(metric, by)
    print(sql + '\n')
    con = duckdb.connect('harbor.duckdb', read_only=True)
    for row in con.execute(sql).fetchall():
        print(row)

terminal — every consumer asks the same doorbash

python3 metrics.py net_revenue                    # one number, the number
python3 metrics.py net_revenue channel            # by channel
python3 metrics.py net_revenue channel,category   # slice two ways: join appears
python3 metrics.py refund_rate channel            # the tricky SQL, always right
python3 metrics.py net_revenue region             # ValueError: unknown dimension

Each command prints the compiled SQL, a blank line, then the result rows as Python tuples — so you can always tell success from a traceback. Here's the exact shape to expect (your revenue totals will differ slightly if you retuned anything upstream):

expected outputtext

$ python3 metrics.py net_revenue
SELECT SUM(qty * unit_price) AS net_revenue FROM fct_order_lines WHERE (status = 'completed')

(Decimal('NNNNNN.NN'),)          # one row, one number (your exact total)

$ python3 metrics.py net_revenue channel
SELECT channel AS channel, SUM(qty * unit_price) AS net_revenue FROM fct_order_lines WHERE (status = 'completed') GROUP BY 1 ORDER BY 1

('pos', Decimal('NNNNN.NN'))     # one row per channel, alphabetized
('web', Decimal('NNNNN.NN'))

$ python3 metrics.py net_revenue region
ValueError: unknown dimension: region   # a traceback, not rows = the refusal

The nested-quote gotcha

When you add --where support, its value contains single-quoted SQL literals, so wrap the whole argument in double quotes: python3 metrics.py net_revenue channel --where "order_date >= '2026-05-15'". Quote it the other way round — single outside, double inside — and your shell strips the quotes the SQL needs, and DuckDB throws a parser error that looks nothing like the real problem.

Study what just happened in that last command: the layer refused an ungoverned slice instead of guessing. That refusal is the safety property — an agent wired to this interface can be wrong about which metric answers a question, but it cannot invent a definition. Compare the refund_rate channel output against hand-written SQL; then compare how you'd feel maintaining fifty copies of that hand-written SQL across dashboards.

1Add the orders metric and a zip dimension (via dim_customer join on customer_key — your Module 4 work is now a governed slice). Verify net_revenue by zip runs.
2Add extra_filters support end-to-end: python3 metrics.py net_revenue channel --where "order_date >= '2026-05-15'". Note the design question you just faced: user filters add to mandatory filters; they can never remove them.
3Write weekly_report.py: five certified metrics, computed through the layer, printed as Harbor Lane's Monday numbers. This script replaces the ad-hoc SQL from Module 2's lab — delete that query with ceremony.
4Stretch: add a --json flag emitting {metric, description, owner, certified, sql, rows}. That envelope — numbers traveling with their definition and provenance — is exactly what an analytics agent should return to users, and the same pattern the Conversational Analytics Agent course builds on (with its own metrics file).

Problem set 5

The problem set is definitional combat: six ambiguous metric requests from Harbor Lane stakeholders ('conversion rate', 'average order value including or excluding refunds?', 'active customer'). You write the five-part definition for each, document the edge-case rulings, and mark which you'd certify immediately versus send to an owner for signature.

PreviousMetric governance Next Capstone: Design the foundation