How We Built a Reliable AI Query Pipeline with Guardrails

• Every company has data. The tough part is getting answers from data.
• A project manager might want to know how many billable hours were logged in a particular project last month. Leadership teams want to see delivery trends before an important review meeting. The data already exists. The challenge is getting to it.
• In most organizations, answering such questions/queries still means reaching out to the data team. And then, someone from the data team will have to comprehend the question, run the SQL queries, validate the numbers, and return the results. Even a straightforward query could end up in several conversations just to get the final number to the user.
• We kept coming back to one simple question – Why should anyone wait for an answer when the data is right there in the database?
• That question eventually became AIRA, our Analytics Intelligence and Reporting Assistant.
• At first glance, building something like AIRA sounds straightforward. Give an LLM access to your database and let it answer questions, but the reality is very different.
• Business data is not like general knowledge. Every metric has a definition. Every project has relationships with other tables. Every calculation follows business rules. If an AI assistant misunderstands even one part of a requested query, the answer may look convincing and believable while being completely false.
• That is why we never wanted AIRA to be “an AI that writes SQL.” We wanted to build an AI Query Pipeline that understands natural language while ensuring every answer comes from governed business data.
• In this blog, we will walk through the engineering decisions that helped us build that system and why we believe the pipeline around an LLM matters far more than the LLM itself.

• Let’s start with a simple question.
  “Show me the billable hours by project for the last quarter.”
• To a user, it feels like a single question. Behind the scenes, it is anything but a single question.
• Before any data is returned, the system needs to answer several smaller questions.
  • Who is asking this question?
  • Has a similar question already been answered?
  • What metric does “billable hours” refer to?
  • What does “last quarter” mean?
  • Does the requested metric actually exist?
  • Which projects should be included?
  • Can the request be represented using the company’s business definitions?
• Only after answering all of these questions can the system retrieve data. That sequence of steps is what we call an AI query pipeline.
• The language model is only responsible for understanding the user’s language and intent. Everything else, including validation, entity resolution, business logic, execution, and formatting, happens outside the model.
• This separation turned out to be one of the biggest reasons AIRA performs reliably.

• Most AI-powered analytics demonstrations look impressive.
  • You ask a question.
  • The model generates SQL.
  • The database returns results.
  • The answer appears on the screen.
• The problem is that production AI pipelines are much less forgiving than demonstrations.
• Real users rarely ask questions using the exact names stored in a database. Business metrics often have strict definitions. Sometimes users ask incomplete questions, and sometimes they ask for metrics that do not exist.
• If an LLM is responsible for both understanding the question and generating SQL, even a small misunderstanding can produce incorrect answers.
• For an analytics platform, “almost correct” is still incorrect. We quickly realized that accuracy would never come from writing better prompts alone. It would come from designing a pipeline that verifies every important decision before any data is queried.

• Our motivation came from a problem we saw every day. Project managers and leadership teams constantly needed insights from operational data, but getting those answers almost always depended on the data team. The process was repetitive.
• We wanted to remove that dependency without compromising accuracy.
• The aim was straightforward. Anybody in the organization should be able to ask a business question in plain English and get an answer that they can rely on. Not an approximation. Not an AI-generated response. A response based on governed business data.
• That became the foundation for every decision we made while building AIRA.

• AIRA follows a single deterministic AI workflow architecture where every component has one responsibility.
• Before involving the language model, AIRA checks its semantic cache. We generate an embedding for the incoming query and search Redis for semantically similar requests within the same user and conversation. If a confident match exists, the stored response is returned immediately, reducing both latency and model usage. If no match is found, the request enters the interpretation stage. This is the only point where an LLM is involved.
• Using DSPy and LiteLLM, AIRA extracts structured information such as the metric, aggregation, grouping, filters, entities, and time range. The model does not generate SQL, decide joins, or access the database. Its role is limited to understanding what the user is asking.
• From that point onward, the pipeline becomes entirely deterministic.
• Every extracted value is validated against YAML-based semantic catalogs. Unknown metrics, unsupported dimensions, or missing required information are rejected before execution.
• When users refer to specific projects or people, AIRA resolves them using a combination of PostgreSQL with pgvector, embedding-based retrieval, keyword search, Reciprocal Rank Fusion, and a cross-encoder reranker. Only high-confidence matches are accepted.
• The validated request is then converted into an mf query command. MetricFlow loads the dbt semantic layer, generates optimized SQL internally, executes it against PostgreSQL, and returns structured results.
• Each component solves one problem and passes structured information to the next. That modular design keeps the system predictable and much easier to maintain.

