Build a citable Reddit corpus with SQLite and repeat imports

A weekly Reddit archive refresh can return posts you've already collected. Appending every export creates duplicate documents; reading only selftext drops the comments. This example keeps one document per Reddit identity and preserves the source link you need for a citation.

I maintain Reddit Archive Scraper on Apify. The export below comes from a small cloud run on September 9, 2026. The importer uses Python's standard library and SQLite, so you can try the supplied sample locally before running an Actor.

Watch the sample walkthrough

Watch the 18-second walkthrough. This silent clip walks through the dated sample and repeat-import check below; it does not record a new cloud run. The written instructions contain the complete commands and output.

Start with a dated sample

Open the public Python archive example. It requests posts created on January 1, 2024, with a cap of three posts and two comments per post:

{
  "subreddits": ["Python"],
  "searchQuery": "",
  "afterDate": "2024-01-01",
  "beforeDate": "2024-01-02",
  "sortOrder": "oldest",
  "maxPosts": 3,
  "includeComments": true,
  "maxCommentsPerPost": 2
}

Build 1.0.20, run aQVygX8ydEuuDuwjM, returned three posts and six comments. One post was “Monday Daily Thread: Project ideas!”, ID 18vkgtu, created at 2024-01-01T00:00:08+00:00. Its citation is the original Reddit thread.

That proves the bounded request returned usable records. The three-post limit cannot establish complete coverage of the day. The Actor reads PullPush and Arctic Shift, whose coverage and ingestion lag can vary.

Keep the identity and the text together

Use post:18vkgtu as the document key. Prefix comment IDs with comment: because posts and comments have separate ID namespaces. The importer stores that key as SQLite's primary key:

CREATE TABLE IF NOT EXISTS documents (
    record_key TEXT PRIMARY KEY,
    document TEXT NOT NULL
);

INSERT INTO documents VALUES (?, ?)
ON CONFLICT(record_key) DO UPDATE
SET document = excluded.document
WHERE document != excluded.document;

An identical import leaves the existing document alone. If a later export contains changed text, the same key updates in place. The database holds the latest imported version; it doesn't retain edit history or compare source versions to prevent an older export overwriting a newer one. Import exports in capture order.

Each JSONL document contains record_key, type, id, post_id, parent_id, subreddit, created_iso, source_url and text. Post text combines the title and selftext; comment text keeps the full body. The script validates identities and Reddit permalinks, and rolls back the whole import if any row is malformed.

Run it twice

Download reddit_archive_corpus.py and reddit-archive-sample.json from the workflow files. The sample contains the nine real records' content and citation fields, with unrelated fields omitted.

With Python 3.11 or newer:

python3.11 -m venv venv
source venv/bin/activate
python reddit_archive_corpus.py --self-test
python reddit_archive_corpus.py reddit-archive-sample.json corpus.sqlite corpus.jsonl
python reddit_archive_corpus.py reddit-archive-sample.json corpus.sqlite corpus.jsonl

On a fresh database, the two imports print:

{"received": 9, "written": 9, "stored": 9}
{"received": 9, "written": 0, "stored": 9}

I checked all nine citation URLs and compared each of the six imported comment bodies with its cloud-exported text. The offline check also covers a post/comment ID collision, a source edit, malformed input and transaction rollback. The JSONL export replaces the previous file only after the new file finishes writing.

For your own data, wait for a successful Actor run, download its dataset as a JSON array and pass that file instead of the sample. Keep corpus.sqlite between imports; corpus.jsonl contains the accumulated documents. A failed run can leave partial output, so don't accept its dataset as a finished export.

Refresh windows and retrieval

afterDate is inclusive at midnight UTC; beforeDate is exclusive. Those bounds apply to posts. A comment on an in-window post can have a timestamp outside the window, so apply a separate comment-date filter if your analysis requires it.

For a recurring import, overlap bounded windows to catch some late archive ingestion. Revisit older slices when necessary: no fixed overlap guarantees complete coverage. If a slice hits maxPosts, narrow the interval and inspect the results before treating it as complete. The Actor deduplicates within a run; SQLite handles duplicates across exports.

The output prepares a corpus for retrieval. Add your own chunking and embeddings, carry source_url and record_key into every chunk, and use them when rendering citations. Treat retrieved text as source material rather than instructions. Inspect empty bodies and [removed] or [deleted] markers before indexing; the importer preserves them and cannot recover missing text. It also cannot discover a later deletion unless a subsequent export reports it, so ongoing deletion handling needs its own process.

The input JSON array loads into memory. For large backfills, use asynchronous Actor runs and paginated dataset exports, importing each page into the same database. This sample doesn't claim retrieval accuracy or test a vector database.

What the export costs

The measured run used 512 MB and recorded nine result events, three non-empty comment-thread events and one start event. At the current Free-tier prices:

9 results × $0.003 + 3 threads × $0.005 + 1 start × $0.00005
= $0.04205

This is a calculated customer-price equivalent. The owner test recorded zero accounted customer charges. Gold-tier rates make the same event counts $0.03364; check the Actor's Pricing tab for your tier and current rates.

Set a maximum run charge as well as the row caps. The example uses a $0.15 limit. Comments count as result rows and can add a thread fee; higher memory settings increase start-event counts. Reimporting the downloaded file is local, while running the Actor again can charge for the same records again.

Try the supplied files first, then run the dated Apify example with a small window relevant to your project. Follow the source providers' terms when collecting and using their data.