Additional Cortex AI Functions: Hands-on Demo
1. Additional Cortex AI Functions: Hands-on Demo
Alright, we talked about Cortex AI functions in theory. But let's get into the code and see how they really work. In the last video, we talked through AI_TRANSCRIBE, AI_PARSE_DOCUMENT, AI_REDACT, AI_FILTER, AI_AGG, and AI_SUMMARIZE_AGG. In this lesson, we're going to actually use some of them, using the ski gear call transcript table you've been working with throughout the course. Before we dive in, here's a quick reminder of what we're working with. We have a table called call transcripts with a column called transcript that contains the full text of a customer support call. There are a few hundred rows. If we look into specific rows, some of the calls are billing questions, some are product complaints, some are just customers asking about the return policy. In this lesson, we want to find the calls where customers were upset about a product issue, understand what the main problem was, and do it all in SQL. Let's go! We will start with AI_FILTER to decrease the amount of data we need to handle going forward. The AI_FILTER function takes two arguments, the text to evaluate and a natural language condition and returns true or false for each row based on whether or not the row meets the filter condition. Here's the basic syntax. So what do we get when we run this? The result is a subset of the table. We don't get every call, but instead just the ones where the model evaluated the transcripts and returned true for the condition. In our case, these are frustrated customers and our product complaints. Notice what we didn't do. We didn't write a list of keywords to match on exactly. We also didn't build a complicated regex. Instead, we describe what we're looking for, all in plain language, and the function handled the matching. You can make the condition as specific or broad as you want. If you want only calls where a specific product was mentioned, add that to the natural language description. If you want calls where the customer asked to speak to a supervisor, describe that. The function reads each transcript in full and evaluates against your description. Now let's save this filter, set it as a CTE, so we can work with it. We will build this in the next step. Now that we have this filtered set of calls, we want to know, across all of these, what were the main issues that customers were calling about? This is where AI_AGG comes in. It's an aggregate function. It takes a column and a natural language question and synthesizes an answer across all the rows in that group. We can run that with code that looks like this. Notice how we chain the filter and the AI_AGG function back to back? That's starting to get powerful. When we run this, we get back a single row. The model read every transcript in the filtered set and synthesized an answer for your question. No Python loop, no chunking, and no API calls in a for loop. This is a big deal if you've ever tried to do a multi-document summarization before without these specialized AI functions. The old approach was something like, chunk your documents, embed them, retrieve the most relevant chunks, send batches to an LLM, then stitch an answer together. Here, you're writing a SQL aggregated function. Much simpler, right? One other major thing, I want you to know about AI_AGG. Specifically, I want you to know that it works within Snowflake's context window limits, so very large tables with very long text will hit limits. For most analytical use cases with reasonable text lengths, you'll be fine. For extremely large data sets, you may want to filter down first, which is exactly what we're doing here by applying AI_FILTER before AI_AGG. Another thing I want to point out is that you can group by a column and get a separate answer per group. So, if your calls are organized by a product category, you can ask for the top three issues per product in one query. Same pattern, one answer per group. The next function we'll cover is AI_REDACT. Call transcripts often contain personally identifiable information, customer names, phone numbers, email addresses, account numbers. If you're sharing this data with another team or exporting it for analysis in a less controlled environment, you want to strip that out to avoid violating privacy concerns. As you can see when we run this, whereas the original transcript had customer name or phone numbers, the redacted version replaces it with a placeholder. The rest of the text is preserved. The structure of the call, the product being discussed, the nature of the complaint, all that stays intact. This lets you run downstream analysis on data that's been de-identified, which is useful for sharing with internal analytics teams, for archiving, or for building training datasets. The last individual function we'll cover here is AI_TRANSCRIBE. It's worth covering briefly even though our demo data is already text. In a real-world scenario, many organizations have recorded calls stored as audio files in cloud storage. If those files are loaded in Snowflake stage, here's how you would transcribe them. The output is a JSON object with a transcript text and some metadata. You typically extract just the text field with something like transcription:text::string afterward. Once you have a transcript in a column, you can run the full suite of AI functions on them, the exact same way we've been doing throughout this lesson. Okay, we've seen the individual functions, but let's put them all together. This is a composability pattern from the concept overview, but now with actual syntax. We'll build one query that filters, extracts, classifies, and aggregates. Let's walk through what happened here. The first CTE uses AI_FILTER to pull only frustrated or defect-related calls. That's your relevant subset. The second CTE enriches each row. AI_EXTRACT pulls from structured fields from each transcript. AI_CLASSIFY tags each call with a category. AI_REDACT strips PII, producing a clean version of the transcripts for downstream use. The outer query then groups by category and uses AI_AGG on a clean transcript to generate a specific defect summary for each category plus a count so you know how many calls fell into each bucket. You get a table with one row per issue category, a natural language summary of what the customer said, and a count. This is an entire qualitative analysis workflow in one SQL query chain. No Python, no external orchestration, and no data leaving Snowflake. One more thing before we go on. You may notice that small little button on the right corner of your interface? That is Snowflake CoCo. CoCo is your data-native AI coding agent that lets you turn your complex data engineering, analytics, and AI workflows into simple conversations. You can use it right in your Snowsight UI, or your terminal with the command line interface, or on your desktop with the CoCo desktop app. As a Snowflake-native AI application, it is integrated right into your schemas, data, existing code, and is aware of all of it. With CoCo, we can perform the same actions as we have via function implementation, but using natural language prompting instead. You simply type your goal, and it will choose the right function to get the job done. Let's look at the example of this. We'll tell CoCo to recap the transcripts. And we'll see the output right there. Your results may be different from mine. This is great for experimenting quickly if you want to use natural language instead of syntactically correct code. Speaking of correct code, I know sometimes I just forget the syntax. Instead of going and using the official docs to look up the function signatures, I could just use CoCo to implement them instead. Just like we did before, let's summarize the transcript, but this time in a new cell. To do this, we simply tell CoCo to make a new cell that recaps the transcripts. Just like that, our AI function is implemented, but keep in mind that might not be always the case. There's no documentation reading or remembering syntax necessary. Just clean, natural language prompting of our AI agent. In the past few minutes, we've covered many new and useful functions, but you may notice that we didn't go deep into AI_PARSE_DOCUMENT or AI_SUMMARIZE_AGG in this demo. AI_PARSE_DOCUMENT is most useful when you have document files in stages, PDFs, images, scanned forms, and syntax similar to AI_TRANSCRIBE in that it takes a file URL and its description of what to extract. AI_SUMMARIZE_AGG works like AI_AGG, but it's optimized specifically for summarization tasks. Both are covered in the companion reading for this lesson, which also includes syntax references for every function in the AI_ family. All of those compose in SQL, filter, enrich, aggregate, all use one query chain and no orchestration code. Both are covered in the companion reading for this lesson, which is also included in syntax reference for every function in the AI_ family. With the code all ran, let's recap what we built. AI_FILTER lets you filter rows using natural language conditions, using qualitative filtering without keywords or regex. AI_AGG aggregates across rows using language models. You can ask a question and get a synthesized answer across your whole dataset or per group. AI_REDACT strips PII and other sensitive content from text columns, so you can share the data more safely. AI_TRANSCRIBE converts audio files in the stage into text, at which point the rest of the AI functions work on the output just like any other text column. All of these compose in SQL. Filter, enrich, aggregate, all using one query chain and no orchestration code.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.