Orchestrate SQLMesh and DuckDB engine

Run SQLMesh data transformations end to end on Kestra: pull a SQLMesh project from a Git repository, execute sqlmesh plan --auto-apply to build the models, and query the resulting DuckDB database in the same flow. This blueprint shows how to combine version-controlled SQL modeling, an embedded analytical engine, and a declarative orchestrator so your data team can ship reproducible analytics pipelines without managing extra infrastructure.

How it works

1. A io.kestra.plugin.core.flow.WorkingDirectory task creates a shared filesystem for the SQLMesh project and its outputs.
2. Inside it, io.kestra.plugin.git.Clone checks out the TobikoData/sqlmesh-examples repository from main.
3. io.kestra.plugin.sqlmesh.cli.SQLMeshCLI then changes into 001_sushi/1_simple and runs sqlmesh plan --auto-apply, materializing the models into db/sushi-example.db which is captured via outputFiles.
4. A downstream io.kestra.plugin.jdbc.duckdb.Queries task receives that file through inputFiles, attaches it as data.db, and runs SELECT * FROM sushisimple.top_waiters with fetchType: STORE so the result is stored as a Kestra output for previews, downloads, or downstream tasks.

What you get

• A reproducible SQLMesh build triggered from Kestra, not a developer laptop.
• DuckDB analytics on the freshly built warehouse file in the same flow.
• Output files and query results persisted as first-class Kestra artifacts.
• A clear pattern for chaining a transformation engine with an ad hoc query engine.

Who it's for

• Analytics engineers adopting SQLMesh who want scheduled, audited runs.
• Data teams using DuckDB as a lightweight warehouse for dev or CI environments.
• Platform engineers standardizing how transformation tools are launched in production.

Why orchestrate this with Kestra

SQLMesh ships a powerful CLI but no scheduler, environment manager, or downstream hand-off. Kestra adds event and cron triggers, automatic retries on flaky Git or build steps, full execution lineage across the clone, plan, and query tasks, and declarative YAML you can review in pull requests. The DuckDB query step then consumes the SQLMesh output file directly, something a standalone SQLMesh run cannot coordinate on its own.

Prerequisites

• A reachable Git repository containing a SQLMesh project (this example uses the public TobikoData/sqlmesh-examples repo, no auth required).
• The io.kestra.plugin.sqlmesh and io.kestra.plugin.jdbc.duckdb plugins available on your Kestra worker.

Secrets

This blueprint does not reference any {{ secret('NAME') }} values. If you point it at a private Git repository, add username and password properties on the Clone task and store the token in a secret such as GIT_TOKEN.

Quick start

1. Copy this flow into your Kestra instance under a namespace like company.team.
2. Optionally swap the url and branch on git_clone for your own SQLMesh project.
3. Adjust beforeCommands to cd into your project directory and update the commands list if you want extra SQLMesh steps such as sqlmesh audit.
4. Trigger an execution and inspect the DuckDB query results in the query task outputs.

How to extend

• Replace the static SELECT in the query task with a templated SQL file driven by flow inputs.
• Add a schedule trigger to run the SQLMesh plan nightly, or a webhook trigger to run it on every merge to main.
• Push the resulting DuckDB file to object storage (S3, GCS, Azure Blob) after the run so downstream consumers can fetch it.
• Chain a dbt, Python, or notification task after the query to act on the freshly modeled data.

Links

• Plugin docs: https://kestra.io/plugins/plugin-sqlmesh
• DuckDB plugin docs: https://kestra.io/plugins/plugin-jdbc-duckdb
• DuckDB orchestration overview: https://kestra.io/orchestration/duckdb