Uncategorized

Bienvenue à tous, it’s a pleasure to announce that on 5th October, at the Data Cloud World Tour Paris, thanks to the amazing hard work of Jade Le Van & Maxime Simon we’ll be launching Jeudis Givrés !

All of the Frosty Friday challenges will be translated into French and released on the following Thursday (for the sake of alliteration), followed by lots of community events to help boost those Parisian (and wider French) Snowflake skills!

To celebrate, we’re revisiting our Week 4 challenge on Spanish monarchs, but this time, in XML (which was partly invented by a Frenchman). Can you parse out the French monarchs?

create or replace file format frosty_parquet
type = 'parquet';

create or replace stage s3_stage
    url = 's3://frostyfridaychallenges'

create or replace table week64 as
select parse_xml($1:"DATA") as data
from @s3_stage/challenge_64/french_monarchs.parquet
    (file_format => frosty_parquet);

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.

Unearthing Joins That Cause a Stir!

We’ve all encountered that pesky join that unexpectedly multiplies or just runs amok. Previously, we’d delve deep into our datasets, trying to pinpoint the culprit. Now, let’s devise a function to streamline this detective work.

Your challenge this week: Design a function that takes a query ID and pinpoints any joins within it that produce more rows than the anticipated 1:1 ratio. To put it simply, if a join merges 10 rows from its left and 80 from its right, yet results in more than 80 rows, your function should highlight such discrepancies.

--Schema creation
create or replace schema week_63;

--T1 creation
CREATE TABLE t1 (value CHAR(1));

INSERT INTO t1
SELECT 'a' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS temp1

UNION ALL

SELECT 'b' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20) AS temp2

UNION ALL

SELECT 'c' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30) AS temp3

UNION ALL

SELECT 'd' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20) AS temp4

;

-- Create table t2 and insert one 'b' value
CREATE TABLE t2 (value CHAR(1));
INSERT INTO t2 (value) VALUES ('b');

-- Create table t3 and insert ten 'c' values
CREATE TABLE t3 (value CHAR(1));
INSERT INTO t3
SELECT 'c' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS temp;

-- Create table t4 and insert one 'd' value
CREATE TABLE t4 (value CHAR(1));
INSERT INTO t4 (value) VALUES ('d');

The query we’d like you to check :

SELECT *
FROM t1
    LEFT JOIN t2 ON t1.value = t2.value
    LEFT JOIN t3 ON t1.value = t3.value
    LEFT JOIN t4 ON t1.value = t4.value;

The result of your function should be similar to this :

Good luck!

Welcome to the Frosty Friday Challenge! I’m Dorian Banutoiu from canonicalized.com, and I’ve got an
exciting challenge for you.

Get ready to showcase your data wrangling skills and JSON manipulation prowess!

Challenge Details
Imagine you’re working at a Telecom company and your colleague Rick has sent you a spreadsheet
containing a list of products.

Your task is to clean up and transform this data into a Snowflake table and create a JSON file in a
specific format for Rick to upload to a third-party tool.

But there’s some cleaning up to do before the data can be used effectively. In his attempt to make the
spreadsheet pretty Rick merged some cells in the Brand column which of course will make your life
harder.

