Mike Droog

by Mike Droog on 2024-11-29 Leave a Comment

Week 121 – Basic

It’s time to play an old children’s game : duck-duck-goose !

A lot of us know tables that are so wide, it’s hard to navigate. If these tables hold an error , finding where that error actually IS , is very hard to do.
That is , until Snowflake made it easy for you with a new Function that we can use.

To start this challenge , create the data with this :

Start-up code

create or replace schema week121;

CREATE OR REPLACE TABLE DucksAndGeese (
id INT PRIMARY KEY,
column1 VARCHAR(10),
column2 VARCHAR(10),
column3 VARCHAR(10),
column4 VARCHAR(10),
column5 VARCHAR(10),
column6 VARCHAR(10),
column7 VARCHAR(10),
column8 VARCHAR(10),
column9 VARCHAR(10),
column10 VARCHAR(10),
column11 VARCHAR(10),
column12 VARCHAR(10),
column13 VARCHAR(10),
column14 VARCHAR(10),
column15 VARCHAR(10),
column16 VARCHAR(10),
column17 VARCHAR(10),
column18 VARCHAR(10),
column19 VARCHAR(10),
column20 VARCHAR(10),
column21 VARCHAR(10),
column22 VARCHAR(10),
column23 VARCHAR(10),
column24 VARCHAR(10),
column25 VARCHAR(10),
column26 VARCHAR(10),
column27 VARCHAR(10),
column28 VARCHAR(10),
column29 VARCHAR(10),
column30 VARCHAR(10),
column31 VARCHAR(10),
column32 VARCHAR(10),
column33 VARCHAR(10),
column34 VARCHAR(10),
column35 VARCHAR(10),
column36 VARCHAR(10),
column37 VARCHAR(10),
column38 VARCHAR(10),
column39 VARCHAR(10),
column40 VARCHAR(10),
column41 VARCHAR(10),
column42 VARCHAR(10),
column43 VARCHAR(10),
column44 VARCHAR(10),
column45 VARCHAR(10),
column46 VARCHAR(10),
column47 VARCHAR(10),
column48 VARCHAR(10),
column49 VARCHAR(10),
column50 VARCHAR(10),
column51 VARCHAR(10),
column52 VARCHAR(10),
column53 VARCHAR(10),
column54 VARCHAR(10),
column55 VARCHAR(10),
column56 VARCHAR(10),
column57 VARCHAR(10),
column58 VARCHAR(10),
column59 VARCHAR(10),
column60 VARCHAR(10),
column61 VARCHAR(10),
column62 VARCHAR(10),
column63 VARCHAR(10),
column64 VARCHAR(10),
column65 VARCHAR(10),
column66 VARCHAR(10),
column67 VARCHAR(10),
column68 VARCHAR(10),
column69 VARCHAR(10),
column70 VARCHAR(10),
column71 VARCHAR(10),
column72 VARCHAR(10),
column73 VARCHAR(10),
column74 VARCHAR(10),
column75 VARCHAR(10),
column76 VARCHAR(10),
column77 VARCHAR(10),
column78 VARCHAR(10),
column79 VARCHAR(10),
column80 VARCHAR(10),
column81 VARCHAR(10),
column82 VARCHAR(10),
column83 VARCHAR(10),
column84 VARCHAR(10),
column85 VARCHAR(10),
column86 VARCHAR(10),
column87 VARCHAR(10),
column88 VARCHAR(10),
column89 VARCHAR(10),
column90 VARCHAR(10),
column91 VARCHAR(10),
column92 VARCHAR(10),
column93 VARCHAR(10),
column94 VARCHAR(10),
column95 VARCHAR(10),
column96 VARCHAR(10),
column97 VARCHAR(10),
column98 VARCHAR(10),
column99 VARCHAR(10),
column100 VARCHAR(10)
);

