DuckDB vs jq

My take

Use DuckDB for this kind of work. It reads CSV and Parquet directly, uses every core and follows links between rows with plain joins. jq is still handy for small fixes to JSON files, but it has to read the data several times to follow those links, and that is where most of its time goes.

Speed7 to 40 times faster with DuckDB, depending on size
Memoryjq is leaner by default; two DuckDB settings close most of the gap
Hard dataDeep trees and long amendment chains slow both down by the same amount
OutputIdentical from both engines in every run

What I tested

Realistic, messy trade data

The data is fake but built like a real feed: trades, counterparties, instruments, books and FX rates, all pointing at each other. About 2% of the rows have problems on purpose, and the same settings always produce the same files.

Duplicate rows Missing currency and dates Messy values like "1,250" and " buy " Impossible dates like 2025-02-30 Links to rows that don't exist Amendments pointing back, and sometimes forward Parent chains 7 levels deep Loops: A's parent is B, B's parent is A

Every arrow is a link the pipeline has to follow. Three of them point back into their own table.

Both engines run the same five stages with the same 13 rules. Only the tool inside each stage changes.

Ingest

Load the five tables, every column as text

Clean

Fix formats, drop duplicates, fill gaps

Validate

Check 13 rules; bad trades go to a rejects file

Transform

Follow every link and build nested JSON

Aggregate

Add up exposure per parent company

Dataset sizes and an example output document

Dataset	Trades	Counterparties	Parquet	CSV	Built in
5k	5,000	101	0.1 MB	0.6 MB	0.05 s
50k	50,000	1,010	1 MB	6 MB	0.3 s
500k	500,000	10,100	8 MB	56 MB	2.5 s
1M	1,000,000	20,200	15 MB	112 MB	5.3 s
5M	5,000,000	50,500	76 MB	553 MB	29.6 s

{
  "trade_id": "TR0000000074", "event_type": "CANCEL", "side": "SELL",
  "economics": { "quantity": 46651, "price": 23.2474, "currency": "GBP", "notional_usd": 1378526.33 },
  "counterparty": {
    "counterparty_id": "CP0000060", "hierarchy_path": ["CP0000057", "CP0000060"],
    "parent": { "counterparty_id": "CP0000057", "country": "GB", "parent": null }
  },
  "lineage": { "version": 3, "chain": ["TR0000000007", "TR0000000043", "TR0000000074"], "is_latest": true },
  "data_quality": { "warnings": [] }
}

Speed

DuckDB is 7 to 40 times faster

The gap grows with the data. jq stays at about 3,000 rows a second whatever the size, while DuckDB gets faster as there's more to do.

DuckDBjq

Total time per run

Log scale: each grid line is ten times the one below.

Rows per second

jq's line is flat because it uses one core and does the same work per row at every size.

At 1M rows the gap drops to 29×. That's DuckDB slowing down while it wrote 1.2 GB of JSON to an almost full disk, not jq catching up.

Numbers

One core each15.6 s

DuckDB limited to a single thread did 1M rows in 15.6 s, still 20× faster than jq. The speed comes from working on columns in memory, not from extra cores.

CSV instead of Parquet+0.08 s

Reading CSV added 0.08 s to DuckDB's ingest and 0.16 s to jq's at 500k rows. The input format barely matters here.

Running it again±17%

A second run of 5k to 500k differed by up to 17%, but the gap stayed put: 40.0× and 39.9× at 500k. Treat small differences as noise.

Where time goes

jq struggles most where rows depend on other rows

To check that every link points at a real trade, jq first reads all the trade ids, then reads everything again. DuckDB does the same check with one join in 0.2 seconds.

How many times longer jq takes, per stage (1M trades)

Each engine's own time, split by stage (1M trades)

DuckDB, 11.0 s in total

jq, 5 min 14 s in total

82% of DuckDB's time goes into writing the final JSON. Cleaning and validating a million rows takes it under a second. jq's time is spread out because every stage parses and writes JSON text again.

Why: how data moves inside each engine

DuckDB turns the data into JSON once, at the end. jq can't read CSV or Parquet, so DuckDB converts the files first (that time counts as jq's), and then 13 jq processes each read and write a full JSON file.

Memory

jq is leaner, until you tune DuckDB

jq streams records, so it only keeps what each pass needs to remember. By default DuckDB builds the whole nested result in memory before writing it out, which is where its 2.6 GB peak comes from.

Peak memory per run

DuckDBjq

Includes the Python worker, about 100 MB.

Two settings change the picture. Lean mode writes the JSON straight from the query and drops tables once no later stage needs them. Unordered output lets DuckDB write rows as soon as they're ready instead of holding them to keep file order. I tried each setting on 1M rows, alone and together.

DuckDB settings at 1M trades

What I'd useOther settings

