Snowflake Cortex AI Functions explained with Patrick Roy

In my Sustainable SQL explained with Jaromir Jágr blog post I showed that you can learn SQL best practices in somewhat entertaining manner. Following that tradition, this time I will explain Snowflake Cortex AI functions through the career of a goaltender who revolutionized the position, Patrick Roy. Patrick Roy, the man who made butterfly goaltending standard, talked to his posts, left Montreal in a huff and then won two more Cups in Colorado and now finds himself behind the bench of the New York Islanders.

You can also think this blog as extension to the Jágr one as these Cortex functions rely on the old fashioned SQL, but add a little bit GenAI flavor to them.

WHAT ARE CORTEX FUNCTIONS, REALLY?

Before we go through Patrick Roy’s career, it is worth taking a moment to explain what Snowflake Cortex functions actually are, in a simple and practical way.

Snowflake Cortex functions are essentially built-in AI capabilities that allow you to perform tasks such as classifying text or images, analyzing sentiment, translating content, summarizing long documents, extracting structured fields from files, generating new text, or creating embeddings for similarity search. These capabilities are exposed directly as SQL functions, which means that you can use them where your data already resides. There is no need to deploy your own machine learning model, no requirement to manage GPUs, and no separate inference service that you must maintain or scale. You simply call the function in SQL, and Snowflake handles the underlying infrastructure and model orchestration.

It is also important to mention that Snowflake is not the only platform offering these types of AI utilities. For example both BiqQuery and Databricks have similar functions even with same naming convention. This means that examples do also apply to other platforms, albeit you might need to change your SQL syntax a bit as these SQL functions are not part of ANSI-SQL. At least yet. For this blog I will focus on Cortex examples, as Snowflake was the first to introduce these and are also most familiar to me. It's also worth noting that I'm also showing you an subset of the Cortex AI functions and the whole Cortex AI family itself. Cortex AI functions have, for example, functions to handle documents, audio and images which are not used in this story and Cortex AI family itself extends to Cortex Agents, Analyst and Search capabilities and more as seen image below.

Figure 1 - Cortex AI Family

AI _CLASSIFY and the rise of the butterfly

Before St. Patrick ever pulled on a Canadiens sweater, Montreal had already carved out the most decorated crease in hockey. The Canadiens are the oldest continually operating club in the NHL and have won more Stanley Cups (24) than any other team. 

Figure 2 -  The Many Stanley Cup Banners of the Montreal Canadiens, Centre Bell, Montreal, Quebec

They were founded in 1909 and by 1915–16 had won their first championship. Legends dotted the ice long before Roy arrived, Jaqcues Plante (6 Stanley Cups, fize straight Vezinas), Ken Dryden (6 Stanley Cups, five Vezinas and Conn Smythe) to name few. So when Roy entered the NHL in 1985–86, he entered a team which had had great goaltenders their team.

Roy was drafted 51st overall, didn’t even like the Canadiens growing up. Roy's career began on a backyard rink in Sainte‑Foy. He shares a birthday with Mario Lemieux (October 5, 1965), and athleticism ran in the family, his father Michel dabbled in hockey and tennis while his mother Barbara was an Olympic swimming hopeful. Patrick first skated on former Canadiens player Leo Bourgeault’s backyard rink and idolized Quebec Nordiques goaltender Daniel Bouchard and Los Angeles Kings legend Rogie Vachon. Bouchard even gave the young goalie a stick, which Roy reportedly slept with for luck. The youngster’s photographic memory and obsession with hockey cards led him to collect more than 100,000 pieces.

Figure 3 -  Daniel Hector Bouchard (born December 12, 1950). He played in the National Hockey League (NHL) between 1972 and 1986 with the Atlanta Flames, Calgary Flames, Quebec Nordiques, and original Winnipeg Jets.	Figure 4 -  Rogatien Rosaire "Rogie the Goalie" Vachon (born September 8, 1945). He played in the National Hockey League (NHL) between 1967 and 1982 with the Montreal Canadiens, Los Angeles Kings, Detroit Red Wings and Boston Bruins

But Roy won the starting job, backstopped Montreal to an improbable Stanley Cup and, at the tender age of 20, claimed his first Conn Smythe Trophy as playoff MVP. He did it with a style that, at the time, looked unorthodox: dropping to his knees and covering the bottom of the net.

