Week 93 – Advanced

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: