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_practiceCompresses 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
Retrieve top-K certified metric definitions, pruned table schemas, and sampled categorical column literals via hybrid dense/BM25 search.
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
🚨 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
- 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?
Bidirectional Taxonomy Links
Internal Tools & Primary Sources
- BIRD-Bench: Can LLM Already Serve as a Database Interface? (paper, 2026-11-17)
- dbt MetricFlow Semantic Layer Architecture (official_docs, 2026-11-17)