Building a data model from PDFs instead of designing one

There is a knowledgebase on my local stack called Aqua Valley: a small pile of annual water-quality reports and some membership statistics PDFs, and nobody has ever written a schema for it. Open its Data Model tab and you get 13 ObjectTypes at version 5: WaterQualityObservation with organisation, site, record_month, turbidity_ntu, e_coli_cfu_per_100ml, ph and chlorine_residual_mg_l, next to dimensions like MonitoringSiteDimension. Every property carries a semantic type; every row in every view carries the PDF it came from.

None of it was designed. The pipeline read the tables and worked it out.

This is the first piece in a series about Certant Strata, the layer that does that: how it works, then the four ways it broke on a real corpus.

Why bother deriving it at all

The structured data most organisations care about is already written down: annual reports, compliance filings, lab results, enterprise agreements. It stays stuck there, one table per document, no shared schema, no way to query across the corpus. Palantir Foundry's answer is a migration into their cloud and forward-deployed engineers, if you have that budget. Our bet was that the tables already contain the model.

Aqua Valley is our demo corpus, rebuilt by scripts/strata_demo_seed.py --recreate in about 30 minutes through the same APIs a user would touch. Its documents are synthetic; the two real corpora behind the failure numbers later are not.

What happens to a table between the PDF and the database

Strata is opt-in per knowledgebase and isn't retroactive: only documents processed afterward get extracted, profiled and matched. Existing documents need Re-run extraction, on the Tables tab.

Extraction doesn't re-read the PDF. It reads the content-list JSON checkpoint the OCR worker wrote, where MinerU, Docling and GLM-OCR each emit a table block as HTML in table_body. pandas.read_html parses that, falling back to a lenient BeautifulSoup parser that flattens rowspan/colspan, joins stacked headers into one row, and turns empty cells into NULLs. A table defeating both parsers is recorded as extraction_failed, raw HTML kept for diagnosis; one bad table has never failed a whole document.

Each table lands as a Parquet sidecar plus a context JSON, into the raw_extracted schema of the per-KB DuckDB file. That JSON, document title, section heading, caption, footnote, preceding paragraph, page number, OCR backend, is the biggest quality lever in the pipeline: it lets the next stage know a column called Result in a table captioned "Monthly turbidity readings" holds turbidity measurements.

Profiling runs next: the statistical half is pure DuckDB over the all-VARCHAR raw table (exact counts, distinct counts, a TRY_CAST sweep for type inference); the semantic half is one LLM call per table at temperature 0, classifying every column into a controlled vocabulary (measurement_value, date_calendar, currency_aud, identifier_code and so on), guessing what one row represents, flagging forecast-readiness. One call per table, never per column: cost at corpus scale shapes everything.

Two things in that pass cost real debugging time. TRY_CAST('3.5' AS BIGINT) rounds in DuckDB rather than failing, so integer detection uses regexp_full_match(TRIM(x), '[+-]?[0-9]+') instead. Currency values get $ and thousands separators stripped before the DOUBLE cast; % deliberately doesn't, so percentages stay VARCHAR rather than becoming numbers 100x wrong.

Each column also gets an embedding, computed with Qwen/Qwen3-Embedding-4B at 2,560 dimensions over the column name, semantic type, generated description and sample values. Those embeddings live in the profile sidecar, never the retrieval vector stores: one job, matching.

Matching columns without a threshold

The same concept gets a different name in every document: Site Name in the 2024 annual report, Monitoring Location in the Q3 quarterly, loc in the compliance filing. Unify those or you have a folder of tables, not a data model.

The original algorithm clustered columns by cosine similarity above an absolute threshold of 0.75, then had an LLM confirm each group. The LLM half survived; the threshold didn't. Real embeddings' same-concept and different-concept similarity distributions overlap, so a single cutoff is either loose enough to collapse a corpus into one group, or tight enough to miss matches. The fix: mutual top-k nearest-neighbour ranking, a pair becomes a candidate only when each column ranks the other inside its own k nearest neighbours. Relative ranking survives an overlap an absolute number can't.

Phase B is unchanged: one LLM call per candidate group at temperature 0, given column names, semantic types, sample data, source titles and captions. It confirms or drops members, proposes a canonical snake_case name, emits per-member transformation SQL (TRIM(x), TRY_CAST(x AS DOUBLE)), and returns a confidence. At 0.8 or above the match auto-integrates; below that, a human review queue with column members side by side and an editable canonical name.

We proved this deliberately. During the acceptance pass we ingested a "drifted" FY2025 report whose turbidity column had been renamed to Turb. NTU, with a brand new Chlorine Residual mg/L column appended: the classic supplier-changed-the-spreadsheet problem.

A match's identity is the SHA-256 of its sorted member set, so a person's decision can't be silently overturned by a later run: different membership means a different match, a fresh conflict rather than a rewrite. That identity also works as a cross-round cache key: confirmed groups cost nothing on the next run, which mattered, since a 1,888-column corpus produces roughly 375 candidate groups and confirming them serially, uncached, took hours per round. They now run concurrently with a semaphore of 8, never Celery fan-out, which livelocked on 5 July 2026.

What comes out the other end

Synthesis takes the confirmed matches, per-table entity guesses and the current model, then proposes the corpus-level structure in one LLM call: ObjectTypes (the nouns), properties with semantic types and star-schema roles, LinkTypes (the verbs, each carrying a real SQL join condition and a cardinality). The proposal passes an OWL structural validator before going active; a rejected one leaves the previous version in place. We call this layer an ontology; I'll call it the model.

The number I like on that screen sits on the OrganisationDimension card: 9 backing tables. The organisation column appears in nine source tables across two report families; the matcher stitched them into one entity. The version number is an audit trail: v5 means five synthesis rounds ran, nothing rewritten in place.

Each ObjectType becomes a DuckDB view in the ontology schema: a UNION ALL across its backing tables with each table's transformation SQL applied, plus two provenance columns outside the model itself.

CREATE OR REPLACE VIEW ontology.water_quality_measure AS
SELECT
    'raw_extracted.doc_annual_report_2024__p12_t0' AS _source_table,
    'doc_annual_report_2024'                       AS _source_document,
    "Site Name"                                    AS location,
    TRY_CAST("Sample Date" AS DATE)                AS measure_date,
    TRY_CAST("Result" AS DOUBLE)                   AS value
FROM raw_extracted.doc_annual_report_2024__p12_t0
UNION ALL
SELECT 'raw_extracted.doc_lab_report__p1_t0', 'doc_lab_report_2024',
       loc, TRY_CAST(sample_dt AS DATE), TRY_CAST(test_result_val AS DOUBLE)
FROM raw_extracted.doc_lab_report__p1_t0;

TRY_CAST everywhere, never CAST. OCR'd values contain garbage; a view that fails wholesale on one bad cell is useless. A row whose measure won't cast stays visible as NULL, counted by the completeness monitor.

Asking the model a question triggers one temperature-0 call that plans the query; a guard then validates the SQL before anything executes: SELECT only, ontology.* views only so raw tables stay unreachable, at most three joins, and joins must follow stored LinkType conditions so the LLM can't invent a join key. Asked for average turbidity by monitoring site in 2024, Aqua Valley returns three rows: Eastern Catchment Intake 2.4, Northern Reservoir 2.17, Southern Treatment Plant 2.51, aggregated across five source PDFs with a chip for each. Western Bore is absent, correctly: the FY2024 report carried no Western Bore readings. Asked something the corpus doesn't contain ("what is our employee churn rate?") it returns a "No matching data model" card with its own reasoning instead of a number.

The raw layer stays where you can see it

The governed model stays one click from the evidence it was built from.

The demo corpus makes that easy to check. The FY2024 water report yields 36 rows, 3 sites by 12 months, so Western Bore's absence is visible before you run a query. The drift report shows 7 columns where its siblings show 6. Preview is manifest-validated: only table names the extraction manifest knows about can be queried, keeping the raw layer inspectable without turning it into an injection surface.

Where it broke

On 12 June 2026 I ran two fresh corpora through the whole thing overnight and wrote down everything, including what didn't work.

Cell-level fidelity beat my expectations. Run A's unified view had exactly 50 rows (10 documents by 5 classification levels), fixture 01's Level 1 row reading 58,000 / 59,800 / 61,600 / 63,400, exact. In run B both ground-truth probe cells came through to the cent, including $1,834.20 from page 288 of a 414-page fully scanned Victorian agreement whose noisy embedded text layer we bypassed in favour of real OCR, a scan that took 7.5 minutes.

Then the failures.

The DuckDB write lock hit first. Three of ten extract tasks in run A failed with "Could not set lock ... Conflicting lock is held", even at a pacing of three uploads per wave. Two recovered on re-dispatch. The third kept failing because the process holding the lock, PID 27, was an idle Celery prefork child sitting on a leaked writable connection with no task running. Killing that child released the lock instantly: a connection some exit path had forgotten to close, not a concurrent writer.

Three matchers then raced on one knowledgebase. Run B's final consolidation round, over all 257 tables, ran more than 75 minutes, longer than kombu's default 3,600-second visibility timeout, so Celery redelivered the task to a second worker mid-run, and a debounced third round joined them. That default applied because ml_worker shares its Redis broker and sets no broker_transport_options, so the 28,800 seconds configured elsewhere never took effect. The racers hit an unlocked version read-increment, and the active pointer briefly landed on a degraded v3 (3 ObjectTypes, 14 tables, no LinkTypes) instead of the clean v1 (17 ObjectTypes, 15 LinkTypes): the "data model doesn't reflect my documents" symptom, mechanism now mapped. The restored v1 still shows it, Compensation and CompensationFact both exist, two proposals merged into one namespace. (The eval records v1 twice: 7 ObjectTypes / 6 LinkTypes from the five DSEAVs, and 17 / 15 when restored. I haven't reconciled the two readings.)

Synthesis then ran out of context. The one clean round matched 131 column groups across all 257 tables, zero sent to review, then died with Requested input length 149729 exceeds maximum input length 131071. At that corpus size no new model version could be produced until synthesis was made hierarchical. The matches held; only the modelling step starved.

Multi-effective-date schedules also flatten. The other probe cell, $1,370.25, is present in the raw layer and absent from the unified view: weekly_rate_aud mapped the 1 July 2022 column, not 1 July 2023 (the max for that classification came out as $1,330.33, exactly one 3% increase earlier). Enterprise agreement rate schedules carry four or five effective-date columns per measure; synthesis canonicalises one. The data is extracted completely and modelled partially: right concepts, but the rate's date dimension needs melting into rows.

One smaller edge: with many same-shaped tables, a synthesis round can temporarily omit a backing table from a view, and the next round absorbs it. The membership statistics tables on the demo KB did exactly that, missing at v3, present at v4.

One more finding had nothing to do with correctness. For 8.3 hours, the only way to know what Strata was doing was to read worker logs: the hub page fetched once on mount and never polled, no API surface carried a "run in progress" signal. That got its own piece of work.

Lineage and the ERD

Once a corpus has been through this, two views make it legible to somebody who wasn't watching.

I haven't measured the thing a sceptic would most want: how long a competent data engineer would take to model these corpora by hand, so there's no speedup multiple here. What I can say: nobody wrote a line of schema for either corpus, and on the enterprise agreements, six of the seven ObjectType names the tabular side produced were the words the text-side entity detector had independently picked out of the prose.

The next piece in this series follows one number all the way back: the weekly rate on page 146 of the Aruma agreement, in the 44-row Schedule J table, and what it takes to make a figure in a dashboard click through to the exact page it was computed from.