DEV Community: Mark Alford

Two Frameworks - dbt & SQLMesh, One Owner

Mark Alford — Tue, 18 Aug 2026 14:00:00 +0000

On September 2025, Fivetran acquired Tobiko Data, the company behind SQLMesh and SQLGlot. A month later it announced an all-stock merger with dbt Labs, which completed on 1 June 2026.
One company now owns both open-source SQL transformation frameworks.

The part where we do not cry wolf

The obvious next paragraph writes itself: the open-source versions will be starved, the good features will move behind a login, you should get out now.

We are not going to write that paragraph, because the evidence points the other way.

In March 2026 Fivetran contributed SQLMesh to the Linux Foundation under open governance, with outside organisations as founding members. On the day the dbt merger closed, dbt Core v2.0 shipped with the Rust engine that had previously been ELv2 relicensed to Apache 2.0 and moved into the dbt-core repository. Both projects are more permissively licensed after the deals than before them.

If your worry was "will the licence get worse", the honest answer so far is no, and anyone telling you otherwise is selling something.

We should also declare an interest. Interlace's entire intermediate representation is sqlglot — the parser that came with the Tobiko acquisition. Every model we compile is parsed, canonicalised and transpiled by a library Fivetran now stewards. We are not neutral observers of this consolidation; we are downstream of it.

The part that is actually true

Here is the thing that is left after the FUD is stripped out, and it is not nothing.

There were two independent answers to "what should a transformation framework be", built by two teams who disagreed with each other in public, and now there is one owner and one roadmap. That roadmap has been stated clearly and repeatedly: data infrastructure for AI agents, with a managed platform at the centre of it. It is a coherent bet and it may well be the right one.

But a category with one roadmap is a category where certain arguments no longer have anywhere to happen. If you think the interesting problem is something other than agentic AI over a managed warehouse — if you think, for instance, that the interesting problem is that a working data platform currently requires four tools that do not know about each other — there is now one fewer venue for that argument.

We started building before either deal closed. The consolidation did not create the reason; it just made the gap easier to describe.

Clean models, in Python or SQL

That is the whole premise, and everything else is downstream of it.

A model is a .sql file containing SQL, or a .py file containing a function that returns Arrow. Not SQL with a templating language wrapped around it, and not Python that generates SQL strings — either language, used as itself, in the same DAG, with dependencies read out of the code rather than declared alongside it:

-- models/stg_orders.sql
SELECT order_id, customer_id, cents_to_dollars(subtotal) AS subtotal FROM raw_orders

# models/customer_risk.py
@model()
def customer_risk(customers, orders):
    return score(customers.table(), orders.table())   # a plain function over Arrow

The SQL file is valid SQL — you can paste it into any client. The Python file is a plain function — you can call it in a unit test with no warehouse. Neither is a template.

Four things that follow

Nothing else has to run it. Scheduling is in the tool: schedule: {cron: "0 * * * *"} or {every: "5m"} on a model, a durable work queue with leases, retries and cancellation, and interlace serve as the long-running process that holds them. dbt is the clear contrast — orchestration is explicitly out of scope, and the answer is Airflow, Dagster or dbt Cloud: a second system, with its own deployment, that has to be told about your DAG.

SQLMesh is closer, but the difference is worth being precise about, because "SQLMesh has a scheduler" is easy to say and slightly wrong. A model's cron there declares how often that model is due; sqlmesh run works out what is due and evaluates it. Something still has to invoke sqlmesh run, and their own guide says so directly: "You must run this command periodically with a cron job, a CI/CD tool like Jenkins, or in a similar fashion." That is a scheduler in the sense of knowing what ought to run. It is not a process that runs it, and the thing you end up deploying is still a crontab or a Kubernetes CronJob wrapped around a CLI. interlace serve is that process.

Events are a first-class input. A @stream is a durable HTTP ingestion endpoint: publishes land in a write-ahead log and are fsynced before the 200, then micro-batched into the warehouse exactly once via an in-warehouse watermark. The closest thing in shape is Cloudflare's Data Platform, where Pipelines ingests to durable streams and writes Iceberg into R2 — the same idea, and a good one. The difference is where it runs: theirs is a hosted platform billed per GB, ours is a process on your machine writing to your warehouse. If your events already belong on Cloudflare, use theirs.
If they belong next to your models, there was not previously an option that was also a transformation framework.

Column-level lineage, in the open-source tool. interlace impact orders.amount gives the column-level blast radius — every downstream column derived from that one — and the same graph drives change classification, so a change that provably touches only columns nothing consumes does not rebuild the consumer. SQLMesh has column-level lineage too, and had it first. The comparison that matters is with dbt, where lineage of this kind lives in dbt Explorer rather than in the thing you run locally.

It can write to tables it does not own. This is the one people are surprised by. A model can materialise into an external database — an attached Postgres or DuckDB, a serving table your application reads — with merge, append, full_merge or windowed incremental semantics, and it never drops that table. Or into a Parquet/CSV/JSON file. SQLMesh's external models are the mirror image: they describe tables it does not manage so it can read them and include them in lineage. Reading outward is solved in both. Writing outward — reverse ETL as an ordinary model with an ordinary strategy, gated by the same checks — is the part we added.

Where we are a cousin, not an original

It would be dishonest to present the architecture as novel. Fingerprint the canonical form of a model, store the result in a versioned physical table, point a per-environment view at it, and promote by moving the view: that is SQLMesh's design, and they published it first. Our snapshot tables and virtual environments are the same idea, arrived at for the same reasons, on top of the same parser.

What we did differently is scope. SQLMesh is a transformation framework that knows when its models are stale. Interlace is trying to be the whole spine — transformation, orchestration, ingestion and the control plane — in one process, on the theory that the seams between those four tools are where data platforms actually break.

That is a bet, not a proof. It might be wrong.

What you give up

No package ecosystem. dbt_utils is a large body of tested SQL that thousands of people use, and we have the mechanism to write your own but not the library. No semantic layer: MetricFlow has no equivalent here. Far fewer battle-tested adapters — DuckDB and Postgres are exercised properly, the rest are honest alpha. And a project this young has had a fraction of the hostile production exposure that either of the others survived years ago.

If you are running dbt or SQLMesh happily today, none of the above is a reason to move. Both are still open source, both got more open in the last year, and both have a company behind them that is considerably better resourced than we are.

The argument for this one is narrower: if you have found yourself running a transformation tool plus an orchestrator plus an ingestion service plus the glue between them, and the glue is what keeps breaking, it is worth an afternoon.

Start with the introduction, read why a unified abstraction for the longer version of the argument, or install it:

pip install interlaced

interlace.sh: A Macro Is an Expression, Not a Template

Mark Alford — Sat, 15 Aug 2026 14:09:51 +0000

Here is cents_to_dollars from dbt's own demo project. It converts an integer column of cents into a decimal of dollars, and it is five macros:

{% macro cents_to_dollars(column_name) -%}
    {{ return(adapter.dispatch('cents_to_dollars')(column_name)) }}
{%- endmacro %}

{% macro default__cents_to_dollars(column_name) -%}
    ({{ column_name }} / 100)::numeric(16, 2)
{%- endmacro %}

{% macro postgres__cents_to_dollars(column_name) -%}
    ({{ column_name }}::numeric(16, 2) / 100)
{%- endmacro %}

{% macro bigquery__cents_to_dollars(column_name) %}
    round(cast(({{ column_name }} / 100) as numeric), 2)
{% endmacro %}

{% macro fabric__cents_to_dollars(column_name) %}
    cast({{ column_name }} / 100 as numeric(16,2))
{% endmacro %}

A dispatcher and four implementations, for one division.

The variants are not gratuitous. Postgres would do integer division on /, so it casts first.
BigQuery spells the type NUMERIC and wants an explicit round. Each engine genuinely needs different SQL, and someone had to know that and write it down five times — and will have to write it a sixth time for the next warehouse.

The reason it is five, rather than one, is that Jinja renders text. A macro is a string template, the output is a string, and a string that is correct on DuckDB is wrong on Postgres. There is nowhere for the knowledge of dialects to live except in more templates.

What a macro actually is

Strip the templating away and cents_to_dollars is a named expression with a parameter. Not a string, not a code generator — an expression. (amount / 100) cast to a decimal, with a hole in it where a column goes.

SQL has syntax for exactly this, and DuckDB implements it:

-- macros/money.sql
CREATE MACRO cents_to_dollars(amount) AS (amount / 100)::numeric(16, 2);

Interlace reads macros/*.sql at project load and makes those callable from any model:

-- models/stg_orders.sql
SELECT
    order_id,
    cents_to_dollars(subtotal) AS subtotal,
    cents_to_dollars(tax_paid) AS tax_paid
FROM raw_orders

That is the whole feature, as far as the syntax goes. One definition, called like a function, in a file that is still valid SQL.

The interesting part is the next question.

Where do you expand it?

A macro has to be substituted for its body at some point, and there are three places to do it. The choice looks like an implementation detail and is not.

In the text, before parsing. This is Jinja, and it is where the five variants come from. Substitution happens before anything understands the SQL, so the only thing that can vary by engine is more text — hence adapter dispatch. You also lose the tree: until the template is rendered there is no query to reason about, and once it is rendered the structure is gone.

In the warehouse. DuckDB and Postgres both support this natively — CREATE MACRO and CREATE FUNCTION — and it is genuinely tempting. Register the macro against the engine once, and every model can call it. The SQL stays short and the engine does the work.

It also quietly breaks the thing that decides what to rebuild.

Interlace fingerprints a model's canonical SQL. If cents_to_dollars lives in the warehouse, then SELECT cents_to_dollars(subtotal) FROM raw_orders is the model's SQL, and it is byte-identical whether the macro divides by 100 or by 1000. Change the macro, and:

every model that calls it has an unchanged fingerprint;
plan reports no changes;
the tables that already exist keep the old arithmetic, indefinitely, with nothing anywhere indicating that they disagree with the definition.

That is the same failure as editing a seed CSV and being told there is nothing to do — except a seed is data and this is logic. It is the worst kind of bug: silent, plausible, and discovered by someone reconciling a number three weeks later.

In the AST, at compile time. This is what Interlace does. The call is replaced by the body while the model compiles — after parsing, and before the fingerprint, the lineage graph and the transpiler ever see the model.

That ordering is the whole design, and everything below falls out of it.

One definition, every dialect

The expansion produces AST, not text, and AST is what the transpiler consumes. So the dialect-specific knowledge stays where it already lived — in sqlglot — instead of in four more macros. The single line above compiles to:

engine	rendered
DuckDB	`CAST((subtotal / 100) AS DECIMAL(16, 2))`
Postgres	`CAST((CAST(subtotal AS DOUBLE PRECISION) / NULLIF(100, 0)) AS DECIMAL(16, 2))`
Snowflake	`CAST((subtotal / NULLIF(100, 0)) AS DECIMAL(16, 2))`
BigQuery	`CAST((subtotal / NULLIF(100, 0)) AS NUMERIC)`

Postgres gets its cast, because sqlglot knows Postgres does integer division. BigQuery gets NUMERIC. Nobody wrote postgres__cents_to_dollars.

The honest version of this claim: sqlglot's dialect knowledge is doing the work, and it is not infallible — a macro that leans on something genuinely engine-specific will still need your attention. But the ordinary case, which is most of them, is handled by the layer that already exists for it.

Editing a macro rebuilds its callers

Because the expansion is part of the canonical SQL, the fingerprint covers the macro body.
Changing 100 to 1000 in macros/jaffle.sql and running plan against dbt's demo project:

 Model          Change     Category   Build
 stg_orders     modified   breaking   rebuild
 stg_products   modified   breaking   rebuild
 stg_supplies   modified   breaking   rebuild
 products       modified   breaking   rebuild
 order_items    modified   breaking   rebuild
 supplies       modified   breaking   rebuild
 orders         modified   breaking   rebuild
 customers      modified   breaking   rebuild

Three models call the macro. The other five are downstream of those three. Nobody declared that relationship; it is the dependency graph doing its ordinary job on SQL that changed.
Note also what is not in that list. The project has nineteen models; eleven of them neither call the macro nor sit downstream of one that does, and they are untouched. Invalidation is on the rendered SQL, not on "this file changed" — the same mechanism that lets you reformat a model without rebuilding anything.

Lineage sees the body, not just the call

Column lineage reads the AST, so it sees whatever the macro's body touches. This matters when the body references something the call site does not mention:

CREATE MACRO net(x) AS x - shipping_fee;

SELECT id, net(subtotal) AS net_total FROM raw_orders

Where does net_total come from? With the macro expanded, both of its real sources:

net_total ← raw_orders.subtotal, raw_orders.shipping_fee

Left as an opaque function call, lineage can only follow the argument — raw_orders.subtotal — and shipping_fee disappears from the graph, along with every impact analysis and column-pruning decision that depends on it.

For a simple pass-through macro there is no difference; the argument is a column reference either way. The difference appears exactly when the macro is doing something interesting, which is when you want lineage to be right.
The same reasoning gives you dependency edges for free. A macro body may reference a model:

CREATE MACRO in_gbp(x) AS x * (SELECT rate FROM fx_rates);

Because expansion runs before dependency resolution, every model calling in_gbp gains a real edge to fx_rates and builds after it. There is nothing to declare, for the same reason there is nothing to declare for a FROM clause: the reference is in the tree.

The rules

Small enough to state completely:

Scalar expressions only. A table macro (AS TABLE SELECT ...) has no call site to expand into. That is a model, and models are the thing this tool is made of.
Macros may call macros, to a depth of ten. Recursion is a compile error naming the model, not a hang.
Arity is checked at compile time, naming the macro and the file it came from.
Definitions live in macros/*.sql, configurable with macro_paths.

What you give up

The macro does not exist in the warehouse. This is the real cost of expanding at compile time, and it is worth being plain about: if you open a SQL client against the built tables, you cannot call cents_to_dollars. It is a build-time abstraction, and ad-hoc queries do not get it.

If that matters more to you than rebuild correctness, the engine's own CREATE MACRO is right there and Interlace will not stop you from running it — you will just be responsible for noticing when a macro changes.

And the larger gap is unchanged: this is the mechanism, not the library. dbt_utils is a body of tested SQL that thousands of people already use, and writing generate_surrogate_key yourself — which is what dbt's demo project needs, and what our conversion of it does — is not the same as installing it.

Macros are documented under Models, and examples/jaffle-shop uses them for both of the cases dbt's project has: a project macro, and a package macro with no package to install. Or install it:

pip install interlaced

Migrating jaffle_shop: A Real dbt Project, End to End

Mark Alford — Sat, 15 Aug 2026 14:04:19 +0000

Every tool in this space claims migration is easy. The claim is usually made by someone who has not migrated anything, so here is an actual one: jaffle-shop-classic, dbt's own demo project, converted to Interlace, with the friction written down rather than edited out.

It is a small project — five models, three seed CSVs, twenty tests — which makes it a fair subject for a walkthrough and a poor one for extrapolating effort. A real project has hundreds of models and its own accumulated strangeness. What transfers from this exercise is the shape of the work: which parts are mechanical, and which parts need a person.

The end state, before the details:

Checks: 20/20 passed
Built 8 model(s); promoted 8 to 'prod'.

Twenty tests in, twenty checks out.

Seeds become models

dbt has a separate concept and a separate command for seeds: CSVs in seeds/, loaded by dbt seed. Interlace has no seed concept, because a seed is just a model with no upstreams:

-- models/raw_customers.sql
SELECT * FROM read_csv_auto('seeds/raw_customers.csv')

Three files, one line each. This is a genuine simplification — the CSV now participates in the DAG, gets a fingerprint, and rebuilds downstream models when it changes, which dbt seed does not do on its own. But it is undocumented as a migration step, and we only worked it out by trying. That is a docs gap on our side.

Four of five models: a two-line regex

The staging models are the ordinary case, and the conversion is entirely mechanical. Here is dbt's stg_customers.sql:

with source as (

    {#-
    Normally we would select from the table here, but we are using seeds to load
    our data in this project
    #}
    select * from {{ ref('raw_customers') }}

),
...

Two transformations cover it — strip Jinja comments, and turn {{ ref('x') }} into x:

sql = re.sub(r'\{#-?.*?-?#\}', '', sql, flags=re.S)
sql = re.sub(r"\{\{\s*ref\(\s*['\"]([^'\"]+)['\"]\s*\)\s*\}\}", r'\1', sql)

That is the diff in full:

-    {#-
-    Normally we would select from the table here, but we are using seeds to load
-    our data in this project
-    #}
-    select * from {{ ref('raw_customers') }}
+    select * from raw_customers

The CTEs, the joins, the column list — untouched. Four of the five models converted this way, because Interlace reads the dependency out of the FROM clause instead of asking you to declare it. customers.sql, with its three CTEs and two left joins, needed nothing but the regex.

Do not over-read this. ref() substitution is the easy half of any dbt project. The regex above handles ref('x') and would need extending for ref('package', 'x'), source(), and anything built by a macro.

The one place the mechanical pass broke

A subdirectory becomes part of the model's name. dbt's staging models live in models/staging/, and Interlace names a model after its path, so stg_customers came out as staging.stg_customers — and customers.sql's from stg_customers stopped resolving. The regex is not wrong; the layout is load-bearing in a way dbt's is not.

Either flatten the directory, or pin the name in the model's config block:

/*
interlace:
  name: stg_customers
*/

One line per staging model, in a config block those models already needed for their checks. It is a small thing, and worth knowing before you run the regex over two hundred files and wonder why the marts cannot see anything.

If every model of yours lives in a subdirectory — models/staging/, models/marts/, and nothing loose in models/ — there is a tidier fix: list the leaf directories as the model paths, and the names come out bare with dbt's layout untouched.

model_paths: [models/staging, models/marts]

They must not overlap, though: listing both models and models/staging registers every staging model twice. jaffle_shop keeps customers.sql and orders.sql directly in models/, so it cannot use this and pins the names instead.

The fifth model is the real work

orders.sql uses Jinja for what Jinja is actually for:

{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}
...
        {% for payment_method in payment_methods -%}
        sum(case when payment_method = '{{ payment_method }}' then amount else 0 end)
            as {{ payment_method }}_amount,
        {% endfor -%}

A loop generating four pivot columns, twice — once in the aggregate, once in the passthrough. There is no regex for this, and this is where a migration tool would hand you back a broken file.

Whichever way you go, the Jinja list becomes a Python list — there is no templating layer to put it in. What differs is whether the pivot itself ends up in Python or stays in SQL. Both are below; both were built and produce byte-identical output.

(There is always a third option, and for a list this stable it may be the right one: expand the four lines by hand and keep a plain .sql file. Nothing below is mandatory.)

Option 1 — a Python model

Write it as a function. Parameters name the upstreams, so stg_orders and stg_payments are the dependency edges, and the pivot is an ordinary loop over Arrow columns:

# models/orders.py
import pyarrow as pa
import pyarrow.compute as pc
from interlace import model

PAYMENT_METHODS = ["credit_card", "coupon", "bank_transfer", "gift_card"]


@model()
def orders(stg_orders, stg_payments):
    payments = stg_payments.table()

    # The Jinja {% for %} pivot, as a Python loop over Arrow columns.
    cols = {"order_id": payments["order_id"]}
    for m in PAYMENT_METHODS:
        is_m = pc.equal(payments["payment_method"], m)
        cols[f"{m}_amount"] = pc.if_else(is_m, payments["amount"], 0.0)
    cols["amount"] = payments["amount"]

    per_method = (
        pa.table(cols)
        .group_by("order_id")
        .aggregate([(f"{m}_amount", "sum") for m in PAYMENT_METHODS] + [("amount", "sum")])
    )
    per_method = per_method.rename_columns(
        ["order_id"] + [f"{m}_amount" for m in PAYMENT_METHODS] + ["amount"]
    )
    return stg_orders.table().join(per_method, keys="order_id", join_type="left outer")

This is the version to reach for if the logic is heading somewhere SQL cannot follow — a model call, a rate-limited API, a library with no SQL equivalent. It is also a plain function, so you can call it in a unit test with no warehouse.

The cost is that the aggregation now happens in the Interlace process rather than in the engine. On jaffle_shop's 113 payment rows that is irrelevant. On 25 million it would not be — DuckDB should do that work, not PyArrow.

Option 2 — a dynamic model

Keep the SQL, and generate it with the Python loop. Model files are imported and executed at project load, so registering a ModelDef is declaring a model:

# models/orders.py
from interlace import CheckSpec
from interlace.dsl.decorators import REGISTRY, ModelDef

PAYMENT_METHODS = ["credit_card", "coupon", "bank_transfer", "gift_card"]

pivot = ",\n        ".join(
    f"sum(case when payment_method = '{m}' then amount else 0 end) as {m}_amount"
    for m in PAYMENT_METHODS
)
passthrough = ",\n        ".join(f"order_payments.{m}_amount" for m in PAYMENT_METHODS)

REGISTRY.register_model(ModelDef(
    name="orders",
    sql=f"""
    with order_payments as (
        select order_id, {pivot}, sum(amount) as total_amount
        from stg_payments group by order_id
    )
    select stg_orders.order_id, stg_orders.customer_id, stg_orders.order_date,
           stg_orders.status, {passthrough},
           order_payments.total_amount as amount
    from stg_orders
    left join order_payments on stg_orders.order_id = order_payments.order_id
    """,
    checks=(
        CheckSpec(type="unique", columns=("order_id",)),
        CheckSpec(type="not_null", columns=("order_id",)),
        CheckSpec(type="not_null", columns=("customer_id",)),
        CheckSpec(type="not_null", columns=("amount",)),
        *(CheckSpec(type="not_null", columns=(f"{m}_amount",)) for m in PAYMENT_METHODS),
        CheckSpec(type="accepted_values", columns=("status",),
                  params={"values": ["placed", "shipped", "completed", "return_pending", "returned"]}),
        CheckSpec(type="relationships", columns=("customer_id",),
                  params={"to": "customers", "field": "customer_id"}),
    ),
))

That is orders' whole schema.yml, ten tests, in the file that defines the model. The checks loop too — the four not_nulls on the pivot columns come from the same list that generated them, so adding a payment method adds its column and its check together.

The relationships check is the interesting one: it reads customers, which is a sibling in the DAG rather than an upstream of orders. Interlace picks that up and schedules the check after customers builds, so the ordering is not yours to get right.

This is the closer translation of what the Jinja was doing, and the better default: the {% set %} becomes a Python list, the {% for %} becomes a generator expression, and the generated SQL still runs in the engine where it belongs. The structure maps almost line for line — which is the point, because the templating language was standing in for a programming language, and now there is one.

The cost is that you are building SQL with string joins, and a malformed f-string produces a parse error rather than a type error.

Both were checked

Each version was built and queried against the invariant that matters — every pivot column sums to the total:

SELECT count(*) AS mismatched_rows FROM orders
WHERE credit_card_amount + coupon_amount + bank_transfer_amount + gift_card_amount <> amount
-- 0

Zero for both, with matching rows throughout. Prefer Option 2 unless the transformation needs
Python — keeping the work in the engine is the difference that shows up at scale.

Tests become checks

All four dbt test types used by jaffle_shop map one-to-one:

dbt	Interlace
`unique`	`unique`
`not_null`	`not_null`
`accepted_values`	`accepted_values`
`relationships`	`relationships`

The difference is location. dbt keeps tests in a separate schema.yml; Interlace puts them in the model's own config block, so the model and its contract are one file. This is stg_orders.sql in full, header and all:

/*
interlace:
  name: stg_orders
  checks:
    - unique: order_id
    - not_null: order_id
    - accepted_values:
        column: status
        values: [placed, shipped, completed, return_pending, returned]
*/
with source as (
    select * from raw_orders
),
...

The fourth type, relationships, is on orders — in the CheckSpec form above, because that model is Python.

Whether that is better is taste. It is fewer files and less indirection; it is also a longer header on models with many checks. What is not taste: Interlace checks gate promotion by default, where dbt test is a separate command you have to remember to run in CI.

What this does not tell you

jaffle_shop has no macros beyond one loop, no packages, no snapshots, no incremental models, no custom materialisations, and no dbt_utils. A real project has several of those, and the honest answer for each:

Macros — macros/*.sql holds CREATE MACRO definitions, expanded into each model's AST at compile time. One definition covers every engine (the transpiler handles the dialect, so there is no postgres__ variant to write), and because the expansion lands before the fingerprint, editing a macro rebuilds its callers.
dbt_utils and packages — still no equivalent, and this is the largest genuine gap. A macro you write yourself is not the same as a shared, tested package other people already use; you get the mechanism, not the library.
Snapshots — strategy: scd covers Type 2 history, but the migration is not textual.
Incremental models — incremental maps closely for SQL. Python models need a key for it; without one, cursor with merge is the equivalent.
Custom materialisations — no equivalent, by design.

If you are considering a migration and want a second pair of eyes on the awkward parts, we would genuinely like to do one with you — partly to help, mostly because doing this one surfaced rough edges we had stopped noticing, and fixed them.

Everything above is reproducible from jaffle-shop-classic, and the converted project ships with Interlace as examples/jaffle-shop-classic — interlace apply --env prod in that directory is where the 20/20 above comes from.

dbt's current jaffle_shop is converted alongside it, as examples/jaffle-shop: nineteen models, twenty-seven checks, and the things this project has none of — source(), a project macro, a dbt_utils package macro, and a semantic layer that does not come across at all. Start with the introduction, or install it:

pip install interlaced

No Jinja: What Replaces Templating When SQL Is an AST

Mark Alford — Sat, 15 Aug 2026 13:52:41 +0000

Interlace has no templating language. A model is a .sql file containing SQL, or a .py file containing a function. There is no {{ }} and no {% %}.

Updated August 2026: Interlace has since gained macros — as SQL expressions expanded into the AST, not as templates. Job three below is rewritten to match; see A Macro Is an Expression, Not a Template.

The reasonable first reaction is that we have simply removed capability. Jinja is doing real work in a dbt project, and if you delete it you owe an answer for that work.

The answer is that Jinja is doing four unrelated jobs, which is why one mechanism doing all four feels both indispensable and awkward. Separated, each has a better answer than a templating language.

Job one: `{{ ref() }}` — declaring dependencies

This is the most common use and the least necessary. ref() exists because dbt does not parse your SQL; it needs you to declare the edge separately, in the text, and then it substitutes the physical name.

But the dependency is already in the query. It is the FROM clause. Interlace parses the SQL with sqlglot and reads the edges out of the AST:

from interlace.ir.canonicalize import parse, table_references

sql = """SELECT o.id, c.tier FROM orders o
         JOIN customers c USING (id)
         WHERE o.d > (SELECT max(d) FROM watermark)"""

sorted(table_references(parse(sql)))

» ['customers', 'orders', 'watermark']

Three dependencies, including one inside a correlated subquery, with nothing to declare. The model is:

SELECT customer_id, name, tier FROM raw_customers

That is the whole file. It is also valid SQL you can paste into any client, which a Jinja template is not.

Job two: `{% for %}` — generating many models or many columns

This is Jinja's legitimate job, and the one people reach for when a project has fifty tenants or a pivot over a list of payment methods.

Interlace's answer is that .py model files are imported and executed when the project loads. Registering a model is a function call, so a loop over a list is a loop over a list:

# models/per_tenant.py
from interlace.dsl.decorators import REGISTRY, ModelDef

def get_tenants():                    # any Python: a query, a file, an env var
    return ["acme", "globex", "initech"]

for tenant in get_tenants():
    REGISTRY.register_model(ModelDef(
        name=f"orders_{tenant}",
        sql=f"SELECT order_id, amount FROM raw WHERE tenant_id = '{tenant}'",
        strategy="merge",
        key=("order_id",),
    ))

Three real models, each with its own snapshot table, environment view, fingerprint, plan entry and checks:

 Model            Output    Strategy   Depends on   Rows   Time
 raw              virtual   replace    —              +4   0.06s
 orders_acme      virtual   merge      raw            +2   0.12s
 orders_globex    virtual   merge      raw            +1   0.08s
 orders_initech   virtual   merge      raw            +1   0.05s

Note what get_tenants() can be. In Jinja it must be something the templating context can reach. Here it is Python, so it can query a database, read a file, or call an API — and you can unit-test it, because it is a function.

This is not a feature we built. It falls out of model files being ordinary Python that runs.

Job three: macros — reusable SQL fragments

Jinja macros exist because SQL has no functions of its own that reach across files. But a macro like dbt's cents_to_dollars is not really a template — it is a named expression with a hole in it, and SQL has syntax for that:

-- macros/money.sql
CREATE MACRO cents_to_dollars(amount) AS (amount / 100)::numeric(16, 2);

Any model can call it, and the call is expanded into the model's AST while it compiles — before the fingerprint, before lineage, before transpilation:

SELECT order_id, cents_to_dollars(subtotal) AS subtotal FROM raw_orders

Because the expansion happens in the tree rather than in text, one definition covers every engine: sqlglot renders Postgres's integer-division cast and BigQuery's NUMERIC from that one line, where dbt needs default__, postgres__ and bigquery__ variants plus a dispatcher. And because it happens before the fingerprint, editing a macro re-plans every model that calls it — which a macro registered in the warehouse could not do, since the callers' SQL would not have changed.

That is its own post: A Macro Is an Expression, Not a Template.

When a macro is doing something SQL cannot express, the Python route is still there — a function that returns a SQL fragment, interpolated into a ModelDef the same way as Job two. A model may import a helper module sitting beside it (from _macros import ...; files starting with _ are not models), which is the shape to reach for when the "macro" is really a program.

Job four: `{{ config() }}` — per-model settings

Interlace puts configuration in a leading block comment, namespaced under interlace:

/* interlace:
  strategy: merge
  key: customer_id
  checks:
    - not_null: customer_id
    - unique: customer_id
*/
SELECT customer_id, name, tier FROM raw_customers

It is YAML inside a SQL comment. The file stays valid SQL — a comment is a comment — so your editor, your formatter and your database client all still work on it.

What the AST buys that text cannot

Removing Jinja is not the interesting part. The interesting part is what becomes possible once the model is a parsed tree rather than a string to be expanded.

Interlace fingerprints the canonical form of the AST, not the file. So changes that cannot affect the output do not rebuild anything:

  original           6b5428631ca23e11  same → reuse
  reformatted        6b5428631ca23e11  same → reuse
  comment added      6b5428631ca23e11  same → reuse
  extra whitespace   6b5428631ca23e11  same → reuse
  SEMANTIC CHANGE    259d4f1398cc67fa  DIFFERENT → rebuild

The first four are the same query written four ways — reflowed across lines, a comment added, spacing changed. Same fingerprint, so downstream models are reused rather than rebuilt. The last one changes sum to avg, and everything downstream of it rebuilds.

Run a formatter across your whole project and nothing rebuilds. That is not a heuristic about whitespace; it is a consequence of hashing a tree rather than a file.

The same parsed tree is what makes column-level lineage, the breaking-change classification in interlace plan, and cross-dialect transpilation possible. None of those can be built on templated text, because until the template is rendered there is no query to reason about — and after it is rendered, the structure that made it comprehensible is gone.

What you give up

Honestly: dbt's macro ecosystem. Macros themselves have an answer now (Job three), but dbt_utils and its relatives are a large body of tested, shared SQL, and writing generate_surrogate_key yourself is not the same as installing a package that thousands of people already use. You get the mechanism, not the library. If your project leans on that ecosystem, this is a real cost and you should weigh it.

You also give up templating inside SQL as a general escape hatch. When you want conditional SQL, the answer is to build the string in Python and register it — which is more explicit and slightly more verbose than an inline {% if %}.

More in Dynamic Models and SQL Models, or install it:

pip install interlaced

Python Models: dbt and Interlace, Side by Side

Mark Alford — Sat, 15 Aug 2026 13:47:44 +0000

We have written before that Python and SQL models are interchangeable nodes in the same graph. That is true of Interlace, and it is worth being careful about what it implies about dbt — because the most common version of this comparison, including one we drafted ourselves, gets it wrong.

dbt Python models participate fully in dbt's DAG. A SQL model can ref() a Python model, and a Python model can dbt.ref() a SQL one. The dbt documentation is explicit about it, with examples in both directions. Anyone claiming dbt Python models are "not real DAG nodes" has not read the page.

The differences are real, but they are elsewhere. Every claim below about dbt is from dbt's current Python models documentation; every claim about Interlace was checked against the source.

Where you can run one

This is the largest difference and the one that decides most cases.

dbt Python models require a data platform with a fully featured Python runtime. In practice that means Snowflake (Snowpark), BigQuery (BigFrames), or Databricks (PySpark). The code runs in the warehouse, on the warehouse's Python runtime.

Interlace Python models run in the Interlace process, so they work on any engine — including DuckDB and Postgres, where dbt has no Python model story at all.

The practical consequence: on a DuckDB or Postgres project, a dbt Python model is not slower or more limited, it is unavailable. If your stack is one of the three supported warehouses, dbt's approach has a real advantage in return — the computation happens next to the data, and never crosses the network.

What a model actually is

dbt's Python model is a function with a required signature, called by dbt with two arguments it supplies:

def model(dbt, session):
    dbt.config(materialized="table")
    orders = dbt.ref("stg_orders")
    return orders.groupBy("customer_id").agg(...)

Interlace's is an ordinary function whose parameters name its upstreams:

from interlace import model

@model(strategy="merge", key="user_id")
def user_ltv(by_user):
    for batch in by_user.reader():
        yield score(batch)

The difference that matters is testability. @model registers the model and returns the function unchanged, so it is callable in a unit test with no warehouse, no session and no dbt object:

>>> from models.user_ltv import user_ltv
>>> callable(user_ltv)
True
>>> user_ltv(fake_batches)     # a plain call, no infrastructure

dbt's model(dbt, session) requires both a dbt object and a live warehouse session, so testing it means testing through dbt.

The data type differs too. dbt hands you a warehouse DataFrame — Snowpark, BigFrames or PySpark depending on the platform, each with a different API, which is a portability cost if you ever change warehouse. Interlace hands you Arrow, streamed as RecordBatches, so memory stays bounded on inputs far larger than RAM.

What you can materialise

Both tools restrict Python models, in different places.

dbt supports table (default) and incremental. It does not support view or ephemeral, and Python models cannot reference ephemeral models at all.

Interlace supports only virtual — its owned, snapshot-backed default. view and ephemeral are rejected because both require SQL the engine can evaluate; table and file are rejected because terminal delivery is SQL-only for now. The errors say so directly:

Python models cannot be ephemeral; ephemeral requires SQL (it is inlined as a CTE)
Python models cannot materialise as 'table' yet; write a SQL model over this model's output

Interlace Python models can read an ephemeral SQL model, which dbt's cannot. The upstream is inlined as a CTE and never materialises:

 Model        Output    Strategy   Depends on   Rows   Time
 raw          virtual   replace    —              +3   0.06s
 pyconsumer   virtual   replace    mid            +3   0.06s

mid is the ephemeral model. It does not appear, because it was compiled into its consumer.

Incremental work

Here dbt is straightforwardly ahead. dbt Python models support incremental with the same incremental strategies as SQL models, subject to adapter support.

Interlace Python models can use incremental when they declare a key — the Arrow output is staged and the window's rows are upserted. Without a key it is refused, because a Python function has already computed everything before the window could narrow it; the window would bound what is written, not what is done. cursor is the tool for bounding the fetch.

So the gap is narrower than it looks, but real: dbt pushes the incremental predicate into the query it generates, and for a Python model we cannot.

Side by side

	dbt	Interlace
Platforms	Snowflake, BigQuery, Databricks	any engine (DuckDB, Postgres, …)
Where the code runs	in the warehouse runtime	in the Interlace process
SQL model can depend on it	yes	yes
It can depend on a SQL model	yes	yes
Can read an `ephemeral` upstream	no	yes
Materialisations	`table`, `incremental`	`virtual` only
`view` / `ephemeral`	no	no
Incremental	yes	keyed only
Data type at the boundary	Snowpark / BigFrames / PySpark DataFrame	Arrow `RecordBatch` stream
Callable in a unit test without the tool	no	yes
Reuse functions across models	no	yes — ordinary Python imports

Choosing

Neither column is a win. The honest decision rule:

dbt if your warehouse is Snowflake, BigQuery or Databricks and you want computation to stay next to the data, or if you need incremental Python models. Both are real advantages and we do not have an answer to either.

Interlace if your engine is DuckDB or Postgres, where dbt Python models do not exist; if you want to unit-test transformation logic as plain functions; or if the memory profile of streaming Arrow matters more than warehouse-side execution.

The thing worth avoiding is picking on a claim neither tool actually makes. dbt Python models are full DAG participants. Ours cannot do incremental. Both statements are inconvenient for the usual marketing, and both are true.

Start with the Python models guide, or install it:

pip install interlaced

Twenty-Five Million Rows in Five Seconds, and How to Check

Mark Alford — Sat, 15 Aug 2026 13:34:25 +0000

Benchmarks in this industry are mostly untrustworthy, and usually for the same reason: the person running them chose the shape of the work. So let us be precise about what this is and what it is not.

This is not a comparison against dbt. dbt is not an execution engine — it generates SQL and hands it to your warehouse — so "Interlace versus dbt" would be measuring DuckDB against whatever you pointed dbt at, dressed up as a tool comparison. That number would tell you nothing.

This is a capability demonstration: a real DAG doing real work, on hardware you can buy, with every command written down. The point is not that a number is small. The point is that you can run the same thing in the next ten minutes and get your own.

The shape of the work

The examples/benchmark project generates 25 million synthetic events in-engine — nothing to download, fully deterministic — and pushes them through a deliberately awkward fan-out:

events (25M rows) ── enriched (ephemeral: inlined into every consumer)
                      ├─ by_user ────┬─ user_ltv       (Python, Arrow batches, merge)
                      │              └─ user_history   (scd — Type 2 history)
                      ├─ by_product ─┬─ top_products   (view)
                      │              └─ product_catalog (full_merge, composite key)
                      ├─ by_device
                      └─ by_day
events ───────────── daily_revenue (incremental, 1d grain)
                      ├─ revenue_report (parquet file)
                      └─ daily_feed     (append → external DuckDB, reverse ETL)

Two details make this harder than it looks. enriched is ephemeral, so it is inlined as a CTE into every consumer rather than materialised once — each of the four by_* branches scans the full 25 million rows independently. And the branches share no edges, so they are eligible to build concurrently.

Between them, twelve models exercise six of the seven strategies — replace, incremental, merge, full_merge, scd and append (only hash_merge is absent) — across virtual, ephemeral, view, file and an external table. There is a Python model in the hot path, not bolted on at the end.

The numbers

Machine: AMD Ryzen 9 9955HX3D (16 cores, 32 threads), 60 GB RAM, Linux. Python 3.12.3, DuckDB 1.5.4, PyArrow 23.0. A DuckLake warehouse on local disk.
Three consecutive cold builds — full teardown of the warehouse between each:

run 1: 5.19s
run 2: 5.10s
run 3: 4.98s

With CPU accounting on the same cold build:


Wall clock	5.11s
CPU (user + system)	13.83s
CPU utilisation	270%
Peak resident memory	1.84 GB

The gap between 13.83s of CPU and 5.11s of wall is the whole point of scheduling the true DAG: independent branches build at the same time, and DuckDB parallelises inside each query. The memory figure matters more than the time one — 25 million rows moved through a Python model and five strategies without the process exceeding two gigabytes, because data crosses as streamed Arrow RecordBatches rather than being materialised into pandas at each boundary.

Per-model, from the build table:

 Model             Output    Strategy              Rows      Time
 events            virtual   replace         25,000,000     3.75s
 by_user           virtual   replace            100,000     0.46s
 top_products      view      replace                  —     0.37s
 product_catalog   virtual   full_merge          15,000     0.35s
 daily_feed        table     append                  29     0.31s
 daily_revenue     virtual   incremental     29     0.29s
 by_product        virtual   replace             15,000     0.21s
 user_ltv          virtual   merge              100,000     0.16s
 by_device         virtual   replace                  4     0.11s
 user_history      virtual   scd                100,000     0.09s
 by_day            virtual   replace                 30     0.07s
 revenue_report    file      replace                 29     0.07s

Generating the 25 million rows is 3.75s of the 5.11s. Everything downstream — five strategies, a Python model, a Parquet write and a delivery into an external database — is the remaining 1.4 seconds of wall time.

Do not take the row counts on trust. Ask the warehouse:

interlace query "SELECT count(*) FROM events"
# 25000000

The parts that are not about speed

Raw throughput is the least interesting thing here, because it is mostly DuckDB's. The interesting behaviour is what happens on the second run.

A repeated incremental window does nothing at all. daily_revenue is incremental at a one-day grain, and completed intervals are recorded in a ledger:

interlace run --select daily_revenue --start 2026-06-01 --end 2026-07-01
# Ran 0 model(s) (0 task(s)); promoted 1 to 'prod'.     0.35s

Zero tasks — not a fast rebuild, no rebuild. Reprocessing is a separate verb, so asking for it is deliberate rather than accidental:

interlace restate --select daily_revenue --start 2026-06-08 --end 2026-06-15
# Checks: 7/7 passed
# Restated 1 model(s) (1 task(s)); promoted 1 to 'prod'.   0.67s

A one-line change rebuilds one branch. Add a column to by_device, one of four branches hanging off a 25-million-row ephemeral scan:

sed -i 's/avg_ticket/avg_ticket, min(amount) AS min_ticket/' models/by_device.sql
interlace plan

 Model       Change     Category   Build
 by_device   modified   breaking   rebuild

One model, and apply then refuses to run it:

plan has breaking changes (by_device); re-run with --force to proceed

That refusal is the feature. Adding a column changes the shape of what consumers see, so it is classified breaking and the build stops before touching the warehouse — the classification is a gate, not a report. Forcing it through rebuilds only what changed:

interlace apply --force
# by_device   virtual   replace   +4   0.10s
# Built 1 model(s); promoted 13 to 'prod'.      0.48s wall

0.48s against 5.11s for the full build, and the other eleven models were never touched, because their fingerprints did not move.

Run it yourself

git clone https://github.com/interlace-sh/interlace
cd interlace/examples/benchmark
pip install interlaced

time interlace apply
interlace query "SELECT count(*) FROM events"

To make the machine work harder, raise range(25000000) in models/events.sql. At 100 million the DAG shape does not change — each branch simply scans four times as much through the inlined CTE.

If your numbers differ from ours, that is useful information and we would like to see them. Different hardware, a different filesystem, a spinning disk, a container with two cores — all of those will move these figures, and none of them are hidden behind a marketing chart.

What this does not show

A single machine. Every number above comes from one process on one box, and that is the case Interlace is built for. If your warehouse does not fit on one machine, this benchmark is not evidence about your workload, and we would rather say so than let a chart imply otherwise.

It also says nothing about the engines we have not proven. These runs are DuckDB and DuckLake, which are tested in CI. Spark is beta; Redshift, Snowflake, BigQuery and MotherDuck are alpha — dialect-correct and unit-tested, but not yet run against a live account.

The DAG above is in examples/benchmark.
Start with the introduction, or install it:

pip install interlaced

interlace.sh: A Python Model Is Just a Function

Mark Alford — Sat, 15 Aug 2026 13:18:20 +0000

Most tools that support both SQL and Python support one of them properly. Python arrives as an escape hatch: a different execution path, a different set of available features, often a different platform requirement, and a strong implicit suggestion that you should have written SQL instead.

In Interlace a .py model and a .sql model are the same kind of node. Either can depend on the other, in either direction, and the planner does not distinguish between them. This post shows that claim running rather than asserts it.

The graph

The benchmark project in the repository is a ten-model DAG over 25 million synthetic events. It is not a toy: the numbers are generated in-engine so there is nothing to download, and the fan-out does real, repeated work.

The chain that matters here is four models long:

events.sql  →  by_user.sql  →  user_ltv.py  →  top_products.sql

A Python model sits in the middle. Its upstream is SQL. Its downstream is SQL. Nothing about the surrounding models acknowledges that the middle one is written in a different language.

The Python model

"""A Python model in the hot path: 100k user rows stream through Arrow."""

import pyarrow as pa
import pyarrow.compute as pc

from interlace import model


@model(depends_on=["by_user"], strategy="merge", key=["user_id"])
def user_ltv(by_user):
    for batch in by_user.reader():
        score = pc.add(pc.multiply(batch.column("spend"), 0.1), batch.column("events"))
        yield pa.RecordBatch.from_arrays(
            [batch.column("user_id"), batch.column("spend"), pc.round(score, 2)],
            names=["user_id", "spend", "ltv"],
        )

Three things are worth pulling out.

The parameter is the dependency. by_user is not a string to be resolved later; it is the name of another model, and the function signature is the edge. For SQL models the same edge comes from parsing the FROM clause. Both produce identical entries in one graph.

It streams. by_user.reader() yields Arrow RecordBatches, and the function is a generator. Memory stays bounded regardless of how far you scale events.sql — you can raise the row count by an order of magnitude and this model's footprint does not move.

The strategy is the same strategy. merge here is the same keyed upsert a SQL model gets, compiled the same way, running as SQL in the warehouse. Python produced the rows; it did not take over the write path.

The SQL either side

Upstream, plain SQL with no header at all, which means it takes the defaults — materialise: virtual, strategy: replace:

SELECT user_id, count(*) AS events, sum(amount) AS spend
FROM enriched
GROUP BY user_id

Downstream, a model that reads a Python model's output as an ordinary relation:

/* interlace:
  materialise: view
*/
SELECT product_id, sum(revenue) AS revenue
FROM by_product
GROUP BY product_id
ORDER BY revenue DESC
LIMIT 20

That is the edge other tools cannot express freely. A SQL model selecting FROM the output of a Python model is not a bridge, an adapter or a special case. It is a table reference that happens to resolve to a model that happens to be Python.

What makes it work

The Arrow wire format. A model boundary is a RecordBatchReader in both directions, so a Python function and a SQL query are interchangeable at that boundary by construction. There is no conversion step to go wrong and no DataFrame round-trip to blow up memory.

The handle you receive is single-pass and gives you a choice:

Call	Returns	Use when
`.table()`	`pyarrow.Table`	eager, whole-table work
`.reader()`	`pyarrow.RecordBatchReader`	streaming, bounded memory
`.schema`	`pyarrow.Schema`	inspecting before consuming

Call one of them, once. A handle consumed twice is an error rather than a silent second scan.

You can return a pyarrow.Table, a RecordBatch, a RecordBatchReader, or — as above — yield batches from a generator.

Where the symmetry actually stops

It would be easy to end here, and dishonest. There are three real limits, and they follow from the design rather than from missing work.

Python models are always virtual. They cannot be a view or ephemeral, because both of those require SQL the engine can evaluate directly — a view is a query, and an ephemeral model is inlined as a CTE. There is nothing to inline when the model is a Python function.

Python models cannot deliver to a terminal destination. materialise: table and materialise: file are SQL-only. If you want a Python model's output in an external system, write a one-line SQL model that selects from it:

/* interlace:
  materialise: table
  target: crm.main.user_scores
  strategy: merge
  key: user_id
*/
SELECT user_id, ltv FROM user_ltv

Python models need a key to use incremental. With one, the function's Arrow output is staged and the window's rows are upserted into the target — the same keyed semantics a SQL model gets. Without one it is refused, and that refusal is the honest part: a SQL model has the window predicate pushed into its query, so the engine only ever computes the window, whereas a Python function has already produced everything by the time the window could be applied. An unkeyed windowed rewrite would look incremental while doing the full work every run. Use cursor to bound what the function fetches instead.

The first two raise at definition time, the moment the decorator runs; the third at plan time.

Testing it

Because the decorator registers the model and returns the function unchanged, a Python model is still an ordinary function. Call it with Arrow tables and assert on what comes back.
No fixtures, no warehouse, no separate framework:

import pyarrow as pa


def test_user_ltv():
    by_user = pa.table({"user_id": [1, 2], "spend": [100.0, 50.0], "events": [3, 1]})
    result = pa.Table.from_batches(user_ltv(FakeHandle(by_user)))
    assert result.column("ltv").to_pylist() == [13.0, 6.0]

This is the part that tends to convert people. A pipeline step you can call in a unit test, with no infrastructure, is a different kind of object from a pipeline step you can only observe by running it.

The next post is about the other end of the system, where a promise is much harder to keep: what it takes for an HTTP 200 to actually mean the data is safe.

Read the Python models guide for handles, cursors and the this parameter, or testing for the layered safety net around them.

interlace.sh: Sandboxes That Cost Nothing

Mark Alford — Sat, 15 Aug 2026 12:54:40 +0000

If you work with external APIs, you know the problem. There is no dev.github.com, no staging.api.companieshouse.gov.uk, no test endpoint for the thing you actually depend on. Production is the only source. So how do you get a development environment without re-fetching everything you already have?

The usual answer is to copy: duplicate the warehouse, or keep a separate dev database and sync it periodically. Both are slow, both drift, and both cost storage in proportion to the number of people on the team.

Interlace does something else. An environment is not a copy of your data, it is a set of views over it.

Fingerprints first

Every model gets a fingerprint: a hash of its canonical SQL — or its Python source — together with its strategy configuration and its upstream fingerprints. A build writes an immutable physical table named after that fingerprint.

interlace__main.orders__a1b2c3

That table never changes. If the model's definition changes, the new version gets a new fingerprint and a new table, and the old one stays exactly where it is.

An environment is then just a set of views pointing at fingerprinted tables. Production is the unprefixed namespace; every other environment prefixes its schema.

