An HR use case using Cortex AISQL where AI_FILTER shortlists on substance, AI_CLASSIFY grades the near misses, AI_AGG writes the summary for the hiring manager.
Every talent acquisition team knows this queue. Multiple applications sit against multiple open requisitions, and the language never lines up cleanly. A resume says “led migration of legacy reporting infrastructure to a cloud data warehouse”. A requisition says “seeking a candidate with Snowflake or BigQuery experience”. Nothing in either string matches the other, and a keyword search has nothing to work with. Worse, keyword search actively misleads you in the other direction, surfacing a resume that happens to share a word with the requisition for reasons that have nothing to do with the job.
Snowflake’s Cortex AISQL replaces that keyword search with a JOIN condition that reasons instead of matching strings. You write the condition in plain English, and the AI decides row by row whether an applicant genuinely fits a role. This post builds that screening pipeline end to end, from a semantic join that shortlists the real fits, to a graded classification that ranks how strong each match actually is, to an aggregation that turns each shortlist into a paragraph a hiring manager can read in ten seconds.
Let us build it out, starting with the database and schema, and a sample pool of resumes and requisitions that we will be screening throughout this post. Step 1: Create database and schema.
We will start by creating a dedicated database and schema.
Step 2: Create the Resume table with ten candidate records
We will now create the resumes table to store the candidate name along with the content from the resume.
Let us now insert candidate records. We will load a total of ten applicant resumes spanning data engineering, product design, fraud/streaming engineering, plus two keyword-trap resumes and two clear non-fits, each with a realistic mix of relevant and irrelevant experience. Once you run the insert statements from the accompanying sql file, you should have ten records as shown below.
Note: In a real pipeline, resumes typically land as PDFs or Word documents on a stage, not as hand typed text. Snowflake has functions built for exactly that. AI_EXTRACT is built for targeted extraction. You provide a schema, key-value pairs, or questions, and it pulls exact, structured data straight from text, tables, or images, no separate parsing step required. Alternatively, AI_PARSE_DOCUMENT and AI_COMPLETE could work together as a two-step pipeline instead: AI_PARSE_DOCUMENT extracts the raw content, structure, and layout from a file like a PDF into a clean, queryable format, then AI_COMPLETE reasons over that parsed output to summarize it or pull out specific insights using a model of your choice. A pipeline might use AI_EXTRACT directly, or pair AI_PARSE_DOCUMENT with AI_COMPLETE when the extraction logic needs more judgment than a fixed schema allows. Previous posts in this series go deep on Snowflake’s multimodal document capabilities. Here, the goal is to show what Cortex AISQL does with resume text once you have it, so the inserts stand in for that upstream step.
Step 3: Create the Jobs table and insert the three Job requisitions.
We will have three open requisitions: Senior Data Engineer, Product Designer, and Fraud Detection Engineer, each with a short, plain-language description of what the role actually needs.
Upon executing the three inserts, the Jobs table will contain these records.
Checkpoint: To summarize, we now have ten applications, three requisitions, and a genuine mix of outcomes hiding in there. Two candidates are a real fit for each role. Two more, Sam Whitfield and Chris Doyle, share a keyword with the Senior Data Engineer requisition for reasons that have nothing to do with the job. Naomi Reyes and Rachel Bennett, are the kind of applications that make up most of any real queue, one with no technical background at all, one close enough to be genuinely ambiguous. A keyword search would handle none of this correctly.
Step 4: Match jobs on substance and not keywords.
We will use AI_FILTER to perform a contextual join of resumes with jobs.
PROMPT builds the templated instruction, substituting each resume and job description into the numbered placeholders. AI_FILTER evaluates that instruction per row pair and returns a Boolean, which is exactly what a JOIN condition needs. The output is displayed below.
Six matches out of thirty possible pairs, two genuine fits shortlisted per role, based entirely on what each application actually describes. Sam Whitfield, Chris Doyle, Naomi Reyes, and Rachel Bennett all correctly drop out.
Let us compare with the full roster (displayed below): We can see six real matches, two keyword traps that a string search would have surfaced incorrectly, and two applications that are simply the wrong fit for anything open right now. That last column is what a keyword search cannot produce. It has no concept of “close but not there,” only whether a string appears.
Step 5: The keyword collision test
Sam Whitfield and Chris Doyle are the two candidates in this pool who would fool a keyword search. Each resume shares a real word with the requisition, but for reasons that have nothing to do with the job. Sam’s resume says “Snowflake”. Chris's resume says “warehouse”. The requisition for Senior Data Engineer contains both words.
As expected, no rows are returned.
A keyword search on either “Snowflake” or “warehouse” against this requisition would have returned both resumes as hits. Neither belongs. Sam writes SQL against Snowflake tables but has never touched infrastructure work, and Chris manages a physical distribution warehouse that has nothing to do with cloud data platforms. That gap between sharing a word and doing the job is exactly what a string match cannot see and a reasoning based condition can.
Step 6: Grading the fit instead of just a yes or no
AI_FILTER’s boolean is exactly right for building a shortlist, but real screening decisions are rarely binary. A recruiter wants to know not just who cleared the bar for a requisition, but how close everyone else came, since a hiring manager might still want to glance at a strong near miss. AI_CLASSIFY replaces the yes or no with a tier, and it takes a task_description parameter so you can tell the model explicitly what to weigh, the same thing AI_FILTER's prompt does.
Sam Whitfield and Aditya Rao are the two candidates whose tier could potentially move. The above run showed up Sam as possible fit. Different runs could produce Sam alone on Possible fit, Aditya alone on Possible fit, and both of them there together on Possible fit, with no changes to the query. Neither candidate’s result is a mistake, and neither is a keyword trap the way Chris Doyle’s would be. Sam Whitfield genuinely writes SQL against Snowflake tables, that part of his resume is real, it just supports Tableau dashboards, not infrastructure work. Aditya Rao’s resume describes designing a streaming pipeline on a distributed event platform, real pipeline architecture work, just built for fraud detection instead of general analytics. Both are legitimate borderline cases, which is exactly why the model doesn’t always land on the same one. What matters here is not which of the two shows up in a given run, it’s that either one can, and that this is ordinary model non-determinism, the same query and the same data producing a different classification depending on when you run it.
This is also a classic case, where how a light tuning of the prompt changes the results. Tightening the wording turned out to fix both problems at once. Adding a single word, asking the model to weigh fit “strictly,” changes the result entirely.
Same functions, same dataset, a different task_description depending on whether the goal is casting a wide net or narrowing a flood. This time there are no Possible fit entries.
The stricter wording does two things at once, and it’s worth being honest about the tradeoff between them rather than calling one version simply better. It collapses the middle tier entirely, so a recruiter using this version never sees Sam Whitfield flagged as worth a second look. He lands in the same bucket as Naomi Reyes, whose background has nothing to do with the role at all. But it also makes the result perfectly repeatable, and very true to the job requisition, which is a real and different kind of value in a hiring context where a classification might need to be defended later. A looser instruction surfaces more nuance but isn’t reliably reproducible. A stricter one gives up that nuance in exchange for an answer you can actually stand behind. Neither wording is the correct one in the abstract, they are two different products, and which one belongs in a real pipeline depends on whether the team wants borderline candidates surfaced for a human to weigh, or a clean, defensible, repeatable line.
Both of those runs used a task_description loose or strict. Now, wun the same pattern against Fraud Detection Engineer next, this time with no task_description at all, to see whether the parameter was ever necessary there or just good practice.
The two candidates were correctly flagged as Strong fit. Here, there isn’t any middle tier, no keyword trap, and no error, a clean result straight out of the bare query. Fraud Detection Engineer has no planted vocabulary collision the way Senior Data Engineer does with “Snowflake” and “warehouse,” so there was nothing here for a bare category list to get fooled by. That is the actual lesson from running both requisitions side by side. task_description matters most when a resume pool has surface level vocabulary that could mislead a classifier. It does add token cost, though, so keep it as short as it can be while still doing its job. The safer default is to write it every time regardless.
Step 7: Generate one summary per shortlist
Once AI_FILTER has produced a shortlist for each requisition, AI_AGG rolls that shortlist into a single written justification, the paragraph a hiring manager actually wants instead of reading every resume individually.
Let us examine the output. With two matched candidates per role in this dataset, each summary genuinely synthesizes across two different resumes rather than restating one. At real scale, when twenty applications clear the filter for one requisition, AI_AGG turns twenty resumes into one paragraph, and it does this without hitting the context window limit that would eventually break a plain COMPLETE call asked to read all twenty at once. AI_AGG is built specifically to aggregate across rows that would not otherwise fit in a single prompt.
Note: None of this needs to live in a SQL worksheet. The team could build a Streamlit app on top of it. The output of these three queries, who matched, how strong the fit is, and why, is exactly the shape of data a Streamlit app within Snowsight would read straight off a table. A page per requisition listing each shortlisted candidate, their AI_CLASSIFY tier, and the AI_AGG summary justifying the shortlist, so a hiring manager gets a working screening dashboard without touching SQL. The wide-net and tight-shortlist prompt variants above can sit behind a simple toggle in a Streamlit app in Snowsight, “hard to fill” versus “high volume”, with each option mapping to a pre-written task_description behind the scenes. A stored procedure could take the job_id and toggle value, run the function calls, and return the shortlist for Streamlit to render as a table.
The naive way to run this join is a cross join, evaluating the AI condition once for every resume and requisition pair, which grows quadratically. Thirty pairs is nothing, but ninety thousand applications against five hundred open roles is forty five million calls to a language model, and no data team easily signs off on that.
This is exactly the optimization Snowflake’s engineering team built into Cortex AISQL. When the query optimizer recognizes that a semantic join can be reformulated as a classification problem, it automatically rewrites the execution plan, evaluating each resume once against the full set of job labels instead of once for every resume and job pair. Snowflake’s engineering benchmarks show that this transformation can reduce a quadratic join to linear complexity, with reported speedups ranging from 15x to 70x depending on the workload, all without requiring any changes to your SQL. You write the query declaratively, and the optimizer determines the most efficient execution strategy.
Semantic joins are not a replacement for your existing structured joins. Keep using keys where keys exist. The value here is entirely in the gap that structured joins cannot close, matching entities across different vocabularies, formats, or writing styles where a recruiter would recognize the connection instantly and a WHERE clause cannot. Resume screening is the textbook case, but the same pattern applies to matching invoices to contracts, support tickets to product catalog entries, or building descriptions to compliance requirements.
Use the three functions for what each is actually good at. AI_FILTER decides who makes the shortlist. AI_CLASSIFY grades how strong a fit is, which is the detail a binary join throws away, and it earns that grading power specifically because of task_description, a bare category list has nothing telling it to weigh substance over shared vocabulary. The wording of task_description changes the shape of the output, not just its accuracy, a looser instruction surfaces borderline candidates, a stricter one doesn't, and neither is simply the correct choice, they trade recruiter visibility for a cleaner, more repeatable line. That parameter is worth including by default. AI_AGG turns a shortlist into something a person will actually read. Chaining all three like this costs more than a single AI_FILTER call, since you are paying for classification and aggregation on top of the join, so reserve the extra steps for the point in the pipeline where a human is about to make a decision based on the output, not for every application on the way there. Cost is the one thing to watch across all three, and it matters more in an HR context than most, since resumes are personal data and every extra pass is both a compute cost and a governance surface. Every AI_FILTER evaluation, every AI_CLASSIFY call, every AI_AGG rollup is a model call, and none of this is free the way a hash join or a SUM is. Start with a WHERE clause that narrows the candidate set using ordinary SQL first, department, location, seniority level, whatever structured filters your respective Applicant Tracking System already gives you, then let the AI functions work on what is left. Let the low cost deterministic predicates do the heavy lifting before the expensive semantic ones run.
A string is easy to match. Matching a person to a job has always been the hard part, and it still is, just with a sharper eye for it now.
The code for this blog can be accessed here. I share hands-on, implementation-focused perspectives on Generative & Agentic AI, LLMs, Snowflake and Cortex AI, translating advanced capabilities into practical, real-world analytics use cases. Do follow me on LinkedIn and Medium for more such insights.
Agentic AI in Action — Part 26 -Candidate Screening, Reimagined. A Cortex AISQL Pipeline for HR was originally published in Towards AI on Medium, where people are continuing the conversation by highlighting and responding to this story.