🤖 AI Summary
Enterprise text-to-SQL systems often fail due to incorrect database schema context, particularly in large-scale data warehouses characterized by complex table structures, informal metrics, implicit relationships, and access constraints. This work proposes Schema-First Retrieval, which formulates catalog selection as a standalone semantic retrieval problem. It introduces type-aware templated embeddings to encode tables, columns, metrics, relationships, and query history, and integrates vector retrieval, lineage expansion, cross-encoder reranking, workload memory, and rule-based access control into a multi-stage retrieval architecture. Experiments show that the method achieves 96.4% table recall@20 on CRUSH4SQL, improves column recall@10 by 11.1 points, outperforms BM25 by 32.8 points in table recall@5, boosts table recall@5 on SEDE from 52.1% to 92.3% using query history, and reduces SQL execution error rates by 2.5× on BIRD.
📝 Abstract
Enterprise text-to-SQL systems often fail before SQL is generated: the model receives the wrong schema context. Modern warehouses contain thousands of tables, abbreviated columns, informal metrics, hidden join conventions, and permission boundaries that are not captured by raw table names. We introduce Schema-First Retrieval, a retrieval layer that embeds catalog metadata rather than warehouse rows. The system indexes five typed catalog objects, tables, columns, metrics, relationships, and query history, using object-specific text templates. At query time, it combines parallel vector search, lineage expansion, cross-encoder reranking, workload memory, and deterministic access-control gates before SQL generation. On CRUSH4SQL (1,534 questions), Schema-First Retrieval reaches 96.4% table recall@20 and cross-encoder reranking adds +11.1 points at column recall@10; against an equally-templated BM25 baseline, semantic retrieval is +32.8 points at table recall@5. On SEDE (857 questions), query history raises table recall@5 from 52.1% to 92.3%. On BIRD (96 questions), schema-first context reduces SQL execution errors from 15.6% to 6.2%, a 2.5x reduction. These results show that catalog selection is a first-class retrieval problem for natural language analytics, not a prompt formatting detail.