Archives for February 2023

Want to learn how to create external tables? Or if you already know, want to show off the fact?

Take the below code:

create stage external_table_stage
	url = 's3://frostyfridaychallenges/challenge_35/';

Create an external table called frosty_et from the above stage and when you run the following query:

select sale_month,
	id,
        drug_name,
        amount_sold
from frosty_et
order by amount_sold;

It should look like the below:

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

Time to help out our SuperAccountant

DataSuperHero Inc. is trying to connect to one of its data stores but sadly, the data that has been output is structured differently than SuperAccountant (“Who do you call when Tax Season comes?”) would like it to be.

The data that we start with, is structured with a parent-child system but there are some caveats :

• The depth of the child/parent varies in the structure but is given for every ‘chain’
• If the code has no child, the flag ‘lowest_level’ is set to 1
• The highest depth in the entire system is 7
• There is a valid_from and valid_until value. You have to compare this to the current date

What SuperAccountant would like to see is the following :

• using columns called “level_1”, “level_2” etc. construct the data in such a way that a single chain gets displayed on a single line
• If a code doesn’t have 7 levels, fill out the missing levels with the last known value
• The end table should only have rows of data using chains that are ‘finished’. You don’t need to display a row for every step of the chain

Visual aid for the end result :

The startup code :

create or replace table start_data(
    code VARCHAR
  , code_parent VARCHAR
  , valid_until DATE
  , valid_from DATE
  , is_lowest_level BOOLEAN
  , max_level INTEGER
)
as
select * from values
    ('CC0193','EBGABA','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',1,7)
  , ('CC0194','EBGABA','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',1,7)
  , ('EBGABA','EBGAB','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('EBGAB','EBGA','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('EBGA','EBG','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('EBG','EB','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('EB','ZZ','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('ZZ',NULL,'9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('7050307','CC','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',1,3)
  , ('CC','A1','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,3)
  , ('A1',NULL,'9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,3)
;

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

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’

If you need a db to use

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

alter database SFSALESSHARED_SFC_SAMPLES_AWS_EU_WEST_2_SAMPLE_DATA rename to SNOWFLAKE_SAMPLE_DATA;

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

This week we’re looking into some new security features that Snowflake has recently released

As you might know, the default idle time for Snowflake sessions is 4 hours BUT that was recently adjusted; you can now change this value on certain levels.

Because we can finetune these numbers, Management has asked us to enforce it for some of our new colleagues :

The challenge for this week :
– Create 2 users
– User 1 should have a max idle time of 8 minutes in the old Snowflake UI
– User 2 should have a max idle time of 10 minutes in SnowSQL and in their communication with Snowflake from tools like Tableau

One of the things we’d be looking for looks 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