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:


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


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


  • Drops the transient table created by CM_IDENTIFY_TABLES

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

The above DB has clustered tables, I would recommend perhaps editing the db name, though…


Note that the name might appear different in your account, and that, depending on your account’s permission structures, you may need ACCOUNTADMIN to add this db.

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

2 responses to “Week 33 – Hard”

  1. ChrisHastie avatar

    That was a fun one, thanks!

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

    Well that *was* hard. This blog was a huge help – thank you CH. https://interworks.com/blog/2022/08/16/a-definitive-guide-to-python-stored-procedures-in-the-snowflake-ui/

    • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_33/ff_week_33.sql

Leave a Reply