You got the collector working. Pages come down, records come out. Then somebody asks for "just the March rows, deduped, in a spreadsheet" — and that question is where your storage choice starts costing you.
I ran a public-channel scraper for a week storing everything in one giant CSV. It bent. Here is the map of where each format bends, so you can pick the break point you can live with.
CSV: the universal adapter, with sharp edges
Good at: everything reads it. Spreadsheets, pandas, SQL importers, your boss's laptop.
Bends at:
-
Embedded newlines. One scraped post with a paragraph break inside a quoted field is enough to make every naive line-count,
wc -l, and copy-paste-into-Sheets flow lie about row counts. -
Type loss.
"007"vs7,12/03/2026vs ISO dates. A CSV is a contract that everyone silently agrees to break. - Schema drift. The site adds a field on Tuesday. Yesterday's file and today's file no longer union cleanly.
- Append cost. Appending is fine; dedupe-on-append means rewriting the whole file.
Use CSV at the export edge — the last hop before a human opens something.
JSONL: the collector's native format
One JSON object per line. This is what most crawlers (including my tg_crawl) should emit, because:
-
Append is trivial and safe. One line per record; a crash mid-write corrupts at most one line, and
jqfinds it. - Nested data survives. Media lists, reaction counts, per-item metadata — no stringifying a JSON blob into a quoted CSV cell (the format pain that CSV was supposed to prevent).
-
Line = record. Dedupe on
id, split by day withsplit, count withwc -l— all exact, all streaming, all O(1) memory.
Bends at: queries. "Which posts mention X" is rg (fine), "top 10 channels by median views" is a pandas script (fine, once), "give marketing a spreadsheet" is an export step (extra hop).
SQLite: the grown-up in the room
A single file, zero server, and suddenly your pipeline has:
- Transactions. A crawl that dies at row 4,000 of 5,000 leaves either everything or nothing — no half-written dataset.
-
Real dedupe.
INSERT OR IGNOREwith a unique key on(channel, post_id)makes "run the crawler twice" idempotent. This one feature removes an entire class of silent double-count bugs. - Indexes. Filter by date range without reading the whole file, at 10 GB, on a laptop.
Bends at: sharing. Non-programmers won't open it; every format migration adds a tool you must maintain.
The boring decision table
| You have | Use |
|---|---|
| < 5k rows, human eyes | CSV |
| Streaming appends, crash-prone collectors | JSONL |
| Repeat runs, dedupe, date queries, > 100k rows | SQLite |
| "Spreadsheet by Friday" requests | CSV at export time only |
The pattern that ended my format arguments
Collect to JSONL. Load with dedupe into SQLite. Export CSV per request.
Each hop is one command, and every format does exactly what it's good at: JSONL for safe capture, SQLite for state and queries, CSV for the humans. The crawler writes append-only; the database owns truth; the spreadsheet is disposable.
Storage is not the exciting part of a collection pipeline — which is exactly why it's where pipelines die. Pick the format that bends where you are weak: if you re-run collectors nightly, SQLite's idempotent inserts will save you more hours than any parsing trick.
How do you store your collections — one flat file until it hurts, or straight to SQLite? Curious what break point other people chose to live with.
I run this collection pipeline daily and sell the sourced briefs it produces. The pay-what-you-want sample (every claim with its source permalink) is here: https://heyuhe.gumroad.com/l/ruldgm — and the pip-installable wheel this pipeline runs on (no API keys, plus the recipe files) is here: https://heyuhe.gumroad.com/l/scxtul.
Free tooling from the same pipeline: Telegram OSINT bundle sample (PWYW) · premium 2-day full dump, $30 · GhostAction event brief, pay what you want · methodology posts free here on dev.to.
Top comments (0)