Environment	View for `main.orders`
`prod`	`main.orders`
`dev`	`dev__main.orders`
`pr-142`	`pr-142__main.orders`

Consumers and BI tools connect to main.orders and never learn that a fingerprint exists. There is no environment list to configure, either — an environment exists once something has been promoted to it.

Why the sandbox is free

Here is where the re-fetching problem disappears. Applying to a sandbox does not rebuild models whose fingerprint already exists. It points the sandbox's views at the tables production already built.

interlace apply --env dev

Change one model out of forty and the sandbox builds one model. The other thirty-nine are reused — not copied, reused, the same physical tables production is reading. The expensive source extract that ran this morning is the table your sandbox reads this afternoon.

It goes further than "did this model change". Because the IR is an AST, impact analysis runs at column level: a semantic change invalidates only the consumers of the columns it actually touched. A downstream model whose output is provably identical is marked reuse in the plan and is not rebuilt at all.

$ interlace plan
 Model         Change    Category      Build
 orders        modified  non_breaking  rebuild
 order_stats   modified  non_breaking  reuse

order_stats sits downstream of a changed model and still does not rebuild, because the analysis proved its output cannot differ.

Promotion is a view swap

Since the tables are immutable and the environment is only a pointer, promoting to production is an atomic view swap rather than a data migration.

interlace apply --env dev   # iterate in a sandbox
interlace plan              # see what prod would get
interlace apply             # promote

Two properties fall out of this, and both matter more than they sound.

A failed apply changes nothing. Views move only after data-quality checks pass. There is no half-promoted state, because promotion is one operation rather than a sequence of them.

Rollback is promotion run backwards. Every promote records the environment's full mapping as a generation in the promotion history. Rolling back repoints the views at an earlier generation — the tables have not gone anywhere, so nothing rebuilds:

interlace env rollback --list
interlace env rollback --to 7

This is the marquee benefit of content-addressed snapshots. Recovery is not a restore. It is a pointer move, and it takes about as long as a CREATE OR REPLACE VIEW.

Reclaiming the space

Immutable tables accumulate, so gc reclaims them — reference-aware, so a snapshot production still uses, or that another environment reuses, survives.

interlace env drop dev            # views and prefixed schemas go; tables remain
interlace gc                      # 7-day grace by default
interlace gc --grace 12h --dry-run

Dropping an environment deliberately leaves the underlying tables alone. They simply become reclaimable.

Sandboxes cannot touch production systems

There is one thing a virtual environment must never do, which is silently fan side-effecting writes out to production.

Models that deliver outside the warehouse — an external table, a file — are environment-gated. By default they fire only on a prod apply. A sandbox apply builds the model and reports the delivery as gated rather than writing to a live external table.

/* interlace:
  materialise: table
  target: crm.main.accounts
  strategy: merge
  key: id
  environments: [dev, prod]
*/

Widening the gate is explicit, and the gating list is part of the fingerprint — so widening it re-plans the model rather than classifying it unchanged and never delivering.

This is the property that makes the whole thing usable in practice. You can run interlace apply --env dev against real production source tables without any risk that a half-finished model writes into your CRM.

What it costs

Storage for one immutable table per fingerprint you have not yet collected, and a view per model per environment. That is the entire overhead. There is no second warehouse, no sync job, and no per-developer copy.

The next post is about the other half of the graph: why a Python model is not a special case.

Read the environments guide for drift, generations and gc, or schema evolution for how changes get classified.

interlace.sh: The IR Is a sqlglot AST

Mark Alford — Sat, 15 Aug 2026 12:01:03 +0000

Every data tool has an intermediate representation, whether it admits to one or not. It is the thing a model becomes after parsing and before execution, and it quietly decides what the tool can do.

dbt's IR is templated text, which is why ref() is a string and why a macro can produce SQL no analyser can reason about. An earlier iteration of Interlace effectively had a pandas DataFrame as its IR — every model boundary ran an eager .execute() and fed the result back in — which cost us laziness, dialect portability and any hope of semantic change detection, all at once.
Three properties, one root cause. That is what an IR does: it sets the ceiling.

So the current design committed to one sentence:

The canonical IR is a sqlglot AST + Arrow schema. The canonical wire format is an Arrow RecordBatchReader. Materialisation happens exactly once, at the sink, as a single native SQL statement executed inside the owning engine.

Everything else in Interlace 2.0 follows from that.

Why not Ibis

Ibis is a good library and 0.x was built on it. Dropping it was the single largest deletion in the rebuild, so it deserves a real justification rather than a preference.

Ibis was doing two jobs for us. As a data plane, it moved results between models — and it sits on Arrow underneath, so we were paying for a wrapper around a format we could use directly. As an expression builder, it turned Python into SQL — and it compiles to sqlglot, which is the AST we had already decided to make canonical.

Both roles were covered without it. Removing Ibis dropped a heavyweight dependency and its governance risk for zero lost capability. Remote engines connect over ADBC rather than Ibis backends, which is a narrower and better-specified contract.

That is the whole argument. Not "Ibis is bad" — "Ibis is a layer over the two things we had already chosen".

What an AST IR buys

A sqlglot AST is a parsed, structured, dialect-neutral representation of the query. Three consequences matter.

Strategies become AST builders, not string templates. A strategy is the thing that turns a model's query into a table. In 0.x, each one wrote DuckDB SQL by hand. Now each one takes the model's query expression and its target, and emits a short list of SQL statements that apply runs in one transaction. Dialect appears only at transpile(), at the very end.

The payoff is that no strategy needs the model's column list. A model's schema can change without a hand-written migration, because a definition change simply mints a new snapshot table.

Dependencies are parsed, not declared. Because we hold the AST, the FROM and JOIN clauses are structure rather than text. A reference whose name — or whose last dotted segment — matches another model becomes a DAG edge. CTEs are excluded, because the parser knows what a CTE is.

/* interlace:
  strategy: merge
  key: order_id
*/
SELECT order_id, customer_id, amount
FROM raw_orders

There is no ref() to write. raw_orders is a real table reference that happens to resolve to a model, and it is rewritten to the right snapshot at build time.

Change classification can be semantic. This is the one that pays for the whole decision.
Because we can compare two ASTs rather than two strings, interlace plan can tell the difference between a change that alters a model's output and one that does not — and then narrow that further, per column, to work out which downstream models are genuinely affected.

The analysis is deliberately conservative. Adding avg(amount) AS avg_amount to a SELECT is additive. Anything touching existing expressions — or anything the analyser cannot prove safe, such as a SELECT * rewrite, a DISTINCT, or a positional GROUP BY — is treated as breaking. Ambiguity always errs toward rebuilding, never toward a false skip.

Arrow at the boundaries

The wire format is an Arrow RecordBatchReader. Data crossing into a Python model arrives as Arrow and leaves as Arrow.

from interlace import model

@model(depends_on=["by_user"], strategy="merge", key=["user_id"])
def user_ltv(by_user):
    for batch in by_user.reader():
        ...
        yield batch

Because it is a reader rather than a table, a Python model can stream. Memory stays bounded no matter how large the upstream is, which is the property 0.x's .execute() → pandas → memtable() round-trip destroyed at every hop.

pandas and Polars are still available — as optional extras, at the edges, when you actually want a DataFrame. They are not in the core and they are not the interchange format.

What the rebuild refused

Three refusals are worth stating plainly, because each one is a thing many peers do.

No Jinja. Python is the macro language. If you need to generate forty models from a config, write a loop — the dynamic models guide covers the patterns and the traps. A templating DSL inside SQL strings is a second language with no parser, no types and no editor support.

No ref()-as-text. References resolve at the AST level, as above.

No pandas in the core. Arrow only.

The result is a core install with nine runtime dependencies: sqlglot, duckdb, pyarrow, pydantic, typer, rich, cronsim, tenacity and pyyaml. The HTTP daemon is an optional service extra.

The shape it produced

Five lines describe the whole system:

The IR is a sqlglot AST; the wire format is an Arrow RecordBatchReader; strategies are AST builders and dialect appears only at transpile().
Storage defaults to DuckLake — Parquet with a SQL catalog — opened as DuckDB's primary database.
The control plane, holding snapshots, intervals, the run queue, events and API keys, is SQLite in WAL mode.
Streams live in their own durable log; the materialiser commits data and watermark in one warehouse transaction, giving exactly-once without distributed coordination.
No Jinja, no pandas in core, no external orchestrator.

