Adding aiextract to the mix: building a unified RAG pipeline with three

Use this as an operator-facing rebuild of the ideas in “Adding ai_extract to the mix: building a unified RAG pipeline with three Databricks SQL AI functions”: clear stages, ordered code slots, and recovery notes that survive a handoff. The Overview stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

Why metadata matters more than it looks

For the Why metadata matters more stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

What the three functions actually do

For the What the three functions stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

The pipeline, step by step

For the The pipeline step by stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap. For the The pipeline step by stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

Step 1: Parse documents

When working through the Step 1 Parse documents stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

SELECT
  path,
  ai_parse_document(
    content,
    MAP('version', '2.0')
  ) AS parsed
FROM READ_FILES(
  '/Volumes/<YOUR_CATALOG>/<YOUR_SCHEMA>/orion_docs/',
  format => 'binaryFile'
)

Step 2: Extract structured metadata with ai_extract v2.1

When working through the Step 2 Extract structured stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

WITH parsed_documents AS (
  SELECT path, ai_parse_document(content, MAP('version', '2.0')) AS parsed
  FROM READ_FILES('/Volumes/<YOUR_CATALOG>/<YOUR_SCHEMA>/orion_docs/', format => 'binaryFile')
)
SELECT
  path,
  ai_extract(
    parsed,
    '{
      "document_title":  {"type": "string", "description": "Main title or heading of the document"},
      "document_type":   {"type": "enum", "labels": ["manual", "datasheet", "guide", "report", "specification", "policy", "other"]},
      "product_name":    {"type": "string", "description": "Primary product or system referenced"},
      "version":         {"type": "string", "description": "Document or product version if mentioned"},
      "key_topics":      {"type": "array", "items": {"type": "string"}},
      "target_audience": {"type": "enum", "labels": ["engineer", "operator", "manager", "customer", "general"]}
    }',
    MAP(
      'version', '2.1',
      'instructions', 'These are technical documents from Orion Robotics. Extract metadata that would help users filter and find relevant documents.'
    )
  ) AS metadata
FROM parsed_documents

Step 3: Prepare semantic chunks with ai_prep_search()

When working through the Step 3 Prepare semantic stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

WITH parsed_documents AS ( ... )
SELECT
  path,
  ai_prep_search(parsed) AS chunked
FROM parsed_documents

When working through the Step 3 Prepare semantic stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

Step 4: Join metadata and chunks into a unified output

The Step 4 Join metadata stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Separate chunking policy from retrieval policy. Changing one should not force a rewrite of the other when quality metrics move.

WITH parsed_documents AS (
  SELECT path, ai_parse_document(content, MAP('version', '2.0')) AS parsed
  FROM READ_FILES('/Volumes/<YOUR_CATALOG>/<YOUR_SCHEMA>/orion_docs/', format => 'binaryFile')
),
doc_metadata AS (
  SELECT
    path,
    parsed,
    ai_extract(
      parsed,
      '{ ... }',
      MAP('version', '2.1', 'instructions', 'These are technical documents from Orion Robotics. Extract metadata for search filtering.')
    ) AS metadata
  FROM parsed_documents
),
doc_chunks AS (
  SELECT path, ai_prep_search(parsed) AS chunked
  FROM doc_metadata
)
SELECT
  chunk.value:chunk_id::STRING          AS chunk_id,
  chunk.value:chunk_position::INT       AS chunk_position,
  chunk.value:chunk_to_retrieve::STRING AS chunk_to_retrieve,
  chunk.value:chunk_to_embed::STRING    AS chunk_to_embed,
  doc_chunks.chunked:document.source_uri::STRING AS source_uri,
  dm.metadata:response:document_title::STRING    AS document_title,
  dm.metadata:response:document_type::STRING     AS document_type,
  dm.metadata:response:product_name::STRING      AS product_name,
  dm.metadata:response:version::STRING           AS doc_version,
  dm.metadata:response:target_audience::STRING   AS target_audience
FROM
  doc_chunks
  JOIN doc_metadata dm ON doc_chunks.path = dm.path,
  LATERAL variant_explode(doc_chunks.chunked:document.contents) AS chunk

Step 5: Persist to Delta and index

The Step 5 Persist to stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Separate chunking policy from retrieval policy. Changing one should not force a rewrite of the other when quality metrics move.

CREATE OR REPLACE TABLE <YOUR_CATALOG>.<YOUR_SCHEMA>.orion_docs_rag_chunks AS
-- [full CTE chain from Step 4]

Why this design works for agents

The Why this design works stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish. Separate chunking policy from retrieval policy. Changing one should not force a rewrite of the other when quality metrics move. The Why this design works stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

Honest limitations

For the Honest limitations stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

Final thoughts

For the Final thoughts stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

Resources

For the Resources stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

Operational checklist

When working through the Operational checklist stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest.

Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline.

Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

Pin dependency versions and record the image digest that ran the demo. Reproducibility beats tribal knowledge.

Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

Before promoting the stack, freeze versions, capture a golden transcript for the critical path, and confirm rollback steps. Shared environments need rate limits, tenancy checks, and a clear owner for secret rotation. Prefer boring reliability over clever one-off demos.

Batch note for 7a3f8fc3e730: keep provider keys out of the repo, set a per-session token ceiling, and store transcripts next to the eval fixtures so later model swaps stay comparable.