• Despite the appearance that the pipeline is data pipeline architecture lengthy on paper, all components serve a purpose.
  • The semantic cache ensures that no effort is duplicated for already answered questions.
  • Static guardrails prevent prompt injection and other unsupported prompts from reaching the model.
  • DSPy performs structured intent extraction rather than open-ended text generation.
  • Pydantic models validate every field returned by the LLM.
  • Semantic catalogs ensure that business definitions remain consistent across every query.
  • Entity resolution identifies the correct project or user even when people use abbreviations or informal names.
  • MetricFlow acts as the governed execution layer, generating SQL from the semantic model instead of relying on AI-generated queries.
  • Phoenix tracing captures every stage of the request, making it much easier to debug unexpected behaviour in production.
• Individually, these components solve small problems. Together, they create a pipeline that users can depend on.

• One decision influenced almost every part of AIRA. We deliberately limited what the LLM was allowed to do.
• Instead of asking it to generate SQL or make business decisions, we treated it as an interpreter. Everything after interpretation goes through AI response validation and is verified independently before execution.
  • Regex-based checks reject prompt injection attempts before they reach the model.
  • Semantic validation confirms that requested metrics and dimensions actually exist.
  • Required time ranges are enforced.
  • Unsupported combinations are rejected.
  • Requests that cannot be executed simply stop instead of producing uncertain answers.
• This approach made the system much more predictable.
• Rather than trying to eliminate hallucinations, we built a pipeline where hallucinations have very little opportunity to influence the final result.

• Interestingly, our biggest challenge was not choosing an LLM. It was helping the system understand how people naturally refer to business entities.
• Users rarely type the exact project names stored in the database. A project called Internal Migration Platform might simply be called migration project. Different teams often use different abbreviations for the same project.
• Early versions of AIRA struggled with these variations. Improving entity resolution became one of our biggest engineering efforts.
• We introduced better database indexing, PostgreSQL vector search using pgvector, keyword retrieval, Reciprocal Rank Fusion, and cross-encoder reranking to improve matching accuracy.
• The day AIRA consistently started identifying the correct project without manual intervention was probably the moment we felt the pipeline was coming together.

• Looking back, a few decisions made a significant difference.
  • We kept the LLM responsible only for language understanding.
  • We validated every extracted entity before execution.
  • We generated MetricFlow commands instead of SQL.
  • We treated caching, validation, execution, and observability as first-class components rather than optional improvements.
• Most importantly, every stage in the pipeline has one clearly defined responsibility. That simplicity has made the system easier to test, debug, and evolve over time.

• If we had to summarize our experience into a few learning pointers, they would be these.
  • Use LLMs where they are strongest, which is understanding natural language.
  • Use LLM guardrails and keep business logic outside the model whenever possible.
  • Validate everything before execution.
  • Build observability into the pipeline from the beginning.
  • Design the individual components to effectively solve specific problems rather than relying on one design to solve all problems.
• These principles may seem straightforward, but the combination of them creates a huge impact when actual users begin interacting with the system through natural language queries.

• One of the easiest mistakes is assuming a good demo means a production-ready system.
• Another is trusting AI-generated SQL without validating business rules.
• Many teams also underestimate entity resolution. Matching the way people naturally talk to the way data is stored is often much harder than expected.
• Finally, do not ignore monitoring and tracing. The more intelligent your pipeline becomes, the more important it is to understand exactly how every answer was produced.

Before deploying an AI analytics pipeline, ask yourself:

• Is every request authenticated?
• Can repeated queries be served from a semantic cache?
• Is the LLM limited to understanding language instead of executing business logic?
• Are metrics and dimensions validated against a semantic model?
• Can projects and users be resolved reliably?
• Is execution handled through a governed semantic layer?
• Can every stage of the pipeline be traced and explained?

If any answer is no, there is probably another opportunity to strengthen the system.

• When we started building AIRA, we thought choosing the right model would be the hardest part. It wasn’t. The real challenge was designing everything around the model.
• Reliable AI systems are rarely built by making the LLM smarter. They are built by reducing uncertainty everywhere else. The more deterministic the pipeline became, the more dependable the final answers became. That was probably our biggest takeaway from this project.

• When people see AIRA for the first time, they often notice the conversational interface. For us, the conversation is only the beginning. The real work happens behind the scenes.
• Caching avoids unnecessary work. The language model understands intent. Validation enforces business rules. Entity resolution identifies the correct data. MetricFlow translates business language into governed queries. Observability makes every step traceable. Each component plays a small role. Working together, they build an analytics assistant that powers trusted business insights, reports, and AI-powered dashboards without requiring users to understand databases, SQL, or semantic models.
• Building AIRA reinforced one idea more than anything else. The future of enterprise AI is not about giving language models more responsibility. It is about building an AI Query Pipeline with thoughtful engineering around them so every answer is grounded in trusted data. That is the direction we chose for AIRA, and it continues to shape how we build every new feature.