The next post takes the second and third of those apart: how fingerprinted snapshots and environment views make a development sandbox cost nothing.

Read the core concepts for how the IR becomes a plan, or dependencies for the resolution rules in detail.

interlace.sh: Why We Built a Unified Abstraction

Mark Alford — Fri, 14 Aug 2026 23:07:02 +0000

Consider what a data engineer has to learn before writing a single useful transformation on a typical 2026 data stack.

dbt, for SQL models, sources, tests and macros. Airflow or Dagster, for scheduling, sensors and retry logic. dlt, Airbyte or Fivetran, for ingestion. Python scripts for anything SQL cannot express. Then YAML to configure all of it, and a growing pile of glue to hold the pieces together.

Each of those tools is good. dbt genuinely standardised SQL transformation. Airflow's operator ecosystem is unmatched. dlt does schema inference and incremental loading better than most hand-written extractors. The problem is not the tools.

The problem is the seams.

Where failures actually live

Your ingestion layer writes to a landing zone. Your transformation layer reads from it. Nothing in either tool knows that this handoff exists — it is a convention, held together by a table name and an assumption about timing.

That is where things break, and they break quietly. An upstream API changes its response shape; the loader happily writes the new columns; the transformation reads the ones it expects and silently produces a narrower result. A load runs late; the transformation runs on schedule anyway and publishes yesterday's numbers. A model fails; the orchestrator retries it; the retry succeeds against half-written data.

The debugging story is worse than the failure. Testing a dbt model uses one framework. Testing a Python script uses another. Testing a dlt pipeline uses a third. When something goes wrong at 2am, the answer is spread across three dashboards, and none of them knows about the other two.

None of this is hypothetical. It is the ordinary experience of running a stack where no single component can see the whole path from source to dashboard.

Software engineering solved this

The same fragmentation existed in general programming and got resolved through abstraction rather than integration.

Functions unified computation — nobody uses a different tool for arithmetic than for string processing. Interfaces decoupled implementation from use. Package managers unified dependency resolution, replacing a manual, error-prone, entirely social process.

In each case the win was not a better tool for each job. It was one concept that covered all of them, so the seams disappeared instead of being managed.

Data engineering is on the same arc, roughly a decade behind. We still have a dedicated tool per concern, each with its own mental model, and we still spend real effort on the glue.

One abstraction

Interlace is built on a single idea: a model is a query or a function that produces a table, and everything is a model.

A SQL model is a file. Dependencies come from the query itself — there is no ref() to write, because the parser can see the FROM clause:

/* interlace:
  strategy: merge
  key: customer_id
  checks:
    - not_null: customer_id
*/
SELECT customer_id, name, tier FROM raw_customers

A Python model is a function. Its parameters name its upstreams:

from interlace import model

@model(strategy="merge", key=["user_id"])
def user_ltv(by_user):
    for batch in by_user.reader():
        yield score(batch)

These are not two systems with a bridge between them. They are two spellings of one node type.
A SQL model can select from a Python model, and a Python model can take a SQL model as a parameter, because both resolve to entries in the same graph.

Ingestion is the same abstraction again. A model with no upstreams is a source — it pulls from the outside world and participates in the DAG like anything else:

@model(strategy="merge", key=["event_id"])
def raw_events():
    import httpx, pyarrow as pa

    response = httpx.get("https://api.example.com/events")
    return pa.Table.from_pylist(response.json())

You can call dlt inside that function if you want its schema inference. The point is not to replace it — the point is that the extraction is now a node in the graph rather than a separate system writing to an agreed table name.

What collapses

The handoff becomes an edge. When ingestion is a model, the dependency between extraction and transformation is a real edge in a real graph. When the API changes shape, you find out in the same place you find out about a broken join.

One testing pattern. The decorator registers the model and returns the function unchanged, so a Python model is an ordinary function you can call in a unit test with no warehouse and no fixtures. SQL models are covered by the same checks that gate production.

One deployment. interlace serve runs the scheduler, the HTTP API, stream ingestion and a web UI in one process. There is no orchestrator to deploy alongside it and no broker to operate.

One vocabulary. A new engineer learns what a model is, and can then read, write and debug any step — extraction, transformation, Python or SQL.

One abstraction is not enough

This is the part where the argument would usually stop, and it should not.

A shared interface removes the seams. It does not, by itself, tell you whether a change is safe — and "safe" is what actually keeps people awake. Two further things are doing that work.

Changes are previewed, not discovered. interlace plan fingerprints every model and classifies each change before touching the warehouse. Because the comparison is between parsed queries rather than file hashes, a downstream model whose output is provably identical is reused rather than rebuilt:

$ interlace plan
 Model         Change    Category      Build
 orders        modified  non_breaking  rebuild
 order_stats   modified  non_breaking  reuse

A plan containing breaking changes refuses to apply without --force, which makes the classification an automated review gate rather than a report.

Ingestion is durable, not best-effort. Models cover data you pull on a schedule. They do not cover events arriving continuously, so streams fill that in — without introducing a second mental model. Declare one, POST to it, and the row is durable before the response returns, deduplicated by idempotency key, and materialised exactly once into a table that SQL models read like any other.

What this does not replace

Interlace is not the answer to everything, and pretending otherwise would undermine the rest of the argument.

dbt has an ecosystem we will not match for years, and an adapter for every warehouse you might already be paying for. Airflow handles cross-system orchestration — the kind that reaches outside your data platform entirely — far better than we intend to. Spark processes volumes Interlace is not designed for.

Engine coverage is closer than it looks — DuckDB, DuckLake, MotherDuck, Postgres, Redshift, Snowflake, BigQuery and Spark all run models, with near-full strategy support on each. What is not comparable is how proven they are: only the DuckDB family and Postgres are tested in CI, Spark is beta, and the four cloud warehouses are alpha. They are dialect-correct and unit-tested, but they have not yet run against a live account, and that is a real difference from a tool with years of production mileage on those platforms.

What Interlace is for is the common case: a team whose warehouse fits on one machine, who are running four tools to do one job, and who would rather spend the effort on pipelines than on the glue between them.

The next post is about the decision that made it possible — what a model actually compiles to, and why that choice determines everything else.

Interlace is MIT-licensed and requires Python 3.12+. Start with the
introduction, or install it:

pip install 'interlaced[service]'

DEV Community: Mark Alford

Two Frameworks - dbt & SQLMesh, One Owner

The part where we do not cry wolf

The part that is actually true

Clean models, in Python or SQL

Four things that follow

Where we are a cousin, not an original

What you give up

interlace.sh: A Macro Is an Expression, Not a Template

What a macro actually is

Where do you expand it?

One definition, every dialect

Editing a macro rebuilds its callers

Lineage sees the body, not just the call

The rules

What you give up

Migrating jaffle_shop: A Real dbt Project, End to End

Seeds become models

Four of five models: a two-line regex

The one place the mechanical pass broke

The fifth model is the real work

Option 1 — a Python model

Option 2 — a dynamic model

Both were checked

Tests become checks

What this does not tell you

No Jinja: What Replaces Templating When SQL Is an AST

Job one: {{ ref() }} — declaring dependencies

Job two: {% for %} — generating many models or many columns

Job three: macros — reusable SQL fragments

Job four: {{ config() }} — per-model settings

What the AST buys that text cannot

What you give up

Python Models: dbt and Interlace, Side by Side

Where you can run one

What a model actually is

What you can materialise

Incremental work

Side by side

Choosing

Twenty-Five Million Rows in Five Seconds, and How to Check

The shape of the work

The numbers

The parts that are not about speed

Run it yourself

What this does not show

interlace.sh: A Python Model Is Just a Function

The graph

The Python model

The SQL either side

What makes it work

Where the symmetry actually stops

Testing it

interlace.sh: Sandboxes That Cost Nothing

Fingerprints first

Why the sandbox is free

Promotion is a view swap

Reclaiming the space

Sandboxes cannot touch production systems

What it costs

interlace.sh: The IR Is a sqlglot AST

Why not Ibis

What an AST IR buys

Arrow at the boundaries

What the rebuild refused

The shape it produced

interlace.sh: Why We Built a Unified Abstraction

Where failures actually live

Software engineering solved this

One abstraction

What collapses

One abstraction is not enough

What this does not replace

Job one: `{{ ref() }}` — declaring dependencies

Job two: `{% for %}` — generating many models or many columns

Job four: `{{ config() }}` — per-model settings