Plan a metric request¶

You have compiled specs and a built mart, and you want SQL that answers "revenue by month for these countries" — correct at the requested grain, refused when it cannot be. The planner turns a structured MetricRequest into a QueryPlan: SQL text plus metadata, rendered by an embedded MetricFlow that never executes anything. For a runnable end-to-end script see examples/quickstart/run.py; the wide-marts page explains why requests are served from marts with no join before an aggregate.

Build the IR and the planner¶

The planner works on the compiled IR, not on specs, and gets its manifests through a hydrator — an in-process LRU that rebuilds (or loads caller-supplied bytes) whenever the specs, the bloomery version, or the MetricFlow version change:

from bloomery import LruManifestHydrator, MetricFlowPlanner, build_project_ir
from bloomery.naming import DefaultNaming

ir = build_project_ir(project, catalog=catalog)

naming = DefaultNaming()
planner = MetricFlowPlanner(LruManifestHydrator(naming), naming=naming)

Two wiring rules:

• naming must be the policy the artifacts were emitted with — it shapes the gold relation names in the SQL, the refusals, and the explanations.
• Build the planner once and reuse it: the hydrator's cache is what makes repeated planning cheap. If you persist manifests yourself, pass fetch_l2= to the hydrator; a None return falls back to rebuilding from the IR. Store what you return under the key you were handed — bytes are cached under that key unexamined, so a loosely keyed store serves another project's manifest until eviction.
• In a service, "reuse it" means across threads, and both objects are safe to share. What stays yours: fetch_l2 is called concurrently, so it must be thread-safe, and concurrent misses of the same cold key each call it — duplicate work on a cold key, never a wrong answer. See Thread safety for the full contract and what it does not promise.

Make a request¶

A request is structured data — metrics, dimensions, typed filters, a time grain, ordering, a limit. There is no SQL string anywhere a caller could inject into:

from bloomery import MetricRequest, Op, OrderSpec, Predicate, TimeGrain

request = MetricRequest(
    metrics=("revenue",),
    dimensions=("country", "ordered_day"),
    filters=(Predicate(dimension="country", op=Op.IN, values=("FR", "DE")),),
    time_grain=TimeGrain.MONTH,
    order_by=(OrderSpec(field="revenue", direction="desc"),),
    limit=100,
)
plan = planner.plan(ir, request, dialect="duckdb")

Structural rules, enforced at construction with InvalidRequest: at least one metric, no duplicates, order_by only over requested members, limit >= 1, and filter operator/value arity coherence. Float filter values are accepted and normalized to Decimal(str(value)) at the boundary — no float ever reaches the rendered SQL, and non-finite values (NaN/Infinity, float or string form) are refused with InvalidLiteral. time_grain re-buckets every date-role dimension in the request — here ordered_day with TimeGrain.MONTH groups by the ordered_month bucket column.

Filters are CNF clauses¶

filters is an implicit AND across clauses; each clause is a single Predicate or one AnyOf disjunction group — exactly one level of OR, which covers every filter a BI UI builds ("carrier in [DHL, UPS] and (region = EU or region = UK)"):

from bloomery import AnyOf, Op, Predicate

filters = (
    Predicate("carrier", Op.IN, ("DHL", "UPS")),
    AnyOf((Predicate("region", Op.EQ, ("EU",)), Predicate("region", Op.EQ, ("UK",)))),
)

The operators are eq, ne, gt, gte, lt, lte, in, not_in, is_null, like, and ilike. Three worth knowing:

• is_null takes exactly one bool — (True,) renders IS NULL, (False,) renders IS NOT NULL.
• like/ilike (case-sensitive / case-insensitive) take one or more SQL LIKE patterns with OR semantics. Wildcards are yours: write %needle% for a substring match, and escape literal %/_/\ as \%/\_/\\ (a pattern ending in an unpaired \ is refused). There is no auto-wrapping.
• Ranges compose from gte + lte as two clauses — there is no between operator.

