Week 33 – Hard

Ok… bear with us a minute. We have a few requirements with this one. We are very worried about clustering depth this week, and so we need to build a DAG of tasks that will monitor this.

Create the following table:

create or replace table cluster_depth_monitoring (
    database_name varchar,
    sceham_name varchar,
    table_name varchar,
    clustering_depth float,
    inserted_at timestamp,
    inserted_by varchar
);

We want a DAG like the one above where:

CM_IDENTIFY_TABLES:

• Identify the tables in your database that are clustered
• Create a transient table with this information

CM_INSERT_DETAILS

• Execute a Snowpark stored procedure that examines the clustering depth of each of those tables
• Inserts the result into the cluster_depth_monitoring table

CM_CLEAN_UP

• Drops the transient table created by CM_IDENTIFY_TABLES

Importantly, we want all these tasks to tag the queries with ‘cluster_depth_monitoring’

Your end result ought to look something like this:

Remember, if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge