Archives for May 2024

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?

DevOps nerds galore celebrate as CREATE OR ALTER goes into public preview! What a great pre-Summit drop, we can use it in our attendee tracking table.

CREATE OR TABLE conference_attendees (
    attendee_id INT,
    first_name STRING,
    last_name STRING,
    email STRING,
    registration_date DATE,
    ticket_type STRING,
    conference_id INT
);

INSERT INTO conference_attendees VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '2024-06-01', 'DSH', 101),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '2024-06-02', 'Regular', 101),
(3, 'Alice', 'Johnson', 'alice.johnson@example.com', '2024-06-03', 'DSH', 102),
(4, 'Bob', 'Brown', 'bob.brown@example.com', '2024-06-04', 'Regular', 102),
(5, 'Charlie', 'Davis', 'charlie.davis@example.com', '2024-06-05', 'Partner', 103),
(6, 'Diana', 'Martinez', 'diana.martinez@example.com', '2024-06-06', 'Regular', 101),
(7, 'Evan', 'Garcia', 'evan.garcia@example.com', '2024-06-07', 'DSH', 103),
(8, 'Fiona', 'Wilson', 'fiona.wilson@example.com', '2024-06-08', 'Regular', 102),
(9, 'George', 'Moore', 'george.moore@example.com', '2024-06-09', 'Partner', 103),
(10, 'Hannah', 'Taylor', 'hannah.taylor@example.com', '2024-06-10', 'DSH', 101);

Wait no… we’ve messed that up!

conference_id ought to be conference_name, and everyone’s going to Summit 24! Use CREATE OR ALTER to rectify our mistake.

If you’ve done it right, the below should work:

INSERT INTO conference_attendees VALUES
(11, 'Irene', 'Lee', 'irene.lee@example.com', '2024-06-11', 'DSH', 'Summit 2024'),
(12, 'Jack', 'Harris', 'jack.harris@example.com', '2024-06-12', 'Regular', 'Summit 2024'),
(13, 'Karen', 'Clark', 'karen.clark@example.com', '2024-06-13', 'DSH', 'Summit 2024'),
(14, 'Larry', 'Lewis', 'larry.lewis@example.com', '2024-06-14', 'Regular', 'Summit 2024'),
(15, 'Monica', 'Walker', 'monica.walker@example.com', '2024-06-15', 'Partner', 'Summit 2024'),
(16, 'Nina', 'Hall', 'nina.hall@example.com', '2024-06-16', 'Regular', 'Summit 2024'),
(17, 'Oscar', 'Young', 'oscar.young@example.com', '2024-06-17', 'DSH', 'Summit 2024'),
(18, 'Paula', 'King', 'paula.king@example.com', '2024-06-18', 'Regular', 'Summit 2024'),
(19, 'Quinn', 'Wright', 'quinn.wright@example.com', '2024-06-19', 'Partner', 'Summit 2024'),
(20, 'Rachel', 'Lopez', 'rachel.lopez@example.com', '2024-06-20', 'DSH', 'Summit 2024');

Happy Altering!

Did you know that you can use python scripts in Snowflake? In this challenge you will have to upload a script to a stage and then use it in a stored procedure.

CREATE OR REPLACE PROCEDURE frosty_challenge()
    returns string
    language python
    packages=('snowflake-snowpark-python')
    imports=('your import goes here')
    handler = 'func'
    RUNTIME_VERSION=3.8
    as
$$

your import goes here

def func(session):

    your code goes here

    return r
$$
    ;

CALL frosty_challenge();

Create a python file with the following content and upload it to a stage in Snowflake:

def success():
    return 'Congratulations!'

Upload the file to a stage. Then specify the path to your stage in the ‘Imports = ()’ part of your procedure. Then import the function ‘success ‘from the file and use it. You will have finished the challenge when your stored procedure returns the string ‘Congratulations!’.

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.

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’);

External Access Integrations are in GA now (well, for a few weeks but there’s so much coming out that even we can’t keep up!) and so it’s a good time to create a challenge based around them!

Note: for this, you will need an admin to run the following code:

CREATE OR REPLACE NETWORK RULE treasury_nr
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('api.fiscaldata.treasury.gov');


CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION treasury_eai
  ALLOWED_NETWORK_RULES = (treasury_nr)
  ENABLED = true;

GRANT USAGE ON INTEGRATION treasury_eai TO ROLE <your_role>;

Once they’ve done that, now it’s for you to create the table:

CREATE OR REPLACE TABLE WEEK_93 (
    AVG_INTEREST_RATE_AMT FLOAT,
    RECORD_DATE DATE,
    SECURITY_DESC STRING,
    SECURITY_TYPE_DESC STRING,
    SRC_LINE_NBR STRING,
    API_CALL_START_DATE DATE,
    API_CALL_END_DATE DATE
);

You then need to create a function with the requests library that looks something like:

CREATE OR REPLACE FUNCTION get_treasury_data(start_date DATE, end_date DATE)
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'get_data'
EXTERNAL_ACCESS_INTEGRATIONS = (treasury_eai)
PACKAGES = ('snowflake-snowpark-python','requests')
AS
$$

$$;

You want to query the following API:

https://api.fiscaldata.treasury.gov/services/api/fiscal_service//v2/accounting/od/avg_interest_rates?fields=record_date,security_type_desc,security_desc,avg_interest_rate_amt,src_line_nbr&filter=record_date:gte:<START_DATE_HERE>,record_date:lt:<END_DATE_HERE>

Your data must start at 2020-01-01 and finish at 2024-04-30. Create a stored procedure (in the language of your choosing) that loops through the result until it can get no more data between those dates and INSERTs the data into your table.

If you get this correct, your table should have 880 rows, and they ought to look like: