by 
In the past, SuperHero Inc. has had some issues with losing data connection: SuperDataBoy (formerly known as InternDataLad) was a bit too overzealous with dropping tables, not knowing that a view was referencing it.
To help out SuperDataBoy, we’re going to create a mechanism that checks if a view is getting its information from a table that we’re about to drop. If there is a reliance, the table should not be dropped.
Let’s see if we can help SuperDataBoy out!
You can test your mechanism with this small piece of startup code that just creates a lot of tables/views :
Tables/Views
CREATE OR REPLACE TABLE table_1 (id INT);
CREATE OR REPLACE VIEW view_1 AS (SELECT * FROM table_1);
CREATE OR REPLACE TABLE table_2 (id INT);
CREATE OR REPLACE VIEW view_2 AS (SELECT * FROM table_2);
CREATE OR REPLACE TABLE table_6 (id INT);
CREATE OR REPLACE VIEW view_6 AS (SELECT * FROM table_6);
CREATE OR REPLACE TABLE table_5 (id INT);
CREATE OR REPLACE VIEW view_5 AS (SELECT * FROM table_5);
CREATE OR REPLACE TABLE table_4 (id INT);
CREATE OR REPLACE VIEW view_4 AS (SELECT * FROM table_4);
CREATE OR REPLACE TABLE table_3 (id INT);
CREATE OR REPLACE VIEW view_3 AS (SELECT * FROM table_3);
CREATE OR REPLACE VIEW my_union_view AS
SELECT * FROM table_1
UNION ALL
SELECT * FROM table_2
UNION ALL
SELECT * FROM table_3
UNION ALL
SELECT * FROM table_4
UNION ALL
SELECT * FROM table_5
UNION ALL
SELECT * FROM table_6;
4 responses to “Week 36 – Intermediate”
-
I discussed two potential options in my solution here, but only constructed one of them
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
Late arriving solutions for weeks 33 – 36 🙂
- Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges
-
this should work
- Solution URL – https://github.com/lbinfolab/frosty-friday/blob/9eb1ca0cf2c0094f7b3826ca1b3f6e394d844291/week36.sql
-
Catching up with the challenges.
Since I’m sticking to dbt, I decided to use a dbt macro instead of a stored procedure
– checks whether there are any dependents
– checks the object type
– drops the object when possible- Solution URL – http://dvd-il-bucket-public.s3-website-eu-west-1.amazonaws.com/#!/macro/macro.frosty.safe_drop_36
Leave a Reply
You must be logged in to post a comment.