INSERT INTO DucksAndGeese VALUES
(1, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(2, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(3, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(4, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(5, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(6, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(7, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(8, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Goose', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(9, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(10, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck');

Your challenge for this week is to find the line of code that hides a goose !
Enjoy finding it and remember to post your code 🙂

Remember if you want to participate:

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)
Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
Post the URL in the comments of the challenge.

by Mike Droog on 2024-11-08 Leave a Comment

Week 118 – Hard

The Intern’s Ambitious Leap

Professor Frosty was sipping his hot cocoa in the cozy glow of his workstation when The Intern burst into the room, eyes gleaming with excitement.

“Professor!” he exclaimed, barely able to contain himself, “I’m ready for something bigger. I want to take on a real data challenge—one that involves more than just table tweaks.”

The Professor raised an eyebrow, a grin playing at the corners of his mouth. “Ah, feeling ambitious, are we? Well then, let’s give you something with a bit of a twist.”

Professor Frosty leaned back in his chair, contemplating. “How about a task that will keep Snowflake on its toes and gets a bit of automation into the mix?”

The Intern’s face lit up, and without further ado, Professor Frosty shared the details:

The Mission: Your task is to create a Snowflake table that stays in sync with a YAML file hosted on GitHub. Whenever that file changes, your table will update automatically—think of it as Snowflake magic, powered by a touch of automation.

Your Journey:

1. Set the Scene: Start by creating a Snowflake table called employee_data. This table should hold two columns: first_name and department_id.

2. Populate Your World: Fill in some sample entries to bring employee_data to life.

3. Forge the Link: Create a config.yaml file on GitHub that mirrors the structure of your employee_data table, with the same columns and data.

4. Conjure the Script: Write a Python script that reads config.yaml from GitHub and updates the contents to your Snowflake table. This script will be the bridge between your GitHub-hosted YAML file and Snowflake.

5. Automate the Magic: Set up a GitHub Action to trigger your script every time the YAML file is updated. With this, your Snowflake table will stay in sync with each change—no manual updates needed!

The Intern’s eyes widened as he scribbled down notes. This was exactly the kind of challenge he had hoped for. With a final nod from Professor Frosty, he raced back to his desk, ready to bring this automation to life.

Good luck, Intern! May your code be clean, your YAML well-structured, and your automation flawless!

Remember if you want to participate:

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)
Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
Post the URL in the comments of the challenge.

by Mike Droog on 2024-10-18 2 Comments

Week 115 – Intermediate

We all have tools at our disposal that are very powerful but go unused for a long time. A tool that once we stop using it, we start losing our ability to understand how it worked.

The tool somewhere near the top of our list that follows that reference is Regular Expressions (or RegEx for short).
To give everyone the opportunity to stretch those muscles again , we’ve got 4 RegEx challenge for you to tackle!

Challenge 1: Validate Complex Email Addresses

Objective: Create a query that uses REGEXP_LIKE to validate complex email addresses.

Pattern Requirements:

Must begin with alphanumeric characters (a-z, A-Z, 0-9).
May include dots (.) or hyphens (-), but these cannot be at the start or end.
The domain should have two parts separated by a dot: the domain name and a valid top-level domain (TLD).
TLD must be 2 to 6 letters long.

Start-up Code

CREATE TABLE users (
user_id INT AUTOINCREMENT ,
email VARCHAR
);

INSERT INTO users (email) VALUES
('john.doe@example.com'),
('invalid-email@.com'),
('alice_smith@sub-domain.co.uk'),
('bob@domain.com'),
('user@invalid_domain@com');

Challenge 2: Extract Valid Dates in Multiple Formats

Objective: Use REGEXP_SUBSTR to extract dates in different formats from a string column, assuming the dates follow one of these formats:

DD/MM/YYYY
MM-DD-YYYY
YYYY.MM.DD

Start-up Code

CREATE TABLE documents (
doc_id INT AUTOINCREMENT PRIMARY KEY,
text_column VARCHAR(500)
);

INSERT INTO documents (text_column) VALUES
('This document was created on 15/03/2023.'),
('The report is due by 04-15-2022.'),
('Version 1.0 released on 2021.08.30.'),
('No date provided in this text.'),
('Invalid date 32/13/2020.');

Challenge 3: Mask Credit Card Numbers

Objective: Use REGEXP_REPLACE to mask all but the last 4 digits of credit card numbers in a string. The credit card numbers can be in the format XXXX-XXXX-XXXX-XXXX or XXXXXXXXXXXX.

Start-up Code

CREATE TABLE transactions (
transaction_id INT AUTOINCREMENT PRIMARY KEY,
card_number VARCHAR(50)
);
INSERT INTO transactions (card_number) VALUES
('1234-5678-9012-3456'),
('9876 5432 1098 7654'),
('1111222233334444'),
('4444-3333-2222-1111'),
('Invalid number 12345678901234567');

Challenge 4: Extract Hashtags from a Text Block

Objective: Use RegEx to extract all hashtags from a string, where hashtags:

Begin with #
Are followed by alphanumeric characters or underscores but no spaces.

Start-up Code

CREATE TABLE social_posts (
post_id INT AUTOINCREMENT PRIMARY KEY,
text_column VARCHAR(500)
);
INSERT INTO social_posts (text_column) VALUES
('Check out our new product! #launch #excited'),
('Loving the weather today! #sunnyDay #relax'),
('Follow us at #example_page for more updates!'),
('No hashtags in this sentence.'),

by Mike Droog on 2024-09-27 2 Comments

Week 112 – Intermediate

In celebration of the upcoming Snowflake World Tours, let’s embark on an exciting challenge inspired by the tour itself!

While geographic features in Snowflake might be a bit underutilized, there’s still so much potential to create something truly remarkable with them. Here’s the challenge: using the provided code, craft a polygon representing the regions from the Snowflake World Tour, organized by the event dates.

Let’s showcase the power of Snowflake and make something special together!

Start-up code

CREATE OR REPLACE TABLE event_schedule_geo (
Region VARCHAR,
City VARCHAR,
Event_Date DATE,
GPS_Coordinates VARCHAR,
Geo_Point GEOGRAPHY
);

INSERT INTO event_schedule_geo (Region, City, Event_Date, Geo_Point)
SELECT
‘Americas’, ‘Atlanta’, ‘2024-10-03’, TO_GEOGRAPHY(‘POINT(-84.3880 33.7490)’)
UNION ALL
SELECT
‘Americas’, ‘Bogotá’, ‘2024-10-30’, TO_GEOGRAPHY(‘POINT(-74.0721 4.7110)’)
UNION ALL
SELECT
‘Americas’, ‘Chicago’, ‘2024-11-04’, TO_GEOGRAPHY(‘POINT(-87.6298 41.8781)’)
UNION ALL
SELECT
‘Americas’, ‘Dallas’, ‘2024-10-01’, TO_GEOGRAPHY(‘POINT(-96.7970 32.7767)’)
UNION ALL
SELECT
‘Americas’, ‘Mexico City’, ‘2024-10-24’, TO_GEOGRAPHY(‘POINT(-99.1332 19.4326)’)
UNION ALL
SELECT
‘Americas’, ‘New York City’, ‘2024-10-15’, TO_GEOGRAPHY(‘POINT(-74.0060 40.7128)’)
UNION ALL
SELECT
‘Americas’, ‘São Paulo’, ‘2024-10-08’, TO_GEOGRAPHY(‘POINT(-46.6333 -23.5505)’)
UNION ALL
SELECT
‘Americas’, ‘Toronto’, ‘2024-10-21’, TO_GEOGRAPHY(‘POINT(-79.347015 43.651070)’)

UNION ALL
SELECT
‘EMEA’, ‘Amsterdam’, ‘2024-10-03’, TO_GEOGRAPHY(‘POINT(4.9041 52.3676)’)
UNION ALL
SELECT
‘EMEA’, ‘Berlin’, ‘2024-10-16’, TO_GEOGRAPHY(‘POINT(13.4050 52.5200)’)
UNION ALL
SELECT
‘EMEA’, ‘London’, ‘2024-10-10’, TO_GEOGRAPHY(‘POINT(-0.1278 51.5074)’)
UNION ALL
SELECT
‘EMEA’, ‘Paris’, ‘2024-10-01’, TO_GEOGRAPHY(‘POINT(2.3522 48.8566)’)
UNION ALL
SELECT
‘EMEA’, ‘Stockholm’, ‘2024-10-17’, TO_GEOGRAPHY(‘POINT(18.0686 59.3293)’)

UNION ALL
SELECT
‘APJ’, ‘Kuala Lumpur’, ‘2024-10-23’, TO_GEOGRAPHY(‘POINT(101.6869 3.1390)’)
UNION ALL
SELECT
‘APJ’, ‘Mumbai’, ‘2024-10-04’, TO_GEOGRAPHY(‘POINT(72.8777 19.0760)’)
UNION ALL
SELECT
‘APJ’, ‘Auckland’, ‘2024-10-24’, TO_GEOGRAPHY(‘POINT(174.7633 -36.8485)’)
UNION ALL
SELECT
‘APJ’, ‘Manila’, ‘2024-10-02’, TO_GEOGRAPHY(‘POINT(120.9842 14.5995)’)
UNION ALL
SELECT
‘APJ’, ‘Sydney’, ‘2024-10-29’, TO_GEOGRAPHY(‘POINT(151.2093 -33.8688)’);

by Mike Droog on 2024-09-06 1 Comment

Week 109 – Intermediate

This challenge centers around leveraging Snowflake’s stored procedure in the FROM clause of a SELECT statement to return tabular data dynamically based on an employee’s department.

Imagine you are a Snowflake developer at a company that needs to generate department-specific employee performance reports on-demand. Your goal is to implement a stored procedure that dynamically returns the performance data for employees based on the selected department and use it in a query.

Story Scenario:

Your company, PerformanceMetrics Inc., has a new initiative where managers want to review employee performance metrics for their respective departments without running complex queries directly. To streamline this, you are tasked with creating a stored procedure that retrieves employee performance data based on department input. Your job is to encapsulate the logic in a stored procedure that allows managers to query department-specific employee data dynamically using a SELECT statement.

As you engage with this challenge, you’ll focus on using the TABLE() function to call a stored procedure in the FROM clause of a SELECT statement, passing in department names as input.

Task 1: Create a sample database and a table named employee_performance containing columns for employee ID, name, department, performance score, and review date. Populate this table with sample data for testing (use the Start-up Code below for this)

Start-up Code

CREATE OR REPLACE TABLE employee_performance (
emp_id INT,
emp_name VARCHAR,
department VARCHAR,
performance_score NUMBER(5,2),
review_date DATE
);

INSERT INTO employee_performance VALUES
(101, 'Alice Smith', 'Sales', 85.50, '2024-08-30'),
(102, 'Bob Johnson', 'HR', 92.75, '2024-08-29'),
(103, 'Charlie Davis', 'Sales', 88.00, '2024-08-28'),
(104, 'Dana Lee', 'Engineering', 95.20, '2024-08-27'),
(105, 'Eli White', 'HR', 78.90, '2024-08-26');

Task 2: Create a stored procedure named get_employee_performance_by_department that accepts a department name as input and returns a table with employee ID, name, performance score, and review date for all employees in that department.

You’re working towards. a result similar to this :

Task 3 : Run a SELECT query that uses the TABLE() function to call the stored procedure in the FROM clause and retrieve the employee performance data for the Sales department.

by Mike Droog on 2024-08-16 Leave a Comment

Week 106 – Intermediate

Challenge: Implementing Dynamic Data Masking in Snowflake

This week’s focus centers around leveraging Snowflake’s Dynamic Data Masking feature to protect sensitive information based on user roles. Imagine you are a data engineer at a company where data privacy and role-based access control are crucial for compliance and security. Your mission is to implement dynamic data masking to ensure that sensitive data is properly masked based on the user’s role while allowing necessary data access for different business functions.

Story Scenario:

Your company, SecureDataCorp, recently underwent a security audit that highlighted the need for better data masking practices to protect sensitive customer information. The audit revealed that sensitive data such as credit card numbers, email addresses, and account balances were accessible to users without appropriate clearance. Your task is to implement Snowflake’s Dynamic Data Masking to ensure that only authorized users can view sensitive information while others see masked data.

As you engage with this challenge, consider the following tasks:

Detailed Tasks:

Initial Setup:

Task: Create a sample database and a table named customer_data containing columns for customer ID, name, email, phone, address, credit card number, and account balance. Populate this table with sample data.

Start-up Code

CREATE DATABASE dynamic_data_masking_db;
USE DATABASE dynamic_data_masking_db;

CREATE TABLE customer_data (
    customer_id INTEGER,
    name STRING,
    email STRING,
    phone STRING,
    address STRING,
    credit_card_number STRING,
    account_balance FLOAT
);

INSERT INTO customer_data (customer_id, name, email, phone, address, credit_card_number, account_balance) VALUES
(1, 'John Doe', 'john.doe@example.com', '123-456-7890', '123 Main St', '4111111111111111', 15000.00),
(2, 'Jane Smith', 'jane.smith@example.com', '234-567-8901', '456 Elm St', '4222222222222222', 8500.00),
(3, 'Alice Johnson', 'alice.johnson@example.com', '345-678-9012', '789 Oak St', '4333333333333333', 3000.00),
(4, 'Bob Brown', 'bob.brown@example.com', '456-789-0123', '101 Pine St', '4444444444444444', 500.00),
(5, 'Charlie Davis', 'charlie.davis@example.com', '567-890-1234', '202 Maple St', '4555555555555555', 12000.00),
(6, 'Diana Evans', 'diana.evans@example.com', '678-901-2345', '303 Cedar St', '4666666666666666', 2000.00),
(7, 'Frank Green', 'frank.green@example.com', '789-012-3456', '404 Birch St', '4777777777777777', 30000.00),
(8, 'Hannah White', 'hannah.white@example.com', '890-123-4567', '505 Willow St', '4888888888888888', 4500.00),
(9, 'Ian Black', 'ian.black@example.com', '901-234-5678', '606 Aspen St', '4999999999999999', 7500.00),
(10, 'Jill Blue', 'jill.blue@example.com', '012-345-6789', '707 Cherry St', '4000000000000000', 500.00);

Define Roles and Users:

Task: Create three roles: admin, manager, and analyst. Assign them to these roles to simulate different levels of data access within the organization (You don’t have to create new users as long as you don’t have secondary_roles enabled).

Implement Masking Policies:

Task: Implement masking policies for the credit_card_number, email, and account_balance columns with the following logic:

For analyst role: Fully mask the credit card number, mask the domain part of the email, and mask the account balance.
For manager role: Partially mask the credit card number (showing only the last 4 digits), mask the domain part of the email, and partially mask the account balance by rounding it.
For admin role: No masking is applied; the admin should see the full data.

Apply and Test Masking Policies:

Task: Apply the masking policies to the customer_data table. Test the implementation by querying the table as each role (admin, manager, analyst) and verify that the masking policies are applied correctly.

Steps to Complete the Challenge:

Initial Setup:
- Use the provided start-up code to create the customer_data table and insert sample data.
Define Roles and Users:
- Create roles: admin, manager, analyst.
- Create users (optional) and assign them to respective roles.
Implement Masking Policies:
- Create and apply masking policies for the credit_card_number, email, and account_balance columns as per the specified logic.
Apply and Test Masking Policies:
- Verify that the masking policies are correctly applied by querying the customer_data table as different users with admin, manager, and analyst roles.

By the end of this challenge, you will have implemented role-based dynamic data masking in Snowflake, ensuring that sensitive data is protected and only visible to users with appropriate access.

by Mike Droog on 2024-07-26

Week 103 – Easy

Challenge: Using Time Travel and Cloning in Snowflake

This week’s focus centers around leveraging Snowflake’s Time Travel and Cloning features for data management and recovery. Imagine you are a data engineer at a company where data integrity is crucial for day-to-day operations. A series of unexpected changes have been made to the database, and it’s your job to ensure the data is accurate and consistent.

Story Scenario:

Your company, DataCorp, recently encountered several incidents that impacted the integrity of the data in the sales database. Over the past week, multiple updates and deletions were performed on the transactions table. Your mission is to use Snowflake’s Time Travel and Cloning features to recover and verify the data at different points in time and ensure the smooth operation of your data processes.

As you engage with this challenge, consider the following tasks:

Initial Data Incident:
- Task: Restore the transactions table to its state from three days ago, before the first series of unintended updates occurred. Verify the data integrity by comparing it to a backup file and correct any discrepancies.
Subsequent Data Incident:
- Task: Identify and restore the transactions table to its state from yesterday morning, just before a significant row deletion was performed. Verify the number of rows and specific key transactions to ensure accuracy.
Recent Data Correction:
- Task: Roll back the transactions table to its state from six hours ago to undo a batch process that accidentally duplicated records. Ensure that no duplicate records exist after the rollback.
Creating a Safe Testing Environment:
- Task: Create a clone of the entire sales database from its state a week ago. This clone will be used for testing new features without affecting the production database. Verify that the cloned database includes all tables and data as of the specific point in time.

Start-up Code

CREATE TABLE sales.transactions ( id INT, customer_name STRING, amount DECIMAL(10,2), transaction_date TIMESTAMP );

INSERT INTO sales.transactions (id, customer_name, amount, transaction_date) VALUES (1, 'Alice', 100.00, '2024-07-20 10:00:00'), (2, 'Bob', 200.00, '2024-07-20 11:00:00'), (3, 'Charlie', 300.00, '2024-07-20 12:00:00');

Below is the code to simulate our problems:

Simulate issues

#Issue 1 (three days ago):
UPDATE sales.transactions SET amount = amount * 1.1 WHERE id = 1;
DELETE FROM sales.transactions WHERE id = 2;

#Issue 2 (Yesterday) :
DELETE FROM sales.transactions WHERE id = 3;

#Issue 3 (6 hours ago):
INSERT INTO sales.transactions (id, customer_name, amount, transaction_date)
SELECT id, customer_name, amount, transaction_date FROM sales.transactions;

Remember if you want to participate:

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)
Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
Post the URL in the comments of the challenge.

by Mike Droog on 2024-07-05 6 Comments

Week 100 – Hard

🎉Celebrating 100 Challenges! 🎉

It’s been nearly two incredible years, and we are ecstatic to announce that we’ve reached our 100th challenge milestone!

The Frosty_Friday team is amazed at the vibrant community that has blossomed around F_F. We’ve met some truly amazing people, and the support we’ve received from Snowflake has been phenomenal.

When we embarked on this journey, we never could have dreamed that Professor Frosty and The Intern would have their very own booth at the Summit!

To celebrate our 100th challenge in grand style, we reached out to the creative minds at Snowflake for some especially spicy challenge ideas. And let’s just say—they’ve delivered beyond our wildest expectations.

Among the many fantastic ideas we received, one particularly stood out. Dash, the Lead Developer Advocate at Snowflake, proposed a brilliant concept that combines the best of both worlds: old and new, with a splash of Streamlit added for extra flavor.

Here’s your task to join the celebration:

Using the startup code provided below, you’ll create a table with 2 columns: FILE_NAME and IMAGE_BYTES.

Startup Code

create or replace table images (file_name string, image_bytes string);

IMAGE_BYTES is going to hold the hex-encoded images that we’re going to share with you in our csv below. We want you to showcase these images in Streamlit and ofcourse , it’s up to you to bring these images to life and display them in your Streamlit app!

Find the actual data right here in our S3 bucket : csv file

We can’t wait to see the creative solutions you’ll come up with. Let’s make this 100th challenge one to remember!

Happy coding and here’s to many more challenges together! 🥳

Remember if you want to participate:

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)
Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
Post the URL in the comments of the challenge.

by Mike Droog on 2024-06-01 Leave a Comment

Snowpark ML – Basic

Just like any savvy Intern, our hero is always on the hunt for a better and more affordable place to live. With his shiny new ML tools, he’s on a mission to figure out what a house should be worth based on square footage and the number of bedrooms.

Can you join him in this adventure and help with his calculations using Snowpark ML?

You’ll find all the property information you need in this S3 bucket: s3://frostyfridaychallenges/summit24_snowpark/

Inside the bucket, you’ll discover three files: one Control file and two Test files. The Control file is meant to train your model, while the Test files are for making predictions. We’ve split the Test files into two: one with actual prices and one without. This setup is designed to give you more options and make your task easier if you choose to use it.

by Mike Droog on 2024-05-31 Leave a Comment

Cortex – Basic

Once upon a time, a few years ago, our Intern didn’t kick off his career with the legendary Professor Frosty. Instead, he embarked on his data journey with the incredible Data Superheroes!

While he wasn’t out there battling villains face-to-face, he played a crucial role in the ‘good fight’ by performing pristine data analysis. His mission? To uncover why villains were striking on specific dates!

Now, it’s your turn to step into the Intern’s shoes. Below is the startup code to see the crimerates of the city where our Intern was working. Can you crack the code and help the Data Superheroes keep the city safe?

Startup Code

-- Create the table with an additional date column
CREATE OR REPLACE TABLE base_data (
    id INT AUTOINCREMENT,
    value INT,
    date TIMESTAMP_NTZ
);

-- Define the start date as a constant
SET start_date = '2020-01-01';

-- Insert 1000 records with values between 1 and 5 and increasing daily dates
INSERT INTO base_data (value, date)
WITH seq AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1 AS i
    FROM 
        TABLE(GENERATOR(ROWCOUNT => 1000))
)
SELECT 
    UNIFORM(1, 5, RANDOM())::INT, 
    DATEADD(DAY, i, to_date($start_date))
FROM seq;

-- Insert 100 records with values between 1 and 5 and increasing daily dates
INSERT INTO data_to_inspect (value, date)
WITH seq AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1 AS i
    FROM 
        TABLE(GENERATOR(ROWCOUNT => 1000))
)
SELECT 
    UNIFORM(1, 5, RANDOM())::INT, 
    DATEADD(DAY, i, to_date($start_date))
FROM 
    seq;

-- Insert 2 records with values greater than 35 and increasing daily dates
INSERT INTO data_to_inspect (value, date)
VALUES
(36, DATEADD(DAY, 1000, to_date($start_date))),
(37, DATEADD(DAY, 1001, to_date($start_date)));

2 dates sprang out to the Intern , can you verify his calculations and point out which 2 dates stood out using anomaly detection (The Intern also specified that IS_ANOMALY might not be the only column you’d need to be sure)?