Mike Droog

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?

-- 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)?

The Intern has been very glad lately with his new position together with Professor Frosty. Before joining F_F, he was stuck at a job that had a particularly nasty manager.

Using the tools Snowflake Cortex and Top_insights, can you figure out which manager was causing so much trouble?

CREATE OR REPLACE TABLE employees (
    employee_id INT AUTOINCREMENT,
    level VARCHAR(2) DEFAULT 'L1',
    manager_id INT,
    base_salary INT,
    time_with_company INT,
    measurement_date DATE
);

INSERT INTO employees (manager_id, base_salary,time_with_company, measurement_date)
WITH emp_data_control AS (
    SELECT
        UNIFORM(1, 20, RANDOM()) AS manager_id,
        CASE
            WHEN RANDOM() < 0.3 THEN UNIFORM(3500, 4499, RANDOM())
            ELSE UNIFORM(4500, 5500, RANDOM())
        END AS base_salary,
        UNIFORM(6, 20, RANDOM()) AS time_with_company,
        DATE_FROM_PARTS(2024, 1, 1) AS measurement_date
    FROM 
        TABLE(GENERATOR(ROWCOUNT => 1000))
)
SELECT * FROM emp_data_control;

INSERT INTO employees (manager_id, base_salary,time_with_company, measurement_date)
WITH managers AS (
    SELECT
        SEQ4() AS manager_id,
        CASE
            WHEN SEQ4() IN (10) THEN 'high_negative'
            ELSE 'high_positive'
        END AS impact
    FROM 
        TABLE(GENERATOR(ROWCOUNT => 20))
),
emp_data AS (
    SELECT
        m.manager_id AS manager_id,
        CASE
            WHEN RANDOM() < 0.3 THEN UNIFORM(4000, 4999, RANDOM()) 
            ELSE UNIFORM(5000, 6000, RANDOM()) 
        END AS base_salary, 
        CASE 
            WHEN m.impact = 'high_negative' THEN UNIFORM(1, 5, RANDOM()) 
            ELSE UNIFORM(6, 20, RANDOM()) 
        END AS time_with_company, 
        DATE_FROM_PARTS(2024, 10, 10) AS measurement_date 
    FROM 
        TABLE(GENERATOR(ROWCOUNT => 1000))
    JOIN 
        managers AS m ON true
    ORDER BY RANDOM()
)
SELECT * FROM emp_data;

Intern has given you 2 pieces of data inside of a single table. One of them is control data, and the other is testing data.

• Control data is from 2024-01-01 when everything seemed normal
• Testing data from 2024-10-10.

Can you point out which manager seems to be having a very high turnover rate?

As fans of dbt , we’re quite happy with the new Jinja2 template support for EXECUTE IMMEDIATE FROM !

This time our intern got the following assignement :
Create a sql script that using Jinja2 templating that does the following based on my input :

• Deployment Differentiation:
  • Dynamically adapts to different deployment types (prod and non-prod).
  • Uses distinct environment names (prod1, prod2 for production and dev, qa, staging for non-production).
• Schema Creation:
  • Creates specific schemas (finance, sales, hr for production and development, testing, support for non-production).
• Database Setup:
  • Creates databases named according to the environment ({{ environment }}_db).
• Schema-Specific Table Creation:
  • Creates multiple tables within each schema according to the environment ({{ environment }}_{{schema}}_employees for example):
    • orders table with columns id, item, quantity, and order_date.
    • customers table with columns id, name, and email.
    • products table with columns product_id, product_name, and price.
    • employees table with columns employee_id, employee_name, and position
• Production-Specific Setup:
  • Creates a separate prod_analytics_db database.
  • Within this database, creates an analytics_reports table with columns report_id, report_name, and created_date.

Professor Frosties input should be similar to the following :

EXECUTE IMMEDIATE FROM @setup_stage/setup_env_made_by_intern.sql
USING (DEPLOYMENT_TYPE => ‘prod’);

The time has come for Snowflake to start integrating Git!

To celebrate the fact that we’ve gotten git integration , we’re looking into some cool features that come with it :
Creating Stored Procedures based on a .py file in a github repo

Your challenge for this week is to:
– connect to this repo from snowflake : https://github.com/FrostyFridays/FF_week_91
– write a procedure that use the file add_1.py
– create a stored procedure that can use the code from the .py file as a handler to add 1 to a column.

You can use the starting code given below for a small testing table.

CREATE OR REPLACE TABLE people (
id INTEGER,
name VARCHAR
);

INSERT INTO people (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David'),
(5, 'Eva'),
(6, 'Fiona'),
(7, 'George'),
(8, 'Hannah'),
(9, 'Ian'),
(10, 'Julia');

This week we’re taking a look into Data Governance and taking it a step further then the Masking Policy and Row Access Policies that usually get all of the attention.

We’re going to protect a column from being queried completely BUT we’d still like to be able to use it for our joins.

CREATE or replace TABLE PersonalDetails (
ID INT PRIMARY KEY,
FirstName VARCHAR,
LastName VARCHAR,
SSN VARCHAR
);

INSERT INTO PersonalDetails (ID, FirstName, LastName, SSN) VALUES
(1, 'John', 'Doe', '123-45-6789'),
(2, 'Jane', 'Doe', '987-65-4321'),
(3, 'Jim', 'Beam', '111-22-3333'),
(4, 'Jill', 'Valentine', '444-55-6666'),
(5, 'Leon', 'Kennedy', '777-88-9999'),
(6, 'Claire', 'Redfield', '222-33-4444'),
(7, 'Chris', 'Redfield', '555-66-7777'),
(8, 'Ada', 'Wong', '888-99-0000'),
(9, 'Albert', 'Wesker', '666-77-8888'),
(10, 'Rebecca', 'Chambers', '999-00-1111'),
(11, 'Barry', 'Burton', '333-44-5555'),
(12, 'Carlos', 'Oliveira', '666-55-4444'),
(13, 'Nikolai', 'Zinoviev', '777-33-2222'),
(14, 'Jill', 'Sandwich', '888-44-5555'),
(15, 'Hunk', 'Unknown', '999-66-7777');

CREATE or replace TABLE EmploymentDetails (
EmploymentID INT PRIMARY KEY,
SSN VARCHAR,
CompanyName VARCHAR,
Position VARCHAR,
StartDate DATE,
Salary INT
);

INSERT INTO EmploymentDetails (EmploymentID, SSN, CompanyName, Position, StartDate, Salary) VALUES
(1, '123-45-6789', 'ACME Corporation', 'Software Engineer', '2018-06-01', 70000),
(2, '987-65-4321', 'Globex Corporation', 'Project Manager', '2019-08-15', 75000),
(3, '111-22-3333', 'Soylent Corp', 'Quality Assurance Engineer', '2020-02-01', 68000),
(4, '444-55-6666', 'Initech', 'IT Support Specialist', '2017-05-23', 62000),
(5, '777-88-9999', 'Umbrella Corporation', 'Research Scientist', '2021-03-12', 78000),
(6, '222-33-4444', 'Hooli', 'Data Analyst', '2018-07-01', 69000),
(7, '555-66-7777', 'Vehement Capital Partners', 'Investment Analyst', '2019-09-09', 71000),
(8, '888-99-0000', 'Massive Dynamic', 'Executive Assistant', '2020-01-20', 65000),
(9, '666-77-8888', 'Wayne Enterprises', 'Security Consultant', '2017-04-10', 72000),
(10, '999-00-1111', 'Stark Industries', 'Mechanical Engineer', '2021-08-05', 83000),
(11, '333-44-5555', 'Pied Piper', 'Software Developer', '2019-06-01', 85000),
(12, '666-55-4444', 'Bluth Company', 'Sales Manager', '2018-11-01', 64000),
(13, '777-33-2222', 'Dunder Mifflin', 'Regional Manager', '2017-12-01', 73000),
(14, '888-44-5555', 'Los Pollos Hermanos', 'Operations Manager', '2020-07-15', 55000),
(15, '999-66-7777', 'Cyberdyne Systems', 'Systems Analyst', '2019-04-01', 76000);

The challenge this week is a bit different in that we want you to works towards an error instead of a solution. The error in question :

This error SHOULD popup when we execute a ‘SELECT * FROM PersonalDetails’ but we should still be able to run the following :

SELECT p.FirstName, p.LastName, e.CompanyName, e.Position, e.StartDate, e.Salary
FROM PersonalDetails p
JOIN EmploymentDetails e ON p.SSN = e.SSN;

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

Time to try out this ASOF join that they’ve introduced to Snowflake!

In the heart of a bustling financial district lies a brokerage firm’s trading floor, where traders make split-second decisions amid a frenzy of activity. Trades and quotes, recorded in separate tables, hold vital information about transactions and market prices.

To ensure traders have the latest market data, a critical task emerges: pairing each trade with the most recent quote available at or before its time. Analysts devise a sophisticated SQL query, leveraging temporal joins to seamlessly integrate trade and quote data based on timestamps.

Given two tables, trade and quotes, containing transactional data in a financial market, where the trade table records trades and the quotes table records quotes for a specific financial instrument, construct an ASOF join SQL statement to pair each trade record with the most recent quote record available at or before the time of the trade.

create or replace temporary table trade (
    type VARCHAR,
    id INT,
    ticker VARCHAR,
    datetime TIMESTAMP_TZ,
    price FLOAT,
    volume INT
);

create or replace temporary table quotes(
    type VARCHAR,
    id INT,
    ticker VARCHAR,
    datetime TIMESTAMP_TZ,
    ask FLOAT,
    bid FLOAT
);
insert into trade values
    ('trade', 2, 'AAPL', '2020-01-06 09:00:30.000+09:00', 305, 1),  
    ('trade', 2, 'AAPL', '2020-01-06 09:01:00.000+09:00', 310, 2),  
    ('trade', 2, 'AAPL', '2020-01-06 09:01:30.000+09:00', 308, 1),  
    ('trade', 3, 'GOOGL', '2020-01-06 09:02:00.000+09:00', 1500, 2),  
    ('trade', 3, 'GOOGL', '2020-01-06 09:03:00.000+09:00', 1520, 3),  
    ('trade', 3, 'GOOGL', '2020-01-06 09:03:30.000+09:00', 1515, 1); 

insert into quotes values
    ('quote', 2, 'AAPL', '2020-01-06 08:59:59.999+09:00', 305, 304),  
    ('quote', 2, 'AAPL', '2020-01-06 09:02:00.000+09:00', 311, 309),  
    ('quote', 3, 'GOOGL', '2020-01-06 09:01:00.000+09:00', 1490, 1485),  
    ('quote', 3, 'GOOGL', '2020-01-06 09:04:00.000+09:00', 1530, 1528);  

insert into quotes values
    ('quote', 2, 'AAPL', '2020-01-06 09:00:30.000+09:00', 307, 305),  
    ('quote', 2, 'AAPL', '2020-01-06 09:01:30.000+09:00', 308, 306),  
    ('quote', 3, 'GOOGL', '2020-01-06 09:02:00.000+09:00', 1502, 1498),  
    ('quote', 3, 'GOOGL', '2020-01-06 09:03:30.000+09:00', 1518, 1513);  

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.

Our intern has tried to optimize our stages …

This week, we’ve received a new challenge from our non-existing intern.

Instead of staging all files to a particular stage, he loaded everything onto a singular stage and deleted the local files to speed up his work.

Because we’ve got a lot of systems that run off of those other named stages, it’s up to us to move the data to its correct location without having to download all the files again and upload them again.

To start this challenge, please find the needed mock files here:
s3://frostyfridaychallenges/challenge_82/

It’s up to you to :

• create a ‘starting stage’
• upload the file to that ‘starting stage’
• delete the local file
• move the file from the ‘starting stage’ into a new stage without downloading it!

We’ve got no code to share with you this time, but be sure to share your coded answers in the comments!

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.

This week’s focus centers around the primary point of interaction for Snowflake users—the login screen. As you engage with this challenge, consider the following tasks:

1. Exclusive UI Access: Ensure that individuals accessing the Snowflake account can only log in through the Snowflake UI, and other clients like SnowSQL are restricted.
2. User_1 Authentication: Configure User_1 to be permitted access solely through a username/password authentication method, disallowing SAML, OAuth, or Keypair.

Your objective is to navigate these tasks effectively by exploring and applying the appropriate commands. Good luck in mastering this week’s challenge!

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.

One of the things that we’re trying to create as Engineers is a robust system that keeps working through failure.
This week we’re going to expand these capabilities when looking at tasks : Even though a task fails , it’s chained task must still continue.

For this challenge , we’ve got a task that is very error prone (for some reason that we still haven’t figured out 😉 ) but even if it DOES fail, we still want a notification in our table that the task itself failed.

The code:

CREATE OR REPLACE TABLE task_table (stamp time, message varchar);

CREATE OR REPLACE TASK main_task 
  SCHEDULE = '1 minute'
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
AS
SELECT CASE
    WHEN RANDOM() <0 THEN 1/0
    ELSE 1
END;

CREATE OR REPLACE TASK child_task 
AFTER main_task
AS
INSERT INTO task_table (stamp,message) VALUES(CURRENT_TIMESTAMP,'main_task succes!');

The challenge

We want you to write a task that is chained to main_task and always runs even if the main_task fails.
The task should simply insert the message ‘task ran’ into task_table with an accompanying timestamp

We’ve gotten the message that some of our DBAs are loosing grasp of the internal structure of our FakeCompany inc. Let’s try to help them out!

To aid in this, we’re initiating a challenge! Your task is to create a reference table that encapsulates the essential aspects of our internal database structure.

What You Need to Do

Examine the Provided Table Image: A reference image (see below) outlines the basic format and information your table should include.

Create a Similar Table: Develop a table that mirrors the structure shown in the image. Make sure your table is tailored to reflect our specific database architecture at FakeCompany Inc.

The starting code for this challenge:

CREATE OR REPLACE table departments (department_name varchar, department_ID int, head_department_ID int);

INSERT INTO departments (department_name, department_ID, head_department_ID) VALUES
    ('Research & Development', 1, NULL),  -- The Research & Development department is the top level.
        ('Product Development', 11, 1),
            ('Software Design', 111, 11),
            ('Product Testing', 112, 11),
        ('Human Resources', 2, 1),
            ('Recruitment', 21, 2),
            ('Employee Relations', 22, 2);

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.