This content originally appeared on DEV Community and was authored by Armaan Khan
# user360.py
import streamlit as st
import pandas as pd
from datetime import date, timedelta
from typing import Dict, Any, List, Tuple, Optional
import numpy as np # For numerical operations like nansum
# Import the FinOps Dashboard API (assuming your framework is updated as per previous instructions)
from finops_framework.api import FinOpsDashboard
from finops_framework.exceptions import DataFetchError
# --- 1. Global Configurations (Reused for consistency) ---
# Ensure your finops_framework/ui/visual_renderers.py supports 'color_col' and 'sort_desc'
MY_APP_THEME = {
"primary_color": "#00BFFF", # Deep sky blue
"background_color": "#1C2833", # Dark slate gray
"secondary_background_color": "#283747",
"text_color": "#EAECEE",
"font": "Segoe UI",
"chart_height": 340, # Slightly taller charts
"chart_colors": ["#00BFFF", "#FFD700", "#FF6347", "#3CB371", "#BA55D3", "#FFA07A", "#20B2AA", "#7B68EE", "#FF69B4", "#ADFF2F"]
}
MY_DATE_RANGE_OPTIONS = {
"Last 7 Days": 7,
"Last 30 Days": 30,
"Last 90 Days": 90, # Default for initial load to get more data
"Last 1 Year": 365,
"Custom Range": None
}
# --- 2. SQL Query Definitions (User-Specific and Comprehensive) ---
# All user-specific queries will include a :selected_user_name parameter.
USER_360_SQL_QUERIES = {
"LIST_ACTIVE_USERS_BY_COST": """
SELECT
USER_NAME,
SUM(CREDITS_USED * 2) AS ESTIMATED_COST_USD
FROM
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
START_TIME >= DATEADD(day, -90, CURRENT_DATE()) -- Users active in last 90 days
GROUP BY
USER_NAME
ORDER BY
ESTIMATED_COST_USD DESC;
""",
"USER_360_SUMMARY_METRICS": """
SELECT
SUM(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN CREDITS_USED ELSE 0 END) AS TOTAL_CREDITS,
SUM(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN CREDITS_USED * 2 ELSE 0 END) AS ESTIMATED_COST_USD,
SUM(CASE WHEN START_TIME BETWEEN :prev_start_date AND :prev_end_date THEN CREDITS_USED ELSE 0 END) AS PREV_TOTAL_CREDITS,
SUM(CASE WHEN START_TIME BETWEEN :prev_start_date AND :prev_end_date THEN CREDITS_USED * 2 ELSE 0 END) AS PREV_ESTIMATED_COST_USD,
AVG(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN TOTAL_ELAPSED_TIME ELSE NULL END) / 1000 AS AVG_QUERY_DURATION_SEC,
AVG(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN CREDITS_USED ELSE NULL END) AS AVG_CREDITS_PER_QUERY,
COUNT(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN QUERY_ID ELSE NULL END) AS TOTAL_QUERY_COUNT,
COUNT(CASE WHEN START_TIME BETWEEN :start_date AND :end_date AND ERROR_MESSAGE IS NOT NULL THEN QUERY_ID ELSE NULL END) AS FAILED_QUERY_COUNT,
SUM(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN BYTES_SCANNED ELSE 0 END) AS TOTAL_BYTES_SCANNED,
SUM(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN COMPILATION_TIME ELSE 0 END) / 1000 AS TOTAL_COMPILATION_TIME_SEC
FROM
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
USER_NAME = :selected_user_name;
""",
"USER_DAILY_CREDIT_CONSUMPTION": """
SELECT
TO_DATE(START_TIME) AS USAGE_DAY,
SUM(CREDITS_USED) AS DAILY_CREDITS_USED,
SUM(CREDITS_USED * 2) AS DAILY_ESTIMATED_COST_USD
FROM
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
GROUP BY
1
ORDER BY
1;
""",
"USER_WAREHOUSE_COST_BREAKDOWN": """
SELECT
WAREHOUSE_NAME,
SUM(CREDITS_USED * 2) AS ESTIMATED_COST_USD,
SUM(CREDITS_USED) AS TOTAL_CREDITS
FROM
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
GROUP BY
WAREHOUSE_NAME
ORDER BY
ESTIMATED_COST_USD DESC
LIMIT 10;
""",
"USER_ROLE_COST_BREAKDOWN": """
SELECT
ROLE_NAME,
SUM(CREDITS_USED * 2) AS ESTIMATED_COST_USD,
SUM(CREDITS_USED) AS TOTAL_CREDITS
FROM
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
GROUP BY
ROLE_NAME
ORDER BY
ESTIMATED_COST_USD DESC
LIMIT 10;
""",
"USER_QUERY_TYPE_DISTRIBUTION": """
SELECT
QUERY_TYPE,
COUNT(*) AS QUERY_COUNT,
SUM(CREDITS_USED) AS TOTAL_CREDITS,
SUM(CREDITS_USED * 2) AS ESTIMATED_COST_USD
FROM
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
GROUP BY
QUERY_TYPE
ORDER BY
ESTIMATED_COST_USD DESC
LIMIT 10;
""",
"USER_TOP_EXPENSIVE_QUERIES": """
SELECT
QUERY_ID,
QUERY_TEXT,
WAREHOUSE_NAME,
ROLE_NAME,
TOTAL_ELAPSED_TIME / 1000 AS TOTAL_ELAPSED_TIME_SEC,
CREDITS_USED,
CREDITS_USED * 2 AS ESTIMATED_COST_USD,
BYTES_SCANNED / POW(1024,3) AS BYTES_SCANNED_GB, -- Convert to GB
BYTES_SPILLED_TO_LOCAL_STORAGE / POW(1024,3) AS LOCAL_SPILL_GB,
BYTES_SPILLED_TO_REMOTE_STORAGE / POW(1024,3) AS REMOTE_SPILL_GB,
START_TIME
FROM
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
AND CREDITS_USED IS NOT NULL
ORDER BY
CREDITS_USED DESC
LIMIT 20; -- Increased to 20 for more detailed review
""",
"USER_TOP_LONG_RUNNING_QUERIES": """
SELECT
QUERY_ID,
QUERY_TEXT,
WAREHOUSE_NAME,
ROLE_NAME,
TOTAL_ELAPSED_TIME / 1000 AS TOTAL_ELAPSED_TIME_SEC,
EXECUTION_TIME / 1000 AS EXECUTION_TIME_SEC,
QUEUED_OVERLOAD_TIME / 1000 AS QUEUED_OVERLOAD_TIME_SEC,
COMPILATION_TIME / 1000 AS COMPILATION_TIME_SEC,
CREDITS_USED,
CREDITS_USED * 2 AS ESTIMATED_COST_USD,
START_TIME
FROM
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
AND TOTAL_ELAPSED_TIME IS NOT NULL
ORDER BY
TOTAL_ELAPSED_TIME DESC
LIMIT 20; -- Increased to 20
""",
"USER_WAREHOUSE_UTILIZATION_OVERVIEW": """
SELECT
WH.WAREHOUSE_NAME,
WH.SIZE,
WH.AUTO_SUSPEND,
SUM(WMH.CREDITS_USED) AS TOTAL_CREDITS,
-- Calculate approximate idle time (if no credits used, but warehouse was on for this user)
-- This is tricky for user-specific. Let's simplify: check if ANY of user's queries ran,
-- and if the warehouse was generally idle by ACCOUNT_USAGE
SUM(CASE WHEN WMH.CREDITS_USED = 0 AND WMH.WAREHOUSE_ID IN (SELECT DISTINCT WAREHOUSE_ID FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date) THEN 1 ELSE 0 END) AS USER_INCURRED_IDLE_MINUTES, -- If user used it, and it was idle
COUNT(WMH.WAREHOUSE_ID) AS TOTAL_MINUTES_ON_FOR_WAREHOUSE,
(USER_INCURRED_IDLE_MINUTES * 100.0 / NULLIF(TOTAL_MINUTES_ON_FOR_WAREHOUSE, 0)) AS USER_INCURRED_IDLE_PERCENTAGE
FROM
SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSES WH
JOIN
SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY WMH
ON WH.WAREHOUSE_ID = WMH.WAREHOUSE_ID
WHERE
WMH.START_TIME BETWEEN :start_date AND :end_date
AND WMH.WAREHOUSE_ID IN (SELECT DISTINCT WAREHOUSE_ID FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date) -- Only warehouses used by this user
GROUP BY
WH.WAREHOUSE_NAME, WH.SIZE, WH.AUTO_SUSPEND
HAVING USER_INCURRED_IDLE_PERCENTAGE IS NOT NULL AND USER_INCURRED_IDLE_PERCENTAGE > 5 -- Only show warehouses with some user-related idle time
ORDER BY USER_INCURRED_IDLE_PERCENTAGE DESC
LIMIT 5;
"""
}
# --- 3. Metric Card Configurations (User-Specific and Comprehensive) ---
USER_360_METRIC_CARDS_CONFIG = [
{
"label": "Total Credits (User)",
"query_id": "USER_360_SUMMARY_METRICS",
"value_col": "TOTAL_CREDITS",
"delta_col": "PREV_TOTAL_CREDITS",
"format_value": "{:,.0f}",
"format_delta": "{:+.2%}",
"help_text": "Total Snowflake credits consumed by this user in the selected period."
},
{
"label": "Estimated Cost (User)",
"query_id": "USER_360_SUMMARY_METRICS",
"value_col": "ESTIMATED_COST_USD",
"delta_col": "PREV_ESTIMATED_COST_USD",
"format_value": "${:,.2f}",
"format_delta": "{:+.2%}",
"help_text": "Estimated cost for this user (assuming $2/credit)."
},
{
"label": "Avg. Query Duration",
"query_id": "USER_360_SUMMARY_METRICS",
"value_col": "AVG_QUERY_DURATION_SEC",
"delta_col": None,
"format_value": "{:,.1f} s",
"help_text": "Average execution time of queries run by this user."
},
{
"label": "Avg. Credits/Query",
"query_id": "USER_360_SUMMARY_METRICS",
"value_col": "AVG_CREDITS_PER_QUERY",
"delta_col": None,
"format_value": "{:,.3f}",
"help_text": "Average credits consumed per query by this user. High value might indicate inefficient queries."
},
{
"label": "Total Query Count",
"query_id": "USER_360_SUMMARY_METRICS",
"value_col": "TOTAL_QUERY_COUNT",
"delta_col": None, # Could add prev_total_query_count if query supports it
"format_value": "{:,.0f}",
"help_text": "Total number of queries executed by this user."
},
{
"label": "Failed Query Rate",
"query_id": "USER_360_SUMMARY_METRICS",
"value_col": "FAILED_QUERY_RATE", # Calculated in Python
"delta_col": None,
"format_value": "{:.1%}",
"help_text": "Percentage of queries that failed for this user. High rate indicates issues."
},
{
"label": "Total Data Scanned",
"query_id": "USER_360_SUMMARY_METRICS",
"value_col": "TOTAL_BYTES_SCANNED",
"delta_col": None,
"format_value": "{:,.2f} GB", # Will convert bytes to GB in Python
"help_text": "Total data scanned by user's queries. High volume can lead to high cost."
}
]
# --- 4. Chart Configurations (User-Specific and Organized by Tab) ---
USER_360_CHART_CONFIGS_BY_TAB = {
"Cost & Usage Overview": [
{
"title": "User's Daily Credit Consumption Trend",
"description": "Visualizes daily credit consumption by the selected user to identify personal trends.",
"query_id": "USER_DAILY_CREDIT_CONSUMPTION",
"chart_type": "line",
"x_col": "USAGE_DAY",
"y_col": "DAILY_CREDITS_USED",
"x_axis_title": "Date",
"y_axis_title": "Credits Used"
},
{
"title": "User's Estimated Cost by Query Type",
"description": "Breakdown of estimated costs by the types of queries run by this user (e.g., SELECT, DML, DDL).",
"query_id": "USER_QUERY_TYPE_DISTRIBUTION",
"chart_type": "bar",
"x_col": "QUERY_TYPE",
"y_col": "ESTIMATED_COST_USD",
"x_axis_title": "Query Type",
"y_axis_title": "Estimated Cost (USD)",
"sort_desc": True # Sort by Y-axis descending
}
],
"Resource Utilization & Roles": [
{
"title": "User's Top 10 Warehouses by Estimated Cost",
"description": "Warehouses where the user incurred the highest estimated costs. Consider if the right warehouse size/type is being used.",
"query_id": "USER_WAREHOUSE_COST_BREAKDOWN",
"chart_type": "bar",
"x_col": "WAREHOUSE_NAME",
"y_col": "ESTIMATED_COST_USD",
"x_axis_title": "Warehouse Name",
"y_axis_title": "Estimated Cost (USD)",
"sort_desc": True
},
{
"title": "User's Top 10 Roles by Estimated Cost",
"description": "Snowflake roles the user frequently assumed that incurred the highest estimated costs. Review role privileges and usage patterns.",
"query_id": "USER_ROLE_COST_BREAKDOWN",
"chart_type": "bar",
"x_col": "ROLE_NAME",
"y_col": "ESTIMATED_COST_USD",
"x_axis_title": "Role Name",
"y_axis_title": "Estimated Cost (USD)",
"sort_desc": True
},
{
"title": "Underutilized Warehouses (Used by User)",
"description": "Warehouses used by this user that exhibit significant idle time. Opportunities to adjust AUTO_SUSPEND.",
"query_id": "USER_WAREHOUSE_UTILIZATION_OVERVIEW",
"chart_type": "table" # Display as table for detailed review
}
],
"Query Performance Deep Dive": [
{
"title": "User's Top 20 Most Expensive Queries",
"description": "The user's queries that consumed the most credits. Prioritize these for optimization, examining `QUERY_TEXT`.",
"query_id": "USER_TOP_EXPENSIVE_QUERIES",
"chart_type": "table"
},
{
"title": "User's Top 20 Longest Running Queries",
"description": "The user's queries with the longest execution times. Analyze `QUEUED_OVERLOAD_TIME` and `COMPILATION_TIME` for bottlenecks.",
"query_id": "USER_TOP_LONG_RUNNING_QUERIES",
"chart_type": "table"
}
]
}
# --- 5. User-Specific Recommendation Logic (Enhanced) ---
def display_user_360_recommendations(dashboard_instance: FinOpsDashboard,
sql_queries: Dict[str, str],
start_date: date, end_date: date,
selected_user_name: str,
summary_data: pd.DataFrame): # Pass summary data for efficiency
"""Generates and displays comprehensive, actionable recommendations for the selected user."""
st.markdown(f"### Actionable Insights for **`{selected_user_name}`**")
st.markdown("---")
recommendations_count = 0
# Parameters for all user-specific queries
params = {
"start_date": start_date.isoformat(),
"end_date": end_date.isoformat(),
"selected_user_name": selected_user_name
}
# --- Recommendation 1: High Cost/Inefficient Queries ---
try:
expensive_queries_data = dashboard_instance._data_fetcher.execute_query(
sql_queries["USER_TOP_EXPENSIVE_QUERIES"], params
)
if not expensive_queries_data.empty:
top_cost = expensive_queries_data["ESTIMATED_COST_USD"].max()
if top_cost > 5: # Threshold for a single "highly expensive" query (adjust as needed)
recommendations_count += 1
top_query = expensive_queries_data.iloc[0]
st.markdown(f"<span style='color:#FF6347; font-weight:bold;'>⚠ CRITICAL: Highly Expensive Query Detected!</span>", unsafe_allow_html=True)
st.markdown(f"**Why it matters:** A single query (ID `{top_query['QUERY_ID']}`) cost **${top_query['ESTIMATED_COST_USD']:.2f}**, contributing significantly to your overall spend.")
st.markdown(f"**Recommended Action:** Review the `QUERY_TEXT` for inefficiencies. Look for: large `BYTES_SCANNED_GB`, high `LOCAL_SPILL_GB`/`REMOTE_SPILL_GB`. Consider: ")
st.markdown(f"- **Optimizing joins/filters.**")
st.markdown(f"- **Materialized Views/Clustering:** If querying frequently used large datasets.")
st.markdown(f"- **Warehouse Size:** Is the warehouse (`{top_query['WAREHOUSE_NAME']}`) appropriate for this workload?")
st.info(f"👉 **Potential Savings:** Up to {top_query['ESTIMATED_COST_USD']:.2f} if this query is optimized or runs less frequently.")
st.info(f"📍 See **'Query Performance Deep Dive'** tab for 'User's Top 20 Most Expensive Queries'.")
st.markdown("---")
elif top_cost > 1: # Moderate
recommendations_count += 1
top_query = expensive_queries_data.iloc[0]
st.markdown(f"<span style='color:#FFD700; font-weight:bold;'>⚡ HIGH: Moderately Expensive Query</span>", unsafe_allow_html=True)
st.markdown(f"**Why it matters:** Query ID `{top_query['QUERY_ID']}` cost **${top_query['ESTIMATED_COST_USD']:.2f}**. Repeated execution can add up.")
st.markdown(f"**Recommended Action:** Review query plan, check for unnecessary scans. Consider minor optimizations or scheduled runs during off-peak hours.")
st.info(f"📍 See **'Query Performance Deep Dive'** tab for 'User's Top 20 Most Expensive Queries'.")
st.markdown("---")
except DataFetchError as e:
st.error(f"Error fetching data for expensive query recommendation: {e}")
# --- Recommendation 2: High Failed Query Rate ---
if not summary_data.empty:
total_queries = summary_data["TOTAL_QUERY_COUNT"].iloc[0]
failed_queries = summary_data["FAILED_QUERY_COUNT"].iloc[0]
failed_rate = (failed_queries / total_queries) if total_queries > 0 else 0
if failed_rate > 0.15 and total_queries > 10: # More than 15% failed, and at least 10 queries
recommendations_count += 1
st.markdown(f"<span style='color:#FF6347; font-weight:bold;'>⚠ CRITICAL: High Failed Query Rate ({failed_rate:.1%})</span>", unsafe_allow_html=True)
st.markdown(f"**Why it matters:** A high failure rate indicates consistent issues with queries, potentially wasting compute resources or indicating data pipeline problems.")
st.markdown(f"**Recommended Action:** Investigate the specific queries that are failing. Common reasons include: syntax errors, permission issues, missing data, or timeouts. Review your recent query history for error messages.")
st.info(f"📍 See Snowflake's `QUERY_HISTORY` for detailed error messages associated with failed queries.")
st.markdown("---")
# --- Recommendation 3: Underutilized Warehouses (Used by User) ---
try:
underutilized_wh_data = dashboard_instance._data_fetcher.execute_query(
sql_queries["USER_WAREHOUSE_UTILIZATION_OVERVIEW"], params
)
if not underutilized_wh_data.empty:
for index, row in underutilized_wh_data.iterrows():
idle_percent = row['USER_INCURRED_IDLE_PERCENTAGE']
wh_name = row['WAREHOUSE_NAME']
current_suspend = row['AUTO_SUSPEND']
user_incurred_credits = row['TOTAL_CREDITS'] # Credits incurred by this user on this warehouse
if idle_percent > 20 and user_incurred_credits > 0: # Significant idle time for user-related activity
recommendations_count += 1
st.markdown(f"<span style='color:#FFD700; font-weight:bold;'>⚡ HIGH: Underutilized Warehouse ({wh_name})</span>", unsafe_allow_html=True)
st.markdown(f"**Why it matters:** You've used warehouse `{wh_name}`, which shows **{idle_percent:.1f}%** idle time (based on your activity). This means it's running but not doing work, costing credits.")
st.markdown(f"**Recommended Action:** Consider adjusting its `AUTO_SUSPEND` parameter. Current: `{current_suspend}` seconds. A lower value (e.g., 60 or 120 seconds) for this warehouse could save credits. If you don't control this warehouse, discuss with the administrator.")
# Rough potential savings (very simplified)
estimated_idle_cost = (user_incurred_credits * 2) * (idle_percent / 100)
st.info(f"👉 **Potential Savings:** Estimated ${estimated_idle_cost:.2f} over the period by reducing idle time.")
st.info(f"📍 See **'Resource Utilization & Roles'** tab for 'Underutilized Warehouses (Used by User)' table.")
st.markdown("---")
except DataFetchError as e:
st.error(f"Error fetching data for underutilized warehouse recommendation: {e}")
# --- Recommendation 4: Queries with High Disk Spilling ---
try:
expensive_queries_data = dashboard_instance._data_fetcher.execute_query( # Re-using this query
sql_queries["USER_TOP_EXPENSIVE_QUERIES"], params
)
spill_queries = expensive_queries_data[(expensive_queries_data['LOCAL_SPILL_GB'] > 0.1) | (expensive_queries_data['REMOTE_SPILL_GB'] > 0.1)] # Threshold > 0.1GB spill
if not spill_queries.empty:
recommendations_count += 1
st.markdown(f"<span style='color:#3CB371; font-weight:bold;'>💡 MEDIUM: Queries with Data Spilling</span>", unsafe_allow_html=True)
st.markdown(f"**Why it matters:** When queries spill data to local or remote disk, it means the warehouse didn't have enough memory, slowing down queries and potentially increasing costs.")
st.markdown(f"**Recommended Action:** Review the top {len(spill_queries)} queries with spilling (e.g., query ID `{spill_queries.iloc[0]['QUERY_ID']}`). Consider:")
st.markdown(f"- **Optimizing query logic:** Reduce data returned, avoid full table scans, use more selective filters.")
st.markdown(f"- **Temporary Warehouse Upsizing:** For very large or complex queries, consider running them on a larger warehouse size if permitted.")
st.info(f"📍 See **'Query Performance Deep Dive'** tab for 'User's Top 20 Most Expensive Queries' (check spill columns).")
st.markdown("---")
except DataFetchError as e:
st.error(f"Error fetching data for spill recommendation: {e}")
# --- Recommendation 5: Consistently Long Running Queries (not necessarily expensive) ---
try:
long_running_queries_data = dashboard_instance._data_fetcher.execute_query(
sql_queries["USER_TOP_LONG_RUNNING_QUERIES"], params
)
if not long_running_queries_data.empty and long_running_queries_data["TOTAL_ELAPSED_TIME_SEC"].max() > 120: # Threshold for long-running > 2 minutes
recommendations_count += 1
top_long_query = long_running_queries_data.iloc[0]
st.markdown(f"<span style='color:#3CB371; font-weight:bold;'>💡 MEDIUM: Long-Running Query Identified</span>", unsafe_allow_html=True)
st.markdown(f"**Why it matters:** Query ID `{top_long_query['QUERY_ID']}` ran for **{top_long_query['TOTAL_ELAPSED_TIME_SEC']:.1f} seconds**. While not always highest cost, long-running queries can indicate performance bottlenecks and user frustration.")
st.markdown(f"**Recommended Action:** Analyze the query profile. Pay attention to: ")
st.markdown(f"- `QUEUED_OVERLOAD_TIME_SEC`: Indicates warehouse concurrency issues. Consider a larger warehouse or splitting workload.")
st.markdown(f"- `COMPILATION_TIME_SEC`: If high, the query itself is complex or metadata is slow. Optimize logic or review schema.")
st.info(f"📍 See **'Query Performance Deep Dive'** tab for 'User's Top 20 Longest Running Queries'.")
st.markdown("---")
except DataFetchError as e:
st.error(f"Error fetching data for long-running query recommendation: {e}")
# Fallback if no specific recommendations found
if recommendations_count == 0:
st.success("🎉 **Great job!** No immediate high-priority optimization opportunities detected for this user in the selected period. Keep up the efficient work!")
st.markdown("*(Recommendations are based on specific thresholds and available data. For deeper insights, explore the other tabs.)*")
st.markdown("---")
# --- 6. Main User Dashboard Run Function ---
def run_user_360_dashboard():
# Initialize the dashboard framework
dashboard = FinOpsDashboard(
app_title="Snowflake User 360 FinOps View",
app_subtitle="Deep Dive into User Consumption, Performance, and Optimization",
theme_config=MY_APP_THEME
)
# --- Sidebar Controls ---
st.sidebar.header("Filter & User Selection")
# 1. User Selection (Most important for this page)
selected_user_name: Optional[str] = None
try:
# Fetch list of active users, ordered by cost for a better default selection
active_users_df = dashboard._data_fetcher.execute_query(USER_360_SQL_QUERIES["LIST_ACTIVE_USERS_BY_COST"], {})
if active_users_df.empty:
st.warning("No active users found in the last 90 days. Please ensure your Snowflake ACCOUNT_USAGE is populated and user activity exists.")
st.stop() # Halts execution for no users
user_names = active_users_df["USER_NAME"].tolist()
# Default to the user with the highest cost
default_user_index = 0
if not active_users_df.empty:
highest_cost_user = active_users_df.iloc[0]["USER_NAME"]
if highest_cost_user in user_names:
default_user_index = user_names.index(highest_cost_user)
selected_user_name = st.sidebar.selectbox(
"Select User:",
user_names,
index=default_user_index,
help="Choose a Snowflake user to view their FinOps insights. Users ordered by estimated cost."
)
except DataFetchError as e:
st.error(f"Error fetching user list: {e}. Please check Snowflake connection and query permissions.")
st.stop() # Halts execution on data fetch error for user list
except Exception as e:
st.error(f"An unexpected error occurred while setting up user selection: {e}")
st.exception(e)
st.stop()
if not selected_user_name: # Defensive check
st.warning("No user selected or available.")
st.stop()
# 2. Date Range Selector (in sidebar)
start_date, end_date = dashboard.render_date_filter(
MY_DATE_RANGE_OPTIONS, default_key="Last 90 Days" # Default to 90 days for more data
)
if start_date is None or end_date is None:
st.warning("Please select a valid date range to load data.")
st.stop() # Halts execution if dates are invalid
st.markdown(f"## User 360 View: **`{selected_user_name}`**")
st.markdown("---")
# --- Main Tabbed Layout ---
tab_titles = ["FinOps Action Center", "Cost & Usage Overview",
"Resource Utilization & Roles", "Query Performance Deep Dive"]
selected_tab_idx = st.tabs(tab_titles)
# Prepare base parameters for all user-specific queries
base_params = {
"start_date": start_date.isoformat(),
"end_date": end_date.isoformat(),
"selected_user_name": selected_user_name
}
# Fetch summary data once and pass to relevant sections for efficiency
summary_data_params = base_params.copy()
prev_start_date, prev_end_date = dashboard._data_fetcher.calculate_prev_period_dates(start_date, end_date)
summary_data_params["prev_start_date"] = prev_start_date.isoformat()
summary_data_params["prev_end_date"] = prev_end_date.isoformat()
user_summary_df: pd.DataFrame = pd.DataFrame()
try:
user_summary_df = dashboard._data_fetcher.execute_query(
USER_360_SQL_QUERIES["USER_360_SUMMARY_METRICS"], summary_data_params
)
except DataFetchError as e:
st.error(f"Error fetching user summary metrics: {e}")
st.info("Some sections may not display correctly due to missing summary data.")
except Exception as e:
st.error(f"An unexpected error occurred fetching user summary metrics: {e}")
st.exception(e)
with selected_tab_idx[0]: # FinOps Action Center Tab
display_user_360_recommendations(dashboard, USER_360_SQL_QUERIES, start_date, end_date, selected_user_name, user_summary_df)
with selected_tab_idx[1]: # Cost & Usage Overview Tab
dashboard.display_section_header("User's Performance & Cost Summary", "Key metrics and trends for the selected user's activity.")
# --- Metric Cards for User Summary ---
updated_user_metrics = USER_360_METRIC_CARDS_CONFIG.copy()
if not user_summary_df.empty:
summary_row = user_summary_df.iloc[0]
for config in updated_user_metrics:
current_val = summary_row.get(config["value_col"])
config["value"] = current_val
if config["label"] == "Total Data Scanned": # Convert bytes to GB
config["value"] = (current_val / (1024**3)) if current_val is not None else 0
elif config["label"] == "Failed Query Rate":
total_q = summary_row.get("TOTAL_QUERY_COUNT", 0)
failed_q = summary_row.get("FAILED_QUERY_COUNT", 0)
config["value"] = (failed_q / total_q) if total_q > 0 else 0
else: # For other metrics, handle delta
if config.get("delta_col") and summary_row.get(config["delta_col"]) is not None:
prev_val = summary_row[config["delta_col"]]
if prev_val is not None and prev_val != 0:
config["delta"] = ((current_val - prev_val) / prev_val) * 100
else:
config["delta"] = None if current_val == 0 else "N/A"
else:
config["delta"] = None
else: # If no data for user, set all to 0/N/A
for config in updated_user_metrics:
config["value"] = 0
config["delta"] = None
config["help_text"] = "No data for this user in selected period."
# Render metrics
cols = st.columns(len(updated_user_metrics))
for i, config in enumerate(updated_user_metrics):
with cols[i]:
dashboard._visual_renderer.render_metric_card(
config,
config.get("value", "N/A"),
config.get("delta")
)
# --- Charts for User Overview ---
dashboard.display_charts_section(
section_title="", # Empty, as header is above
chart_configs=USER_360_CHART_CONFIGS_BY_TAB["Cost & Usage Overview"],
sql_queries=USER_360_SQL_QUERIES, # Use user-specific queries
current_start_date=start_date,
current_end_date=end_date,
extra_query_params={"selected_user_name": selected_user_name} # Pass the user parameter
)
with selected_tab_idx[2]: # Resource Utilization & Roles Tab
dashboard.display_section_header("User's Resource & Role Utilization", "Insights into which warehouses and roles drive the user's costs.")
dashboard.display_charts_section(
section_title="",
chart_configs=USER_360_CHART_CONFIGS_BY_TAB["Resource Utilization & Roles"],
sql_queries=USER_360_SQL_QUERIES,
current_start_date=start_date,
current_end_date=end_date,
extra_query_params={"selected_user_name": selected_user_name}
)
with selected_tab_idx[3]: # Query Performance Deep Dive Tab
dashboard.display_section_header("User's Query Performance & Optimization Details", "Detailed analysis of expensive and slow queries for targeted tuning.")
dashboard.display_charts_section(
section_title="",
chart_configs=USER_360_CHART_CONFIGS_BY_TAB["Query Performance Deep Dive"],
sql_queries=USER_360_SQL_QUERIES,
current_start_date=start_date,
current_end_date=end_date,
extra_query_params={"selected_user_name": selected_user_name}
)
if __name__ == "__main__":
run_user_360_dashboard()
This content originally appeared on DEV Community and was authored by Armaan Khan