INGOAMPT · Learn

SQLite, Transactions & Record Matching: A Developer’s Vocabulary Guide

Two questions sit underneath almost every serious data application: “how do I store millions of records without ever losing or corrupting one, even if the power cuts out?” and “how do I match a messy, typo-filled record against the right one out of millions?” This guide answers both, term by term, with figures, tables and real code — for complete beginners.

The running example. Picture a service where someone uploads a messy, typo-filled list of book and article references — like a student’s badly-formatted bibliography — and the system must find the correct, verified record for each one from a library of millions, flag anything it isn’t sure about, and never simply invent an answer. That’s the example we’ll use throughout.

Part 1 · Storing Data So It Survives Anything

Before you can match anything, you need a database that never quietly loses or corrupts a record — not even if someone trips over the power cable mid-write. These twelve terms are how that promise is kept.

1. SQLite

Big databases like PostgreSQL or MySQL run as a separate background program that your app talks to over a network connection, even if that network is just localhost. SQLite skips all of that: the database is the file.

<span class="c"># That's really it — no server to start</span>
<span class="k">import</span> sqlite3
conn = sqlite3.connect(<span class="k">"catalog.sqlite3"</span>)
conn.execute(<span class="k">"SELECT COUNT(*) FROM records"</span>)

2. Schema, Table, Row & Column

Picture our reference-matching database. One table, records, might look like this once populated:

↑ Each horizontal line is a row. Each vertical field is a column. The whole table’s shape (which columns exist, what type each holds) is defined by the schema.

<span class="c">-- The schema: the rules for this one table</span>
<span class="k">CREATE TABLE</span> records (
    record_id INTEGER <span class="k">PRIMARY KEY</span>,
    title     TEXT    <span class="k">NOT NULL</span>,
    year      INTEGER,
    doi       TEXT
);

3. Primary Key & Foreign Key

Say each reference can have several authors. You’d naturally split that into two tables — one row per reference, one row per author-of-a-reference:

<span class="k">CREATE TABLE</span> authors (
    author_id INTEGER <span class="k">PRIMARY KEY</span>,
    record_id INTEGER <span class="k">NOT NULL</span>,
    name      TEXT,
    <span class="k">FOREIGN KEY</span> (record_id) <span class="k">REFERENCES</span> records(record_id)
);

4. Transactions, Commit & Rollback

Storing one reference actually means writing to several tables — the record itself, its authors, its identifiers. If you save them one at a time and the program crashes halfway, you get a broken half-record. A transaction prevents that:

<span class="k">BEGIN</span>;                                   <span class="c">-- open the transaction</span>
  <span class="k">INSERT INTO</span> records VALUES (3, <span class="k">'BERT: Pre-training...'</span>, 2018, <span class="k">NULL</span>);
  <span class="k">INSERT INTO</span> authors VALUES (4, 3, <span class="k">'Devlin'</span>);
  <span class="k">INSERT INTO</span> authors VALUES (5, 3, <span class="k">'Chang'</span>);
<span class="k">COMMIT</span>;                                  <span class="c">-- ✅ all 3 writes saved together</span>

<span class="c">-- If something fails before COMMIT:</span>
<span class="k">ROLLBACK</span>;                                <span class="c">-- ❌ all 3 writes are undone, none remain</span>

5. Atomicity

It’s the first letter of the classic ACID guarantees every serious database offers:

Atomicity, Consistency, and Isolation hold true in SQLite under essentially every setting. As you’ll see in section 9, the Durability guarantee is the one knob you can deliberately loosen for speed.

6. PRAGMA

Normal SQL (SELECT, INSERT) works with your rows. A PRAGMA works with the engine’s settings:

<span class="k">PRAGMA</span> foreign_keys = <span class="k">ON</span>;       <span class="c">-- turn on foreign-key enforcement</span>
<span class="k">PRAGMA</span> journal_mode;             <span class="c">-- ask: "what journal mode is actually active?"</span>
<span class="k">PRAGMA</span> integrity_check;          <span class="c">-- ask: "is this database file healthy?"</span>
<span class="k">PRAGMA</span> busy_timeout = 5000;      <span class="c">-- wait up to 5000ms if the database is locked</span>

Two of these — journal_mode and synchronous — control how safely and how fast your data survives a crash. They’re the subject of the next three sections.

7. Journal & Journal Modes

Three common modes, each a different strategy for the same goal:

<span class="k">PRAGMA</span> journal_mode = <span class="k">WAL</span>;
<span class="c">-- SQLite may not grant exactly what you asked — always check what it actually gave you:</span>
<span class="k">PRAGMA</span> journal_mode;   <span class="c">-- → "wal"  ✅ confirmed</span>

8. WAL Mode (Write-Ahead Logging)

In the default DELETE mode, a writer briefly locks out all readers. Under WAL, readers see a consistent snapshot built from the main file plus the WAL’s committed entries, while a writer keeps appending in the background:

WAL entries eventually get folded back into the main file — a process called a checkpoint (covered in section 11). Unlike other modes, WAL is “sticky”: once set, it stays active even after you close and reopen the database.

9. Synchronous & the Durability Trade-off

Here’s the layered reality most beginners miss: a “saved” write can actually be sitting at three different depths.

10. Busy Timeout

<span class="k">PRAGMA</span> busy_timeout = 5000;   <span class="c">-- wait up to 5 seconds for the lock to clear</span>
<span class="c">-- Without this, a second writer fails INSTANTLY with "database is locked"</span>

Picture two parts of your program both wanting to write at nearly the same moment. Without a busy timeout, the second one crashes immediately. With a reasonable timeout, it simply waits — usually a fraction of a second — and then proceeds normally.

11. Checkpoints — Two Related Meanings

a) WAL checkpoint (inside SQLite)

Remember that WAL appends new pages to a side file rather than writing the main file directly. A WAL checkpoint is the process of folding those appended pages back into the main database file, so the side file doesn’t grow forever.

b) Ingestion checkpoint (inside your own pipeline)

When importing millions of records, you don’t want a crash at record 8,000,000 to force you back to record 1. So your own code records, inside the same transaction as the data itself, exactly how far it got:

The crucial rule: the data rows and the checkpoint marker must be saved in the very same transaction. Otherwise a crash could leave them disagreeing — for instance, rows committed through 3,000 but the checkpoint still saying 2,000, or worse, the reverse.

12. Benchmark & Median

Suppose you time importing 10,000 records under four different SQLite settings, three runs each, in rotated order (to cancel out disk-cache warm-up bias):

Notice run 3 of delete+full: a one-off disk hiccup pushed it to 31.4s. The average of that row would be dragged up to ~22.5s, giving a misleading picture. The median (18.2s) ignores that outlier — which is exactly why engineers report medians for performance benchmarks.

Part 2 · Matching Messy Records to the Truth

The data is now safely stored. The next challenge is different: someone hands you a messy, incomplete, typo-ridden reference, and you must find — and trust — the correct record out of millions. These eight terms describe that matching pipeline end to end.

13. Canonical Record

Source data is often messy or inconsistent in ways that are fine for humans but painful for a matching algorithm: extra whitespace, mixed capitalization, markup left inside a title. The canonical layer smooths that out for searching, while the original stays untouched as the source of truth.

This follows the same rule as protecting source data anywhere in a pipeline: normalize a copy, never overwrite the original. The canonical fields exist purely to make comparison and search fast and consistent.

14. Exact Identifier Matching

<span class="c"># Pipeline order: cheapest, strongest evidence FIRST</span>
<span class="k">if</span> input_has_doi(reference):
    <span class="k">return</span> exact_lookup_by_doi(reference.doi)     <span class="c"># done — near-certain</span>
<span class="k">elif</span> input_has_isbn(reference):
    <span class="k">return</span> exact_lookup_by_isbn(reference.isbn)
<span class="k">else</span>:
    <span class="k">return</span> fuzzy_search_pipeline(reference)         <span class="c"># fall back to sections 15-17</span>

15. Candidate Retrieval & Recall-First Thinking

This stage deliberately favors recall (don’t miss the answer) over precision (don’t worry yet about perfect ordering) — the same recall concept from classic search evaluation. Typical retrieval methods combined here: keyword search (BM25), normalized-title matching, prefix matching, trigram/fuzzy matching, and rough author+year filtering.

16. Field-Level Matching Evidence

For each input-reference and candidate pair, compute separate signals:

This turns “trust me, it’s 94% confident” into an inspectable, explainable decision — which matters enormously when the answer feeds into something a person will rely on, like a corrected bibliography.

17. Reranking

candidates = [<span class="k">"Attention Is All You Need"</span>, <span class="k">"Attention Networks for..."</span>, <span class="k">"..."</span>]  <span class="c"># ~100 items</span>

<span class="c"># A cheap score got them this far. Now a stronger model re-scores just these few:</span>
<span class="k">for</span> candidate <span class="k">in</span> candidates:
    score = expensive_reranker(input_reference, candidate)   <span class="c"># title+author+year model, or a small LLM</span>

ranked = sort_by_score(candidates)   <span class="c"># the top of this list is the proposed answer</span>

Reranking approaches range from a simple weighted combination of the field-level evidence scores, up through learned classifiers, to a constrained model that compares candidates without being allowed to invent new bibliographic facts.

18. Calibration

A raw similarity score is not automatically a trustworthy probability. Calibration is the process of testing and adjusting that mapping using data where you already know the right answer:

19. Abstention

Compare two outcomes for a vague input like "Smith AI systems 2019":

Abstention thresholds are usually tiered, so the system’s honesty scales with its actual certainty:

98–100%  <span class="c">→ near-certain</span>
90–97%   <span class="c">→ strong match</span>
70–89%   <span class="c">→ probable, but flagged for review</span>
50–69%   <span class="c">→ uncertain</span>
< 50%    <span class="c">→ abstain: "no reliable match found"</span>

For anything a person will rely on — citations, medical records, legal documents — a confidently wrong answer is far more damaging than an honest “I’m not sure.” (These exact thresholds should always come from measuring calibration on real data, not from guessing.)

20. Batch Processing Pipeline

Put every term from Part 2 together, and this is the shape of the whole matching product:

The output a user downloads typically looks like a spreadsheet: one row per input reference, the best verified match, its confidence, its status (strong / review / unresolved), and a link back to the authoritative source record.

Part 3 · The Engineering Discipline Around the Code

Storing data safely and matching it intelligently are the heart of the system. But no serious project runs on code and a database alone — a handful of everyday engineering terms hold the whole thing together and prove, to you and to anyone else, that it actually works. Here are the rest.

21. Record

“Row” is a database word; “record” is what that row means. One record might be one book, one customer, one sensor reading — in our running example, one bibliographic reference:

22. Streaming Parser

<span class="c"># ❌ Loads the ENTIRE file into memory before doing anything</span>
tree = parse_entire_file(<span class="k">"huge_dataset.xml"</span>)

<span class="c"># ✅ Streaming: reads, converts, and releases ONE record at a time</span>
<span class="k">for</span> record <span class="k">in</span> stream_records(<span class="k">"huge_dataset.xml"</span>):
    save(record)
    <span class="c"># memory for this record is freed before the next one is read</span>

If a source file is several gigabytes — larger than comfortably fits in RAM alongside everything else your program needs — a streaming parser is what makes processing it on a normal machine possible at all, with memory usage staying flat no matter how big the file grows.

23. Reconciliation

“It looks about right” is not proof. A reconciliation check is:

<span class="k">assert</span> records_stored + records_skipped == records_read, \
    f<span class="k">"LEAK! {records_read - records_stored - records_skipped} records unaccounted for"</span>

If even one record is unaccounted for, you stop and investigate before trusting anything built on top of that data.

24. Provenance

Provenance lets you answer questions like: Which source produced this title? Was it copied exactly, or cleaned up? Can I show the original? Combined with canonical records and field-level evidence from Part 2, it’s what makes a matching system’s output auditable rather than a black box.

25. BM25 & Embedding

<span class="c"># A minimal BM25-style intuition: score = sum of rarity-weighted shared words</span>
query = <span class="k">"attention vaswani"</span>
<span class="c"># "attention" appears in 12,000 records → moderate weight</span>
<span class="c"># "vaswani"   appears in 45 records     → high weight (rare = strong evidence)</span>

In a real matching pipeline, both feed into candidate retrieval from Part 2 — BM25 catching exact wording, embeddings catching paraphrases and typo-heavy input that shares almost no literal words with the true title.

26. The Two Meanings of “Commit”

<span class="c">-- Database commit: saves DATA changes</span>
<span class="k">BEGIN</span>;
  <span class="k">INSERT INTO</span> records VALUES (...);
<span class="k">COMMIT</span>;

# Git commit: saves a CODE snapshot
git add ingestion_runner.py
git commit -m "Add checkpointed DBLP ingestion runner"

27. Git, GitHub & Push

git add ingestion_runner.py           <span class="c"># stage the changed file</span>
git commit -m <span class="k">"Add checkpointed DBLP ingestion runner"</span>   <span class="c"># snapshot it locally</span>
git push origin main                  <span class="c"># send that snapshot to GitHub</span>

Until you push, your commits exist only on your own machine — a hard-drive failure would lose them. GitHub is what turns “recorded” into “durably backed up and shareable.”

28. pytest, Ruff & mypy

<span class="c"># pytest: does the behavior work?</span>
<span class="k">def</span> test_all_records_stored():
    <span class="k">assert</span> stored_count == 100    <span class="c"># fails loudly if the real count is 99</span>

<span class="c"># Ruff: style / suspicious patterns (run from the terminal)</span>
$ ruff check .

<span class="c"># mypy: do the types add up? (run from the terminal)</span>
$ mypy src/
<span class="c"># error: Function is declared to return 'int', but returns 'int | None'</span>

These three catch genuinely different bug categories: pytest proves behavior against real expectations, Ruff flags sloppy or risky patterns before they cause trouble, and mypy catches internal inconsistencies that tests might never happen to exercise. A project reporting “all tests pass” alongside “Ruff and mypy also pass” is reporting three separate, complementary kinds of confidence.

Real-World Numbers: How Big Are These Bibliography Databases?

All the ideas above become more concrete once you see them applied to a real dataset. DBLP is a well-known, freely available computer-science bibliography — a good example of exactly the kind of dataset this article’s techniques are built for.

DBLP’s official guidance is a textbook case for the streaming concept: the dataset is too large to load fully into memory, so their own documentation recommends event-based streaming parsers rather than loading the whole tree at once — exactly the “process one record at a time” idea this article’s companion piece covers.

DBLP is far from the largest bibliography source in existence. Here’s how a few major ones compare in scale (figures change constantly — treat these as an “as of” snapshot):

Quick-Reference Glossary

Putting It All Together

These twenty-eight terms split into three acts. Act one keeps your data safe no matter what fails: transactions bundle writes atomically, journal modes and WAL decide how recovery works, the synchronous setting is the one honest knob that trades speed for surviving a real power loss, and checkpoints — at both the SQLite level and your own pipeline’s level — mark exactly how far you can trust your progress. Act two turns that safely-stored data into something genuinely useful: exact identifiers first, a wide candidate net second, explainable field-by-field evidence third, a careful reranking pass, an honestly calibrated confidence score, and — when the evidence just isn’t there — the maturity to abstain rather than guess. Act three is the discipline around all of it: proving your counts reconcile, keeping a provenance trail, knowing your two “commits” apart, and using Git, GitHub, and a testing toolchain to make sure the whole system is reproducible by someone who wasn’t in the room when you built it.

Master these terms and you can read the architecture of almost any serious data pipeline or record-matching system — including the kind that turns a messy pile of citations into a verified, trustworthy bibliography.