Natural language to SQL on custom enterprise data warehouse powered by generative artificial intelligence
Systems and methods for translating natural language to SQL on a custom enterprise data warehouse powered by Generative AI. With an embodiment of the present invention, a natural language question may be converted to a meaningful and accurate database query, e.g., SQL query, relevant to tables existing in an enterprise data warehouse. An embodiment of the present invention is directed to a comprehensive approach of transforming a natural language query to a focused SQL query using domain specific data models across firmwide metadata systems and data systems. In response to a user query, an embodiment of the present invention performs metadata analysis, targeted data retrieval and then SQL generation. An embodiment of the present invention may apply data warehousing standards and guidelines followed in the enterprise and provide a plug-and-play type architecture and solution that is scalable to large warehousing and other systems.
1. A computer-implemented system comprising:
a computer server comprising one or more processors;
a memory component storing data tables; and
non-transitory memory comprising instructions that, when executed by the one or more processors, cause the one or more processors to:
receive, from a user via a user interface, a user query in natural language format to retrieve the data tables;
in response to the receiving the user query:
extract, via an attribute extractor, attributes from the user query;
map, based on indexing and vectorization via a domain mapper, the attributes to a relevant domain and a relevant sub-domain included in a standardized metadata store, wherein the standardized metadata store is generated by a standard data model using contextual data domain mapping and common data domain mapping, wherein the standard data model is based on physical data model and data statistics from a plurality of firmwide metadata systems;
execute, via a retriever, a set of similarity searches limited to the relevant domain and the relevant sub-domain of the standardized metadata store to generate results comprising one or more tables and columns, wherein the set of similarity searches are performed by a semantic retriever, a hybrid retriever and a graph retriever executing in combination, wherein the executing the set of similarity searches includes running the similarity searches on indexes identified by the domain mapper to find the tables and columns matching the attributes in the user query, wherein the retriever further retrieves, from few shot queries, datasets including the tables and columns;
apply, via a re-ranker, a re-ranking model to the results of the set of similarity searches to generate a re-ranked result including re-ranked tables and columns;
generate, via a generator, a query statement using the re-ranked result embedded with an enhanced prompt by applying a set of prompt configurations, wherein the set of prompt configurations comprise two or more of: domain specific guidelines, query optimization guidelines, and output formatting; and
transmit, on the same prompt via a communication network, the query statement with corresponding explanation including constraints to the user interface.
2. The computer-implemented system of claim 1 , wherein the standard data model is based on logical data model.
3. The computer-implemented system of claim 1 , wherein the standardized metadata store applies metadata standardization across a plurality of firmwide metadata systems and firmwide data systems.
4. The computer-implemented system of claim 1 , wherein the standardized metadata store applies domain based on indexing and vectorization.
5. The computer-implemented system of claim 1 , wherein the query statement comprises a structured query language (SQL) query.
6. The computer-implemented system of claim 1 , wherein the generator comprises a query checker that applies validation and guidelines.
7. The computer-implemented system of claim 1 , wherein the corresponding explanation comprises: data sources and records.
8. A computer-implemented method comprising steps of:
receiving, from a user via a user interface, a user query in natural language format to retrieve data tables stored in a memory;
in response to the receiving the user query:
extracting, via an attribute extractor executed by a computer system, attributes from the user query;
mapping, based on indexing and vectorization via a domain mapper executed by the computer system, the attributes to a relevant domain and a relevant sub-domain included in a standardized metadata store, wherein the standardized metadata store is generated by a standard data model using contextual data domain mapping and common data domain mapping, wherein the standard data model is based on physical data model and data statistics from a plurality of firmwide metadata systems;
executing, via a retriever executed by the computer system, a set of similarity searches limited to the relevant domain and the relevant sub-domain of the standardized metadata store to generate results comprising one or more tables and columns, wherein the set of similarity searches are performed by a semantic retriever, a hybrid retriever and a graph retriever executing in combination, wherein the executing the set of similarity searches includes running the similarity searches on indexes identified by the domain mapper to find the tables and columns matching the attributes in the user query, wherein the retriever further retrieves, from few shot queries, datasets including the tables and columns;
applying, via a re-ranker executed by the computer system, a re-ranking model to the results of the set of similarity searches to generate a re-ranked result including re-ranked tables and columns;
generating, via a generator executed by the computer system, a query statement using the re-ranked result embedded with an enhanced prompt by applying a set of prompt configurations, wherein the set of prompt configurations comprise two or more of: domain specific guidelines, query optimization guidelines, and output formatting; and
transmitting, on the same prompt via a communication network, the query statement with corresponding explanation including constraints to the user interface.
9. The computer-implemented method of claim 8 , wherein the standard data model is based on logical data model.
10. The computer-implemented method of claim 8 , wherein the standardized metadata store applies metadata standardization across a plurality of firmwide metadata systems and firmwide data systems.
11. The computer-implemented method of claim 8 , wherein the standardized metadata store applies domain based on indexing and vectorization.
12. The computer-implemented method of claim 8 , wherein the query statement comprises a structured query language (SQL) query.
13. The computer-implemented method of claim 8 , wherein the generator comprises a query checker that applies validation and guidelines.
14. The computer-implemented method of claim 8 , wherein the corresponding explanation comprises: data sources and records.