Overview

What You Will Build

What You Will Learn

Prerequisites

Architecture

SNOWFLAKE.ACCOUNT_USAGE.CORTEX_REST_API_USAGE_HISTORY
        │
        ├── Claude Skill (Tab 1)
        │   ├── KPI Totals (tokens, requests, models)
        │   ├── Daily Token Usage chart
        │   ├── Tokens by Model chart
        │   ├── Daily Usage by Model table
        │   └── /pdf command → PDF report (reportlab + matplotlib)
        │
        └── CoCo Skill (Tab 2)
            ├── Daily/Weekly Budget Status (OK / WARNING / OVER BUDGET)
            ├── Projected Usage (daily avg, weekly, monthly)
            ├── Burn Rate chart (30-day with 7-day rolling avg)
            ├── Top Models & Top Users
            ├── Week-over-Week comparison
            └── Rolling Alerts table (30-day breach history)

Claude Skill — Usage Reports

KPI Totals

SELECT 
    SUM(TOKENS) as TOTAL_TOKENS,
    COUNT(*) as TOTAL_REQUESTS,
    COUNT(DISTINCT MODEL_NAME) as UNIQUE_MODELS
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_REST_API_USAGE_HISTORY 
WHERE START_TIME >= :start_date

Daily Usage

SELECT 
    DATE_TRUNC('day', START_TIME)::DATE AS USAGE_DATE,
    SUM(TOKENS) as TOTAL_TOKENS,
    COUNT(*) as REQUEST_COUNT
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_REST_API_USAGE_HISTORY 
WHERE START_TIME >= :start_date
GROUP BY 1 ORDER BY 1

Usage by Model

SELECT 
    MODEL_NAME,
    SUM(TOKENS) as TOTAL_TOKENS,
    COUNT(*) as REQUEST_COUNT
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_REST_API_USAGE_HISTORY 
WHERE START_TIME >= :start_date
GROUP BY 1 ORDER BY 2 DESC

Daily by Model

SELECT 
    DATE_TRUNC('day', START_TIME)::DATE AS USAGE_DATE,
    MODEL_NAME,
    SUM(TOKENS) as TOTAL_TOKENS,
    COUNT(*) as REQUEST_COUNT
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_REST_API_USAGE_HISTORY 
WHERE START_TIME >= :start_date
GROUP BY 1, 2 ORDER BY 1, 2

The /pdf Slash Command

CoCo Skill — Budget Enforcement

Budget Configuration

Daily Budget Status

SELECT 
    COALESCE(SUM(TOKENS), 0) AS today_tokens,
    :daily_budget AS budget,
    ROUND(100.0 * COALESCE(SUM(TOKENS), 0) / :daily_budget, 2) AS pct_used,
    CASE 
        WHEN COALESCE(SUM(TOKENS), 0) >= :daily_budget THEN 'OVER BUDGET'
        WHEN COALESCE(SUM(TOKENS), 0) >= :daily_budget * 0.8 THEN 'WARNING'
        ELSE 'OK'
    END AS status
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_REST_API_USAGE_HISTORY 
WHERE DATE(START_TIME) = CURRENT_DATE();

Daily Burn Rate with 7-Day Rolling Average

SELECT 
    DATE_TRUNC('day', START_TIME)::DATE AS usage_date,
    SUM(TOKENS) AS daily_tokens,
    COUNT(*) AS daily_requests,
    AVG(SUM(TOKENS)) OVER (
        ORDER BY DATE_TRUNC('day', START_TIME)::DATE 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_REST_API_USAGE_HISTORY 
WHERE START_TIME >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 1 DESC;

Projected Monthly Usage

WITH daily_avg AS (
    SELECT AVG(daily_tokens) AS avg_daily_tokens
    FROM (
        SELECT SUM(TOKENS) AS daily_tokens
        FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_REST_API_USAGE_HISTORY 
        WHERE START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP())
        GROUP BY DATE(START_TIME)
    )
)
SELECT 
    avg_daily_tokens,
    avg_daily_tokens * 30 AS projected_monthly_tokens,
    avg_daily_tokens * 7 AS projected_weekly_tokens
FROM daily_avg;

Rolling Alerts Table

Run the Dashboard

Setup

USE ROLE ACCOUNTADMIN;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE <your_role>;

Create the Streamlit App

Explore Tab 1 (Claude Skill)

Explore Tab 2 (CoCo Skill)

Cleanup

Conclusion and Resources

Related Resources