Ingesting Documents at Scale: Formats and How Each One Fails

Ingestion looks like a dispatch table: switch on the extension, call the right parser. It stops looking like that the first time a parser returns an empty string instead of raising, and a thousand documents enter your index with no content and no error.

The extension is not the format

Before any parsing, decide what the bytes are. Filenames come from humans and from export scripts, and both lie routinely: .doc files that are RTF or even HTML (Word has happily saved both under that extension for twenty years), .csv files that are actually the HTML of a login page because the export was fetched without a session, .xls files that are tab-separated text.

Magic bytes settle it in most cases. %PDF- opens a PDF. PK\x03\x04 opens any ZIP, which includes every OOXML file — a .docx is a ZIP whose member list contains word/document.xml, an .xlsx has xl/workbook.xml, so the member list is what distinguishes them. \xd0\xcf\x11\xe0 is the old OLE2 compound file used by .doc and .xls. Python’s filetype or the libmagic binding do this for you; the important thing is that the answer, not the extension, is what selects the parser, and that the answer is stored.

Encoding is the second half of “what are these bytes”, and it has to be settled at the same moment, because that is the only point where all the evidence is in one place. The evidence has a precedence order: a byte-order mark if present, then the charset in the HTTP Content-Type header, then a <meta charset> declaration inside the document, then statistical detection with charset-normalizer or chardet. One trap is written into the HTML specification itself: a document labelled ISO-8859-1 must be decoded as windows-1252, because that is what browsers do and therefore what authors wrote against. The two differ exactly in the range 0x80–0x9F, which is where curly quotes, dashes and the euro sign live — so getting it wrong corrupts precisely the punctuation that appears in every other sentence of real prose.

Whatever you decide, record it. A stored encoding and encoding_source pair turns a later mojibake report into a two-minute query, and leaves you able to re-decode the affected subset from the raw bytes instead of re-fetching from a source that may have moved on.

One failure mode per format

Each format has a characteristic way of defeating naive ingestion. Not a bug in the parser — a consequence of what the format is.

The spreadsheet epoch trap is worth its own sentence because it costs people a full day: Excel on Windows counts days from 1899-12-30 while the legacy Mac workbooks count from 1904-01-01, a difference of 1,462 days. A date column read as a number and converted with the wrong epoch is off by exactly four years and a day, which is wrong in a way that still looks like a date.

The assertions that catch them

Every one of the modes above can be detected cheaply at the moment of extraction. These four assertions belong in the extract stage, not in a dashboard somebody checks later:

import re, unicodedata

CID = re.compile(r"\(cid:\d+\)")

def extraction_problems(text: str, raw_bytes: int) -> list[str]:
    bad = []
    stripped = text.strip()

    # 1. Empty or near-empty output from a file that clearly had content.
    if len(stripped) < 50 and raw_bytes > 20_000:
        bad.append("empty_extraction")

    # 2. Glyph ids leaked through: a font with no ToUnicode CMap.
    if CID.search(text):
        bad.append("cid_leak")

    # 3. Mojibake: UTF-8 bytes that were decoded as cp1252.
    if "\u00e2\u20ac" in text or "\u00c3\u00a9" in text:
        bad.append("mojibake")

    # 4. Not prose: a page of glyph soup has almost no spaces, and a
    #    table dump has almost nothing else.
    letters = sum(c.isalpha() for c in stripped)
    if stripped and letters / len(stripped) < 0.5:
        bad.append("low_letter_ratio")

    # 5. Replacement characters mean a decode already gave up.
    if "\ufffd" in text:
        bad.append("replacement_chars")

    return bad

The empty_extraction check is the one that earns its keep. A PDF of scanned pages is a large file that parses without error and yields nothing, and without this check it becomes a row in your index that can never be retrieved and never be explained.

A triage script for your own corpus

Failure rates per format are a property of your corpus, not a published constant — a corpus of born-digital reports and a corpus of scanned 1990s contracts have nothing in common. Run this over a sample of a few thousand files before you commit to a design; the shape of the output tells you where the engineering budget goes.

import collections, pathlib, random

def triage(root: str, sample: int = 2000):
    files = list(pathlib.Path(root).rglob("*"))
    files = [f for f in files if f.is_file()]
    random.seed(0)                      # same sample every run
    counts = collections.Counter()
    for f in random.sample(files, min(sample, len(files))):
        kind = sniff(f)                 # magic bytes, not suffix
        try:
            text = extract(f, kind)
        except Exception as e:
            counts[(kind, type(e).__name__)] += 1
            continue
        problems = extraction_problems(text, f.stat().st_size)
        counts[(kind, problems[0] if problems else "ok")] += 1

    for (kind, outcome), n in counts.most_common():
        print(f"{kind:12} {outcome:20} {n:6}")

Seed the sample so the run is repeatable, and keep the output: it is the baseline you compare against when you swap a parser. If pdf empty_extraction is a large row, the work is an OCR path. If html ok dominates but the text is full of navigation, the work is boilerplate removal.

What to do with the failures

Not drop them. A document that fails extraction should land in a quarantine table with its raw hash, the detected format, the problem codes and the parser version — so that when you add an OCR fallback six weeks later, the set of documents to re-run is a query rather than a full rebuild.

Give the pipeline three terminal states rather than two: indexed, quarantined and skipped, where skipped is a deliberate rule (binaries, images with no text, files over a size limit) and quarantined means the system does not yet know how to read this. The count of quarantined documents is the honest coverage number for the feature, and it is the one to put in front of whoever asked why the assistant does not know about a particular contract.

Give quarantine a shape that makes it actionable rather than a bin. Store the problem code, the parser and version that produced it, the detected format and the raw hash, and nothing else — the bytes are already addressable by that hash. Adding an OCR fallback then becomes SELECT raw_sha256 FROM quarantine WHERE problem = 'empty_extraction', and the re-run touches only the documents that need it rather than the corpus.

And make the quarantine count something somebody sees weekly, because it only ever grows quietly. A new source arrives in a format nobody planned for, its documents fail, and the feature develops a blind spot that no error rate reports — because nothing errored. A count on a dashboard is the cheapest available detector for “the corpus has started missing something”, and it is one query.