Uncategorized – Page 4

by Christopher Marland on 2022-08-12 6 Comments

Week 9 – Intermediate

STORY
It’s not just bad guys that need to guard their secrets!
Superheroes are our first line of defence against those evil-doers so we really need to protect their information.
Running a superhero organisation however is a big job so we’ve got a lot of people that have access to our systems and we need to make sure that the true identity of our heroes is never revealed!
HR is advocating for a more personal touch to our business though and has requested that some staff should be able to see the first names of the superheroes to connect on a more basic level. Higher ups should still be able to see everything !
With the constant changing roles within the organisation , we’d really like something that’s dynamic and can handle roles that haven’t been created yet.

CHALLENGE

With the use of Tags and Masking , we want to mask the first_name and last_name columns from our data_to_be_masked table.
We want the following :

The default user that has access can only see the hero_name data unmasked
Role foo1 can only see hero_name and first_name
Role foo2 can see the contents of the whole table
The used masking policy should NOT use a role checking feature. (current_role = … etc.)

START UP CODE

--CREATE DATA
CREATE OR REPLACE TABLE data_to_be_masked(first_name varchar, last_name varchar,hero_name varchar);
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Eveleen', 'Danzelman','The Quiet Antman');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Harlie', 'Filipowicz','The Yellow Vulture');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Mozes', 'McWhin','The Broken Shaman');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Horatio', 'Hamshere','The Quiet Charmer');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Julianna', 'Pellington','Professor Ancient Spectacle');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Grenville', 'Southouse','Fire Wonder');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Analise', 'Beards','Purple Fighter');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Darnell', 'Bims','Mister Majestic Mothman');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Micky', 'Shillan','Switcher');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Ware', 'Ledstone','Optimo');

--CREATE ROLE
CREATE ROLE foo1;
CREATE ROLE foo2;
GRANT ROLE foo1 TO USER <ENTER USERNAME HERE>;
GRANT ROLE foo2 TO USER <ENTER USERNAME HERE>;

RESULTS

–Can you see the data?

USE ROLE ACCOUNTADMIN;
SELECT * FROM data_to_be_masked;

–What can foo1 see?

USE ROLE foo1;


SELECT * FROM data_to_be_masked;

–And foo2?

USE ROLE foo2;


SELECT * FROM data_to_be_masked;

ADMIN NOTE :
This challenge has been clarified on 30-09-2022 and the last bullet point for the challenge was added.
Any solutions given before this time might not be a total solution to the question.

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 Christopher Marland on 2022-08-05 8 Comments

Week 8 – Basic

Whilst, as of the time of writing, the Snowflake-Streamlit integration isn’t here yet, FrostyFriday sees that as only more reason to get ahead of the curve and start developing those Streamlit skills.

While Streamlit is Python-based, and we encourage you to learn Python, this challenge is Python-optional. The skeleton script below should mean you can do this challenge without any Python knowledge.

For a guide on getting started, head over here.

So…what’s the challenge?

Well, a company has a nice and simple payments fact table that you can find here. They want FrostyData to help them by ingesting the data and creating the below line chart.

Result:

The script must not expose passwords, as this would be very unsafe, instead, it should use Streamlit secrets.
The title must be “Payments in 2021”.
It must have a ‘min date’ filter which specifies the earliest date a user can select, by default this should be set to the earliest date possible.
It must have a ‘max date’ filter which specifies the latest date a user can select, by default this should be set to the latest date possible.
It should have a line chart with dates on the X axis, and amount on the Y axis. The data should be aggregated at the weekly level.

Skeleton Script for non-Python Users:

import streamlit as st
import pandas as pd
import snowflake.connector


# Normally, a secrets file should be saved in C:\Users\<your_user>\.streamlit
# as secrets.toml
ctx = snowflake.connector.connect(
    user="""<enter username here using a secrets.toml file>""",
    password="""<enter password here using a secrets.toml file>""",
    account="""<enter account here using a secrets.toml file>"""
    )
cs = ctx.cursor()

# WARNING - When aggregating columns in this query, keep the column names the same.
query = """<enter SQL here>"""


@st.cache # This keeps a cache in place so the query isn't constantly re-run.
def load_data():
    """
    In Python, def() creates a function. This particular function connects to your Snowflake
    account and executes the query above. If you have no Python experience, I recommend leaving
    this alone.
    """
    cur = ctx.cursor().execute(query)
    payments_df = pd.DataFrame.from_records(iter(cur), columns=[x[0] for x in cur.description])
    payments_df['PAYMENT_DATE'] = pd.to_datetime(payments_df['PAYMENT_DATE'])
    payments_df = payments_df.set_index('PAYMENT_DATE')
    return payments_df


payments_df = load_data() # This creates what we call a 'dataframe' called payments_df, think of this as
                            # a table. To create the table, we use the above function. So, basically,
                            # every time your write 'payments_df' in your code, you're referencing
                            # the result of your query.

def get_min_date():
    """
    This function returns the earliest date present in the dataset.
    When you want to use this value, just write get_min_date().
    """
    return min(payments_df.index.to_list()).date()

def get_max_date():
    """
    This function returns the latest date present in the dataset.
    When you want to use this value, just write get_max_date().
    """
    return max(payments_df.index.to_list()).date()


def app_creation():
    """
    This function is the one your need to edit. 
    """
    # <Create a title here>
    min_filter = # <Create a slider for the min date>
    max_filter = # <Create a slider for the max date>
    mask = (payments_df.index >= pd.to_datetime("""<your minimum filter should go here>""")) \
             & (payments_df.index <= pd.to_datetime("""<your maximum filter should go here>"""))
    payments_df_filtered = payments_df.loc[mask] #This line creates a new dataframe (table) that filters
                                                    # your results to between the range of your min
                                                    # slider, and your max slider.
    # Create a line chart using the new payments_df_filtered dataframe. 

app_creation() # The function above is now invoked.

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 Christopher Marland on 2022-07-29 8 Comments

Week 7 – Intermediate

Being a villain is hard enough as it is and data issues aren’t only a problem for the good guys. Villains have got a lot of overhead and information to keep track of and EVIL INC. has started using Snowflake for it’s needs.

However , you’ve noticed that the most important part of your superweapons have been leaked : The catch-phrase!

Fortunately , you’ve set up tagging to allow you to keep track of how accessed what information!

Your challenge is to figure out who accessed data that was tagged with “Level Super Secret A+++++++”

Because it might be a bit too difficult to create users to access the data, we’re using roles instead of users.

The following is the preliminary code we want you to run before the challenge. Note that account_usage takes 2 hours to update, so we suggest running the below code and then coming back to the challenge at least a couple of hours later.

If you want to create a new database and schema for this challenge…

-- Create Database
create or replace database FF_WEEK_7;
create or replace warehouse compute_wh with warehouse_size='X-SMALL';
use database FF_WEEK_7;

-- Create Schemas
create schema super_weapons;
create schema super_monsters;
create schema super_villains;

-- Create Tables and Mock data
create or replace table super_villains.villain_information (
	id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(50),
	Alter_Ego VARCHAR(50)
);
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (1, 'Chrissy', 'Riches', 'criches0@ning.com', 'Waterbuck, defassa');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (2, 'Libbie', 'Fargher', 'lfargher1@vistaprint.com', 'Ibis, puna');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (3, 'Becka', 'Attack', 'battack2@altervista.org', 'Falcon, prairie');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (4, 'Euphemia', 'Whale', 'ewhale3@mozilla.org', 'Egyptian goose');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (5, 'Dixie', 'Bemlott', 'dbemlott4@moonfruit.com', 'Eagle, long-crested hawk');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (6, 'Giffard', 'Prendergast', 'gprendergast5@odnoklassniki.ru', 'Armadillo, seven-banded');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (7, 'Esmaria', 'Anthonies', 'eanthonies6@biblegateway.com', 'Cat, european wild');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (8, 'Celine', 'Fotitt', 'cfotitt7@baidu.com', 'Clark''s nutcracker');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (9, 'Leopold', 'Axton', 'laxton8@mac.com', 'Defassa waterbuck');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (10, 'Tadeas', 'Thorouggood', 'tthorouggood9@va.gov', 'Armadillo, nine-banded');

create or replace table super_monsters.monster_information (
	id INT,
	monster VARCHAR(50),
	hideout_location VARCHAR(50)
);
insert into super_monsters.monster_information (id, monster, hideout_location) values (1, 'Northern elephant seal', 'Huangban');
insert into super_monsters.monster_information (id, monster, hideout_location) values (2, 'Paddy heron (unidentified)', 'Várzea Paulista');
insert into super_monsters.monster_information (id, monster, hideout_location) values (3, 'Australian brush turkey', 'Adelaide Mail Centre');
insert into super_monsters.monster_information (id, monster, hideout_location) values (4, 'Gecko, tokay', 'Tafí Viejo');
insert into super_monsters.monster_information (id, monster, hideout_location) values (5, 'Robin, white-throated', 'Turośń Kościelna');
insert into super_monsters.monster_information (id, monster, hideout_location) values (6, 'Goose, andean', 'Berezovo');
insert into super_monsters.monster_information (id, monster, hideout_location) values (7, 'Puku', 'Mayskiy');
insert into super_monsters.monster_information (id, monster, hideout_location) values (8, 'Frilled lizard', 'Fort Lauderdale');
insert into super_monsters.monster_information (id, monster, hideout_location) values (9, 'Yellow-necked spurfowl', 'Sezemice');
insert into super_monsters.monster_information (id, monster, hideout_location) values (10, 'Agouti', 'Najd al Jumā‘ī');

create table super_weapons.weapon_storage_location (
	id INT,
	created_by VARCHAR(50),
	location VARCHAR(50),
	catch_phrase VARCHAR(50),
	weapon VARCHAR(50)
);
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (1, 'Ullrich-Gerhold', 'Mazatenango', 'Assimilated object-oriented extranet', 'Fintone');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (2, 'Olson-Lindgren', 'Dvorichna', 'Switchable demand-driven knowledge user', 'Andalax');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (3, 'Rodriguez, Flatley and Fritsch', 'Palmira', 'Persevering directional encoding', 'Toughjoyfax');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (4, 'Conn-Douglas', 'Rukem', 'Robust tangible Graphical User Interface', 'Flowdesk');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (5, 'Huel, Hettinger and Terry', 'Bulawin', 'Multi-channelled radical knowledge user', 'Y-Solowarm');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (6, 'Torphy, Ritchie and Lakin', 'Wang Sai Phun', 'Self-enabling client-driven project', 'Alphazap');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (7, 'Carroll and Sons', 'Digne-les-Bains', 'Profound radical benchmark', 'Stronghold');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (8, 'Hane, Breitenberg and Schoen', 'Huangbu', 'Function-based client-server encoding', 'Asoka');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (9, 'Ledner and Sons', 'Bukal Sur', 'Visionary eco-centric budgetary management', 'Ronstring');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (10, 'Will-Thiel', 'Zafar', 'Robust even-keeled algorithm', 'Tin');


--Create Tags
create or replace tag security_class comment = 'sensitive data';


--Apply tags
alter table super_villains.villain_information set tag security_class = 'Level Super Secret A+++++++';
alter table super_monsters.monster_information set tag security_class = 'Level B';
alter table super_weapons.weapon_storage_location set tag security_class = 'Level Super Secret A+++++++';


--Create Roles
create role user1;
create role user2;
create role user3;

--Assign Roles to yourself with all needed privileges
grant role user1 to role accountadmin;
grant USAGE  on warehouse compute_wh to role user1;
grant usage on database ff_week_7 to role user1;
grant usage on all schemas in database ff_week_7 to role user1;
grant select on all tables in database ff_week_7 to role user1;

