CHESS is a novel end-to-end text-to-SQL system designed for complex, real-world databases. It introduces a scalable and effective LLM-based pipeline consisting of three main components: entity and context retrieval, schema selection, and SQL generation. The system addresses the challenge of generating accurate SQL queries by effectively retrieving relevant data and context, selecting an efficient schema, and synthesizing correct and efficient SQL queries. To increase retrieval precision, the pipeline employs a hierarchical retrieval method leveraging model-generated keywords, locality-sensitive hashing indexing, and vector databases. Additionally, an adaptive schema pruning technique adjusts based on the complexity of the problem and the model's context size. The system generalizes to both proprietary models like GPT-4 and open-source models such as Llama-3-70B. Through ablation studies, the effectiveness of each component of the pipeline and its impact on end-to-end performance are demonstrated. CHESS achieves new state-of-the-art performance on the BIRD dataset, a challenging real-world text-to-SQL benchmark with over 12,000 unique question-SQL pairs across 37 professional fields. The system also provides an end-to-end open-source version that achieves the best performance among other open-source baselines on the BIRD development set. The use of open-source models is crucial, especially when databases contain private information. The system's contributions include breaking down the text-to-SQL task into a three-stage pipeline, a scalable hierarchical retrieval approach, an efficient three-stage schema pruning protocol, a fine-tuned open-source DeepSeek-33B Coder model, and a high-performing end-to-end open-source pipeline ensuring data privacy. CHESS sets new state-of-the-art results on the BIRD dataset among known methodologies. The system's methodology includes entity and context retrieval, schema selection, and query generation, with the latter involving candidate generation and revision steps to correct potential errors. The system's performance is evaluated on the BIRD and Spider datasets, achieving high accuracy on both. The system's ablation studies show the critical role of each module in the pipeline in guiding LLMs to generate accurate SQL queries. CHESS ranks first on the BIRD leaderboard with a 65% and 66.69% execution accuracy on the development and test sets, respectively. The system's open-source version achieves 61.5% execution accuracy on the BIRD development set. The system's performance is further validated through experiments on the BIRD and Spider datasets, demonstrating its effectiveness in generating accurate SQL queries. The system's methodology is compared to previous approaches, showing improvements in performance and efficiency. The system's contributions include a novel approach to text-to-SQL synthesis, a scalable hierarchical retrieval method, an efficient schema pruning protocol, and a fine-tuned open-source model. The system's performance is evaluated across different query complexities, showing significant improvements in accuracy. The system's schema selection process is evaluated, showing improvements in precisionCHESS is a novel end-to-end text-to-SQL system designed for complex, real-world databases. It introduces a scalable and effective LLM-based pipeline consisting of three main components: entity and context retrieval, schema selection, and SQL generation. The system addresses the challenge of generating accurate SQL queries by effectively retrieving relevant data and context, selecting an efficient schema, and synthesizing correct and efficient SQL queries. To increase retrieval precision, the pipeline employs a hierarchical retrieval method leveraging model-generated keywords, locality-sensitive hashing indexing, and vector databases. Additionally, an adaptive schema pruning technique adjusts based on the complexity of the problem and the model's context size. The system generalizes to both proprietary models like GPT-4 and open-source models such as Llama-3-70B. Through ablation studies, the effectiveness of each component of the pipeline and its impact on end-to-end performance are demonstrated. CHESS achieves new state-of-the-art performance on the BIRD dataset, a challenging real-world text-to-SQL benchmark with over 12,000 unique question-SQL pairs across 37 professional fields. The system also provides an end-to-end open-source version that achieves the best performance among other open-source baselines on the BIRD development set. The use of open-source models is crucial, especially when databases contain private information. The system's contributions include breaking down the text-to-SQL task into a three-stage pipeline, a scalable hierarchical retrieval approach, an efficient three-stage schema pruning protocol, a fine-tuned open-source DeepSeek-33B Coder model, and a high-performing end-to-end open-source pipeline ensuring data privacy. CHESS sets new state-of-the-art results on the BIRD dataset among known methodologies. The system's methodology includes entity and context retrieval, schema selection, and query generation, with the latter involving candidate generation and revision steps to correct potential errors. The system's performance is evaluated on the BIRD and Spider datasets, achieving high accuracy on both. The system's ablation studies show the critical role of each module in the pipeline in guiding LLMs to generate accurate SQL queries. CHESS ranks first on the BIRD leaderboard with a 65% and 66.69% execution accuracy on the development and test sets, respectively. The system's open-source version achieves 61.5% execution accuracy on the BIRD development set. The system's performance is further validated through experiments on the BIRD and Spider datasets, demonstrating its effectiveness in generating accurate SQL queries. The system's methodology is compared to previous approaches, showing improvements in performance and efficiency. The system's contributions include a novel approach to text-to-SQL synthesis, a scalable hierarchical retrieval method, an efficient schema pruning protocol, and a fine-tuned open-source model. The system's performance is evaluated across different query complexities, showing significant improvements in accuracy. The system's schema selection process is evaluated, showing improvements in precision