February 2024

by Mike Droog on 2024-02-23 2 Comments

Week 82 – Basic

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:

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 2024-02-16 1 Comment

Week 81 – Intermediate

Did you know that one task can have more than one parent? No?

Run the following code:

CREATE STAGE frosty_aws_stage
  URL = 's3://frostyfridaychallenges/';

EXECUTE IMMEDIATE FROM @frosty_aws_stage/challenge_81/starter_code.sql;

Now you want to create 5 tasks:

Task 1 will run the following:

Task 1

INSERT INTO w81_raw_product (data)
SELECT parse_json(column1)
FROM
VALUES
('{"product_id": 21, "product_name": "Product U", "category": "Electronics", "price": 120.99, "created_at": "2024-02-16"}'),
('{"product_id": 22, "product_name": "Product V", "category": "Books", "price": 35.00, "created_at": "2024-02-16"}');
INSERT INTO w81_raw_customer (data)
SELECT parse_json(column1)
FROM
VALUES
('{"customer_id": 6, "customer_name": "Frank", "email": "frank@example.com", "created_at": "2024-02-16"}'),
('{"customer_id": 7, "customer_name": "Grace", "email": "grace@example.com", "created_at": "2024-02-16"}');
INSERT INTO w81_raw_sales (data)
SELECT parse_json(column1)
FROM
VALUES
('{"sale_id": 11, "product_id": 21, "customer_id": 6, "quantity": 1, "sale_date": "2024-02-17"}'), -- New product, new customer
('{"sale_id": 12, "product_id": 22, "customer_id": 1, "quantity": 1, "sale_date": "2024-02-17"}'), -- New product, existing customer
('{"sale_id": 13, "product_id": 2, "customer_id": 7, "quantity": 2, "sale_date": "2024-02-17"}'), -- Existing product, new customer
('{"sale_id": 14, "product_id": 3, "customer_id": 6, "quantity": 1, "sale_date": "2024-02-17"}'), -- Existing product, new customer
('{"sale_id": 15, "product_id": 21, "customer_id": 5, "quantity": 1, "sale_date": "2024-02-17"}'); -- New product, existing customer;

Tasks 2-4 will parse the JSON from the raw tables

Task 5 will run the following AFTER tasks 2-4 have completed:

Task 5

CREATE OR replace VIEW aggregated_sales
AS
  SELECT c.customer_name,
         p.product_name,
         SUM(s.quantity)           AS total_quantity,
         SUM(s.quantity * p.price) AS total_sales
  FROM   sales s
         join product p
           ON s.product_id = p.product_id
         join customer c
           ON s.customer_id = c.customer_id
  GROUP  BY c.customer_name,
            p.product_name

In the end, you should run:

End run

SELECT t.name,
       t.predecessors
FROM   TABLE(information_schema.Task_dependents(task_name => 'INSERT_INTO_RAW', recursive => TRUE)) t;

And get:

Happy DAGing!

by Sofia Pierini on 2024-02-09 2 Comments

Week 80 – Basic

Just ten days ago Snowflake announced the general availability of creating a new table when loading a file in Snowsight. How? By using the INFER_SCHEMA table function to automatically detect the file metadata schema, retrieve the column definitions, and generate a new table.

All you need to do is:

Log on to Snowsight.
Go to the “Data” section in the navigation menu, then select “Databases.”
Choose a specific database and schema you want to work with.
Click “Create.”
In the drop-down menu, select “Table” and then choose “From File.”

And voila, you’ll discover how cool this new feature is! It will certainly save you time!

by Mike Droog on 2024-02-02 3 Comments

Week 79 – Basic

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:

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.
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:

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.

Archives for February 2024