TLDR: AI-powered analytics tools use probabilistic systems (LLMs, semantic search, RAG) to answer business questions that demand deterministic accuracy. Ask the same question twice with slightly different wording, and you might get different SQL queries and different answers. This isn’t a minor bug - it’s a fundamental architectural problem. The solution isn’t better AI, it’s rethinking how we match business questions to data, using exact matching for core concepts instead of fuzzy semantic search.

---

Imagine asking your CFO: "How many claims did we deny in Q3?"

They pull up a dashboard and say "approximately 1,247, give or take."

You’d be concerned, right? When audit asks a question, "approximately" doesn’t cut it. It’s either 1,247 or it isn’t.

Now imagine that same CFO runs the question again tomorrow using slightly different words - "What’s the count of rejected claims last quarter?" - and gets 1,189.

You’d question the entire system.

Yet this is exactly how most AI-powered analytics tools work today.

The Probabilistic Foundation

Modern AI analytics stacks look something like this:

1. User asks a question in natural language
2. Semantic search finds relevant tables and columns from metadata
3. RAG pulls additional context about those data elements
4. An LLM generates a SQL query based on the retrieved information
5. Execute query, return results

Every step in this pipeline is probabilistic.

Semantic search uses embeddings to find "similar" content. The same question phrased differently produces different vector representations, which retrieve different metadata chunks.

RAG retrieval ranks results by relevance scores. Small changes in the query can shuffle the ranking, changing which context makes it into the LLM’s prompt.

LLM generation is fundamentally non-deterministic. Even with temperature set to 0, the same prompt can produce variations in SQL structure, table joins, or filter conditions.

This works fine for creative tasks. Writing marketing copy, brainstorming ideas, drafting emails - probabilistic is perfect for these use cases.

But business analytics isn’t creative work. It’s precision work.

When Approximation Fails

Let’s trace what happens with a real question:

Question: "What’s the average order value for premium customers last quarter?"

First attempt: Semantic search finds:

• fact_orders table (0.89 relevance)
• customer_tier column (0.87 relevance)
• order_total column (0.85 relevance)

LLM generates:

SELECT AVG(order_total)
FROM fact_orders
WHERE customer_tier = 'premium'
AND order_date >= '2024-07-01'

Result: $247.83

Second attempt (user rephrases): "What’s the mean order amount for our top-tier customers in Q3?"

Semantic search now finds:

• fact_orders table (0.88 relevance)
• customer_segment column (0.86 relevance) - different column!
• order_value column (0.84 relevance) - different column!

LLM generates:

SELECT AVG(order_value)
FROM fact_orders
WHERE customer_segment = 'gold'
AND order_date >= '2024-07-01'

Result: $231.56

Same question. Different columns. Different answer.

The user didn’t change what they were asking. But the probabilistic system interpreted it differently.

The Core Problem

The issue isn’t that the AI is "wrong." It’s that there’s no ground truth enforcement.

In the first query, how does the system know that "premium customers" maps to customer_tier = 'premium' and not customer_segment = 'gold'?

It doesn’t. It guesses based on semantic similarity.

Both mappings seem reasonable. Both are retrieved by the semantic search. The LLM makes a choice based on subtle factors in the retrieval ranking and its training data.

Change the wording slightly, change the retrieval ranking, change the choice.

This is the fundamental mismatch: using approximation engines to deliver deterministic outcomes.

How Humans Actually Work

Watch an experienced analyst tackle a question, and you’ll notice something important.

Given: "What’s the average order value and customer satisfaction rating for premium customers who made at least 3 purchases in high volume stores during peak hours last quarter? Break it down by product and region."

An analyst doesn’t treat every word equally. They immediately decompose it into structured components:

Metrics (what to measure):

• Average order value
• Customer satisfaction rating

Filters (what to include):

• Premium customers
• At least 3 purchases
• High volume stores
• Peak hours
• Last quarter

Dimensions (how to group):

• Product
• Region

Now here’s the key: they do exact lookups, not fuzzy searches.

They open their metrics catalog and search for "average order value" - exact match. Either it exists or it doesn’t.

They check their customer segments for "premium customers" - exact match. Either it’s defined or it isn’t.

They look up "high volume stores" in the business glossary - exact match.

No approximation. No "well, this seems similar enough." Either the concept is defined in the system, or they need to go ask someone what it means.

The Architecture That Works

The solution is to structure query generation around exact matching for business concepts.

Step 1: Parse the Question into Components

Use an LLM to break down the natural language question into structured parts:

{
"metrics": ["average order value", "customer satisfaction rating"],
"filters": [
"premium customers",
"at least 3 purchases",
"high volume stores",
"peak hours",
"last quarter"
],
"dimensions": ["product", "region"]
}

This step can be probabilistic - you’re just extracting the business concepts the user is asking about.

Step 2: Exact Match Against Your Metadata

Now search your metrics catalog for exact matches:

• "average order value" → FOUND: metrics.avg_order_value
• "customer satisfaction rating" → FOUND: metrics.csat_score

Search your business glossary for filter definitions:

• "premium customers" → FOUND: customer_tier = 'premium'
• "high volume stores" → FOUND: store_id IN (SELECT store_id FROM dim_stores WHERE annual_revenue > 5000000)
• "peak hours" → FOUND: EXTRACT(HOUR FROM order_time) IN (10,11,12,13,17,18,19,20)

Search dimension tables:

• "product" → FOUND: dim_product.product_name
• "region" → FOUND: dim_geography.region_name

If any concept doesn’t have an exact match, you stop and tell the user: "I don’t have a definition for ‘at least 3 purchases’ in the system. Can you clarify, or should I use this interpretation?"

Step 3: Assemble the Query Deterministically

Now you have concrete metadata for each component. Pull the complete definition for each:

• metrics.avg_order_value includes: table, column, calculation logic, any required joins
• customer_tier = 'premium' includes: table, column, exact value, any prerequisites

Use an LLM to assemble these concrete pieces into SQL. But now the LLM isn’t guessing which tables or columns to use - it’s combining predefined building blocks.

SELECT
p.product_name,
g.region_name,
AVG(o.order_total) as avg_order_value,
AVG(o.satisfaction_score) as avg_csat
FROM fact_orders o
JOIN dim_customer c ON o.customer_id = c.customer_id
JOIN dim_product p ON o.product_id = p.product_id
JOIN dim_geography g ON o.store_id = g.store_id
WHERE c.customer_tier = 'premium'
AND o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
AND o.store_id IN (SELECT store_id FROM dim_stores WHERE annual_revenue > 5000000)
AND EXTRACT(HOUR FROM o.order_time) IN (10,11,12,13,17,18,19,20)
GROUP BY p.product_name, g.region_name

Same question asked twice → same metadata retrieved → same query generated.

Deterministic.

What This Requires

This architecture has prerequisites:

You need a metrics catalog. Every KPI, every metric, every measure your business uses - defined precisely, stored in a queryable system.

You need a business glossary. Every business term, every filter condition, every segment - with exact definitions and the logic to implement them.

You need dimension mapping. Clear relationships between business concepts and data model entities.

In other words, you need the 80% solved (see the previous article on this). You need the business context documented and structured.

But once you have that foundation, this architecture works.

The Hybrid Approach

Pure exact matching is rigid. What if a user asks about "VIP customers" when your system calls them "premium customers"?

The answer is a hybrid approach:

1. Try exact matching first
2. If no exact match, do fuzzy matching and ask for confirmation
3. Learn from confirmations to expand your synonym mappings

"I don’t have a definition for ‘VIP customers’. Did you mean ‘premium customers’ or ‘enterprise customers’?"

User confirms. System logs that "VIP customers" → "premium customers" for this user. Next time, exact match succeeds.

Over time, the system learns the vocabulary variations while maintaining deterministic query generation.

Why This Matters

The difference between "approximately right" and "exactly right" is everything in business analytics.

Investors expect precise numbers. Audits demand accurate counts. Regulatory reports have zero tolerance for approximation. Strategic decisions need reliable data.

A system that gives you different answers to the same question isn’t useful. It’s dangerous. It erodes trust in data.

The current approach - throw everything into RAG and hope the LLM figures it out - works for demos. It fails in production where precision matters.

The Path Forward

If you’re building or buying AI analytics tools, ask:

How do you ensure deterministic query generation?

If the answer is "better prompts" or "fine-tuned models" or "improved embeddings," that’s not enough. Those are incremental improvements to a fundamentally probabilistic system.

Look for:

• Exact matching on business concepts
• Structured metadata catalogs
• Clear decomposition of questions into components
• Explicit handling of ambiguity (asking users instead of guessing)

What happens when I ask the same question twice?

If you get different SQL queries, that’s a red flag. The system should be stable and predictable.

What happens when a concept isn’t defined?

The system should admit it doesn’t know rather than approximate. "I don’t have a definition for X" is better than silently using the wrong definition.

The Bottom Line

Probabilistic AI is powerful for creative tasks. But business analytics isn’t creative - it’s precision engineering.

You can’t build a deterministic system on a purely probabilistic foundation. Semantic search and RAG are tools, but they can’t be the entire architecture.

The solution is to use exact matching where precision matters (business concepts, metrics, filters) and use AI where flexibility helps (parsing questions, assembling queries, explaining results).

Get the architecture right, and AI analytics becomes reliable. Get it wrong, and you build an impressive demo that nobody trusts in production.

The choice is yours.

---

Key Takeaways:

• Business analytics demands deterministic accuracy, but most AI systems are fundamentally probabilistic
• Semantic search and RAG can return different results for the same question phrased differently
• Human analysts use exact matching for business concepts, not fuzzy approximation
• The solution: parse questions into components, exact-match against metadata, then assemble queries
• This requires well-documented business context (metrics catalogs, business glossaries)
• Hybrid approaches can handle vocabulary variation while maintaining deterministic core behavior
• The right architecture uses AI for flexibility while ensuring reproducible, accurate results