Gowtham Potureddi

Posted on Aug 29

Unstructured & Document Pipelines: PDFs, OCR & Text Extraction for the Warehouse

#python #sql #interview #dataengineering

Unstructured data pipelines are the part of the platform that has to turn a folder of opaque bytes — a scanned invoice, a 90-page contract, a quarterly report exported to PDF, a customer email with a spreadsheet attached — into governed, queryable rows the warehouse can trust, and later into embeddings a retrieval layer can search. The hard problem was never storing the file; object storage does that for pennies. The hard problem is that a document arrives with no schema: no columns, no keys, variable layout, sometimes not even a text layer, and no promise that the next file in the folder looks anything like this one. Tabular ingestion knows what a row is before it starts; document ingestion has to discover what a row is, one file at a time, and prove where every character came from.

This guide is the senior-data-engineering walkthrough for building that ingestion path — framed the way interviewers actually probe it: why document ingestion is a different problem from a CSV load, how OCR enters the picture the moment a page has no text layer and how you detect that before you waste money OCR-ing everything, how text extraction and document parsing with tools like Unstructured and Apache Tika turn a file into typed elements and tables instead of a wall of text, how chunking and normalization prepare that text for embeddings without shipping duplicates, and how the whole thing lands in the warehouse with the provenance and idempotency a production ingestion pipeline needs. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the parsing practice library →, rehearse the cleaning and transformation work on the data processing practice library →, and sharpen the architecture axis with the system design practice library →.

On this page

Why unstructured and document pipelines are different
PDF and OCR — text-layer, scanned, and layout
Text extraction and parsing — Unstructured and Apache Tika
Chunking and normalization — strategies, cleaning, dedup
Landing to the warehouse and embeddings for RAG
Cheat sheet — unstructured document pipelines
Frequently asked questions
Practice on PipeCode

1. Why unstructured and document pipelines are different

The schema gap — tabular ingestion knows its columns; a document is opaque bytes you must interpret

The one-sentence invariant: an unstructured data pipeline is an ingestion path whose first job is to discover structure that was never declared — detecting the file's format, extracting its text and layout with the right fidelity, normalizing and deduplicating it, and attaching provenance — because unlike a CSV whose schema is fixed before the load starts, a document arrives as opaque bytes with variable layout, possibly no text layer at all, and no keys, so the pipeline has to manufacture rows, lineage, and a stable identity rather than merely copy them. Point a tabular loader at a PDF and it sees one blob column; the entire discipline is turning that blob into rows you can query and cite.

The four axes interviewers actually probe.

Format detection. Is this born-digital (a real text layer) or scanned (an image needing OCR)? Office (DOCX/PPTX), HTML, email, or something exotic? The senior answer detects the format first and routes to the cheapest extractor that works, because the wrong path is either lossy (OCR-ing text that was already there) or empty (text-extracting a scan and getting nothing).
Extraction fidelity. Do you need just the running text, or the layout — reading order, columns, headers/footers — and the tables as structured rows? A contract's clauses and an invoice's line-item table demand different fidelity. The senior answer names what fidelity the downstream use needs and picks a parser accordingly.
Normalization and provenance. How do you clean the text deterministically and, crucially, keep lineage — which document, which page, which character offset each chunk came from? Without provenance a retrieval answer cannot be cited or audited. The senior answer treats provenance as a first-class output, not a nice-to-have.
Downstream shape. What does the consumer need — warehouse rows for analytics, or chunks and embeddings for RAG? The shape decides your chunking strategy and your landing model. The senior answer designs backward from the consumer.

The 2026 reality — the document stack is a small set of well-worn tools.

Born-digital extraction is cheap and exact: pdfplumber or PyMuPDF pull the embedded text layer and per-word coordinates without any model. Reach for OCR only when there is no text layer.
OCR — Tesseract locally, or a cloud OCR/Document AI service — converts a page image into text, with preprocessing (deskew, binarize, DPI) and a per-word confidence you should keep.
Parsing/partitioning — the Unstructured library and Apache Tika turn heterogeneous formats into typed elements (title, narrative, list, table) with metadata, so you keep meaning instead of flattening everything to a string.
Chunking + embeddings — deterministic chunkers (recursive, structure-aware) with overlap feed an embedding model; the vectors land in a store like pgvector for retrieval. The warehouse holds the curated chunks and the provenance that lets a RAG answer cite its source.

What interviewers listen for.

Do you say detect the format before extracting and route born-digital away from OCR? — senior signal.
Do you treat provenance (doc/page/offset) as a required output for citations and audit, not an afterthought? — required answer.
Do you make reprocessing idempotent with a content hash so a re-run does not duplicate rows or re-embed unchanged text? — senior signal.
Do you pick chunking and fidelity from the downstream consumer (analytics rows vs RAG chunks)? — required answer.
Do you name the pipeline's output as a governed dataset with lineage, not "a table of strings"? — senior signal.

Worked example — the format-to-extraction decision table

Detailed explanation. The single most useful artifact for a document-pipeline interview is a memorised mapping of input format → extraction strategy. Every senior discussion converges on it: given a file, do you read a text layer, OCR an image, or use an office/HTML parser — and what does that cost? Walk through building the table for a mixed intake of business documents.

The inputs. A born-digital PDF report, a scanned PDF contract, an image-only invoice, a DOCX policy, and an HTML export.
The tension. OCR is slow and lossy but the only option for scans; text-layer extraction is fast and exact but returns nothing on an image.
The rule. Detect the format (and, for PDFs, whether a text layer exists) first, then route to the cheapest extractor that preserves the fidelity you need.

Question. For each input, name the extraction strategy and the tool, and say what it costs relative to the others.

Input.

Input	Has text layer?	Strategy	Tool
Born-digital PDF	yes	read text layer	pdfplumber / PyMuPDF
Scanned PDF	no (image pages)	OCR each page	Tesseract / cloud OCR
Image invoice (PNG/TIFF)	no	OCR	Tesseract / Document AI
DOCX / PPTX	n/a (structured)	office parser	Unstructured / Tika
HTML / email	n/a (markup)	markup parser	Unstructured / Tika

Code.

# Route each file to the cheapest extractor that works.
# The key move: DETECT before you extract — never OCR a born-digital page.
import fitz  # PyMuPDF

def classify_and_route(path: str) -> str:
    ext = path.lower().rsplit(".", 1)[-1]
    if ext in {"docx", "pptx", "html", "htm", "eml", "msg"}:
        return "office_or_markup_parser"        # Unstructured / Tika
    if ext in {"png", "jpg", "jpeg", "tif", "tiff"}:
        return "ocr"                             # pure image -> OCR
    if ext == "pdf":
        doc = fitz.open(path)
        # A PDF is "born-digital" if its pages actually carry extractable text.
        chars = sum(len(page.get_text("text").strip()) for page in doc)
        pages = doc.page_count or 1
        return "text_layer" if (chars / pages) > 40 else "ocr"
    return "unknown"

Step-by-step explanation.

Extension alone routes the structured formats: DOCX, PPTX, HTML, and email are markup/office containers, so an office/markup parser (Unstructured or Tika) is correct and OCR would be absurd.
Pure image formats (PNG/TIFF/JPEG) have no text at all, so they go straight to OCR — there is nothing else to try.
A PDF is ambiguous: the same extension covers a crisp born-digital export and a photocopied scan. The router opens it and measures how much extractable text exists per page.
The chars / pages > 40 heuristic is the crux: a born-digital page returns hundreds of characters of real text, while a scanned page returns ~zero (only stray artifacts). Above the threshold, read the cheap text layer; below it, the page is effectively an image and must be OCR-ed.
The mistake this prevents is a single global strategy: OCR-ing everything wastes minutes-per-document and loses accuracy on text that was already perfect, while text-extracting everything silently returns empty strings for every scan. Detection is what makes the pipeline both cheap and correct.

Output.

Input	Routed to	Relative cost
Born-digital PDF	text_layer	cheapest (no model)
Scanned PDF	ocr	expensive (per-page OCR)
Image invoice	ocr	expensive
DOCX / HTML	office_or_markup_parser	cheap
unknown ext	unknown → quarantine	—

Rule of thumb. Detect the format first, and for PDFs measure the text layer before choosing a path: read the cheap text layer when it exists, OCR only when it does not. A single global strategy is either lossy or empty — routing is what makes a document pipeline both correct and affordable.

Worked example — what interviewers actually probe

Detailed explanation. The senior document-pipeline interview has a predictable escalation: an ambiguous opener ("ingest these documents into the warehouse"), then progressive narrowing to test whether you understand format detection, provenance, and idempotency. The candidates who name detect-before-extract, provenance, and content-hash idempotency score highest.

Ambiguous opener. "We have an S3 bucket of PDFs. Load them into the warehouse."
Follow-up 1. "Half of them are scans and return no text. Now what?" — probes OCR routing.
Follow-up 2. "The RAG answer needs to cite page 7 of a specific contract. How?" — probes provenance.
Follow-up 3. "We re-run the loader nightly. Why did the row count double?" — probes idempotency.
Follow-up 4. "Re-embedding everything each night costs a fortune. Fix it." — probes change detection.

Question. Draft a 5-minute senior document-ingestion answer that pre-empts all four follow-ups without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Ingest	"read text from each PDF"	"detect format/text-layer, route per file"
Scans	"they just come out empty"	"OCR the pages with no text layer"
Citations	"store the whole document text"	"keep doc_id + page + char offset per chunk"
Re-runs	"truncate and reload"	"idempotent upsert keyed on content hash"
Re-embed cost	"embed everything again"	"only embed chunks whose hash changed"

Code.

Senior document-ingestion answer template (5 minutes)
=====================================================

Minute 1 — name the schema gap up front
  "These are unstructured — no schema until I extract one. Step one is
   DETECT the format and, for PDFs, whether a text layer exists, then
   route: text-layer read for born-digital, OCR for scans, an office
   parser for DOCX/HTML."

Minute 2 — extraction fidelity
  "I partition into typed elements (title/narrative/table) so tables stay
   structured rows, not flattened text, and I keep coordinates/reading
   order so the layout survives."

Minute 3 — provenance is a first-class output
  "Every chunk carries doc_id, page_number, and char offsets. That's what
   lets a RAG answer cite 'page 7 of contract X' and lets audit trace a
   value back to its source line."

Minute 4 — idempotency
  "I key rows on a content hash of the source and the chunk, and upsert.
   A nightly re-run of unchanged files is a no-op — no duplicate rows."

Minute 5 — cost of embeddings
  "I only embed chunks whose hash is new or changed, so re-embedding is
   proportional to what actually changed, not the whole corpus. Curated
   chunks + vectors + provenance land in the warehouse as one dataset."

Step-by-step explanation.

Minute 1 frames the whole answer around the schema gap. Weak candidates start extracting; naming "detect the format first" signals you understand that structure is discovered, not given.
Minute 2 shows you preserve fidelity — typed elements and tables-as-rows — rather than collapsing a document into a lossy string, which is the difference between a searchable dataset and mush.
Minute 3 pre-empts the citation follow-up. Volunteering doc/page/offset provenance before the interviewer asks proves you have shipped a pipeline whose outputs are auditable and citable.
Minute 4 pre-empts the re-run follow-up. A content-hash-keyed upsert is the single most senior thing you can say about a batch document loader — it makes the pipeline idempotent and safe to re-run.
Minute 5 closes on the cost axis: embedding is the expensive step, so tying it to a change hash makes re-processing proportional to change. That is the sentence that separates a platform engineer from someone who reloads the whole corpus nightly.

Output.

Grading criterion	Weak score	Senior score
Detect format before extract	rare	mandatory
Fidelity (typed elements, tables)	occasional	senior signal
Provenance for citations	rare	mandatory
Idempotent, content-hash keyed	rare	senior signal
Change-scoped re-embedding	rare	senior signal

Rule of thumb. The senior document-ingestion answer is a 5-minute monologue covering the schema gap, detect-before-extract, fidelity, provenance, idempotency, and change-scoped embedding — without waiting for the follow-ups. Rehearse it once; deploy it every interview.

Worked example — unstructured vs tabular ingestion, side by side

Detailed explanation. A common trap is to model a document pipeline exactly like a CSV load. The two differ at every stage, and naming those differences is what shows you understand the problem. Contrast the two pipelines stage by stage on the same "load a folder into the warehouse" task.

Tabular. Known schema, known keys, deterministic parse, one row per record.
Unstructured. Discovered structure, manufactured keys, model-dependent extraction, many chunks per file with lineage.
The lesson. Everything a CSV load takes for granted — schema, identity, determinism — a document pipeline must produce.

Question. For each pipeline stage, state what tabular ingestion assumes and what a document pipeline must instead do.

Input.

Stage	Tabular ingestion	Document pipeline
Schema	declared up front	discovered per file
Identity	natural/primary key	manufactured (content hash)
Parse	deterministic (delimiter)	model-dependent (OCR/parse)
Unit	one row per record	many chunks per document
Lineage	source + row number	doc_id + page + char offset

Code.

# Same task, two shapes. Tabular: a row IS a record.
def load_csv_row(row: dict) -> dict:
    return {"id": row["order_id"], **row}     # key already exists

# Document: one file becomes MANY provenance-carrying chunks with a made-up id.
import hashlib

def make_chunk_rows(doc_id: str, page: int, chunks: list[tuple[int, str]]) -> list[dict]:
    rows = []
    for start_offset, text in chunks:
        h = hashlib.sha256(f"{doc_id}:{page}:{start_offset}:{text}".encode()).hexdigest()
        rows.append({
            "chunk_id": h,               # manufactured, stable identity
            "doc_id": doc_id,            # provenance: which document
            "page_number": page,         # provenance: which page
            "char_offset": start_offset, # provenance: where on the page
            "text": text,
        })
    return rows

Step-by-step explanation.

The tabular loader is trivial because the record already has a key (order_id) and a schema — the row is the unit of work, and the parse is a deterministic delimiter split.
The document loader has no natural key, so it manufactures one: a SHA-256 over doc_id + page + offset + text gives every chunk a stable, content-derived identity that is also the idempotency key.
One file explodes into many chunks, so the unit of work is a chunk, not a file — and each chunk must carry the lineage (doc_id, page_number, char_offset) a CSV row never needs because a CSV row already knows where it came from.
The parse step hidden behind chunks is model-dependent (OCR or a layout parser), so unlike a delimiter split it can be imperfect, versioned, and worth re-running when the extractor improves — another reason identity must be content-derived, not positional.
The takeaway: a document pipeline produces the schema, identity, and lineage that a tabular pipeline is handed for free — which is exactly why it needs detection, provenance, and hashing as first-class stages.

Output.

Property	Tabular	Document pipeline
Rows per input file	~ line count	many chunks per doc
Key source	given	manufactured (hash)
Parse determinism	exact	model-dependent
Carries lineage?	trivially	must be added
Re-run safety	overwrite by key	upsert by content hash

Rule of thumb. Treat a document pipeline as the inverse of a CSV load: schema, identity, and lineage are outputs you manufacture, not inputs you are given. Every design decision — detection, provenance, hashing — exists to produce what tabular ingestion takes for granted.

Senior interview question on document-ingestion strategy

A senior interviewer often opens with: "We have a bucket of mixed documents — born-digital PDFs, scanned contracts, image invoices, and some DOCX files — and we want their content queryable in the warehouse and searchable via RAG. Design the ingestion pipeline: how you detect format and route extraction, where OCR fits and how you avoid running it needlessly, how you keep provenance so answers can be cited, and how you make nightly re-runs idempotent without re-embedding the whole corpus."

Solution Using format detection, routed extraction, provenance, and content-hash idempotency

# 1. Detect + route: born-digital -> text layer, scans -> OCR, office -> parser.
import fitz, hashlib

def route(path: str) -> str:
    ext = path.lower().rsplit(".", 1)[-1]
    if ext in {"docx", "pptx", "html", "eml"}:      return "parse"
    if ext in {"png", "jpg", "jpeg", "tif", "tiff"}: return "ocr"
    if ext == "pdf":
        doc = fitz.open(path)
        chars = sum(len(p.get_text("text").strip()) for p in doc)
        return "text_layer" if chars / (doc.page_count or 1) > 40 else "ocr"
    return "quarantine"

# 2. Manufacture a stable identity from CONTENT, not the filename or load time.
def content_hash(raw: bytes) -> str:
    return hashlib.sha256(raw).hexdigest()

# 3. Every chunk carries provenance so a RAG answer can cite its source.
def to_chunk_rows(doc_id, source_hash, page, chunks):
    return [{
        "chunk_id": hashlib.sha256(f"{doc_id}:{page}:{off}:{txt}".encode()).hexdigest(),
        "doc_id": doc_id, "source_hash": source_hash,
        "page_number": page, "char_offset": off, "text": txt,
    } for off, txt in chunks]

-- 4. Idempotent landing: upsert on the content-derived chunk_id.
--    A nightly re-run of unchanged files touches nothing (no dupes).
INSERT INTO curated.doc_chunks (chunk_id, doc_id, source_hash, page_number, char_offset, text)
SELECT chunk_id, doc_id, source_hash, page_number, char_offset, text
FROM   staging.doc_chunks
ON CONFLICT (chunk_id) DO NOTHING;      -- content hash already present => skip

-- 5. Only embed what is new: chunks with no vector yet.
--    Re-embedding cost is proportional to CHANGE, not corpus size.
SELECT chunk_id, text
FROM   curated.doc_chunks c
WHERE  NOT EXISTS (SELECT 1 FROM curated.doc_embeddings e WHERE e.chunk_id = c.chunk_id);

Step-by-step trace.

Decision	Before (naive load)	After (document pipeline)
Format handling	one extractor for all	detect + route per file
Scans	come out empty	OCR-ed only when no text layer
Identity	filename + load time	content hash (stable)
Citations	whole-doc text blob	doc_id + page + offset per chunk
Nightly re-run	row count doubles	`ON CONFLICT DO NOTHING` no-op
Re-embedding	whole corpus	only chunks lacking a vector

After the rollout, each file is opened, classified, and routed — born-digital PDFs read their text layer, scans and images go through OCR, DOCX/HTML go through a parser; every chunk is landed with a content-derived chunk_id and its doc_id/page/offset provenance, so retrieval can cite "page 7 of contract X"; the nightly re-run upserts on chunk_id and unchanged files are a no-op; and only chunks without a vector are embedded, so cost tracks change. The warehouse holds curated chunks, their embeddings, and the lineage that ties an answer back to a source page.

Output:

Metric	Naive load	Document pipeline
Scanned-doc coverage	0% (empty text)	full (OCR routed)
Citable to a page	no	yes (provenance)
Nightly re-run	duplicates rows	idempotent no-op
Re-embed cost	O(corpus)	O(changed chunks)
Wasted OCR on text PDFs	every page	none (detected)

Why this works — concept by concept:

Detect before extract — measuring the text layer and routing per file means born-digital pages take the cheap exact path and only true scans pay for OCR, so the pipeline is both correct on scans and affordable on the rest.
Provenance as output — carrying doc_id, page_number, and char_offset on every chunk turns the corpus into an auditable, citable dataset; a retrieval answer can point at the exact source line instead of a whole document.
Content-hash identity — a hash over the bytes (and per chunk over doc/page/offset/text) manufactures a stable key the source never provided, which is simultaneously the deduplication key and the idempotency key.
Idempotent upsert + change-scoped embedding — ON CONFLICT DO NOTHING makes a re-run a no-op, and embedding only vector-less chunks ties the expensive step to actual change rather than corpus size.
Cost — one detection per file, OCR only for scans, and embeddings only for changed chunks, versus OCR-ing and re-embedding everything each run. The eliminated cost is the wasted GPU/OCR spend and the duplicate rows a naive loader produces — O(change) instead of O(corpus) per run.

Design
Topic — design
Design problems on document ingestion pipelines

Practice →

Data processing Topic — data-processing Data processing problems on ingesting and transforming raw inputs

Practice →

2. PDF and OCR — text-layer, scanned, and layout

Detect the text layer before you extract; only scans pay for OCR

The mental model in one line: a PDF is not one thing — a born-digital PDF carries an embedded text layer you can read exactly and cheaply with pdfplumber or PyMuPDF (including per-word coordinates for reading order), while a scanned PDF is just page images with no text at all, so it must be rendered and passed through OCR (Tesseract locally or a cloud Document AI service), ideally after preprocessing (deskew, binarize, set DPI) and while keeping the per-word confidence — which means the first and most important step is detecting which kind of page you have, because text-extracting a scan returns nothing and OCR-ing a born-digital page is slow and less accurate than the text that was already there. Get detection right and OCR becomes a targeted tool, not a blunt tax on every page.

Born-digital vs scanned — detect first.

The text layer. A born-digital PDF stores the actual characters and their positions; extracting them is deterministic, exact, and needs no model. This is always the preferred path when it exists.
The scan. A scanned or photographed page is an image embedded in a PDF wrapper; get_text() returns an empty (or near-empty) string. The only way to recover text is to render the page to a raster and OCR it.
The detection heuristic. Extract the text layer; if a page yields below a small character threshold, treat it as image-only and route it to OCR. Some documents are mixed — a born-digital body with a scanned signature page — so detect per page, not per file.

pdfplumber / PyMuPDF — reading the text layer.

Words with boxes. Both libraries return words plus bounding boxes (x0, top, x1, bottom), which you need to reconstruct reading order and detect columns.
Reading order. PDFs store text in draw order, which is not always reading order; sort by (top, x0) (with a tolerance) or use a layout mode to recover human reading sequence.
Headers, footers, artifacts. Repeated top/bottom lines and page numbers are noise for retrieval; detect and strip them by position so they do not pollute chunks.

Tesseract / cloud OCR — reading the image.

Preprocessing pays off. Deskewing a tilted scan, binarizing to high-contrast black/white, and rendering at ~300 DPI materially improve OCR accuracy; garbage-in is the biggest cause of bad OCR.
Confidence is data. OCR engines return a per-word confidence; keep it. A page whose mean confidence is low should be flagged for review or a better engine, not silently trusted.
Language and layout. Set the language pack and, for structured pages, an OCR page-segmentation mode; a wrong mode scrambles columns and tables.

The failure modes senior engineers pre-empt.

OCR everything. Running OCR on born-digital pages is slow, costly, and less accurate than reading the existing text layer. Mitigation: per-page detection; OCR only pages with no text.
Ignore reading order. Emitting text in draw order jumbles multi-column pages into nonsense. Mitigation: sort words by position; handle columns.
Discard confidence. Trusting low-confidence OCR silently injects garbage into the warehouse and the embeddings. Mitigation: keep and threshold confidence; route low-confidence pages for review.

Common interview probes on PDF and OCR.

"How do you know a PDF needs OCR?" — extract the text layer; if a page is near-empty, it is image-only and must be OCR-ed.
"Why not OCR everything to be safe?" — it is slower and less accurate than the exact text layer; detect and route.
"How do you preprocess a scan?" — deskew, binarize, ~300 DPI; then OCR with the right language and segmentation mode.
"What do you do with OCR confidence?" — keep it; threshold and flag low-confidence pages instead of trusting them blindly.

Worked example — extract a born-digital PDF and flag pages that need OCR

Detailed explanation. The canonical first pass over a PDF: pull the text layer with pdfplumber, and for each page decide whether it is born-digital (keep the text) or image-only (mark it for OCR). Build a per-page classifier that also captures word boxes for later reading-order work.

The tool. pdfplumber for text and word boxes.
The signal. Character count per page; near-zero means image-only.
The output. Per-page text plus a needs_ocr flag.

Question. Extract text from a PDF page by page and flag any page whose text layer is empty enough to require OCR.

Input.

Page	Extracted chars	Verdict
1 (report body)	2,140	born-digital
2 (report body)	1,880	born-digital
3 (scanned signature)	3	needs OCR
4 (image chart only)	0	needs OCR

Code.

import pdfplumber

OCR_CHAR_THRESHOLD = 40   # per-page: below this, treat the page as image-only

def extract_pages(path: str) -> list[dict]:
    pages = []
    with pdfplumber.open(path) as pdf:
        for i, page in enumerate(pdf.pages, start=1):
            text = page.extract_text() or ""
            words = page.extract_words()          # each has x0, top, x1, bottom, text
            pages.append({
                "page_number": i,
                "text": text,
                "char_count": len(text.strip()),
                "needs_ocr": len(text.strip()) < OCR_CHAR_THRESHOLD,
                "word_boxes": words,              # kept for reading-order reconstruction
            })
    return pages

pages = extract_pages("report.pdf")
for p in pages:
    tag = "OCR" if p["needs_ocr"] else "text"
    print(f"page {p['page_number']:>2}  chars={p['char_count']:>5}  -> {tag}")

Step-by-step explanation.

pdfplumber.open gives page objects; page.extract_text() returns the embedded text layer for that page, or an empty string when the page is an image with no characters.
len(text.strip()) < OCR_CHAR_THRESHOLD is the per-page detector: born-digital pages return hundreds to thousands of characters, while a scanned page returns ~zero, so a small threshold cleanly separates them.
Crucially the decision is per page, not per document, so a mixed PDF — a born-digital body with one scanned signature page — routes only the scanned page to OCR and reads the rest cheaply.
page.extract_words() is captured now even for born-digital pages, because the word bounding boxes are what a later step uses to reconstruct reading order and strip headers/footers by position.
The result is a per-page manifest: text where it exists, a needs_ocr flag where it does not, and boxes throughout — the input the OCR and layout stages consume, with no page silently dropped.

Output.

Page	char_count	needs_ocr	routed to
1	2,140	false	text layer
2	1,880	false	text layer
3	3	true	OCR
4	0	true	OCR

Rule of thumb. Extract the text layer page by page and flag any page under a small character threshold as needs_ocr — detection is per page, not per file, because real documents mix born-digital and scanned pages. Capture word boxes even on text pages; you will need them for reading order.

Worked example — OCR a scanned page with preprocessing and confidence

Detailed explanation. For the pages flagged needs_ocr, render the page to an image, preprocess it, and run Tesseract while keeping the per-word confidence. The preprocessing is what separates usable OCR from garbage. OCR the scanned signature page from the previous example.

Render. Rasterize the PDF page at ~300 DPI.
Preprocess. Grayscale, threshold (binarize), deskew.
OCR. pytesseract with image_to_data so you get text and confidence.

Question. OCR a scanned page and return its text plus a mean confidence, discarding words below a confidence floor.

Input.

Step	Setting
DPI	300
Color	grayscale → binarized
Engine	Tesseract (`--oem 1 --psm 6`)
Confidence floor	drop words with conf < 60

Code.

import fitz                      # PyMuPDF: render PDF page -> image
import cv2, numpy as np
import pytesseract
from pytesseract import Output

def render_page(path: str, page_index: int, dpi: int = 300) -> np.ndarray:
    page = fitz.open(path)[page_index]
    pix = page.get_pixmap(dpi=dpi)                      # rasterize at 300 DPI
    img = np.frombuffer(pix.samples, dtype=np.uint8).reshape(pix.height, pix.width, pix.n)
    return img

def preprocess(img: np.ndarray) -> np.ndarray:
    gray = cv2.cvtColor(img, cv2.COLOR_RGB2GRAY)        # 1. grayscale
    _, binar = cv2.threshold(gray, 0, 255,              # 2. binarize (Otsu)
                             cv2.THRESH_BINARY + cv2.THRESH_OTSU)
    return binar

def ocr_with_confidence(img: np.ndarray, floor: int = 60) -> dict:
    data = pytesseract.image_to_data(img, config="--oem 1 --psm 6", output_type=Output.DICT)
    kept = [(t, int(c)) for t, c in zip(data["text"], data["conf"])
            if t.strip() and int(c) >= floor]           # keep confident words only
    text = " ".join(t for t, _ in kept)
    mean_conf = sum(c for _, c in kept) / len(kept) if kept else 0
    return {"text": text, "mean_confidence": round(mean_conf, 1), "kept_words": len(kept)}

result = ocr_with_confidence(preprocess(render_page("report.pdf", 2)))

Step-by-step explanation.

render_page rasterizes the PDF page to a 300-DPI image — resolution matters, because OCR accuracy collapses on low-DPI renders where character strokes blur together.
preprocess converts to grayscale then binarizes with Otsu's method, producing the high-contrast black-on-white that Tesseract is tuned for; a noisy or low-contrast scan is the number-one cause of bad OCR.
image_to_data (not image_to_string) returns per-word text and a conf value, which is the whole point: confidence is data you keep, not a number you throw away.
The confidence floor drops words Tesseract is unsure about (conf < 60), so obvious garbage does not enter the text; the retained mean confidence becomes a page-level quality signal you can threshold downstream.
A page whose mean_confidence comes back low is a signal — flag it for a better engine (a cloud Document AI service) or human review — rather than silently landing low-quality text into the warehouse and the embeddings.

Output.

Field	Value
text	"AGREEMENT executed on the 3rd day ..."
mean_confidence	91.4
kept_words	128
low-conf dropped	6

Rule of thumb. Render scans at ~300 DPI, binarize and deskew before OCR, and always read per-word confidence with image_to_data. Keep the mean confidence as a page-quality metric and route low-confidence pages to a better engine or review — never trust unmeasured OCR.

Worked example — per-page routing between text-layer and OCR

Detailed explanation. The two previous examples combine into a router that walks a PDF once and, per page, either keeps the cheap text layer or OCRs the image — emitting a uniform per-page record either way. This is the extraction stage's public interface.

Walk once. Detect per page.
Branch. Text layer → keep; image-only → render + OCR.
Unify. Same record shape, plus a source field recording which path ran.

Question. Produce a single function that returns uniform per-page text for a PDF, using the text layer where present and OCR only where needed.

Input.

Page	Path taken	source
1	text layer	pdf_text
2	text layer	pdf_text
3	OCR	ocr
4	OCR	ocr

Code.

def extract_pdf(path: str) -> list[dict]:
    out = []
    for p in extract_pages(path):                      # from the pdfplumber example
        if not p["needs_ocr"]:
            out.append({"page_number": p["page_number"], "text": p["text"],
                        "source": "pdf_text", "confidence": None})
        else:
            # only image-only pages reach the expensive OCR branch
            img = preprocess(render_page(path, p["page_number"] - 1))
            r = ocr_with_confidence(img)
            out.append({"page_number": p["page_number"], "text": r["text"],
                        "source": "ocr", "confidence": r["mean_confidence"]})
    return out

pages = extract_pdf("report.pdf")
n_ocr = sum(1 for p in pages if p["source"] == "ocr")
print(f"{len(pages)} pages, {n_ocr} needed OCR, {len(pages) - n_ocr} read from text layer")

Step-by-step explanation.

The router reuses the per-page detector: pages with a text layer (needs_ocr false) take the free branch and simply keep their extracted text with source="pdf_text".
Only pages that failed detection reach the OCR branch, so the expensive render-preprocess-OCR path runs on the minority of pages that actually need it — the cost control that per-page detection buys.
Both branches emit the same record shape — page_number, text, source, confidence — so every downstream stage (parsing, chunking, landing) consumes one uniform structure and never has to know which extractor ran.
Recording source and confidence on every page is provenance for the extraction method itself: later you can audit which pages came from OCR and re-run just those when your OCR improves, without touching the born-digital pages.
The summary line makes the economics visible: on a typical mixed corpus most pages read from the text layer and only a handful hit OCR, which is exactly the ratio that keeps the pipeline fast and cheap.

Output.

Metric	Value
pages total	4
read from text layer	2
OCR-ed	2
uniform record shape	yes

Rule of thumb. Wrap detection and both extractors behind one function that emits a uniform per-page record with a source field. The text-layer branch handles the majority for free, OCR runs only on image pages, and recording which path ran gives you extraction-method provenance for targeted re-runs.

Senior interview question on PDF extraction and OCR routing

A senior interviewer might ask: "You are ingesting a corpus of PDFs where some are clean exports and some are scans — and many files mix both. Design the extraction stage: how you detect per page whether OCR is needed, how you extract the text layer with reading order for born-digital pages, how you preprocess and OCR the scanned pages while keeping a quality signal, and how you emit a uniform result so the rest of the pipeline does not care which path ran."

Solution Using per-page detection, text-layer extraction, preprocessed OCR, and a uniform record

# 1. Detect per PAGE (files mix born-digital and scanned pages).
import pdfplumber, fitz, cv2, numpy as np, pytesseract
from pytesseract import Output

OCR_THRESHOLD = 40

def page_needs_ocr(text: str) -> bool:
    return len(text.strip()) < OCR_THRESHOLD

# 2. Born-digital: text layer + reading order from word boxes.
def read_text_layer(page) -> str:
    words = page.extract_words()                       # x0, top, x1, bottom, text
    # reading order: top-to-bottom, then left-to-right within a line tolerance
    words.sort(key=lambda w: (round(w["top"] / 10), w["x0"]))
    return " ".join(w["text"] for w in words)

# 3. Scanned: render -> binarize -> OCR with confidence.
def ocr_page(path: str, idx: int) -> dict:
    pix = fitz.open(path)[idx].get_pixmap(dpi=300)
    img = np.frombuffer(pix.samples, np.uint8).reshape(pix.height, pix.width, pix.n)
    gray = cv2.cvtColor(img, cv2.COLOR_RGB2GRAY)
    _, binar = cv2.threshold(gray, 0, 255, cv2.THRESH_BINARY + cv2.THRESH_OTSU)
    d = pytesseract.image_to_data(binar, config="--oem 1 --psm 6", output_type=Output.DICT)
    kept = [(t, int(c)) for t, c in zip(d["text"], d["conf"]) if t.strip() and int(c) >= 60]
    conf = sum(c for _, c in kept) / len(kept) if kept else 0
    return {"text": " ".join(t for t, _ in kept), "confidence": round(conf, 1)}

# 4. Uniform emit: same shape whichever path ran.
def extract(path: str) -> list[dict]:
    rows = []
    with pdfplumber.open(path) as pdf:
        for i, page in enumerate(pdf.pages):
            layer = page.extract_text() or ""
            if page_needs_ocr(layer):
                r = ocr_page(path, i)
                rows.append({"page_number": i + 1, "text": r["text"],
                             "source": "ocr", "confidence": r["confidence"]})
            else:
                rows.append({"page_number": i + 1, "text": read_text_layer(page),
                             "source": "pdf_text", "confidence": None})
    return rows

Step-by-step trace.

Layer	Component	Purpose
Detection	`page_needs_ocr`	per-page route decision
Born-digital	`read_text_layer`	exact text + reading order
Scanned	`ocr_page`	render, binarize, OCR + confidence
Quality	confidence floor + mean	flag bad OCR pages
Emit	uniform record + `source`	downstream is path-agnostic
Provenance	`page_number`, `source`	targeted re-runs, citations

After deployment, extract walks each PDF once; every page is tested for a text layer; born-digital pages return exact text ordered top-to-bottom then left-to-right; image pages are rendered at 300 DPI, binarized, and OCR-ed with a confidence floor and a retained mean; and each page comes back in one record shape carrying source and confidence. The rest of the pipeline consumes a clean per-page stream and never branches on extractor type, while source lets you re-OCR only the scanned pages when the engine improves.

Output:

Metric	OCR-everything	Detect + route
Text-page accuracy	degraded (OCR)	exact (text layer)
OCR cost	every page	scanned pages only
Reading order	often jumbled	sorted by position
Bad-OCR visibility	none	mean confidence per page
Downstream branching	per extractor	none (uniform record)

Why this works — concept by concept:

Per-page detection — because real documents mix born-digital and scanned pages, deciding per page (not per file) sends only true image pages to OCR and reads the rest exactly, which is both more accurate and far cheaper than a global OCR pass.
Reading-order reconstruction — sorting word boxes by (top, x0) recovers human reading sequence from the PDF's draw order, so multi-column and complex pages emit coherent text instead of scrambled fragments.
Preprocessed OCR with confidence — 300-DPI rendering plus binarization gives the engine a clean image, and reading per-word confidence turns OCR from a black box into a measurable step you can threshold and audit.
Uniform record + source — emitting one record shape with a source field decouples every downstream stage from the extractor and provides extraction-method provenance for targeted re-runs.
Cost — one detection per page, OCR confined to image pages, and no re-processing of good text, versus OCR-ing an entire corpus. The eliminated cost is the OCR compute and accuracy loss on pages that never needed it — O(scanned pages) instead of O(all pages).

Parsing
Topic — parsing
Parsing problems on extracting text from raw files

Practice →

String processing Topic — string-processing String processing problems on cleaning extracted text

Practice →

3. Text extraction and parsing — Unstructured and Apache Tika

A document becomes typed elements and structured tables, not a wall of text

The mental model in one line: serious document parsing means turning a file into typed elements — a Title, a NarrativeText, a ListItem, a Table — each with metadata (page, coordinates, source), rather than a single flat string, because the structure is exactly what downstream retrieval and analytics need; the Unstructured library does this across PDFs, DOCX, HTML, and email with one partition call, Apache Tika covers the long tail of a thousand office and legacy formats plus metadata, and dedicated text extraction of tables lifts them into rows you can query instead of flattening them into unreadable text — so the parsing stage's job is to preserve meaning (element type + table structure + metadata), not merely to pull characters out. Flatten a document to one string and you have thrown away the very structure that made it useful.

Typed elements — structure over strings.

Element types. A partitioner classifies each block as Title, NarrativeText, ListItem, Table, Header/Footer, and so on — so you can index narrative for search, keep titles as section anchors, and route tables to structured storage.
Metadata per element. Each element carries page_number, filename, a category, and often coordinates — the provenance you attach to every downstream chunk.
Why it matters. Retrieval quality and chunking both improve when you chunk within elements (never splitting a table across chunks, keeping a title with its section) instead of blindly slicing a flat string.

Unstructured — one partition call across formats.

partition. The library's partition(filename=...) dispatches on file type and returns a list of typed elements; partition_pdf, partition_html, partition_docx are the specialised entry points.
Strategies. For PDFs, a fast strategy reads the text layer while an hi_res/OCR strategy handles scans and complex layout — the same detect-and-route decision from section 2, exposed as a parameter.
Chunk-by-title. Unstructured can group elements into sections by their titles, giving structure-aware chunks out of the box.

Apache Tika — the long tail and metadata.

Format breadth. Tika parses a huge range — DOC/DOCX, PPT, XLS, RTF, ODF, email, and more — via one interface, making it the workhorse for heterogeneous corpora.
Metadata extraction. Tika returns document metadata (author, created/modified dates, content type) alongside the text — useful lineage and filtering fields.
Content-type detection. Tika sniffs the true type from magic bytes, so a mislabelled .txt that is really a PDF is handled correctly — detection you should not skip.

Table extraction — rows, not flattened text.

The problem. Flattening a table to text destroys the row/column relationships that make it meaningful; "100 200 300" is useless without its headers.
The fix. pdfplumber.extract_tables() (or Unstructured's table element with its HTML representation) returns a grid you can turn into tidy records — one row per line item, columns preserved.
Where it lands. Table rows often belong in a structured warehouse table, separate from the narrative chunks that feed embeddings.

The failure modes senior engineers pre-empt.

Flatten everything to a string. Losing element types and table structure destroys retrieval quality and makes tables unqueryable. Mitigation: keep typed elements; extract tables as rows.
One parser for all formats. A PDF-only extractor chokes on DOCX/PPTX/email. Mitigation: dispatch by detected content type (Unstructured/Tika) rather than by extension alone.
Drop metadata. Discarding page numbers and coordinates loses the provenance you need later. Mitigation: carry element metadata into every chunk.

Common interview probes on extraction and parsing.

"Why partition into typed elements?" — to preserve structure (titles, tables, lists) that flat text destroys and that improves chunking and retrieval.
"Unstructured or Tika?" — Unstructured for typed-element partitioning and RAG-shaped output; Tika for the broad long tail of formats and metadata.
"How do you handle tables?" — extract them as structured rows, not flattened text, and often land them separately.
"How do you know the real file type?" — sniff content type from magic bytes (Tika), do not trust the extension.

Worked example — partition a document into typed elements with Unstructured

Detailed explanation. The core Unstructured loop: call partition, get back typed elements, and use their categories and metadata rather than a flat string. Partition a mixed report and separate narrative from tables and titles.

Call. partition(filename="report.pdf").
Inspect. Each element has .category, .text, and .metadata (page number).
Route. Titles anchor sections; narrative feeds chunks; tables go to structured storage.

Question. Partition a document into typed elements and produce a per-element record with its category, page, and text, ready for structure-aware chunking.

Input.

Element	Category	Page
"Q3 Financial Summary"	Title	1
"Revenue grew 12% ..."	NarrativeText	1
"- North: 4.2M"	ListItem	1
line-item grid	Table	2

Code.

from unstructured.partition.auto import partition

def to_elements(path: str) -> list[dict]:
    # One call dispatches on file type (PDF/DOCX/HTML/email) and returns typed elements.
    elements = partition(filename=path, strategy="fast")   # "hi_res" to OCR scans/complex
    rows = []
    for el in elements:
        rows.append({
            "category": el.category,                       # Title / NarrativeText / Table ...
            "text": el.text,
            "page_number": el.metadata.page_number,        # provenance
            "is_table": el.category == "Table",
            "table_html": getattr(el.metadata, "text_as_html", None),  # structured form
        })
    return rows

elements = to_elements("report.pdf")
titles    = [e for e in elements if e["category"] == "Title"]
narrative = [e for e in elements if e["category"] == "NarrativeText"]
tables    = [e for e in elements if e["is_table"]]
print(f"{len(titles)} titles, {len(narrative)} narrative blocks, {len(tables)} tables")

Step-by-step explanation.

partition(filename=path) dispatches on the detected file type and returns a list of typed elements — the single entry point that hides whether the input was a PDF, DOCX, HTML, or email.
Each element exposes .category, so instead of a flat string you get a labelled stream: Title, NarrativeText, ListItem, Table, and more — the structure the rest of the pipeline routes on.
el.metadata.page_number is the provenance you carry forward; every chunk derived from this element inherits the page it came from, which is what later powers citations.
Tables are flagged and their structured HTML representation (text_as_html) is preserved, so a table is not flattened into meaningless space-separated numbers — you can reconstruct rows and columns from it.
Splitting the elements into titles, narrative, and tables lets each go to its right home: titles anchor section-aware chunks, narrative feeds embeddings, and tables go to structured storage — exactly the routing a flat string makes impossible.

Output.

Category	Count	Routed to
Title	6	section anchors
NarrativeText	41	chunks → embeddings
ListItem	12	chunks (kept with parent)
Table	3	structured rows

Rule of thumb. Partition into typed elements and route by category — titles anchor sections, narrative feeds chunks, tables go to structured storage — carrying each element's page metadata forward. A flat string throws away the structure that both chunking and retrieval depend on.

Worked example — extract a PDF table into tidy rows

Detailed explanation. Tables are where flattening hurts most. Pull a table out of a PDF as a grid and turn it into tidy records with real column names, so an invoice's line items become queryable rows rather than a string of numbers. Use pdfplumber's table extraction.

Extract. page.extract_tables() returns a list of row-lists.
Header. The first row is the header; the rest are records.
Tidy. Emit list[dict] keyed by header, typed where sensible.

Question. Extract a line-item table from a PDF page and return tidy records keyed by column header.

Input.

sku	qty	unit_cents
WIDGET-1	3	1400
GADGET-9	1	990

Code.

import pdfplumber

def extract_tables(path: str, page_index: int) -> list[dict]:
    with pdfplumber.open(path) as pdf:
        raw_tables = pdf.pages[page_index].extract_tables()   # list of row-lists
    records = []
    for grid in raw_tables:
        if not grid or len(grid) < 2:
            continue
        header = [(h or "").strip().lower().replace(" ", "_") for h in grid[0]]
        for row in grid[1:]:
            rec = {header[i]: (cell or "").strip() for i, cell in enumerate(row)}
            # light typing: numeric-looking columns become ints
            for k, v in rec.items():
                if v.isdigit():
                    rec[k] = int(v)
            records.append(rec)
    return records

rows = extract_tables("invoice.pdf", 1)

Step-by-step explanation.

extract_tables() returns each detected table as a list of rows, where each row is a list of cell strings — the raw grid, with the row/column structure intact rather than flattened.
The first row is treated as the header and normalised into clean keys (unit_cents, not "Unit Cents"), so the resulting records have stable, code-friendly column names.
Each subsequent row is zipped against the header into a dict, preserving the association between a value and its column — the relationship that flattening to text destroys.
A light typing pass turns numeric-looking cells into integers, so qty and unit_cents land as numbers ready for aggregation instead of strings — the minimum structure a warehouse table wants.
The output is tidy list[dict] — one dict per line item — which lands cleanly in a structured warehouse table, kept separate from the narrative chunks that feed embeddings; the table is now queryable (SUM(qty * unit_cents)) rather than an opaque blob.

Output.

sku	qty	unit_cents
WIDGET-1	3	1400
GADGET-9	1	990

Rule of thumb. Extract tables as grids and rebuild tidy records keyed by a normalised header, typing numeric columns — then land them in a structured table, not the narrative chunk store. A table flattened to text is unqueryable; a table kept as rows is just data.

Worked example — parse heterogeneous formats and capture metadata with Tika

Detailed explanation. A real corpus is not all PDFs — it has DOCX, PPTX, HTML, and email, sometimes mislabelled. Apache Tika parses the long tail through one interface and returns document metadata alongside the text. Parse a DOCX and capture both.

Detect. Tika sniffs the true content type from magic bytes.
Parse. One call returns text plus metadata.
Capture. Author, dates, and content type become lineage fields.

Question. Parse a non-PDF document with Tika and return its text plus metadata, using the sniffed content type rather than the file extension.

Input.

Field	Value
file	policy.docx
sniffed type	application/vnd...wordprocessingml.document
author	J. Rivera
modified	2026-07-30

Code.

from tika import parser as tika_parser

def parse_with_tika(path: str) -> dict:
    parsed = tika_parser.from_file(path)          # detects type from magic bytes, not ext
    meta = parsed.get("metadata", {}) or {}
    return {
        "content_type": meta.get("Content-Type"),  # the TRUE type, sniffed
        "author": meta.get("dc:creator") or meta.get("Author"),
        "modified": meta.get("dcterms:modified") or meta.get("Last-Modified"),
        "text": (parsed.get("content") or "").strip(),
    }

doc = parse_with_tika("policy.docx")
assert "wordprocessingml" in (doc["content_type"] or "")   # a real DOCX, even if mislabelled

Step-by-step explanation.

tika_parser.from_file hands the bytes to Tika, which sniffs the content type from magic bytes rather than trusting the extension — so a file named .txt that is really a PDF is still parsed correctly.
The call returns both content (the extracted text) and metadata, so text extraction and lineage capture happen in one pass across whatever format the file actually is.
content_type is recorded as the authoritative type; downstream routing and auditing key on the sniffed value, closing the "wrong extension" failure mode.
Author and modified dates are pulled into explicit lineage fields — provenance beyond page numbers, useful for filtering (only 2026 documents) and for audit trails.
Because Tika normalises a thousand formats behind one interface, the DOCX/PPTX/HTML/email long tail lands in the same record shape as PDFs — so the corpus is heterogeneous at the edge but uniform by the time it reaches chunking.

Output.

Field	Value
content_type	...wordprocessingml.document
author	J. Rivera
modified	2026-07-30
text length	8,214 chars

Rule of thumb. Use Tika for the non-PDF long tail and let it sniff the true content type from magic bytes, capturing author/dates/type as lineage. One interface across formats means the whole heterogeneous corpus reaches chunking in a single uniform shape.

Senior interview question on document parsing and structure preservation

A senior interviewer might ask: "Your corpus is a mix of born-digital PDFs, scanned PDFs, DOCX, HTML, and email, and it contains tables that matter — invoice line items, financial grids. Design the parsing stage: how you turn each file into typed elements, how you preserve table structure as rows instead of flattening it, how you handle the non-PDF formats and mislabelled files, and how you carry metadata so every downstream chunk keeps its provenance."

Solution Using Unstructured partitioning, table extraction, Tika for the long tail, and metadata capture

# 1. Dispatch by DETECTED type: Unstructured for common formats, Tika for the long tail.
from unstructured.partition.auto import partition
from tika import parser as tika_parser

COMMON = {"pdf", "docx", "html", "htm", "eml"}

def parse_document(path: str) -> dict:
    ext = path.lower().rsplit(".", 1)[-1]
    if ext in COMMON:
        elements = partition(filename=path, strategy="fast")   # hi_res for scans
        return {"elements": [_el(e) for e in elements], "meta": {}}
    parsed = tika_parser.from_file(path)                        # PPTX/XLS/RTF/ODF/...
    return {"elements": [{"category": "NarrativeText",
                          "text": (parsed.get("content") or "").strip(),
                          "page_number": None, "table_html": None}],
            "meta": parsed.get("metadata", {})}

def _el(e) -> dict:
    return {"category": e.category, "text": e.text,
            "page_number": e.metadata.page_number,
            "table_html": getattr(e.metadata, "text_as_html", None)}

# 2. Tables preserved as ROWS, not flattened, and landed separately from narrative.
import pdfplumber

def tables_as_rows(path: str) -> list[dict]:
    out = []
    with pdfplumber.open(path) as pdf:
        for pno, page in enumerate(pdf.pages, start=1):
            for grid in page.extract_tables() or []:
                if len(grid) < 2:
                    continue
                header = [(h or "").strip().lower().replace(" ", "_") for h in grid[0]]
                for row in grid[1:]:
                    rec = {header[i]: (c or "").strip() for i, c in enumerate(row)}
                    rec["_page"] = pno
                    out.append(rec)
    return out

# 3. Every element -> a provenance-carrying record for chunking.
def to_chunk_inputs(doc_id: str, parsed: dict) -> list[dict]:
    rows = []
    for el in parsed["elements"]:
        if el["category"] == "Table":
            continue                                # tables go to structured storage
        rows.append({"doc_id": doc_id, "category": el["category"],
                     "page_number": el["page_number"], "text": el["text"]})
    return rows

Step-by-step trace.

Layer	Component	Purpose
Dispatch	`parse_document`	Unstructured (common) + Tika (long tail)
Structure	typed elements	titles/narrative/tables kept distinct
Tables	`tables_as_rows`	structured rows, landed separately
Long tail	Tika + sniffed type	DOCX/PPTX/email/mislabelled handled
Metadata	element + doc metadata	provenance on every chunk
Output	chunk inputs	narrative → chunking; tables → warehouse

After deployment, each file is dispatched by detected type — Unstructured partitions the common formats into typed elements and Tika handles the long tail with sniffed content types; tables are lifted into tidy rows and routed to structured storage instead of being flattened; narrative and titles become provenance-carrying chunk inputs (each with doc_id, category, page_number); and the whole heterogeneous corpus emerges in one uniform shape. Retrieval sees clean narrative chunks with citations, and analytics sees real table rows.

Output:

Metric	Flatten-to-string	Structured parsing
Element types preserved	none	Title/Narrative/List/Table
Tables	unqueryable text	tidy rows in a table
Non-PDF formats	fail/garbled	handled (Tika)
Mislabelled files	mis-parsed	sniffed + correct
Provenance per chunk	none	doc_id + page + category

Why this works — concept by concept:

Typed-element partitioning — classifying each block as Title/Narrative/List/Table preserves the document's structure, which improves chunk boundaries and retrieval and lets each element type go to its right destination.
Tables as rows — extracting tables into tidy records keyed by header keeps the row/column relationships that flattening destroys, so financial and line-item data stays queryable in the warehouse.
Dispatch by detected type — Unstructured for common formats and Tika for the long tail, both keying on sniffed content type, means the whole heterogeneous corpus (including mislabelled files) is handled by one uniform stage.
Metadata as provenance — carrying page numbers and document metadata onto every element gives each downstream chunk the lineage it needs for citations and audit.
Cost — one partition call per file, table extraction only where tables exist, and a single uniform output shape, versus hand-writing a parser per format. The eliminated cost is per-format bespoke extraction code and the lost information a flat string throws away — O(formats handled by the library) instead of O(parsers you maintain).

Parsing
Topic — parsing
Parsing problems on structured extraction from documents

Practice →

ETL Topic — etl ETL problems on transforming raw sources into rows

Practice →

4. Chunking and normalization — strategies, cleaning, dedup

Chunk with overlap, clean deterministically, and drop the duplicates before you embed

The mental model in one line: preparing extracted text for embeddings is three disciplines — chunking it into retrieval-sized pieces (fixed/character, recursive-with-overlap, or structure-aware by element/title) so each chunk is small enough to embed but large enough to carry meaning, normalizing it deterministically (collapsing whitespace, fixing dehyphenation and ligatures, normalizing unicode and stripping control characters) so the same content always produces the same text, and deduplicating it (exact by content hash, near-duplicate by shingling/MinHash) so you neither store nor embed the same passage twice — because a chunk that is too big dilutes retrieval, text that is inconsistently cleaned defeats your hash-based idempotency, and duplicates waste embedding spend and skew results. Chunking, cleaning, and dedup are the difference between a tidy vector store and an expensive, noisy one.

Chunking strategies.

Fixed / character. Split every N characters or tokens — simplest, but blind to sentence and paragraph boundaries, so it often cuts mid-thought.
Recursive with overlap. Split on a hierarchy of separators (paragraph → sentence → word) to a target size, with a small overlap between adjacent chunks so context is not lost at the seam. The pragmatic default.
Structure-aware. Chunk within typed elements from section 3 — never split a table across chunks, keep a title with its section — using the structure the parser already gave you.
Size and overlap. Target a token budget the embedding model likes (e.g. a few hundred tokens) with ~10–20% overlap; too large dilutes the vector, too small fragments meaning.

Normalization and cleaning.

Whitespace. Collapse runs of spaces/newlines; PDFs and OCR are full of stray whitespace that inflates chunk size and breaks hashing.
Dehyphenation. PDFs break words across lines with a hyphen ("inter-\nnational"); rejoin them so "international" is one token, not two garbage ones.
Ligatures and unicode. Normalize ligatures (ﬁ → fi) and apply Unicode NFC/NFKC so visually identical text has one canonical byte form — essential for hashing to work.
Control characters. Strip non-printable and zero-width characters that OCR and PDFs inject and that pollute embeddings.

Deduplication.

Exact. A content hash (SHA-256) of the cleaned chunk detects byte-identical duplicates — boilerplate footers, repeated disclaimers, re-uploaded files.
Near-duplicate. Shingling + MinHash (or SimHash) estimates Jaccard similarity to catch almost-identical chunks (a document re-exported with trivial differences) without an O(n²) comparison.
Why before embedding. Dedup before the embedding call so you never pay to vectorise the same passage twice, and your vector store is not skewed by repeated content.

The failure modes senior engineers pre-empt.

Chunk mid-sentence. Blind fixed-size splitting cuts sentences and tables in half, wrecking retrieval. Mitigation: recursive splitting on natural boundaries with overlap; structure-aware chunking.
Inconsistent cleaning. If cleaning is non-deterministic (or skipped), the same content hashes differently and idempotency breaks. Mitigation: a single deterministic normalize function applied everywhere.
Embed duplicates. Vectorising boilerplate and re-uploads wastes money and skews similarity. Mitigation: exact + near-dup detection before the embed call.

Common interview probes on chunking and normalization.

"How do you chunk documents?" — recursive splitting on natural boundaries to a token budget with overlap, or structure-aware within elements; not blind fixed size.
"Why normalize before hashing?" — so visually identical text has one canonical form and the content hash is stable, which idempotency depends on.
"How do you dedup?" — exact by content hash, near-duplicate by shingling/MinHash, done before embedding.
"What chunk size?" — a few hundred tokens with ~10–20% overlap; large dilutes, small fragments.

Worked example — recursive chunking with overlap

Detailed explanation. The pragmatic default chunker: split on a hierarchy of separators down to a target size, adding an overlap between chunks so a sentence spanning a boundary is not lost. Chunk a cleaned narrative block to ~500 characters with 60 characters of overlap.

Separators. Try paragraph, then sentence, then word.
Target. ~500 chars per chunk.
Overlap. Carry the last ~60 chars into the next chunk.

Question. Split a long text into overlapping chunks that respect natural boundaries and never exceed a target size.

Input.

Setting	Value
target size	500 chars
overlap	60 chars
separators	["\n\n", ". ", " "]
input length	1,320 chars

Code.

def recursive_chunk(text: str, size: int = 500, overlap: int = 60,
                    seps=("\n\n", ". ", " ")) -> list[str]:
    # Split on the coarsest separator that yields pieces under `size`; then pack
    # pieces into chunks with an overlap tail so context survives the seam.
    def split(t: str, seps) -> list[str]:
        if len(t) <= size or not seps:
            return [t]
        head, *rest = seps
        parts, buf = [], ""
        for piece in t.split(head):
            candidate = f"{buf}{head}{piece}" if buf else piece
            if len(candidate) <= size:
                buf = candidate
            else:
                if buf:
                    parts.append(buf)
                parts.extend(split(piece, rest) if len(piece) > size else [piece])
                buf = ""
        if buf:
            parts.append(buf)
        return parts

    pieces = split(text, list(seps))
    chunks, i = [], 0
    for piece in pieces:                      # add overlap tail from the previous chunk
        tail = chunks[-1][-overlap:] if chunks else ""
        chunks.append((tail + " " + piece).strip() if tail else piece)
        i += 1
    return chunks

chunks = recursive_chunk(open("clean.txt").read())
print(f"{len(chunks)} chunks, sizes {[len(c) for c in chunks]}")

Step-by-step explanation.

split tries the coarsest separator first (paragraph \n\n), packing pieces into a buffer until adding the next would exceed size — so boundaries fall at paragraph breaks whenever possible.
When a single piece is still too large, it recurses to the next finer separator (sentence, then word), so the splitter degrades gracefully from paragraph to sentence to word rather than cutting blindly.
Because splits land on natural boundaries, a chunk almost never ends mid-word or mid-sentence — the thing that wrecks retrieval when a fixed-size splitter guillotines a sentence.
The overlap step prepends the previous chunk's last overlap characters to the next chunk, so a sentence straddling a boundary appears (in part) in both chunks and a query matching it retrieves the right neighbourhood.
The result is a list of chunks each ≤ the target size, aligned to natural boundaries, with overlapping context — the shape an embedding model and a retriever both want, and the input the next (cleaning-verified) stage hashes and embeds.

Output.

chunk	size	ends on
1	498	paragraph break
2	486	sentence end
3	336	end of text
overlap present	yes	60-char tails

Rule of thumb. Chunk recursively on a hierarchy of natural separators to a token/char budget, with ~10–20% overlap so context survives the seam. Blind fixed-size splitting cuts sentences and tables in half; recursive-with-overlap is the pragmatic default that keeps chunks coherent.

Worked example — deterministic normalization and cleaning

Detailed explanation. The same passage extracted twice must produce byte-identical text, or hashing and dedup break. A single deterministic normalizer handles whitespace, dehyphenation, ligatures, unicode, and control characters. Build it and prove it is idempotent.

Whitespace. Collapse runs; trim.
Dehyphenation. Rejoin words split across line breaks.
Unicode. NFC normalize; strip control/zero-width chars.

Question. Write a deterministic normalize that produces stable text, so the same content always hashes the same.

Input.

Issue	Raw	Normalized
line-break hyphen	"inter-\nnational"	"international"
ligature	"oﬃce"	"office"
double spaces	"a␣␣␣b"	"a b"
zero-width	"data"	"data"

Code.

import re, unicodedata

_CONTROL = dict.fromkeys(range(0, 32))          # strip C0 controls except we re-add \n
_CONTROL.pop(10); _CONTROL.pop(9)               # keep newline, tab for now
ZERO_WIDTH = ["", "‌", "‍", ""]

def normalize(text: str) -> str:
    # 1. Unicode canonical form (NFKC folds ligatures ﬁ->fi and compat forms).
    text = unicodedata.normalize("NFKC", text)
    # 2. Remove zero-width and control characters that OCR/PDFs inject.
    for z in ZERO_WIDTH:
        text = text.replace(z, "")
    text = text.translate(_CONTROL)
    # 3. Dehyphenate words broken across a line break: "inter-\nnational" -> "international".
    text = re.sub(r"(\w)-\n(\w)", r"\1\2", text)
    # 4. Collapse all whitespace runs to a single space; trim.
    text = re.sub(r"\s+", " ", text).strip()
    return text

# Idempotent: normalizing twice equals normalizing once (required for stable hashing).
raw = "The oﬃce inter-\nnational data   report."
once = normalize(raw)
assert once == normalize(once)                  # deterministic + idempotent

Step-by-step explanation.

unicodedata.normalize("NFKC", ...) folds ligatures (ﬁ → fi) and compatibility forms into a single canonical byte representation, so visually identical text is byte-identical — the precondition for hashing to work.
Zero-width and control characters, which OCR and PDF extraction sprinkle invisibly, are stripped; left in, they make two "identical" passages hash differently and pollute embeddings.
Dehyphenation rejoins words the PDF broke across a line (inter-\nnational → international), turning two garbage tokens back into the real word a retriever should match.
Collapsing whitespace runs to a single space removes the stray spacing PDFs and OCR produce, both shrinking chunk size and further canonicalising the text.
The assert once == normalize(once) proves the function is idempotent — normalizing already-normalized text changes nothing — which is exactly the property that makes a downstream content hash stable across re-runs and re-extractions.

Output.

Property	Result
ligature folded	office
dehyphenated	international
whitespace collapsed	single spaces
idempotent	normalize(normalize(x)) == normalize(x)

Rule of thumb. Apply one deterministic normalize — NFKC, strip control/zero-width, dehyphenate, collapse whitespace — everywhere, and assert it is idempotent. Stable text is what makes content hashing (and therefore dedup and idempotent loads) actually work.

Worked example — exact and near-duplicate detection

Detailed explanation. Boilerplate footers, repeated disclaimers, and re-uploaded files fill a corpus with duplicates you should not embed. Exact duplicates are caught by a content hash; near-duplicates (a document re-exported with trivial changes) need shingling and MinHash. Detect both before embedding.

Exact. SHA-256 of the normalized chunk.
Near. Shingle into k-grams, MinHash, compare signatures.
When. Before the embedding call, so duplicates are never vectorised.

Question. Given cleaned chunks, drop exact duplicates by hash and flag near-duplicates by MinHash similarity.

Input.

chunk	relation
A	unique
B	byte-identical to A (exact dup)
C	95% overlap with A (near dup)
D	unique

Code.

import hashlib
from datasketch import MinHash, MinHashLSH

def sha(text: str) -> str:
    return hashlib.sha256(text.encode("utf-8")).hexdigest()

def minhash(text: str, k: int = 5, num_perm: int = 128) -> MinHash:
    m = MinHash(num_perm=num_perm)
    tokens = text.split()
    shingles = {" ".join(tokens[i:i + k]) for i in range(len(tokens) - k + 1)}
    for s in shingles:
        m.update(s.encode("utf-8"))
    return m

def dedup(chunks: list[str], near_threshold: float = 0.9) -> list[str]:
    seen_hashes, kept = set(), []
    lsh = MinHashLSH(threshold=near_threshold, num_perm=128)
    for i, text in enumerate(chunks):
        h = sha(text)
        if h in seen_hashes:                    # 1. exact duplicate -> skip
            continue
        m = minhash(text)
        if lsh.query(m):                        # 2. near-duplicate -> skip
            continue
        seen_hashes.add(h)
        lsh.insert(f"c{i}", m)
        kept.append(text)
    return kept

unique_chunks = dedup([A, B, C, D])             # -> [A, D]

Step-by-step explanation.

The SHA-256 hash catches exact duplicates: chunk B is byte-identical to A (thanks to deterministic normalization), so its hash is already in seen_hashes and it is skipped without any similarity math.
For everything that passes the exact check, minhash shingles the text into overlapping k-grams and builds a compact signature that approximates the set of shingles — a fixed-size stand-in for the chunk's content.
MinHashLSH.query finds previously-kept chunks whose signature is within the Jaccard near_threshold (0.9), so chunk C — 95% overlapping with A — is recognised as a near-duplicate and skipped, even though its exact hash differs.
LSH makes near-dup detection sub-quadratic: instead of comparing every chunk to every other (O(n²)), it buckets similar signatures so each query is roughly constant time — the difference between feasible and hopeless at corpus scale.
Dedup runs before embedding, so A and D are the only chunks ever sent to the (expensive) embedding model; the boilerplate and the re-export never cost a vector and never skew retrieval with repeated content.

Output.

chunk	verdict	reason
A	kept	unique
B	dropped	exact hash match
C	dropped	MinHash ≥ 0.9
D	kept	unique

Rule of thumb. Dedup before embedding: a content hash removes exact duplicates and MinHash+LSH removes near-duplicates in sub-quadratic time. Every duplicate you drop is an embedding you do not pay for and a skew you keep out of the vector store.

Senior interview question on chunking, cleaning, and dedup

A senior interviewer might ask: "You are preparing extracted document text for embeddings. Design the chunking-and-normalization stage: how you chunk so pieces are coherent and retrievable, how you clean text deterministically so the same content always produces the same bytes, how you deduplicate exact and near-identical passages, and how each of these keeps embedding cost and retrieval quality under control — and why cleaning must come before hashing."

Solution Using recursive overlap chunking, a deterministic normalizer, and exact-plus-near dedup

# 1. Deterministic normalize FIRST — stable bytes are the precondition for hashing.
import re, unicodedata, hashlib
from datasketch import MinHash, MinHashLSH

def normalize(text: str) -> str:
    text = unicodedata.normalize("NFKC", text)
    for z in ("", "‌", "‍", ""):
        text = text.replace(z, "")
    text = re.sub(r"(\w)-\n(\w)", r"\1\2", text)     # dehyphenate line breaks
    return re.sub(r"\s+", " ", text).strip()          # collapse whitespace

# 2. Chunk recursively to a token budget with overlap (context survives the seam).
def chunk(text: str, size: int = 500, overlap: int = 60) -> list[str]:
    sentences = re.split(r"(?<=[.!?])\s+", text)
    chunks, buf = [], ""
    for s in sentences:
        if len(buf) + len(s) <= size:
            buf = f"{buf} {s}".strip()
        else:
            chunks.append(buf)
            buf = (chunks[-1][-overlap:] + " " + s).strip()   # overlap tail
    if buf:
        chunks.append(buf)
    return chunks

# 3. Dedup exact (hash) + near (MinHash/LSH) BEFORE embedding.
def prepare(raw_text: str) -> list[dict]:
    clean = normalize(raw_text)
    seen, kept = set(), []
    lsh = MinHashLSH(threshold=0.9, num_perm=128)
    for i, c in enumerate(chunk(clean)):
        h = hashlib.sha256(c.encode()).hexdigest()
        if h in seen:
            continue                                   # exact duplicate
        m = MinHash(num_perm=128)
        toks = c.split()
        for j in range(len(toks) - 4):
            m.update(" ".join(toks[j:j + 5]).encode())
        if lsh.query(m):
            continue                                   # near duplicate
        seen.add(h); lsh.insert(f"c{i}", m)
        kept.append({"chunk_id": h, "text": c})        # hash IS the identity
    return kept

Step-by-step trace.

Stage	Component	Effect
Normalize	NFKC + strip + dehyphenate + whitespace	stable, canonical bytes
Chunk	recursive + overlap	coherent, retrievable pieces
Exact dedup	SHA-256 set	byte-identical dups dropped
Near dedup	MinHash + LSH	~identical dups dropped, sub-quadratic
Identity	hash = chunk_id	idempotency + dedup key in one
Order	clean → chunk → dedup → embed	duplicates never vectorised

After the stage runs, raw text is normalized to canonical bytes, split into overlapping coherent chunks, and passed through exact then near-duplicate filters before anything is embedded; each surviving chunk's SHA-256 is both its stable identity and its dedup key. Because cleaning happens before hashing, the same passage from a re-extracted file produces the same hash and is recognised as already present — so re-runs neither duplicate rows nor re-embed unchanged content.

Output:

Metric	Naive prep	Structured prep
Chunk coherence	cut mid-sentence	natural boundaries + overlap
Text stability	varies per extraction	deterministic (NFKC etc.)
Duplicate chunks	embedded repeatedly	dropped before embed
Near-dup handling	none	MinHash/LSH
Chunk identity	positional/unstable	content hash (stable)

Why this works — concept by concept:

Deterministic normalization — NFKC folding, control/zero-width stripping, dehyphenation, and whitespace collapse give the same content one canonical byte form, which is the non-negotiable precondition for content hashing, dedup, and idempotent loads.
Recursive overlap chunking — splitting on natural boundaries to a token budget with overlap keeps chunks coherent and preserves context across seams, so retrieval matches whole thoughts rather than guillotined fragments.
Exact + near dedup before embedding — a hash removes byte-identical duplicates and MinHash+LSH removes near-identical ones in sub-quadratic time, so boilerplate and re-exports never cost a vector or skew similarity.
Hash as identity — using the chunk's content hash as its id makes dedup key and idempotency key the same value, so the same content is recognised on every re-run.
Cost — cleaning and hashing are cheap CPU work that runs once, while embedding is the expensive step gated behind dedup, so you pay to vectorise only unique content. The eliminated cost is duplicate embeddings and unstable re-processing — O(unique chunks) embedded instead of O(all chunks, every run).

Data transformation
Topic — data-transformation
Data transformation problems on chunking and normalization

Practice →

String processing Topic — string-processing String processing problems on text cleaning and dedup

Practice →

5. Landing to the warehouse and embeddings for RAG

Land raw to staging to curated, key on a content hash, and embed only what changed

The mental model in one line: the landing stage turns extracted, chunked text into a governed warehouse dataset through a raw → staging → curated model — raw holds the source bytes and a manifest, staging holds pages and typed elements, curated holds cleaned chunks with provenance (doc_id, page_number, char_offset) — where every row is keyed on a content hash so an idempotent upsert makes re-runs a no-op, and the final step batches the curated chunks through an embedding model into a vector store (pgvector with an ANN index) so a downstream RAG layer can retrieve by similarity and cite the source — and the discipline that makes it affordable is embedding only chunks whose hash is new, so ingestion cost tracks change, not corpus size. The warehouse ends up holding the text, the vectors, and the lineage that ties a retrieved answer back to a page.

The landing model — raw to staging to curated.

Raw. The source bytes (in object storage) plus a manifest row per file: doc_id, source_hash, filename, content type, ingested-at. The immutable record of what arrived.
Staging. Per-page and per-element rows from extraction/parsing — text, source (pdf_text/ocr), confidence, category, page. The intermediate you can reprocess without re-reading the source.
Curated. The cleaned, chunked, deduplicated chunks with full provenance and a content-hash chunk_id — the table analytics and embeddings read from.

Idempotency and provenance.

Content-hash keys. source_hash identifies a file; chunk_id (hash of doc/page/offset/text) identifies a chunk. Both are content-derived, so identity is stable across re-runs.
Idempotent upsert. INSERT ... ON CONFLICT (chunk_id) DO NOTHING (or DO UPDATE for metadata) means a re-run of unchanged files changes nothing — no duplicates, safe to schedule.
Provenance columns. doc_id, page_number, char_offset on every chunk let a RAG answer cite "page 7 of contract X" and let audit trace a value to its source line.

Embeddings and the vector store.

Batch embed. Send chunks to the embedding model in batches; store the returned vector alongside the chunk. Only embed chunks with no vector yet.
pgvector + ANN. A vector column plus an approximate-nearest-neighbour index (HNSW or IVFFlat) makes similarity search fast; the index is what turns a table of vectors into a retriever.
Retrieval joins provenance. A similarity query returns chunk_ids; joining back to curated gives the text and the doc_id/page for citation — the whole point of keeping provenance.

Change detection and cost.

Only embed the new. A chunk whose chunk_id already has a vector is skipped; embedding cost is proportional to changed/new chunks, not the corpus.
Reprocess from staging. When the chunker or cleaner improves, reprocess from staging (not the source bytes), re-hash, and only the chunks that actually changed get re-embedded.
Delete/supersede. When a document is re-uploaded and changes, its old chunks are superseded; track source_hash per doc_id to detect it.

The failure modes senior engineers pre-empt.

No provenance. Chunks without doc/page/offset cannot be cited or audited. Mitigation: provenance columns are mandatory, populated at chunk creation.
Non-idempotent loads. Truncate-and-reload or insert-without-conflict doubles rows and re-embeds everything. Mitigation: content-hash keys + upsert.
Re-embed everything. Embedding the whole corpus each run is the dominant cost. Mitigation: embed only vector-less chunks; reprocess from staging.

Common interview probes on landing and embeddings.

"What's your landing model?" — raw (bytes + manifest) → staging (pages/elements) → curated (chunks + provenance), content-hash keyed.
"How is it idempotent?" — content-hash chunk_id + ON CONFLICT DO NOTHING; re-runs of unchanged files are no-ops.
"Where do embeddings live?" — a vector column (pgvector) with an ANN index; retrieval joins back to provenance for citations.
"How do you control embedding cost?" — embed only new/changed chunks; reprocess from staging, not the source.

Worked example — the warehouse landing schema and an idempotent upsert

Detailed explanation. The schema is the contract. Model raw, staging, and curated with content-hash keys and provenance, then write an idempotent upsert so a nightly re-run of unchanged files is a no-op. Build the three-layer schema.

Raw. One manifest row per document.
Curated. One row per chunk with provenance and a hash id.
Upsert. ON CONFLICT (chunk_id) DO NOTHING.

Question. Design the landing tables and an idempotent load that never duplicates chunks on re-run.

Input.

Table	Grain	Key
raw.documents	one file	source_hash
staging.doc_chunks	one chunk (pre-curate)	chunk_id
curated.doc_chunks	one chunk (final)	chunk_id

Code.

-- Raw: the immutable record of what arrived (bytes live in object storage).
CREATE TABLE raw.documents (
  doc_id        text PRIMARY KEY,
  source_hash   text NOT NULL,            -- sha256 of the file bytes
  filename      text NOT NULL,
  content_type  text,
  ingested_at   timestamptz DEFAULT now()
);

-- Curated: one row per cleaned chunk, with PROVENANCE and a content-hash id.
CREATE TABLE curated.doc_chunks (
  chunk_id      text PRIMARY KEY,         -- sha256(doc_id:page:offset:text)
  doc_id        text NOT NULL REFERENCES raw.documents(doc_id),
  source_hash   text NOT NULL,            -- ties chunk to the file version
  page_number   int,                      -- provenance
  char_offset   int,                      -- provenance
  category      text,                     -- Title / NarrativeText / ...
  text          text NOT NULL
);
CREATE INDEX ON curated.doc_chunks (doc_id, page_number);

-- Idempotent load: a re-run of UNCHANGED files inserts zero rows.
INSERT INTO curated.doc_chunks
  (chunk_id, doc_id, source_hash, page_number, char_offset, category, text)
SELECT chunk_id, doc_id, source_hash, page_number, char_offset, category, text
FROM   staging.doc_chunks
ON CONFLICT (chunk_id) DO NOTHING;         -- content hash already present => skip

-- When a document is re-uploaded and CHANGED, its source_hash differs, so its
-- new chunks get new chunk_ids; supersede the old version's chunks:
DELETE FROM curated.doc_chunks c
USING  raw.documents d
WHERE  c.doc_id = d.doc_id AND c.source_hash <> d.source_hash;

Step-by-step explanation.

raw.documents records one manifest row per file keyed on source_hash, so the exact bytes that arrived are identifiable and the source stays immutable in object storage — the audit anchor for everything downstream.
curated.doc_chunks uses a content-derived chunk_id as its primary key and carries page_number and char_offset as first-class provenance columns, so every chunk knows exactly where it came from.
The ON CONFLICT (chunk_id) DO NOTHING upsert is what makes the load idempotent: because chunk_id is a hash of the content, a re-run of an unchanged file produces the same ids, which already exist, so zero rows are inserted.
The source_hash on each chunk ties it to a specific version of the file; when a document is re-uploaded with changes, its source_hash differs, so the supersede DELETE removes the stale chunks while the new ones land under new ids.
Together this gives a pipeline that is safe to schedule nightly: unchanged files are no-ops, changed files supersede cleanly, and nothing ever duplicates — the property a naive truncate-and-reload can never provide.

Output.

Scenario	Rows inserted	Result
First load	all chunks	corpus landed
Re-run, unchanged	0	idempotent no-op
Doc changed	new chunks; old deleted	clean supersede
Doc unchanged, chunker improved	reprocess from staging	only changed re-land

Rule of thumb. Model raw → staging → curated with content-hash keys and provenance columns, and load with ON CONFLICT (chunk_id) DO NOTHING. Idempotency comes free once identity is content-derived, and a source_hash per chunk lets changed documents supersede their old versions cleanly.

Worked example — embed chunks and store vectors with an ANN index

Detailed explanation. The final step turns curated chunks into searchable vectors: embed only the chunks that lack a vector, store them in a pgvector column, and build an ANN index so retrieval is fast. Wire embeddings + retrieval for the curated table.

Embed the new. Select chunks with no vector; batch-embed.
Store. A vector column in an embeddings table keyed on chunk_id.
Index + retrieve. HNSW index; nearest-neighbour query joins back to provenance.

Question. Embed only vector-less chunks, store them in pgvector with an ANN index, and write a retrieval query that returns text plus its citation.

Input.

Step	Detail
select	chunks with no embedding row
embed	batch through the model (dim 1536)
store	curated.doc_embeddings(chunk_id, vector)
index	HNSW for cosine similarity

Code.

-- Vector store: one embedding per chunk; ANN index makes similarity search fast.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE curated.doc_embeddings (
  chunk_id   text PRIMARY KEY REFERENCES curated.doc_chunks(chunk_id),
  embedding  vector(1536) NOT NULL
);
CREATE INDEX ON curated.doc_embeddings USING hnsw (embedding vector_cosine_ops);

# Embed ONLY chunks that have no vector yet -> cost tracks change, not corpus size.
def embed_new(conn, embed_fn, batch=256):
    rows = conn.execute("""
        SELECT c.chunk_id, c.text
        FROM   curated.doc_chunks c
        LEFT   JOIN curated.doc_embeddings e USING (chunk_id)
        WHERE  e.chunk_id IS NULL              -- only the un-embedded
    """).fetchall()
    for i in range(0, len(rows), batch):
        part = rows[i:i + batch]
        vectors = embed_fn([r["text"] for r in part])     # one batched model call
        conn.executemany(
            "INSERT INTO curated.doc_embeddings (chunk_id, embedding) VALUES (%s, %s) "
            "ON CONFLICT (chunk_id) DO NOTHING",
            [(r["chunk_id"], v) for r, v in zip(part, vectors)])
    conn.commit()
    return len(rows)

-- Retrieval: nearest chunks to a query vector, JOINED to provenance for citations.
SELECT c.text, c.doc_id, c.page_number,
       e.embedding <=> :query_vec AS distance     -- cosine distance
FROM   curated.doc_embeddings e
JOIN   curated.doc_chunks c USING (chunk_id)
ORDER  BY e.embedding <=> :query_vec              -- ANN index serves this
LIMIT  5;

Step-by-step explanation.

The doc_embeddings table stores one vector(1536) per chunk_id, and the HNSW index with vector_cosine_ops is what makes approximate-nearest-neighbour search fast — without it, retrieval would scan every vector.
embed_new selects only chunks with no embedding row (the LEFT JOIN ... IS NULL), so the expensive embedding calls run exclusively on new or changed chunks — the change-scoped cost control.
Embedding happens in batches, amortising the per-call overhead of the model API, and the insert uses ON CONFLICT DO NOTHING so even a retried batch cannot create duplicate vectors.
The retrieval query orders by cosine distance (<=>) which the HNSW index serves efficiently, returning the nearest chunks to a query vector — the core RAG lookup.
Crucially the retrieval joins back to curated.doc_chunks, so each hit comes with its doc_id and page_number — the answer is not just relevant text but citable text, which is the entire reason provenance was carried from extraction all the way here.

Output.

Metric	Value
chunks embedded this run	only new (e.g. 320 of 40k)
duplicate vectors	0 (conflict-guarded)
retrieval	top-5 by cosine distance
citation available	yes (doc_id + page)

Rule of thumb. Store one vector per chunk in pgvector with an HNSW/IVFFlat index, embed only chunks that lack a vector, and always join retrieval back to provenance. The index makes search fast, change-scoped embedding makes it cheap, and provenance makes the answer citable.

Worked example — incremental reprocessing via content-hash change detection

Detailed explanation. Documents get re-uploaded and pipelines get improved; neither should trigger a full reprocess. Content-hash change detection reprocesses only what actually changed. Handle a re-uploaded document and an improved chunker.

File change. New source_hash for a doc_id ⇒ reprocess that document.
Pipeline change. Reprocess from staging; only changed chunk_ids re-land and re-embed.
No change. Same hashes ⇒ nothing happens.

Question. Detect which documents and chunks actually changed and reprocess only those, leaving everything else untouched.

Input.

Event	source_hash	Action
doc A re-uploaded, identical	same	skip
doc B re-uploaded, edited	changed	reprocess B
chunker improved	(all)	reprocess from staging; re-land changed
doc C untouched	same	skip

Code.

import hashlib

def changed_documents(conn, incoming: list[dict]) -> list[dict]:
    # incoming: [{doc_id, source_hash, path}] from the object-store manifest scan.
    known = dict(conn.execute(
        "SELECT doc_id, source_hash FROM raw.documents").fetchall())
    # Reprocess only docs that are new or whose bytes changed.
    return [d for d in incoming if known.get(d["doc_id"]) != d["source_hash"]]

def reprocess(conn, docs: list[dict], extract_chunk_fn, embed_fn):
    for d in docs:
        chunks = extract_chunk_fn(d["path"])              # extract -> parse -> chunk -> clean
        for ch in chunks:
            ch["chunk_id"] = hashlib.sha256(
                f"{d['doc_id']}:{ch['page']}:{ch['offset']}:{ch['text']}".encode()).hexdigest()
        _upsert_manifest(conn, d)                         # raw.documents upsert
        _supersede_old(conn, d)                           # delete stale source_hash chunks
        _upsert_chunks(conn, chunks)                      # ON CONFLICT DO NOTHING
    embed_new(conn, embed_fn)                             # embeds only vector-less chunks

Step-by-step explanation.

changed_documents compares each incoming file's source_hash against what raw.documents already knows, returning only files that are new or whose bytes changed — so an identical re-upload of doc A is filtered out before any work happens.
For a genuinely changed doc B, reprocess re-extracts and re-chunks, and each chunk gets a fresh content-derived chunk_id; because the text changed, the changed chunks get new ids while unchanged chunks keep their old ids.
_supersede_old deletes the previous version's chunks (matched by the old source_hash), and _upsert_chunks lands the new ones with ON CONFLICT DO NOTHING, so B is cleanly replaced without duplicating unchanged content.
When the chunker improves rather than the source, you reprocess from staging: only chunks whose recomputed hash differs from the stored one actually change identity, so only those re-land and — via embed_new — get re-embedded.
embed_new at the end embeds only the chunks that now lack a vector, so a re-upload of one document or a chunker tweak costs embeddings proportional to the handful of changed chunks, never the whole corpus.

Output.

Event	Reprocessed?	Re-embedded chunks
doc A identical re-upload	no	0
doc B edited	yes (B only)	B's changed chunks
chunker improved	from staging	only changed chunk_ids
doc C untouched	no	0

Rule of thumb. Detect change by content hash — a differing source_hash reprocesses a document, a differing chunk_id re-lands and re-embeds a chunk — and skip everything unchanged. Reprocess from staging when the pipeline improves so you never re-read source bytes, and embedding cost always tracks actual change.

Senior interview question on the end-to-end landing and embedding architecture

A senior interviewer might ask: "Design the full landing architecture for a document pipeline feeding both warehouse analytics and a RAG retriever. Cover your raw/staging/curated model, how content-hash keys make the load idempotent, what provenance you carry and why, where and how embeddings are stored and searched, and how you keep both reprocessing and embedding cost proportional to change rather than corpus size."

Solution Using a raw/staging/curated model, content-hash idempotency, provenance, and change-scoped embeddings

-- 1. Three layers, content-hash keys, provenance on curated.
CREATE TABLE raw.documents (
  doc_id text PRIMARY KEY, source_hash text NOT NULL,
  filename text, content_type text, ingested_at timestamptz DEFAULT now());

CREATE TABLE curated.doc_chunks (
  chunk_id text PRIMARY KEY, doc_id text NOT NULL, source_hash text NOT NULL,
  page_number int, char_offset int, category text, text text NOT NULL);   -- provenance

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE curated.doc_embeddings (
  chunk_id text PRIMARY KEY REFERENCES curated.doc_chunks(chunk_id),
  embedding vector(1536) NOT NULL);
CREATE INDEX ON curated.doc_embeddings USING hnsw (embedding vector_cosine_ops);

-- 2. Idempotent load + clean supersede of changed documents.
INSERT INTO curated.doc_chunks
  (chunk_id, doc_id, source_hash, page_number, char_offset, category, text)
SELECT chunk_id, doc_id, source_hash, page_number, char_offset, category, text
FROM   staging.doc_chunks
ON CONFLICT (chunk_id) DO NOTHING;                       -- re-run = no-op

DELETE FROM curated.doc_chunks c USING raw.documents d
WHERE  c.doc_id = d.doc_id AND c.source_hash <> d.source_hash;   -- supersede old version

# 3. Embed ONLY the un-embedded; retrieval joins provenance for citations.
def embed_new(conn, embed_fn, batch=256):
    rows = conn.execute("""
        SELECT c.chunk_id, c.text FROM curated.doc_chunks c
        LEFT JOIN curated.doc_embeddings e USING (chunk_id)
        WHERE e.chunk_id IS NULL""").fetchall()
    for i in range(0, len(rows), batch):
        part = rows[i:i + batch]
        vecs = embed_fn([r["text"] for r in part])
        conn.executemany(
            "INSERT INTO curated.doc_embeddings VALUES (%s,%s) "
            "ON CONFLICT (chunk_id) DO NOTHING",
            [(r["chunk_id"], v) for r, v in zip(part, vecs)])

-- 4. RAG retrieval: nearest chunks + their citation.
SELECT c.text, c.doc_id, c.page_number, e.embedding <=> :q AS distance
FROM   curated.doc_embeddings e JOIN curated.doc_chunks c USING (chunk_id)
ORDER  BY e.embedding <=> :q LIMIT 5;

Step-by-step trace.

Layer	Component	Responsibility
Raw	manifest + source bytes	immutable record of what arrived
Staging	pages / elements	reprocess without re-reading source
Curated	chunks + provenance	the queryable, citable dataset
Identity	content-hash keys	idempotency + dedup in one
Vectors	pgvector + HNSW	fast similarity retrieval
Cost control	embed only un-embedded	spend tracks change, not corpus

After deployment, files land in raw with a source_hash; extraction and parsing populate staging; cleaned, deduplicated chunks land in curated via an idempotent ON CONFLICT DO NOTHING upsert with full provenance; changed documents supersede their old chunks by source_hash; only chunks lacking a vector are embedded into pgvector; and retrieval returns the nearest chunks joined to their doc_id/page for citation. Re-runs of unchanged files are no-ops, and both reprocessing and embedding cost are proportional to what actually changed.

Output:

Metric	Naive (truncate + reload + re-embed)	Landing architecture
Re-run of unchanged corpus	full reload + re-embed	no-op
Duplicate chunks/vectors	possible	impossible (hash keys)
Citations	none	doc_id + page per hit
Changed-doc handling	full rebuild	clean supersede
Embedding cost	O(corpus) every run	O(changed chunks)

Why this works — concept by concept:

Raw/staging/curated layering — separating immutable source, reprocessable intermediates, and the curated dataset lets you improve the pipeline and reprocess from staging without ever re-reading source bytes, while keeping a clean audit trail.
Content-hash identity — deriving source_hash and chunk_id from content makes identity stable across re-runs, so the same value serves as dedup key and idempotency key and re-loads are no-ops.
Provenance columns — doc_id, page_number, and char_offset on every chunk make the corpus auditable and make RAG answers citable to an exact source page, which is the difference between a demo and a trustworthy system.
pgvector + change-scoped embedding — vectors with an ANN index give fast retrieval, and embedding only un-embedded chunks ties the dominant cost to change rather than corpus size.
Cost — one idempotent upsert, embeddings only for new chunks, and reprocessing from staging, versus truncate-reload-and-re-embed each run. The eliminated cost is the repeated embedding of unchanged content and the duplicate rows a naive load creates — O(change) per run instead of O(corpus).

ETL
Topic — etl
ETL problems on idempotent loads and warehouse landing

Practice →

Design
Topic — design
Design problems on data landing and retrieval architecture

Practice →

Cheat sheet — unstructured document pipelines

The schema gap. A tabular load knows its columns; a document arrives as opaque bytes with no schema, keys, or fixed layout. A document pipeline manufactures structure, identity, and lineage instead of copying them — that is the whole discipline.
Detect before you extract. Route by format, and for PDFs measure the text layer per page: read the cheap exact text layer for born-digital pages, OCR only pages with no text. A global OCR pass is slow, costly, and less accurate than the text that was already there.
PDF extraction template. pdfplumber/PyMuPDF for the text layer + word boxes; sort words by (top, x0) for reading order; strip headers/footers by position. Detect per page (chars/page < ~40 ⇒ OCR) because files mix born-digital and scanned pages.
OCR template. Render at ~300 DPI, grayscale + binarize (Otsu), deskew; run Tesseract with image_to_data to keep per-word confidence; drop low-confidence words and flag low mean-confidence pages for a better engine or review. Never trust unmeasured OCR.
Parsing template. Partition into typed elements (Unstructured partition): Title / NarrativeText / ListItem / Table, each with page metadata. Apache Tika for the long tail (DOCX/PPTX/XLS/email) and metadata; sniff the true content type from magic bytes, not the extension.
Tables as rows. Extract tables (pdfplumber.extract_tables() or the element's HTML) into tidy records keyed by a normalised header, typed where numeric, and land them in a structured table — never flatten a table to text, which makes it unqueryable.
Chunking. Recursive split on natural boundaries (paragraph → sentence → word) to a token budget (~a few hundred tokens) with ~10–20% overlap so context survives the seam; or structure-aware within elements. Blind fixed-size splitting cuts sentences and tables in half.
Normalization. One deterministic normalize: NFKC (folds ligatures), strip control/zero-width chars, dehyphenate line breaks, collapse whitespace. Assert it is idempotent — stable bytes are the precondition for content hashing, dedup, and idempotent loads.
Deduplication. Exact by SHA-256 of the cleaned chunk; near-duplicate by shingling + MinHash/LSH (sub-quadratic). Dedup before embedding so boilerplate and re-exports never cost a vector or skew retrieval.
Landing model. raw (source bytes + manifest, keyed on source_hash) → staging (pages/elements, reprocessable) → curated (chunks + provenance, keyed on chunk_id). Load with INSERT ... ON CONFLICT (chunk_id) DO NOTHING; supersede changed docs by differing source_hash.
Provenance. doc_id, page_number, char_offset on every chunk — mandatory. It is what lets a RAG answer cite "page 7 of contract X" and lets audit trace a value to its source line.
Embeddings + retrieval. Store one vector per chunk in pgvector with an HNSW/IVFFlat ANN index; embed only chunks that lack a vector; retrieval joins back to provenance for citations. Embedding cost then tracks change, not corpus size.
Idempotency + cost. Content-hash keys make re-runs no-ops; reprocess from staging (not source) when the pipeline improves; embed only changed chunks. The whole pipeline's cost stays O(change), not O(corpus).

Frequently asked questions

What is an unstructured or document data pipeline?

An unstructured data pipeline is the ingestion path that turns documents — PDFs, scans, DOCX, HTML, email — into governed, queryable rows in a warehouse and, downstream, into embeddings a retrieval layer can search. Unlike a tabular load, whose schema and keys are known before it starts, a document arrives as opaque bytes with no schema, variable layout, sometimes no text layer, and no natural key, so the pipeline's first job is to discover structure: detect the format, extract the text and tables with the right fidelity, normalize and deduplicate, and attach provenance. The output is a dataset where each chunk carries its lineage (which document, which page, which offset), so analytics can query it and a RAG answer can cite it — a manufactured structure, not a copied one.

How do I know when a PDF needs OCR?

Detect it per page, not per file. Extract the text layer with pdfplumber or PyMuPDF and measure how many characters each page returns: a born-digital page returns hundreds to thousands of real characters, while a scanned or image-only page returns essentially zero (only stray artifacts). A simple threshold — say, fewer than ~40 characters per page — cleanly flags the pages that are image-only and must be rendered and OCR-ed. The per-page decision matters because real documents mix born-digital and scanned pages (a digital contract body with a scanned signature page), so a per-file decision either wastes OCR on text that was already perfect or returns empty strings for the scanned pages. Detect first, then OCR only the pages that actually need it.

Which tool should I use — pdfplumber, Unstructured, or Apache Tika?

They solve different parts of the problem, and a real pipeline uses several. Use pdfplumber (or PyMuPDF) for born-digital PDF text and word coordinates and for extracting tables as grids — it is exact and cheap when a text layer exists. Use the Unstructured library to partition documents into typed elements (Title, NarrativeText, ListItem, Table) with metadata across PDFs, DOCX, HTML, and email — the RAG-shaped output that preserves structure. Use Apache Tika for the long tail of formats (PPTX, XLS, RTF, ODF, legacy Office, email) and for metadata and content-type sniffing. For scans, none of these is OCR — you add Tesseract or a cloud Document AI service. The senior pattern is to dispatch by detected content type: text-layer extraction where possible, OCR for scans, Unstructured/Tika for the rest.

How should I chunk documents for embeddings?

Chunk into pieces small enough to embed but large enough to carry meaning — typically a few hundred tokens — using recursive splitting on natural boundaries (paragraph, then sentence, then word) rather than a blind fixed-size cut that guillotines sentences and tables. Add a small overlap (roughly 10–20%) between adjacent chunks so a sentence spanning a boundary still appears in both and retrieval finds the right neighbourhood. Even better, chunk structure-aware: use the typed elements from parsing so you never split a table across chunks and you keep a title with its section. Whatever the strategy, normalize the text deterministically first (so the same content produces the same bytes) and deduplicate before embedding, so you neither dilute a vector with an oversized chunk nor pay to embed the same passage twice.

How do I land documents in the warehouse with provenance?

Use a three-layer model. raw holds the source bytes (in object storage) plus one manifest row per file keyed on a source_hash. staging holds the extracted pages and typed elements, so you can reprocess without re-reading the source. curated holds the cleaned, chunked, deduplicated chunks, each with a content-derived chunk_id and provenance columns — doc_id, page_number, char_offset. Those provenance columns are the point: they let a RAG answer cite "page 7 of contract X" and let an auditor trace a value back to its source line. Load curated with an idempotent upsert (ON CONFLICT (chunk_id) DO NOTHING) so re-runs never duplicate, and store the embeddings in a separate vector table keyed on the same chunk_id, so retrieval joins straight back to the provenance.

How do I make document reprocessing idempotent and cheap?

Make identity content-derived and scope work to change. Hash the file bytes into a source_hash and each chunk into a chunk_id (over doc/page/offset/normalized text); because the same content always produces the same hash, an ON CONFLICT DO NOTHING upsert makes a re-run of unchanged files a no-op — no duplicate rows. Detect changed documents by comparing incoming source_hash values against the manifest and reprocess only those; when the pipeline itself improves, reprocess from staging (not the source bytes) so only chunks whose recomputed hash differs actually re-land. Finally, embed only chunks that lack a vector, so the dominant cost — the embedding calls — is proportional to what changed, not to the size of the corpus. The result is a pipeline safe to schedule nightly whose cost stays O(change), not O(corpus).

Practice on PipeCode

Drill the parsing practice library → for the PDF/text extraction, tokenization, and structured-parsing problems that pdfplumber, Unstructured, and Tika make concrete.
Rehearse the cleaning and reshaping work on the data processing practice library → for the normalization, chunking, and dedup scenarios where deterministic text handling earns its keep.
Sharpen the transformation muscle with the data transformation practice library → and the pipeline-shaping problems on the ETL practice library → for idempotent loads and warehouse landing.
Sharpen the architecture axis with the system design practice library → for the raw/staging/curated, provenance, and retrieval trade-offs a document pipeline must get right — against PipeCode's broader 450+ data-engineering catalogue.

Lock in document-pipeline muscle memory

Docs explain pdfplumber, Tesseract, and Unstructured. PipeCode drills explain the decision — when a page must be OCR-ed instead of read, when a table has to stay rows instead of text, when `normalization` must come before hashing, and when embedding only the changed chunks beats re-embedding the corpus. Pipecode.ai is Leetcode for Data Engineering — parsing and ingestion practice tuned for the production trade-offs senior data engineers actually face.

Practice parsing problems →
Practice data processing problems →