DEV Community: Mohammed Arshad Ansari

Querying Parquet on S3 with DuckDB: secrets, speed and the settings that matter

Mohammed Arshad Ansari — Mon, 26 Oct 2026 14:00:00 +0000

DuckDB reads Parquet on S3 in one line, and that part is not the problem. The problem shows up a month later, when a query that took two seconds on your laptop takes forty against the bucket and nobody can say why.

The answer is almost never bandwidth. It is the number of requests. Object storage charges you a round trip for every file you open and every column chunk you fetch. A layout that costs nothing on local disk can mean tens of thousands of round trips on S3. This post covers credentials first, because they are what people search for, and then the layout and settings that decide speed.

One honest note. My own production pipeline reads Parquet from an NFS share on a NAS, not from S3. The credential and request-level detail here comes from DuckDB's documentation and from my book. The many-files failure near the end is mine, from that NFS pipeline, and it would have hit the same way on S3.

Can DuckDB read from S3?

Yes. The httpfs extension handles S3, and DuckDB autoloads it the first time you touch an s3:// path. You don't need to install or load anything by hand, though doing so in production scripts makes the dependency explicit.

SELECT count(*)
FROM read_parquet('s3://analytics/events/dt=2026-09-*/*.parquet');

For Parquet, DuckDB does not download whole files. Its docs describe partial reading: it reads the file's metadata, then uses HTTP range requests to fetch only the parts the query needs. A count(*) can be answered from metadata alone. That is why Parquet on S3 works at all, and also why the number of files matters so much: every file needs its own metadata read before anything else happens.

Setting S3 credentials: CREATE SECRET

DuckDB manages credentials as secrets. The S3 docs show two providers.

credential_chain asks the AWS SDK for credentials, the same way the AWS CLI would. That covers environment variables, profiles, SSO, assumed roles and instance or container roles. Use it on servers, so no key ever sits in your code:

CREATE OR REPLACE SECRET aws (
    TYPE s3,
    PROVIDER credential_chain
);

config takes the values directly. Use it when the keys come from somewhere else, such as a secrets manager your app already reads. Pass them in from code rather than typing them into SQL:

import os
import duckdb

con = duckdb.connect()
con.execute(
    "CREATE OR REPLACE SECRET aws "
    "(TYPE s3, PROVIDER config, KEY_ID ?, SECRET ?, REGION 'eu-west-2')",
    [os.environ["AWS_ACCESS_KEY_ID"], os.environ["AWS_SECRET_ACCESS_KEY"]],
)

Three details that save time:

Scope secrets to buckets. Add SCOPE 's3://raw-bucket' and the secret only applies there. With several matching secrets, the secrets manager picks the longest matching prefix. FROM which_secret('s3://raw-bucket/x.parquet', 's3') tells you which one a path will use, and FROM duckdb_secrets() lists them with sensitive values redacted.
Persistent secrets are plain files. Secrets live in memory for the session by default. CREATE PERSISTENT SECRET writes them to ~/.duckdb/stored_secrets, and the docs say they are "stored in unencrypted binary format on the disk." On a shared server, use credential_chain instead.
S3-compatible stores need three more fields. For MinIO and similar, set ENDPOINT, URL_STYLE 'path' and, for a local HTTP endpoint, USE_SSL false.

CREATE OR REPLACE SECRET minio (
    TYPE s3,
    PROVIDER config,
    KEY_ID '',
    SECRET '',
    ENDPOINT 'minio:9000',
    URL_STYLE 'path',
    USE_SSL false,
    SCOPE 's3://analytics'
);

If you have older code that uses SET s3_access_key_id = ..., it still runs. Chapter 9 of Local-First Analytics uses that style for its MinIO example. DuckDB's docs now call it a legacy method and say to avoid it, because settings are easy to print by accident. Move to secrets when you next touch that code.

This is the first part. The full post — including the rest of the working details — is on my site: Querying Parquet on S3 with DuckDB: secrets, speed and the settings that matter

Dagster and dbt: do you need both?

Mohammed Arshad Ansari — Fri, 23 Oct 2026 14:00:00 +0000

"Why use dbt if I have Dagster?" is the number-two Google result for this topic, and it's a Reddit thread. That tells you the question is real. People see two tools that both draw a lineage graph, both run on a schedule, and both run tests, and they wonder if they are paying twice.

They aren't, quite. dbt is a SQL transformation framework. Dagster is an orchestrator. They overlap in the middle, and the overlap is where the confusion lives. My own production pipeline runs on Dagster with no dbt at all, so I'll also say when skipping dbt is the right call.

What each tool actually does

dbt takes a folder of SELECT statements with Jinja templating. It works out their order from ref() calls, compiles them for your warehouse, runs them as tables or views, and runs tests (not_null, unique, custom SQL) against the results. It does not schedule itself. Something has to call dbt build.

Dagster decides when things run, records what each run produced, retries failures, tracks partitions, and runs arbitrary Python. It has its own data-quality checks (asset checks) and its own lineage graph, built from the dependencies you declare.

The overlap: both have a lineage graph and both have tests. The difference: dbt's graph covers SQL models in one warehouse. Dagster's graph covers everything, including the Python that loads data before dbt runs and the models you train after it.

Why use dbt if you have Dagster

You still want dbt when:

Most of your transformations are SQL in one warehouse. dbt's templating, ref(), incremental materializations and the docs site are a decade of work you would otherwise rebuild badly.
Analysts write the models. dbt is the tool analysts already know. Asking them to write Dagster assets in Python is a bigger change than it sounds.
You want the project to be portable. A dbt project runs under Dagster, Airflow, Prefect, cron or dbt Cloud. Your SQL is not locked to the orchestrator.

You can skip dbt when the transformations are mostly Python, or when one engineer writes everything and is happy to keep the SQL inside Python assets. That is my situation, below.

This is the first part. The full post — including the rest of the working details — is on my site: Dagster and dbt: do you need both?

AEGIS v1 is archived. Here is what replaces it

Mohammed Arshad Ansari — Thu, 22 Oct 2026 04:30:51 +0000

I've shut down AEGIS v1 and archived github.com/hikmahtech/aegis. The code stays public and MIT-licensed. You can still read it, fork it and learn from it. It just won't get fixes, releases or answers to issues any more.

AEGIS itself isn't going away. It is being rebuilt as version 2, and version 2 is not open source. This post explains why, what v2 looks like, and what is already running on it.

What v1 was

AEGIS v1 was one system that did everything for one person: my tasks, email, money, research, and the alerts from the machines in my house. Three services shared one Postgres database. A Temporal worker ran every workflow on one task queue. A handful of named agents each owned a slice of the work, and every time one needed me, it asked through the same approval card.

I open-sourced it in July, and the ideas in it held up. One primitive for every interruption. Durable workflows that survive a weekend of waiting for my tap. Local models first. An audit line for everything that matters. None of that is changing.

What went wrong with one system

The shape was the problem, not the ideas.

One deploy for everything. A fix to bank-statement parsing redeployed the code that restarts services in my homelab. Every change carried the risk of every other area.
One database for everything. The accounting tables and the alert tables lived side by side. Nothing stopped one area from reading or writing another's rows, so over time some did.
Nothing could leave. If someone wanted just the bookkeeping part, there was no way to give it to them. It came with my task manager, my research feeds and my infrastructure agent attached.

The last one matters most. The bookkeeping lane is useful to people who aren't me. To hand it to anyone, it has to stand on its own.

This is the first part. The full post, including the rest of the working details, is on my site: AEGIS v1 is archived. Here is what replaces it

DuckDB vs Spark: which workloads actually need a cluster

Mohammed Arshad Ansari — Wed, 21 Oct 2026 14:00:00 +0000

Most "DuckDB vs Spark" questions are really a sizing question. Spark is a distributed engine: it splits a job across many machines and pays to coordinate them. DuckDB is an in-process engine: it runs the whole job on one machine and pays nothing to coordinate. Which one is faster depends almost entirely on whether one machine can hold the work.

So the useful question is not which engine is better. It is: how many bytes does your job actually touch, and does that fit on one box? Here is the arithmetic, where a cluster earns its cost, and what one node has handled for me. I have not run Spark in production; this is the single-node side of the line, measured, plus what Spark's own documentation says about the other side.

DuckDB vs Spark: what each one is built for

Spark spreads work over a cluster. A driver plans the job, executors on many machines each process part of the data, and a shuffle moves rows between machines when a join or group-by needs them together. If a machine dies, Spark rebuilds the lost piece. Its documentation puts it plainly: if any partition of a cached dataset is lost, "it will automatically be recomputed using the transformations that originally created it." That is what you rent a cluster for: data larger than any one machine, and jobs that keep going when one machine fails.

DuckDB is a columnar SQL engine that runs inside your Python process, a CLI or a service. No driver, no executors, no shuffle over a network. It reads Parquet and CSV in place, uses every core, and spills to disk when a join or sort outgrows memory. The tuning guide lists GROUP BY, JOIN, ORDER BY and window functions as operators that can work larger than memory.

Both read the same Parquet. That matters more than any benchmark: the choice is reversible, because the files don't care which engine opens them.

The sizing math that decides it

The number that matters is the working set: the bytes a job has to process after partition pruning and column selection. It is not the size of the lake.

Three steps get you there.

1. Start from what the job reads, not what exists. A 20 TB events table, partitioned by day, queried for the last 30 days, touching 6 of 60 columns, reads roughly 30/365 × 6/60 of the table. That is about 160 GB on disk before any filter on row values. Parquet compresses, so memory use runs higher than disk size. But most of that work streams through and never has to sit in memory at once.

2. Budget memory by operation. DuckDB's environment guide gives the rule of thumb: about 1–2 GB of memory per thread for aggregation-heavy work and 3–4 GB per thread for join-heavy work. So a 32-thread job needs roughly 32–64 GB for aggregations, or 96–128 GB for joins. That is one ordinary server, not a cluster.

3. Compare with the biggest box you can rent. Single machines are large now. AWS lists an x2iedn.metal with 128 vCPUs and 4,096 GiB of memory. You don't need that. The point is that the ceiling of "one machine" sits far above where most teams assume it is.

Local-First Analytics draws the same line. Chapter 5 suggests DuckDB for up to about 1 TB of partitioned Parquet for aggregation-heavy work, and less for join-heavy work. It also warns that many-to-many joins blow up intermediate state whatever the input size. Its rule of thumb for starting local-first is a working set that fits in 32 GB of RAM and fewer than ten people running queries. Both bars are much lower than where a single node actually gives out.

If your working set after this arithmetic is in the gigabytes or low hundreds of gigabytes, you are running Spark for the cluster's sake, not the data's.

This is the first part. The full post — including the rest of the working details — is on my site: DuckDB vs Spark: which workloads actually need a cluster

Dagster vs Airflow: which to choose for a small data team

Mohammed Arshad Ansari — Mon, 19 Oct 2026 14:00:00 +0000

"Dagster vs Airflow" sounds like a feature contest. It mostly isn't. Both schedule Python, both retry failures, both have a UI, both are free to self-host. The real difference is what each one thinks it is scheduling. Airflow schedules tasks. Dagster schedules the things those tasks produce.

For a big team with an Airflow platform already running, that difference rarely justifies a migration. For a small data team starting from nothing, it decides how much you can hold in your head. I run a production Dagster pipeline alone: 112 assets, 93 asset checks, 57 schedules. This post is about why that model fit, what still hurt, and when Airflow is the better answer. I have not run Airflow in production, so the Airflow side comes from its documentation, not from my scars.

Tasks or assets: the one difference that matters

In Airflow you write a DAG: task A, then task B, then task C. Airflow knows whether each task succeeded. It does not, by default, know what the task wrote.

In Dagster you write an asset: a function that produces a table, a file or a model, and names the assets it depends on. The graph comes from those dependencies. Dagster records each materialization, so it knows which partitions of which table exist, when they were built, and whether the checks on them passed.

from dagster import asset, AssetIn

@asset
def daily_prices(): ...

@asset(ins={"prices": AssetIn("daily_prices")})
def sector_statistics(prices): ...

That is the whole pitch. If your job is "keep these 100 tables correct and current", the asset model matches the job. If your job is "call these 40 systems in this order", Airflow's task model matches it at least as well.

Why the asset model fit a 112-asset pipeline run by one person

My pipeline ingests market data, builds features, trains models and writes to ClickHouse. The counts today: 112 assets, 36 of them partitioned (34 daily, 2 static), 100 jobs, 57 schedules, 3 sensors, 93 asset checks. It has 913 commits since November 2025 and about 3,000 tests. The teardown of the whole platform gives the architecture; it has grown since that was written.

Three things made the asset model worth it for one person:

Partitions are data, not a loop. Dagster tracks the status of every daily partition. When a scrape fails for one date, I see one red square and rerun one square. I don't write backfill scripts that remember which dates they have done.
Checks sit next to the thing they check. 93 asset checks run against the assets they guard and show up on the asset's page. When something is wrong, the check and the data are in one place.
Granularity is a decision, not a default. I could choose to drop partitions where they cost more than they saved. One sector-statistics asset rebuilt 1,882 dates, 109,152 rows, in about 45 seconds as a single unpartitioned asset. The per-partition version would have meant over 2,000 partitions and 5,000 to 20,000 queries. The first phase of that change materialised 9 of 10 such "full" assets in about six minutes.

None of this is impossible in Airflow. Airflow 3 now has assets too (more below). But in Dagster the asset is the centre of the tool, and with one maintainer, the centre of the tool is what you get for free.

This is the first part. The full post — including the rest of the working details — is on my site: Dagster vs Airflow: which to choose for a small data team

Polars vs Pandas: the three differences that matter

Mohammed Arshad Ansari — Wed, 14 Oct 2026 14:00:01 +0000

Every Polars post opens with a benchmark where it beats pandas by a factor of ten. That number is real on some workload somewhere, and it is the least useful thing about the library.

Three differences actually cause the gap. Once you know them, you can predict when Polars will help you and when it will not, which is worth more than a chart.

1. It builds the plan before it does the work

Pandas is eager. Each line runs when it is read:

df = pd.read_parquet("transactions.parquet")   # whole file, all columns
df = df[df["amount"] > 0]                      # walk it, allocate a new frame
df["fee"] = df["amount"] * 0.029               # walk it again
df = df.drop_duplicates(["transaction_id"])    # and again

The file is fully loaded before the first filter, including columns you never mention again. Every step allocates.

Polars, given scan_ instead of read_, does nothing at all until you ask:

result = (
    pl.scan_parquet("transactions.parquet")
    .filter(pl.col("amount") > 0)
    .with_columns((pl.col("amount") * 0.029).alias("fee"))
    .unique(subset=["transaction_id"])
    .collect()          # only now does anything run
)

By the time collect fires, Polars knows the whole pipeline, so it can do two things pandas structurally cannot:

Projection pushdown. Your query mentions three columns, so it reads three columns off disk. The other forty are never touched.
Predicate pushdown. The filter goes down to the file reader, so non-matching rows are never materialised at all.

This is also where Parquet and Polars compound. Columnar storage makes reading three of forty-three columns cheap, and lazy execution is what tells the reader it only needs three.

One trap worth knowing, because it silently undoes all of it:

lazy_df.collect().head(100)   # runs the whole query, throws away almost everything
lazy_df.head(100).collect()   # pushes the limit into the plan, stops scanning early

If you learned Polars from older material you may reach for .fetch(n). That is deprecated in Polars 1.x; .head(n).collect() is the current form.

2. No hidden copies, because everything is Arrow

Polars stores data in Arrow buffers, which is the same layout DuckDB and PyArrow use. When you pass a frame between them, they exchange pointers rather than bytes.

That sounds like an implementation detail until you count the boundaries in a real pipeline. Read Parquet into pandas with the NumPy backend, transform, hand the result to DuckDB, and each crossing allocates fresh memory the size of your data, spends CPU translating layouts, and adds garbage collector pressure. On a gigabyte of decoded data that is roughly 2.3 seconds through the copying path against 0.4 through the Arrow one, and about half the peak memory.

Memory is the more reliable win, and it is the one people notice on real jobs. Pandas materialises every intermediate frame, so a chain of eight transforms over a large table holds several copies at once. That is the difference between a job that fits in your container and one that gets killed.

This is the first part. The full post — including the rest of the working details — is on my site: Polars vs Pandas: the three differences that matter

Parquet vs CSV: what you actually save, and why

Mohammed Arshad Ansari — Mon, 12 Oct 2026 14:00:00 +0000

"Just use Parquet" is the most repeated advice in data engineering and the least explained. It is good advice. But if you do not know why it wins, you cannot tell when it will not, and you end up either converting files that should have stayed CSV or being surprised when your ratio is 3x instead of the 20x you read about.

There are three mechanical reasons, and none of them need a paper.

1. It reads only the columns you asked for

A CSV stores a row at a time:

id,name,email,signup_date,revenue
1,Alice,alice@x.com,2026-01-15,127.50
2,Bob,bob@y.com,2026-01-16,340.00

To total the revenue, the reader walks the whole file. Every name, every email, every date, all read off disk and parsed to reach one column.

Parquet stores a column at a time. SELECT SUM(revenue) reads the revenue column and nothing else. Most analytical queries touch 10 to 20% of the columns, so they read 10 to 20% of the file. CSV always reads 100%.

That is the entire trick behind the headline benchmarks, and it is why the win grows as your tables get wider.

2. Similar things sit next to each other, so they compress

Compression works on patterns. A CSV row is a string, a date, a float and two integers jammed together, which is close to noise. A Parquet column is ten million floats, or ten million dates, or the word "USA" repeated ten thousand times.

Parquet picks an encoding per column with no input from you:

Run-length encoding turns ten thousand repeats of "USA" into the pair ["USA", 10000].
Dictionary encoding maps "San Francisco" to 0 and "New York" to 1, then stores the small integers.
Delta encoding turns hourly timestamps [1704067200, 1704070800, 1704074400] into [1704067200, +3600, +3600].

Then a general compressor runs over the result.

This is why asking "what compression ratio will I get?" has no answer without your schema. What decides it:

Column type	Typical ratio	Why
Booleans	50-100x	Bit-packing
Timestamps	10-50x	Delta encoding
Low-cardinality strings	10-30x	Dictionary encoding
Integers, IDs, counts	5-20x	Dictionary and bit-packing
High-cardinality strings	2-5x	Little structure to exploit
Floats, measurements	2-4x	Little structure to exploit

A table of flags, IDs and timestamps compresses enormously. A table of sensor readings barely compresses at all. Both are normal.

Real numbers, so you have an anchor: ten million rows of synthetic user data go from about 635 MB of CSV to about 29 MB of Parquet, roughly 22x. Ten million NYC taxi rides go from 2.1 GB to 180 MB, roughly 12x. Event logs usually land at 15 to 25x, transactional data at 10 to 15x, float-heavy sensor data at 4 to 8x.

Measure your own. It takes one write.

