As businesses increasingly rely on data-driven decision-making, the volume and complexity of data within enterprises have grown exponentially. However, processing this data and deriving actionable insights remains challenging due to the reliance on human analysts. The explosion in interest surrounding text-to-SQL (T2SQL) solutions and the capabilities of large language models (LLMs) like ChatGPT have presented a promising avenue to address these bottlenecks. However, even the most advanced LLMs struggle with generating accurate SQL queries without adequate contextual knowledge, business logic, and understanding of complex database schemas.

Integrating a semantic layer and its query engine with LLMs offers a significant leap forward in improving the accuracy and viability of T2SQL solutions. Major cloud providers have all written and spoken about the importance of semantic models in improving the use of their AI offerings. This integration provides LLMs with essential business-side metadata, negating the need to create complex metrics from scratch or generate joins between tables. Building upon Jeff Curran’s recent research, this article examines how a semantic layer and its query engine can elevate the performance of LLMs in generating SQL queries, offering a practical solution for organizations looking to leverage AI for natural language querying (NLQ) tasks.

The Challenges of Natural Language to SQL Conversions

Translating natural language questions into SQL queries is not a new challenge, but traditional attempts have struggled to produce reliable results. Even with modern LLMs showing impressive performance in natural language understanding and generation, significant issues remain:

1. Schema Complexity: LLMs struggle with generating correct table joins when database schemas become intricate.
2. Generative Inconsistencies: While LLMs are powerful at generating responses, they can produce inconsistent or incorrect results when handling calculations and KPIs.
3. Terminology Mismatch: Database tables and columns are often named according to data engineering conventions, which may differ from the business terminology used in queries. With context, LLMs find it easier to map between these different terminologies.

Addressing Challenges with a Semantic Layer

The primary use case for a universal semantic layer is to allow enterprises to democratize data access with accuracy, safety, and consistency. While this initial value proposition is compelling enough on its own, the semantic layer also became critical for managing cost and accelerating query performance as data moved to cloud data platforms like Snowflake, Databricks, and Google BigQuery. With the advent of generative AI, we’re finding yet another value proposition for a universal semantic layer. By providing a contextual layer on top of the data platform, the semantic layer can inform LLMs of the correct business logic, metrics, and relationships, eliminating the need to infer these elements from scratch.

The semantic layer standardizes business metrics and logic and provides additional metadata that LLMs can leverage during NLQ tasks. This includes business-relevant names, warehouse names, and descriptions critical for translating natural language into accurate SQL queries.

TPC-DS Benchmark Results and Analysis

In his white paper, Curran created an experiment using the publicly available TPC-DS dataset and a collection of evaluation questions categorized by complexity (regarding the database schema and the questions themselves). The evaluation used Google’s Gemini Pro 1.5 model, a commercially available LLM. 

The results demonstrated a substantial improvement in accuracy when the LLM was integrated with a semantic layer and query engine. The control system achieved only 20% accuracy across 40 questions. In contrast, the evaluation system with the semantic layer and query engine saw an accuracy rate of 92.5%. 

Specifically:

• The semantic layer achieved 100% accuracy with low-complexity questions with simple schema requirements, compared to 60% without it.
• High-complexity questions involving multiple table joins and complex calculations drastically improved from 0% accuracy in the control system to 70% with the semantic layer and query engine.

This performance improvement is attributed to several factors:

1. Simplified SQL Generation: The semantic layer query engine handled the complexity of joins and business logic, allowing the LLM to focus on generating more straightforward, single-table queries.
2. Business-Relevant Metadata: The semantic layer fed relevant metadata to the LLM, reducing confusion and improving the translation of natural language questions into accurate SQL queries.
3. Error Handling and Iteration: The evaluation system includes mechanisms to refine SQL queries when errors occur, leading to higher accuracy.

Key Takeaways

1. Integration of a semantic layer and query engine with LLMs can dramatically improve the accuracy of NLQ systems, making them viable for real-world business use cases.
2. The experiment demonstrated a 72.5 percentage point improvement in accuracy when using a semantic layer compared to traditional schema-only LLM prompting.
3. A semantic layer provides crucial business logic, metadata, and standardization, significantly reducing the burden on LLMs during SQL generation.

Conclusion

Combining a semantic layer and query engine with generative AI models represents a significant advancement in enabling natural language prompting for SQL queries. As organizations seek efficient and scalable ways to analyze their growing datasets, leveraging semantic layers with AI-driven NLQ solutions offers a practical path forward. The success demonstrated in Curran’s research underscores the potential for such integrations to become the standard approach for business intelligence and data analytics.