grant role user2 to role accountadmin;
grant USAGE  on warehouse compute_wh to role user2;
grant usage on database ff_week_7 to role user2;
grant usage on all schemas in database ff_week_7 to role user2;
grant select on all tables in database ff_week_7 to role user2;

grant role user3 to role accountadmin;
grant USAGE  on warehouse compute_wh to role user3;
grant usage on database ff_week_7 to role user3;
grant usage on all schemas in database ff_week_7 to role user3;
grant select on all tables in database ff_week_7 to role user3;

--Queries to build history
use role user1;
use database FF_WEEK_7;
select * from super_villains.villain_information;

use role user2;
use database FF_WEEK_7;
select * from super_monsters.monster_information;

use role user3;
use database FF_WEEK_7;
select * from super_weapons.weapon_storage_location;

If you don’t want to create new databases/schemas for this challenge…

USE WAREHOUSE <enter_wh_here>;
USE DATABASE <enter_db_here>;
USE SCHEMA <enter_schema_here>;

create or replace table week7_villain_information (
	id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(50),
	Alter_Ego VARCHAR(50)
);
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (1, 'Chrissy', 'Riches', 'criches0@ning.com', 'Waterbuck, defassa');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (2, 'Libbie', 'Fargher', 'lfargher1@vistaprint.com', 'Ibis, puna');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (3, 'Becka', 'Attack', 'battack2@altervista.org', 'Falcon, prairie');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (4, 'Euphemia', 'Whale', 'ewhale3@mozilla.org', 'Egyptian goose');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (5, 'Dixie', 'Bemlott', 'dbemlott4@moonfruit.com', 'Eagle, long-crested hawk');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (6, 'Giffard', 'Prendergast', 'gprendergast5@odnoklassniki.ru', 'Armadillo, seven-banded');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (7, 'Esmaria', 'Anthonies', 'eanthonies6@biblegateway.com', 'Cat, european wild');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (8, 'Celine', 'Fotitt', 'cfotitt7@baidu.com', 'Clark''s nutcracker');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (9, 'Leopold', 'Axton', 'laxton8@mac.com', 'Defassa waterbuck');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (10, 'Tadeas', 'Thorouggood', 'tthorouggood9@va.gov', 'Armadillo, nine-banded');

create or replace table week7_monster_information (
	id INT,
	monster VARCHAR(50),
	hideout_location VARCHAR(50)
);

insert into week7_monster_information (id, monster, hideout_location) values (1, 'Northern elephant seal', 'Huangban');
insert into week7_monster_information (id, monster, hideout_location) values (2, 'Paddy heron (unidentified)', 'Várzea Paulista');
insert into week7_monster_information (id, monster, hideout_location) values (3, 'Australian brush turkey', 'Adelaide Mail Centre');
insert into week7_monster_information (id, monster, hideout_location) values (4, 'Gecko, tokay', 'Tafí Viejo');
insert into week7_monster_information (id, monster, hideout_location) values (5, 'Robin, white-throated', 'Turośń Kościelna');
insert into week7_monster_information (id, monster, hideout_location) values (6, 'Goose, andean', 'Berezovo');
insert into week7_monster_information (id, monster, hideout_location) values (7, 'Puku', 'Mayskiy');
insert into week7_monster_information (id, monster, hideout_location) values (8, 'Frilled lizard', 'Fort Lauderdale');
insert into week7_monster_information (id, monster, hideout_location) values (9, 'Yellow-necked spurfowl', 'Sezemice');
insert into week7_monster_information (id, monster, hideout_location) values (10, 'Agouti', 'Najd al Jumā‘ī');


create table week7_weapon_storage_location (
	id INT,
	created_by VARCHAR(50),
	location VARCHAR(50),
	catch_phrase VARCHAR(50),
	weapon VARCHAR(50)
);

insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (1, 'Ullrich-Gerhold', 'Mazatenango', 'Assimilated object-oriented extranet', 'Fintone');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (2, 'Olson-Lindgren', 'Dvorichna', 'Switchable demand-driven knowledge user', 'Andalax');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (3, 'Rodriguez, Flatley and Fritsch', 'Palmira', 'Persevering directional encoding', 'Toughjoyfax');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (4, 'Conn-Douglas', 'Rukem', 'Robust tangible Graphical User Interface', 'Flowdesk');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (5, 'Huel, Hettinger and Terry', 'Bulawin', 'Multi-channelled radical knowledge user', 'Y-Solowarm');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (6, 'Torphy, Ritchie and Lakin', 'Wang Sai Phun', 'Self-enabling client-driven project', 'Alphazap');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (7, 'Carroll and Sons', 'Digne-les-Bains', 'Profound radical benchmark', 'Stronghold');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (8, 'Hane, Breitenberg and Schoen', 'Huangbu', 'Function-based client-server encoding', 'Asoka');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (9, 'Ledner and Sons', 'Bukal Sur', 'Visionary eco-centric budgetary management', 'Ronstring');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (10, 'Will-Thiel', 'Zafar', 'Robust even-keeled algorithm', 'Tin');
    

--Create Tags
create or replace tag security_class comment = 'sensitive data';

--Apply tags
alter table week7_villain_information set tag security_class = 'Level Super Secret A+++++++';
alter table week7_monster_information set tag security_class = 'Level B';
alter table week7_weapon_storage_location set tag security_class = 'Level Super Secret A+++++++';

--Create Roles
create role user1;
create role user2;
create role user3;

--Assign Roles to yourself with all needed privileges
grant role user1 to role accountadmin;
grant USAGE  on warehouse <enter_wh_here> to role user1;
grant usage on database <enter_db_here> to role user1;
grant usage on all schemas in database <enter_db_here> to role user1;
grant select on all tables in database <enter_db_here> to role user1;

grant role user2 to role accountadmin;
grant USAGE  on warehouse <enter_wh_here> to role user2;
grant usage on database <enter_db_here> to role user2;
grant usage on all schemas in database <enter_db_here> to role user2;
grant select on all tables in database <enter_db_here> to role user2;

grant role user3 to role accountadmin;
grant USAGE  on warehouse <enter_wh_here> to role user3;
grant usage on database <enter_db_here> to role user3;
grant usage on all schemas in database <enter_db_here> to role user3;
grant select on all tables in database <enter_db_here> to role user3;

--Queries to build history
use role user1;
select * from week7_villain_information;

use role user2;
select * from week7_monster_information;

use role user3;
select * from week7_weapon_storage_location;

Result

Your answer should look like the below

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 Christopher Marland on 2022-07-22 10 Comments

Week 6 – Hard

This week we’re going to play with spatial functions. Frosty Lobbying is thinking of supporting some candidates in the next UK General Election. What they need is to understand the geographic spread of candidates by nation/region of the UK.

Your job is to build both the nations/regions and parliamentary seats into polygons, and then work out how many Westminster seats intersect with region polygons.

Be wary that some seats may sit within two different regions, some may not sit within any (Northern Ireland is not included in the data provided) and some may just be awkward.

Note: Within the data, the ‘part’ column is an integer given to each landmass that makes up that region/nation/constituency – for example, the Isle of Mull could be ‘part 34’ of Scotland, and ‘part 12’ of the Argyll and Bute constituency.

You can find the nations are regions data here and the Westminster constituency data here.

Source: ONS, Open Geography Portal

End Result:

NATION_OR_REGION	INTERSECTING_CONSTITUENCIES
South East	116
North West	95
London	91
East of England	82
West Midlands	80
East Midlands	78
Yorkshire and The Humber	76
South West	70
Scotland	63
Wales	52
North East	33

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.

If you have any technical questions you’d like to pose to the community, you can ask here on our dedicated thread.

by Christopher Marland on 2022-07-15 13 Comments

Week 5 – Basic

This week, we’re using a feature that, at the time of writing, is pretty hot off the press :
Python in Snowflake.

To start out create a simple table with a single column with a number, the size and amount are up to you,

After that we’ll start with a very basic function: multiply those numbers by 3.

The challenge here is not ‘build a very difficult python function’ but to build and use the function in Snowflake.

We can test the code with a simple select statement :

SELECT timesthree(start_int)
FROM FF_week_5

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.

If you have any technical questions you’d like to pose to the community, you can ask here on our dedicated thread.

by Christopher Marland on 2022-07-15 17 Comments

Week 4 – Hard

Frosty Friday Consultants has been hired by the University of Frost’s history department; they want data on monarchs in their data warehouse for analysis. Your job is to take the JSON file located here, ingest it into the data warehouse, and parse it into a table that looks like this:

End Result:

If you can’t read the above right-click and view image in another tab.

Separate columns for nicknames and consorts 1 – 3, many will be null.
An ID in chronological order (birth).
An Inter-House ID in order as they appear in the file.
There should be 26 rows at the end.

Hints:

Make sure you don’t lose any rows along the way.
Be sure to investigate all the outputs and parameters available when transforming JSON.

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.

If you have any technical questions you’d like to pose to the community, you can ask here on our dedicated thread.

by Christopher Marland on 2022-07-15 20 Comments

Week 3 – Basic

In Week 1 we looked at ingesting S3 data, now it’s time to take that a step further. So this week we’ve got a short list of tasks for you all to do.

The basics aren’t earth-shattering but might cause you to scratch your head a bit once you start building the solution.

Frosty Friday Inc., your benevolent employer, has an S3 bucket that was filled with .csv data dumps. These dumps aren’t very complicated and all have the same style and contents. All of these files should be placed into a single table.

However, it might occur that some important data is uploaded as well, these files have a different naming scheme and need to be tracked. We need to have the metadata stored for reference in a separate table. You can recognize these files because of a file inside of the S3 bucket. This file, keywords.csv, contains all of the keywords that mark a file as important.

Objective:

Create a table that lists all the files in our stage that contain any of the keywords in the keywords.csv file.

The S3 bucket’s URI is: s3://frostyfridaychallenges/challenge_3/

Result:

Your result should look like:

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.

If you have any technical questions you’d like to pose to the community, you can ask here on our dedicated thread.

by Christopher Marland on 2022-07-15 19 Comments

Week 2 – Intermediate

A stakeholder in the HR department wants to do some change-tracking but is concerned that the stream which was created for them gives them too much info they don’t care about.

Load in the parquet data and transform it into a table, then create a stream that will only show us changes to the DEPT and JOB_TITLE columns.

You can find the parquet data here.

Execute the following commands:

UPDATE <table_name> SET COUNTRY = 'Japan' WHERE EMPLOYEE_ID = 8;
UPDATE <table_name> SET LAST_NAME = 'Forester' WHERE EMPLOYEE_ID = 22;
UPDATE <table_name> SET DEPT = 'Marketing' WHERE EMPLOYEE_ID = 25;
UPDATE <table_name> SET TITLE = 'Ms' WHERE EMPLOYEE_ID = 32;
UPDATE <table_name> SET JOB_TITLE = 'Senior Financial Analyst' WHERE EMPLOYEE_ID = 68;

The result should look like this:

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.

If you have any technical questions you’d like to pose to the community, you can ask here on our dedicated thread.

by Christopher Marland on 2022-07-14 37 Comments

Week 1 – Basic

FrostyFriday Inc., your benevolent employer, has an S3 bucket that is filled with .csv data dumps. This data is needed for analysis. Your task is to create an external stage, and load the csv files directly from that stage into a table.

The S3 bucket’s URI is: s3://frostyfridaychallenges/challenge_1/

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.

If you have any technical questions you’d like to pose to the community, you can ask here on our dedicated thread.