How Semantic Layers & Prompt Engineering Empower AI Data

Marley was dead to begin with. It's a great opening line and not just because it instantly draws you in, but it's important to the story. If we don't keep in mind the fact that Jacob Marley is dead, we won't really understand all the things that are about to happen to poor Ebenezer Scrooge.

Continue with the video below, or read the full transcript.

When we're working with generative AI data Q and A tools, there is a similar key fact that we have to keep in mind. Large language models take a text prompt as input and generate text as output. This is the one and only thing LLMs are capable of doing. When I talk about data Q and A tools, what exactly does that mean? Basically, think chatGPT for your data. It's a text interface where a user asks their question in natural language and the tool returns the answer from a dataset. Some folks might call it natural language query or data chat bots or similar. There are a lot of variants by a lot of companies, but they all work from the same basic principle, which is called text-to-query. So how does text-to-query work?

[01:03] How Does Text-to-SQL Work?

Well, it starts with a user asking their question in plain text, and a query is just computer code that is used to process data. So in text-to-query, we use a large language model to translate the user's question into code that we can then use to process the data and retrieve the answer. This works because code is just highly structured text. Writing code is actually one of the things that LLMs are best at because there's so much high quality example code that they were trained on. In our examples where the computer language is SQL or SQL, we might call it text-to-SQL.

It's one thing to write technically valid SQL query, which LLMs are good at, and it's another thing entirely to write a query that actually provides the correct answer to our question. Data are complex because the businesses they describe are complex. Writing good queries thus requires not just technical expertise, but specialized and often insider knowledge of the data and business processes it's used for. While LLMs are great at general purpose knowledge and technical expertise, they have absolutely none of that specialized insider knowledge. How could they? 

[02:12] What is a Semantic Layer?

The solution to this problem is something that you may have heard some of us at HelioCampus talking about over the last year or so: the semantic layer. It's essentially an instruction manual that explains to the LLM all the details about what is in the data and how to use it. Think of it as all the little details about how to use the data correctly that we're used to carrying around in our heads and now writing it down for the LLM to read. The semantic layer is what allows the LLM to utilize the data correctly, and without one, it has no hope of ever writing useful queries. 

To better understand exactly how text-to-SQL tool uses the semantic layer, let's go back to our Marley was dead to begin with principle. Large language models take a text prompt as input and generate text as output. The implication is the only way to get a significantly different response from an LLM is to provide it with a different input prompt. So if we ask a question like how many students were enrolled in fall 2024, and that is the only prompt we give the LLM, it has absolutely no context. What institution? What dataset? This is just like walking up to a complete stranger on the street and blurting out with no introduction: how many students were enrolled in fall 2024? What answer do you expect them to give you? ChatGPT is likely to be more polite but not more accurate. 

This is where our Marley rules come in. The only way to get a significantly different response from an LLM is to provide with a different input prompt. If we want the LLM to give us a usable SQL query based on a specific data set, incorporating all the business practices and context and data governance guidelines and everything else that we expect from a correct answer, then we need to tell it all of those things as part of the input system prompt. We call this process of building and adjusting the system prompt, prompt engineering.

[04:12] The Art of Prompt Engineering

To see how this works, let's take a slight detour to a simple bit of prompt engineering that we've probably all used and we now take for granted.

LLM tools like chatGPT remember our prior conversation. First we start with a clear prompt: write one sentence about how LLMs work. Our next prompt, however, can make an implicit reference to the first prompt. Add a second sentence. We didn't tell it the topics to write about, but it remembers our first prompt and knows that we want to continue to generate text on the same topic, namely how LLMs work. The way this works is that behind the scenes the web interface is doing some prompt engineering. They refer to it as a context window. When it sends the second prompt to the LLM, it includes the contents of the first prompt and response. So the system prompt that actually got sent to the LLM the second time was something like this. The only reason the second prompt knows about the first prompt and response is that the interface used prompt engineering to explicitly add these to the system prompt as context.

[05:17] Language Learning Models Need Context

So every time we add a new prompt for the whole conversation, the system prompt go into the LLM just gets longer and longer, always with the entire prior conversation included for context. I like to think of an LLM as a goldfish. There's no ability to make new memories. It just reacts to whatever is immediately in front of it. In other words, it generates text based on the system prompt it is given. For our text-to-SQL application, the bare minimum prompt engineering might be: one, tell it to output a SQL statement (how else does it know we're trying to do text-to-SQL?) and two, give it the table name and definition so we can write a valid query. But we probably need more to get the answer actually correct. In our course registrations table students have a row for each class they're taking. For example, Craig might be taking two courses, but he still only counts as one student enrolled in the term.

There's a simple way to do this type of calculation in SQL called count distinct. So we need that instruction to be part of the system prompt, either in words or as a sample SQL statement or both. Here is what that system prompt might look like. The table name and definition plus the instructions on how to count distinct students are all examples of information stored in the semantic layer.

[06:35] RAG: Retrieval Augmented Generation

The semantic layer is the repository of information about our data from which the Q and A application populates the system prompt. Another name for this process is RAG for retrieval of information from the semantic layer, which augmented the system prompt used in the generation of the output. The terminology isn't that important, but if someone mentions RAG, now you know. So far we've given the LLM enough context to answer just one type of question about counting students.

What if we ask a question about credit hours or course grades or instructors, or any number of other things that can be found in the course registrations table? By now, you can probably guess the answer. We need to have all the rules and logic and best practices for all of those things in our semantic layer so they can be part of the system prompt. Eventually, our system prompt is going to get very long, and that's okay. We need a comprehensive semantic layer to build a complete system prompt, which gives the LLM the context to answer a varied set of questions. 

[07:39] Three Key Takeaways for Prompt Engineering

So now you all know just enough to be dangerous when it comes to prompt engineering and system prompts and semantic layers. The key facts that we have discussed are: one, large language models take a text prompt as input and generate text as output; two, the only way to get a significantly different response from an LLM is to provide it with a different input prompt; and three, the semantic layer is how a text-to-query system builds the input prompt or system prompt, which contains the relevant context to write correct queries. Ergo, the way to get a text to query system to write correct queries is to provide it with a robust semantic layer. 

[08:18] Your Semantic Layer Improves AI Analytics

The way to improve the accuracy of the system's results is to improve the semantic layer content. The foundation of the semantic layer is data organized around key use cases, validated by functional experts and governed for effective distribution. This has always been the core value proposition provided by HelioCampus that we provide that premier data infrastructure for higher education institutions. When it comes to AI, it's clear that the more things change, the more they stay the same, but the more sophisticated the tools get, the more they rely on having this solid foundation to power them. The secret, the secret sauce, is not the AI tools. The secret sauce is what we put in the semantic layer that feeds the AI tools. 

Hopefully this video has helped folks understand how data Q and A tools work. The ultimate goal of AI analytics and reporting tools is to make data more accessible to more people. But that can't happen without doing the hard work of setting up the foundational data infrastructure with our robust semantic layer. So if you take just one thing away from this video, it should be this: Marley was dead to begin with.

Vocabulary Words

• User Prompt: The text that a user types into an LLM chat interface.

• System Prompt (aka Input Prompt): The final text string that is actually sent as input to the LLM.

• Prompt Engineering: The process of converting a User Prompt into a System Prompt, usually by appending additional context designed to improve the LLM response.

  • RAG (Retrieval Augmented Generation): A type of Prompt Engineering where information is pulled into a System Prompt from a knowledge base (such as a Semantic Layer).

• Text-to-Query: The process of using an LLM to write a query in computer code that retrieves an answer from a data set, based on a User Prompt about the data set.

  • Text-to-SQL: A specific variant of Text-to-Query where the query is written in SQL (pronounced “S-Q-L” or “sequel”), a common query language.

• Semantic Layer: A body of text that describes a data set, including both its technical structure and its functional content. A key component used in the Prompt Engineering process in Text-to-Query applications.