[Data File](s3://frostyfridaychallenges/challenge_61/Telecom Products – Sheet1.csv)

Tasks

Data Cleanup:
– Fill the null values in the “Brand” column with the value from the first non-empty row from
above (forward fill)
– Fill null values in the “Friendly URL” column with the corresponding URL from the second
column
– Remove rows with null values in the Category column.
JSON Object Creation:
– Transform the cleaned data into a nested JSON object using the following format

{
  "Category": {
    "Brand": [
      {"Product Name": "Friendly URL"}
    ],
  "Brand": [
    {"Product Name": "Friendly URL"},
    {"Product Name": "Friendly URL"}
  ]
},
  "Category": {
"Brand": [
    {"Product Name": "Friendly URL"}
  ]
},
...
}

Unload Data:
– Once the JSON object is created, unload the data into a JSON file to a Snowflake stage.
Send your colleague [a pre-signed link]() to the file.

Final Outputs:

Snowflake Table: A cleaned and transformed dataset that’s ready to be used by you and your
colleagues looking like the below screenshot

JSON File: A JSON file in the required format, suitable for Rick to upload into a third-party tool. Pay attention to the case of the Category column.

Tips

• Window functions should make it easy to forward fill empty values in Snowflake
• You can create a UDF to make it easy to covert the case of the Category values
• Server-side encryption is required on the unload stage to be able to generate a pre-signed URL
  Have fun!

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‘
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

Following week 57, we’re continuing with our SELECT options with REPLACE!
Given our start-up code below, we’d want you to do the following :
– subtract 1 from age (because the intern forgot that babies start at 0 , not 1)
– add 10% to everyone’s weight because the scale seemed to be off
– replace everyone’s gender to ‘unknown ‘ if it was marked ‘other’.

CREATE OR REPLACE TABLE measurements (
height INT,
weight INT,
age INT,
gender VARCHAR(),
id INT
);
insert into measurements (height, weight, age, gender, id) values (41, 178, 74, 'other', 1);
insert into measurements (height, weight, age, gender, id) values (188, 145, 87, 'other', 2);
insert into measurements (height, weight, age, gender, id) values (215, 725, 30, 'male', 3);
insert into measurements (height, weight, age, gender, id) values (159, 48, 116, 'female', 4);
insert into measurements (height, weight, age, gender, id) values (243, 204, 6, 'other', 5);
insert into measurements (height, weight, age, gender, id) values (232, 306, 30, 'male', 6);
insert into measurements (height, weight, age, gender, id) values (261, 602, 62, 'other', 7);
insert into measurements (height, weight, age, gender, id) values (143, 829, 113, 'female', 8);
insert into measurements (height, weight, age, gender, id) values (62, 190, 86, 'male', 9);
insert into measurements (height, weight, age, gender, id) values (249, 15, 73, 'male', 10);

The challenge is to do this with 1 query, with the end results looking like this :

(there might be a small trap hidden in our challenge this week so don’t get discouraged if you don’t get it on your first attempt)

Not every new feature had a big announcement at Summit 2023, but that doesn’t mean we can’t spotlight it!

This week we’ve got a puzzle for you to crack and without spoiling everything, let’s see if you can figure out the puzzle from the starting code :

-- database and schema creation
CREATE DATABASE F_F_WEEK_54;
CREATE SCHEMA WEEK_54;

--table creation
CREATE TABLE table_a (
id INT,
name VARCHAR,
age INT
);

CREATE TABLE table_b (
id INT,
name VARCHAR,
age INT
);

--data creation
INSERT INTO table_a (id, name, age)
VALUES
(1, 'John', 25),
(2, 'Mary', 30),
(3, 'David', 28),
(4, 'Sarah', 35),
(5, 'Michael', 32),
(6, 'Emily', 27),
(7, 'Daniel', 29),
(8, 'Olivia', 31),
(9, 'Matthew', 26),
(10, 'Sophia', 33),
(11, 'Jacob', 24),
(12, 'Emma', 29),
(13, 'Joshua', 32),
(14, 'Ava', 30),
(15, 'Andrew', 28),
(16, 'Isabella', 34),
(17, 'James', 27),
(18, 'Mia', 31),
(19, 'Logan', 25),
(20, 'Charlotte', 29);

--role creation
CREATE ROLE week_54_role;
GRANT ROLE week_54_role to user <user_name> ;
GRANT USAGE ON database F_F_WEEK_54 TO ROLE week_54_role;
GRANT USAGE ON schema WEEK_54 TO ROLE week_54_role;
GRANT SELECT ON ALL TABLES IN SCHEMA F_F_WEEK_54.WEEK_54 TO ROLE week_54_role;
GRANT INSERT ON ALL TABLES IN SCHEMA F_F_WEEK_54.WEEK_54 TO ROLE week_54_role;
GRANT USAGE ON WAREHOUSE TO ROLE week_54_role;

As you can see, we create 2 simple tables and a new role that can select and insert from these tables. We now wish to use that role to create and execute a stored procedure to move data from table A to table B. HOWEVER, the role that we’ve created doesn’t have the CREATE PROCEDURE privilege!

The challenge for today is to create and execute the following procedure without granting the CREATE PROCEDURE privilege:

CREATE OR REPLACE copy_to_table(fromTable STRING, toTable STRING, count INT) RETURNS STRING LANGUAGE PYTHON RUNTIME_VERSION = '3.8' PACKAGES = ('snowflake-snowpark-python') HANDLER = 'copyBetweenTables' AS $$ def copyBetweenTables(snowpark_session, fromTable, toTable, count): snowpark_session.table(fromTable).limit(count).write.mode("append").save_as_table(toTable) return "Success" $$ ;

You solution should keep the procedure intact, and this piece of code needs to be part of your solution:

CALL copy_to_table('table_a', 'table_b', 5);

One of the quieter releases into public preview is that we can now infer schemas for CSVs. To test this follow the following instructions:

1. Create an internal stage
   
   create stage frosty_stage;
   
2. Create a csv file format
   create or replace file format frosty_csv
field_optionally_enclosed_by = '"'
skip_header = 1;
   
3. PUT the following csv file into that internal stage. Download Here

Now you need to infer the schema and produce the following:

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‘
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