Stage a pipeline change

This example walks a change through a staged deployment from start to finish. You add a model to the playground project, change the model it reads from, check the new version, promote it, and then roll it back. The query that reads the result stays the same the whole time. You need Python 3.12 or newer and Docker.

pip install --upgrade sqltide
sqt playground orders-stage
cd orders-stage

The playground runs ClickHouse with three orders and one model, order_report. The project uses virtual mode: a build puts the new version next to the current one, and nothing your queries read changes until you promote it.

1. Add a model

Save this model as pipelines/pl__orders/large_orders.sql:

MODEL (
  engine "MergeTree()",
  order_by ["order_id"]
);

SELECT
  order_id::UInt64 AS order_id,
  total_cents::Int64 AS total_cents,
  _replay_partition::Int32 AS _replay_partition,
  _replay_offset::Int64 AS _replay_offset
FROM __ref("order_report")
WHERE total_cents >= 1000

It reads order_report and keeps orders of 10.00 or more.

Save the query consumers run as large.sql:

SELECT order_id, total_cents FROM tbl__large_orders ORDER BY order_id;

2. Publish both models

sqt build
sqt deployment promote --latest
sqt playground query large.sql

Type y when sqt build asks. The query returns orders 1, 2 and 3.

3. Change the upstream model

cp revisions/completed_orders.sql pipelines/pl__orders/order_report.sql
sqt plan
sqt build

order_report now keeps only completed orders. The plan rebuilds it and large_orders, which reads it, from the source table. The build creates both new tables beside the published ones.

4. Inspect the new version

sqt deployment list
sqt deployment diff --latest
sqt deployment audit --latest
sqt playground query large.sql --compare

list shows the published deployment as active and the new one as staged. diff lists the models that changed. audit compares the new tables with the published ones and reports ready, caution or not_ready. --compare runs large.sql against both versions: consumers still get orders 1, 2 and 3, and the new version returns 1 and 3.

New rows reach both versions while you inspect:

sqt playground query live.sql
sqt playground query large.sql --compare

Consumers now get orders 1 to 4; the new version gets 1, 3 and 4.

5. Promote

sqt deployment promote --latest
sqt playground query large.sql

The same query now returns orders 1, 3 and 4.

6. Roll back

sqt deployment rollback --previous
sqt playground query large.sql

Type y to confirm. The stable names point at the first version again, which kept receiving rows: the query returns orders 1 to 4.

7. Clean up

sqt playground down --volumes

What to keep in mind

• Promotion switches the stable names one at a time. While it runs, a query that reads two models can see the new version of one and the old version of the other.
• A new version is built from the rows the source still holds. Rows near the cutoff can reach the new tables twice, so write models that tolerate a repeated row.
• Rollback switches names back. It does not remove rows the newer version wrote.
• sqt deployment promote refuses a version built on an upstream model version that is no longer published. Rebuild the model and promote the new build.

See staged deployments for audits, partial promotion and cleanup, and direct builds for the default mode without a publish step.