Christopher Marland

External Access Integrations are in GA now (well, for a few weeks but there’s so much coming out that even we can’t keep up!) and so it’s a good time to create a challenge based around them!

Note: for this, you will need an admin to run the following code:

CREATE OR REPLACE NETWORK RULE treasury_nr
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('api.fiscaldata.treasury.gov');


CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION treasury_eai
  ALLOWED_NETWORK_RULES = (treasury_nr)
  ENABLED = true;

GRANT USAGE ON INTEGRATION treasury_eai TO ROLE <your_role>;

Once they’ve done that, now it’s for you to create the table:

CREATE OR REPLACE TABLE WEEK_93 (
    AVG_INTEREST_RATE_AMT FLOAT,
    RECORD_DATE DATE,
    SECURITY_DESC STRING,
    SECURITY_TYPE_DESC STRING,
    SRC_LINE_NBR STRING,
    API_CALL_START_DATE DATE,
    API_CALL_END_DATE DATE
);

You then need to create a function with the requests library that looks something like:

CREATE OR REPLACE FUNCTION get_treasury_data(start_date DATE, end_date DATE)
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'get_data'
EXTERNAL_ACCESS_INTEGRATIONS = (treasury_eai)
PACKAGES = ('snowflake-snowpark-python','requests')
AS
$$

$$;

You want to query the following API:

https://api.fiscaldata.treasury.gov/services/api/fiscal_service//v2/accounting/od/avg_interest_rates?fields=record_date,security_type_desc,security_desc,avg_interest_rate_amt,src_line_nbr&filter=record_date:gte:<START_DATE_HERE>,record_date:lt:<END_DATE_HERE>

Your data must start at 2020-01-01 and finish at 2024-04-30. Create a stored procedure (in the language of your choosing) that loops through the result until it can get no more data between those dates and INSERTs the data into your table.

If you get this correct, your table should have 880 rows, and they ought to look like:

One of the cool things about native apps, is the ability to request permissions and be granted them without actually having real access to the consumer account.

Instead, the application gets the permissions.

But how do we request permissions? Well, let’s find out. Your challenge is to request:

• SELECT access to one table
• CREATE DATABASE on account

Your native app should then verify it has all the permissions it needs. The flow should look like:

Here is a zip with the skeleton code. You will need to change every file in the zip.

Good luck!

Today’s challenge is a guest challenge from Marcel Schwarze!

One typical task during performance and cost optimization is identifying common, heavily executed query patterns and trying to optimize them afterwards. The following challenge is exactly about this: The identification of frequently executed query patterns in combination with the associated execution times. The following queries, which need to be executed as part of the challenge-setup, provide an example what is considered as “queries following the same pattern”. In our scenario, queries with the same pattern have the same query text and only the parameter values used in the WHERE-clauses are allowed to differ (see examples).

Challenge: Complete the following SQL statement to identify queries following the same pattern (as per the mentioned definition/examples), count the execution frequency per pattern and sum the execution time. For each group show one sample query. The expected result can be seen in the screenshot. 

ALTER SESSION SET query_tag = 'ff_challenge';
/* Random queries without common pattern */
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
SELECT COUNT(*) FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_NATIONKEY = 15;
SELECT C_NAME FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_PHONE = '19-144-468-5416';

/* First set of queries following the same pattern. Pattern = All columns from customers with one WHERE-condition for C_MKTSEGMENT. */
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'BUILDING';
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'AUTOMOBILE';
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'MACHINERY';
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'HOUSEHOLD';
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'BUILDING';
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'BUILDING';

/*Second set of queries following the same pattern. Pattern= C_NAME and C_NATIONKEY from customers with two WHERE-conditions for C_MKTSEGMENT and C_NATIONKEY. */
SELECT C_NAME, C_NATIONKEY FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'BUILDING' AND C_NATIONKEY = 21;
SELECT C_NAME, C_NATIONKEY FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = 'MACHINERY' AND C_NATIONKEY = 9;

Query to complete:

select …
from table(information_schema.query_history())
where query_tag = ‘ff_challenge’ and query_text NOT ILIKE ‘%information_schema%’
group by …
order by … ;

Comment the link to your solutions in GitHub below!

After our Paris launch of the French version of FROSTY_FRIDAY() we have news for you! At the Data Cloud World Tour Madrid, we’re launching our Spanish version!

To celebrate, here’s a Spanish challenge.

You can access the data at this URI: s3://frostyfridaychallenges/challenge_68/

Your task is to recreate the following in SiS (Streamlit in Snowflake):

Data Source: WorldPopulationReview.com