Stop Parsing Data Manually: How AI Extracts Structure from Unstructured Data

Getting data into a bronze layer is easy. Auto Loader, COPY INTO, a handful of Spark read options. The tools are established, and pipelines are genuinely easy to create. The hard part starts when those ingested terabytes are unstructured. Or when they look structured but aren’t.

The interpretation problem nobody talks about

Here’s a scenario that comes up more than it should. Raw files land in cloud storage, Auto Loader picks them up, a Bronze Delta table grows. Everything looks clean on the whiteboard. Then you open the actual data.

A column called contact_ref. Values formatted as XXX-XX-XXXX. I’ve seen good engineers who scan right past that in a manual audit without a second thought. An LLM reads the sample values and flags it as Social Security Numbers in under a minute. That gap isn’t about ingestion tooling. It’s about interpretation.

“Unstructured data” covers more ground than most people mean when they say it. Documents are the obvious case: PDFs, Word files, scanned invoices where OCR turns a neat table into a flat wall of text. The harder problem in practice is data that merely looks structured:

• Logs and JSON with optional fields that some systems produce and others don’t
• CSVs where date_of_birth is YYYY-MM-DD in the first 50 partitions and DD/MM/YYYY after a system migration nobody told your team about
• An amount column with negative values that could be refunds, corrections, or a data bug from Q3 last year
• Column names so generic (ref_id, val_1, status_code) that the schema communicates nothing

Rule-based systems fail here. Not because they’re poor tools, regex is excellent at what it does, but because they require you to know the pattern before writing the rule. You can’t write a regex for “this column holds something sensitive I haven’t seen before.” You can’t write a null check for a partition shift that hasn’t happened yet. That’s where the real bottleneck lives. Not storage, not compute, but interpretation at scale.

The architectural shape

Before getting into specific use cases, it’s worth establishing the pattern they all share. Once you see it once, the three scenarios below are just variations on it.

1. Raw data (Bronze)
2. Profile / Sample: schema, stats and representative values, never the full file
3. LLM call: structured prompt, structured output via JSON schema or function calling
4. Validation: Pydantic / jsonschema contract, reject off-spec responses
5. Approved output goes to the Silver table, Unity Catalog tags or DLT expectations
6. Rejected output goes to a quarantine table plus an alert

A few engineering decisions actually matter here:

Sampling. 200–300 cells per column handles most classification tasks. Full files never go to the LLM. Cost, latency, and context window limits all make that impractical. If you find yourself sending full files, something went wrong in the design.

Output enforcement. Always use function calling or tool use with a declared JSON schema. Free-text output is a trap: without a schema declaration, the model might return a confidence score as an integer, a float, or the string “high” depending on context. A schema gives you a contract you can test against.

The dead-letter queue. LLM output is an external API call. Treat it like one. Schema failures go to a quarantine table with context about what failed and why. That audit trail isn’t optional. You will need it the first time something goes wrong at 3am.

Three places an LLM earns its token cost

Entity extraction from documents

Consider a common scenario: pulling structured records from a vendor export. The files are technically CSVs, but one column holds free-form notes with an order ID, a complaint description, an embedded date range, occasionally a dollar figure mixed into a sentence. No parser, split, or regex handles that reliably. Every row is structurally different.

The traditional approach: build a parser, test it against 200 rows, deploy it, watch it break on row 201.

With an LLM: pass the raw column content plus a target schema to a Model Serving endpoint. Declare the fields you want back and enforce the output format via function calling with a JSON schema. Validate against a Pydantic model before writing downstream. Rows that fail validation go to a quarantine table, not silently into the silver layer where they corrode everything.

What makes this work and the thing regex genuinely cannot replicate is that the model reads context across the full record, not just field-level patterns. XXX-XX-XXXX in a column called contact_ref gets flagged as an SSN because the model reads the column name, the surrounding columns, and the sample values together. You can’t express that holistic reasoning as a rule.

Practical setup:

• Auto Loader lands raw files into a Bronze Delta table with _metadata columns preserving file path, source, and ingestion timestamp
• A Model Serving endpoint receives batches of raw text with sampled rows, never full files
• The prompt declares a strict output schema via function calling
• Pydantic or jsonschema validation runs on every response before anything touches Silver

The model will make mistakes. With a validation layer, those mistakes are catchable and you have a paper trail. That’s more than a regex silently producing garbage ever gave you.

PII detection across wide, evolving schemas

Ten tables you can audit manually. A thousand tables across an evolving platform, with new columns appearing every week as services ship features, the arithmetic doesn’t work.

The standard approach: schedule a quarterly review, scan column names, grep for email, phone, ssn. This reliably misses contact_ref. It misses ref_handle_v2. I once watched a column a contractor named uid_external hold passport numbers in the APAC partition for two quarters before anyone caught it.

Databricks built a system called LogSentinel to address this at scale running in production on their own infrastructure, not a demo environment. The ingestion step pulls table name, column name, inferred type, any existing Unity Catalog comment, and a random sample of values, batched per table to keep token costs manageable. This alone outperforms regex, because the model receives the column’s full context rather than just its name.

The augmentation step is what separates useful from genuinely good. For columns without existing comments, the system generates an AI-written description. It then retrieves few-shot examples from a Vector Search index built on a ground-truth labeled dataset and finding the most similar already-labeled columns and including them in the prompt. Instead of asking the model to guess cold, you’re giving it the most relevant solved examples from your own corpus.

Multiple model configurations run in parallel. Each produces a label and a confidence score. A multi-model ensemble selects the highest-confidence prediction. Output is a three-tier label structure: granular labels (100+ options), hierarchical rollups for reporting, and residency labels that directly control whether data can move cross-region. Those labels feed directly into Unity Catalog tags, masking policies, and access controls.

When the system detects a deviation between current annotations and new predictions, it auto-generates a JIRA ticket for the owning team with context about what changed, not just a flag that something did. Classification drift becomes a tracked production incident rather than a finding in next year’s audit.

Measured outcome: 92% precision and 95% recall on 2,258 labeled samples. Manual audit cycles that previously took weeks now complete in hours.

AI-assisted data quality rule generation

This one is closest to day-to-day data engineering work because it targets something you do constantly: writing validation checks.

Every new table triggers the same loop. Open a notebook, profile a few columns, write null checks and range checks, decide what values are valid in a status column. The rules end up inconsistent across teams because different engineers make different judgment calls on the same ambiguous data. Coverage is incomplete because being thorough across every column of every table isn’t realistic when you have three more tables waiting.

The DQX Profiler from Databricks Labs automates the profiling step. It computes column-level statistics: min, max, null percentage, distinct count, distribution shape. That’s the scaffolding.

An LLM receives the schema, the statistics, and sample values, then drafts a structured set of rule candidates. Not just “this column has 3 distinct values”, but the model interprets those 3 distinct values in context and infers: “this appears to be an order status field and it should contain only NEW, PROCESSING, and DONE.” That inference requires understanding what the column means, and the model derives it from the name combined with actual values.

Cross-field consistency rules are where this pays off most. If a table has a delivery_date column and an order_status column, and the model notices from sample data that delivery_date is populated while order_status is still NEW on certain rows, then it can draft the constraint: delivery_date should be null when order_status is ‘NEW’. Writing that rule manually requires understanding the relationship between columns. The model infers it from samples. It’s not always right, but it surfaces relationships you might otherwise miss.

Why human review isn’t optional

Here’s something that gets undersold in the AI-for-data-quality narrative: sample data lies.

Profile the first 1,000 rows of a partitioned table and you might observe 0% nulls in a column that’s critical for downstream logic. The next partition, the one from after a source system migration, or the third-party feed that went silent for two weeks, could be 30% null. A blindly deployed NOT NULL constraint breaks the next batch.

Similarly, a model seeing a score column with values between 0 and 100 will reasonably suggest a range check of 0 to 100. If that score is a raw sensor reading with a legitimate range of -40 to 300, the suggestion is statistically reasonable and contextually wrong. The model has no way to know that from samples. The engineer does.

What changes with AI assistance isn’t whether human review is needed. It’s what engineers spend that time doing. Before AI-assisted drafting, you start from a blank notebook and author every rule from scratch. You skip the less obvious checks because you have three more tables to finish. Coverage varies across teams based on who happened to be available when the table was profiled.

With AI-assisted drafting, you start from a candidate list that’s mostly correct. Effort goes toward reviewing, adjusting thresholds, and catching the contextually wrong suggestions not authoring from zero. That shift compounds: better baseline coverage across all tables, more consistent patterns across teams, less variation based on who happened to profile a given source.

A few practices worth locking in as you build this workflow:

• Set confidence thresholds before human review, not after. Low-confidence classifications should always be reviewed, full stop.
• Build feedback loops. When an engineer rejects or adjusts a model suggestion, that correction should feed back into prompt templates or few-shot examples. The model improves on your specific data over time.
• Don’t let draft quality become an invitation to rubber-stamp. The model is useful because it’s fast. That’s different from infallible.

Common failure modes

A few ways this goes wrong in practice:

Skipping the validation layer. Accepting free-text LLM output without a schema contract means errors are silent. Always enforce a Pydantic or json schema validation step before writing to Silver.

Profiling only the most recent partition. Sample data from the latest partition may not represent the full table. Sample across multiple partitions, especially when a source system migration is known or suspected to have occurred.

Missing the feedback loop. A system where human corrections disappear into a notebook rather than improving future prompts will plateau quickly. The labeled corrections are the most valuable training signal you have.

Bypassing the quarantine table. The dead-letter queue isn’t overhead. It’s your audit trail and your debugging surface when something breaks at 3am. Ship it from day one.

Getting started without overbuilding

You don’t need to build a system like LogSentinel in the first sprint. Nobody does.

Weeks 1–2: Pick your ten most critical Bronze tables. Run PII detection on column names plus 20–50 sample values per column using a Model Serving endpoint or the Databricks Data Classification product. Review the output manually. You will find at least one issue you weren’t aware of. That’s your proof of concept.

Weeks 3–4: Set up the DQX Profiler on those same tables. Let it generate rule candidates. Run a review session with the engineer who knows the source data. Approve the unambiguous checks, flag the contextually uncertain ones. Deploy approved rules as DLT expectations. You now have consistent validation coverage that didn’t exist before.

Month 2: Extend to entity extraction if document-type sources exist in your pipeline. Structured JSON output from Model Serving with Pydantic validation before writing to Silver. A quarantine table for failures. The architecture is the same pattern you already proved out on the PII use case, so it won’t feel novel by the time you get here.

The review workflow is what you’re really validating in month one. Getting comfortable with the shift from authoring to reviewing. Deciding what confidence threshold triggers mandatory human review. Working out how the team handles the feedback loop when corrections happen. That process, once it’s working, is the durable thing.

If your Bronze tables are full of data that only looks structured, and the interpretation step is where your pipelines stall, this is the kind of work we do on Databricks every week: sampling, schema-enforced LLM calls, validation and quarantine wired into the medallion flow. Talk to our team about what it would take in your platform.

Sources

1. Databricks Blog - LogSentinel: How Databricks uses Databricks for LLM-powered PII detection and governance
2. Databricks Labs DQX - Profiling and Quality Checks Generation
3. Databricks Blog - Find Sensitive Data at Scale with Data Classification in Unity Catalog