Nine-Hour Nightly Ingestion for Six Years of Clinical PDFs
A regional health system ingests clinical-guideline documents into a RAG corpus on Databricks. The source is ADLS Gen2 with 1.4 million PDFs and 90,000 DOCX files accumulated over six years, plus a JSON sidecar per file whose schema has changed three times: the field doc_type became document_type in v2 and doc_class in v3. The nightly job reprocesses the entire landing zone, takes nine hours, fails about twice a month, and is rerun manually. Roughly 11% of PDFs are scans with no text layer, so the parser returns empty or garbage; another 6% of files are byte-identical duplicates under different names, and 4% are near-duplicates re-exported with a new cover page. Extraction of dosing tables produces run-together text that clinicians say is unusable. Downstream consumers need page-level citations, and the compliance team needs to know which source file produced any given answer.
1. Redesign the pipeline so nightly runs are bounded and safe to re-run. Where do checkpoints, hashes, and MERGE belong?
Read the worked answer
Split ingestion into stages and stop treating the landing zone as the unit of work.
Bronze (raw, cheap, idempotent): Auto Loader with file notification on ADLS writes path, file size, modification time, ingestion timestamp, and raw content reference into an append-only bronze table. Its checkpoint is the record of what has been seen. This stage does no parsing, so it is fast and never the nine-hour bottleneck.
Stable identity: derive doc_id from the sidecar id when present, otherwise a deterministic hash of the canonical path - not from row order or filename alone, because files get renamed.
Silver (parse, chunk, embed): MERGE on (doc_id, content_hash), where content_hash is SHA-256 of extracted text after normalization. Only rows whose hash changed are re-parsed, re-chunked, and re-embedded. Chunk ids are deterministic (doc_id + page + chunk_index + hash), which makes chunk writes idempotent and keeps citations stable when unrelated pages change.
Bounded runs: availableNow trigger with a max-files-per-trigger cap, so a backlog drains over several runs instead of one nine-hour window. Separate scheduled backfill jobs take explicit path lists rather than reprocessing everything.
Failure handling: task-level retries, then a quarantine table for files that fail parsing after N attempts, with an alert. A rerun must be safe by construction - deterministic outputs plus MERGE - so manual reruns stop being a risk.
Page-level citations come from chunking per page and storing page_number and a bounding-box or character offset alongside each chunk.
2. Where would you detect exact and near-duplicate documents, and what key would you dedupe on without breaking citations?
Read the worked answer
Dedupe at two points for two different reasons.
Exact duplicates (6%) at bronze, on raw bytes. Hashing the file is cheap and avoids paying for parse and OCR on files you already have. Insert one canonical row plus an alias row mapping the duplicate path to the canonical doc_id.
Near duplicates (4%) after text extraction. Byte hashing cannot see a re-exported file with a new cover page. Normalize first - strip headers, footers, page numbers, and boilerplate, collapse whitespace, lowercase - then compute a hash of the normalized text (catches re-exports with identical bodies) and a shingle-based similarity signature such as SimHash or MinHash for the rest. Cluster at a similarity threshold, pick the canonical document by policy (most recent effective date, then most complete), and review a sample of borderline pairs manually before trusting the threshold.
Why not dedupe before extraction: you lose the ability to compare content, and you break provenance for the copies you drop.
Never delete the alias. Citations must resolve to the path the clinician or auditor can open. Keep a document_alias table (alias_path -> canonical doc_id, reason, detected_at) so a citation to any copy still resolves, and so you can answer audit questions about why two files became one document.
Watch the failure mode: near-duplicate detection on clinical text can collapse genuinely distinct versions of a guideline. Require the effective date and version string to match before clustering, otherwise keep both and flag the pair.
3. How do you handle the v1/v2/v3 sidecar schema and the scanned PDFs without the pipeline failing or silently degrading?
Read the worked answer
Sidecar drift: do not rely on inference alone. Auto Loader schema hints should declare the union of known fields; the rescued-data column captures anything unparsed so nothing is silently dropped. In silver, resolve the field explicitly: coalesce(document_type, doc_type, doc_class) into a single governed column, and keep schema_version from the sidecar so you can trace which contract a record came from. Any future rename lands in rescued data and raises an alert instead of breaking the job. Add an expectation that the resolved doc class is non-null; failing rows go to quarantine, not to the corpus. Monitor rescued-data and quarantine rates as first-class metrics - a rescued-data fraction above ~0.1% means the contract changed again.
Scanned PDFs: do not OCR everything, because that is where the nine hours go. After text extraction, measure characters per page. Pages below a threshold (for example, under 100 characters) are routed to OCR; the rest are kept as-is. Record extraction_method per page so downstream evaluation can compare OCR quality against digital text. OCR output has higher error rates - surface that in the citation metadata so reviewers know.
Dosing tables: plain text extraction destroys them. Use a parser that preserves table structure to markdown, and keep the table as a single chunk with its header row rather than splitting mid-row. Chunking policy should treat a table as an atomic unit, even if it exceeds the nominal chunk size; a truncated dosing table is worse than a large one.
Both issues share a principle: degrade visibly into quarantine and per-page metadata, never silently into the index.