Unstructured Data & AI-Ready Serving

M5–M7 built the retrieval side of the AI data platform. This module builds the other half: turning documents into governed warehouse tables with LLM batch extraction, running AI functions inside SQL with a real cost model, resolving entities with embeddings plus LLM judgment, and serving the whole platform to humans and agents through text-to-SQL and MCP.

The scenario — the backlog that became two tables and an endpoint

Monday, 09:10. Meena is a senior data engineer at a freight-forwarding SaaS, nine years in. Her sprint board holds the same two tickets it has held for a year. Ticket one: finance-ops keys ~300 vendor invoices a week from PDFs in s3://raw-docs/invoices/ into finance.invoice_lines by hand — 40,000 documents of backlog, error rate unknown. Ticket two: the ad-hoc query queue. By 11:30 she has answered "GMV by lane for Q2, but only EU corridors" for the third time this month, each time as a bespoke 40-line SQL snippet, because the ops team can't self-serve against a 1,900-table warehouse.

The same Monday, one quarter later.

The unstructured backlog is now two governed tables and one endpoint. The queue is now a dashboard she checks, not a job she does.

The rest of this module is the machinery behind that quarter, in build order.

Documents to tables — batch extraction behind a validation gate

Start with the contract, not the prompt. Extraction that lands in a warehouse is schema-first: define the target as a Pydantic model, and let structured outputs enforce it at the API level.

from pydantic import BaseModel
from typing import Literal
import anthropic

class InvoiceLine(BaseModel):
    description: str
    quantity: float
    unit_price: float
    line_total: float

class InvoiceRecord(BaseModel):
    vendor_name: str
    invoice_number: str
    invoice_date: str            # ISO-8601; validated downstream
    currency: Literal["INR", "USD", "EUR", "AED", "SGD"]
    total_amount: float
    lines: list[InvoiceLine]
    extraction_confidence: Literal["high", "medium", "low"]

client = anthropic.Anthropic()

response = client.messages.parse(
    model="claude-opus-4-8",
    max_tokens=16000,
    thinking={"type": "adaptive"},
    messages=[{
        "role": "user",
        "content": [
            {"type": "document",
             "source": {"type": "base64", "media_type": "application/pdf", "data": pdf_b64}},
            {"type": "text",
             "text": "Extract the invoice into the schema. Use only values present in the "
                     "document; if a field is absent or illegible, set confidence to 'low'. "
                     "Never infer or invent amounts."},
        ],
    }],
    output_format=InvoiceRecord,
)
record = response.parsed_output   # validated InvoiceRecord instance

In production this exact request shape runs through the provider's batch API — roughly 50% cheaper and built for 40,000-document backfills — with each result keyed by custom_id = doc_id. Warehouse-native alternatives do the same job without data leaving the platform: Snowflake AI_EXTRACT / AI_COMPLETE with a response format, or BigQuery AI.GENERATE_TABLE straight into a table. Pick two mental models: external batch API when you need the strongest extraction model and can ship documents out; warehouse-native when governance or data gravity says the documents stay in tenancy.

Schema validation is the floor, not the gate. The gate is a set of checks that treat LLM output as untrusted input, exactly as you treat a third-party feed:

• Allowed values and referential checksvendor_name fuzzy-matches the vendor master; currency is already enum-enforced by the schema.
• Cross-field arithmeticsum(lines.line_total) equals total_amount within tolerance; dates parse and fall in a plausible window.
• Confidence routinghigh promotes automatically; medium/low and any failed check land in invoice_review_queue for a human, never silently dropped and never silently promoted.
• Distribution monitorsGreat Expectations or Soda suites on the output table alert when the currency mix or reject rate drifts, which is how you catch a silent model or prompt regression.

Land raw extractions in a staging table (stg_invoice_extractions) carrying doc_id, model_version, and prompt_version before the gate promotes rows onward. When accuracy shifts, you can attribute it to a version instead of a vibe — and re-run only the affected slice.

The hard documents deserve their own stage. A born-digital invoice extracts cleanly; a scanned bill of lading with a rotated stamp and a nested rate table does not, and the choice of extractor is a measured trade-off, not a default.

• Specialized parsersDocling (fully OSS, the free-path recommendation) and LlamaParse: cheap per page, strong on layout and tables.
• A vision-capable LLM reading page imagescosts more per page, but wins on degraded scans, handwriting annotations, and tables whose structure the parsers mangle.

Route by document class: parser-first for the clean 80%, vision-LLM fallback for the pages the parser flags or fails, and record which path produced each record so accuracy can be tracked per route. Table extraction gets the strongest validation of all because the failure mode is silent transposition: every extracted table must reconcile against a known total — the invoice's stated sum, the manifest's declared count — before promotion, and a table that doesn't reconcile is a review-queue item, not a warehouse row.

For cleaning and standardization tasks (addresses, company names, category labels), the same discipline applies with one tightening: constrain outputs to a reference list — classify against known values, don't generate free text — and measure the fix-rate against your regex baseline before declaring victory. The pattern is classify-don't-generate: "map this company name to one of these 4,000 canonical vendors, or NO_MATCH" is auditable; "fix this company name" is an invitation to invent a plausible vendor that doesn't exist. Where the reference list fits in a warehouse function's argument, this whole task collapses into the in-SQL layer below.

In-SQL AI functions — and the cost model that decides when to use them

Sometimes the fastest pipeline is no pipeline. Every major warehouse now runs LLM functions inside SQL — Snowflake Cortex AISQL (AI_CLASSIFY, AI_FILTER, AI_COMPLETE), the BigQuery AI.GENERATE family, Databricks ai_query():

SELECT
    ticket_id,
    AI_CLASSIFY(
        ticket_body,
        ['billing', 'delivery_delay', 'damage_claim', 'documentation', 'other']
    ) AS category
FROM support.tickets
WHERE created_at >= CURRENT_DATE - 7;

No Python service, no orchestration hop, no data movement — and processing stays inside the governed platform boundary, which for regulated Indian clients under DPDP is often the deciding argument by itself. The trade is a per-row, per-token bill that arrives on your warehouse invoice, so you build the cost model before the backfill, not after. The model is always the same three factors — rows × tokens per row × price per token — plus the honest comparison against an external batch API:

Treat the dollar figures as illustrative — the method is the deliverable. Right-size the model per task: classification runs happily on a small, cheap model; contract-clause extraction earns the frontier model. And make the incremental path incremental — an AI_CLASSIFY in a view re-bills every query, so materialize the labels once and only classify new rows.

The decision between warehouse-native and external is rarely about the per-token rate alone. Warehouse-native wins when the data is already governed in place (no egress review, no new pipeline, processing inside the tenancy — the DPDP-friendly story), when the task is a simple per-row transform, and when the team's operating surface is SQL. The external batch API wins when you need the strongest frontier model, structured outputs against a rich Pydantic schema, prompt caching across a shared document context, or per-request control the SQL function signature doesn't expose. Most mature platforms run both: AI_CLASSIFY for the high-volume cheap transforms, batch-API extraction for the documents where accuracy is the product. Put the comparison in the cost-model spreadsheet either way — the spreadsheet is what turns "we should use AI functions" into an approved line item.

Entity resolution — embeddings for blocking, an LLM for judgment

The classic MDM task ("are Shree Logistics Pvt Ltd and SHREE LOGISTIC PRIVATE LIMITED, Mumbai the same vendor?") has a new toolkit, and its economics only work as a two-stage funnel. Comparing 1.2M vendor records pairwise is 720 billion comparisons; nobody LLM-adjudicates that.

Stage 1 — blocking with embeddings. Normalize (casefold, strip legal suffixes, canonicalize city names), embed a composed string (name | city | tax_id_prefix), and use ANN search — pgvector or FAISS — to pull each record's top-k neighbors above a cosine threshold. 1.2M records typically yield 2–4M candidate pairs: a 100,000× reduction that costs one embedding run. Splink's probabilistic blocking is the strong non-LLM alternative here, and pairs well with LLM adjudication downstream.

Stage 2 — LLM adjudication with a structured verdict. Each candidate pair goes to the model with both records and a forced verdict schema:

class MatchVerdict(BaseModel):
    verdict: Literal["match", "no_match", "unsure"]
    confidence: float          # 0.0 - 1.0
    evidence: str              # one sentence citing the deciding fields

response = client.messages.parse(
    model="claude-opus-4-8",
    max_tokens=1024,
    thinking={"type": "adaptive"},
    messages=[{"role": "user", "content": render_pair_prompt(record_a, record_b)}],
    output_format=MatchVerdict,
)
verdict = response.parsed_output

Route on thresholds, not vibes: match above 0.9 auto-merges with lineage recorded, no_match below 0.2 closes, everything between lands in a human review band. Run the adjudication through the batch API — 3M pairs is exactly what batch pricing exists for — and calibrate the thresholds against a hand-labeled sample of a few hundred pairs, comparing precision/recall against your deterministic fuzzy-match baseline. If the LLM stage isn't beating the baseline on the labeled set, fix the blocking or the prompt before scaling the spend.

The verdict's evidence field is not decoration: it's what makes the human review band fast, and it's the audit trail when someone asks in six months why two vendors were merged.

Two operational details separate a demo from an MDM capability:

• Incremental resolutionafter the backfill, new records only need blocking against the existing index and adjudication of their own candidate pairs: a few hundred LLM calls a day, not a re-run, which slots naturally into the same CDC-driven orchestration as the M7 index sync.
• Survivorship with lineagean auto-merge must record which source record won each field and which verdict justified the merge, written to the same kind of ledger as M7's sync state.

Merges are decisions about the business's master data; a merge you cannot explain or reverse is a data-quality incident on a delay timer.

Text-to-SQL — the QueryGPT pattern, and why curation beats prompting

Uber's QueryGPT reports cutting query-authoring time ~70% (vendor-reported — treat as directional), and its architecture is the reference pattern. It is a retrieval system that happens to emit SQL:

1. 1Intent → workspace. Classify the question into a domain workspace (finance, ops, growth) to shrink the search space from 1,900 tables to 30.
2. 2Schema + example retrieval. RAG over table schemas and a curated library of known-good SQL examples — the examples matter as much as the schemas.
3. 3Generation against the retrieved context, with the semantic layer's certified metric definitions in the prompt.
4. 4Self-correction loop. Run the SQL; on error, feed the error message back for one or two retries — this alone converts a large share of near-misses.

Build-vs-assemble options, vendor-neutral: LangChain/LlamaIndex plus your warehouse for the custom build, Vanna.ai for a packaged pattern, or the warehouse-native routes — Databricks Genie spaces, Snowflake Cortex Analyst — where curation happens in the vendor's semantic model.

The engineering insight that separates 31% from 87% execution accuracy is that accuracy is a data-curation problem, not a prompting problem — and curation is the data engineer's home turf (this is Uber's Finch lesson: curated single-table marts plus a semantic layer to kill WHERE-clause errors). The levers, in order of impact:

1. 1Expose curated wide marts. Not the raw schema.
2. 2Maintain column and value aliases in a metadata store. "EU corridors" → lane_region = 'EMEA'; value aliases kill the WHERE-clause errors that schema docs never fix.
3. 3Certify metric definitions in the semantic layer. dbt semantic layer, Cube — so "GMV" compiles to one expression.
4. 4Harvest real analyst SQL into the example library. Refreshed as schemas evolve.

Evaluation is a living process, not a launch gate. Maintain question→SQL→result triples (30–50 to start), score execution accuracy — compare result sets, not SQL strings, since many correct queries differ textually — and re-run the harness on every schema change, alias addition, and model swap. Uber's teams treat the benchmark as continuously maintained; the day it goes stale, the accuracy number becomes a lie.

Design the abstention path with as much care as the happy path. A text-to-SQL system that always answers is a system that confidently returns wrong numbers to people who can't check them — the worst failure mode in the building, because a wrong revenue figure in a QBR deck outlives any postmortem. When retrieval finds no curated table covering the question, when the question references a metric with no certified definition, or when self-correction exhausts its retries, the right answer is "I can't answer that reliably — here's the closest certified mart and the analyst channel," logged as an abstention so you can mine the log for what to curate next. Abstention rate on out-of-scope questions belongs in the eval harness alongside execution accuracy; a system scoring 87% on in-scope questions and answering nonsense on out-of-scope ones has not earned self-service.

And because a text generator is now emitting code against your warehouse, guardrails are layered, mostly outside the model:

MCP — exposing the platform to every assistant

The same governed serving layer, one abstraction up: an MCP server makes your platform a set of typed tools that any org AI assistant — Claude, an IDE agent, an internal copilot — can call. A minimal, high-leverage tool surface:

# Tools exposed by the data-platform MCP server
run_query(sql: str)            # read-only role, LIMIT + timeout enforced server-side
search_catalog(term: str)      # tables, columns, descriptions, owners
get_lineage(table: str)        # upstream/downstream for impact analysis
get_metric(name: str)          # certified semantic-layer definition + SQL expression

Build it with the MCP SDK, or start from the warehouse vendors' own MCP servers (Snowflake and Databricks both ship them; Genie supports custom MCP) and wrap your governance around them. The design rules are the ones you already know from the guardrail matrix, applied at the tool boundary:

• Caller entitlementsevery tool call executes under the caller's entitlements, resolved from a verified identity; never a shared super-role.
• Server-side limitsenforced inside the tool itself.
• Audit loggingevery call recorded with caller, arguments, and rows returned.

Agents multiply query volume, so the cost caps and monitors from M4 stop being optional hygiene and become load-bearing.

Done well, this is the quiet career move in the module: the engineer who owns the MCP layer owns how every AI system in the company touches the data platform.

Check yourself: Meena's text-to-SQL system scored 31% execution accuracy on the raw warehouse schema and 87% on curated marts — same model, same prompts. Why is accuracy a data-curation problem rather than a prompting problem, and what are the levers?

Where this lands in your week

This module anchors three labs, each producing a portfolio artifact that maps to what AI-data-platform postings actually screen for. LAB-12 — Unstructured → warehouse with LLM batch: extract structured records from 200 invoices/contracts via batch API and structured outputs, land them behind the full validation gate with a human-review queue — artifact: the pipeline plus a quality report with field-level accuracy on a 30-document hand-labeled sample. LAB-13 — In-SQL AI functions: on a free/trial warehouse tier (BigQuery sandbox needs no card; Databricks Free Edition and Snowflake trial credits cover the rest), classify support tickets with AI_CLASSIFY-class functions, embed and VECTOR_SEARCH the same data, and build the per-million-rows cost model against the external-API alternative — artifact: the SQL notebook plus the cost-model spreadsheet. LAB-14 — Text-to-SQL mini-platform: build the QueryGPT pattern small — schema RAG, curated examples, generation, self-correction — curate a semantic metadata layer, evaluate on 30 question→SQL pairs with execution accuracy, and wire in the read-only and row-limit guardrails — artifact: the working service plus an eval harness that demonstrates the accuracy uplift from metadata curation, the single most persuasive chart in this track's portfolio. LLM spend across all three stays within the ~$5-per-lab budget via batch pricing and small corpora. Do this at work this week → pb-41 Extract Structured Tables from a Document Corpus at Scale, pb-46 Build a Text-to-SQL Service on the QueryGPT Pattern, and pb-49 Expose Your Data Platform to AI Assistants Through an MCP Server. M9 then wraps this module's outputs in the evaluation and governance layer the capstone requires.