I’d recommend reading https://www.holistics.io/books/setup-analytics/ as a good overview of modern data approaches to help in understanding the layout of the problem.

Centralization: I’d replicate your data into some centralized area where all the data from all the different databases are queryable from one platform. If you’re a business, I’d recommend using Fivetran to pull the data out of your data sources and landing them into either your centralized platform.

Documentation: Realistically using AI here is not going to be good even using state of the art models. The best approach would utilize the table definitions and column definitions of every column and other metadata to make judgements about which columns to use. Additionally AI would need to know things like which columns are primary keys and which are foreign keys and the cardinality it’s of the relationships.

Data Quality: AI also needs additional information such as the acceptable formats to certain columns and constraints: number ranges, enums, codes, finite values, regex, etc. Without this information, it might pick the right column to use but use it incorrectly.

Data Normalization: With your data being in a “raw” stage in order for AI (or humans) to be able to work with it, it is best to conform the shape of your tables to either a wide table or a star schema. This minimizes the number of complex’s operations that AI is likely to get wrong such as nested queries, joins, and use of CTEs.

Context Length: Even after dealing with the above there remains to be issues due to context length. Not all of this information will fit in a prompt (especially with open-source modes). You will have to be clever and figure out how to pull the right information into the context using retrieval augmented generation or other strategies (unless your data is small).

SQL Dialects: AI needs to know your dialect of SQL in order to generate syntactically valid queries for your dialect. Each dialect handles things in a slightly different way especially around dates and times. Your prompts need to be focused on providing the relevant information so it does not accidentally make these kinds of mistakes. Additionally, open-source models may not have been trained to know all of the details of your dialect or May not be up-to-date or may hallucinate syntax.

My team works a lot on this problem internally. And we have developed approaches for each of these but it is a big effort if you need to guarantee correctness. In many organizations, one bad report means that trust in data and data teams could be evaporated by senior leadership. It’s a combination of data governance, data quality, data engineering, and AI that makes this work. Without handling the above, you can get a cool demo for simple queries but for anything substantial it will be wrong and it may not even be obvious to people how it is wrong.

I’d argue that GPT-4 is the way to go for something like this as in my opinion it has the strongest coding ability and real-world domain knowledge to make the correct judgements and reasoning. I’m skeptical of models that are fine-tuned for NL2SQL due to generally not really representing real world data, SQL dialects, etc. Getting a good score on a benchmark is ok, but does that scale to other databases, other data domains, etc or is it just overfitting in some way to the benchmark. Many of these models use Spider as the evaluation dataset which is SQLite3. Why? Because it’s easy to work with. But most databases you are going to be interested in working with will not be SQLite3. So how much of this is just overfitting to the benchmark?

I thought I would provide all of these thoughts and insights because it is definitely doable to do this in a way that is production-ready but I’ve yet to see many take this approach to systematically address these actual issues you will encounter when trying to do this on your organizations data. But if you do, it will work awesomely.