Ratios, time grains, and filters

Ratio metrics: compute components, then divide per group

For a ratio like CAC, the compiler resolves it into its two component measures, computes each grouped by the requested dimensions, then divides them group-by-group. Because both components resolve channel to the same values, the division lines up:

# Requesting cac by channel runs, effectively:
spend      = query(["marketing_spend"], ["channel"])   # from marketing_spend
customers  = query(["new_customers"],   ["channel"])   # from customers
cac[chan]  = spend[chan] / customers[chan]             # per-channel division

Run it and you get a real, interpretable result — Organic acquires customers for about $88, while Paid Search costs far more. Same structured request, two different tables, one correct ratio.

Time grains

When a request includes the date dimension with time_grain: month, the compiler wraps the date column in a bucketing expression before grouping:

-- time_grain: month  →
strftime('%Y-%m', orders.order_date) AS dim_0
-- time_grain: quarter →  '2025-Q3', etc.

So "revenue by month" returns one row per month (2024-01, 2024-02, …), and you can watch Percolate's revenue ramp over the two years. The grain is just a wrapper on the same date column — day, week, month, quarter, or year.

Filters

# "revenue by channel, EMEA only"
query(["revenue"], ["channel"],
      filters=[{"field": "region", "op": "=", "value": "EMEA"}])

Filters are structured {field, op, value} objects, translated to parameterized WHERE clauses. Supported ops: =, !=, >, >=, <, <=, in, contains. Parameter binding means user-supplied values can never inject SQL — another reason the LLM never touches raw queries.