This is the first part. The full post — including the rest of the working details — is on my site: Parquet vs CSV: what you actually save, and why

DuckDB as an LLM cache: what to cache, what to key on, and where it breaks

Mohammed Arshad Ansari — Sat, 10 Oct 2026 14:00:00 +0000

Someone reached this site last month asking, word for word, what are the pros and cons of using DuckDB as an LLM cache in a production-grade data app? Short answer: it fits when one process owns the cache, the hard part is the key rather than the database, and the reason to pick it over Redis is that the cache becomes a table you can ask questions of. The long answer follows, including where it's the wrong tool. Whether DuckDB belongs in production at all is its own post; this one is only about the cache.

"LLM cache" is three different caches

Exact-match. Key: a hash of the fully resolved prompt plus the versions below. Value: the response. A hit means this exact call was made before, so the answer is identical by construction. Hit rates are poor for free-text chat and excellent for classification, extraction, batch jobs and retries — the shapes in calling an LLM from inside SQL, where every re-run re-classifies the same rows unless something remembers. This is the tier that pays. (Your API vendor's prompt caching is a different thing: it makes a repeated prefix cheaper but still makes the call.)

Semantic. Embed the prompt, find the nearest stored prompt, serve its answer if the similarity clears a threshold. Every hit is a bet that two different prompts wanted the same answer. A false positive isn't a miss; it's a wrong answer the user sees.

Tool and retrieval results. The search call, the database lookup, the chunks fetched to build the prompt. Deterministic given their inputs and the source's version, run more often than the model, usually the cheapest win. They want a TTL more than a version.

Why DuckDB is plausible here

An exact-match hit is a function call, not a network round trip. On DuckDB 1.5.5 with 8 threads, a keyed lookup over 200,000 rows measured 0.26ms p50, 0.39ms p99 with an index on the key (2ms / 5.7ms without). The rows are columnar, so the cache is also a dataset — hit rate, spend saved and which prompts dominate are one query, which is the strongest argument and gets its own section. The same table takes a FLOAT[1536] column for the semantic tier. And there is nothing to run: a file inside the process you already operate. The memory_limit / temp_directory / threads setup is in running DuckDB on your own infrastructure; the one addition here is that the HNSW index lives outside that memory limit entirely.

This is the first part. The full post — including the rest of the working details — is on my site: DuckDB as an LLM cache: what to cache, what to key on, and where it breaks

Paper Before Real Money: How My Finance Agent Earns the Right to Trade

Mohammed Arshad Ansari — Fri, 09 Oct 2026 14:00:00 +0000

Maou is the finance agent in AEGIS, the self-hosted agent platform I run my life on. A few weeks ago it learned to keep my books properly, and I wrote about why a ledger is not a database table. Now it has a second job: it runs a paper trading desk on real market prices.

The goal is simple. I have a trading system that decides what to hold each day. I want Maou to place those trades with real money. But not yet. First it has to show, on paper and over months, that the whole chain works and that the results are worth having.

This post covers both halves of what Maou now does with money, and what has to be true before the paper desk becomes a real one.

This is a write-up about engineering, not investment advice. The desk trades on paper, and this post deliberately does not show which stocks it holds.

The books: one view of where the money stands

The books are a plain-text double-entry journal in git, checked by hledger. Postgres is only an index over it. That was the last post. What changed since is that I can now see the money, not just trust it.

The figures are blurred because they are my real finances. The page shows:

Net worth at each month end: everything I have, less everything I owe.
Money in and money out, month by month.
Where it went this month, by category. My business spending splits into its own categories rather than arriving as one bar called "business".

Every number comes from hledger, asked at the moment the page loads. Nothing is copied into a second table that could drift.

One rule mattered more than any chart. Some of my spending is in dollars and pounds, and the books have no exchange rate for those days yet. The easy move is to guess a rate and fold them in. Instead the page leaves them out of the chart and says so underneath: "left out of the charts because there is no rate for them: $, £". A wrong number that looks right is worse than a gap you can see.

Behind the charts are the parts that need action: bills still to pay, spending the books could not explain, and bank statements that have not been matched line by line. Maou raises those as tasks for me, and a task closes itself when its problem goes away.

The desk: who decides what

The split of responsibilities is the most important design choice, so here it is plainly:

The trading system decides what to hold. Every evening it publishes a list of positions and how much of the portfolio each should be. That system is Ansaar, my market data platform, with its own models and its own risk manager.
Maou decides nothing about the picks. It turns that list into orders for whole shares against the paper capital, places them, and fills them at the next session's opening price, using real prices.
Maou scores the result against a benchmark every week and every month.

The agent is dumb on purpose. What I am testing is not whether an LLM can pick stocks. It is whether the full chain holds together: the trading system's decisions, the data they arrive on, the sizing, the fills, the costs and the scoring. When real money is involved, every link in that chain can lose it.

This is the first part. The full post — including the rest of the working details — is on my site: Paper Before Real Money: How My Finance Agent Earns the Right to Trade

The agent writes the code. I'm paid for the rest.

Mohammed Arshad Ansari — Wed, 07 Oct 2026 14:00:00 +0000

I don't type much code any more. Over the past year I rebuilt AEGIS, my own operations layer, into its third version, kept the Ansaar data platform running every night, and shipped most of the Hikmah Technologies site. Claude Code wrote most of the lines. Work I would once have planned in weeks now fits in days.

So where do my hours go? Almost none of them go on writing code. They go on three things:

Finding out what the person actually means.
Deciding what a correct result looks like.
Checking what got built against the real world.

That is the job now. The agent handles the part in the middle.

The code was the easy part all along

For most of my career it didn't feel that way, because writing code was slow and took up the day. Whoever could write it was the expensive person in the room, and every other role was arranged around them.

Now a clear description turns into working code in minutes. Agents are also getting steadily better at the work after that: reviewing, fixing bugs, deploying. I expect them to get most of the way there. What I don't expect them to get is the description itself.

The knowledge isn't written down

Take a simple request: "we need a way to track our leads."

Hundreds of products fit that sentence. The right one for a property brokerage is not the right one for a software company. At a brokerage, the lead may be a family, not a person. The same buyer calls in through three channels. A site visit matters more than an email open. The rules about who "owns" a lead can decide whether the partners stay friends. Usually none of that is written down. The owner carries it in their head and would struggle to list it if you asked directly.

No model has seen it, because it has never been written anywhere. Someone has to sit with the business, pull it out piece by piece, and turn it into something exact enough to build. Then they have to show the result and hear "no, not like that", because the first version is always wrong in a way nobody could have predicted.

This also does not carry over from one client to the next. What "correct" means for one brokerage's leads is not quite what it means for the next. That is why off-the-shelf software grows a hundred settings and still doesn't fit. Cheap code makes it possible to build the version that does fit, but only if somebody has done the finding out.

This is the first part. The full post — including the rest of the working details — is on my site: The agent writes the code. I'm paid for the rest.

DuckDB vs ClickHouse: which one, and when you need both

Mohammed Arshad Ansari — Tue, 06 Oct 2026 14:00:00 +0000

DuckDB and ClickHouse get compared because they sit near each other in every analytical benchmark: both are columnar, both are vectorised, both are very fast. The benchmark framing hides the difference that actually decides it. DuckDB is a library you put inside your program. ClickHouse is a server your programs connect to. Almost every practical difference follows from that.

I run both. ClickHouse is the analytical store behind Ansaar, feeding a live API. DuckDB is what I reach for in batch jobs and local work, and it is the subject of my book. So this is the comparison from operating them, not from a benchmark table.

The short answer

Pick DuckDB when the work happens inside one program — a transformation job, a service that owns its own queries, a notebook, a CI run — and the data arrives in batches. There is nothing to operate, and it is extraordinarily fast for that one program.

Pick ClickHouse when many clients query the same data at the same time, the data keeps arriving, and people expect the numbers to be seconds old. That is a server's job, and ClickHouse is one of the best servers for it.

If you have both shapes, run both. That's more common than either camp admits.

What each one is

DuckDB is an in-process analytical database — the usual shorthand is "SQLite for analytics". You import it into Python, Node, Go or the CLI, and it runs a columnar query engine inside that process. It reads Parquet, CSV and JSON directly, including from S3, with no load step. There is no server, no port, no cluster. Only one process may open a database file read-write, and while it does, no other process can open the file at all; any number of processes may read it if none is writing. The pattern that scales is one writer producing Parquet and many read-only readers over it.

ClickHouse is a client-server columnar database built for real-time analytics. You run it as a service (or buy ClickHouse Cloud), clients connect over the network, and it stores data in its MergeTree engine: rows land in sorted parts on disk, and background merges combine them. It is built to ingest continuously and answer aggregations over billions of rows in milliseconds, for many clients at once. It scales out across machines when one isn't enough.

This is the first part. The full post — including the rest of the working details — is on my site: DuckDB vs ClickHouse: which one, and when you need both

A Pile of Documents Is Not Knowledge

Mohammed Arshad Ansari — Fri, 02 Oct 2026 14:00:00 +0000

My research agent had read about 20,400 documents. It had concluded nothing.

Not metaphorically. The knowledge store held roughly twenty thousand rows — papers, articles, PDFs, nightly log entries — and there was nowhere in the system that said what any of it meant. Research answers were stored as aegis://research/ rows filed among ten thousand PDFs. The nightly journal wrote one dated entry into the same pile, in a format nobody could open.

This is the third of three posts about rebuilding lanes of AEGIS, my self-hosted agent platform, between 5 and 13 September. The first two were alerting and money. All three ended at the same rule, and this lane is where it's easiest to see why.

Measure use, not ingestion

Every RAG dashboard I have ever seen measures the wrong end of the pipe. Documents ingested. Chunks embedded. Index size. All of it is input, and input is the part that is trivially easy to grow.

The measurement that mattered took one join: a log of which documents were injected into which prompt, joined back to where each document came from. Over the 30 days to 12 September:

arXiv: 1,889 papers, 89,669 chunks — 90% of all RSS chunks. Prompts used 14 papers.
Across the whole corpus, arXiv PDFs were 93% of all chunks, and 78 of 10,284 had ever reached a prompt.
The intelligence scans had stored 371 items. Exactly one was ever used in a prompt.
Those same scans created 277 task-manager items in 30 days, every one auto-classified as reference material on arrival. My task list was being used as a log file.

A retention preview said the same thing in bytes. One rule — keep only the first chunk of any PDF no prompt has used in 30 days — would touch 8,297 PDFs and drop 362,153 of 500,055 chunks, freeing about 530 MB of text and 1 GB of vectors from a 4.8 GB table. The PDFs themselves stay, and nothing has been deleted: the preview is a dry run, and deleting is a separate, explicit decision.

This is not a story about arXiv being low quality. It is excellent. It is a story about a system that was optimising the metric it could see.

The filter I measured and did not ship

The obvious fix is a topic gate: only store the full text when the item matches something you care about. I built it, measured it, and made it opt-in.

Here is why. On arXiv the gate would pass 41% of papers — about 2.3× fewer chunks, for real added complexity. And on every other feed, the gate would have kept the full text of only 2 of the 10 documents a prompt actually used.

That second number killed it as a default. A filter that removes 80% of the value you can prove, to save storage you have plenty of, is not an optimisation. The measurement changed the design, which is the only reason to take a measurement.

The actual win was somewhere else entirely.

This is the first part. The full post — including the rest of the working details — is on my site: A Pile of Documents Is Not Knowledge