Each clause renders as its own WHERE constraint; an AnyOf group is always parenthesized, so a disjunction can never leak past a row policy.

The JSON front door¶

If your filters arrive as a Mongo-flavoured JSON document ($and/$or/$not, field maps, $eq $neq $gt $gte $lt $lte $in $nin $null $like $ilike), bloomery.planner.parse_filter_json turns it into clauses, normalizing before refusing — De Morgan push-down, complement inversion ($not $eq → ne), then CNF distribution with a clause cap:

from bloomery.planner import parse_filter_json

clauses = parse_filter_json(
    {"carrier": {"$in": ["DHL", "UPS"]}, "$or": [{"region": "EU"}, {"region": "UK"}]}
)
request = MetricRequest(metrics=("revenue",), filters=clauses)

Scalars are the $eq shortcut, arrays the $in shortcut, and null means is_null: true. What cannot cross is refused after normalization with a typed UnsupportedFilter carrying a stable .reason code from the closed list bloomery.planner.KNOWN_UNSUPPORTED — a reviewed gap, never drift. A malformed document is a different class: a non-mapping payload, a field map of the wrong shape, an unknown $op, an operand of the wrong type — these are InvalidRequest, because a schema error is not a reviewed refusal and never enters the closed list. parse_sort_json and parse_page_json ship alongside and split the same way (sort is direction-only; pagination is limit-only — a well-formed non-default nulls placement, a non-zero offset, and cursors are refused rather than silently dropped, while a malformed nulls, limit, or offset value is InvalidRequest).

What comes back¶

QueryPlan is the planner's whole product:

The explanation is generated from the plan, never from a model — every number ships with how it was computed:

revenue
  mart:     gold.mart_orders (grain: order)
  measure:  revenue = SUM(amount)
            [additive — SUM]
  filters:  country in ('FR', 'DE')
  policy:   not applied

Reading the derivation back¶

plan.semantic is always there. It is a short pipeline of nodes, and render() prints it in execution order:

Scan(sales @ sale)
  -> Filter(none)
    -> Aggregate(revenue : sale -> sale by sold_day)
      -> Offset(revenue_yoy.prior = revenue 1 year earlier over sold_day)
        -> Compute(revenue_yoy = current - prior where current = revenue, prior = revenue)
          -> Project(sold_day, revenue_yoy)

Each node that makes a semantic claim carries a proof, and a plan whose claiming nodes do not is invalid rather than merely unexplained — so a plan you can read is one whose every claim closed. plan.semantic.proofs walks them, branches included, and plan.semantic.serialize() gives canonical JSON if you want to keep one.

It is not what produced the SQL. bloomery decides what to compute and states it; the query is rendered alongside. Read the plan as evidence about the answer, not as the machinery that generated it.

Refusals¶

The planner answers correctly or refuses with a typed error — never guesses. All planner errors subclass PlannerError; the first failure wins (they are not batched). The three you will design UX around:

UnknownMember — a name that does not exist, with a closest match:

unknown metric 'revenu'; did you mean 'revenue'?

UnreachableAtGrain — the request cannot be served at the grain it asks for:

metric 'order_count' (grain: order) is served by no mart — no mart lists it as a measure.
  Define a mart at grain 'order' carrying it.

Measures that span grains are not automatically a refusal. Where every requested dimension is the same dimension on every mart involved, the planner aggregates each measure on its own mart and joins the results — marts then names each one and the explanation prints a branch: line per mart:

shipping_count, line_discount
  branch:   gold.mart_order_items (grain: order_item)
  branch:   gold.mart_orders (grain: order)
  measure:  shipping_count = COUNT(order_id)
            [additive — COUNT]
  measure:  line_discount = SUM(discount)
            [additive — SUM]
  filters:  (none)
  policy:   not applied

