We present CHESS, a flexible multi-agent system designed for Text-to-SQL tasks that can be adapted to various deployment constraints. Effective Text-to-SQL can become very challenging due to (i) the extensive size of database catalogs (descriptions of tables and their columns) and database values, (ii) reasoning over very large database schemas, (iii) ensuring the functional validity of generated queries, and (iv) navigating ambiguities of natural language questions. Our system addresses these challenges by introducing four specialized agents: Information Retriever (IR), Schema Selector (SS), Candidate Generator (CG), and Unit Tester (UT). We demonstrate the efficacy of CHESS under different deployment conditions. On industrial-scale large databases, CHESS, using the SS agent, efficiently narrows down very large database schemas, reducing the number of LLM tokens by x5 while boosting accuracy by approximately 2%. Additionally, CHESS with no additional fine-tuning achieves 71.10% on the BIRD test set, within 2% of the leading fine-tuned proprietary method, while requiring approximately 93% fewer LLM calls and 70% lower costs (USD). Our codebase is publicly available here.