Frosty Friday
Snowflake Weekly Challenges
If you’ve, in the past, used CORTEX.COMPLETE as part of any workflow you are well aware of the strenuous prompting necessary in order to make it produce predictable results.
Well, worry no more! Structured outputs allow you to provide a JSON schema within which the LLM must respond.
Your challenge is to have CORTEX.COMPLETE produce a list of cool Snowflake features and descriptions of those features. The output ought to look like the below, post-processing:
Every Pi Day (3.14), math students, using fake but famous mathematicians’ names, flock to The Golden Ratio Bakery to enjoy their favorite π-themed pies. Instead of picking a random dessert, they select their treat based on the legendary mathematician they admire most.
Alan Turing Apple Pie 9.99£
Ada Lovelace Cherry Pie 12.5£
Carl Gauss Pumpkin Pie 15.00£
Srinivasa Ramanujan Chocolate Pie 8.75£
Katherine Johnson Blueberry Pie 10.99£
Euclid Raspberry Pie 11.25£
Johannes Kepler Strawberry Pie 14.50£
Leonhard Euler Peach Pie 14.99£
Pythagoras Blackberry Pie 9.75£
Fibonacci Mango Pie 13.50£
Sophie Germain Mixed Berry Pie 10.00£
Pierre-Simon Laplace Banana Cream Pie 12.25£
Blaise Pascal Lemon Meringue Pie 11.99£
Hypatia Key Lime Pie 10.75£
John von Neumann Pear Pie 13.99£
Joseph Fourier Apricot Pie 14.25£
Gottfried Wilhelm Leibniz Plum Pie 15.75£
David Hilbert Fig Pie 16.00£
Bernhard Riemann Walnut Pie 14.50£
Niels Bohr Almond Pie 13.25£
Johann Bernoulli Cranberry Pie 12.75£
Henri Poincaré Plum Tart 14.00£
Emmy Noether Coconut Cream Pie 15.99£
Georg Cantor Infinite Layer Pie 17.77£
This year, demand is through the roof, and the usual sequential order processing is causing frustrating delays. Mathematicians—real or not—are notoriously impatient and expect instant, parallel execution for their pie requests.
To avoid a pie-induced catastrophe, we need to optimize the order system by leveraging Snowflake’s ASYNC execution to process multiple mathematicians’ orders simultaneously and return the updated list of satisfied customers without delays.
Your task is to speed up pie order processing by inserting two mathematicians’ pie orders asynchronously using ASYNC, ensuring both inserts complete before returning results using AWAIT, and returning the updated order list sorted by order_id so we know which legendary mind—or at least their name—got their pie first.
Happy Pi Day to all!
Remember if you want to participate:
“A little knowledge is a dangerous thing”—a quote attributed to many, yet still undeniably true. We’ve all witnessed situations where people jump to conclusions based on partial information rather than considering the full picture.
Today, we’re tackling this issue by enforcing restrictions that prevent users from viewing only a fragment of the data.
Join Policies to the Rescue
Join Policies in Snowflake allow us to enforce joins, ensuring that users cannot retrieve data from a table unless they join it with another table. And that’s exactly what we’re going to implement in today’s challenge.
The Challenge
Step 1: Create and Populate the Tables
start-up code
CREATE OR REPLACE TABLE join_table (
col1 INT,
col2 VARCHAR,
col3 NUMBER )
JOIN POLICY my_join_policy
;INSERT INTO join_table (col1, col2, col3) VALUES (1, 'Sample Data', 123.45);CREATE OR REPLACE TABLE join_table_details (col1 INT, col4 VARCHAR, col5 DATE);INSERT INTO join_table_details (col1, col4, col5) VALUES (1, 'Additional Info', '2025-03-07');Step 2: Enforce Join Restriction
Your task is to configure the join policy so that only the ACCOUNTADMIN role can query join_table directly.
Any other role attempting to query join_table without a join (e.g., with SELECT * FROM join_table) should encounter an error message instead of retrieving data.
Goal
If anyone besides ACCOUNTADMIN runs a direct query on join_table, the expected result should be an error message similar to:
