Skip to content

Quickstart

By the end of this page you have compiled a five-spec project into runnable SQLMesh models on disk, read the SQL bloomery generated, and planned one metric request into SQL — all without touching a database. The whole run lives in the repository as examples/quickstart/, so you can run it first and read it second.

Prerequisites

  • bloomery installed, or the repository cloned with uv sync run.
  • If you cloned the repository, the complete example is one command from the repo root:
uv run python examples/quickstart/run.py

The steps below build up exactly what that script does.

Step 1: Author the specs

The project is one order entity, described by five YAML documents. First the catalog — the vertical-level domain knowledge: a canonical amount field with monetary metadata, a reusable revenue template, and the date dimension bloomery owns:

# The vertical-level catalog: canonical fields with monetary metadata, a
# reusable metric template, and the vertical-owned date dimension that becomes
# the emitted gold.dim_date calendar.
catalog_version: 1
vertical: ecom_retail

canonical_fields:
  amount:
    entity: order
    type: decimal(12,2)
    unit: currency
    tax_basis: net
    recipes:
      - {id: direct, requires: [amount]}

metric_templates:
  revenue:
    requires: [amount]
    grain: order
    additivity: additive
    agg: sum
    expr: "amount"

date_dimension:
  name: dim_date
  grain: day
  start_year: 2020
  end_year: 2030

Then the entity model. canonical: amount links the field to the catalog, which is what makes the revenue template reachable for this project:

# The tenant's entity model: one order entity. `canonical: amount` links the
# field back to the catalog, which is what makes the `revenue` template
# reachable for this project.
spec_version: 1
entities:
  order:
    grain: one row per order
    key: [order_id]
    fields:
      order_id: {type: string, required: true}
      customer_id: {type: string}
      amount: {type: "decimal(12,2)", canonical: amount}
      order_date: {type: date, assert: {not_null: true}}

The mapping says how the bronze source becomes the entity — key lowering plus one transform chain per field:

# How the bronze source becomes the order entity: key lowering plus one
# transform chain per field, drawn from the closed transform whitelist.
mapping_version: 1
source: shop__orders
target: order
key:
  order_id: {from: "$.id", transform: [to_string]}
fields:
  customer_id: {from: "$.customer_id", transform: [to_string]}
  amount: {from: "$.amount", transform: [{to_decimal: [12, 2]}]}
  order_date: {from: "$.created_at", transform: [{parse_date: ISO8601}]}

The metrics — one instantiated from the catalog template, one inline:

# The project's metrics: one instantiated from the catalog template, one
# fully inline. Every metric declares grain and additivity.
metrics_version: 1
metrics:
  revenue:
    template: revenue
  order_count:
    grain: order
    additivity: additive
    agg: count
    expr: "order_id"

And the mart — the wide gold table the planner will serve requests from. The date role expands order_date into ordered_day … ordered_year bucket columns:

# The gold layer: one wide mart at order grain. The date role expands
# order_date into ordered_day .. ordered_year bucket columns — the dimensions
# the planner serves requests from.
marts_version: 1
marts:
  orders:
    grain: order
    base: order
    flatten:
      - {date: order_date, role: ordered}
    measures: [revenue, order_count]
    partition_by: [days(ordered_day)]

Step 2: Load the specs

The loaders are pure: they take YAML strings, never paths. Reading files is your four lines, because the library performs no I/O:

from pathlib import Path

from bloomery import load_catalog, load_project

HERE = Path("examples/quickstart")

catalog = load_catalog((HERE / "catalog.yaml").read_text())
project = load_project(
    {
        path.name: path.read_text()
        for path in sorted(HERE.glob("*.yaml"))
        if path.name != "catalog.yaml"
    }
)

load_project detects each document's kind from its version key (spec_version, mapping_version, metrics_version, marts_version); the catalog is loaded separately because it is shared across projects. A typo in any document fails here, loudly, with a source path into the offending YAML node.

Step 3: Compile and write the artifacts

compile_project is a pure function: parsed specs in, a tuple of file-shaped artifacts out. Writing them to disk is again the caller's job:

from bloomery import Target, compile_project

artifacts = compile_project(project, target=Target.SQLMESH, dialect="duckdb", catalog=catalog)
for artifact in artifacts:
    destination = Path("out") / artifact.path
    destination.parent.mkdir(parents=True, exist_ok=True)
    destination.write_text(artifact.content)
    print(f"wrote {destination}")

wrote out/models/gold/dim_date.sql
wrote out/models/gold/mart_orders.sql
wrote out/models/silver/order.sql

Three models: the silver entity, the gold mart, and the calendar table generated from the catalog's date_dimension.

Step 4: Read one model

Open out/models/silver/order.sql:

-- Generated by bloomery — do not edit.
-- fingerprint: blm1:76cae811da6c94e150d4e78900c1d57d80c56dbbf769e46ed3cd8eab581bfb80
MODEL (
  name silver.order,
  kind FULL,
  grain (order_id),
  audits (not_null(columns := (order_date)))
);

SELECT
  CAST(amount AS DECIMAL(12, 2)) AS amount,
  CAST(customer_id AS TEXT) AS customer_id,
  CAST(created_at AS DATE) AS order_date,
  CAST(id AS TEXT) AS order_id
FROM bronze.shop__orders

Everything traces back to a spec line: the transform chains became the CASTs, the assert: {not_null: true} became a SQLMesh audit, and the fingerprint header is the content hash of the compiled project — compile the same specs anywhere and you get these exact bytes (determinism).

Step 5: Plan a metric request

The same specs also answer metric questions at request time. The planner turns a structured MetricRequest into SQL over the mart — rendered, never executed:

Filters can arrive as a Mongo-flavoured JSON document — parse_filter_json is the public front door that normalizes it (De Morgan push-down, complement inversion, capped CNF) into typed clauses. Constructs the vocabulary reviewed and declined — including a non-finite literal — raise UnsupportedFilter with a .reason from the closed KNOWN_UNSUPPORTED list (InvalidLiteral is one of those refusals, not a malformed-input error); a document that is simply ill-formed raises InvalidRequest, which never carries such a reason:

from bloomery.planner import parse_filter_json

filters = parse_filter_json(
    {
        "customer_id": {"$neq": "internal"},
        "$or": [{"ordered_month": {"$gte": "2024-01-01"}}, {"ordered_month": "2023-12-01"}],
    }
)
print(f"parsed {len(filters)} filter clause(s) from JSON")

parsed 2 filter clause(s) from JSON

Two clauses, ANDed: one Predicate, and one AnyOf disjunction group. They go straight into the request:

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

naming = DefaultNaming()
planner = MetricFlowPlanner(LruManifestHydrator(naming), naming=naming)
plan = planner.plan(
    build_project_ir(project, catalog=catalog),
    MetricRequest(metrics=("revenue",), dimensions=("ordered_month",), filters=filters),
    dialect="duckdb",
)
print(plan.sql)
print(plan.explanation.render())

The plan carries runnable SQL plus a deterministic provenance record — the filters explained from the clause objects, never scraped back out of the SQL:

revenue
  mart:     gold.mart_orders (grain: order)
  measure:  revenue = SUM(amount)
            [additive — SUM]
  filters:  customer_id != 'internal'; ordered_month >= '2024-01-01' OR ordered_month = '2023-12-01'
  policy:   not applied

What you just did

You exercised both halves of the library. The compile half — parse, resolve against the catalog, typecheck, guardrail-check, lower, emit — produced deterministic SQLMesh artifacts from declarative specs. The planning half served a metric request from the wide mart those same specs declared, refusing nothing here because everything was reachable — but a request for an unreachable metric or an ambiguous dimension would have been a typed error, not wrong SQL. The compile pipeline page names each stage you just ran.

Where next