Keep the Runner Dumb

A lot of my data work is one machine, DuckDB, a file in, a file out. Read a CSV, reshape it with some SQL, check it isn’t garbage, write a Parquet. The standard tooling answer to that is Airflow for scheduling, dbt for the SQL, a metadata database to track everything, and a scheduler to tie it together. That’s cluster-scale machinery wrapped around a laptop-scale problem, and most of it solves problems I don’t have.

So I built the opposite, mostly to see how small it could stay. It’s called pktl (pronounced pickle), and it’s a single-statement SQL runner for DuckDB. A whole pipeline is one .sql file with a few comment directives:

--@pipeline load_orders
--@param source PATH INPUT

--@step land
CREATE OR REPLACE TABLE orders AS
  SELECT * FROM read_csv_auto(@{source});

--@assert no_null_id EXPECT_NO_ROWS
SELECT * FROM orders WHERE id IS NULL;

--@step export
COPY orders TO 'out/orders.parquet' (FORMAT parquet);

You run it, and it reads the input, runs your SQL top to bottom, checks the data properties you asserted, writes the output, emits a structured JSON report of what happened, and exits. No daemon, no database of its own, no scheduler. On purpose — and the “on purpose” is the whole post.

The tool is mostly a list of things it won’t do

The interesting part of pktl isn’t a feature. It’s a boundary, held together by one question I ask of every idea: does this belong in the runner, or in the layer above it?

There are three layers, and each has one job. Something outside — cron, a CI job, a bash script — decides when to run and with what parameters. pktl runs one SQL pipeline and checks the data. DuckDB executes the SQL. Each layer is replaceable without touching the others, and nothing reaches across. If a request belongs to the layer above — “can it schedule?”, “can it retry on a timer?”, “can it track runs over weeks?” — it stays out. Not because it’s hard, but as a principle: the moment the runner starts scheduling, it’s competing with cron, and losing.

The sharpest version of this is that pktl refuses to parse your SQL. It has no idea what your query means; it hands the text to DuckDB and gets out of the way. Which means no macros, no templating, no {{ ref() }}. That’s a deliberate cliff, because I’ve watched where the other road goes: you add text macros, then people want parameters inside the macros, then conditionals, then loops, and now you’ve built a templating language that sits on top of SQL and fights it — the way dbt slowly became Jinja-on-SQL. SQL already has abstraction: views, CTEs, functions. Declining to bolt on a second, worse one isn’t a missing feature. It is the feature.

Assertions belong inside the run, not after it

The one thing I did make first-class is data-quality checks — because that’s the part that actually bites, and most setups put it in the wrong place.

The usual pattern is: run the pipeline, write the output, then run a separate test suite against what you wrote. Which means by the time you learn the data is bad, it’s already in the table. In pktl an assertion is a step like any other — one SQL query, run in sequence, that halts the pipeline if it fails. The no_null_id check above runs before the export; if a null id shows up, nothing gets written. The bad data never lands.

Two details I’m quietly proud of. First, there are exactly three kinds of assertion — “these rows should not exist,” “this should be true,” “this number should be within a bound” — and there are three because a SQL query can only hand you back three shapes of answer: a table, a boolean, or a number. It’s a closed set, not a framework that grows a fourth mode next quarter. Second, the exit code tells you who to wake up. “The data arrived dirty” and “the script is broken” are different failures with different owners, so they get different exit codes — one points at whoever owns the source, the other points at me. Most pipelines collapse both into a generic non-zero and make you read logs to find out which of your problems you have.

Holding no state is a feature you get to keep

pktl’s working tables live in an in-memory DuckDB that is born when the process starts and dies when it exits. There’s no run-history database, no state to migrate, no lock to get wedged on.

That sounds like a limitation until you notice what it buys. A failed run leaves nothing behind to corrupt the next one — the intermediate tables simply evaporate. Which means there’s no “resume from step 4” feature, and there doesn’t need to be: re-running from the top is cheap and clean, so that’s the resume story. Every stateful convenience I didn’t add is a whole category of bug I never have to write — no half-committed runs, no stale checkpoints, no “it works unless the previous run crashed at exactly the wrong moment.”

The hard part was saying no

None of this is clever technology. It’s DuckDB, SQL, and a few hundred lines of glue; I had the first working version in a handful of days. The work wasn’t in what it does. It was in the steady refusal to let it do more — because a small sharp tool asks, every single day, to become a big blunt one. Someone needs scheduling, so why not a --cron flag. Someone needs a macro, just a small one. Someone wants run history, only a little table.

Each of those is reasonable on its own, and each one, taken, would trade the thing that makes the tool worth using — that you can hold all of it in your head — for one more capability it does adequately. It’s the same discipline I keep writing about in code, pointed at a tool instead of a class: the value isn’t the abstraction you add, it’s the ones you have the nerve to leave out. Keep the runner dumb. Let the smart parts live where they already work.

Companion code: ramwise-examples/sql-pipeline-runner — a compile→run→assert→emit runner on SQLite; assertions gate the run and exit codes name the owner.