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 — which must be a date or timestamp, since it becomes the kind's time_column; a non-temporal leading column is refused. 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.

Composing across projects¶

SQLMesh has no cross-project reference, so a relation imported from an upstream is named directly — a mart built on an imported order_item selects FROM silver.order_item, the same text it would carry if the entity were local. There is no manifest of upstream projects to emit, and no model is emitted for the imported relation: the upstream builds it.

That directness is what makes the shared naming policy a constraint rather than a preference. silver.order_item is what DefaultNaming calls the entity; compile the upstream under a different policy — a tenant prefix, say — and it built acme_silver.order_item, which the downstream's SQL never names. The compile cannot see the mismatch, because the policy is an argument to this compile and the upstream IR records nothing about the one it was compiled under. Compile both sides with the same policy, and deploy the upstream's models before the downstream's.

The dbt target is the other half of this: it has a cross-project ref(), and emits the dependencies.yml that makes one resolvable.

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.
• The targets/ example runs this end to end — sqlmesh plan --auto-apply against a seeded DuckDB warehouse — and compares the mart it builds against the one dbt builds from the same specs.
• To serve metrics over the emitted marts at request time, continue to Plan a metric request.