Production Text-to-SQL Systems: Why Architecture Matters More Than Model

If you’re evaluating LLMs for analytics, you’re usually asking two questions at the same time:

1. Should we use RAG (Retrieval-Augmented Generation) or fine-tuning for SQL generation?
2. How do we ensure query accuracy improves over time instead of degrading?

The most important truth is also the least marketed: production text-to-SQL is an architecture problem, not a model-selection problem. On real enterprise schemas with business logic and ambiguous metric definitions, “one-shot prompt → SQL” is brittle. It can look great in a demo and quietly produce wrong dashboards in production.

The framing that causes teams to fail is: “RAG vs fine-tuning”

The framing that works is: “Which failure modes are we trying to solve, and what system design prevents them?”

In production analytics, RAG, semantic layers, fine-tuning, and feedback loops aren’t competing ideas. They’re different tools for different failure modes:

1. RAG grounds the model in the current schema and definitions.
2. Semantic layers reduce ambiguity and enforce governed metric logic.
3. Fine-tuning improves consistency and pattern learning when patterns are stable.
4. Feedback loops turn corrections and runtime signals into continuous improvement.

If you want a system that gets more accurate with usage, the winning pattern is:

Semantic layer + schema-aware retrieval (RAG) + evaluation + feedback loops

Optional: light fine-tuning for stable, repetitive query families.

Why SQL generation is harder than “text”

LLMs are excellent at language. Analytics is not a language problem. Text-to-SQL sits at the intersection of:

1. Business definitions (what “revenue” means here)
2. Schema correctness (tables/columns, types, constraints)
3. Join logic (relationship paths, grain alignment)
4. Time semantics (cohorts, SCD, “last month” definitions)
5. Governance (RBAC, row-level security, multi-tenant constraints)

A query that runs and returns results can still be semantically wrong. In BI, “confident but wrong” is worse than “can’t answer”. That’s why generic advice like few-shot prompting breaks down: you’re not optimizing for fluency. You’re optimizing for:

1. Execution correctness (does it run?)
2. Semantic correctness (does it answer the intended question?)
3. Consistency (does it behave reliably over time?)

Those goals require architecture.

The core decision framework (what to build first, and why)

Start with RAG when

RAG is your fastest path to credible production results when:

1. Schemas evolve (new tables, new columns, frequent changes)
2. Metrics are governed and change (definitions evolve)
3. You need a working system in weeks, not months
4. You can accept good-but-not-perfect v1 accuracy
5. You want changes in business logic to take effect without retraining

Add a semantic layer early when

This is where analytics teams usually win or lose:

1. You have 50+ tables, or multiple data domains
2. Terms are ambiguous: revenue, churn, MRR, active users, “conversion”
3. Multi-tenant usage means accuracy per tenant matters
4. You need permission-aware generation
5. You care about consistent definitions across dashboards, not just SQL that runs

Justify fine-tuning later when

Fine-tuning is powerful, but only under the right conditions:

1. Query patterns are stable for months
2. You have hundreds to thousands of validated examples
3. Your RAG+ semantic layer foundation is already working
4. You need consistent output style and fewer recurring reasoning errors
5. You have ML/ops capacity to manage training + evaluation + deployment

Invest in feedback loops once usage is real

Feedback loops are what turn “demo” into “product”:

1. You’re past pilot: tens of queries per week minimum
2. You can build an evaluation harness (golden set + scoring)
3. You can log context snapshots alongside outputs
4. You want accuracy to improve with usage, not drift

What RAG means for analytics

Most RAG definitions say “retrieve documents and inject them into the prompt”. For analytics, that’s incomplete.

RAG for text-to-SQL is grounding the model in trusted, schema-aware context.

Your retrieved “documents” are not PDFs, they are structured metadata and governed definitions:

1. Curated table/column descriptions (not raw information_schema dumps)
2. Join graphs and relationship constraints
3. Semantic model definitions: metrics, dimensions, grains
4. Temporal rules: canonical date fields, timezone conventions, cohort windows
5. Filter constraints: allowed filters, “safe” defaults, permission rules

The goal is to reduce guessing. Without retrieval, the model invents. With schema-aware retrieval, the model references documented truth.

What not to do with RAG for SQL

1. Don’t dump entire schemas into context
2. Don’t rely on embeddings over raw table names only
3. Don’t omit join metadata and grain
4. Don’t ignore temporal semantics
5. Don’t skip permissions and governance constraints

What to do instead

1. Retrieve a curated subset of relevant tables/metrics (often 10–50 items)
2. Include explicit join paths and grain alignment notes
3. Provide canonical metric definitions and allowed dimensions
4. Add permission constraints (tenant filters, RBAC, sensitive columns)
5. Provide temporal “rules of the road” (what “last month” means here)

Why RAG is powerful for text-to-SQL

1) Schema awareness without retraining

RAG lets the model “see” what exists now:

1. Tables, Columns
2. Relationship Paths
3. Constraints and Naming Conventions

When schemas change, you update metadata and retrieval indexes, not model weights.

2) Metric governance and consistency

Most wrong queries aren’t SQL syntax failures, they’re definition failures.

If “Revenue” has a canonical definition, RAG + semantic layer can enforce it:

1. Revenue = completed orders only
2. MRR excludes trials and adjusts for mid-month plan changes
3. Active user has a specific activity event definition

This prevents silent re-interpretation.

3) Lower cost and faster iteration

1. Update a metric definition: minutes
2. Update retrieval indexing: minutes to hours
3. Retrain a tuned model: hours plus validation and rollout risk

For fast-moving teams, RAG is operationally aligned with how analytics evolves.

Where RAG breaks down (and why people overpromise)

RAG grounds the model. It does not automatically teach better reasoning.

RAG struggles when:

1. Complex multi-step reasoning is required (nested windows, advanced cohort logic)
2. The same reasoning mistake repeats despite correct context
3. You need strict formatting / output structure
4. You need performance-aware query construction (e.g., using materializations)

The core limitation: RAG gives the model what to reference, not how to think.

That’s where fine-tuning helps.

What fine-tuning actually does for SQL

Fine-tuning changes model behavior by teaching patterns:

1. How your organization writes SQL (style, idioms, preferred join strategies)
2. Common table selection patterns for business questions
3. Stable metric and cohort logic patterns
4. Consistent formatting and structure
5. Reduced repeated failure modes for known query families

Fine-tuning works best when the domain is stable and your examples are high quality.

When fine-tuning works well

1. Stable schemas and metric logic exist
2. High-quality labels (validated SQL, not guessed)
3. Repetitive query classes (e.g., the same analysis across regions/segments)
4. Clear success criteria and evaluation harness

The real downsides (the operational truth)

Fine-tuning is:

1. expensive (data curation, training, validation, hosting)
2. slower to iterate than prompts/metadata
3. brittle to schema changes (weights learn old patterns)
4. a drift risk if not monitored

Once you fine-tune, schema/definition changes become release events unless you keep RAG + semantic grounding strong.

Practical principle:

Treat fine-tuning as an accelerator for stable patterns, not a replacement for grounding and governance.

Semantic layer: the missing “truth layer” in most LLM BI

A semantic layer is the system’s canonical definition of:

1. metrics
2. dimensions
3. grains
4. join paths
5. allowed filters
6. permissions and tenant rules
7. time semantics

In production text-to-SQL, the semantic layer reduces ambiguity and increases correctness more reliably than any prompt trick.

Why it matters

When a user asks:

“What was churn last month by plan?”

The model must know:

1. churn definition (logo vs revenue churn, cohort rules)
2. the correct time window (calendar vs trailing)
3. where plan lives (subscription table? product dimension?)
4. grain alignment and allowed joins
5. permission constraints

If those definitions aren’t explicit, the model guesses. Your “accuracy” becomes a marketing number, not a reliable system.

What “semantic layer + RAG” looks like in practice

1. intent parsed
2. retrieval pulls relevant metrics + dimensions + join paths
3. SQL planned using governed definitions
4. SQL generated, then validated

This is why most serious systems converge toward semantic grounding.

Databrain’s Semantic Layer:

• Create Datamart
• Semantic Layer
• Chat Mode

Feedback loops

RAG and fine-tuning influence how the model generates. Feedback loops define how it improves over time.

Feedback signals in analytics include:

1. analyst correction to SQL
2. rejected result (“this doesn’t match expectations”)
3. filter adjustments
4. join removed or replaced
5. “metric override” events (definition gap)
6. runtime errors and execution feedback

If you don’t capture and use these signals, the system repeats mistakes indefinitely.

A pragmatic feedback loop architecture

Log the entire interaction as a context snapshot:

1. question
2. retrieved context (tables/metrics/join paths)
3. generated SQL
4. execution outcome (success/error)
5. corrected SQL (if any)
6. semantic outcome (accepted/rejected)
7. metadata (timestamp, tenant, permissions, risk tier)

Then route the signals into three improvement channels:

Immediate (weekly): prompt + retrieval tuning

1. common failure patterns
2. missing join paths or ambiguous definitions
3. retrieval over/under-inclusion

Medium-term (monthly): retrieval ranking optimization

1. reweight indices based on what context was actually needed
2. improve schema linking and synonym dictionaries
3. adjust chunking strategy for definitions

Long-term (quarterly+): fine-tuning dataset curation

1. only high-confidence, validated examples
2. focus on stable query families
3. treat tuning as optional and measured

Execution-guided feedback

One of the most practical signals in SQL generation is objective:

1. Did the query run?
2. Did it error?
3. Did it return empty results?
4. Did it violate constraints?

Execution feedback enables iterative correction loops:

1. generate → execute → capture error → regenerate with error context

This doesn’t solve semantic correctness by itself, but it dramatically improves robustness and reduces failure modes like invalid columns or wrong function usage.

The modern production architecture

The “one-shot SQL” era is fading. The modern pattern is multi-step:

1. intent parsing (what is being asked?)
2. schema linking + retrieval (what is relevant?)
3. planning (joins, grains, metrics)
4. SQL generation
5. static checks (permissions, risky patterns)
6. execution (safe, read-only)
7. correction loops (if error)
8. semantic validation gates (accept/reject)
9. feedback logging

This is the architecture that aligns with enterprise BI requirements: correctness, governance, and continuous improvement.

A practical comparison table

‍

Key insight: Most teams should prioritize semantic layer + RAG + evaluation + feedback, then use fine-tuning surgically.

A 3-stage implementation roadmap

Stage 1: Foundation (weeks 1–6)

Goal: get to a credible Minimal Viable Product

1. Define core semantic layer (top metrics/dimensions)
2. Implement schema-aware retrieval (start simple, then iterate)
3. Build logging + evaluation harness (golden set of queries)
4. Put guardrails in place (read-only, row limits, approval gates)

Stage 2: Hardening (months 2–4)

Goal: reduce failures and prevent damage

1. Add static analysis checks (invalid joins, risky patterns)
2. Add execution-guided correction loops
3. Add semantic validation gates for high-risk queries
4. Improve retrieval ranking and synonym mapping

Stage 3: Learning system (months 4–6+)

Goal: improve with usage

1. formal feedback taxonomy (error, correction, rejection)
2. weekly review and prompt/retrieval refinements
3. drift monitoring (accuracy trends, failure clusters)
4. optional: fine-tuning for stable query families

The text-to-SQL maturity model (what “good” looks like)

Stage 1: Prompt-only prototype

1. manual schema descriptions
2. full human review
3. low reliability

Stage 2: Semantic layer + RAG

1. grounded generation
2. selective review gates
3. credible production MVP

Stage 3: Feedback loops + evaluation

1. continuous improvement
2. monitoring and drift detection
3. robust production system

Stage 4: Hybrid with light fine-tuning

1. targeted fine-tuning for stable patterns
2. strong orchestration and evaluation
3. mission-critical correctness at scale

Most real teams operate at Stage 2–3. That’s normal and sufficient for many production use cases.

Key takeaways

1. Start with semantic layer + RAG. It’s the fastest route to correctness and maintainability.
2. Fine-tuning is optional and conditional. Use it only when patterns are stable and you have validated examples.
3. Feedback loops are non-negotiable for production. Logging alone isn’t enough, use signals to improve retrieval and definitions.
4. Execution feedback is the most practical objective signal. It makes the system robust even before full semantic validation is mature.
5. Measure what matters. Track semantic correctness, not just “query ran”.
6. Architecture beats model choice. Strong governance with a solid baseline model beats a powerful model with weak grounding.