Using Cortex Analyst
1. Using Cortex Analyst
Welcome back. In the last video, you created a semantic model and passed it to complete, but we need better results. In this video, we will engage Cortex Analyst and see how it elegantly deals with this level of complexity. Let's think about this for a second. So imagine you are a senior manager at an international retail company that is feeling a little frustrated. You asked your text-to-SQL workflow for data on sales performance by region, by store, and who the top-performing sales reps are for each store and region. What you got back was a dataset that had columns of numerical IDs for the stores, regions, and reps. Not very human-readable. A quick filter tells you what you want to know, but now you have to spend time looking up which numerical identifier refers to which region, store, and rep. The other option is to join in new columns from dimension tables with the human-readable labels for the region, store, and reps. This is time-consuming and pretty frustrating. What you really want is the dataset delivered to you with these human-readable features given as default. Since you didn't get this, you contact your data team, send the dataset to them, and ask for them to build the joins and send a new version back to you. Then you wait. The problem wasn't the SQL. It returned the ask for information, just not in a human-readable form. The problem was the semantic model that the text-to-SQL workflow was using. It was too basic. And we can fix that. Are you ready to try this out to see the process in action? Jump back into your notebook, and we'll test the semantic model we've built, and then build it out even further to overcome this hurdle. The reason that the LLM did not produce SQL that was any better than OK is because it did not deal with the joins properly. Now, we'll call Cortex Analyst and see how it's able to deal with this new level of complexity. We start by importing from JSON and Snowflake. Now, we set the API endpoint and the timeout for 50,000 milliseconds. Now we define a method, getSQLFromCortexAnalyst. We set Cortex Analyst to take the user query and return any SQL statements. We build messages with the role being the user and the content query being identified as type equals text. We can now move on to setting up the request body that will be sent to the Cortex Analyst API. Under request body, we set the API request. We set the method to post, the path to API endpoint, leave a blank array for headers and parameters. We'll set the request body, and that the timeout will be in milliseconds. This covers the requests that will be sent to the API. We'll then set the parse JSON content to be transformed from the response JSON that the API sends back. Once that's set up, we move on to getting the content back from the Cortex Analyst API, and return the request body message, the content from the API, and the statement. Then, we'll test it out with our user query, asking what the highest single-day revenue was in each sales region. Now, we'll inspect the output of Cortex Analyst by running the next cell. Okay, we see that the return tells us the type of request SQL and the SQL code itself. Now, we'll run the SQL to see what we get back. But we will not stop there. We will continue on to the natural language response in a moment. Let's talk about why Cortex Analyst worked when a state-of-the-art LLM didn't. There is a real need for a semantic model when we are generating SQL queries. Even a super smart analyst would struggle to write SQL on raw schema data without further context. To understand this, let's look at the parts that make up the agentic workflow that answers user questions. The first part is the classification agent that takes the semantic model you produced, and the user question, and decides if the question is answerable. The agent classifies the question into classes, such as ambiguous, non-data question, non-SQL data question, and more. If the question can be answered using SQL, it's passed on. But if not, the user will be presented with similar questions that the classification agent can answer. Or, the question may be rejected. From there, content is analyzed and features are extracted by the feature extraction agent. This agent determines if the question is a time series question or a period-over-period question, such as asking what the year-over-year revenue was for a certain month. The feature extraction agent passes all of this to the context enrichment agent. That will process the semantic model with additional context relevant for answering the question. This is then passed to an array of SQL generation agents, which each use their own LLM to generate SQL. The error correction agents take the generated SQL and check for both syntactic and semantic errors by utilizing core Snowflake services, such as the SQL compiler. The agent then runs on an error correction loop on any errors that are found. From there, all of this corrected code is passed to a synthesizer agent and generates the final SQL query. I've left a link to a really interesting blog post that goes into this in even more detail in the reading after the video. Well done. You learned the limitations of using COMPLETE directly when working with semantic model and why a well-developed semantic model assists the SQL generation in a powerful way. In the next video, you will learn how to use RAG sibling, table-augmented generation, or TAG. Oh.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.