Hockey historians will tell you the “butterfly” existed before Roy. Roger Crozier, Glenn Hall and Tony Esposito had all used it, but Roy made it his entire game. Sports Illustrated wrote that those older goalies were hybrids who still spent time on their feet, whereas Roy “made the butterfly his game, times ten”. By the mid‑1990s his knee‑based elegance had been mastered. He paired it with a world‑class glove hand and such competitiveness that he dared shooters to beat him. Today every NHL goaltender plays a version of Roy’s butterfly.

Figure 5 - Butterfly style is a technique of goaltending distinguished by the goaltender guarding the lower part of the net by dropping to the knees to block attempts to score. The butterfly style derives its name from the resemblance of the spread goal pads and hands to a butterfly's wings.

The AI_CLASSIFY function in Snowflake Cortex can label text or images into categories you define. In other words, you can ask Cortex to look at a dataset of save techniques and classify each clip as butterfly, stand‑up or hybrid. Just as Roy defined a category of goaltending, you can define the categories for your data:

 

-- Classify video frames of goaltenders into styles
SELECT
id,
AI_CLASSIFY(
image,
['butterfly','standup','hybrid']
) AS style
FROM hockey_save_frames;

 

Figure 6 - Butterfly	Figure 7 - Stand-up	Figure 8 - Hybrid

Replace image with your image column and watch how Cortex identifies the same knee‑driven stance Roy perfected decades ago. Classifying technique isn’t limited to images either, you can classify game recaps or scouting reports into categories like clutch, choke, controversial, legendary, or anything else you dream up.

AI _SENTIMENT and a tale of emotions

Roy’s career wasn’t just about style; it was full of emotion.

During the 1986 playoffs fans became fascinated with his rituals – he refused to cross blue or red lines and kept pucks from shutouts in his locker, wrote the names of his children on his stick, kept the pucks from his shutouts until season’s end and wore the same equipment for as long as winning streaks lasted. He once channelled his inner Ron Hextall, delivering a two‑handed paddle whack to Minnesota’s Warren Babe in 1987; the NHL suspended him for eight games. 

Montreal fans adored him for the 1986 miracle and the 1993 Cup run. In fact, that 1992‑93 season remains the last time a Canadian team won the Stanley Cup. Montreal lost only four playoff games and Roy again took home the Conn Smythe.

Figure 10 - Roy telling Ronald Corey that he had played his last game for Montreal.

Two years later the relationship imploded. On 2 December 1995, the Canadiens were routed by Detroit. Coach Mario Tremblay left Roy in net for nine goals before finally pulling him.

Furious and humiliated, Roy walked past his coach and told team president Ronald Corey that he had played his last game for Montreal.

The rift with Tremblay, a former teammate, had simmered for months. Four days later he was traded to Colorado. 

Fans and media were divided. Some sided with Roy, others with the Canadiens. This is where AI_SENTIMENT comes in handy. The function analyses text and returns sentiment scores across multiple languages. Imagine feeding it thousands of newspaper articles, tweets and forum comments from December 1995:

 

-- Gauge the mood around Roy's trade
SELECT
AI_SENTIMENT(content) AS sentiment,
COUNT(*) AS count
FROM habs_fans_posts
GROUP BY 1;


 

Figure 11 - Dec 7, New York Times - Roy Traded to Colorado

Figure 12 - Dec 6, Denver Post

You might discover that local newspapers were angry (negative), Montreal fans felt betrayed but hopeful (mixed), and Colorado fans were ecstatic (positive). Cortex even provides aspect‑based scores, so you could see whether people were upset about the trade itself, the coach’s decision, or the team’s management.

Figure 13 - Dec. 2, 1995, The Canadiens lose 11-1 to Detroit and Patrick Roy is humiliated by Mario Tremblay in the process, marking his last game as a Canadien.

Roy’s trade in December 1995 didn’t just alter his career; it also intersected with a franchise undergoing its own transformation. The Quebec Nordiques, founded in the WHA and later joining the NHL, struggled financially and in 1995 relocated to Denver to become the Colorado Avalanche. The team retained its Quebec roots, bringing over future Hall of Famers Joe Sakic, Peter Forsberg, Adam Foote and Valeri Kamensky.

Figure 14 - Peter Forsberg , Adam Foote and Joe Sakic The three stood for a portrait at the team’s practice facility in Englewood on March 3, 2008.

Days before their inaugural game, general manager Pierre Lacroix acquired Conn Smythe winner Claude Lemieux. The Avalanche started the 1995–96 season 16‑7‑4 and on December 6, 1995 traded for Patrick Roy, adding a three‑time Vezina winner and two‑time champion to an already potent roster. Roy quickly became the backbone of Colorado’s defense. In June 1996 he backstopped the Avalanche to a Stanley Cup in a sweep of the Florida Panthers, giving Colorado its first major professional sports championship.

Five years later the Avalanche returned to the Final; they acquired Ray Bourque in 2000, and in 2001 Roy posted a .934 save percentage in the playoffs and became the first player to win three Conn Smythe trophies. The Avalanche’s success was built on a foundation laid in Quebec – the Nordiques drafted Sakic, Forsberg and Foote and even acquired Roy’s eventual teammate Uwe Krupp through the infamous Eric Lindros trade

One of Roy's most famous one‑liners came after moving into Colorado. during the 1996 Western Conference semifinals, when Chicago forward Jeremy Roenick complained about a missed penalty shot. Roy responded: “I can’t really hear what Jeremy says because I’ve got my two Stanley Cup rings plugging my ears”. Years later he admitted the line was suggested by teammate Mike Keane, adding with a wink that he eventually won four rings.

Figure 15 - May 9, 1996 Western Conference Finals

Roy also famously talked to his goal posts during games. In his own words, he would tell them, “Come on guys, help me out. … The goalposts are always with me”. Sometimes they answered “bing” when the puck hit them. Cortex can’t yet measure the sentiment of inanimate posts, but it can quantify how endearing (or bizarre) this superstition appeared to fans.

AI _TRANSLATE and bridging cultures

Growing up, Roy was a Francophone in Quebec who secretly admired the Nordiques. After being drafted by the Canadiens, he needed to navigate a bilingual locker room and an English‑speaking hockey world. Later, when he joined the Colorado Avalanche, he became the spine of a team loaded with stars like Joe Sakic, Peter Forsberg and Adam Foote. In 1998 he even backstopped Team Canada at the Olympics.

The AI_TRANSLATE function converts text from one language to another. Suppose you have scouting reports on Roy in French (“un gardien papillon intraitable”) and want to make them accessible to your English‑speaking analytics team. Cortex can auto‑detect the language and translate:

Translating data helps you bridge silos, just as Roy bridged cultures in Montreal, Denver and now Long Island. Today he coaches in the U.S. but remains a proud Quebecer.

Figure 16 - Roy seen here in action as a calm coach	Figure 17 - Roy seen here in action as a calm coach

The Islanders’ roster includes Europeans, Americans and Canadians, and Roy’s staff must handle French, English, Russian and Swedish comments. AI‑powered translation ensures that no nuance is lost.

AI _SUMMARIZE, AI _EXTRACT and the legend’s résumé

Roy’s résumé is staggering: 551 regular‑season wins, third in NHL history; 151 playoff victories, most all‑time; four Stanley Cups (1986, 1993, 1996, 2001); three Conn Smythe Trophies; three Vezina Trophies and five Jennings Trophies. He holds numerous Avalanche records for games, wins and shutouts. He played in 11 All‑Star games and surpassed 447 career wins early in 2000–01.

Figure 18 -  November 14, 1987 Roy seen here in action against the Chicago Blackhawks

As data engineers we love long columns of numbers, but sometimes you just need the highlights. SNOWFLAKE.CORTEX.SUMMARIZE takes an English‑language text and returns a shorter summary. You could feed it a verbose game recap or even this blog and ask for “the top three takeaways.” It would respond with something like: Roy revolutionized goaltending with the butterfly style, won four Cups and three Conn Smythes, and later became a coach. Summarization is perfect for executive dashboards or Slack messages.

For more structured extraction, AI_EXTRACT can parse PDFs, JPEGs and plain text to pull out specific fields. Suppose you have a folder of scanned box scores from Roy’s games. You can define a JSON schema to extract the date, opponent, shots against, saves and goals allowed. AI_EXTRACT will produce a table ready for analysis.

 

SELECT metadata:date AS game_date, metadata:opponent AS opponent, metadata:saves AS saves, metadata:goals AS goals_allowed FROM ( SELECT AI_EXTRACT(file, '{"type": "object", "properties": {"date": {"type":"string"}, "opponent": {"type":"string"}, "saves": {"type":"integer"}, "goals": {"type":"integer"}}, "required": ["date", "opponent", "saves", "goals"]}' ) AS metadata FROM scanned_box_scores );

Figure 19 - November 16, 1997 - Roy goes over the redline resulting in not-so-familiar penalty to result sheet

This structured extraction is akin to what scouts did when they watched Roy: they distilled hours of game footage into key metrics that told the story of his dominance.

AI _COMPLETE and the art of coaching

After retiring in 2003 with 1,029 games played, Roy purchased the Quebec Remparts of the QMJHL. He coached them to a Memorial Cup in 2006 and again in 2023. In 2013 he returned to the NHL as head coach and vice‑president of hockey operations for the Colorado Avalanche. In his rookie season he led the Avs to a 52‑22‑8 record (112 points), a huge improvement from 29th to third overall. Colorado matched a franchise record for wins, posted the NHL’s best road record and never lost in regulation when leading after two periods. He received the Jack Adams Award as coach of the year.

Roy’s tenure in Colorado was as fiery as his playing days. He nearly knocked over a glass partition while yelling at Ducks coach Bruce Boudreau during his NHL coaching debut.

After missing the playoffs for two straight seasons, he resigned in August 2016. Roy said that his vision for improving the team wasn’t aligned with management and that he didn’t have enough say in the decisions that affected performance. He returned to juniors to refine his craft.

In January 2024, the New York Islanders hired Roy as head coach, replacing Lane Lambert. Islanders GM Lou Lamoriello admired Roy’s presence, his success in juniors and his ability to develop players. Roy, now 58, said he was excited for the challenge and still passionate about the game.

What does this have to do with AI? AI_COMPLETE is Cortex’s generative function. It takes a prompt and produces a completion using a selected large language model. You can use it to generate scouting reports, draft emails or even training plans. Suppose Roy wants recap the Islanders game against the Dallas Stars to the media on a random November evening. He could write:

AI_COMPLETE will generate a speech that channels Roy’s competitive spirit. For more deterministic outputs, say, generating JSON describing players’ weaknesses, you can supply a JSON schema using response_format.

AI _EMBED and finding the next St. Patrick

Scouts and analysts are always looking for the next Patrick Roy. Snowflake’s AI_EMBED function converts text or images into embedding vectors, that you can use for similarity searches. Imagine embedding every goalie’s scouting report and then searching for those closest to Roy’s vector. You might identify prospects who share his competitiveness, butterfly technique and clutch performances.

Pair embeddings with AI_SIMILARITY and you can calculate distances between players. You can also embed newspaper articles and track how descriptions of goaltending have evolved since the 1980s. It’s like using analytics to find echoes of greatness.

Closing thoughts

Patrick Roy’s story is legendary and it still continues. A shy kid drafted by a team he didn’t support, who then revolutionized his position and became known as “Saint Patrick”, winning three Conn Smythe trophies; a sudden divorce from his hometown team after an 11‑1 humiliation; immediate redemption with a new franchise; a late‑career Cup run that helped Ray Bourque finally lift the trophy; and a transition to coaching that has taken him from junior hockey to the NHL, to juniors again, and now to Long Island. Through it all he remained the ultimate clutch performer, someone who thrived under pressure.

Snowflake Cortex’s AI functions offer a toolbox for making sense of unstructured data. AI_CLASSIFY identifies patterns, just like coaches identify styles. AI_SENTIMENT gauges the mood around controversial moments. AI_TRANSLATE bridges linguistic divides. AI_SUMMARIZE and AI_EXTRACT turn mountains of text into digestible insights. AI_COMPLETE generates new content, and AI_EMBED helps us find similarities across vast datasets. When combined, these tools can help data teams capture the essence of a story, whether it’s about customer reviews or about a goalie who changed the game.

So next time you open the Snowflake docs and think, “this is dry,” grab a cup of coffee and remember Patrick Roy telling his posts to have a good night.