Snowflake Trial

Week 38 – Basic

With every month, we get more Snowflake updates! This month, we’ve seen the general release of Stream on Views hit General Availability!
To celebrate this, we’ve created this basic challenge, focussed on Streams.

Use the following block of code to quickly form your table/view basis :

-- Create first table
CREATE TABLE employees (
id INT,
name VARCHAR(50),
department VARCHAR(50)
);

-- Insert example data into first table

INSERT INTO employees (id, name, department)
VALUES
(1, "Alice" "Sales"),
(2, "Bob", "Marketing");

-- Create second table
CREATE TABLE sales (
id INT,
employee_id INT,
sale_amount DECIMAL(10, 2)
);

-- Insert example data into second table
INSERT INTO sales (id, employee_id, sale_amount)
VALUES
(1, 1, 100.00),
(2, 1, 200.00),
(3, 2, 150.00);

-- Create view that combines both tables
CREATE VIEW employee_sales AS
SELECT e.id, e.name, e.department, s.sale_amount
FROM employees e
JOIN sales s ON e.id = s.employee_id;

-- Query the view to verify the data
SELECT * FROM employee_sales;

We’ve had some issues with sales being rung-up but then not showing up in the system anymore. We’ve got a feeling that they’re being removed and would like to very quickly see if that’s the case. Could you help out the store with the following?

Use the above structure to :
– Add a stream to the employee_sales VIEW
– Keep track of every deletion that was made
– Move these into a new table called ‘deleted_sales’

7 responses to “Week 38 – Basic”

  1. zlzlzl2 avatar
    zlzlzl2

    Used as chance to practice stream, will use it with task in the future to move batch of data.

    • Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF38_LZ.sql
  2. ChrisHastie avatar
    ChrisHastie

    Another nice demo of new functionality, thanks

    • Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
  3. mat avatar
    mat

    Thank you, I have added to my toolbox.

    • Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w38.sql
  4. Jamie Laird avatar
    Jamie Laird

    Here’s my solution. An automated process for moving records from the stream to deleted_sales table would definitely be the way to go if doing this in the real world, but I kept it simple here. Thanks for another fun task!

    • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_38/ff_week_38.sql
  5. lukas.bogacz avatar
    lukas.bogacz

    Nice!

    • Solution URL – https://github.com/lbinfolab/frosty-friday/blob/1f8df4a4f3b5f626493ea11785dc27e9e95dda10/week38.sql
  6. dsmdavid avatar
    dsmdavid

    This was indeed not very “dbt-friendly” but here’s my attempt anyway

    • Solution URL – http://dvd-il-bucket-public.s3-website-eu-west-1.amazonaws.com/#!/model/model.frosty.challenge_38_04_deleted_sales
  7. Non-deterministicNorman avatar
    Non-deterministicNorman

    An intermediate challenge would be to add tasks to automate the process

    • Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2038%20-%20basic%20/stream%20on%20view

Leave a Reply