dlt to DuckDB Analytics Chain: Ingest, Aggregate, Publish

Ingestion that nobody queries is just storage costs. This blueprint chains the two halves in one execution: io.kestra.plugin.dlt.CLI runs a pipeline script that lands a REST API into a DuckDB file, the file travels through Kestra's internal storage to io.kestra.plugin.jdbc.duckdb.Query, which computes completion KPIs with plain SQL, and Discord receives the numbers seconds after the data lands. No warehouse server, no handoff between two schedulers, no dashboard polling a table that might be mid-load.

How it works

1. load_events (io.kestra.plugin.dlt.CLI) receives the pipeline script through inputFiles and executes it with commands. dlt writes analytics.duckdb in the working directory (with DLT_DATA_DIR keeping pipeline state there too, so every run is self-contained), and outputFiles uploads it to Kestra's internal storage.
2. aggregate (io.kestra.plugin.jdbc.duckdb.Query) opens that file through its databaseUri property, so the SQL queries exactly the file this execution produced, never a half-loaded shared database.
3. fetchType: FETCH_ONE exposes the KPI row as outputs.aggregate.row.*, which publish_kpi interpolates into a Discord message.
4. One errors block covers the chain; the execution view shows precisely which half failed.

What you get

• Data and KPIs that are consistent by construction, since the aggregation reads the exact artifact the load produced.
• A serverless analytics stack: dlt for ingestion, a DuckDB file for storage, SQL for metrics.
• Outputs at every joint, so any task's result can feed further tasks, conditions, or alerts.
• The dlt.CLI pattern with inputFiles and outputFiles, ready for repos of pipeline scripts synced from Git.

Who it's for

• Analytics engineers who want ingestion and metric computation in one place instead of two schedulers.
• Small data teams standardizing on DuckDB files instead of a warehouse server.
• Anyone publishing daily KPIs to chat and tired of screenshots of dashboards.

Why orchestrate this with Kestra

The chain is the point: dlt does not schedule SQL, DuckDB does not fetch APIs, and Discord knows nothing about either. Kestra sequences the three with typed file passing through internal storage, retries per task, and a single execution history entry that answers what ran, what it computed, and who was told. Swapping any link, another source, Postgres instead of a DuckDB file, Slack instead of Discord, changes one task, not the architecture.

Prerequisites

• A Discord incoming webhook.
• For real sources, API credentials passed to the dlt task through env from secrets.

Secrets

• DISCORD_WEBHOOK_URL: Discord incoming webhook URL.

Quick start

1. Add the DISCORD_WEBHOOK_URL secret to your Kestra namespace.
2. Execute the flow; Discord should report the todo completion KPI within a minute.
3. Replace the resource with your real API, adjust the SQL, and enable the daily trigger.

How to extend

• Keep pipeline scripts in Git and load them with namespaceFiles instead of inline inputFiles.
• Store the aggregate with fetchType: STORE and feed it to further tasks, or into the dlt freshness monitor blueprint's warehouse.
• Swap the KPI target: the same row outputs interpolate into Slack, email, or an HTTP request to your metrics API.
• For bigger data, point dlt at MotherDuck by changing the destination and credentials; the chain shape survives.

Links

• dlt plugin documentation
• DuckDB plugin documentation