From Claim PDFs to Governed Delta Tables with Databricks AI Functions
A practical walkthrough for ingesting P&C claim documents into a Bronze-Silver-Gold lakehouse using Databricks ai_parse_document and ai_extract 2.1, with deterministic validation and a human-review step before data reaches Gold.
Most document ingestion pipelines encounter the same failure mode: they treat AI extraction as the final step rather than the first step in a governed delivery chain.
A model can parse a claim PDF and return structured values. Validation and human review reduce the risk that those values are incorrect, incomplete, or inconsistent with your business rules - but they do not eliminate it. That risk reduction requires deterministic checks, confidence thresholds, and a human-review step before any extracted value becomes a trusted record.
This article walks through a practical pattern for ingesting P&C claim documents into a Bronze-Silver-Gold lakehouse using Databricks ai_parse_document and ai_extract 2.1. The focus is on the reasoning behind each stage, not on exhaustive SQL.
Prerequisites
Both AI functions have specific compute requirements. ai_parse_document requires Databricks Runtime 17.3 or above, or serverless compute with environment version 3 or above - the VARIANT type that the function returns is only available from those versions. ai_extract 2.1 requires Databricks Runtime 15.4 LTS or above, or serverless compute. Both functions are available in SQL and Python; this article uses SQL throughout.
Regional availability varies. Not all Databricks regions support these functions. Check the AI function availability by region page before building against them.
The parse snippet below reads from a Unity Catalog external volume path. To follow the example, upload the synthetic PDF to a Unity Catalog volume - for example main.claims_landing.raw_pdfs - and replace the read_files path accordingly. The principal running the query needs READ VOLUME on the source volume and MODIFY on the target Silver table. The extraction snippet reads from claims.silver.parse_raw_responses, which is assumed to exist with at least three columns: document_id STRING, parsed_at TIMESTAMP, and raw_parse_response VARIANT. The extraction step writes claims.silver.extracted_claims directly; the validation snippet reads from that same table. Note that document_id throughout these examples is the source file path returned by read_files - for example dbfs:/Volumes/main/claims_landing/raw_pdfs/batch-2024-09-16/claim-001.pdf - not the extracted claim reference number such as CLM-2024-089341.
1. The Practical Problem
A property and casualty claim PDF is a document that contains information your systems need as structured data. The same claim might arrive as a scanned form, a fillable PDF, or a multi-page loss notice. The layout varies. The terminology varies. The date formats vary.
Consider a synthetic claim document with the following content:
Claim Reference: CLM-2024-089341
Policy Number: POL-AUTO-774821
Date of Loss: September 14, 2024
Reported Date: September 16, 2024
Claimant: Jordan M. Reyes
Loss Description: Rear-end collision at intersection, vehicle damage to rear bumper and trunk.
Estimated Claimed Amount: USD 8,400.00
Assigned Adjuster: T. Hargrove
This document needs to become a structured, reviewable record in your lakehouse. The challenge is not extracting the values - Databricks AI functions handle that well. The challenge is knowing whether the extracted values are trustworthy enough to act on, and building a delivery chain that makes that judgment explicit and auditable.
The four fields this pattern targets are claim_reference_number, policy_number, date_of_loss, and claimed_amount. These are the minimum identifiers needed to link an extracted record back to a claim in a downstream system and to apply the most important business rules. Additional fields - claimant name, loss description, adjuster, currency - can be added to the schema without changing the pipeline structure.
2. The Lakehouse Flow
The pipeline moves through three layers. Each layer has a defined purpose and a defined handoff condition.
| Stage | Layer | What happens |
|---|---|---|
| Raw PDF stored as binary | Bronze | Document ingested, no transformation |
ai_parse_document | Bronze to Silver | Binary converted to structured VARIANT |
ai_extract 2.1 | Silver | Fields extracted with confidence scores and citations |
| Deterministic validation | Silver | Business rules applied; records flagged or approved |
| Human review | Silver | Flagged records inspected and corrected |
| Gold promotion | Gold | Approved records written as trusted, traceable records |
Bronze holds the original binary and ingestion metadata. Silver holds every intermediate result - parse output, extracted values, validation flags, and reviewer decisions. Gold holds only records that have passed both deterministic validation and, where required, human review. Nothing reaches Gold by default; promotion is an explicit, idempotent operation.
3. Parse the Claim Document
ai_parse_document converts a binary document into a structured VARIANT. It identifies text paragraphs, tables, form fields, headers, and figures, and returns them as a typed element array. The version option pins the output schema so downstream code is not affected by future default-version changes.
The document binary is read from cloud storage using read_files. The binaryFile format returns the raw content as a BINARY column. The path column returned by read_files with binaryFile format is used as the document identifier - not file_path.
-- Read the binary content and parse the document structure.
-- Persist the raw parse response before any downstream use.
-- ai_parse_document requires Databricks Runtime 17.3+ or serverless env 3+.
-- Replace the read_files path with your Unity Catalog volume path.
-- The principal running this query needs READ VOLUME on the source
-- and MODIFY on claims.silver.parse_raw_responses.
-- 'path' is the column returned by read_files binaryFile format.
INSERT INTO claims.silver.parse_raw_responses (
document_id,
parsed_at,
raw_parse_response
)
SELECT
path AS document_id,
current_timestamp() AS parsed_at,
ai_parse_document(
content,
map('version', '2.0')
) AS raw_parse_response
FROM read_files(
'dbfs:/Volumes/main/claims_landing/raw_pdfs/batch-2024-09-16/',
format => 'binaryFile'
);
The raw response is persisted to an intermediate table before any field extraction or status derivation. Calling ai_parse_document inside a CTE and referencing the CTE multiple times in the same statement does not guarantee a single function call. Persisting the result first is the correct approach and avoids redundant billable calls.
The response includes an error_status field that surfaces per-page parsing failures. Parse errors should be recorded and handled before extraction proceeds. Documents with a failed parse status should not be passed to ai_extract. If error_status is present and non-empty, record the failure and route the document to a separate error table rather than continuing the pipeline.
4. Extract Structured Fields
ai_extract 2.1 reads the parsed VARIANT and returns field values with per-field confidence scores and source citations. The schema is passed as an inline STRING literal - the second argument to the function. The field-definition format uses description and type keys, as shown in the official ai_extract documentation. Verify the exact accepted schema format against the current Databricks documentation for the runtime version in use.
The example below writes claims.silver.extracted_claims directly so the validation snippet can read from it without any additional setup. CREATE OR REPLACE TABLE is used here only because this is a disposable synthetic demonstration; it replaces the table on every run. A production pipeline must preserve prior results - use INSERT INTO or a keyed MERGE instead. This compact form does not guarantee a single AI invocation; persisting the raw parse response before extraction, as shown in the previous step, remains a production requirement.
The outer query projects typed columns from the raw VARIANT result. min_required_confidence is calculated as the minimum confidence score across the four core fields; it returns NULL if any core score is missing, which the validation step treats as low_or_missing_confidence. extraction_status is set to failed when the response is null or result:error_message::STRING is non-null; otherwise it is set to extracted.
-- Write claims.silver.extracted_claims for use by the validation step.
-- CREATE OR REPLACE is for this synthetic demo only.
-- Production writes must preserve prior results (INSERT INTO or keyed MERGE).
-- ai_extract 2.1 requires Databricks Runtime 15.4 LTS+ or serverless.
-- Reads from claims.silver.parse_raw_responses (document_id, raw_parse_response).
CREATE OR REPLACE TABLE claims.silver.extracted_claims AS
SELECT
document_id,
result:response:claim_reference_number:value::STRING AS claim_reference_number,
result:response:policy_number:value::STRING AS policy_number,
result:response:date_of_loss:value::STRING AS date_of_loss,
result:response:reported_date:value::STRING AS reported_date,
TRY_CAST(
result:response:claimed_amount:value::STRING
AS DECIMAL(18,2)
) AS claimed_amount,
CASE
WHEN result:response:claim_reference_number:confidence_score IS NULL
OR result:response:policy_number:confidence_score IS NULL
OR result:response:date_of_loss:confidence_score IS NULL
OR result:response:claimed_amount:confidence_score IS NULL
THEN NULL
ELSE LEAST(
result:response:claim_reference_number:confidence_score::DOUBLE,
result:response:policy_number:confidence_score::DOUBLE,
result:response:date_of_loss:confidence_score::DOUBLE,
result:response:claimed_amount:confidence_score::DOUBLE
)
END AS min_required_confidence,
CASE
WHEN result IS NULL
OR result:error_message::STRING IS NOT NULL
THEN 'failed'
ELSE 'extracted'
END AS extraction_status,
result AS raw_extraction_response
FROM (
SELECT
document_id,
ai_extract(
raw_parse_response,
'{
"claim_reference_number": {
"description": "Unique claim reference assigned by the insurer",
"type": "string"
},
"policy_number": {
"description": "Policy number associated with this claim",
"type": "string"
},
"date_of_loss": {
"description": "Date the loss occurred, ISO 8601 format preferred",
"type": "string"
},
"reported_date": {
"description": "Date the loss was reported to the insurer, YYYY-MM-DD preferred",
"type": "string"
},
"claimed_amount": {
"description": "Total amount claimed as a numeric value without currency symbols",
"type": "number"
}
}',
options => map(
'version', '2.1',
'enableCitations', 'true',
'enableConfidenceScores', 'true'
)
) AS result
FROM claims.silver.parse_raw_responses
) extracted;
For the synthetic claim above, the response would look similar to this:
{
"response": {
"claim_reference_number": {
"value": "CLM-2024-089341",
"confidence_score": 0.97,
"citation_ids": [1]
},
"policy_number": {
"value": "POL-AUTO-774821",
"confidence_score": 0.96,
"citation_ids": [1]
},
"date_of_loss": {
"value": "2024-09-14",
"confidence_score": 0.94,
"citation_ids": [2]
},
"reported_date": {
"value": "2024-09-16",
"confidence_score": 0.95,
"citation_ids": [2]
},
"claimed_amount": {
"value": 8400.00,
"confidence_score": 0.91,
"citation_ids": [3]
}
},
"error_message": null,
"metadata": {
"version": "2.1",
"chunk_type": "bbox",
"citations": [
{
"id": 1,
"bbox": [
{"coord": [100, 120, 350, 150], "page_id": 0}
]
},
{
"id": 2,
"bbox": [
{"coord": [100, 170, 350, 200], "page_id": 0}
]
},
{
"id": 3,
"bbox": [
{"coord": [100, 220, 350, 250], "page_id": 1}
]
}
]
}
}
This is a synthetic illustration of the response structure, not an executed result. The actual response shape should be verified against the official documentation.
Field values are accessed via paths such as result:response:policy_number:value. Confidence scores are at result:response:policy_number:confidence_score. Citation IDs are at result:response:policy_number:citation_ids. The full citations array is at result:metadata:citations. The result:error_message field surfaces function-level errors and should be checked before reading field values.
result:metadata:citations contains an id, a bbox array, and within each bbox entry a coord array (x1, y1, x2, y2) and a zero-based page_id. A citation ID in a field's citation_ids array references the matching entry in that citations list by id. In the illustrative response above, claim_reference_number and policy_number both reference citation id 1, which identifies a bounding-box region on page 0 (zero-based). date_of_loss and reported_date reference citation id 2, also on page 0. claimed_amount references citation id 3, on page 1. A reviewer can use these coordinates to locate the source region in the original PDF. If bounding-box overlays are needed, page images can be generated using the imageOutputPath option documented for ai_parse_document; otherwise, the coordinates serve as a guide for consulting the original document directly.
Monetary values should be stored as DECIMAL(18, 2) rather than DOUBLE to avoid floating-point rounding. Date strings should be stored as STRING at extraction time and cast to DATE during validation, where invalid formats can be flagged explicitly rather than silently coerced.
5. Validate Before Trusting
Validation applies deterministic business rules to the extracted values. These rules do not use AI. They are explicit, testable, and auditable.
The rules for this pattern cover five categories: missing required identifiers, invalid dates, invalid amounts, low or missing confidence scores, and upstream parse or extraction failures. The confidence threshold of 0.75 used below is illustrative. The right threshold depends on your document population, your error tolerance, and calibration against real outcomes. Do not use 0.75 in a production system without that calibration.
-- Apply deterministic validation rules to extracted values.
-- Confidence threshold is illustrative; calibrate against your document population.
-- DECIMAL(18,2) is used for claimed_amount throughout Silver and Gold.
-- Reads from claims.silver.extracted_claims written by the extraction step.
-- document_id is the source file path, not the extracted claim reference number.
SELECT
document_id,
claim_reference_number,
TRY_CAST(date_of_loss AS DATE) AS date_of_loss,
TRY_CAST(reported_date AS DATE) AS reported_date,
TRY_CAST(claimed_amount AS DECIMAL(18,2)) AS claimed_amount,
ARRAY_COMPACT(ARRAY(
CASE WHEN claim_reference_number IS NULL
OR trim(claim_reference_number) = ''
THEN 'missing_claim_reference' END,
CASE WHEN policy_number IS NULL
OR trim(policy_number) = ''
THEN 'missing_policy_number' END,
CASE WHEN TRY_CAST(date_of_loss AS DATE) IS NULL
THEN 'invalid_date_of_loss' END,
CASE WHEN TRY_CAST(date_of_loss AS DATE) > current_date()
THEN 'future_date_of_loss' END,
CASE WHEN reported_date IS NOT NULL
AND TRY_CAST(reported_date AS DATE) IS NULL
THEN 'invalid_reported_date' END,
CASE WHEN TRY_CAST(reported_date AS DATE) IS NOT NULL
AND TRY_CAST(date_of_loss AS DATE) IS NOT NULL
AND TRY_CAST(reported_date AS DATE) < TRY_CAST(date_of_loss AS DATE)
THEN 'reported_before_loss' END,
CASE WHEN TRY_CAST(claimed_amount AS DECIMAL(18,2)) IS NULL
OR TRY_CAST(claimed_amount AS DECIMAL(18,2)) <= 0
THEN 'invalid_claimed_amount' END,
CASE WHEN min_required_confidence IS NULL
OR min_required_confidence < 0.75
THEN 'low_or_missing_confidence' END,
CASE WHEN extraction_status = 'failed'
THEN 'extraction_failed' END
)) AS validation_flags
FROM claims.silver.extracted_claims
WHERE document_id =
'dbfs:/Volumes/main/claims_landing/raw_pdfs/batch-2024-09-16/claim-001.pdf';
TRY_CAST('2024-09-14' AS DATE) succeeds and returns a date in the past. TRY_CAST('2024-09-16' AS DATE) succeeds for reported_date, and September 16 is after September 14, so reported_before_loss does not fire. TRY_CAST(8400.00 AS DECIMAL(18,2)) succeeds and returns a positive value. The validation_flags array is empty. The record is approved automatically and is eligible for Gold promotion without human review.
Now consider a variation where the date of loss is recorded as September 18, 2024, two days after the reported date of September 16. The extraction step returns date_of_loss as "2024-09-18" with a confidence score of 0.93 - the model found a clear match, but the value is inconsistent with reported_date. The validation step evaluates TRY_CAST('2024-09-16' AS DATE) < TRY_CAST('2024-09-18' AS DATE) and raises reported_before_loss. The record goes to human review. Note that September 18, 2024 is a past date; future_date_of_loss does not apply here. The reviewer opens the source document, uses the citation bounding-box coordinates for date_of_loss to locate the relevant region on page 0, and determines whether the loss date or the reported date was entered incorrectly. That determination is recorded as a correction and revalidated before the record reaches Gold.
This is the value of the validation layer: the model extracted the value correctly from the document, but the value itself reflects an inconsistency in the source data. AI extraction cannot catch that. Deterministic rules can.
Validation status is derived from the flags array. Records with no flags are approved automatically. Records with flags on non-critical fields go to human review. Records missing required identifiers, with a failed extraction, or with an invalid amount are quarantined.
| Synthetic outcome | Flags | Status |
|---|---|---|
| CLM-2024-089341 | None | Approved |
| CLM-2024-089342 | low_or_missing_confidence, future_date_of_loss | Review required |
| CLM-2024-089343 | missing_claim_reference, extraction_failed | Quarantined |
Quarantining an extracted record means it cannot be promoted to Gold without human intervention. It does not mean the insurance claim itself is denied. The claim exists in the source system. The quarantine applies only to the extracted data record in the pipeline.
6. Human Review and Gold
Records that reach the review queue are not errors. They are records where the pipeline correctly identified uncertainty and deferred the decision to a human.
A reviewer works from three inputs: the original source document, the extracted field values, and the citation metadata that links each value back to a bounding-box region on a specific page. The review process for a record flagged with low_or_missing_confidence typically starts with the confidence scores. A score below the threshold on date_of_loss tells the reviewer that the model was uncertain about that field. The reviewer consults the source document, using the coord and page_id values from the matching citation entry to locate the relevant region. If the document is a low-resolution scan and the date is partially obscured, the reviewer can see that directly. If the extracted value is wrong, the reviewer supplies a corrected value.
A record flagged with reported_before_loss requires a different kind of investigation. The confidence scores for both date fields may be high - the model found clear matches - but the values are inconsistent with each other. The reviewer checks whether the loss date in the document is a typographical error, whether the document contains multiple dates and the model selected the wrong one, or whether the reported date itself was entered incorrectly. The citation entries for date_of_loss and reported_date each carry bounding-box coordinates that identify the source region the model used. If both dates appear on the same page, the reviewer can compare them side by side in the original document.
Corrections must be revalidated before promotion. A reviewer supplying an invalid date or a blank policy number does not bypass the validation rules - the corrected values go through the same deterministic checks. Records that fail revalidation are returned to the reviewer with the specific flags that caused the failure. This prevents a correction from introducing a new error that reaches Gold undetected.
Approved records - whether auto-approved or human-approved - are promoted to Gold via an idempotent MERGE keyed on the extraction attempt identifier. A rerun of the promotion step does not insert duplicate Gold records. Gold holds the claim reference, policy number, validated dates, normalized monetary amount, the source document identifier, the approval source, and the timestamp of promotion. Every Gold record is traceable to the document it came from and the attempt that produced it.
7. Production Requirements
The four code snippets in this article illustrate the core logic. A production implementation requires additional work that the snippets do not cover.
- •Persist original documents and raw AI responses. Store the source binary in Bronze and the full
ai_parse_documentandai_extractVARIANT responses in Silver. If a field value is questioned after Gold promotion, the raw response is the evidence. Deriving status columns and discarding the raw response removes that evidence. - •Retry handling and idempotent promotion. Each pipeline run should carry a run identifier. A document that fails parsing or extraction under one run ID can be retried under a new run ID without overwriting the prior attempt. The Gold MERGE should be keyed on the extraction attempt identifier so rerunning the promotion step does not insert duplicate records.
- •Review decisions and correction history. A production review queue should append each reviewer action to a history table rather than overwriting the prior decision in place. The pattern described in this article overwrites in place for simplicity. An append-only history is necessary for audit trails in regulated environments.
- •Access controls and retention for claimant information. Bronze holds binary document content that may include personally identifiable information. Apply Unity Catalog column masking, row filters, or table-level permissions appropriate to the sensitivity of claim data. Define and enforce retention policies for each layer independently.
- •Monitor failures, review rates, and usage costs.
ai_parse_documentandai_extractare billed per call. Track call volume, parse failure rates, extraction failure rates, and confidence score distributions by batch. A sudden increase in the review rate or a drop in average confidence score is a signal worth investigating before it reaches the review queue at scale. Set alerts on these metrics rather than discovering problems through downstream data quality issues.
These are requirements beyond the illustrative example, not optional enhancements. A pipeline that persists AI responses but has no access controls on claimant data, or that promotes records idempotently but has no review history, is not ready for regulated use.
8. Conclusion
The pattern in this article treats document ingestion as a governed delivery chain. Parsing, extraction, validation, and human review are separate stages with defined handoff conditions. Nothing reaches Gold by default. Every promotion is explicit, idempotent, and traceable.
The synthetic claim passes validation automatically because all five fields are present, all confidence scores exceed the threshold, and the dates are internally consistent. A variation with an inconsistent loss date goes to human review, where citation bounding-box coordinates guide the reviewer to the source region in the original document that produced the extracted value. That is the practical value of the pattern: it makes the basis for every data decision visible and correctable.
Before adapting this pattern, verify the ai_parse_document and ai_extract function signatures, schema format, and response paths against the official Databricks documentation for the runtime version in use. The code in this article is illustrative. Runtime validation in Databricks remains pending.
Official References
Continue Reading
Extending Databricks Lakehouse Industry Data Models for Property & Casualty Insurance
Explore how the Databricks Lakehouse Industry Data Models approach could be extended to Property & Casualty insurance, covering policy, coverage, risk, premium, claims, grain and AI-assisted data engineering.
Databricks Industry Data Models: From AI Coding to AI Architecture
Databricks' 23,000+ table Industry Data Models show how GenAI, metadata, deterministic validation, Unity Catalog and human governance could reshape enterprise data architecture.