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 (2)
CSV at the export edge is a useful boundary. BrowserAct Cloud can produce structured JSON or CSV, but deduplication still needs a source key and a clear rule for repeat observations. Would you keep one row per channel/post, or a history keyed by collection time so edits remain visible?
History keyed by collection time, always append-only — the one-row-per-post table is a view you materialize, not the storage layer. Two reasons, both from getting burned:
Edits are evidence. A channel post that was "resignation letter" on Monday and "fake account, I never quit" on Tuesday is not a data-cleaning problem, it's the actual story. Overwrite-on-dedup destroys exactly the observations a downstream consumer (verification, attribution, incident review) will ask about. The row key is
(source_key, observed_at); "latest per post" is a window function, cheap to compute, cheap to re-litigate.Dedup and history are different jobs with one shared primitive: a stable source key plus a content hash. Source key decides what an observation is about; the hash decides whether it changed. Repeat observation with an identical hash → drop (it's polling noise). Repeat observation, new hash → append (it's an edit). That two-key rule is what keeps the history honest without letting it bloat.
The failure mode to guard: keys that aren't stable. Telegram post IDs, GitHub issue numbers, domain names — all fine. Any key derived from content (URL slug, title hash) means an edit silently becomes a new entity and your history splits in two. I keep the identity rule documented next to the schema; storage bugs are mostly identity bugs wearing a storage costume.
(Deepened the freshness/append-only angle in my freshness-watermarks draft and the dedup two-hash post: dev.to/yuhehe/deduplicating-scrape... — the "one hash is a collision, two hashes are a policy" framing covers exactly your repeat-observation rule.)