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;
I discussed two potential options in my solution here, but only constructed one of them
Late arriving solutions for weeks 33 – 36 🙂
this should work
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