Week 36 – Intermediate

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 :

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”

  1. ChrisHastie avatar
    ChrisHastie

    I discussed two potential options in my solution here, but only constructed one of them

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

    Late arriving solutions for weeks 33 – 36 🙂

    • Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges
  3. lukas.bogacz avatar
    lukas.bogacz

    this should work

    • Solution URL – https://github.com/lbinfolab/frosty-friday/blob/9eb1ca0cf2c0094f7b3826ca1b3f6e394d844291/week36.sql
  4. dsmdavid avatar
    dsmdavid

    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