The secret sauce to building specialized AI agents

There are two ways to make an AI agent an expert in your domain. The first way is fine-tuning: you train the model further on your own data, so your domain knowledge moves into its weights. The second way is retrieval: you keep a general-purpose model, and you hand it the right knowledge at the exact moment it needs it.

Fine-tuning looks attractive at first. But an analytics stack is a moving target. The schema changes every sprint. Definitions get renamed. New values land in columns every week. A fine-tuned model falls behind every one of those changes, and when it answers, it cannot show where the answer came from.

So in this post, we will take the second way and push it all the way. We will start with an agent that guesses, watch it fail, and fix one failure at a time. By the end, a general-purpose model will answer like an analyst who has worked at our company for years. And nothing here depends on SQL. The same approach works on a graph database, a search index, or anything else you can query.

The demo that lied.

Let's say we run a commerce company, and we built an agent over the warehouse. A manager asks:

“Top five product families by net revenue, last quarter.”

The agent thinks for two seconds and produces this.

select p.category_name,
       sum(o.gross_amount) as revenue
from   orders o
join   products p on p.product_id = o.product_id
where  o.order_date >= :last_quarter_start
group  by 1
order  by 2 desc
limit  5;

The SQL runs. Five product families come back with numbers. The manager pastes them into a slide. Now let's look at what actually happened.

Net revenue became gross_amount. Refunds and discounts were never subtracted.

Product family became category_name, an old grouping the data team stopped maintaining last year.

Neither of these is a SQL mistake. The syntax is clean and the join is correct. Both mistakes happened one step earlier, when the agent decided which columns the manager's words point to. The model guessed. And guessing was all it could do, because it has read most of the public internet and none of our warehouse.

What is this question made of?

Before we fix anything, we need to understand the question itself. Every analytics question is built from a small set of parts, and each part needs a different kind of grounding. We call these parts roles.

the question “Top five product families by net revenue, last quarter.”

aggregation target “net revenue” a metric to compute. which column? which formula?
group by “product families” a field to bucket rows with. which one?
sort hint “top five” an order and a limit. easy once the metric is right.
time window “last quarter” two dates. plain calendar code, no AI needed.
filter none in this question a stored value inside a field. we will meet this one soon.
identifier none in this question an exact id like ORD-88231. used as-is, no lookup.

Fig. 01 — Six roles a question can carry. This one uses four of them.

In production, a small planning step does this decomposition before anything else runs. How that planner works deserves its own article, and it is on the way. For now, we will decompose by hand, because our lesson starts after the decomposition.

With the roles in front of us, we can point at the exact failure. The sort hint was easy. The time window was plain calendar math. The two roles that name schema things, the aggregation target and the group by, are the ones the agent guessed. It guessed because it had nothing to look up. So let's build the thing it should have looked up.

Teach it what your schema means.

For every metric and field that matters, we write one small record. The formal name for this collection is a semantic catalog. I think of it as the onboarding document our warehouse never had.

Here is the record for net revenue:

{
  "concept_id": "metric.net_revenue",
  "label": "Net Revenue",
  "description": "Revenue after discounts, credits, and
                  recognized refunds, in reporting currency.",
  "aliases": ["net sales", "realized revenue",
              "revenue after credits"],
  "usage_hint": "Use the governed net revenue measure.
                 Do not substitute gross order value.",
  "examples": ["net sales by month",
               "revenue after refunds"],
  "sources": [{
    "table": "analytics.orders",
    "column": "net_revenue_amount",
    "aggregation": "sum"
  }]
}

Let's read the three important fields slowly. The aliases are the words real people use for this metric. We collected them from the company glossary, from the labels on old BI dashboards, and from logs of what users actually typed. The usage_hint says, in plain words, what this metric must not be confused with. And sources points at the exact table and column, so a matched concept can become a query without any more guessing.

While collecting aliases, we learned something quickly: not every related word is a synonym. Some words mean the same thing. Some mean a bigger idea. Some mean a different thing that only sounds similar. The catalog should record those differences instead of piling every word into one list:

concept: net_revenue
aliases:                  # same thing, same scope
  - net sales
  - revenue after credits
broader:                  # the parent idea
  - revenue
adjacent:                 # related, NOT equivalent
  - gross merchandise value
  - bookings
forbidden:                # the common confusion, named
  - gross order amount

Look at the forbidden line. Our demo failed because net revenue silently became gross amount, and nothing in the system said those two are different. Now something does.

Next, we make these records searchable by meaning. An embedding is a list of numbers that captures the meaning of a piece of text. Two texts with similar meaning get numbers that sit close together. That is what will let “revenue after refunds” find net revenue even when no words overlap. For each concept, we stitch the label, meaning, examples, and aliases into one block of text, and we embed that:

Concept: Net Revenue

Meaning:
Revenue after discounts, credits, and recognized
refunds, in reporting currency.

User intent examples:
- net sales by month
- revenue after refunds

Aliases:
net sales, realized revenue, revenue after credits

Usage:
Use the governed net revenue measure.
Do not substitute gross order value.

One rule will save you pain later: the concept_id is the identity, never the text. Descriptions and aliases will keep improving. The ids must never change, because everything downstream hangs off them.

How much work is this? Our warehouse has hundreds of tables, but only about a hundred concepts anyone actually asks about. Writing these records took a couple of days with a domain expert. Aliases and usage hints are expensive to write, so treat them like source code: reviewed and versioned.

Attach the tool, rerun the query.

Now we give the agent one new tool, with one rule: never bind a metric or a field without calling it first.

resolve_concept(term, full_query)

resolve_concept("net revenue", "...")
→ {
    "concept_id": "metric.net_revenue",
    "table": "analytics.orders",
    "column": "net_revenue_amount",
    "aggregation": "sum",
    "confidence": "high"
  }

resolve_concept("product families", "...")
→ {
    "concept_id": "dimension.product_family",
    "table": "analytics.products",
    "column": "product_family_code",
    "confidence": "high"
  }

“Net revenue” lands on net_revenue_amount, because the alias list catches it. “Product families” lands on product_family_code. And the stale category_name column was never even a candidate, because we never wrote a record for it. The agent swaps in both bindings, reruns the query, and the manager finally gets the right five families with the right numbers.

Let's take stock. resolve_concept now grounds every role that names a schema thing: the aggregation target, the group by, and the sort hint. But look back at the roles figure. There is one role we have not tested yet: the filter. Let's give it one.

What about filters?

The next morning, the same manager asks a bigger question:

“Monthly net revenue for enterprise customers in the Gulf, excluding refunded orders.”

Decompose it like before, and the new shape is clear: the same metric, a new group by (month), and three filters. Enterprise customers. The Gulf. Refunded orders.

Our agent starts well. It calls resolve_concept and finds the right fields: segment_code for customer segments, billing_country_code for geography, order_status_code for order state. Then it writes:

where c.segment_code = 'Enterprise'

→ 0 rows

Zero rows. The warehouse stores 'ENT', and the manager said “enterprise customers”. Nothing we embedded connects those two strings. The other filters have the same problem. Which countries count as “the Gulf”? Is a refunded order 'REFUNDED', 'RFND', or a boolean flag? The agent is guessing again.

So now we can see the gap clearly. resolve_concept knows the fields of our schema. A filter needs the value stored inside the field. We taught the agent our fields. We never taught it our values.

Teach it what your values are called.

The fix starts with a query any warehouse can run:

select distinct segment_code
from analytics.customers
where segment_code is not null;

→ 'ENT', 'MM', 'SMB', 'STARTUP'

Four values. This is the part that surprises people, so let me say it slowly. The customers table can hold a hundred million rows, and this column still holds exactly four distinct values. When we embed values, we embed these four. We never embed the rows. That is why the index grows with the meaning of the schema, and never with the size of the data.

the data

analytics.orders

100,000,000 rows

the index

concept order_status what state an order is in right now
valueplaced
valueshipped
valuerefundedaliases: returned, money back, cancelled after payment
valuedisputedaliases: chargeback, contested

5 records. Not 100,000,000.

Fig. 02 — The index grows with meaning, never with data volume.

Each extracted value now gets the same treatment a concept got: a stable id, the exact stored form, and the words people actually use for it. Here is the record for the enterprise segment, together with the text we embed for it:

{
  "concept_id": "value.customer_segment.enterprise",
  "concept_key": "customer_segment",
  "canonical_value": "ENT",
  "aliases": ["enterprise", "enterprise customer",
              "large account", "strategic account"],
  "targets": [{
    "table": "analytics.customers",
    "column": "segment_code",
    "operator": "=",
    "value": "ENT"
  }]
}

Embedded text:
Value: Enterprise
Enterprise — customer accounts in the enterprise
commercial segment.
Aliases: enterprise customer, large account,
strategic account
Concept: governed customer segment

Group words get their own records too. “The Gulf” becomes value.sales_region.gulf_group, and that one record carries all six country codes. Users name the group far more often than they name its members.

One more rule keeps the index small. Columns like order ids, SKUs, and URLs hold millions of distinct values with no meaning to embed. Worse, embeddings of random ids can accidentally sit close together, which creates false matches. So we mark those families exact-match only: they stay searchable by keyword, and they never get embedded. In our project, a few hundred curated values got the full treatment, and over a hundred thousand long-tail values stayed exact-match only.

How do we search this index? With hybrid search: an exact-word search and a meaning search run side by side, and a reranker cleans up the ordering. These are well-solved problems, and the vector database companies have written excellent explainers, so I will not reteach them here:

In our code, all of that hides behind one function:

candidates = hybrid_search(term, index, top_k=50)

Why top_k=50? No deep reason. It is a generous guess. Remember this number, because we will come back to it soon.

The rerun, grounded.

Now our index holds two kinds of records. Concept records describe the fields. Value records describe what is stored inside the fields. So the agent gets a second tool, resolve_term, and every role in a question now has a clear home:

aggregation_target  → resolve_concept
group_by            → resolve_concept
sort_hint           → resolve_concept
filter              → resolve_term
identifier          → used directly, no lookup
time window         → plain calendar code

Let's rerun the Gulf question and watch the filters resolve:

resolve_term("enterprise customers", "...")
→ segment_code = 'ENT'

resolve_term("the Gulf", "...")
→ billing_country_code in
  ('AE','SA','BH','KW','OM','QA')

resolve_term("refunded orders", "...")
→ order_status_code = 'REFUNDED'  (negated by planner)

select date_trunc('month', o.order_date) as month,
       sum(o.net_revenue_amount)         as net_revenue
from   analytics.orders o
join   analytics.customers c using (customer_id)
where  c.segment_code = 'ENT'
  and  c.billing_country_code in
       ('AE','SA','BH','KW','OM','QA')
  and  o.order_status_code != 'REFUNDED'
group  by 1
order  by 1;

Count the literals the model invented in that query. Zero. The manager said “enterprise customers”, the catalog said 'ENT', and the engine copied it into the query. The model did not list the Gulf countries from memory either. The group record carried its six codes. Every value in this query has a receipt.

Too many good-looking matches.

At this point our agent answers both questions correctly, and honestly, we shipped a version that looked a lot like this. Then the questions got messier, and a problem surfaced inside that top_k=50.

Retrieval has one job: never miss the right answer. So when we ask it for “enterprise customers”, it returns everything that looks even close. Here is what actually comes back:

the term “enterprise customers”

Click a candidate and judge it the way an analyst would: by the rows it returns when you filter with it.

Fig. 03 — One term, three candidates. Each looks right until you imagine the rows.

A reranker improves the ordering of this pool, and you should use one. But even a perfect ordering leaves two questions unanswered.

First, the reranker scores the term against each candidate in isolation. Give it just the word “enterprise”, and it has no way to know the manager meant customer accounts and not software editions. The answer to that lives in the full sentence the manager asked, and the reranker never sees that sentence.

Second, and this is the mistake we actually shipped: an ordering does not tell you how many to keep. We started with a fixed cutoff, keep the top 5. Now look at what the right cutoff actually was on three real terms:

customer_segment.enterprisethe rows are enterprise customers. keep.
product_tier.enterprisethe rows are products. drop.
account_name.enterprise_holdingsone company with a matching name. drop.

right answer: keep 1

Fig. 04 — The right k was 1, then 3, then 0. No config value covers that.

The right k changes with every question.

Keep 5 when the right answer is 1, and four wrong candidates flow downstream. Keep 5 when the right answer is 0, and the agent confidently filters on something the user never meant. A fixed number cannot decide this. Deciding this needs something that can read the question and think about it.

The selector agent.

So we placed a small reasoning step right there, between retrieval and the query. We call it the selector. It is a second, much smaller model with one narrow job: read the retrieved candidates and decide which of them truly represent what the user asked. Instead of a fixed top-k, it keeps exactly the ones that fit, however many that turns out to be. Sometimes one. Sometimes three. Sometimes zero.

Ours is Gemma 3 12B, an open-weight model small enough to run on modest hardware. It does not need to be brilliant, because we hand it everything the reranker never had: the term, the full original question, and the candidates with their descriptions and stored values. For every candidate, it runs one test:

“If I filter by this, what rows come back? Are those rows what the user asked about?”

This test is why the selector catches what similarity scores cannot. Filter by product_tier.enterprise and the rows that come back are software editions. The question was about customers. No similarity score changes what the rows are.

The prompt we actually use.

Here is our selector's system prompt, lightly redacted. Notice that it teaches principles instead of listing rules. A rule written for one failure stops working the day the schema changes. A principle keeps working.

role: Schema Candidate Selector

goal: For each search term, decide which retrieved
candidates genuinely represent what the user is
asking about. Return only valid JSON.

PRINCIPLES

1. Executability over resemblance.
   Judge a candidate by the rows it returns when
   queried, not by how its text reads. If the rows
   answer the user, it fits. If they are about
   something else, it does not, no matter how
   strong the wording match.

2. The full question is ground truth.
   The isolated term is ambiguous. Word overlap is
   evidence; alignment with the user's intent is
   proof. When they disagree, intent wins.

3. A broad term may need many values.
   When the user names a class, all values that
   make up the class jointly represent it.
   Selecting one member silently shrinks the
   question.

4. Prefer group values within one concept_key.
   Candidates sharing a concept_key are values of
   the same concept; prefer the one covering the
   whole group the user named. Candidates from
   different concept_keys are independent and may
   both be selected.

5. Description keywords are hooks, not proof.
   Descriptions are written to be findable, so
   they mention words the field is merely near.
   Catching that gap is the core of this job.

6. Prefer inclusion under uncertainty.
   Downstream can reconcile an extra candidate.
   It cannot recover a missing one. Return empty
   only when confident nothing fits.

7. Stay within the list.
   Never select ids that are not in the
   candidates. If the right one seems absent,
   that absence is the answer: return empty.

OUTPUT
{ "selections": [ { "concept_id": "...",
    "confidence": "high|medium|low",
    "reason": "..." } ],
  "rejection_confidence": "high|medium|low" | null }

Two details around this prompt matter in practice. First, the selector has one small tool. When a candidate's description points at a concept it has not seen (ours say things like “for the commercial segment of an account, use customer_segment”), it may look up that one id before deciding. One lookup, and only for unfamiliar ids.

Second, we do not trust the prompt to enforce its own rule 7. The engine checks every returned id against the candidate list, in code (this article makes the full case for that division of duties):

allowed = {c.id for c in candidates}
kept = [s for s in output.selections if s.id in allowed]

And when the selector returns empty, we tell the user honestly instead of using the closest match:

{
  "unsupported_concept": "customer expiry date",
  "reason": "No matching field or value exists
             in the catalog."
}

“I cannot answer that from our data” costs a little pride. A confident number built on the closest match costs the team's trust in every number after it.

One prompt, or many?

One selector problem remains, and we found it in production. A real question produces several terms. Each term produces its own candidates. The lazy implementation collects all of them and sends them to the selector in one call. Fifty candidates, five unrelated concept families, one prompt.

Quality drops when you do this, and the reason is easy to see. Deciding about “the Gulf” means comparing the region candidates with each other, because choosing the group over the members is a comparison. Mix in segment candidates, status candidates, and product candidates, and the model now has to hold five unrelated decisions in its head at once. Its attention spreads thin, and every decision gets a little worse. This is true for a 12B model, and it is true for a frontier model too.

The opposite extreme also fails. Send each candidate alone, and the selector cannot compare anything. It would happily approve sales_region.saudi_arabia without ever seeing that sales_region.gulf_group was available.

Fan out by family.

The rule we landed on is simple: candidates that compete with each other must be judged together, and candidates that have nothing to do with each other should be judged separately. Candidates sharing a concept_key are competing readings of the same concept, so they go into one call. Candidates from different families are independent, so they run in parallel, in smaller and calmer prompts.

def selector_batches(candidates):
    by_key = group_by(candidates, "concept_key")
    rivals    = [g for g in by_key if len(g) > 1]
    singletons = [c for g in by_key if len(g) == 1
                    for c in g]
    return rivals + [singletons]

results = await asyncio.gather(*[
    ask_selector(term, full_query, batch)
    for batch in selector_batches(candidates)
])

For the Gulf question, the four region candidates form one batch, so the group-versus-members comparison stays intact. The unrelated singletons share another batch. The batches run at the same time, so the wall-clock cost is the slowest batch rather than the sum. The engine merges the results back in retrieval order and drops anything outside the allowed ids, exactly as before.

The machine you just built.

Let's step back and look at the whole thing at once:

offline · rerun on every refresh

warehouse schema
distinct values
human aliases + boundaries
semantic catalog
search indexconcepts + values

online · every question, in milliseconds

question
roles
hybrid search + rerankgenerous top-k
selector fan-outGemma 3 12B · exact k
bindings
query engine

Fig. 05 — The whole machine. Every box entered this post as the fix for a failure you watched.

the catalog     defines meaning
retrieval       protects recall
the selector    judges intent, and how many
the harness     enforces the rules
hydration       supplies canonical values
the engine      runs the query

Each stage has one job, and no stage trusts the one before it. The model never types a stored value. The engine never guesses a meaning. When something breaks, the seams tell you which piece failed. Measuring each seam properly deserves its own article, and it is coming.

And notice what we never did. We never fine-tuned anything. The expertise sits in the catalog, in the value records, and in one small judge with a principled prompt. The general-purpose model in the middle stays general-purpose. When a better model ships next year, it drops in and inherits all of it.

Your warehouse already knows the facts. The catalog writes down what your company's words mean. Hand both to a general-purpose model, give it a way to judge what it retrieves, and you have your specialist.