Enterprise AI & Databases

Multi-Dialect Text-to-SQL Pipeline with Vectorized Schema Caching & Pruning

Architected an enterprise Text-to-SQL system reducing generation latency by 75% via dynamic schema pruning, dialect-aware routing, and vector caching.

Dec 20248 pages · Technical Architecture Spec

Context: Pratham Software (Sr Data Scientist)
Format: Technical Specification (.DOCX)

Problem Statement

In enterprise databases possessing hundreds of tables and thousands of columns, injecting the complete database catalog into an LLM prompt exceeds token limits and drastically increases hallucination rates and response latency (exceeding 8+ seconds per query).

Architecture Specification

1. Dynamic Vectorized Schema Pruning

  • Schemas, table documentation, column descriptions, and primary/foreign key relationships are pre-embedded into a vector store (Pinecone).
  • When a user submits an analytical inquiry, a bi-encoder retrieves only the top-kk relevant table definitions.
  • Reduces prompt token overhead by 82%, dropping latency to under 1.8 seconds.

2. Multi-Dialect Syntax Normalization

  • Supports Snowflake SQL, PostgreSQL, BigQuery, and MySQL.
  • Employs a two-tier synthesis:
    1. Abstract SQL AST Generation (Dialect-agnostic)
    2. Concrete Dialect Transpiler with SQLGlot validation.