How much does it take to set up a reliable data pipeline? Less than many reference architectures say: Parquet files, DuckDB to query them, dbt to transform and test, and a scheduler. Here you build that minimal architecture on a single computer, run it, and put it to the test with a faulty load to see what stops it. Then you see when it stops being enough and what to add, one piece at a time.
Prerequisites. SQL and the command line; the previous articles in the series help but are not required.
What "minimal" means
A minimal architecture is not the one with the fewest tools, but the one in which every piece solves a problem you already have. The starting point is a concrete problem: every night order files arrive from a management system, and in the morning someone wants a revenue table by day and country that can be trusted. Five tasks follow from it, and for each one I choose the simplest tool that does the job.
- keep the raw data without modifying it: Parquet files, one folder per day;
- query and transform them: DuckDB, which reads those files directly;
- decide whether the result is reliable: dbt's tests, run before publishing;
- publish the result: a Parquet file that consumers read;
- start everything every night: a system timer, until something else is needed.
The order matters here: the raw data come first because they are the only thing that cannot be recovered, publication comes after the tests because it is the only thing others see. They are all pieces that the previous articles in the series cover one by one (DuckDB, DuckDB with Polars, table formats, lakeFS); here I put them together.
Versions, licenses and news about dbt
The tools used, at the consultation date 2026-11-13: DuckDB 2.0.0 (MIT), dbt Core 1.12.7 with the dbt-duckdb 1.11.1 adapter, all installed with pip in virtual environments. Before copying the commands, read one piece of news: pip install dbt now installs dbt v2, a rewrite in Rust described in the documentation as the current experience, whose source code is under Apache 2.0 while the distribution you install is released under a dbt product license, presented as free to use. Version 1 (Python) continues to exist on the 1.latest branch. In this article I used dbt Core 1.12.7, version 1, because the dbt-duckdb adapter declares in its README that it targets dbt-core from 1.8 onwards; if you work with v2, first verify that the adapter for DuckDB is supported.
The practical point is that "dbt" no longer denotes a single product: write in the project which version you use and pin the version in the requirements. The same holds for any piece of the stack, and the lesson on lakeFS's license in the previous article is of the same kind.
The raw layer: immutable files, one per day
The script creates 5 days of orders (20,000 rows a day) and a master list of 2,000 customers, with values derived from the row index through hash() so they are the same on every run. The structure is raw/ordini/giorno=2026-11-0N/ordini.parquet (ordini is orders, giorno is day): the folder is named like the partition column, so DuckDB reads it on its own. A rule to copy: a load adds a new folder and never modifies the old ones. The names of the data, kept in Italian as in the original run, are clienti (customers), cliente_id, paese (country) and importo (amount).
import pathlib, shutil
import duckdb
raw = pathlib.Path("mva/raw")
shutil.rmtree(raw, ignore_errors=True)
con = duckdb.connect()
# customer master list: 2,000 rows, always the same
(raw / "clienti").mkdir(parents=True)
pathlib.Path("mva/out").mkdir(exist_ok=True) # dbt-duckdb does not create the folder for external files
con.execute(f"""
COPY (SELECT i AS cliente_id,
(['IT','DE','FR','ES','NL'])[1 + (hash(i) % 5)::INT] AS paese
FROM range(2000) t(i)) TO '{raw}/clienti/clienti.parquet' (FORMAT parquet)""")
# orders: one file per day, as a nightly ingestion job would leave them
for day in range(1, 6):
folder = raw / "ordini" / f"giorno=2026-11-0{day}"
folder.mkdir(parents=True)
con.execute(f"""
COPY (SELECT {day} * 100000 + i AS order_id,
(hash(i * {day}) % 2000)::INT AS cliente_id,
round(((hash(i + {day} * 7) % 20000) / 100.0)::DOUBLE, 2) + 1 AS importo
FROM range(20000) t(i)) TO '{folder}/ordini.parquet' (FORMAT parquet)""")
n = con.execute(f"SELECT count(*), count(DISTINCT order_id) FROM read_parquet('{raw}/ordini/*/*.parquet')").fetchall()[0]
print("order files:", len(list(raw.glob("ordini/*/*.parquet"))), "| rows:", n[0], "| distinct order_id:", n[1])
print("customers:", con.execute(f"SELECT count(*) FROM '{raw}/clienti/clienti.parquet'").fetchall()[0][0])
# Output:
# order files: 5 | rows: 100000 | distinct order_id: 100000
# customers: 2000
The rows are 100,000 and the distinct order_id values are 100,000: the uniqueness tests you will write later start from this fact. The out/ folder for the published files is created here because dbt-duckdb does not create it by itself: the run discovered it, with a file-not-found error on the first attempt.
The dbt project: three models and six tests
The project is small. Two staging models (stg_ordini and stg_clienti) are views that read the Parquet files with read_parquet and give clean names and types. The final model ricavi_giornalieri (daily revenue) joins orders and customers and computes the orders and revenue by day and country; it is declared with materialized='external' and a location, which with the dbt-duckdb adapter writes the result as a Parquet file instead of as a table in the database. The tests are in the YAML file.
{{ config(materialized='external', location='out/ricavi_giornalieri.parquet') }}
select
o.giorno,
c.paese,
count(*) as ordini,
round(sum(o.importo), 2) as ricavi
from {{ ref('stg_ordini') }} o
join {{ ref('stg_clienti') }} c using (cliente_id)
group by o.giorno, c.paese
The model is a SQL query with a configuration header: ref('stg_ordini') tells dbt which models it depends on and therefore in what order to run them, and using (cliente_id) joins the two tables on the key. The tests instead describe the rules on the data and live next to the models, in a YAML file:
version: 2
models:
- name: stg_ordini
columns:
- name: order_id
data_tests: [not_null, unique]
- name: cliente_id
data_tests:
- not_null
- relationships:
arguments: {to: ref('stg_clienti'), field: cliente_id}
- name: importo
data_tests: [not_null]
- name: stg_clienti
columns:
- name: paese
data_tests:
- accepted_values: {arguments: {values: ['IT', 'DE', 'FR', 'ES', 'NL']}}
The tests say what "reliable" means: the order identifier is there and is unique, the customer is there and exists in the master list (relationships), the amount is there, the country is among the five allowed. Note the syntax of relationships: the arguments sit under the arguments key. My first attempt wrote them inline, and dbt replied with a deprecation warning (MissingArgumentsPropertyInGenericTestDeprecation); the correct form is the one in the file.
A complete run
The dbt build command runs models and tests in dependency order. The listing launches it and cleans dbt's output of times, durations and numbering, which change on every run, then checks the result on its own: the total revenue of the published file must coincide with the sum of the amounts in the raw files.
import duckdb
from dbtlib import dbt
code, lines = dbt("build")
print("dbt build exit code:", code)
print("\n".join(lines))
# cross-check: the mart's revenue must coincide with the sum of the raw files
mart = duckdb.sql("SELECT count(*), round(sum(ricavi), 2) FROM 'mva/out/ricavi_giornalieri.parquet'").fetchall()[0]
raw_sum = duckdb.sql("SELECT round(sum(importo), 2) FROM read_parquet('mva/raw/ordini/*/*.parquet')").fetchall()[0][0]
print("mart rows:", mart[0], "| revenue in the mart:", mart[1], "| sum in the raw files:", raw_sum)
# Output:
# dbt build exit code: 0
# Done. PASS=9 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=9
# OK created sql external model main.ricavi_giornalieri .. [OK]
# OK created sql view model main.stg_clienti .. [OK]
# OK created sql view model main.stg_ordini .. [OK]
# PASS accepted_values_stg_clienti_paese__IT__DE__FR__ES__NL .. [PASS]
# PASS not_null_stg_ordini_cliente_id .. [PASS]
# PASS not_null_stg_ordini_importo .. [PASS]
# PASS not_null_stg_ordini_order_id .. [PASS]
# PASS relationships_stg_ordini_cliente_id__cliente_id__ref_stg_clienti_ .. [PASS]
# PASS unique_stg_ordini_order_id .. [PASS]
# mart rows: 25 | revenue in the mart: 10132463.4 | sum in the raw files: 10132463.4
There are nine nodes among views, tests and the external model, all passed, and the mart has 25 rows: 5 days times 5 countries. The sum of the revenue (10,132,463.40) coincides with that of the raw files, which is the independent check: a wrong join that lost or duplicated orders would have broken it. dbt's tests check the incoming data; the cross-check checks the result.
The gate: what happens with bad data
A pipeline is judged by how it behaves when the data are wrong. The listing adds a folder for a sixth day with three faulty rows: an order_id that already exists, a null customer, a customer that is not in the master list. Then it reruns dbt build and verifies a fact that can escape a reader: the published file stayed identical, byte for byte, by comparing a SHA-256 fingerprint before and after. At the end the defect is cleaned up.
import hashlib, pathlib, shutil
import duckdb
from dbtlib import dbt
mart = pathlib.Path("mva/out/ricavi_giornalieri.parquet")
fingerprint = lambda: hashlib.sha256(mart.read_bytes()).hexdigest()[:12]
before = fingerprint()
# a faulty load: an order_id already seen, a null customer, a customer that does not exist
bad = pathlib.Path("mva/raw/ordini/giorno=2026-11-06")
bad.mkdir()
duckdb.sql(f"""COPY (SELECT * FROM (VALUES (100000, 5, 10.0), (600001, NULL, 20.0), (600002, 99999, 30.0))
t(order_id, cliente_id, importo)) TO '{bad}/ordini.parquet' (FORMAT parquet)""")
try:
code, lines = dbt("build")
print("dbt build exit code:", code)
print("\n".join(r for r in lines if not r.startswith("PASS ")))
print("the mart stayed the same as before:", fingerprint() == before)
finally:
shutil.rmtree(bad) # I clean up the faulty load
# Output:
# dbt build exit code: 1
# Done. PASS=5 WARN=0 ERROR=3 SKIP=1 NO-OP=0 REUSED=0 TOTAL=9
# FAIL 1 not_null_stg_ordini_cliente_id .. [FAIL 1]
# FAIL 1 relationships_stg_ordini_cliente_id__cliente_id__ref_stg_clienti_ [FAIL 1]
# FAIL 1 unique_stg_ordini_order_id .. [FAIL 1]
# OK created sql view model main.stg_clienti .. [OK]
# OK created sql view model main.stg_ordini .. [OK]
# SKIP relation main.ricavi_giornalieri .. [SKIP]
# the mart stayed the same as before: True
Three tests fail (null customer, relationship with the master list, uniqueness of the identifier), dbt exits with code 1 and the ricavi_giornalieri model is skipped: SKIP. dbt counts the failed tests as ERROR in the summary. It is the behavior you want: consumers keep reading yesterday's file, correct but dated, instead of today's file, up to date but wrong. An old and correct file is almost always better than a new and wrong one, and it is almost always better that someone knows about the difference: you need an alert on the failure, which the timer alone does not give.
Starting it every night
For a single daily job you do not need an orchestrator: a systemd unit with its timer is enough. The systemd documentation specifies that with Type=oneshot you can give several ExecStart= lines, executed in sequence, and that Persistent=true makes up for a missed run if the machine was off. Watch out for comments: in a unit file a comment goes on its own line and not at the end of a value. There are two files, the service and the timer; I did not install them, they are schemas to adapt.
# /etc/systemd/system/data-pipeline.service (schema to adapt, not executed: I did not install any unit)
[Unit]
Description=Nightly data pipeline (ingestion and dbt build)
[Service]
Type=oneshot
User=pipeline
WorkingDirectory=/opt/pipeline
# if a command fails, systemd marks the unit as failed and the following ones do not start
ExecStart=/opt/pipeline/venv/bin/python ingestion.py
ExecStart=/opt/pipeline/venv-dbt/bin/dbt build --profiles-dir . --no-version-check
The service describes what to run: first the ingestion, then dbt build, one after the other, and if the first fails the second does not start. The timer describes when:
# /etc/systemd/system/data-pipeline.timer
[Unit]
Description=Runs the data pipeline every night
[Timer]
OnCalendar=*-*-* 02:00:00
# if the machine was off at 02:00, catch up at startup
Persistent=true
[Install]
WantedBy=timers.target
The following block checks the only part verifiable without installing anything, the OnCalendar expression, by asking systemd-analyze calendar to interpret it:
import subprocess
# systemd-analyze calendar interprets the OnCalendar expression without starting anything
r = subprocess.run(["systemd-analyze", "calendar", "*-*-* 02:00:00"], capture_output=True, text=True)
print(r.stdout.splitlines()[0])
# Output:
# Normalized form: *-*-* 02:00:00
The service declares an unprivileged user and a working folder, so as not to write where it should not. The failure of a command makes the unit fail: you configure the alert separately (an OnFailure= line pointing to another unit that sends the notification, for example), because the timer writes to no one. An orchestrator is needed when the jobs become more than one and depend on each other.
When to add an orchestrator, a table format, versioning
The honest answer is "when you need what it gives you", not before. An orchestrator like Apache Airflow (3.4.0, Apache-2.0 on PyPI) or Kestra (open source edition Apache-2.0, documentation at version 2.0) brings retries, dependencies between jobs, an interface that shows what failed and when; it costs one more service to keep alive and updated. A table format like Iceberg or Delta brings atomic commits and time travel on the published table. A system like lakeFS brings branches and merges over the whole lake, with the license and the management that the dedicated article explains.
This is the list of signals that say "now you need it", with the corresponding piece, but each item is independent of the others:
- several jobs with dependencies or retries to repeat: an orchestrator;
- consumers that read while you write and see half-written files: a table format;
- need to try a transformation on a copy of production data: branches, for example with lakeFS;
- several processes writing together to the same DuckDB database: the single-writer constraint described in the article on DuckDB;
- continuous streams or volumes that do not fit on one machine: another engine, outside this architecture.
Start from the signal you really have and solve it with the smallest possible tool. For the second one you can first try writing the file to a temporary folder and renaming it at the end of the write, before adopting a table format. The last two are real limits of this architecture: if you meet them, you have outgrown it.
What I did not measure
In this article there is no figure for cost, latency or market share: I did not measure them and did not find them in a primary source. The figure you can measure yourself, with the same scripts, is the time of a complete run on your hardware and your volumes. For the cost of a managed service, the vendor's price list on the day you decide applies, not this article.
What you can do now
You can assemble a daily pipeline with immutable raw data, transformations and tests in dbt, publication in Parquet and scheduling with systemd; you can prove that a faulty load does not touch the published file; you know which signals say it is time to add an orchestrator, a table format or versioning; and you know that dbt's v2 changes the distribution and the license compared with the v1 used here.
Frequently asked questions
Do I need Airflow for a daily pipeline?
Not for a single job: a system timer runs the command and dbt stops by itself when a test fails. Airflow or Kestra are needed when the jobs are more than one, have dependencies or require repeated retries and a control interface.
Does dbt work with DuckDB?
Yes, with the dbt-duckdb adapter: in the article I ran dbt Core 1.12.7 with dbt-duckdb 1.11.1 and DuckDB 2.0.0. With dbt v2, now installed by default by pip install dbt, check the adapter's support before switching.
How do I notice that the nightly load has failed?
The systemd service shows as failed and dbt exits with code 1, but no one is alerted on their own. Add a notification, for example with OnFailure= pointing to another unit, and also check the date of the last published file.
Does this architecture handle millions of rows?
In this series' tests I used from 100 thousand to 20 million rows on a computer with 12 logical threads and 30 GB of RAM, without problems. Beyond that I did not measure: the limit depends on memory, disk and write concurrency, and must be measured on your data.
Sources
- dbt Developer Hub: Install dbt - dbt v2 installed by default, Apache 2.0 runtime and product license, consulted on 2026-11-13
- GitHub: dbt-labs/dbt-core - the main branch contains dbt v2 in Rust, v1 is developed on 1.latest, consulted on 2026-11-13
- GitHub: duckdb/dbt-duckdb - supported dbt-core versions and the
externalmaterialization, consulted on 2026-11-13 - DuckDB: Release Calendar - version 2.0.0, consulted on 2026-11-13
- systemd.service(5) - several
ExecStart=withType=oneshot, consulted on 2026-11-13 - systemd.timer(5) -
OnCalendar=andPersistent=, consulted on 2026-11-13 - PyPI: apache-airflow - version 3.4.0 and Apache-2.0 license, consulted on 2026-11-13
- Kestra: Open-Source vs Enterprise Edition - differences between editions, consulted on 2026-11-13
The ingestion scripts, the dbt build, the gate and the OnCalendar check were run with Python 3.14.4, DuckDB 2.0.0, dbt Core 1.12.7 and dbt-duckdb 1.11.1, twice with identical output after the normalization described. The two systemd unit files were neither installed nor run. Of the dbt project files the listings show the final model ricavi_giornalieri and the YAML file with the tests; the two staging models (stg_ordini.sql and stg_clienti.sql), dbt_project.yml, profiles.yml and the helper module dbtlib that the scripts import are not shown and you have to create them yourself.

Commenti
Caricamento commenti...
Sign in to comment