How a Metrics Engine Compiles a Request

Module: Query Planning Over a Semantic Graph | Duration: ~13 min | Lesson: 1 of 8

Priya types one line into the BI tool: total_revenue by region. A second later, a bar chart. Simple.

Behind that one line, the metrics engine did something a junior analyst would take an afternoon to do by hand: it found which table has revenue, found which table has region, figured out how to join them without double-counting, applied the metric's baked-in filters, picked the right aggregation, and emitted dialect-correct SQL for Snowflake. Then it ran it.

total_revenue by region isn't a query. It's a request, a high-level statement of intent. Turning it into the multi-join SQL that actually runs is compilation, and understanding that compilation is the difference between using a metrics layer and trusting it.

2. Concept Explanation

A metrics engine is a compiler (made concrete)

Lesson 5 of the first course called the metrics layer "a compiler from semantic intent to SQL". This whole course is that sentence, unpacked. A compiler takes a high-level expression and produces low-level code. A metrics engine takes a request (metric + dimensions + filters + grain) and produces SQL. Same idea, same stages.

The request has a small, fixed shape:

• Metrics: what to measure (total_revenue).
• Group-by dimensions: how to break it down (region).
• Filters: what to limit to (order_date >= '2026-01-01').
• Grain: the time resolution (month), if time is involved.

That's the entire input language. Everything a consumer can ask is some combination of those four. The engine's job is to compile any such combination into correct SQL.

The compilation stages

A metrics engine works in roughly these stages, and the rest of this course is one lesson per hard stage:

1. Resolve the metric. Look up total_revenue: its measure (SUM(amount)), its baked-in filters, its time dimension, its null-handling. (Course 2 covered what's in a definition.)

2. Resolve the dimensions and the join path. region may live in a different table than amount. The engine walks the entity graph to find how to connect them, and there may be more than one way (Lesson 2, Join Path Resolution).

3. Guard against fan-out. If the join multiplies rows, naive aggregation inflates the measure. The engine must aggregate at the right grain or use symmetric aggregates to stay correct (Lessons 3 and 4).

4. Decide where aggregation happens. Push the GROUP BY down to the warehouse when possible; performance depends on it (Lesson 5, Aggregation Pushdown).

5. Resolve time grain. "Monthly revenue" from daily data is fine for additive measures and impossible for some non-additive ones; the engine has to know (Lesson 6).

6. Apply slice semantics. The same metric at different dimension sets is genuinely different SQL with different fan-out behavior (Lesson 7).

7. Emit dialect SQL and (optionally) consult the cache keyed on the full request tuple (Lesson 8).

The output is SQL the warehouse runs, plus the rows. The engine stored no data and drew no chart, it compiled and dispatched.

Why "request, not query" is the key reframe

The reframe that makes everything else click: a consumer expresses what they want, not how to get it. total_revenue by region says nothing about joins, grain, or fan-out. Those are implementation, and the engine owns them. This is exactly what a compiler does, you write a + b, not the register allocation.

This separation is the whole value. Because the consumer states intent and the engine owns implementation:

• The same request compiles correctly no matter which tables changed underneath.
• Two consumers issuing the same request get identical SQL, so identical numbers (no drift).
• The hard parts (join paths, fan-out, pushdown) are solved once, in the engine, not re-solved per analyst.

It also sets up the failure modes you'll spend this course learning: every hard stage above is a place the compile can go subtly wrong, pick the wrong join path, miss a fan-out, mis-resolve a grain, and produce a number that's wrong without erroring. Knowing the stages is knowing where to look when a metric lies.

3. Worked Example

Let's compile total_revenue by region for TheWorldShop, stage by stage, and watch a request become SQL.

The request:

metrics:   [total_revenue]
group_by:  [region]
filters:   [order_date >= '2026-01-01']
grain:     month

The relevant model (from Course 2):

semantic_model:
  name: orders
  entities:
    - { name: order, type: primary, expr: order_id }
    - { name: customer, type: foreign, expr: customer_id }
  measures:
    - { name: amount_sum, agg: sum, expr: amount }
  dimensions:
    - { name: order_date, type: time, grains: [day, month] }

metric:
  name: total_revenue
  measure: amount_sum
  filter: "account_type != 'test'"

# region lives in customers, reached via the customer entity

Stage by stage:

1. Resolve metric: total_revenue = SUM(amount), baked-in filter account_type != 'test', time dimension order_date.
2. Resolve dimension + join path: region is in customers. orders.customer (foreign) connects to customers.customer (primary). One safe many-to-one path.
3. Fan-out guard: many-to-one join doesn't multiply orders rows, so SUM(amount) is safe (no symmetric aggregate needed here).
4. Aggregation pushdown: the GROUP BY region, month goes to the warehouse.
5. Time grain: amount is additive, so monthly = sum of daily; grain resolves cleanly.
6. Emit SQL:

SELECT c.region,
       date_trunc('month', o.order_date) AS month,
       SUM(o.amount) AS total_revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.account_type != 'test'
  AND o.order_date >= '2026-01-01'
GROUP BY 1, 2;

1. Cache: key this result on the tuple (total_revenue, [region], filters, month); a repeat request hits the cache.

One line of intent, seven stages, one correct query. Every stage was a decision the consumer never had to make. And every stage is a lesson in this course, because every one of them has a way to go wrong that the next several lessons will show you.

4. Your Turn

Exercise: A consumer issues the request active_users by product_category, grain=week, filter: region='us'. The measure active_users lives in events; product_category lives in products; region lives in customers.

1. List the compilation stages the engine must perform for this request (use the stage list from this lesson).
2. Identify the two joins the engine must resolve and which entities connect them.
3. Name one stage where this particular request could produce a wrong-but-not-erroring number, and why.

5. Real-World Application

Understanding the compile stages is what separates engineers who can debug a metrics layer from those who can only file tickets against it. When a number looks wrong, the question "which stage failed?" turns a vague "the metric is off" into a targeted investigation: was it the join path, the fan-out, the grain? Every metrics tool exposes the compiled SQL for exactly this reason, dbt's Semantic Layer lets you see the generated query, Cube shows the SQL it produced, and the first move of any senior debugger is to read that SQL and map it back to the stages.

The compiler framing also explains why metrics engines are genuinely hard software, not thin wrappers. Each stage is a known-hard problem (the rest of this course is proof), and they interact, the join path you pick affects fan-out, which affects whether you can push aggregation down, which affects the cache key. This is why "build your own" (Course 1, Lesson 6) is a trap: you're not writing a SQL templater, you're writing a multi-stage compiler with interacting passes, and the easy demo only exercises the first stage.

For an engineer adopting a metrics layer, the practical takeaway is to learn to read the generated SQL fluently and to know which stage produces which part of it. The JOIN clauses come from join-path resolution; any subquery-then-join structure is usually the fan-out guard; the date_trunc is grain resolution; the WHERE blends metric-level and query-level filters. Reading the SQL as the output of named stages makes the whole system legible, and legibility is what lets you trust it.

6. Recap + Bridge

A metrics engine is a compiler: it takes a request (metrics, group-by dimensions, filters, grain) and produces warehouse SQL through a sequence of stages, resolve the metric, resolve dimensions and join paths, guard fan-out, decide pushdown, resolve time grain, apply slice semantics, emit dialect SQL, and consult the cache. The key reframe is "request, not query": the consumer states intent, the engine owns implementation. That separation is why metrics don't drift and where every subtle wrong-number bug hides.

The next several lessons each take one hard stage. First and hardest: join path resolution, where there can be two valid ways to connect two tables, and picking the wrong one silently double-counts.