← Back to Registry Hub/Site Search/L1 · stage2_data_ingestion_pattern
⚡Flashcard Drill➔
L1S2 · Data & RetrievalS5 · Eval & LLMOpsVerified: 2026-11-17

Stage 2 · Semantic-Layer Grounded Text-to-SQL & Read-Only Sandbox Repair Pattern

Production analytics NL2SQL pattern grounding generation in governed semantic-layer metric definitions and schema RAG, validated through read-only EXPLAIN/sandbox execution loops with parameterized tenant isolation.

Best For

Enterprise self-serve analytics copilots over wide warehouses (100+ tables) where financial metrics (ARR, NRR, net retention) carry strict non-obvious SQL definitions.

Human-AI Boundary

Certified KPIs bind deterministically to governed Semantic Layer specs; every chart renders the compiled SQL lineage for human verification before executive sharing.

Anti-Patterns to Avoid

  • Stuffing raw DDL of 200 tables into the prompt context without dynamic table pruning or semantic metric definitions
  • Executing LLM-generated raw SQL directly against production transactional databases without read-only credentials, row-limit caps, or AST tenant WHERE-clause verification

Latency/Cost Profile & Eval Metrics

Schema retrieval (<60ms) + SQL generation (~800ms) + sandbox dry-run (<250ms) · Retrieved DDL/Few-shot token footprint vs max 2 retry repair iterations on SQL syntax errors

  • Execution Accuracy (EX) on Verified Business Slices: >= 92% result-set equivalence against analyst gold SQL (Compare normalized result tuples (ignoring column order aliases where equivalent) on domain golden sets)
  • Schema-Linking Recall & Zero DDL Leakage: 100% required tables/join keys recalled; 0 DML/unscoped queries executed (Assert AST parser blocks non-SELECT statements and injects mandatory tenant_id predicate)

💰 Business ROI Model & Evidence Level

production_practice

Compresses multi-day analyst ad-hoc ticket queues into sub-3-second governed self-serve queries while eliminating conflicting KPI calculations across departments.

🏗️ End-to-End Architecture Pipeline

Step 1: Semantic Layer + Value-Sketch Hybrid Retrieval
≤ 80msZero frontier tokens

Retrieve top-K certified metric definitions, pruned table schemas, and sampled categorical column literals via hybrid dense/BM25 search.

Step 2: SQL AST Tenant Guard, EXPLAIN Dry-Run & Bounded Repair
≤ 1200msCapped at max 2 repair rounds

Parse SQL AST via sqlglot to enforce read-only + tenant predicates, run EXPLAIN cost caps, and allow at most 2 compiler-error repair loops.

⚖️ Engineering Trade-Off & Decision Matrix

Semantic Layer Metric-Spec Compilation + Ad-hoc Fallback
When to Use: Analytics products where 70%+ questions hit governed KPIs alongside occasional ad-hoc slicing.
Trade-Off: Requires upfront metric modeling in dbt/Cube, reducing core KPI calculation drift near zero.
Pure Free-Form Schema-RAG + SQL Self-Correction
When to Use: Exploratory analysis over ad-hoc user CSV uploads with no pre-defined KPI catalog.
Trade-Off: Zero modeling setup overhead, though implicit join semantics require user SQL inspection.

🚨 Production Failure Modes & Signatures

  • Filter literal mismatch ('USA' vs 'United States' vs 'us') causing empty result sets silently misread as zero revenue
  • Cartesian product fan-out JOINs inflating SUM() metrics without grain-mismatch warnings

🎤 Interview Defense & System Design Deep-Dive Q&A

  1. Why is prompting an LLM with raw `CREATE TABLE` DDL insufficient for enterprise Text-to-SQL accuracy, and how do Semantic Layers + AST SQL guards solve both accuracy and multi-tenant security?
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.
🎯 Drill This Card Now➔

Bidirectional Taxonomy Links

Internal Tools & Primary Sources