Why attaching question-SQL pairs to your semantic model pushes accuracy past the baseline
The Verified Query Repository: The Missing Piece for Text-to-SQL Accuracy
Why attaching question-SQL pairs to your semantic model pushes accuracy past the baseline
The Text-to-SQL Accuracy Ceiling
Text-to-SQL systems have made remarkable progress. With a well-designed semantic model, modern LLMs can translate natural language questions into SQL with 70–80% accuracy. But that last 20–30% is stubborn.
The system might generate syntactically correct SQL that doesn’t quite answer the question. Or it might miss subtle business logic. Or it might choose the wrong join path when multiple are valid.
The problem: LLMs are guessing. Even with rich semantic context, they’re inferring the correct SQL from descriptions and schemas. They don’t have concrete examples of “when users ask X, they mean Y.”
The Pattern That Works
There’s a pattern emerging that pushes accuracy significantly past the baseline:
Verified Questions and SQL attached to your semantic model.
The idea is simple:
- Take a question your users actually ask
- Write (or validate) the correct SQL for it
- Attach that pair to your semantic model
- When a similar question comes in, retrieve relevant examples for the model to reason from
Snowflake calls this the “Verified Query Repository” in Cortex Analyst. Microsoft has similar concepts in Fabric. But here’s the surprising part:
No open standard has made this a first-class concept yet.
The Open Semantic Interchange (OSI) spec has an ai_context block, but it’s just a freeform string. Every other major format has at best a description field. Verified question-SQL pairs don’t fit anywhere.
Why This Matters
Let me illustrate with an example.
Without verified examples:
User: "Top 5 providers by claims in California"
LLM thinks: "Okay, I need to:
- Join providers and claims
- Filter to California (but which column? state? region? zip?)
- Aggregate by provider
- Sort descending
- Limit to 5
Let me guess..."
With verified examples:
User: "Top 5 providers by claims in California"
Retrieved example (92% similar):
Q: "Top 10 providers by claims in Texas"
SQL: SELECT p.provider_name, COUNT(c.claim_id) as claim_count
FROM dim_provider p
JOIN fact_claim c ON p.provider_key = c.provider_key
WHERE p.state = 'Texas'
GROUP BY p.provider_name
ORDER BY claim_count DESC
LIMIT 10
LLM thinks: "I can adapt this pattern.
California instead of Texas, 5 instead of 10."
The second approach is:
- More accurate — concrete pattern to follow
- Faster — less inference required
- More consistent — same question types produce similar SQL
The Architecture
Here’s how to make verified queries a first-class concept:
1. Attach Queries to Semantic Models
# semantic_model/verified_queries/customer_metrics.yaml
verified_queries:
- id: vq_001
question: "Top 5 customers by revenue last quarter"
sql: |
SELECT c.customer_name, SUM(o.amount) as total_revenue
FROM dim_customer c
JOIN fact_orders o ON c.customer_key = o.customer_key
WHERE o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
AND o.order_date < DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY c.customer_name
ORDER BY total_revenue DESC
LIMIT 5
intent: ranking
entities: [dim_customer, fact_orders]
verified_by: "analytics_team"
verified_at: "2026-03-15"
- id: vq_002
question: "Monthly revenue trend"
sql: |
SELECT DATE_TRUNC('month', order_date) as month, SUM(amount) as revenue
FROM fact_orders
GROUP BY 1
ORDER BY 1
intent: trend
entities: [fact_orders]
2. Classify Question Intent
Not all questions are the same. Classifying intent helps retrieval:
3. Retrieve Similar Examples
When a new question arrives:
def retrieve_examples(question, semantic_model_id, top_k=3):
# 1. Classify intent
intent = classify_intent(question)
# 2. Extract entities mentioned
entities = extract_entity_hints(question)
# 3. Find similar verified queries
candidates = repository.list_queries(semantic_model_id)
scored = []
for candidate in candidates:
score = (
text_similarity(question, candidate.question) * 0.4 +
intent_match(intent, candidate.intent) * 0.2 +
entity_overlap(entities, candidate.entities) * 0.2 +
keyword_match(question, candidate.question) * 0.2
)
scored.append((candidate, score))
return sorted(scored, key=lambda x: x[1], reverse=True)[:top_k]
4. Inject Into Generation
def generate_sql(question, semantic_model):
# Retrieve relevant verified examples
examples = retrieve_examples(question, semantic_model.id)
# Build prompt with examples
prompt = f"""
Given this semantic model:
{semantic_model.to_context()}
Here are verified examples of similar questions:
{format_examples(examples)}
Generate SQL for: {question}
"""
return llm.generate(prompt)
The Feedback Loop
The real power comes from the feedback loop:
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Query │────►│ Usage │────►│ Candidate │
│ History │ │ Analysis │ │ Selection │
└──────────────┘ └──────────────┘ └──────────────┘
│
▼
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Verified │◄────│ Human │◄────│ Quality │
│ Repository │ │ Approval │ │ Filters │
└──────────────┘ └──────────────┘ └──────────────┘
Auto-Promotion Criteria
Automatically identify high-quality candidates:
- Frequency — Asked 10+ times
- User diversity — By 3+ different users
- Satisfaction — 80%+ positive feedback
- Stability — Consistent results over time
- Syntax valid — SQL parses correctly
The Promotion Pipeline
# Find promotion candidates
candidates = promoter.find_candidates(
semantic_model_id="sales_model",
min_frequency=10,
min_satisfaction=0.80
)
# Review and approve
for candidate in candidates:
print(f"Q: {candidate.question}")
print(f"SQL: {candidate.sql}")
print(f"Frequency: {candidate.frequency}, Satisfaction: {candidate.satisfaction_rate:.0%}")
if approve(candidate):
promoter.promote(candidate, verified_by="data_team")
Measuring Impact
Track these metrics to measure the impact of verified queries:
The time improvement comes from not having to re-generate on failures.
Implementation Considerations
1. Storage Format
Store verified queries in YAML alongside your semantic model:
semantic_model/
├── model.yaml
├── entities/
│ ├── dim_customer.yaml
│ └── fact_orders.yaml
├── metrics/
│ └── revenue.yaml
└── verified_queries/ ← New!
├── customer_queries.yaml
└── order_queries.yaml
This keeps everything version-controlled together.
2. Question Variations
Include common variations:
- id: vq_001
question: "Top 5 customers by revenue"
question_variations:
- "Best customers by revenue"
- "Highest revenue customers"
- "Top revenue customers"
- "Who are our top customers?"
sql: |
SELECT ...
3. Staleness Detection
Verified queries can become stale when:
- Schema changes
- Business logic changes
- Better patterns emerge
Track verification dates and flag old queries for review:
stale_queries = repo.list_queries(
semantic_model_id="sales_model",
verified_before=datetime.now() - timedelta(days=90)
)
4. Coverage Metrics
Track which entities and metrics have verified examples:
Verified Query Coverage Report
==============================
Entities:
✓ dim_customer 12 examples
✓ dim_product 8 examples
✓ fact_orders 15 examples
✗ dim_region 0 examples ← Gap!
Metrics:
✓ total_revenue 10 examples
✓ order_count 7 examples
✗ avg_order_value 0 examples ← Gap!
Intents:
✓ ranking 18 examples
✓ trend 12 examples
✗ correlation 0 examples ← Gap!
The Open Standard Question
Currently, no semantic layer standard supports this pattern natively:
This raises a question: Should we push for verified queries in existing standards, or define something new?
Our position: Make it a first-class concept with a standard schema. A freeform string isn’t enough. The schema should define:
- Question text and variations
- Verified SQL
- Intent classification
- Entity/metric references
- Verification metadata
- Usage tracking
Getting Started
Step 1: Identify High-Value Questions
Start with questions that:
- Are asked frequently
- Have clear, correct answers
- Represent common patterns
Step 2: Write Verified Pairs
For each question, write the correct SQL and classify:
- question: "Total revenue last month"
sql: |
SELECT SUM(amount) as total_revenue
FROM fact_orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
AND order_date < DATE_TRUNC('month', CURRENT_DATE)
intent: aggregation
entities: [fact_orders]
metrics: [total_revenue]
Step 3: Integrate with Generation
Add retrieval to your text-to-SQL pipeline:
# Before generation
examples = repository.retrieve_similar(question, model_id, top_k=3)
# Add to prompt
prompt = base_prompt + format_examples(examples)
# Generate
sql = llm.generate(prompt)
# Track usage
for example in examples:
repository.record_usage(example.query_id)
Step 4: Build the Feedback Loop
Capture feedback and auto-promote:
# After user interaction
if user_satisfied:
repository.record_feedback(query_id, positive=True)
# Weekly: promote high-quality candidates
candidates = promoter.find_candidates(model_id)
promoter.promote_candidates(candidates, approved_by="data_team")
Conclusion
The Verified Query Repository is the missing piece that pushes text-to-SQL accuracy from “good enough” to “production ready.”
Key takeaways:
- Attach question-SQL pairs to your semantic model — Not in a separate system
- Classify by intent — Ranking, trend, comparison, etc.
- Retrieve at generation time — Inject relevant examples into prompts
- Build a feedback loop — Auto-promote from usage patterns
- Track coverage — Know which areas lack examples
The pattern is simple. The impact is significant. And it’s time for open standards to make this a first-class concept.
This article describes the Verified Query Repository pattern implemented in MDDE (Metadata-Driven Data Engineering).