How the compiler turns a request into SQL

The compiler (semantic_layer.py) is small but does the real work. Given a request — metrics, dimensions, filters, time grain — it produces and runs SQL. Understanding its steps demystifies the whole layer.

The algorithm for one measure

1. 1Start at the base table. revenue's base is order_items.
2. 2Collect needed tables. Scan the metric's SQL and filters, plus the chosen dimensions' columns, for table names. Revenue-by-region needs order_items, orders (for the status filter), and customers (for region).
3. 3Resolve joins. Walk the join graph from the base to each needed table, collecting exactly the join clauses along the path — no extra joins.
4. 4Build SELECT. Emit each dimension column (wrapped in a time-grain function if requested) plus the metric's aggregate SQL, GROUP BY the dimensions.
5. 5Add WHERE. Combine the metric's built-in filters (refunds excluded) with any user filters, using parameter binding for safety.
6. 6Run it against SQLite and return rows keyed by their dimension values.

Here's the shape of what it generates for "revenue by region":

SELECT customers.region AS dim_0,
       SUM(order_items.quantity * order_items.unit_price - order_items.discount) AS value
FROM order_items
  JOIN orders ON order_items.order_id = orders.order_id
  JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.status = 'completed'
GROUP BY 1;