Two paths to a trustworthy answer

The warehouse wasn’t the problem. Consolidating a sprawl of siloed warehouses into one modern platform was a real achievement. But it left two things behind: a schema of hundreds of tables, and a knowledge split nobody had designed away.

Two groups, each holding half the key. The data team knew the schema and could write the SQL, but didn’t hold the business questions. The business knew the questions and what the data meant, but couldn’t write a query and didn’t know the schema. So every real question became its own small project: requirements, design, development, testing, deployment. The wait ran to days or weeks. The old way couldn’t be fixed from inside itself.

What got it funded was the moment, not one stalled report. A top-down AI mandate had arrived: use this. What the data team did with it matters. Instead of chasing a flashy demo, they pointed AI at the constraint that had slowed the business for years: letting business users get answers in plain English, instead of waiting on the data team to write each report.

In healthcare, a confidently wrong number is not a small bug. Some answers get checked by the person asking. Others are expected to be right, and acted on without a second look. Those are not the same risk. Treating them the same is how trust quietly breaks down.

One honest note that shaped the build: during development we did not use patient data. The hard part was structural, not sensitive. Navigating a huge schema and resolving messy real-world values is difficult on its own, and none of it needed exposed data to work on. The real driver was a distinction: some answers a person will check, and some have to stand on their own. That is what shaped the main design decision.

One pipeline cannot be both open enough for exploring and tight enough for must-be-right answers. So we didn’t build one. We split the system by how much a wrong answer costs.

On the exploratory path, the user is going to check the result anyway, so wide freedom is safe. The human is the verifier. On the deterministic path, the answer has to be dependable, so we narrow what can be asked and add heavier guardrails. Same engine, two levels of trust.

Access was the most-debated question, and the right move was to not over-build it. We didn’t write a new permission system, and we didn’t centralise a decision that was really about ownership. We reused the identity platform the organisation already ran on (Microsoft Entra), so the request-and-approve flow was familiar. And we put the decision where it belonged: with the data stewards, the business owners responsible for each data domain. Business data, business owner decides who sees it.

Enterprise information security set the hard floor over everything: some information excluded outright, other fields anonymised, no matter who asked. Accountability for access stayed human, by design.

Off-the-shelf text-to-SQL tools quote impressive accuracy. That’s on toy datasets of a handful of tables. Point them at a real warehouse, with hundreds of tables, many fact tables, and many possible join paths, and those accuracy claims fall apart. The model alone can’t find its way through that. The real work was in what we fed it.

That last part is the unglamorous one that separates a demo from a system people trust. A user types a product name, a country, or a provider name or address. There are thousands of products in one lookup, hundreds of countries in another. The reference table maps what the user said to the value actually stored, so the filter is right, not just plausibly right.

An important point: at the time, a stronger model was available from another provider, but only outside our AWS environment. The warehouse already lived in AWS, with the security sign-off and the integrations in place. Using the outside model would have meant reopening all of that for a lead we expected to be short-lived. We judged the model gap to be temporary, and stayed. The models we could run caught up soon after.

Take a real exploratory question: “what is the sales figure for product X, in region Y, for period Z?” Follow it end to end. The user is already signed in. The server checks the token and the access before anything else happens.

Routing comes first. The two-path split isn’t a line in a design doc. It’s the actual front door. Classify the question, send it down the right path. Then the context layer assembles what the model needs, the model writes the SQL, and the query runs under least privilege (only the access the task needs): a secure channel, scoped credentials, read-only access to just the data in question. Because that path is scoped and read-only, a bad query can’t change data or reach beyond what it was allowed.

Two loops keep the system improving. If the engine rejects a query as malformed, the context layer and the model work together to fix it and try again. Be precise about what this catches: broken queries, not wrong-but-valid ones. A query that runs cleanly while answering the wrong question is what the path-split and the human check are for. The second loop is feedback: thumbs-up or thumbs-down, capturing the question, the generated SQL, and whether it was right. That feeds learning over time. Closing the loop is the step everyone skips, and here it’s built into the product.

The deterministic path is a different design, not a stricter version of the same one. Instead of open-ended SQL generation, it uses divide and conquer: keep sorting the question into narrower categories until it lands on a pre-written query meant to answer that category. The work shifts from improvising SQL to matching the question to a prepared one.

This is “AI proposes, human disposes,” built into the architecture. Where being wrong is unacceptable, the human-written query is the source of truth and the model is kept on a short leash. When a question falls outside the allowed set, the system doesn’t guess. It nudges the user back toward what it can answer dependably. A clear “I can’t answer that” beats a confident wrong answer.

One concrete example was the provider lookup. It’s really an entity-resolution problem (matching a messy input to the one right record): a name spelled a little differently, an address that’s almost but not quite, several addresses mapped to one provider. The fix was a specific prompt carrying a query example, plus a small workflow that tried alternatives when the first match failed. Fixed logic wrapped around the model, with a fallback ladder for the ambiguous cases.

Not SQL generation. The part that nearly sank the build was using the reference data correctly, at scale. Thousands of products in one lookup, hundreds of countries in another. Mapping a user’s loose wording to the exact stored value was the wall we didn’t see coming. It’s the same problem the provider lookup showed, multiplied across every field a question could filter on. Getting the joins right was part of it. Getting the value right was the part that caught the team off guard, and it’s where systems like this quietly fail.

Security pushed back first, and fairly. Once we laid out the IAM integration and the guardrails, those concerns settled. The analysts raised a different objection. They pointed at the cases where exploratory answers came back wrong.

We didn’t argue the objection away. We pointed to the design. The deterministic path was there so that anyone who needed a dependable answer didn’t have to rely on a generated one. It started as a design distinction, exploratory versus must-be-right, and it doubled as the answer to the analysts’ complaint. Meanwhile the learning loop made the exploratory path better with use, closing the gap from the other side.

At one point the work was handed to a vendor team. Their plan was to index the entire database into a vector store and build the experience on AWS Lex and Amplify. We shelved it. Indexing a structured warehouse into a vector store is the wrong shape for the problem. Text-to-SQL needs schema-aware generation, not semantic search over rows. The rest of the plan was inefficient, weak on security, and locked to one vendor.

This reached a limited working pilot. The aim was plain-English querying of the consolidated warehouse, with no SQL and no waiting in the report queue. The two paths balanced freedom against trust, and the feedback loop let the system improve with use. The deterministic path got to the point where extending it was pure configuration. Add one more question-and-query pair and the routing improves, with no code change. That’s a sign the design was clean.

We won’t dress that up with numbers we can’t stand behind. The honest report is the architecture and the judgement, not a glossy metric. The organisation’s AI strategy seemed to move from in-house build toward a large platform vendor, and our view ended there. That’s an honest place to stop.

Strip away the specifics and the build came down to a few durable ideas. These are the ones that outlast any single model choice.

• →Calibrate to stakes. Match rigour to the cost of being wrong. The two-path split is that idea made structural.
• →AI proposes, the prepared answer disposes. Where being wrong is unacceptable, the deterministic path leans on pre-written queries, not open-ended generation.
• →Treat the model as the swappable part. We judged the model gap temporary and didn't reopen a settled security boundary to chase it. The gap did close.
• →Close the feedback loop. Thumbs-up and thumbs-down were captured and fed back, so flagged answers sharpened the next ones.
• →Put the decision where it belongs. Access is a business-ownership decision, so we routed it to the data stewards instead of centralising it.
• →The unglamorous mile is the moat. Anyone can demo SQL generation. Resolving messy real-world values correctly is what earns trust.