Peak memory

Total time

Lean plus unordered used 1.4 GB and took 3.2 s, down from 3.2 GB and 6.3 s. Add a 512 MB limit and an on-disk database and it drops to 752 MB, close to jq's 632 MB.

A memory limit on its own doesn't help: at 1 GB the default mode ran out of memory, because the full result still has to fit. Spilling to disk also needs free disk. One run with a 512 MB limit failed because the disk filled up. Unordered output changes the order of rows in the file, which didn't matter for this job.

Settings: POC_ENGINE__DUCKDB_LEAN=true, POC_ENGINE__DUCKDB_PRESERVE_INSERTION_ORDER=false, optionally POC_ENGINE__DUCKDB_MEMORY_LIMIT and POC_ENGINE__DUCKDB_STORAGE=disk.

Hard data

Deep trees and long chains don't break either one

I made two harder versions of the 50k dataset: parent chains 200 levels deep, and trades amended up to 100 times. Both engines slowed down by about the same amount.

Transform stage time (each panel has its own scale)

DuckDB

jq

The output gets bigger too: a trade under a 200-level chain carries 200 nested parent objects.

Where they differ is how naturally they express it. DuckDB has no type for an object that contains itself, so I built the nested parent as JSON inside a recursive query, and I had to start from the top of each tree so loops were never entered. jq handles nesting with plain recursion but needs a list of visited ids to stop on loops.

Tuning jq

How you write jq matters a lot

I wrote the same aggregation three ways and timed each one on 100,000 output documents.

Cutting each document down to the fields it needs before adding things up was 2.6× faster than the first version. A single flat lookup table was 11× slower than the final one.

At 1M rows the rewrite took jq's aggregate stage from 188 s to 47.5 s. Even so, every jq pass has to parse JSON text: just reading and counting the 1M-row output takes 18 s, which is more than DuckDB needs for the whole job.

Correctness

Same answer from both engines

After every run a checker matches the two outputs document by document. Above 200k rows it compares a fixed sample of about 50,000 documents in depth and still checks every row count and key. They matched every time.

1,000,000rows read

10,000duplicates removed

8,999rejected, each with a reason

981,001nested JSON documents

9,194exposure groups

Why trades were rejected (1M trades)

A trade can break more than one rule. Another 3,922 settlement dates and 2,997 currencies were missing but could be filled in, so those trades were kept with a warning.

Strengths and limits

What each tool is good at, and where it stops

Speed is one part of it. Here are 20 situations you might run into, with what each tool does well, what takes extra work and what it can't do at all.

DuckDB

Strong13

Limited6

Cannot1

jq

Strong6

Limited7

Cannot6

Not applicable1

Strong: works well out of the box Limited: possible, but slow or needs a workaround Cannot: not possible with the tool alone

Situation	DuckDB	jq
Input and output
Read CSV or Parquet	StrongReads both directly, on all cores.	CannotReads JSON only. Something else has to convert the files first.
Write Parquet	StrongWrites Parquet, CSV and JSON.	CannotWrites JSON or plain text only.
Records with different shapes in one file	LimitedWants one shape per table. Very mixed records end up in a generic JSON column.	StrongEach record can have its own shape.
Scale and speed
Use all CPU cores	StrongRuns in parallel by default.	CannotOne core per process. Splitting the file only helps for per-record steps.
Data bigger than memory	StrongSpills to disk above its memory limit, if there's free disk.	CannotCan't spill. A step that must remember every key fails when memory runs out.
Millions of rows in seconds	Strong1M trades in 3 to 11 seconds.	Limited1M trades took 5 min 14 s.
Low memory use	Limited2.6 to 3.6 GB at 1M by default, 0.75 to 1.4 GB with the lean settings.	StrongStreams records: 632 MB at 1M, far less for simple filters.
Data spread over several machines	CannotOne machine only.	CannotOne machine only.
Links between rows
Join two large tables	StrongBuilt-in joins that can spill to disk.	LimitedOne side has to be loaded into memory as a lookup table.
A row pointing to a later row	StrongRow order doesn't matter in SQL.	LimitedNeeds an extra full pass, because a stream can't look ahead.
Sort or group everything	StrongAny size, spilling to disk if needed.	Limitedsort_by and group_by need everything in memory. Running totals are fine.
Chains of parents	StrongRecursive SQL follows chains of any length.	StrongRecursive functions follow chains of any length.
Loops in the links	LimitedA recursive query can run forever unless it's written to avoid loops.	LimitedYou have to track visited ids yourself.
Output shape
Nesting of unknown depth	LimitedNo type can contain itself, so that part has to be built as JSON.	StrongBuilds nested objects of any depth naturally.
Correctness
Exact money amounts	StrongHas an exact DECIMAL type.	CannotNumbers are 64-bit floats, so sums can round.
Strict date and type checks	Strongtry_strptime and try_cast reject bad values.	Limitedstrptime accepts 2025-02-30 and rolls it over to 2 March, so it needs an extra check.
Running it
Same speed however the code is written	StrongA query planner picks the join order.	LimitedNo planner. Rewriting the same logic changed speed up to 11×.
An endless stream of events	LimitedWorks on data that already exists, so new data goes in small batches.	StrongReads line by line for as long as input arrives.
Several programs writing at once	LimitedMany readers, but only one process can write a database file.	Not applicableNot a data store. Each run writes its own files.
A quick command in the terminal	StrongThe duckdb command line runs SQL on files directly.	StrongSmall, fast to start and made for shell pipelines.

Things jq can't do

Read CSV or ParquetConvert to NDJSON first, for example with DuckDB.
Use more than one core in a runSplit the file across several jq processes, for per-record steps only.
Move data to disk when memory runs outKeep what each pass remembers small, or use a database for that step.
Exact decimal mathsWork in whole cents, or calculate money elsewhere.
Check a link to a row it hasn't read yetRead the data twice: collect the ids, then check.

Things DuckDB can't do

Store an object that contains itself as a typed valueBuild that part as JSON in a recursive query.
Let several processes write one database file at onceSend writes through one service, or use a server database.
Spread one job over several machinesMove to Spark or similar once the data outgrows one machine.
Process an endless stream continuouslyRun the same query on small batches.

Other tools

Other tools worth a look

I didn't benchmark these. The notes come from what each tool documents, judged against this job: 1 to 5 million rows on one machine.

Tool	Good for this job	Weak spot	Pick it when
Polars	As fast as DuckDB for joins and totals, with a Python dataframe API and a streaming mode	No recursive queries, so parent and version chains need a loop of joins	Your team prefers Python dataframes to SQL
Apache DataFusion	SQL with recursive queries, streams and spills to disk	Smaller community and fewer file helpers	You're building the service in Rust
chDB / ClickHouse	Very fast scans and totals, JSON output built in	Less flexible joins across many large tables	The results also feed dashboards
Apache Spark	Handles data far bigger than one machine	Cluster cost and slow start; slower than DuckDB at this size	The data no longer fits on one machine
PostgreSQL	Recursive SQL and JSON functions, results stay queryable	Much slower for bulk transforms	The output has to live in an operational database
jaq / gojq	Same jq language, often faster per record	Same one-record-at-a-time model and extra passes for links	You need to keep existing jq scripts

For this job I'd still pick DuckDB with the lean settings. Polars is the closest alternative. A faster jq clone won't fix the real problem, which is the extra passes needed to follow links between rows.

How the app works

Answer first, work in the background

A request is saved and answered straight away. Services announce what happened as events, orchestrators pick those up, and worker processes do the heavy lifting, two jobs at a time by default.

Each job runs in a fresh process, so memory from one run never leaks into the next. The dashboard follows every event live.

Datasets

Ask for a size, or upload CSV or Parquet files.
If that exact dataset already exists, it's reused.
A worker builds the tables and writes Parquet and CSV, or checks an upload's columns.
The dataset is marked ready, with row counts and sizes.

Pipeline

Pick a dataset, the engines and any filters.
Each engine runs in turn through the five measured stages.
If a stage fails, that run stops and its partial files are deleted.
A checker compares the outputs, and the result shows timings and the verdict.

Benchmarks

Choose the sizes, smallest first.
Missing datasets are generated and waited for.
Each size runs a full comparison, then its big output files are deleted.
Sizes never overlap, so they don't fight over CPU.

There's also a trade tracker. After each run it records where every input row ended up: delivered, rejected, removed as a duplicate or filtered out. You can search any trade and see its path through the stages with real timestamps, and its record before and after.

Run it

Try it yourself

make setup       # check tools, install, create small datasets, run the tests
make run         # dashboard on http://127.0.0.1:8000, tracker on /tracker
make benchmark   # 5k to 1M rows, both engines
make scenarios   # deep trees and long chains

All numbers come from one run per size on an Apple Silicon laptop with 10 cores and 16 GB of RAM, using Parquet input. The 5M dataset is in the repo's generator but I didn't run it: each engine writes about 1.2 GB of JSON per million rows, so 5M needs around 20 GB of free disk. My rough guess from the measured speeds is 26 minutes for jq and about a minute for DuckDB.

Terms used on this page

Total time	Clock time from reading the files to the last output written.
Cores used	CPU time divided by clock time. 1.0 means one core was busy the whole time.
Peak memory	The most RAM in use at any moment, counting the worker and every process it started.
× longer	jq's time divided by DuckDB's time.
Lean mode	A DuckDB setting in this project that streams the output and drops tables early.