Build governed analytics assets with DuckDB using data lineage, staging, and mart layers

Turn a plain SQL data pipeline into a fully governed analytics workflow with DuckDB and Kestra Assets. This blueprint materializes staging and mart tables in DuckDB (backed by MotherDuck), registers each result as a first-class Kestra asset, and automatically builds a data lineage graph from external source to staging to marts. It solves the common problem of untracked, undocumented SQL tables that nobody can trace, audit, or trust.

How it works

• create_staging_layer_asset runs io.kestra.plugin.jdbc.duckdb.Query to build a trips table from sample_data.nyc.taxi. It declares sample_data.nyc.taxi as an input asset and registers trips as an output io.kestra.plugin.ee.assets.Table with metadata.model_layer: staging.
• for_each is an io.kestra.plugin.core.flow.ForEach over passenger_count and trip_distance.
• For each value, create_mart_layer_asset runs a DuckDB Query computing AVG({{taskrun.value}}), declares trips as its input asset, and registers avg_{{taskrun.value}} as an output Table asset with metadata.model_layer: mart.
• pluginDefaults points every DuckDB task at MotherDuck via jdbc:duckdb:md:my_db and sets fetchType: STORE.

What you get

• A staging table and dynamically generated mart tables (avg_passenger_count, avg_trip_distance).
• An automatic lineage graph: external source to trips to mart aggregates.
• Asset metadata (model_layer) that makes layers discoverable in the catalog.
• Reusable, traceable analytics outputs for downstream pipelines and dashboards.

Who it's for

• Analytics engineers adopting staging and mart modeling patterns.
• Data platform teams that need lineage and governance without manual docs.
• DuckDB and MotherDuck users building lightweight, governed warehouses.

Why orchestrate this with Kestra

DuckDB executes SQL but has no scheduler, no lineage tracking, and no asset catalog. Kestra adds declarative YAML pipelines, event and schedule triggers, retries, and full execution observability. Crucially, Kestra Assets capture input and output relationships across tasks, so lineage, dependency graphs, and impact analysis come for free, something a DuckDB connection alone cannot provide.

Prerequisites

• A Kestra Enterprise Edition instance (Assets are an EE feature).
• A MotherDuck account and the DuckDB JDBC driver available to the runner.

Secrets

• MOTHERDUCK_TOKEN: the MotherDuck access token used in the DuckDB JDBC URL.

Quick start

1. Add the MOTHERDUCK_TOKEN secret to your Kestra instance.
2. Import this blueprint into a namespace.
3. Execute the flow to materialize the staging and mart tables.
4. Open the asset catalog and lineage view to inspect the generated graph.

How to extend

• Swap sample_data.nyc.taxi for your own source tables.
• Add more metrics to the ForEach values list to generate extra marts.
• Enrich asset metadata with owners, descriptions, or quality tags.
• Add a schedule or event trigger to refresh the marts automatically.

Links

• Plugin docs: DuckDB
• Orchestrate DuckDB with Kestra