Filters, the row policy, order_by and limit all work on such a request. A filter and the policy are placed on every branch in that branch's own spelling of the dimension; one a branch cannot evaluate refuses the whole request, because a restriction applied to half the answer returns a number rather than an error. The refusal names the filter or policy and the marts that cannot apply it:

filter `region = 'EU'` does not reach every branch: customers cannot apply it.

The ordering and the limit apply to the joined result, so a limit is a limit on rows you get back and not on rows one branch contributed.

A metric's own filter: is admitted: it restricts only its own measure, on the branch that owns it, and that measure's line in the explanation says so — [additive — COUNT (restricted to region eq ['EU'])]. A group the filter leaves empty reads NULL for that metric while other branches still report it.

A metric whose components span the marts — a ratio, or a derived: expression — is computed once above the join, over operands each branch aggregated, with its inputs' own filters named on its column. Three shapes stay refused: a component that itself needs two marts, a derived input read at a time offset, and a derived: metric whose inputs are restricted differently (RatioOperandsDisagree, R019 operands_disagree, naming each input and its restriction) — refused on a single mart too.

Where it cannot prove a request safe, it refuses with the conflict named — including the case where both marts carry a column of the same name and mean different things by it. See wide-marts.

AmbiguousDimension — an unqualified bucket where the mart has several date roles:

'month' has roles ['ordered', 'shipped']. Use 'ordered_month' or 'shipped_month'.

The remaining refusals: InvalidRequest (structural problems, raised at request construction or planning), FilterTypeMismatch (a filter value whose type contradicts the dimension's column type — refused before any SQL renders), and the UnsupportedFilter family — the closed query-vocabulary list (set relations, hierarchy operators, $regex, over-cap CNF expansions, non-invertible negations, non-finite literals, non-default sort-nulls, offset/cursor paging), each with a stable .reason in bloomery.planner.KNOWN_UNSUPPORTED. See Errors.

Scope rows with a policy¶

Row-level scoping is a typed filter, not a predicate string. Deciding whose policy applies is your upstream concern; the planner takes the value and renders it through the same escaping pipeline as every other filter, prepended to the user's filters:

from bloomery import Op, RowPolicy

plan = planner.plan(
    ir,
    MetricRequest(metrics=("revenue",), dimensions=("ordered_month",)),
    dialect="duckdb",
    policy=RowPolicy(dimension="country", op=Op.EQ, value="FR"),
)

The rendered predicate reaches every scan of the mart relation, and explanation.render() reports policy: applied — the plan itself records that scoping happened.

Execute the SQL yourself¶

The planner never sees a connection — execution is your side of the line. plan.sql runs directly on the dialect you asked for; on DuckDB, with the mart table built (by the emitted SQLMesh models in real life, by hand here):

import duckdb

con = duckdb.connect()
con.execute("CREATE SCHEMA gold")
con.execute("""
    CREATE TABLE gold.mart_orders AS
    SELECT * FROM (VALUES
        ('FR', DECIMAL '10.00', DATE '2024-01-05'),
        ('DE', DECIMAL '20.00', DATE '2024-01-20'),
        ('FR', DECIMAL '5.00',  DATE '2024-02-01')
    ) AS t(country, amount, ordered_day)
""")
rows = con.execute(plan.sql).fetchall()

Pair the rows with plan.columns for names and types, and cache results under plan.fingerprint — identical requests over identical specs produce identical SQL, so the fingerprint is a sound cache key.

Notes¶

• limit is clamped to the planner's max_limit (default 50 000) with a warning on the plan, and default_limit applies when a request carries none.
• TimeGrain.HOUR is accepted by the contract but refused at coverage — marts expand date roles to day–year buckets only.
• A non-additive ratio metric is planned from its additive components on the mart that carries both; it is never read from storage. See guardrails for why.
• When specs change shape, re-run build_project_ir and hand the new IR to the same planner — the hydrator treats it as a new cache key automatically.