How to Build an AI Data Platform Your Business Applications Can Search

Ask a chatbot wired to your production database "what was our match rate last week" and it will answer. It will pick a table that looks right, guess a join, maybe get the date filter wrong by one day, and hand you a number with the same confident tone whether it's correct or fabricated. Nobody told the model which table is authoritative, what a "match" actually means in your schema, or that the join it just picked silently double-counts rows. It didn't make a reasoning error — it never had the information to reason with in the first place.

That gap is why most first attempts at "AI search over our data" stall out after the demo. The fix isn't a better model or a longer prompt. It's ordering the build correctly: put a catalog, a set of relationships, and a semantic layer underneath the agent before you ever let it answer a question, so every response resolves to governed SQL or a verified query someone already checked — not a plausible-sounding guess. This post walks through a reference architecture we sketched for exactly that, layer by layer, plus the adapter pattern that lets business applications use it without ever touching the lake directly. Every application name below — Apex, Orbit, Quill — is a fictional stand-in; use them as placeholders for whatever your own matching tool, KPI dashboard, or chat surface happens to be called.

Why bolting a chatbot onto a database doesn't work

The instinct is understandable. You have a database, you have an LLM API key, and connecting the two takes an afternoon. The demo looks great — someone types a question in English, SQL comes back, a chart renders. Then it goes to a second team, someone asks a question about a table the model has never seen described anywhere, and the wheels come off: wrong join, wrong aggregation grain, a confident answer built from a column that means something different than its name suggests.

None of that is really a model problem. Large language models are good at pattern-matching a question onto SQL-shaped text when the table and column names are self-explanatory and the relationships are obvious. Production schemas are rarely either. A column called status might mean five different things across five tables; a "customer" join might need to go through two intermediate tables that nothing in the schema itself documents. A model asked to bridge that gap without help will bridge it with the same fluent confidence whether it's right or wrong, because fluency and correctness are not the same capability, and nothing in a bare schema teaches it the difference.

The fix looks unglamorous: catalog the assets, document the relationships, define the metrics once in a semantic layer, and only then give an agent tools to search across all of it. It's slower to ship than a weekend chatbot, and it's the only version that still works on the hundredth question instead of just the first three.

Build the boring layers before the agent

The core discipline in the architecture below is sequencing. Every layer exists to make the layer above it trustworthy, and the agent sits at the very top precisely because it depends on everything underneath having already been built. Skip a layer and the agent doesn't fail loudly — it fails by answering anyway, using whatever partial structure it can infer, which is the worst kind of failure because it's invisible until someone checks the number against the source of truth by hand.

Ingestion and the lakehouse: where facts live

Everything starts with getting heterogeneous sources — Excel and CSV drops, Postgres and MySQL replicas, Google Sheets and Docs, PDFs, REST APIs, and files a user uploads for one-off matching — into one typed shape. That's the job of a CanonicalType: a schema every stream normalizes into on the way in, regardless of what the source system called its columns. Typed ingestion is what lets a quality gate reject a bad row before it ever reaches a table an agent might later query.

from noddle_platform.ingestion import CanonicalType, Field, Source
from datetime import date


class MatchCandidate(CanonicalType):
    """Canonical shape every source stream normalizes into before it
    touches the lakehouse — Excel, Postgres, and the REST API all map
    into the same fields, whatever their native column names are."""

    record_id: str = Field(pii=False, description="Source system's own key")
    entity_name: str = Field(pii=True, synonyms=["party_name", "customer"])
    account_number: str = Field(pii=True)
    source_system: str = Field(description="apex | orbit | quill | manual_upload")
    as_of_date: date

    class Config:
        quality_gates = ["not_null:record_id", "not_null:account_number"]
        stream = Source.MULTI  # Excel/CSV, Postgres, and the REST API all feed this type

From there, ETL workflows move data through the lakehouse's four zones — raw, staging, intermediate, mart — on a scheduler, with quality gates between each hop. Raw keeps an unmodified copy of what arrived; staging applies the CanonicalType normalization; intermediate handles joins and dedup logic; mart is the analysis-ready shape everything downstream reads from. Delta Lake on object storage (MinIO in this reference architecture) gives every zone ACID writes and time travel, and Spark Connect lets transform jobs run against it without each job needing its own cluster. A separate PostgreSQL database holds metadata — workspace definitions, row-level security policies, multi-tenant isolation — deliberately kept apart from the lakehouse itself, so a tenant boundary is enforced by the database engine, not by application code that a future refactor could quietly break.

Catalog, relationships, and the semantic layer: where meaning lives

A lakehouse full of correctly-typed tables is necessary and not remotely sufficient. The catalog is what turns a list of tables into something searchable: every asset and column gets a name, a set of synonyms (so "customer" and "party" resolve to the same field), tags, and an entry in a glossary, all behind full-text search. Lineage sits next to it, tracing the graph from ingestion through every transform to whatever eventually gets exported — the answer to "where did this number come from" shouldn't require reading transform code.

Relationships — foreign keys, dimension tables, business links that aren't enforced at the database level but are true in practice — feed the semantic layer, which is the piece most bolted-on chatbots skip entirely. A semantic layer defines entities and metrics once, compiles them to governed SQL, and gives every consumer — dashboard, agent, analyst — the same definition of "match rate" instead of five slightly different ones living in five people's heads and five sets of ad-hoc queries. An ontology layer sits alongside it for the cases where a plain entity/metric model isn't enough structure — object types, typed links between them, and for financial concepts specifically, FIBO-style modeling that's already been through the work of defining what a "position" or a "counterparty" means in a standard way.

entity: matched_account
source_mart: mart.reconciliation
primary_key: record_id
dimensions:
  - source_system
  - as_of_date
metrics:
  match_rate:
    sql: "COUNT(*) FILTER (WHERE match_status = 'matched') / COUNT(*)::float"
    grain: source_system, as_of_date
  unmatched_count:
    sql: "COUNT(*) FILTER (WHERE match_status = 'unmatched')"
verified_queries:
  - question: "What's this week's match rate for the Apex feed?"
    sql: |
      SELECT as_of_date, match_rate
      FROM semantic.matched_account
      WHERE source_system = 'apex'
      ORDER BY as_of_date DESC
      LIMIT 1

Notice that match_rate is defined exactly once, in one file, and every question about match rate — whether it comes from a dashboard, an analyst's SQL client, or the agent — compiles down to that same definition. That's the entire point of a semantic layer: it converts "ask the model to guess the right aggregation" into "look up the aggregation that's already been agreed on."

Knowledge base and vector store: where retrieval lives

The knowledge base holds the material that doesn't fit neatly into rows and columns — documents, and critically, verified queries: SQL someone already wrote, checked, and approved as the correct answer to a recurring question. It's auto-indexed from the profiling step that already runs during ingestion, so it doesn't require a second manual documentation effort that will inevitably drift from the actual data. The vector store sits next to it — embeddings over that same material, combined with hybrid full-text search, built on infrastructure (pgvector-ready) that doesn't require standing up a separate vector database just to get semantic search working.

Dashboards round out this layer — standard BI on top of the same marts and metrics, with email schedules for the people who'd rather get a Monday-morning report than open a chat window. They read from the exact same semantic definitions the agent does, which is what keeps a dashboard number and an agent-reported number from silently diverging six months in.

The agent layer: gated tools, not free-form SQL

Once the layers above exist, the agent itself is almost anticlimactic — it's a small, fixed set of tools sitting on top of a lot of prior work. In this architecture the agent has roughly thirteen tools: read tools like search_catalog, get_entity, search_knowledge, and find_verified_queries, a constrained run_sql that only ever executes SELECT statements under workspace row-level security, and a handful of create_* write tools — new glossary terms, new verified queries, new dashboard schedules — that are confirm-gated rather than fired automatically.

Every one of those tool calls — read or write — routes through an AI gateway rather than talking to a model provider directly. The gateway is what makes providers swappable (LiteLLM in front of Anthropic and others, with fallback if a provider has an outage), and it's where the operational guardrails live: hard-cap budgets so a runaway loop can't rack up an unbounded bill, content and rate guardrails, and a full audit log of what was asked, which tool ran, and what came back. None of that lives in the agent's prompt, where it would be one bad instruction away from being ignored — it lives in infrastructure the agent has no way to route around.

READ_TOOLS = [
    "search_catalog",         # full-text over assets, columns, synonyms, tags
    "get_entity",             # resolve a semantic-layer entity by name
    "search_knowledge",       # hybrid FTS + vector over docs and profiles
    "find_verified_queries",  # reuse a previously-approved SQL answer
    "run_sql",                # SELECT-only, workspace RLS enforced
]

WRITE_TOOLS = [
    "create_glossary_term",
    "create_verified_query",
    "create_dashboard_schedule",
]


def call_tool(name: str, args: dict, user):
    if name in WRITE_TOOLS and not args.get("confirmed"):
        # Draft goes back to the human instead of executing — this is the
        # confirm gate, and it is not optional for any create_* tool.
        return {"status": "needs_confirmation", "draft": args}
    return dispatch(name, args, user)

The confirm gate on write tools is the single highest-leverage governance decision in this architecture. Read tools can be wrong in a way that's merely annoying — a bad search result, a query that returns nothing. Write tools can be wrong in a way that corrupts the glossary or ships a verified query that's subtly incorrect and then gets trusted by everyone downstream forever. A human in the loop for every write, no exceptions, is cheap insurance against exactly that.

Governance runs through every layer of this stack, not just the agent: workspace row-level security scopes every query to the right tenant, SQL execution is SELECT-only end to end, privacy modes and PII flags travel with a field from the moment it's typed in CanonicalType, and the human-in-the-loop draft review on writes is the same confirm gate shown above, just named from the governance side instead of the tool-dispatch side.

Keeping business applications decoupled: the adapter pattern

None of the above is useful if every business application ends up with its own bespoke, tightly-coupled connection straight into the lakehouse. That's how you get an app that breaks the moment someone renames a column three layers away from where the app team is looking. The fix is a thin adapter per application: it pulls what it needs from the platform using scoped credentials, on its own schedule, and writes the result into a local snapshot inside the application. The platform never reaches into an app to push data — the direction of initiative is always the adapter reaching out, never the reverse.

Scoped credentials matter as much as the pull direction does. An adapter's service account is granted read access to exactly the marts and semantic entities its application needs — nothing more — so a compromised or misconfigured adapter has a small, auditable blast radius instead of standing access to the entire lakehouse.

adapter: apex
mode: pull            # adapters pull; the platform never pushes into an app
schedule: "*/15 * * * *"
credentials:
  type: scoped_service_account
  workspace: apex-matching
  grants: [read:mart.reconciliation, read:semantic.matched_account]
snapshot:
  target: apex_app.local_match_candidates
  on_conflict: replace

This is what lets the platform evolve — add a source, refactor a mart, rename a staging table — without every downstream application needing a synchronized deploy. As long as the semantic layer's contract (the entity and metric names an adapter depends on) stays stable, everything underneath it is free to change. That contract, not the raw schema, is the actual interface between the platform team and every application team building on top of it.

Anti-patterns to avoid

• Skipping the semantic layer and querying marts directly from the agent. Without a compiled definition of what a metric means, the agent re-derives the aggregation from scratch on every question, and it will re-derive it differently on different days. A semantic layer isn't bureaucracy — it's the thing that makes two people (or two agent turns) asking the same question get the same answer.
• Giving the agent unscoped write access "to save a step." The confirm gate exists because a write tool that's wrong is far more expensive than a read tool that's wrong. Removing the gate to make a demo feel more autonomous trades a one-time convenience for an open-ended risk.
• Letting an application query the lake directly "just this once." It never stays once. The app now depends on a schema it doesn't own, and the platform team either freezes that schema forever or breaks the app on the next refactor — usually without anyone noticing until a report goes silently wrong.
• Treating the vector store as the only retrieval path. A hybrid of full-text and vector search is good for finding documents and verified queries. It is not a substitute for governed SQL when the question has a precise numeric answer — nearest-neighbor similarity is not the same operation as aggregation.

Worked example: wiring Apex end to end

Apex, again a fictional stand-in, is a record-matching and reconciliation tool: it takes two sets of records — say, a bank feed and an internal ledger — and tries to match them up, flagging what didn't match for someone to review. Here's how it sits on top of everything described above, from raw files to a number on a dashboard.

1. Sources. Apex's inputs are exactly the messy kind this architecture is built for: an Excel export from one system, a Postgres replica from another, and a REST API pull for a third-party data source. A user can also upload a one-off file directly for an ad-hoc match run.
2. Ingestion. All three streams normalize into the MatchCandidate CanonicalType from earlier in this post — same fields, same PII flags, same quality gates — regardless of which source they came from.
3. Lakehouse. ETL workflows carry the normalized records through raw, staging, and intermediate, resolving duplicate records and applying the matching logic, and land the result in mart.reconciliation.
4. Catalog and semantic layer. mart.reconciliation is cataloged with synonyms ("match," "reconciliation," "unmatched") so it's findable by name, and the matched_account entity from the YAML above compiles match_rate and unmatched_count into governed SQL anyone — dashboard, analyst, or agent — can call by name instead of re-deriving.
5. Agent. Someone asks the copilot, in Quill's chat surface, "what's this week's match rate for the Apex feed?" The agent calls find_verified_queries first, finds the exact query from the YAML above already sitting in the knowledge base, and runs it through the gateway's run_sql rather than composing a new query from scratch — the answer resolves to a query a human already checked, not a fresh guess.
6. Adapter. Separately, on its own 15-minute schedule, the Apex adapter pulls the latest unmatched candidates from semantic.matched_account using its scoped service account, and writes them into Apex's own local_match_candidates snapshot — the table the Apex application actually reads from when a reviewer opens the app to work through today's exceptions.

Nothing in that chain required the Apex application to know anything about Delta Lake, Spark, or the shape of the raw source files. It knows one thing: read from its own local snapshot table, which the adapter keeps current. Everything upstream of that snapshot is free to change without a coordinated release.

How this fails in practice

The chatbot with no semantic layer underneath it

Symptom: the agent answers every question fluently and confidently, and roughly one answer in five is subtly wrong — a join that double-counts, a date filter off by a period, a metric computed a different way than the dashboard computes the "same" number. Cause: there's no semantic layer, so the model is re-deriving the aggregation from raw tables on every question, and a large language model asked to guess a join will guess a plausible one whether or not it's correct. Fix: define the metric once in a semantic layer, compile it to SQL, and have the agent call that definition by name instead of composing new SQL against raw tables for questions that already have a governed answer.

The agent with unscoped write access

Symptom: a glossary term gets silently overwritten with an incorrect definition, or a "verified" query turns out to have never been verified by anyone — the agent created it directly. Cause: the create_* tools ran without a confirm gate, usually removed because someone wanted the agent to feel more autonomous in a demo. Fix: every write tool returns a draft for human approval before it executes, no exceptions — the entire value of a confirm gate comes from it having zero carve-outs.

Applications querying the lake directly

Symptom: a business application that was working fine breaks overnight with no application-level deploy — a report goes blank, a dashboard errors out — and the root cause turns out to be a column rename or a mart refactor three layers upstream that the app team never knew it depended on. Cause: at some point the app started querying the lakehouse directly instead of going through an adapter, usually to save time on a deadline, and nobody tracked that dependency anywhere the platform team could see it. Fix: route every application through its own adapter with scoped credentials, so the contract the app depends on is the semantic layer's stable entity names — not whatever the mart's internal schema happens to look like this quarter.

The vector store as the only retrieval path

Symptom: the agent reports a KPI number that's close to right but not exactly right, and it's not obvious why until someone traces the answer back to an embedding match against a similar-but-outdated document rather than a live query. Cause: the retrieval design treated the vector store as the source of truth for a numeric question instead of as a way to find the right verified query or glossary entry. Fix: use vector and hybrid search to locate the right SQL or document, then execute governed SQL for the actual number — never let a nearest-neighbor match stand in for an aggregation.

Trade-offs: what this architecture costs you

Semantic layer first vs. ship the chatbot first

Building the catalog, relationships, and semantic layer before the agent is slower to a demo — there's real work with no user-facing payoff for weeks. Shipping the chatbot first gets something into someone's hands immediately, and for a genuinely narrow, well-understood dataset that can be the right call. The trade-off shows up the moment the dataset grows past what one person can hold in their head: the chatbot-first path accumulates silent correctness debt that's expensive to untangle later, while the layer-first path pays its cost upfront and stays correct as the data grows.

One shared agent vs. per-application agents

A single agent sitting on top of the whole platform, used by Apex, Orbit, and Quill alike, means one gateway, one audit log, one place to tune guardrails — and it means every application inherits whatever tool surface the shared agent has, whether or not it needs all of it. Per-application agents can have a tighter, more relevant tool set for each use case, at the cost of duplicating gateway configuration, budget limits, and audit plumbing three times over. Most teams are better served starting shared and only splitting out a dedicated agent for an application with genuinely different risk tolerance — say, one that's customer-facing versus one that's internal-only.

Snapshot pull vs. live queries from the adapter

Adapters in this design pull on a schedule and write a snapshot, which means an application is reading data that's at most as stale as the pull interval — fine for a weekly KPI report, less fine for something that needs to reflect a change in the last thirty seconds. The alternative — an adapter that queries live on every request instead of maintaining a snapshot — trades that staleness for a live dependency on the platform's availability and latency, which is exactly the coupling the adapter pattern exists to avoid. Pick the pull interval per application based on how stale an answer that application can tolerate, not by default.

AI search platforms and Noddle Deck

Everything in this post — the layer ordering, the confirm gate, the adapter pattern — is the same discipline Noddle Deck applies to agent work generally: an agent is only as trustworthy as the structure underneath it, whether that structure is a semantic layer for a data platform or a well-scoped skill and tool surface for a coding agent. If you're setting up the data-engineering side of this kind of work, the data-engineer persona pack bundles skills and commands scoped to exactly this territory — ingestion schemas, transform review, catalog hygiene — the same way this post walked through it.

noddle-deck pack install data-engineer

Browse the full set of packs at /packs if a different role fits better — every pack installs into ~/.claude/skills/ and ~/.claude/commands/, ready for the next Claude Code session, and FREE tier installs everything with no pack-level gating.

This post sits in the same series as the agent harness post (the loop this agent runs inside), the MCP post (a standard way to expose tools like search_catalog to a model in the first place), and the AI architect role post (the job of deciding where a boundary like the adapter pattern belongs). Worth reading together if this is the first post of the three you've landed on.

References

• Delta Lake — the open table format behind the lakehouse's raw → staging → intermediate → mart zones.
• MinIO — S3-compatible object storage the lakehouse runs on.
• pgvector — the Postgres extension that makes a hybrid FTS + vector store practical without standing up separate infrastructure.
• LiteLLM — the provider-abstraction layer the AI gateway sits on, for fallback across model providers.
• FIBO (Financial Industry Business Ontology) — a standard reference for modeling financial concepts in an ontology layer instead of inventing definitions from scratch.