A Tiny ETL Binary Competes With curl, jq and SQLite in a Cron Job, So Build It That Small
The real competitor is a shell script in a crontab, and it usually looks like this:
curl -s "https://api.example.com/v1/launches?limit=100" \
| jq -r '.data[] | [.id, .name, .net] | @csv' \
| sqlite3 -csv launches.db ".import /dev/stdin launches"
It works on the day you write it. Then the API answers 429 and curl pipes an error page into jq. Or the API has a second page, and the script never asks for it. When the job dies halfway, the rerun inserts the same rows again or trips over the primary key, depending on how the table was made. A field that starts arriving as a string goes unnoticed until a chart looks wrong. Retries, backoff, pagination, incremental state, idempotent writes and schema drift: that’s the list, and shell scripts get every item on it wrong in predictable ways. (To be fair, curl --retry covers the first two.)
A tiny ETL binary wins by doing those six things right and nothing else: one static binary around 10 MB, a config you can read at a glance, SQLite and stdout as sinks. Everything past that is how it stops being tiny. A tool that grows a scheduler, a UI and a plugin system has become the thing it was meant to replace, and now it’s competing with Airflow instead of a crontab.
Who Else Is Here
Benthos, now Redpanda Connect, is the closest in shape: a Go binary configured in YAML with inputs, processors and outputs. Redpanda acquired it in 2024 and moved parts of it under a more restrictive license, and WarpStream forked the project as Bento. It does far more than a nightly pull, and the price of that generality is a config language to learn. Vector and Fluent Bit are built for logs and metrics, so their model is a stream of events rather than a table you keep current. Singer (with Meltano to run it) and Airbyte sit on the ELT side. Singer’s taps and targets pass JSON between processes and keep state as bookmarks; Airbyte has a large connector catalog and runs as a service with a UI. dlt is a Python library that loads API data into a database with schema inference and incremental cursors, and if you already live in Python it’s a fine answer. sling moves data between databases, files and object storage. sqlite-utils, Simon Willison’s CLI and library, does the JSON-into-SQLite step well, with upserts by primary key, but it starts after the HTTP part is done. Getting the JSON is still your problem.
The gap those leave is small and common: one HTTP API, one table or file, one machine, one cron entry. Past that (several sources, real transformation logic, a warehouse, more than one box) pick one of the tools above and don’t apologize for it.
Fifteen Lines
Here’s a config for the script above, written as a proposal for a tool that doesn’t exist yet. The command, etl run launches.toml, is a placeholder name.
[source]
url = "https://api.example.com/v1/launches"
header = "Authorization: Bearer ${EXAMPLE_TOKEN}"
paging = "cursor" # cursor | link | page
cursor = { param = "cursor", next = "$.meta.next" }
rows = "$.data[*]"
since = { param = "updated_since", field = "$.updated_at", overlap = "10m" }
[sink]
sqlite = "launches.db"
table = "launches"
key = "$.id"
keep = ["$.name", "$.net", "$.pad.name"] # promoted to columns
retry = { max = 6, base = "1s" }
Each line replaces something the script got wrong. paging and cursor walk every page instead of the first. since sends updated_since from saved state, so a run after a quiet hour asks for almost nothing. key turns inserts into upserts. retry handles 429 and 5xx with exponential backoff, honors Retry-After (seconds or a date) when the server sends it, and gives up loudly on 400, 401, 403 and 404 instead of retrying a request that can’t succeed. --dry-run prints the first page, the rows it would write and the SQL, with the Authorization header redacted, and writes nothing.
The state file holds the cursor and a high-water mark of updated_at per source. It’s committed after the sink transaction, through a temp file and a rename. If the process dies between the two, the next run reads the same window again, and because every write is an upsert on $.id the replay changes nothing. That’s the honest guarantee: at-least-once reads with idempotent writes. Exactly-once is a claim you can’t back when the source is somebody else’s API. The overlap setting rereads the last ten minutes on every run, since rows can turn up with an updated_at older than your mark (replicas, long transactions on the provider’s side), and upserts make the extra read free. Skipping downstream work when nothing changed is a separate layer, and make for APIs sketches it.
Schema drift is where tables go to rot, so don’t migrate on every new field. Store each row’s raw JSON next to its key and expose the fields in keep as generated columns:
CREATE TABLE launches (
id TEXT PRIMARY KEY,
raw TEXT NOT NULL,
name TEXT GENERATED ALWAYS AS (json_extract(raw, '$.name')) VIRTUAL
);
INSERT INTO launches (id, raw) VALUES (?, ?)
ON CONFLICT (id) DO UPDATE SET raw = excluded.raw
WHERE launches.raw IS NOT excluded.raw;
A field that shows up upstream next month is already in raw. Promoting it to a column later is one ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUAL; generated columns arrived in SQLite 3.31.0, and virtual ones are computed on read, so no stored row changes. The WHERE on the upsert skips rows that came back identical, which makes the “changed” count in a dry run mean something. It’s the same bet as keeping API responses in SQLite: the raw body is the source of truth and everything else is a view onto it.
Pagination Breaks First
Three styles cover most APIs: a cursor in the body, a Link header with rel="next", and a counter (page number or offset). A POST-based search like the one in the USAspending query post can take its paging parameters in the request body, so the config has to say where they go. Offsets have a worse problem. Rows inserted during the crawl shift every later page, so a row shows up twice or not at all. The upsert absorbs the duplicate. Nothing absorbs the skip, so the tool should prefer cursors, sort by a stable key when it’s stuck with offsets, and compare the rows it saw against any total the API reports. Pagination strategies for large datasets covers why offsets fail, and offset, cursor and keyset patterns lays out the alternatives.
Rate limits come next. Cap total run time so a slow run can’t overlap the next cron tick, and take a lock file so a second copy refuses to start. Secrets come only from ${VAR} references. The tool should reject a config with a literal Authorization value in it, and redact headers in logs and dry runs, because a config file that holds a token will end up in a repository.
Scope for Version 0.1
Version 0.1 has an HTTP source with three pagination styles (cursor in the body, Link header, page or offset counter), JSONPath extraction, SQLite and stdout sinks, a state file and retries. That’s the whole list. It has no scheduler, because cron exists, no UI, no streaming sources, and no plugins until the built-in pieces have survived real APIs. An aggregate step is the first feature people will ask for, and it belongs in SQL, one CREATE VIEW away from the table. The one-binary pattern only works if the binary stays small, which makes the refusals matter more than the features.
Do those six things right, then stop.