by 
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 :
Startup Code
-- 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”
-
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
-
Another nice demo of new functionality, thanks
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
Thank you, I have added to my toolbox.
- Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w38.sql
-
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
-
Nice!
- Solution URL – https://github.com/lbinfolab/frosty-friday/blob/1f8df4a4f3b5f626493ea11785dc27e9e95dda10/week38.sql
-
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
-
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
You must be logged in to post a comment.