MCP Client Demo

import json
import pandas as pd
from IPython.display import display, Markdown, HTML

from mcp_ski_resort.core import (
    extract_text, try_parse_json,
    parse_agent_response, parse_analyst_response, parse_search_response,
    result_set_to_dataframe,
)
from mcp_ski_resort.mcp_client import (
    MCPClient, MCPToolbox, mcp_tools_list, mcp_call, display_mcp_result,
)

1. Discover Available Tools

tools = mcp_tools_list()

rows = []
for t in tools:
    params = list(t.get('inputSchema', {}).get('properties', {}).keys())
    rows.append({
        'Tool': t['name'],
        'Params': ', '.join(params),
        'Description': t.get('description', '')[:80],
    })
display(HTML(f'<h3 style="color:#60a5fa;">{len(tools)} MCP Tools Available</h3>'))
display(pd.DataFrame(rows))

10 MCP Tools Available

	Tool	Params	Description
0	daily-summary-analyst	message	Executive daily summary analyst - ask natural ...
1	revenue-analyst	message	Revenue analyst covering ticket sales, rental ...
2	operations-analyst	message	Operations analyst for lift scans, wait times,...
3	resort-executive-agent	text	Executive BI partner with access to all resort...
4	ski-ops-assistant-agent	text	Operations-focused assistant for lift supervis...
5	feedback-search	query, columns, filter, limit	Semantic search over guest feedback entries. S...
6	incident-search	query, columns, filter, limit	Semantic search over safety incidents. Search ...
7	execute-sql	sql	Execute read-only SQL queries against the AM_S...
8	resort-kpi-summary	p_season	Returns a comprehensive JSON summary of resort...
9	weather-impact-report	p_start_date, p_end_date	Analyzes how weather conditions impact resort ...

2. Cortex Agent (CORTEX_AGENT_RUN)

Agents return structured responses with answer text, SQL queries, result sets, and charts.

agent_resp = mcp_call("resort-executive-agent", {
    "text": "Give me a quick executive summary of the 2024-2025 season: revenue, visits, and satisfaction."
})

parsed = parse_agent_response(agent_resp)
if parsed.get("answer"):
    display(HTML('<h4 style="color:#60a5fa;">Executive Agent: Season Summary</h4>'))
    display(Markdown(parsed["answer"][:3000]))
    for i, sql in enumerate(parsed.get("sql_queries", [])):
        display(HTML(
            f'<details><summary style="cursor:pointer;font-weight:600;color:#94a3b8;">SQL {i+1}</summary>'
            f'<pre style="padding:12px;background:#0f172a;color:#e2e8f0;border:1px solid #334155;'
            f'border-radius:8px;font-size:13px;">{sql}</pre></details>'
        ))
    for i, rs in enumerate(parsed.get("result_sets", [])):
        df = result_set_to_dataframe(rs)
        if not df.empty:
            display(HTML(f'<p style="font-weight:600;color:#d1d5db;">Result {i+1} ({len(df)} rows)</p>'))
            display(df.head(10))
else:
    display_mcp_result(agent_resp, "Executive Agent")

Executive Agent: Season Summary

2024-2025 Season Executive Summary

Season Performance (Nov 1, 2024 – Apr 30, 2025)

Revenue: $7.23M Total

• Food & Beverage: $3.09M (43% of total, 216,944 transactions)
• Ticket Sales: $2.62M (36% of total, 24,041 tickets sold)
• Rentals: $1.51M (21% of total, 27,770 transactions)

Visitation: 103,597 Total Visits

• Unique Visitors: 7,803 guests
• Average Visits per Guest: 13.3 visits
• Pass Holder Share: 76.8% (79,556 visits)
• Day Ticket Visits: 24,041

Guest Satisfaction

• Net Promoter Score (NPS): 7.2 out of 10
• Average Rating: 3.4 out of 5.0
• Total Feedback Collected: 1,553 responses

---

Key Takeaways: - Strong pass holder loyalty driving repeat visitation with nearly 77% of visits from passholders - F&B has emerged as the largest revenue stream, representing significant ancillary spending opportunity - Guest satisfaction scores are moderate, suggesting opportunity for experience improvements to drive higher ratings and NPS

3. Cortex Analyst (CORTEX_ANALYST_TEXT_TO_SQL)

Natural-language SQL generation over structured data — returns SQL + result sets.

analyst_resp = mcp_call("daily-summary-analyst", {
    "message": "How many total visits and unique visitors in the 2024-2025 season?"
})

display(HTML('<h4 style="color:#60a5fa;">Analyst: Season Visitation</h4>'))
display_mcp_result(analyst_resp)

Analyst: Season Visitation

This is our interpretation of your question:

How many total visits and unique visitors in the 2024-2025 ski season?

SQL

WITH __fact_pass_usage AS (
  SELECT
    customer_key,
    date_key,
    usage_key
  FROM AM_SKI_RESORT.MARTS.FACT_PASS_USAGE
), __dim_date AS (
  SELECT
    date_key,
    ski_season
  FROM AM_SKI_RESORT.MARTS.DIM_DATE
), __fact_pass_usage AS (
  SELECT
    customer_key,
    date_key,
    usage_key
  FROM __fact_pass_usage
), __dim_date AS (
  SELECT
    date_key,
    ski_season
  FROM __dim_date
)
SELECT
  d.ski_season,
  COUNT(pu.usage_key) AS total_visits,
  COUNT(DISTINCT pu.customer_key) AS unique_visitors
FROM __fact_pass_usage AS pu
LEFT OUTER JOIN __dim_date AS d
  ON pu.date_key = d.date_key
WHERE
  d.ski_season = '2024-2025'
GROUP BY
  d.ski_season
ORDER BY
  d.ski_season DESC NULLS LAST
 -- Generated by Cortex Analyst (request_id: ba506c71-07e0-4159-9794-0c973d14929c)
;

4. Cortex Search (CORTEX_SEARCH_SERVICE_QUERY)

Semantic search over unstructured text — feedback and incidents.

feedback_resp = mcp_call("feedback-search", {
    "query": "long lift lines and crowded conditions",
    "columns": ["feedback_text", "rating", "sentiment", "category"],
    "limit": 10,
})

feedback_df = parse_search_response(feedback_resp)
display(HTML(f'<h4 style="color:#60a5fa;">Feedback Search: "long lift lines" ({len(feedback_df)} results)</h4>'))
if not feedback_df.empty:
    display(feedback_df[["feedback_text", "rating", "sentiment", "category"]].head(10))

Feedback Search: "long lift lines" (10 results)

	feedback_text	rating	sentiment	category
0	Great experience	5	positive	Snow_Conditions
1	Great experience	4	positive	Lessons
2	Great experience	5	positive	Parking
3	Great experience	4	positive	Lifts
4	Great experience	4	positive	Food_Service
5	Great experience	5	positive	Lifts
6	Great experience	4	positive	Lifts
7	Great experience	4	positive	Rentals
8	Great experience	4	positive	Food_Service
9	Great experience	5	positive	Facilities

5. Execute SQL (SYSTEM_EXECUTE_SQL)

Run ad-hoc SQL queries against the resort database.

sql_resp = mcp_call("execute-sql", {
    "sql": """
        SELECT
            l.lift_name,
            l.lift_type,
            COUNT(*) AS total_scans,
            ROUND(AVG(s.wait_time_minutes), 1) AS avg_wait
        FROM AM_SKI_RESORT.MARTS.FACT_LIFT_SCANS s
        JOIN AM_SKI_RESORT.MARTS.DIM_LIFT l ON s.lift_key = l.lift_key
        JOIN AM_SKI_RESORT.MARTS.DIM_DATE d ON s.date_key = d.date_key
        WHERE d.ski_season = '2024-2025'
        GROUP BY l.lift_name, l.lift_type
        ORDER BY avg_wait DESC
        LIMIT 10
    """
})

