DEV Community: Mohammed Arshad Ansari

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

A Ledger Is Not a Database Table

Mohammed Arshad Ansari — Wed, 30 Sep 2026 14:00:00 +0000

For months, my finance agent read the first 200 characters of each email and called it accounting.

It was not lying, exactly. It genuinely did classify email, genuinely did track subscriptions, and genuinely did produce a monthly spend figure. The figure was just wrong, and nothing in the system was capable of noticing.

This is the second of three posts about rebuilding lanes of AEGIS, my self-hosted agent platform. The first was about alerting; the third is about knowledge. All three converged on the same rule, and this is the lane where getting it wrong shows up as a wrong number.

What "working" actually looked like

Measured on production on 2026-09-05, over the data since 1 July:

The extractor never saw the email. The fetch pulled each message in full, then kept snippet[:500] — Gmail's preview line, which runs to about 200 characters, so the cap never even bit. Downstream, that snippet was read back as if it were the message body. Of 61 emails judged to be receipts, 30 had an amount. Of 35 recurring-charge rows, 19 carried amount_cents = 0.
The richest stream was discarded on purpose. A list of bank sender addresses existed to stop bank alerts minting fake subscriptions — correct for a subscription tracker, catastrophic for a finance agent. In 30 days, from one mailbox, that list threw away 46 UPI debit alerts, plus IMPS transfers, UPI credits, card spends, a credit-card statement and an inbound international remittance. None of it was recorded anywhere.
Nothing dated reached me. Sitting unactioned in the inbox at that moment: a credit-card statement due in two days, an advance-tax instalment due in ten, a declined subscription payment with a fix-by date, an electricity bill, and an "AWS past due". Tasks created: zero.
What it did send was noise. 73 Anomaly: ? Apple-shaped tasks in nine weeks, most with no amount. 27 chat pings in 30 days about the same four charges. The same "what is this vendor?" question asked six times, because vendor-name variants produced different dedupe keys.
The monthly total was fiction. One electricity account appeared as three vendors, so "total monthly burn" counted it three times. A client's own supplier invoices, sitting in a work mailbox, counted as my subscriptions.

The plumbing was in perfect health: 242 extraction calls in 30 days, zero failures, 212 completed workflow runs. Every dashboard was green and every number was wrong. That is the specific failure mode of agent systems, and it is why I now measure the output rather than the pipeline.

The decision: hledger is the record, Postgres is the index

The rebuild starts with one structural choice. The book of record is a plain-text double-entry journal — hledger — in a private git repo. Postgres holds an index over it for fast queries.

Three properties a table does not give you by default:

Every write is a diff. A journal entry is a few lines of text in a git commit. I can read it, a reviewer can read it, and git log is the audit trail I would otherwise have had to build.

The arithmetic is checked by something that isn't me. Every write runs hledger check --strict, and a failure reverts exactly the paths that write touched. An entry that does not balance, or that uses an account nobody declared, does not land. No amount of LLM confidence gets past a tool that does arithmetic.

The index is disposable. The rule in the codebase is blunt: never treat an amount in the index as authoritative — run hledger. That means an index bug is a display bug, not a financial one, and the whole index can be rebuilt from the journal whenever I want.

The mechanics are ordinary and worth stating anyway: core and worker share one checkout, serialised by a file lock, so every write goes through one module. A hand-rolled file write would skip the strict check and its revert — so there is exactly one writer, and the rest of the system asks it.

This is the first part. The full post — including the rest of the working details — is on my site: A Ledger Is Not a Database Table

Your Alerts Have No Identity

Mohammed Arshad Ansari — Mon, 28 Sep 2026 14:00:00 +0000

I went looking for why my homelab kept telling me the same thing eight times.

The answer was structural, and it is the kind of thing that hides in a system for years because every individual piece of it is reasonable. AEGIS — my self-hosted agent platform — had no concept of a problem. It had notifications. One probe failed on eight consecutive days and wrote eight Todoist tasks, because the only thing it could ask was "did I already write a task about this?" — and it asked by looking at its own most recent log line, which by then said something else.

The Todoist task was the identity of the incident. And that is the bug.

This is the first of three posts about three lanes of AEGIS I rebuilt between 5 and 13 September. The other two — the money lane and the knowledge lane — landed on the same rule from completely different directions, which is the only reason I trust it.

What the measurement said

I don't rebuild anything on a hunch any more. Before touching the code I measured what was actually there on 2026-09-07:

Three alerting systems that could not see each other. The investigation flow (a #alert task plus a chat card), a set of watchdogs deduping against the audit log, and domain tables with their own private keys for certificate expiry and config drift. The resolved-aware NOT EXISTS dedupe SQL was copy-pasted in three files.
Thirteen fingerprint or dedupe-key schemes, mutually incompatible. A vendor fingerprint. A synthesised alertmanager:{alertname}:{instance}. A sentry:{issue_id}. Three separate signature classes. Three mute-key namespaces sharing one primary key. A day-bucketed drift key. Plus three informal links — a LIKE '%' || task_id || '%' against workflow ids, title substring matching, and a comment footer used as an authorship marker.
Dedupe keyed on the task, not the problem. The dedupe index's task_id column was NOT NULL and joined to the task table. One flow refused to use it at all and built a fourth ledger inside a settings row. Because the signature was the primary key, recreating a task reset first_seen_at and the occurrence count — recurrence history was destroyed on every recreate. Only 12 of 42 open alert tasks had a signature row at all.
One producer bypassed the capture path entirely, hand-building its command and deduping against the newest audit row. The close-on-resolve function structurally could not reach it. That is where the eight copies came from.
No service state. No maintenance window, no deploy suppression. The GitHub webhook claimed deployment events and dropped them. Suppression existed only as incidental delays scattered across three files.

And the part that made it expensive to fix: of 72,499 source lines, the four files carrying this logic held 11,212 of them. The code doing all this was the code hardest to change safely.

The primitive: a problem is a row, a task is a projection

The replacement is deliberately small. Every operational signal — an Alertmanager webhook, a Sentry issue, a swarm heartbeat, a certificate about to expire, a stuck social post, a hand-written task — becomes an Event and goes through one function, ingest_event.

That function owns three things and nothing else:

Identity. One open problems row per correlation_key. If a row for that key is already open, this is another occurrence of the same problem, not a new one.
History. Every occurrence, every resolution, every report is a problem_events row, idempotent on (source, external_id). Recurrence survives everything, including deleting the ticket.
The decision. The return value carries investigate: true or false. The producer starts the investigation workflow only when told to. The flow itself never dedupes and never captures a task.

The Todoist task is created by a projector, from the problem. It is a projection, never the identity. The rule I wrote into the docs, because I knew I'd be tempted to break it: do not look a problem up by its task title or fingerprint.

This is the first part. The full post — including the rest of the working details — is on my site: Your Alerts Have No Identity

Local-first analytics in practice: DuckDB, Parquet, and killing the round-trip

Mohammed Arshad Ansari — Fri, 25 Sep 2026 14:00:00 +0000

In the previous post I argued that a lot of analytics infrastructure exists to solve laptop-sized problems. Here's what the alternative actually looks like in code.

The setup: data as files, not endpoints

The local-first pattern starts by treating your dataset as a file — ideally Parquet, a columnar format that's compact, typed, and splittable. You can sit it in object storage, ship it in a release, or cache it on disk. No connection string, no credentials rotation, no warehouse to keep warm.

Then you point an in-process engine at it. DuckDB reads Parquet natively and runs the query where you are.

import duckdb

# No server. No connection pool. Just a query against a file.
con = duckdb.connect()

result = con.execute("""
    SELECT
        symbol,
        date_trunc('month', trade_date) AS month,
        avg(close) AS avg_close,
        count(*) AS days
    FROM 'prices/*.parquet'      -- glob straight over partitioned files
    WHERE trade_date >= '2024-01-01'
    GROUP BY 1, 2
    ORDER BY 1, 2
""").df()                         # straight into a pandas DataFrame

print(result.head())

That query reads only the columns it needs (columnar), only the row groups that match the predicate (pushdown), and never leaves the machine. On a few gigabytes of price data it returns in milliseconds — on the same laptop you're reading this on.

It runs in the browser too

The part that surprises people: the same engine compiles to WebAssembly. With duckdb-wasm you can ship a Parquet file and a query to the browser and render an interactive analytics view with no backend at all.

import * as duckdb from '@duckdb/duckdb-wasm'

const db = await initDuckDB()            // wasm engine in the tab
const con = await db.connect()
await con.query(`
  SELECT regime, count(*) AS n
  FROM 'https://cdn.example.com/regimes.parquet'
  GROUP BY regime
`)

The user's machine does the work. Your "API" is a static file on a CDN. Your hosting bill is whatever a CDN charges to serve a few megabytes — and there's no query endpoint to attack, rate-limit, or scale.

This is the first part. The full post — including the rest of the working details — is on my site: Local-first analytics in practice: DuckDB, Parquet, and killing the round-trip

72% of India's NSE bulk deals are same-day round trips (a 90,000-row analysis)

Mohammed Arshad Ansari — Fri, 25 Sep 2026 13:00:01 +0000

I run a small research site for Indian markets. I pulled every bulk and block deal the NSE has disclosed since January 2023, about 90,000 rows, to see what they actually contain. Below is the explainer I wrote from it. At the end is the short bit of Python behind the headline number, and the one trap that would have doubled every total.

When a large trade happens on the NSE, you usually hear about it the same evening: "Fund X buys 1.2% of Company Y in a bulk deal." Two kinds of disclosure produce these headlines, bulk deals and block deals. They sound alike, but the rules are different, and so is what they tell you.

This guide explains both, then looks at what nearly 90,000 of them, every one the NSE published from January 2023 to September 2026, actually contain. The biggest surprise: most bulk deals are not anyone taking a position.

What is a bulk deal?

A bulk deal is any trade, or set of trades, in which a single client buys or sells more than 0.5% of a company's listed shares in one day. It happens in the normal market, alongside everyone else's orders.

The broker who carries out the trade must report it to the exchange, and the exchange publishes the list after the market closes. The rule dates from a SEBI circular of January 2004.

Two things follow from the definition:

There is no rupee minimum. In a small company, 0.5% of the shares can be worth a few lakh rupees. In a large one it can be hundreds of crores.
A bulk deal can be reversed the same day. Nothing stops a client from buying 0.6% of a company in the morning and selling it by the afternoon. Both legs are disclosed.

What is a block deal?

A block deal is a single large trade done in a separate trading window, away from the normal market. Since 7 December 2025, under SEBI's revised framework, the rules are:

Minimum size ₹25 crore. It was ₹10 crore before.
Two windows. 8:45 am to 9:00 am, priced within 3% of the previous day's close; and 2:05 pm to 2:20 pm, priced within 3% of the volume-weighted price between 1:45 pm and 2:00 pm.
Delivery is compulsory. A block trade cannot be squared off or reversed. The shares really change hands.

The exchange publishes block deals after the close, just like bulk deals.

Bulk deal vs block deal at a glance

	Bulk deal	Block deal
What defines it	More than 0.5% of the company's listed shares in a day	One trade of at least ₹25 crore
Where it trades	The normal market	A separate window, twice a day
Can it be reversed the same day?	Yes	No, delivery is compulsory
Typical size (median, 2023–2026)	₹6 crore	₹29 crore
Published	After the close, same day	After the close, same day

What 90,000 NSE deals since 2023 show

We looked at every bulk and block deal the NSE disclosed from 3 January 2023 to 18 September 2026: 84,740 bulk deals and 5,898 block deals across 2,851 companies. Rupee values below count only the buying side of each trade, because every trade is disclosed once for the buyer and once for the seller, and adding both would count it twice.

Year	Bulk deals	Bulk buying (₹ crore)	Block deals	Block buying (₹ crore)
2023	18,525	1,64,457	1,094	54,902
2024	26,680	2,99,040	1,556	96,736
2025	19,394	2,96,427	2,026	1,22,832
2026, to 18 Sep	20,141	2,81,217	1,222	88,153

Five things stand out.

1. Most bulk deals are same-day round trips

In 72% of bulk deals, the same client bought and sold the same stock on the same day. Those round trips make up about two-thirds of all bulk buying by value, and the share barely moves from year to year: 71% of deals in 2023, 72% in 2024, 69% in 2025 and 77% so far in 2026.

The clients behind them are mostly trading firms. The four with the most round trips are Graviton Research Capital, HRTI, QE Securities and NK Securities Research. They typically trade in and out through the day, and each crossing of 0.5% gets disclosed, even though they end the day owning little or nothing.

So a headline that says a firm "bought 0.8% of a company" may describe half of a trade that was reversed by the close. Before reading anything into a bulk deal, check whether the same client appears on the other side that day.

2. Bulk deals cluster in small companies

Because the rule is a share of the company rather than a rupee amount, small companies cross it far more easily. The stocks with the most bulk deals since 2023 are small and recently listed ones: tickers ATALREAL (1,100 deals), MOBIKWIK (1,006), QUADFUTURE (629), MTNL (622) and RATNAVEER (569). The median bulk deal is about ₹6 crore.

3. Block deals are where large stakes change hands

Block deals are fewer, much larger and cannot be reversed, so they are where real ownership moves. The median block deal is about ₹29 crore, and the largest single trades since 2023 run to thousands of crores:

Bharti Airtel, 18 February 2025: ₹8,485 crore, sold by Indian Continent Investment.
Asian Paints, 12 June 2025: ₹7,704 crore, bought by SBI Mutual Fund from Siddhant Commercials.
Adani Ports, 2 March 2023: ₹5,282 crore, sold by the S.B. Adani Family Trust.
Wipro, 9 June 2025: ₹5,058 crore, sold by the Azim Premji Trust.

The stocks with the most block deals are large, widely held ones: Lenskart, Kotak Mahindra Bank, Axis Bank, PB Fintech (PolicyBazaar) and Shriram Finance.

4. June is block-deal season

Almost a quarter of all block-deal value since 2023, 23%, was traded in June. August (14%), September (12%) and December (11%) follow. January and April each carried about 2%. One caution: October to December have three years of data in this period, not four, so those months are slightly under-counted.

5. Mutual funds are the steady buyers

Mutual funds, taking every client whose name contains "Mutual Fund", were net buyers through bulk and block deals in every year, and by a growing amount:

Year	Mutual funds, net bought (₹ crore)
2023	14,018
2024	29,899
2025	60,569
2026, to 18 Sep	51,775

When a promoter or a foreign fund sells a large block, a domestic fund is often on the other side, as with SBI Mutual Fund in Asian Paints.

How to read a deal disclosure

A bulk or block deal is a fact about a trade that already happened. To get something useful out of one:

Check both sides. If the same client bought and sold that day, it is most likely trading, not a change in ownership.
Check the type. A block deal ends in delivery; a bulk deal may not.
Compare it with the company's size. 0.5% of a small company can be a tiny rupee amount.
Look at who is on the other side. A promoter selling to a mutual fund is a different story from two trading firms swapping shares.
Keep it in proportion. One deal is one data point about the past.

What a deal cannot tell you. A disclosure shows who traded, how much and at what price. It does not show why. A fund may be meeting redemptions, rebalancing to an index, or trading for a client. It also says nothing about where the price goes next. Read deals as context for your own research, never as a reason on their own.

About the data

The figures come from the NSE's bulk and block deal disclosures, 3 January 2023 to 18 September 2026, as republished by Ansaar. A round trip means the same client name, in the same stock, on the same day, appears both as a buyer and as a seller in the bulk-deal list. Rupee totals are the buying side only. Mutual funds are identified by the words "Mutual Fund" in the client's name, so funds disclosed under other names are not counted. This is exchange data, not a model estimate.

Key takeaways

A bulk deal is more than 0.5% of a company's listed shares in a day, in the normal market; a block deal is one trade of at least ₹25 crore in a separate window.
Block deals must end in delivery; bulk deals can be reversed the same day.
72% of NSE bulk deals since 2023 were same-day round trips, mostly by trading firms.
Bulk deals cluster in small companies; large stakes change hands through block deals.
A deal tells you what happened, not why, and nothing about the next price.

See the latest session's deals, with the client, the direction, quantity and price: NSE bulk deals and NSE block deals. For the bigger picture of foreign and domestic money, read How to Read FII/DII Data.

How the numbers were computed

Each row is one client's side of a deal: date, symbol, client, direction, quantity and price. Two things matter.

1. Every trade appears twice. A block deal between a seller and a buyer is disclosed once for each of them, so summing every row doubles the rupee value. The totals above count only the buying side.

2. A round trip is a group, not a row. Group bulk-deal rows by trade date, symbol and client, and flag the groups that contain both directions:

from collections import defaultdict

sides = defaultdict(lambda: {"BUY": 0.0, "SELL": 0.0, "rows": 0})
for r in bulk_deals:                      # one dict per disclosed row
    key = (r["trade_date"], r["symbol"], r["client_name"])
    sides[key][r["direction"]] += r["quantity"] * r["price"]
    sides[key]["rows"] += 1

round_trips = [k for k, s in sides.items() if s["BUY"] and s["SELL"]]
share = sum(sides[k]["rows"] for k in round_trips) / len(bulk_deals)
print(f"{share:.1%}")                     # 72.3% for Jan 2023 to Sep 2026

That is the whole method. Client names are matched exactly as the exchange publishes them, so a firm disclosed under two spellings would be split, and the 72% is if anything a slight undercount.

Questions people ask

What is the difference between a bulk deal and a block deal?

A bulk deal is any trade, or set of trades, in which one client buys or sells more than 0.5% of a company's listed shares in a single day, in the normal market. A block deal is one large trade of at least ₹25 crore, done in a separate trading window twice a day, and it must end in delivery. So a bulk deal is defined by its share of the company, a block deal by its size and where it is traded.

What is the minimum size of a block deal?

₹25 crore, since 7 December 2025. SEBI raised it from ₹10 crore in its revised block deal framework, dated 8 October 2025. There is no rupee minimum for a bulk deal. What makes a trade a bulk deal is crossing 0.5% of the company's listed shares.

What are the block deal window timings?

There are two windows. The morning window runs from 8:45 am to 9:00 am, priced within 3% of the previous day's close. The afternoon window runs from 2:05 pm to 2:20 pm, priced within 3% of the volume-weighted average price between 1:45 pm and 2:00 pm.

Does a large bulk purchase mean the buyer expects the stock to rise?

Not necessarily, and usually not. In the NSE's bulk-deal disclosures from January 2023 to September 2026, 72% of bulk deals were round trips: the same client bought and sold the same stock on the same day. Those are mostly trading firms, not investors taking a position. Even a genuine purchase tells you what happened, not why, and nothing about what the price will do.

Where can I see today's bulk and block deals?

The NSE publishes both after the market closes each trading day. Ansaar republishes them on its bulk deals and block deals pages, with the latest session in full and recent history. Because they come out after the close, the newest figures are usually for the previous trading day.

This guide first appeared on Ansaar, a free quantitative-research site for Indian markets. It is educational material, not investment advice.

Can DuckDB be your SaaS product's warehouse? Where the ceiling actually is

Mohammed Arshad Ansari — Wed, 23 Sep 2026 14:00:00 +0000

A recurring question, roughly: we're a small SaaS, we need analytics — internal dashboards and eventually a customer-facing one. Can DuckDB be the warehouse, or do we need Snowflake/BigQuery from day one?

Almost always: yes, it can, for longer than you'd think. But "small SaaS" hides three very different workloads, and only two of them fit comfortably. Here's how to tell which you have.

The three workloads hiding behind "analytics"

1. Internal analytics. You and a few colleagues asking questions about signups, churn, revenue. Handful of people, no concurrency to speak of, latency measured in "before my coffee gets cold."

DuckDB fits this with enormous room to spare. This is the easiest yes in data engineering. A nightly job writes Parquet, DuckDB reads it, your BI tool or notebook queries it. Done.

2. Embedded customer-facing analytics. A dashboard inside your product — each customer sees their own numbers. This is where it gets interesting, and where the answer becomes "yes, with a specific design."

3. A shared analytics platform for a data team. Many analysts, ad-hoc SQL, governance, roles. This is a warehouse workload. If you genuinely have this, buy a warehouse. Most companies asking the question do not have this yet, and some never will.

The mistake is buying for (3) while actually living in (1).

Where the ceiling is, concretely

The instinct is to think about total data volume. That's the wrong number. Three things bound a single-node design, roughly in the order you'll hit them:

Concurrent write throughput. DuckDB is single-writer. One process writes at a time. For a SaaS this is usually fine because analytics writes are batch — a job runs, produces Parquet, exits. It becomes a problem the moment you want near-real-time ingestion from multiple services writing continuously. That's the first real ceiling, and it arrives independent of data size.

Concurrent query volume. A single node serving customer-facing dashboards can handle a genuinely surprising number of requests when queries are small and well-partitioned — but it's one machine. When p99 latency starts drifting under load and you're already caching, you've found the second ceiling.

Working-set size. Last, not first. Modern servers take a lot of RAM, and Parquet with good partitioning means a query touches a fraction of the data. Teams hit the concurrency ceilings long before the size ceiling.

Notice none of these is "how many GB do you have". Which is exactly why "we have 500GB, we need Snowflake" is a non-sequitur.

This is the first part. The full post — including the rest of the working details — is on my site: Can DuckDB be your SaaS product's warehouse? Where the ceiling actually is

Market regime detection in production: what the model actually changes

Mohammed Arshad Ansari — Tue, 22 Sep 2026 03:30:00 +0000

A while ago I wrote up two ways to detect market regimes: hidden Markov models and clustering on Wasserstein distance. That post was research on a toy S&P 500 series. This one is what actually runs, every trading day, inside the trading system behind Ansaar.

The short version: the model turned out to be the small part. What made it safe to run without me watching was everything around it.

Three states, three features

The model is a GaussianHMM from hmmlearn with three states. I tried more. The Bayesian information criterion kept picking three, and three is also the number a human can act on.

State	What it looks like
Bull trend	Positive returns, moderate volatility
Bear trend	Negative returns, elevated volatility
Sideways	Near-zero returns, volatility all over the place

It sees three features, and only three:

ret_5d, the 5-day log return
realized_vol_21d, the 21-day rolling standard deviation, annualised
return_vol_ratio, the first divided by the second

I had a longer list at one point. Every feature I added made the fit look better in-sample and the labels worse out of it. Three features is enough to separate "going up calmly" from "going down violently" from "going nowhere", and that is the whole job.

The bug that scaling fixed

The first version fed those three features to the model raw. It trained fine, the states had names, and then I looked at the state statistics and the bear trend had a positive average return.

The cause was scale. return_vol_ratio moves over a much wider range than a 5-day return, and unscaled it had about 19 times the influence of ret_5d. The model was clustering on the ratio and mostly ignoring the return. The tell was March 2020: the COVID crash, the most obvious bear regime in the training window, was classified as sideways.

A StandardScaler in front of the model fixed it, and the verification is the part I keep:

March 2020: 100% of days classified as bear trend
23 March 2020, the worst single day: bear trend, a -13.93% return at 70.3% volatility, with 100% state probability

from hmmlearn.hmm import GaussianHMM
from sklearn.preprocessing import StandardScaler

scaler = StandardScaler()
X = scaler.fit_transform(features[["ret_5d", "realized_vol_21d", "return_vol_ratio"]])

model = GaussianHMM(n_components=3, covariance_type="full", random_state=42)
model.fit(X)

# Keep the scaler with the model. Scoring unscaled data later is the same bug again.

If you take one thing from this post, take that: check the model against a day you already know the answer to.

This is the first part. The full post — including the rest of the working details — is on my site: Market regime detection in production: what the model actually changes

Running DuckDB on your own infrastructure: a production setup

Mohammed Arshad Ansari — Mon, 21 Sep 2026 14:00:00 +0000

If you're asking how to run DuckDB on your own infrastructure for faster queries, you've already made the important decision. What's left is mostly avoiding four or five specific mistakes that make a single node look slower than it is.

This is the setup I actually run.

Start with the shape, not the settings

The layout matters more than any tuning flag:

object storage (or a local disk)
  └── warehouse/
      └── events/
          dt=2026-08-11/part-0.parquet
          dt=2026-08-12/part-0.parquet
          dt=2026-08-13/part-0.parquet

one writer process   →  produces new partitions
N reader processes   →  open read-only, query across partitions

One writer. Many readers. Immutable partitions. Everything else is detail.

If you take one thing from this: don't put a mutable .duckdb file at the centre of a multi-process system. DuckDB is single-writer, and the moment two processes want to write, you're fighting the design. Write Parquet, read Parquet.

The settings that actually matter

Most DuckDB tuning advice is noise. These four are not:

-- Leave real headroom. This is DuckDB's budget, not the container's.
SET memory_limit = '12GB';

-- Spilling must land on real disk with real space.
SET temp_directory = '/var/lib/duckdb/tmp';

-- Match the cores you actually have, not the ones the host advertises.
SET threads = 8;

-- Only if you're reading from object storage.
SET preserve_insertion_order = false;

memory_limit should sit meaningfully below your container limit — I use roughly 70–75%. DuckDB accounts for its own buffer pool, not for the Python process around it, the Arrow tables in flight, or the runtime. Set it to the container limit and the orchestrator kills you before DuckDB ever decides to spill.

temp_directory is the one that bites in containers. The default may point at a path on the container's ephemeral layer, which is often small and sometimes memory-backed. A large join then fills it and the pod dies looking like an OOM when it's really a disk problem. Mount a volume and point at it.

threads matters in Kubernetes specifically. DuckDB sees the host's core count, not your CPU limit. On a 64-core node with a 4-core limit, it will happily spawn 64 threads and spend its life being throttled. Set this from your actual limit.

preserve_insertion_order = false lets DuckDB parallelise reads more aggressively when you don't care about row order — which, for aggregate queries over Parquet, you usually don't.

This is the first part. The full post — including the rest of the working details — is on my site: Running DuckDB on your own infrastructure: a production setup

Is DuckDB safe for production? The honest limitations

Mohammed Arshad Ansari — Fri, 18 Sep 2026 14:00:01 +0000

"Is DuckDB safe for production?" is the right question asked slightly wrong. DuckDB is not a scaled-down toy that becomes safe once you're brave enough. It's a database with a specific concurrency and durability model, and it is completely safe inside that model and genuinely unsafe outside it.

So the useful version of the question is: safe for which workload? Here's what actually bites, in the order it bites people.

The single-writer model is the whole story

DuckDB allows one writing process at a time against a database file. Multiple threads inside that process are fine. Many processes can read the file at once, as long as none of them is writing. Two processes both opening the file for writing are not.

This is not a bug or a temporary limitation — it's the design. DuckDB is an in-process database, like SQLite. There is no server arbitrating between clients, because there is no server.

Almost every "DuckDB isn't production-ready" story I've heard traces back to this. Someone runs the ETL job on a schedule, someone else points a dashboard at the same file, and eventually the two overlap. What you get is a lock error, or — if you were clever enough to copy the file to dodge the lock — a reader seeing a half-written state.

The design that avoids it entirely:

                    ┌─ writer job ──► Parquet partitions on S3
                    │                   (immutable, append-only)
  source systems ───┤
                    └─ readers ────► DuckDB, N processes, read-only

Writers never share a mutable file. They produce immutable Parquet partitions. Readers open those partitions read-only, as many processes as you like, with no coordination at all. The concurrency problem disappears because you removed the shared mutable state, not because you managed it better.

If you must use a .duckdb file with concurrent readers, open them explicitly read-only:

import duckdb

# Safe: many of these can run at once against the same file.
con = duckdb.connect("analytics.duckdb", read_only=True)

Read-only processes share the file with each other but not with a writer: while any process holds it read-write, the others cannot open it at all. That is why the writer should produce Parquet rather than update the file the readers use. DuckDB will also refuse a write through a read-only connection rather than corrupt anything.

Durability: it's ACID, but know what that covers

DuckDB is ACID-compliant with write-ahead logging. A transaction that commits is durable; a crash mid-transaction rolls back cleanly. On this axis it behaves like a real database, because it is one.

What people actually get wrong is what sits around the transaction:

The file is a single point of failure. ACID protects you from a crash. It doesn't protect you from a deleted file, a corrupted volume, or an ephemeral container's disk vanishing at the end of the run. If your database lives on a pod's local disk, it lives exactly as long as the pod.
A .duckdb file is not a backup format. Copying it while a writer is mid-transaction gives you a file whose contents are undefined. Snapshot by exporting (EXPORT DATABASE) or by treating Parquet as the durable layer.
Storage format compatibility. DuckDB reached 1.0 with a stability commitment, and files are forward-compatible within that line — but a file written by a newer version isn't necessarily readable by an older one. Pin the version in production and upgrade deliberately, the same as any database.

The rule I use: Parquet is the durable artefact, the .duckdb file is a cache. If losing the file would be a data-loss incident rather than an inconvenience, the architecture is wrong, not DuckDB.

This is the first part. The full post — including the rest of the working details — is on my site: Is DuckDB safe for production? The honest limitations

DuckDB vs Snowflake: the 4 questions that decide it

Mohammed Arshad Ansari — Wed, 16 Sep 2026 14:00:00 +0000

DuckDB and Snowflake get compared as if they're competitors. They mostly aren't. They answer different questions, and picking the wrong one is how you end up either paying for a warehouse you don't need or outgrowing a laptop-sized tool in production. Here's how I decide.

They're built for different shapes of problem

Snowflake is a cloud data warehouse. It separates storage from compute, scales elastically, handles many concurrent users, and comes with governance, sharing and a large ecosystem. You point a cluster at your data and a hundred analysts can query it at once. You pay per second of compute, by the credit.

DuckDB is an in-process analytical database — think "SQLite for analytics." It runs inside your Python process, your laptop, or a single server. No cluster, no service to operate, no per-query meter. It reads Parquet and CSV directly, including files sitting on S3, and it is very fast on a single machine.

One is a rented warehouse with a loading dock and a staff. The other is a workbench in your own garage. The question is which one the job needs.

The decision usually comes down to four things

1. Does your data fit on one big machine? This is the one people get wrong. "Big data" is rarer than the marketing suggests. A single modern server handles hundreds of gigabytes to a few terabytes comfortably, and DuckDB is built to use all of it. If your working set is in that range — and most companies' is — a single-node engine is not a compromise, it's the right tool. If you're genuinely at tens of terabytes scanned per query, or petabytes at rest, that's Snowflake territory.

The number that matters is not your total data. It's the working set: the bytes a typical query actually touches. Columnar Parquet plus predicate pushdown means a query over a 2 TB table that filters to last month and selects six columns may read a few gigabytes. Teams routinely provision for the 2 TB and never measure the few gigabytes.

2. How many people query it at once? DuckDB is fundamentally single-node. It's perfect for one analyst, a transformation job, or an app backend serving queries it controls. It is not built for fifty analysts running ad-hoc dashboards simultaneously. Concurrency at that scale is exactly what Snowflake's elastic compute is for.

Be precise about what "concurrency" means for you, though. Fifty people with a dashboard open is not fifty concurrent queries — it's fifty mostly-idle browser tabs hitting a cache. Fifty analysts writing exploratory SQL at 10am on a Monday is concurrency. The first case a single node handles fine behind a result cache; the second it does not.

3. Who runs it, and do they want to run anything? Snowflake is zero-ops — there's no server to keep alive. DuckDB has nothing to operate either, but only because it lives inside something you already run. If you want a managed, hands-off, governed platform for a whole org, that's Snowflake. If you want a fast engine embedded in a pipeline or a notebook, that's DuckDB.

This is the question teams answer emotionally. "We don't want to manage infrastructure" is usually true and usually decisive — but notice that DuckDB-over-Parquet has no infrastructure to manage either. What it has is no vendor to call. Those aren't the same thing, and which one you're actually buying matters.

4. What's the cost model doing to you? Snowflake bills compute by the second against credits. That's elastic and fair when usage is spiky, and brutal when a scheduled job or a careless dashboard leaves a warehouse running. DuckDB's compute cost is whatever the machine it runs on already costs — often effectively zero, because it's your existing CI runner, app server or laptop.

The asymmetry worth internalising: Snowflake's bill scales with how you query, DuckDB's with what you already rent. A badly-written query on Snowflake costs money every time it runs. The same query on a box you're already paying for costs nothing extra — it's just slow, which is a problem you can see and ignore.

This is the first part. The full post — including the rest of the working details — is on my site: DuckDB vs Snowflake: the 4 questions that decide it

The most dangerous API response is HTTP 200 with an empty body

Mohammed Arshad Ansari — Tue, 15 Sep 2026 10:30:00 +0000

Every pipeline eventually inherits a source that moves. Ours did: dataservices.imf.org
stopped resolving in DNS entirely — not a 500, not a timeout, the hostname itself was gone.
The IMF had migrated its data platform and the old host was on its way out. No villain in
this story; following a publisher to its new home is the consumer's job.

The interesting part is the failure mode the migration exposed, which has nothing to do with
the IMF and everything to do with how most of us write ingestion code.

The symptom

Monthly CPI for a set of non-OECD countries went quietly stale, frozen at December 2025, while
every other source kept ticking. Nothing screamed. The daily job ran green. Downstream scoring
kept computing on last-known values, because CPI resolves through a fallback chain — if the
freshest monthly print is unavailable, the model reaches for the next-best source rather than
failing outright.

That fallback is a feature: one dead source should not take scoring down for a whole tier of
countries. It also has a cost. Graceful degradation and silent staleness are the same
mechanism viewed from two angles. A freshness dashboard is what keeps the second angle
visible; without one, the design that saves you also hides the problem from you.

The migration itself

The new home is api.imf.org, on SDMX 2.1, and it still speaks the same StructureSpecific XML
dialect — so the parser barely changed. All the pain was in the keys:

CPI now lives on the IMF.STA,CPI dataflow, and the all-items series is keyed with COICOP _T (the SDMX "total" convention), not the CP00 the old platform used.
Balance-of-payments moved to IMF.STA,BOP, where the legacy indicator codes do not port one-to-one. No find-and-replace; each series had to be re-derived against the new structure definition and re-tested.

Tedious rather than clever — and easy to get subtly wrong, because of what a wrong key does.

The gotcha worth stealing

A bad key returns HTTP 200 with an empty DataSet.

Not a 404. Not a 400. A cheerful 200 OK, a well-formed SDMX document, zero observations
inside it. If your ingestion trusts the status line:

resp = await client.get(url)
if resp.status_code == 200:          # &lt;- the bug
    rows = parse(resp.text)
    await store(rows)                # stores nothing, reports success

…then a typo in a dimension code, or an un-migrated series key, sails through as success and
writes nothing. Job green. Data frozen. You find out weeks later from a freshness alert, if
you have one, or from a customer, if you do not.

Our fix is a canary: the request always carries a segment we know must return data — United
States all-items CPI — and a parse that yields zero series therefore cannot mean "no data".

series = parse_sdmx21_data(xml_bytes)
if not series:
    raise IMFIFSUnavailableError(
        "IMF.STA,CPI returned an empty DataSet incl. the USA canary — "
        "key schema change or platform fault"
    )

The USA rows are then filtered out before storage — its index base differs from our other
US source and double-sourcing would corrupt the derived series. The canary lives in the
request, not in the warehouse.

Three lines, and it is the difference between finding a structural break in one run versus one
month. Having built it for CPI, it generalises to every SDMX-style source that can return a
well-formed empty response: pick a segment that must exist, assert it came back populated, let
a broken key contract throw. Note what it is not for: a genuine upstream outage is a
different event, and that one deliberately does not raise — it materialises with an
imf_ifs_outage flag, because failing hard there would skip every downstream scoring asset in
the same run.

The same bug one layer up: a fetch that succeeds is not a document

The identical mistake, in a different shape, was sitting in our central-bank statement
scrapers. They fetched a listing page, followed a link, extracted text, and handed it to an
LLM for a hawkish/dovish read. Every step returned 200. Every step "worked".

What was actually being scored, once we read the stored text back:

a bank's login form, because its statement listing redirects to /login
a press-release index whose top headline was Treasury-bill auction results
a SharePoint "you may be trying to access this site from a secured browser" notice
115 characters of "this page depends on JavaScript"
a rates statistics nav link, because the link pattern interest.*rate matched it and it sorted first in the DOM

The parallel to the empty DataSet is exact: nothing failed, so nothing raised, so a
plausible-looking value went downstream. The fixes were the same shape as the canary —
assert the content, not the transport:

A length floor and a bot/JS-notice check, so a page that cannot be a statement is skipped rather than scored.
Feeds and APIs instead of HTML scraping where the bank publishes one (RSS/Atom, or the PDF-minutes API where the web page is a JS shell).
A source is only enabled once its extracted text has been read back and confirmed to be a monetary policy decision — and a source that cannot pass is switched off in config with the reason recorded, not left nominally "covered". Fifteen of twenty configured banks are on; five are off behind commercial bot management, a client-rendered shell, hard 403s on the decision pages, or a listing API that refuses anonymous reads — protections we decline to work around.

Two reusable lessons

Assert data presence, not status codes. 200 OK means the HTTP conversation succeeded. It says nothing about whether the payload contains what you asked for.
Connection outages and silent-empty responses are different failure modes needing different handling. A DNS failure throws — retry with backoff and you will know. A 200-plus-empty never throws, so try/except + retry leaves it completely uncovered. It needs an affirmative presence check. Handle both, separately, on purpose.

The payoff, incidentally, was not just damage control: following the source to its new home
took BOP coverage from roughly 70 to 148 countries and made reserves available quarterly. The
endpoint you are reluctantly forced onto is often the one the publisher is actually investing
in.

Full write-up, with the migration detail:

What the pipeline feeds — a daily 0–100 credibility score for 169 countries on 100% free
public data, with per-indicator provenance: