A data migration is not merely “reading a file and inserting rows”

Beginners often imagine a migration as one short script: open an XML or CSV file, loop over the records, run an INSERT statement, and wait until the script finishes. That may work for a tiny disposable file. It is not enough for a large, valuable, or difficult-to-replace dataset.

A serious migration is a chain of evidence. You need to prove that the source was correct, the target was safe, the mapping was intentional, the loader did not silently lose information, interruptions were recoverable, and the final database agrees with the original source.

What data is worth converting to SQLite?

SQLite is a serverless relational database stored locally, usually in one main database file. It is especially useful when the data will be filtered, joined, indexed, searched repeatedly, distributed with an application, or analysed offline without maintaining a database server.

SQLite, flat files, or a server database?

Why convert XML, JSON or CSV to SQL at all?

Must every dataset go through all six stages?

Conceptually, yes: every migration should answer the six questions below. However, the depth of each answer should scale with the dataset’s size, value, replaceability and operational risk.

The minimum directory model

Copyproject/
├── src/                     # Loader and application code
├── tests/                   # Automated tests
├── data/
│   └── raw/                 # Original source files; never modified
└── docs/                    # Design and run documentation

external-data/
├── databases/               # Large SQLite files
├── logs/                    # Human-readable progress and errors
├── manifests/               # Machine-readable run metadata
├── samples/                 # Retained validation databases
└── crash-snapshots/         # Evidence from interrupted runs

Large generated data should normally remain outside the Git repository. Version control should contain code, schemas, tests, documentation and small fixtures—not multi-gigabyte databases or raw dumps.

What should be recorded before migration?

Simple example

Suppose you download an 8 GB XML file containing books. A weak setup uses books.xml and test.db on the desktop. A strong setup records:

Four levels of source verification

Example checksum commands

Copy# macOS
shasum -a 256 large_dataset.xml

# Linux
sha256sum large_dataset.xml

# Python
python -c "import hashlib, pathlib; p=pathlib.Path('large_dataset.xml'); print(hashlib.sha256(p.read_bytes()).hexdigest())"

Copyfrom hashlib import sha256
from pathlib import Path

def file_sha256(path: Path, chunk_size: int = 8 * 1024 * 1024) -> str:
    digest = sha256()
    with path.open("rb") as source:
        while chunk := source.read(chunk_size):
            digest.update(chunk)
    return digest.hexdigest()

Why structural validation is not enough

An XML document may be perfectly valid according to its DTD while still containing unexpected duplicate identifiers. A CSV may have the correct columns while the final 20% of the download is missing. This is why source validation should combine structural checks with a content census.

Format-specific checks

Destination preflight checklist

How much free space should you reserve?

There is no universal multiplier because storage depends on text length, normalization, indexes, page size, temporary operations and journaling mode. Estimate with a sample benchmark rather than guessing.

Safer deployment pattern

1. Build the new database under a versioned or temporary production path.
2. Complete all integrity and reconciliation checks.
3. Close every connection and settle required journal or WAL state.
4. Publish or rename the verified file atomically where the operating system permits.
5. Retain the previous production version until the new one is accepted.

4.1 Design the data model before writing the parser

The first question is not “How do I read XML?” It is “What information must remain recoverable after conversion?” If the relational model discards fields, attributes, ordering or identifiers that later become important, the import may be fast but incorrect.

A simple XML example

Copy<book id="b-101" language="en">
  <title>Data Pipelines for Everyone</title>
  <author order="1">Ava Chen</author>
  <author order="2">Noah Stein</author>
  <year>2026</year>
  <keyword>SQLite</keyword>
  <keyword>ETL</keyword>
</book>

A fragile relational design

CopyCREATE TABLE books (
    id TEXT PRIMARY KEY,
    title TEXT,
    author TEXT,     -- only one author fits
    year INTEGER
);

This design silently loses the second author, the author order, language and keywords. A more faithful model separates repeatable fields.

CopyCREATE TABLE books (
    id TEXT PRIMARY KEY,
    language TEXT,
    title TEXT NOT NULL,
    year INTEGER,
    source_order INTEGER NOT NULL UNIQUE
);

CREATE TABLE book_authors (
    book_id TEXT NOT NULL REFERENCES books(id),
    author_order INTEGER NOT NULL,
    author_name TEXT NOT NULL,
    PRIMARY KEY (book_id, author_order)
);

CREATE TABLE book_keywords (
    book_id TEXT NOT NULL REFERENCES books(id),
    keyword_order INTEGER NOT NULL,
    keyword TEXT NOT NULL,
    PRIMARY KEY (book_id, keyword_order)
);

4.2 Use bounded-memory parsing

A large XML document should normally be parsed incrementally. In Python, xml.etree.ElementTree.iterparse() can emit events as elements are read. After processing a complete record, clear the element so old subtrees do not remain in memory.

Copyimport xml.etree.ElementTree as ET

for event, element in ET.iterparse("library.xml", events=("end",)):
    if element.tag == "book":
        record = parse_book(element)
        write_book(connection, record)
        element.clear()  # release processed content

4.3 Separate parsing, modelling and writing

4.4 Use transactions deliberately

Committing after every row forces excessive durability work. Keeping the entire multi-hour migration in one transaction makes restart and rollback impractical. Group records into measured transaction batches.

4.5 Add source order and checkpoints

A checkpoint is more than a line in a log. It should be transactionally consistent with committed data. If the checkpoint says record 100,000 is complete, the database must contain exactly the committed prefix through 100,000.

CopyBEGIN;

-- insert a group of source records
INSERT INTO books (...);
INSERT INTO book_authors (...);

-- update checkpoint in the same transaction
UPDATE ingestion_batches
SET committed_source_order = 100000,
    committed_records = 100000
WHERE batch_id = 1;

COMMIT;

4.6 Design resume and abandoned-run recovery

Resume logic must not blindly trust a checkpoint. Before continuing, compare:

• the batch status;
• the maximum stored source order;
• the committed-record count;
• the checkpoint;
• the source identity and loader configuration.

4.7 Benchmark the real path

A useful benchmark uses the real parser, schema, writer and transaction runner. It should report more than elapsed time.

4.8 Indexes: before or after the bulk load?

Indexes speed queries but make inserts more expensive because every inserted row may update several index trees. For a fresh bulk import, it is often efficient to create essential constraints first, load the source, verify it, then build large secondary search indexes afterward. The correct choice depends on whether an index is required for uniqueness or ingestion logic.

A sample is not merely “the first 100 rows”

The easiest records may hide the exact edge cases that break the production run. A good sample should include representative variation.

What must the retained sample prove?

1. The production launcher validates paths, source identity, free space and destination safety.
2. The real schema is created with the intended SQLite settings.
3. The parser and writer ingest exactly the allowed sample size.
4. The checkpoint equals the final committed sample record.
5. Failed-record and ingestion-error counts are zero, or every accepted exception is documented.
6. Integrity and foreign-key checks pass.
7. The database, log and manifest are retained for independent inspection.

Example sample manifest

Copy{
  "run_id": "20260722T090000Z",
  "source_sha256": "9f...example",
  "source_bytes": 8589934592,
  "loader_git_commit": "abc123",
  "sqlite_profile": "delete-full",
  "commit_interval": 1000,
  "expected_records": 1000,
  "inserted_records": 1000,
  "failed_records": 0,
  "final_checkpoint": 1000,
  "integrity_check": "ok",
  "foreign_key_violations": 0,
  "status": "completed"
}

Why retain the sample database?

A deleted temporary sample proves very little later. A retained sample allows another developer to inspect actual tables, run verification queries, compare future schema versions and understand the storage footprint.

Before pressing “run”

Progress monitoring should answer useful questions

Independent reconciliation after completion

Do not rely only on “status: completed” written by the loader. Open the database separately in read-only mode and compare its state with the source and manifest.

Copy-- Core record count
SELECT COUNT(*) FROM source_records;

-- Source-order range
SELECT MIN(source_order), MAX(source_order) FROM source_records;

-- Batch status and checkpoint
SELECT status, expected_records, committed_records, failed_records,
       committed_source_order
FROM ingestion_batches;

-- Database structural checks
PRAGMA integrity_check;
PRAGMA foreign_key_check;

Final acceptance matrix

What should be preserved after success?

• the production SQLite database;
• the exact source fingerprints and release identity;
• the loader Git commit and runtime versions;
• the final manifest;
• the complete progress and console logs;
• the selected SQLite settings and transaction interval;
• the independent verification output;
• any crash snapshot or failed-attempt evidence that explains operational history.

What happens when you add another open-source library or dataset?

The new source should normally go through the same six-stage framework, but you should reuse the migration infrastructure rather than duplicate it. Think of the framework as a factory and each source as a new adapter entering that factory.

Which work is reusable and which must be repeated?

Three common integration strategies

A practical decision tree for any XML, JSON or CSV migration

Example decisions

Common migration mistakes and why they are dangerous

Complete large-data-to-SQLite migration checklist

The checkboxes work in the browser and are intentionally not saved after closing the page.

Stage 1 — Environment

Stage 2 — Source verification

Stage 3 — Destination safety

Stage 4 — Loader engineering

Stage 5 — Retained sample

Stage 6 — Full migration

Questions beginners and experienced developers both ask

Primary technical references

This article presents a generalized engineering framework. The following official documentation is useful when implementing the details: