Emit SQLMesh artifacts¶

You have a spec project and want SQLMesh models and audits on disk, ready for sqlmesh plan. SQLMesh is bloomery's primary target — the one surface that expresses everything the IR can say. For a full worked project, see examples/quickstart/; this page uses a deliberately small inline project so every step is visible.

Load the specs¶

The loaders take YAML strings — how the strings reach you (files, object store, a database row) is your concern, not the library's:

from bloomery import load_project

entity_model = """
spec_version: 1
entities:
  payment:
    grain: one row per payment
    key: [payment_id]
    scd: type2
    fields:
      payment_id: {type: string, required: true}
      order_id: {type: string, required: true}
      amount: {type: "decimal(12,2)", assert: {min: 0}}
"""

mapping = """
mapping_version: 1
source: psp__payments
target: payment
key:
  payment_id: {from: "$.id", transform: [to_string]}
fields:
  order_id: {from: "$.order_id", transform: [to_string]}
  amount: {from: "$.amount", transform: [{to_decimal: [12, 2]}]}
"""

project = load_project({"entity_model.yaml": entity_model, "mapping.yaml": mapping})

If your project has a catalog, load it with load_catalog(text) and pass it as catalog= below — it is deliberately not part of Project (why).

Compile¶

from bloomery import Target, compile_project

artifacts = compile_project(project, target=Target.SQLMESH, dialect="duckdb")

dialect selects the SQL rendering: "duckdb", "trino", or "postgres" ship in core. The choice changes only how expressions render (quoting, functions, type names) — never which artifacts exist or what they mean.

Write the artifacts yourself¶

compile_project returns a tuple of EmittedArtifact values — relative path, full content, kind (model / audit / config), and a SHA-256 checksum. The library never touches the filesystem, so writing is your loop:

from pathlib import Path

for artifact in artifacts:
    destination = Path("my_sqlmesh_repo") / artifact.path
    destination.parent.mkdir(parents=True, exist_ok=True)
    destination.write_text(artifact.content)

Point the loop at your SQLMesh repository root: the emitted paths already follow SQLMesh's models/ and audits/ layout.

What appears¶

For the project above:

A fuller project adds, per mart, models/gold/mart_<name>.sql — the wide gold table with relationship joins and date-role bucket columns — and, when the catalog declares a date_dimension, models/gold/dim_date.sql, a generated calendar with no clock involved. Namespaces (silver, gold, the mart_ prefix) come from the naming policy; pass naming= to compile_project to override them.

The silver model reads:

-- Generated by bloomery — do not edit.
-- fingerprint: blm1:6d7538120d74ec7ce9d6561623a53e8ef6e61b82bf40cc01f47210e6f19e57b6
MODEL (
  name silver.payment,
  kind SCD_TYPE_2_BY_COLUMN (unique_key (payment_id), columns *),
  grain (payment_id),
  audits (payment_amount_min)
);

SELECT
  CAST(amount AS DECIMAL(12, 2)) AS amount,
  CAST(order_id AS TEXT) AS order_id,
  CAST(id AS TEXT) AS payment_id
FROM bronze.psp__payments

The fingerprint header¶

Every artifact opens with the same two comment lines: a "do not edit" marker and the blm1:-prefixed fingerprint — a content hash of the entire compiled project. Two uses:

• Drift detection. Compare the header in your deployed repo against project_fingerprint(build_project_ir(project, catalog=catalog)) for the specs you believe are deployed; a mismatch means applied artifacts and specs have diverged.
• Change detection. Identical fingerprints mean byte-identical artifacts — same specs in, same bytes out, on any machine (determinism).

Materialization and SCD2¶

The entity's resolved materialization maps to the SQLMesh model kind: full → FULL, incremental_by_key → INCREMENTAL_BY_UNIQUE_KEY, incremental_by_partition → INCREMENTAL_BY_TIME_RANGE over the first partition column. Without an explicit materialization:, an entity with partition_by defaults to incremental_by_partition, otherwise full.

scd: type2 entities use SQLMesh's native kind, as in the model above: SCD_TYPE_2_BY_COLUMN (unique_key (...), columns *). Change tracking is by column comparison over all columns, not by an updated-at timestamp — the specs declare no such marker, and inventing one would silently degrade history. Composite keys work here (unlike the dbt target, which refuses them).

Audits¶

assert: clauses lower to SQLMesh audits in two ways:

• not_null and enum become builtin audits inline in the MODEL block — not_null(columns := (...)), accepted_values(column := ..., is_in := (...)).
• min, max, regex, and the path-conflict reconcile audit each become a custom audits/<entity>_<column>_<kind>.sql artifact selecting the violating rows from @this_model, referenced by name in the MODEL block.

Notes¶

• Artifacts are regeneration-only: edit specs and recompile, never the SQL — the header says so, and the fingerprint makes hand-edits detectable.
• bloomery emits models; running sqlmesh plan / sqlmesh run, scheduling, and backfills stay entirely on the SQLMesh side.
• The mart and calendar models assume the silver models feed them — deploy the whole artifact set together, not model by model.
• To serve metrics over the emitted marts at request time, continue to Plan a metric request.