Week 151 - Cortex Agents
Challenge
Coach Frosty just inherited a basketball analytics database from a retiring stats guru. Problem is, the old-timer had a... unique naming convention. Every column is just C1, C2, C3, and so on. Renaming columns isn't an option -- half theleague's reporting tools depend on the current schema.
The front office needs answers -- fast -- and they don't want to learn SQL. They want to ask plain English questions like "Who scored the most total points?" and get instant answers.
Assignment
Your mission: use a Semantic View to give meaning to the cryptic columns, then wire up a Cortex Agent that the front office can query in natural language.
Setup Code
-
CREATE OR REPLACE SCHEMA FROSTY_DB.WEEK_151;
CREATE OR REPLACE TABLE WEEK_151.PLAYERS (
C1 INT, C2 STRING, C3 STRING, C4 STRING, C5 INT
);
INSERT INTO WEEK_151.PLAYERS VALUES
(1, 'Luka Frostbite', 'Arctic Wolves', 'PG', 77),
(2, 'Glacier Thompson', 'Arctic Wolves', 'SG', 11),
(3, 'Icicle Johnson', 'Arctic Wolves', 'SF', 23),
(4, 'Permafrost Davis', 'Polar Bears', 'PF', 34),
(5, 'Snowdrift Williams','Polar Bears', 'C', 50),
(6, 'Blizzard Brown', 'Polar Bears', 'PG', 4),
(7, 'Hailstone Garcia', 'Frost Giants', 'SG', 8),
(8, 'Avalanche Martinez','Frost Giants', 'SF', 15),
(9, 'Tundra Robinson', 'Frost Giants', 'PF', 21),
(10, 'Sleet Anderson', 'Frost Giants', 'C', 42);
CREATE OR REPLACE TABLE WEEK_151.GAME_LOG (
C1 INT, C2 INT, C3 DATE, C4 STRING,
C5 INT, C6 INT, C7 INT, C8 INT, C9 INT, C10 INT
);
INSERT INTO WEEK_151.GAME_LOG VALUES
(1, 1, '2025-01-10', 'Polar Bears', 36, 28, 4, 12, 2, 3),
(2, 2, '2025-01-10', 'Polar Bears', 32, 18, 3, 5, 1, 2),
(3, 3, '2025-01-10', 'Polar Bears', 28, 15, 9, 2, 3, 1),
(4, 4, '2025-01-10', 'Arctic Wolves', 34, 22, 11, 3, 1, 4),
(5, 5, '2025-01-10', 'Arctic Wolves', 30, 14, 13, 1, 0, 2),
(6, 6, '2025-01-10', 'Arctic Wolves', 26, 10, 2, 8, 4, 5),
(7, 7, '2025-01-17', 'Arctic Wolves', 34, 24, 5, 6, 2, 3),
(8, 8, '2025-01-17', 'Arctic Wolves', 30, 19, 7, 3, 1, 2),
(9, 9, '2025-01-17', 'Arctic Wolves', 32, 16, 10, 2, 0, 1),
(10, 1, '2025-01-17', 'Frost Giants', 38, 35, 5, 14, 3, 4),
(11, 2, '2025-01-17', 'Frost Giants', 33, 21, 4, 7, 2, 1),
(12, 3, '2025-01-17', 'Frost Giants', 27, 12, 8, 1, 1, 3),
(13, 4, '2025-01-24', 'Frost Giants', 36, 26, 12, 4, 2, 3),
(14, 5, '2025-01-24', 'Frost Giants', 31, 18, 15, 2, 1, 1),
(15, 6, '2025-01-24', 'Frost Giants', 29, 13, 3, 9, 3, 6),
(16, 7, '2025-01-24', 'Polar Bears', 35, 27, 6, 5, 1, 2),
(17, 8, '2025-01-24', 'Polar Bears', 28, 14, 8, 4, 2, 1),
(18, 9, '2025-01-24', 'Polar Bears', 33, 20, 11, 3, 0, 2),
(19, 1, '2025-01-31', 'Frost Giants', 37, 31, 3, 11, 2, 2),
(20, 2, '2025-01-31', 'Frost Giants', 34, 22, 5, 6, 3, 3),
(21, 3, '2025-01-31', 'Frost Giants', 26, 11, 7, 2, 0, 1),
(22, 7, '2025-01-31', 'Arctic Wolves', 36, 29, 4, 7, 1, 4),
(23, 8, '2025-01-31', 'Arctic Wolves', 31, 17, 9, 3, 2, 2),
(24, 9, '2025-01-31', 'Arctic Wolves', 30, 13, 8, 1, 1, 3),
(25, 4, '2025-02-07', 'Arctic Wolves', 35, 24, 10, 5, 1, 2),
(26, 5, '2025-02-07', 'Arctic Wolves', 32, 16, 14, 3, 0, 3),
(27, 6, '2025-02-07', 'Arctic Wolves', 28, 11, 2, 7, 5, 4),
(28, 1, '2025-02-07', 'Polar Bears', 36, 30, 4, 13, 2, 3),
(29, 2, '2025-02-07', 'Polar Bears', 30, 19, 3, 4, 1, 2),
(30, 3, '2025-02-07', 'Polar Bears', 25, 10, 6, 1, 2, 1);
Column Mapping
Here's what the old stats guru's columns actually mean:
PLAYERS
┌────────┬──────────────────────────────┐
│ Column │ Meaning │
├────────┼──────────────────────────────┤
│ C1 │ player_id (primary key) │
├────────┼──────────────────────────────┤
│ C2 │ player_name │
├────────┼──────────────────────────────┤
│ C3 │ team │
├────────┼──────────────────────────────┤
│ C4 │ position (PG, SG, SF, PF, C) │
├────────┼──────────────────────────────┤
│ C5 │ jersey_number │
└────────┴──────────────────────────────┘
GAME_LOG
┌────────┬───────────────────────────────────────┐
│ Column │ Meaning │
├────────┼───────────────────────────────────────┤
│ C1 │ game_id (primary key) │
├────────┼───────────────────────────────────────┤
│ C2 │ player_id (foreign key to PLAYERS.C1) │
├────────┼───────────────────────────────────────┤
│ C3 │ game_date │
├────────┼───────────────────────────────────────┤
│ C4 │ opponent │
├────────┼───────────────────────────────────────┤
│ C5 │ minutes_played │
├────────┼───────────────────────────────────────┤
│ C6 │ points │
├────────┼───────────────────────────────────────┤
│ C7 │ rebounds │
├────────┼───────────────────────────────────────┤
│ C8 │ assists │
├────────┼───────────────────────────────────────┤
│ C9 │ steals │
├────────┼───────────────────────────────────────┤
│ C10 │ turnovers │
└────────┴───────────────────────────────────────┘
Your Objective
Step 1 -- Create a Semantic View
Create a Semantic View called SPORTS_ANALYTICS in FROSTY_DB.WEEK_151 that maps the cryptic column names to meaningful business concepts.
Your semantic view should include:
• Both tables with primary keys and a relationship joining them on player_id
• Dimensions: player name, team, position, game date, opponent, jersey number
• Facts: points, rebounds, assists, steals, turnovers, minutes played
• At least 3 Metrics with aggregations (e.g. total points, average points per game, total assists)
• Synonyms and comments on your dimensions and facts so the agent can understand the data
-
In Snowsight, go to AI & ML > Cortex Analyst > Create new > Create new Semantic View. Select FROSTY_DB.WEEK_151 as the location. Add both tables, select the columns, and click Create and save. Once generated, edit the view to refine the dimensions, facts, and metrics. To add a metric, click + next to Metrics, give it a name like total_points, set the expression to SUM(C6), and add a description. Add synonyms and comments to help the agent understand what each field
means.
Step 2 -- Create a Cortex Agent
Create a Cortex Agent called HOOPS_AGENT in FROSTY_DB.WEEK_151 that uses your semantic view to answer basketball questions.
Your agent should have:
• A Cortex Analyst tool connected to your SPORTS_ANALYTICS semantic view
• A description that tells users what the agent is for
-
In Snowsight, go to AI & ML > Agents > Create agent. Name it HOOPS_AGENT in FROSTY_DB.WEEK_151. Click Edit, then under Tools, find Cortex Analyst and click + Add. Select your SPORTS_ANALYTICS semantic view, choose a warehouse, and add a description like "Analyzes basketball player performance data including points, rebounds, assists, steals, and turnovers across games." Click Add, then Save. For the agent description, enter something like: "Basketball analytics assistant for the Frosty Friday league. Ask questions about player performance, team stats, and game results."
Step 3 -- Prove it works
Open your agent's chat interface and ask:
Who scored the most total points across all games?
Expected Output
The agent should answer: Luka Frostbite with 124 total points.
Hints
• Read the Snowflake docs on Semantic Views and Snowflake Intelligence
• If you get model availability errors, ask your ACCOUNTADMIN to run: ALTER ACCOUNT SET CORTEX_ENABLED_CROSS_REGION = 'ANY_REGION';
• Your synonyms and comments are what help the agent understand that C6 means "points", don't skip them