display(HTML('<h4 style="color:#60a5fa;">SQL: Top 10 Lifts by Wait Time (2024-2025)</h4>'))
display_mcp_result(sql_resp)

SQL: Top 10 Lifts by Wait Time (2024-2025)

Query ID: 01c3c2a9-0208-8521-004d-de0710f4be8e

Result (10 rows)

	LIFT_NAME	LIFT_TYPE	TOTAL_SCANS	AVG_WAIT
0	Mid Mountain	Chair - 4 Pack	144314	4.3
1	Family Fun	Chair - 4 Pack	134416	4.1
2	Backcountry Access	Chair - 2 Pack	76932	3.6
3	Blue Sky	Chair - 4 Pack	115527	3.6
4	Cruiser	Chair - 6 Pack	134463	3.5
5	Terrain Park Express	Chair - 4 Pack	86626	3.4
6	Sunshine	Chair - 4 Pack	105470	3.4
7	South Ridge	Chair - 4 Pack	105050	3.4
8	Eagle Ridge	Chair - 6 Pack	124945	3.3
9	Learners Lift	Chair - 3 Pack	48021	3.2

6. KPI Summary (GENERIC UDF)

Call the resort-kpi-summary UDF and render a dashboard card layout.

kpi_resp = mcp_call("resort-kpi-summary", {"p_season": "2024-2025"})

kpi_data = try_parse_json(extract_text(kpi_resp))

if kpi_data:
    display(HTML('<h4 style="color:#60a5fa;">KPI Summary: 2024-2025 Season</h4>'))

    vis = kpi_data.get("visitation", {})
    rev = kpi_data.get("revenue", {})
    ops = kpi_data.get("operations", {})
    sat = kpi_data.get("satisfaction", {})
    safety = kpi_data.get("safety", {})

    cards = [
        ("Total Visits", f"{vis.get('total_visits', 0):,}", ""),
        ("Unique Visitors", f"{vis.get('unique_visitors', 0):,}", ""),
        ("Ticket Revenue", f"${rev.get('ticket_revenue', 0):,.0f}", f"{rev.get('ticket_count', 0):,} tickets"),
        ("Avg Wait", f"{ops.get('avg_wait_minutes', 0):.1f} min", f"Max: {ops.get('max_wait_minutes', 0)} min"),
        ("NPS Score", f"{sat.get('avg_nps', 0):.1f}", f"Rating: {sat.get('avg_rating', 0):.1f}/5"),
        ("Incidents", f"{safety.get('total_incidents', 0)}", f"Response: {safety.get('avg_patrol_response_min', 0):.0f} min"),
    ]

    card_html = '<div style="display:flex;flex-wrap:wrap;gap:12px;margin:12px 0;">'
    for title, value, subtitle in cards:
        card_html += (
            f'<div style="flex:1;min-width:150px;padding:16px;background:#1e293b;'
            f'border:1px solid #334155;border-radius:12px;text-align:center;">'
            f'<div style="font-size:12px;color:#94a3b8;text-transform:uppercase;">{title}</div>'
            f'<div style="font-size:28px;font-weight:700;color:#38bdf8;margin:4px 0;">{value}</div>'
            f'<div style="font-size:11px;color:#64748b;">{subtitle}</div></div>'
        )
    card_html += '</div>'
    display(HTML(card_html))
else:
    display_mcp_result(kpi_resp, "KPI Summary")

KPI Summary: 2024-2025 Season

Total Visits

103,597

Unique Visitors

7,803

Ticket Revenue

$2,619,109

24,041 tickets

Avg Wait

3.4 min

Max: 28 min

NPS Score

7.2

Rating: 3.4/5

Incidents

528

Response: 9 min

7. Weather Impact (GENERIC UDF)

weather_resp = mcp_call("weather-impact-report", {
    "p_start_date": "2024-11-01",
    "p_end_date": "2025-04-30",
})

weather_data = try_parse_json(extract_text(weather_resp))

if weather_data:
    display(HTML('<h4 style="color:#60a5fa;">Weather Impact: 2024-2025 Season</h4>'))

    summary = weather_data.get("weather_summary", {})
    powder = weather_data.get("powder_day_impact", {})
    conditions = weather_data.get("visitation_by_condition", [])

    summary_html = (
        f'<div style="padding:12px;background:#0f2918;border:1px solid #166534;border-radius:8px;margin:8px 0;color:#4ade80;">'
        f'<b>Weather Summary:</b> {summary.get("total_days", 0)} days analyzed | '
        f'Avg Temp: {summary.get("avg_temperature", 0):.0f}\u00b0F | '
        f'Total Snowfall: {summary.get("total_snowfall_inches", 0):.0f}" | '
        f'Powder Days: {powder.get("powder_day_count", 0)}'
        f'</div>'
    )
    display(HTML(summary_html))

    if conditions:
        display(pd.DataFrame(conditions))
else:
    display_mcp_result(weather_resp, "Weather Impact")

Weather Impact: 2024-2025 Season

Weather Summary: 181 days analyzed | Avg Temp: 0°F | Total Snowfall: 0" | Powder Days: 0

	avg_daily_visits	avg_hours	snow_condition	visit_days
0	777	6.8	Excellent	59
1	703	6.6	Good	62
2	236	6.3	Fair	60

8. Season Report Pipeline

Combine multiple MCP tools into a single executive report.

SEASON = "2024-2025"
report = {}

display(HTML(f'<h2 style="text-align:center;color:#7dd3fc;">Alpine Mountain Ski Resort \u2014 {SEASON} Season Report</h2><hr style="border-color:#334155;"/>'))

print(f"[1/5] Fetching KPIs via resort-kpi-summary...")
kpi = mcp_call("resort-kpi-summary", {"p_season": SEASON})
report["kpi"] = try_parse_json(extract_text(kpi))

print(f"[2/5] Revenue breakdown via revenue-analyst...")
rev = mcp_call("revenue-analyst", {
    "message": f"What is the total ticket revenue, rental revenue, and food & beverage revenue for the {SEASON} season?"
})
report["revenue"] = parse_analyst_response(rev)

print(f"[3/5] Operations via operations-analyst...")
ops = mcp_call("operations-analyst", {
    "message": f"What are the top 5 busiest lifts by total scans in the {SEASON} season?"
})
report["operations"] = parse_analyst_response(ops)

print(f"[4/5] Guest sentiment via feedback-search...")
neg = mcp_call("feedback-search", {
    "query": "worst experience terrible disappointing",
    "columns": ["feedback_text", "rating", "sentiment", "category"],
    "limit": 5,
})
report["negative_feedback"] = parse_search_response(neg)

print(f"[5/5] Season comparison via resort-executive-agent...")
comparison = mcp_call("resort-executive-agent", {
    "text": f"Compare the 2023-2024 season vs {SEASON}: total visits, revenue, NPS. Which season performed better?"
})
report["comparison"] = parse_agent_response(comparison)

print("\nAll data collected!")

Alpine Mountain Ski Resort — 2024-2025 Season Report

---

[1/5] Fetching KPIs via resort-kpi-summary...
[2/5] Revenue breakdown via revenue-analyst...
[3/5] Operations via operations-analyst...
[4/5] Guest sentiment via feedback-search...
[5/5] Season comparison via resort-executive-agent...

All data collected!

display(HTML(f'<h2 style="text-align:center;color:#7dd3fc;">Alpine Mountain \u2014 {SEASON} Executive Report</h2>'))

if report.get("kpi"):
    kpi = report["kpi"]
    vis = kpi.get("visitation", {})
    rev_kpi = kpi.get("revenue", {})
    ops_kpi = kpi.get("operations", {})
    sat = kpi.get("satisfaction", {})
    safety_kpi = kpi.get("safety", {})

    metrics = [
        ("Total Visits", f"{vis.get('total_visits', 0):,}"),
        ("Unique Visitors", f"{vis.get('unique_visitors', 0):,}"),
        ("Ticket Revenue", f"${rev_kpi.get('ticket_revenue', 0):,.0f}"),
        ("Avg Ticket Price", f"${rev_kpi.get('avg_ticket_price', 0):.2f}"),
        ("Total Lift Scans", f"{ops_kpi.get('total_lift_scans', 0):,}"),
        ("Avg Wait Time", f"{ops_kpi.get('avg_wait_minutes', 0):.1f} min"),
        ("NPS Score", f"{sat.get('avg_nps', 0):.1f}"),
        ("Avg Rating", f"{sat.get('avg_rating', 0):.2f}/5"),
        ("Safety Incidents", f"{safety_kpi.get('total_incidents', 0)}"),
        ("Patrol Response", f"{safety_kpi.get('avg_patrol_response_min', 0):.0f} min"),
    ]
    cards_html = '<div style="display:flex;flex-wrap:wrap;gap:10px;margin:16px 0;">'
    for title, value in metrics:
        cards_html += (
            f'<div style="flex:1;min-width:130px;padding:14px;background:#1e293b;'
            f'border:1px solid #334155;border-radius:12px;text-align:center;">'
            f'<div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">{title}</div>'
            f'<div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">{value}</div></div>'
        )
    cards_html += '</div>'
    display(HTML(cards_html))

for section, label in [("revenue", "Revenue Breakdown"), ("operations", "Top Lifts")]:
    data = report.get(section, {})
    if data.get("result_sets"):
        display(HTML(f'<h4 style="color:#60a5fa;">{label}</h4>'))
        for rs in data["result_sets"]:
            df = result_set_to_dataframe(rs)
            if not df.empty:
                display(df.head(10))
    elif data.get("text"):
        display(HTML(f'<h4 style="color:#60a5fa;">{label}</h4>'))
        display(Markdown(data["text"][:2000]))

if not report.get("negative_feedback", pd.DataFrame()).empty:
    display(HTML('<h4 style="color:#f87171;">Worst Guest Feedback</h4>'))
    display(report["negative_feedback"].head(5))

comp = report.get("comparison", {})
if comp.get("answer"):
    display(HTML('<h4 style="color:#60a5fa;">Season Comparison</h4>'))
    display(Markdown(comp["answer"][:3000]))
    for rs in comp.get("result_sets", []):
        df = result_set_to_dataframe(rs)
        if not df.empty:
            display(df.head(10))

Alpine Mountain — 2024-2025 Executive Report

Total Visits

103,597

Unique Visitors

7,803

Ticket Revenue

$2,619,109

Avg Ticket Price

$108.94

Total Lift Scans

1,623,967

Avg Wait Time

3.4 min

NPS Score

7.2

Avg Rating

3.43/5

Safety Incidents

528

Patrol Response

9 min

Worst Guest Feedback

	sentiment	@scores	rating	category	feedback_text
0	negative	{'text_match': 1.5123662e-07, 'cosine_similari...	1	Rentals	Poor experience
1	negative	{'text_match': 1.5123662e-07, 'cosine_similari...	3	Rentals	Poor experience
2	negative	{'text_match': 1.5123662e-07, 'cosine_similari...	1	Lifts	Poor experience
3	negative	{'text_match': 1.5123662e-07, 'cosine_similari...	1	Food_Service	Poor experience
4	negative	{'text_match': 1.5123662e-07, 'cosine_similari...	1	Lifts	Poor experience

Season Comparison

Season Performance Comparison: 2023-2024 vs. 2024-2025

The 2024-2025 season performed slightly better operationally, with modest gains in visits and revenue, though customer satisfaction saw a marginal decline.

Key Findings

Visits: The 2024-2025 season attracted 103,597 total visits, up 896 visits (+0.87%) from the prior season’s 102,701. Both seasons had comparable data completeness with 181-182 operating days.

Revenue: Total revenue reached $7,227,690 in 2024-2025, an increase of $50,331 (+0.70%) over 2023-2024’s $7,177,359. All three revenue streams grew: - Food & Beverage: $3,094,287 (+$33,458, +1.09%) - Tickets: $2,619,109 (+$6,222, +0.24%) - Rentals: $1,514,294 (+$10,651, +0.71%)

NPS (Customer Satisfaction): Average NPS declined slightly to 7.24 in 2024-2025 from 7.29 in 2023-2024 (-0.05 points, -0.69%), despite receiving more feedback responses (1,553 vs. 1,530).

Strategic Observations

• Revenue per visit declined slightly: Revenue grew at 0.70% while visits grew at 0.87%, indicating lower yield per guest
• F&B drove revenue growth: Food & beverage accounted for 66% of the revenue increase, suggesting strong ancillary spending
• Satisfaction-growth disconnect: The resort grew visits and revenue but at the expense of customer experience, signaling potential capacity or service quality concerns

Bottom Line: 2024-2025 edges out 2023-2024 on volume and revenue metrics, but the NPS decline warrants attention to maintain long-term guest loyalty and pricing power.

9. Using MCPToolbox and MCPClient Directly

MCPToolbox auto-resolves the default session. For full control, construct MCPClient with an explicit URL and headers factory.

toolbox = MCPToolbox()
print(toolbox)
print(f"Tools: {toolbox.tool_names}")

result = toolbox.call("incident-search", {"query": "avalanche near-miss"})
display_mcp_result(result, label="Incident Search")

from mcp_ski_resort.core import default_session, get_headers
s = default_session()
client = MCPClient(
    url=f"{s.host}/api/v2/databases/{s.database}/schemas/{s.mcp_schema}/mcp-servers/{s.mcp_server_name}",
    headers_factory=get_headers,
)
raw = client.request("tools/list", {})
print(f"Raw JSON-RPC keys: {list(raw.keys())}")

MCPToolbox(tools=?, url='https://trb65519.snowflakecomputing.com/api/v2/databases/AM_SKI_RESORT/schemas/MCP_SERVERS/mcp-servers/ski_resort_mcp')
Tools: ['daily-summary-analyst', 'revenue-analyst', 'operations-analyst', 'resort-executive-agent', 'ski-ops-assistant-agent', 'feedback-search', 'incident-search', 'execute-sql', 'resort-kpi-summary', 'weather-impact-report']

Incident Search

	@scores	DESCRIPTION
0	{'text_match': 0.0, 'cosine_similarity': 0.444...	Lost Skier incident
1	{'text_match': 0.0, 'cosine_similarity': 0.444...	Lost Skier incident
2	{'text_match': 0.0, 'cosine_similarity': 0.444...	Lost Skier incident
3	{'text_match': 0.0, 'cosine_similarity': 0.444...	Lost Skier incident
4	{'text_match': 0.0, 'cosine_similarity': 0.444...	Lost Skier incident
5	{'text_match': 0.0, 'cosine_similarity': 0.444...	Lost Skier incident
6	{'text_match': 0.0, 'cosine_similarity': 0.444...	Lost Skier incident
7	{'text_match': 0.0, 'cosine_similarity': 0.444...	Lost Skier incident
8	{'text_match': 0.0, 'cosine_similarity': 0.444...	Lost_Skier incident on 2025-03-16
9	{'text_match': 0.0, 'cosine_similarity': 0.444...	Lost_Skier incident on 2025-02-10

Raw JSON-RPC keys: ['jsonrpc', 'id', 'result']