Sofia Pierini

Your goal is to establish a connection to Snowflake using an RSA PKCS8 private key. However, the behavior of snowflake.connector changes based on how the key is generated. Your challenge is to determine the correct connection parameters for both encrypted and unencrypted private keys.

Step 1: Use OpenSSL to generate two different private keys

Encrypted Private key (Passphrase Required)
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key_des3.p8

Unencrypted Private key (No Passphrase)
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8

Step 2: Establish Connection by using snowflake.connector library

Your task is to connect to Snowflake using two different RSA private keys. However, depending on how the key was generated, the connection parameters may need to be adjusted.

• Try connecting with the first key (rsa_key_des3.p8) and include the private_key_file_pwd parameter.
• Try connecting with the second key (rsa_key.p8) but this time, decide whether the private_key_file_pwd should be included or not.

Pay close attention to how Snowflake responds to each connection attempt. If you get an error saying a password was provided but not needed, think about whether the private_key_file_pwd parameter should be there in the first place. But, if the connection fails completely, double-check how the key was generated and whether it requires a passphrase. Try adjusting the private_key_file_pwd parameter by adding or removing it and see what changes.

Thanks to @StefanoGiostra for the topic of this challenge 🙂

Ever spent too much time searching for something in a PDF (what’s boring activity!)? Maybe an old report, a receipt, or meeting notes? Let’s make that easier with Snowflake Cortex and AI.

In this challenge you should take a few PDFs you already have (work docs, invoices, articles, etc.). Load them into Snowflake, use Cortex AI to process the text, and build a smart search that lets you quickly find answers.

This challenge could be a little bit trickier, so here the steps to complete the challenge:

1. Upload Your PDFs
2. Enable AI-Powered Search
   • Use Cortex Search Service to enable semantic search on your document collection.
   • Implement Anthropic Claude to generate AI-powered responses when querying your documents.
3. Test it! – Try searching for something specific (e.g., “How much money have I mysteriously spent on coffee last year?”).

Need more help? You’re not alone! This challenge is powered by the collaboration of the Data Superhero community and the incredible blog by @MajaFerle on how Snowflake Cortex +  Anthropic Claude can make document search effortless:

To smash the challenge click here: Using Snowflake Cortex with Anthropic Claude

We can’t wait to see what you discovered!

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.

Welcome to this week’s Frosty Friday challenge! In this challenge, you’ll explore Snowflake’s powerful metadata and schema inference capabilities. Your task is to use INFER_SCHEMA to extract metadata from a Parquet file in a stage and transform it into a Snowflake table.

By following a series of steps, you will go from querying the metadata of a staged file to creating a Snowflake table using the inferred schema. Can you connect the dots and complete the pipeline?

Using the INFER_SCHEMA and GENERATE_COLUMN_DESCRIPTION functions, as shown in the first image, your task is to:

1. Query Metadata: Use the INFER_SCHEMA function to extract the metadata of a staged Parquet file. This metadata will include column names, data types, and file-level details.
2. Transform Metadata: Generate a CREATE TABLE statement using the inferred metadata (as shown in the second image) to define the table structure.

Enjoy finding it and remember to post your code 🙂

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.

You’ve just been appointed as the SQL expert at Dynamic Data Academy, a prestigious institution celebrated for its variaty of courses and programs. As part of your role, you’re tasked with analyzing enrollment data stored in the college’s Snowflake database. The table structure is as follows:

CREATE TABLE student_enroll_info (
    student_id INT PRIMARY KEY,
    course VARCHAR(50),
    duration VARCHAR(50)
);

-- Step 2: Insert data into the table
INSERT INTO student_enroll_info (student_id, course, duration) VALUES
(1, 'CSE', 'Four Years'),
(2, 'EEE', 'Three Years'),
(3, 'CSE', 'Four Years'),
(4, 'MSC', 'Three Years'),
(5, 'BSC', 'Three Years'),
(6, 'Mech', 'Four Years');

How do you count the total number of students grouped by each course and duration separately?

Hint: Have you ever heard about GROUP BY GROUPING SETS? 😊

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.

Have you ever created a lightweight data pipelne using the Snowflake REST APIs and Postman? If not, this is the challenge for you. We provide you all the information and steps to complete this challenge.

Step 1: Set up Snowflake REST API Access

Refer to the Snowflake GA REST API announcement blog to understand the API capabilities and configure your Snowflake account for REST API access. Then, enable API integration, and generate the required API token for secure authentication.

Step 2: Install and configure Postman

After having downloaded and installed Postman, create a collection in Postman for your Snowflake REST API calls.

Step 3: Make your first API call

Use Postman to send a simple GET request to verify your connection to Snowflake. Use the /system/health endpoint (or any other basic endpoint) and verify the response status is 200, confirming the Snowflake instance is healthy.

Final step: Create a simple data pipeline in three paces

1. Create a Table in Snowflake: Use the Snowflake REST API to send a SQL command that creates a new table.
2. Add data to the table: Insert some example data into your table using the API.
3. Retrieve from table: Use a SELECT query to fetch the data you just added.

Run these steps using Postman, and view the responses to confirm your pipeline is working!

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.

Your company, ElfCrypt Solutions, recently received an email from Snowflake announcing the upcoming enforcement of Multi-Factor Authentication (MFA) by default for all new accounts, as part of the BCR Bundle 2024_08. This update aims to strengthen security, particularly for human users (TYPE=PERSON) logging in with Snowflake’s built-in password authentication.

In light of this change, ElfCrypt Solutions has decided to proactively implement a custom authentication policy that enforces MFA for sensitive users and the entire account. As the Snowflake Security Administrator, you’ll apply these security configurations and delegate policy management responsibilities to another team role.

Challenge steps:

• Define the Custom Authentication Policy: Create an MFA-enforced authentication policy called ‘enhanced_auth_policy‘.
• Apply the Policy Across the Account: Set enhanced_auth_policy as the default authentication policy for the entire Snowflake account to require MFA for all users.
• Enforce the Policy for a Sensitive User: Assign enhanced_auth_policy to a specific user, Alicia, who requires stricter security controls.
• Create a Policy Management Role: Set up a role named policy_manager and grant it the APPLY AUTHENTICATION POLICY privilege, so team members can manage authentication policies without full security access.
• Verify the Policy Manager Role: Use the policy_manager role to apply enhanced_auth_policy to another sensitive user, Marco, to confirm that the role permissions are working as intended.
• Optional Reset: Remove enhanced_auth_policy from Alice and Bob if needed, and delete the policy from the account to restore default settings.

Take on this challenge to be the Celebrimbor of custom authentication policies in Snowflake—secure your data, empower your team, and become the go-to expert in Snowflake security.

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.

Imagine you’re working with a database that contains two primary tables: employees and departments. Each table has columns with associated comments that provide additional information about the data. Your goal is to manage and visualize these comments efficiently and automatically.

CREATE OR REPLACE TABLE employees (
    EMPLOYEE_ID INTEGER COMMENT 'The unique ID for each employee',
    NAME STRING COMMENT 'Full name of the employee',
    DEPARTMENT_ID INTEGER COMMENT 'ID of the department to which the employee belongs',
    SALARY FLOAT COMMENT 'Salary of the employee'
);

INSERT INTO employees (EMPLOYEE_ID, NAME, DEPARTMENT_ID, SALARY) VALUES
(1, 'Alice Smith', 101, 75000),
(2, 'Bob Johnson', 102, 80000),
(3, 'Carol Williams', 101, 72000),
(4, 'David Brown', 103, 68000),
(5, 'Emma Davis', 102, 73000);

CREATE OR REPLACE TABLE departments ( 
DEPARTMENT_ID INTEGER COMMENT 'Unique ID for the department', DEPARTMENT_NAME STRING COMMENT 'Name of the department', 
LOCATION STRING COMMENT 'Location where the department is based' );

INSERT INTO departments (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION) VALUES
(101, 'Engineering', 'San Francisco'),
(102, 'Marketing', 'New York'),
(103, 'HR', 'Chicago');

In this challenge, you’ll need to address the problem of keeping column comments updated and integrating them into a final view. This involves creating a system to store and continuously update column comments in an automated manner. Additionally, you’ll need to ensure that these comments are easily accessible and visible when combining data from different tables into a presentation view.

The end result should be a view that not only combines the data from the tables but also includes the updated comments, providing a comprehensive and informative overview.

With this challenge, you’ll add a powerful tool to your data management arsenal, enhancing both clarity and usability for anyone who interacts with the database. May the INFORMATION_SCHEMA be with you!

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.

Hey there, you salty sea dog! As the mighty captain of this pirate crew, you have amassed a treasure trove worthy of legend! Your booty, gathered from distant islands and fierce naval skirmishes, is a veritable mountain of riches, including a fine assortment of valuable trinkets and captured ships. But the time has come to make it easier for your crew to locate all the precious loot hidden in our vault.

To do this job, you’ll need to set up a search system using Snowflake’s Cortex search service so that your crew can quickly find any gems or pieces of gold they’re looking for among the pile.

CREATE OR REPLACE TABLE Treasures_Collected (
    Treasure_ID INT AUTOINCREMENT,
    Treasure_Name VARCHAR(255),
    Treasure_Type VARCHAR(255),
    Estimated_Value DECIMAL(10, 2),
    Discovery_Date DATE,
    Discovery_Island VARCHAR(255)
);

INSERT INTO Treasures_Collected (Treasure_Name, Treasure_Type, Estimated_Value, Discovery_Date, Discovery_Island) VALUES
('Golden Chest', 'Jewels', 5000.00, '1721-04-20', 'Tortuga'),
('Pearl Necklace', 'Jewels', 3000.00, '1720-08-15', 'Nassau'),
('Silver Coins', 'Coins', 1500.00, '1722-06-01', 'Cayman'),
('Royal Scepter', 'Relics', 7500.00, '1723-02-25', 'Bermuda'),
('King’s Sapphire', 'Precious Stones', 12000.00, '1723-09-20', 'Havana'),
('Ancient Map', 'Artifacts', 1800.00, '1721-11-12', 'Tortuga'),
('Emerald Ring', 'Jewels', 2500.00, '1721-06-25', 'Nassau'),
('Gold Goblet', 'Relics', 3000.00, '1722-07-18', 'Cayman'),
('Ivory Dagger', 'Weapons', 4000.00, '1723-05-30', 'Bermuda'),
('Opal Necklace', 'Jewels', 13000.00, '1723-12-15', 'Havana'),
('Bronze Statue', 'Artifacts', 2200.00, '1722-09-01', 'Tortuga'),
('Diamond Earrings', 'Jewels', 4000.00, '1721-10-10', 'Nassau'),
('Silver Chalice', 'Relics', 2800.00, '1722-04-12', 'Cayman'),
('Ancient Sword', 'Weapons', 5000.00, '1723-01-05', 'Bermuda'),
('Ruby Pendant', 'Jewels', 15000.00, '1723-08-22', 'Havana'),
('Ceremonial Mask', 'Artifacts', 1900.00, '1721-03-19', 'Tortuga'),
('Gold Bracelets', 'Jewels', 3500.00, '1722-02-10', 'Nassau'),
('Platinum Coins', 'Coins', 1800.00, '1722-11-14', 'Cayman'),
('Sapphire Ring', 'Jewels', 8000.00, '1723-07-07', 'Bermuda'),
('Emerald Necklace', 'Jewels', 16000.00, '1723-11-30', 'Havana');

Here’s what you need to do:

1. Create the search index: First, set up the treasure_search_service to catalog all our loot. This will help the crew quickly find every shiny piece and sparkling gem.
2. Check the search service: Once the index is ready, make sure the search service is in Bristol shape and fashion. (Here the author has gotten into character a bit too much). Run a preview query to locate all treasures of the type “jewelry.” Use the SNOWFLAKE.CORTEX.SEARCH_PREVIEW function to make sure everything is working properly.
3. Get specific: For a more precise search, run a preview query to find all the jewels looted from the island of “Nassau.” This will help your crew reach the right cache without any problems. (The intended result in the picture below).

Set sail on this quest and soon your crew will find its treasure like child’s play!

How fascinating are mermaid and pirate stories? Some would spend their entire summer vacations reading every word of these tales, while others would rather be swimming in the sea. For those who prefer the latter, having a summary of the story is a great reason to sit in front of the computer and use Cortex!

The goal of this challenge is to create a complete pipeline in Snowflake for processing PDF files, extracting and chunking their text content, embedding the text for further analysis, and generating summaries of the text chunks.

Setup steps:

• Create the environement: database, schema, warehouse.
• Define a Python function that given the file_url as parameter, it returns a table with a single column chunk of type VARCHAR. (Hint: check here , very carefully xd ).
• Create a stage with a Directory Table. (You can do that directly from Snowsight).
• Upload into the stage the three PDF files (you can find them here).

The goal is to have a table STORIES_SUMMARY_TABLE with three rows (as shown in the figure below), where for each story, you have the relative_path as story_title and the summary of the text that is produced by using a specific Cortex function (guess which one? The challenge’s title could be useful).

Stories about mermaids and pirates are undeniably fascinating. But can we say that it is equally amazing to create summaries of them in this way? Once you solve this challenge, the sea awaits, maybe you will find treasure!

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.