January 2024

by Christopher Marland on 2024-01-26 3 Comments

Week 78 – Basic

Session Variables!

Did you even know that you can use variables in your SQL queries outwith stored procedures? No? Well, I’m here to tell you you can.

Set Up Code

CREATE TABLE w78 AS
SELECT
  SEQ4() AS sales_id,
  CASE
    WHEN MOD(SEQ4(), 4) = 0 THEN 'Product A'
    WHEN MOD(SEQ4(), 4) = 1 THEN 'Product B'
    WHEN MOD(SEQ4(), 4) = 2 THEN 'Product C'
    ELSE 'Product D'
  END AS product_name,
  UNIFORM(1, 10, RANDOM())::INTEGER AS quantity_sold,
  DATEADD('day', -UNIFORM(1, 365, RANDOM())::INTEGER, CURRENT_DATE()) AS sales_date,
  UNIFORM(20, 100, RANDOM())::FLOAT * UNIFORM(1, 10, RANDOM())::INTEGER AS sales_amount
FROM TABLE(GENERATOR(ROWCOUNT => 1000));

Run the above code, and then create a variable called “sales_avg”.

Run the below script:

SELECT *
FROM w78
WHERE sales_amount between $sales_avg - 50 and $sales_avg +50;

Your result should look like the below (numbers will vary)

by Sofia Pierini on 2024-01-19 2 Comments

Shapefiles play a crucial role in BI by providing a geospatial foundation for data analysis. These files, comprising geometric and attribute information, enable businesses to integrate location-based data seamlessly into their analytical workflows. Recognizing the importance of location-based information, Snowflake allows users to store, query, and analyze geospatial data. How to load geospatial data that is in a shapefile into a Snowflake table?

Some previous steps before starting the challenge:

Download shapefile as a .zip archive from here: https://mapcruzin.com/free-italy-arcgis-maps-shapefiles.htm#google_vignette (feel free to choose any other type of shapefile from any other platform, I really like trains so I chose this one🚆).
Open this file. You can do it online here: https://mapshaper.org/. Below is a screenshot of the picked file opened by Mapshaper.

At this point, let the challenge begin:

Load the .zip file into a Snowflake stage. (There are at least three ways to do that!).
Find out how to convert the shapefile into a Snowflake table with geospatial features.
Review the results (as in the picture below).

Can’t wait to see your ideas!

‘It’s impossible not to love trains. Btw, good job! You took on another challenge!

by Mike Droog on 2024-01-12 1 Comment

Week 76 – Intermediate

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:

Setup

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

Archives for January 2024

The challenge