Week 155 - Cortex AI Functions USAGE

Challenge

Frostbite Analytics Inc. has been going all-in on Cortex AI Functions — sentiment analysis on customer reviews, summarisation of support tickets, you name it. But the CFO just called an emergency meeting: "Why is our Cortex bill so high this month?!" The data team needs to quickly understand which functions, models, and users are driving the spend.

Your job: query the CORTEX_AI_FUNCTIONS_USAGE_HISTORY Account Usage view to build a clear picture of Cortex consumption.

Assignment

Use the SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY view to analyse your account's Cortex AI Function spend by function, model, and time period.

Setup Code

Setup Code (click me)

-- If you want a dedicated schema for your analysis results:
CREATE DATABASE IF NOT EXISTS FROSTY_DB;
CREATE SCHEMA IF NOT EXISTS FROSTY_DB.WEEK_155;
USE SCHEMA FROSTY_DB.WEEK_155;

Your Objective

Step 1: Explore the view

Query SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY to see what data is available. Return the 10 most recent rows to understand the schema.

I'm Stuck -- Help! (Click me)

The view lives in the SNOWFLAKE.ACCOUNT_USAGE schema. You'll need a role with the IMPORTED PRIVILEGES grant on the SNOWFLAKE database. Try SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY ORDER BY START_TIME DESC LIMIT 10;

Step 2: Total credits by function

Write a query that shows total credits consumed per FUNCTION_NAME, ordered from highest to lowest spend.

I'm Stuck -- Help! (Click me)

Group by FUNCTION_NAME and SUM(CREDITS). The CREDITS column gives you the direct Cortex credit cost (excluding warehouse compute).

Step 3: Credits by model

Some functions (like AI_COMPLETE) let you choose different models, and costs vary significantly between them. Write a query showing total credits broken down by both FUNCTION_NAME and MODEL_NAME.

I'm Stuck -- Help! (Click me)

Group by both FUNCTION_NAME and MODEL_NAME. Remember that MODEL_NAME will be empty for functions where no model is specified (like AI_SENTIMENT).

Step 4: Daily spend trend

Write a query showing total Cortex credits consumed per day for the last 30 days. This will help spot any usage spikes.

I'm Stuck -- Help! (Click me)

Cast START_TIME to a date using START_TIME::DATE and filter with WHERE START_TIME >= DATEADD('day', -30, CURRENT_TIMESTAMP()). Group by the date.

Step 5: Token breakdown

The METRICS column contains a JSON array with token counts. Write a query that extracts total input tokens and output tokens per function for the last 7 days. This helps understand whether cost is driven by large prompts (input) or verbose responses (output).

I'm Stuck -- Help! (Click me)

The METRICS column is an ARRAY. Use LATERAL FLATTEN to unnest it, then filter on f.value:key:metric to separate 'input' from 'output' tokens. Use conditional aggregation: SUM(CASE WHEN f.value:key:metric = 'input' THEN f.value:value END).

Step 6: Verify your understanding

Write a single query that produces a summary showing: the total number of distinct Cortex AI function calls (distinct QUERY_IDs), the total credits consumed, and the most expensive single query (by credits) in your account's history.

I'm Stuck -- Help! (Click me)

Use COUNT(DISTINCT QUERY_ID) for total calls, SUM(CREDITS) for total spend, and a subquery or window function for the most expensive query. Remember that a single QUERY_ID can appear in multiple rows (one per function/model combination), so sum credits per query first before finding the max.

Hints

• You need the IMPORTED PRIVILEGES grant on the SNOWFLAKE database to access Account Usage views.
• The view only contains data from January 5, 2026 onwards.
• Each row represents usage for a single function + model + query + warehouse combination within a 1-hour window.
• The CREDITS column does not include warehouse compute credits -- it's purely the Cortex AI Function cost.
• The METRICS array structure varies: most functions use token-based metering (input/output or total tokens), but AI_PARSE_DOCUMENT uses page-based metering.
• Doc reference: https://docs.snowflake.com/en/sql-reference/account-usage/cortex_ai_functions_usage_history

• Accordion Item 1

  Description text goes here

• Accordion Item 2

  Description text goes here

• Accordion Item 3

  Description text goes here