import streamlit as st
import pandas as pd
import json
from datetime import datetime
# Configure page
st.set_page_config(
page_title="Snowflake Query Optimizer",
page_icon="❄️",
layout="wide",
initial_sidebar_state="collapsed"
)
# Enhanced CSS for modern UI
st.markdown("""
<style>
/* Import Google Fonts */
@import url('https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700&display=swap');
/* Reset and base styles */
.main {
padding: 1rem 2rem;
font-family: 'Inter', sans-serif;
background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%);
min-height: 100vh;
}
/* Header with glassmorphism effect */
.header-container {
background: rgba(255, 255, 255, 0.25);
backdrop-filter: blur(20px);
border: 1px solid rgba(255, 255, 255, 0.18);
padding: 3rem;
border-radius: 25px;
margin-bottom: 3rem;
color: #2c3e50;
text-align: center;
box-shadow: 0 20px 40px rgba(0,0,0,0.1);
position: relative;
overflow: hidden;
}
.header-container::before {
content: '';
position: absolute;
top: -50%;
left: -50%;
width: 200%;
height: 200%;
background: linear-gradient(45deg, transparent, rgba(255,255,255,0.1), transparent);
animation: shimmer 3s infinite;
}
@keyframes shimmer {
0% { transform: translateX(-100%) translateY(-100%) rotate(45deg); }
100% { transform: translateX(100%) translateY(100%) rotate(45deg); }
}
.header-title {
font-size: 3.5rem;
font-weight: 700;
margin-bottom: 1rem;
background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
-webkit-background-clip: text;
-webkit-text-fill-color: transparent;
background-clip: text;
position: relative;
z-index: 1;
}
.header-subtitle {
font-size: 1.4rem;
opacity: 0.8;
font-weight: 400;
color: #34495e;
position: relative;
z-index: 1;
}
/* Floating metrics cards with micro-interactions */
.metrics-container {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
gap: 1.5rem;
margin-bottom: 3rem;
}
.metric-card {
background: rgba(255, 255, 255, 0.9);
backdrop-filter: blur(10px);
padding: 2rem;
border-radius: 20px;
border: 1px solid rgba(255, 255, 255, 0.2);
box-shadow: 0 8px 32px rgba(0,0,0,0.1);
transition: all 0.4s cubic-bezier(0.175, 0.885, 0.32, 1.275);
position: relative;
overflow: hidden;
cursor: pointer;
}
.metric-card:hover {
transform: translateY(-10px) scale(1.02);
box-shadow: 0 20px 60px rgba(0,0,0,0.15);
background: rgba(255, 255, 255, 0.95);
}
.metric-card::before {
content: '';
position: absolute;
top: 0;
left: 0;
right: 0;
height: 4px;
background: linear-gradient(90deg, #667eea, #764ba2, #f093fb, #f5576c);
background-size: 400% 400%;
animation: gradient 3s ease infinite;
}
@keyframes gradient {
0% { background-position: 0% 50%; }
50% { background-position: 100% 50%; }
100% { background-position: 0% 50%; }
}
.metric-value {
font-size: 2.8rem;
font-weight: 700;
color: #2c3e50;
margin-bottom: 0.5rem;
line-height: 1;
}
.metric-label {
color: #7f8c8d;
font-size: 0.95rem;
text-transform: uppercase;
letter-spacing: 1.5px;
font-weight: 500;
}
/* Advanced table with dark theme */
.analysis-table {
background: rgba(255, 255, 255, 0.95);
backdrop-filter: blur(20px);
border-radius: 25px;
overflow: hidden;
box-shadow: 0 20px 60px rgba(0,0,0,0.1);
margin: 2rem 0;
border: 1px solid rgba(255, 255, 255, 0.2);
}
.table-header {
background: linear-gradient(135deg, #2c3e50 0%, #34495e 100%);
color: white;
padding: 2rem;
font-size: 1.5rem;
font-weight: 600;
text-align: center;
position: relative;
overflow: hidden;
}
.table-header::before {
content: '';
position: absolute;
top: -2px;
left: -2px;
right: -2px;
bottom: -2px;
background: linear-gradient(45deg, #667eea, #764ba2, #f093fb, #f5576c);
z-index: -1;
animation: gradient 3s ease infinite;
}
/* User cell styling with gradient animation */
.user-cell {
background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
color: white;
font-weight: 700;
font-size: 1.1rem;
border-radius: 15px;
padding: 15px;
text-align: center;
margin: 4px;
transition: all 0.3s ease;
cursor: pointer;
position: relative;
overflow: hidden;
}
.user-cell:hover {
transform: scale(1.05);
box-shadow: 0 8px 25px rgba(102, 126, 234, 0.3);
}
.user-cell::before {
content: '';
position: absolute;
top: 0;
left: -100%;
width: 100%;
height: 100%;
background: linear-gradient(90deg, transparent, rgba(255,255,255,0.2), transparent);
transition: left 0.5s;
}
.user-cell:hover::before {
left: 100%;
}
/* Enhanced button styling */
.stButton > button {
background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
color: white;
border: none;
border-radius: 12px;
padding: 12px 20px;
font-weight: 600;
font-size: 1rem;
transition: all 0.3s cubic-bezier(0.175, 0.885, 0.32, 1.275);
width: 100%;
margin: 4px;
box-shadow: 0 4px 15px rgba(102, 126, 234, 0.2);
position: relative;
overflow: hidden;
}
.stButton > button:hover {
transform: translateY(-3px) scale(1.05);
box-shadow: 0 10px 30px rgba(102, 126, 234, 0.4);
background: linear-gradient(135deg, #764ba2 0%, #667eea 100%);
}
.stButton > button:active {
transform: translateY(-1px) scale(1.02);
}
/* Zero value styling with subtle animation */
.zero-button {
background: linear-gradient(135deg, #ecf0f1 0%, #bdc3c7 100%) !important;
color: #95a5a6 !important;
cursor: default !important;
animation: pulse 2s infinite;
}
@keyframes pulse {
0%, 100% { opacity: 0.6; }
50% { opacity: 0.8; }
}
.zero-button:hover {
transform: none !important;
box-shadow: none !important;
background: linear-gradient(135deg, #ecf0f1 0%, #bdc3c7 100%) !important;
}
/* Status badges with glow effect */
.status-high {
background: linear-gradient(135deg, #e74c3c 0%, #c0392b 100%);
color: white;
padding: 10px 16px;
border-radius: 20px;
font-weight: 600;
font-size: 0.9rem;
text-align: center;
box-shadow: 0 0 20px rgba(231, 76, 60, 0.3);
animation: glow-red 2s ease-in-out infinite alternate;
}
.status-normal {
background: linear-gradient(135deg, #27ae60 0%, #229954 100%);
color: white;
padding: 10px 16px;
border-radius: 20px;
font-weight: 600;
font-size: 0.9rem;
text-align: center;
box-shadow: 0 0 20px rgba(39, 174, 96, 0.3);
animation: glow-green 2s ease-in-out infinite alternate;
}
@keyframes glow-red {
from { box-shadow: 0 0 20px rgba(231, 76, 60, 0.3); }
to { box-shadow: 0 0 30px rgba(231, 76, 60, 0.5); }
}
@keyframes glow-green {
from { box-shadow: 0 0 20px rgba(39, 174, 96, 0.3); }
to { box-shadow: 0 0 30px rgba(39, 174, 96, 0.5); }
}
/* Details page styling */
.details-container {
background: rgba(255, 255, 255, 0.95);
backdrop-filter: blur(20px);
border-radius: 25px;
padding: 3rem;
margin: 2rem 0;
box-shadow: 0 20px 60px rgba(0,0,0,0.1);
border: 1px solid rgba(255, 255, 255, 0.2);
}
.details-header {
background: linear-gradient(135deg, #2c3e50 0%, #34495e 100%);
color: white;
padding: 2rem;
border-radius: 20px;
margin-bottom: 2rem;
text-align: center;
position: relative;
overflow: hidden;
}
.details-header::before {
content: '';
position: absolute;
top: -50%;
left: -50%;
width: 200%;
height: 200%;
background: linear-gradient(45deg, transparent, rgba(255,255,255,0.1), transparent);
animation: shimmer 3s infinite;
}
.query-box {
background: linear-gradient(135deg, #f8f9fa 0%, #e9ecef 100%);
border: 2px solid #dee2e6;
border-radius: 15px;
padding: 2rem;
margin: 1.5rem 0;
font-family: 'Monaco', 'Menlo', 'Ubuntu Mono', monospace;
white-space: pre-wrap;
max-height: 400px;
overflow-y: auto;
box-shadow: inset 0 4px 12px rgba(0,0,0,0.05);
position: relative;
font-size: 0.95rem;
line-height: 1.5;
}
.query-box::before {
content: '';
position: absolute;
top: 0;
left: 0;
right: 0;
height: 4px;
background: linear-gradient(90deg, #667eea, #764ba2);
border-radius: 15px 15px 0 0;
}
/* Recommendations with enhanced styling */
.recommendation-item {
background: linear-gradient(135deg, #ffeaa7 0%, #fab1a0 100%);
color: #2d3436;
padding: 15px 25px;
margin: 12px 0;
border-radius: 25px;
font-weight: 500;
box-shadow: 0 6px 20px rgba(250, 177, 160, 0.3);
transition: all 0.3s cubic-bezier(0.175, 0.885, 0.32, 1.275);
position: relative;
overflow: hidden;
}
.recommendation-item:hover {
transform: translateX(15px) scale(1.02);
box-shadow: 0 10px 30px rgba(250, 177, 160, 0.4);
}
.recommendation-item::before {
content: '💡';
position: absolute;
left: -30px;
top: 50%;
transform: translateY(-50%);
font-size: 1.2rem;
transition: all 0.3s ease;
}
.recommendation-item:hover::before {
left: 10px;
}
/* Back button with special styling */
.back-button {
background: linear-gradient(135deg, #95a5a6 0%, #7f8c8d 100%);
color: white;
border: none;
border-radius: 15px;
padding: 12px 24px;
font-weight: 600;
font-size: 1rem;
transition: all 0.3s ease;
margin-bottom: 2rem;
box-shadow: 0 4px 15px rgba(149, 165, 166, 0.3);
}
.back-button:hover {
transform: translateX(-5px);
background: linear-gradient(135deg, #7f8c8d 0%, #95a5a6 100%);
box-shadow: 0 6px 20px rgba(149, 165, 166, 0.4);
}
/* Animation classes */
.fadeInUp {
animation: fadeInUp 0.8s ease-out;
}
.fadeInLeft {
animation: fadeInLeft 0.8s ease-out;
}
.fadeInRight {
animation: fadeInRight 0.8s ease-out;
}
@keyframes fadeInUp {
from {
opacity: 0;
transform: translateY(40px);
}
to {
opacity: 1;
transform: translateY(0);
}
}
@keyframes fadeInLeft {
from {
opacity: 0;
transform: translateX(-40px);
}
to {
opacity: 1;
transform: translateX(0);
}
}
@keyframes fadeInRight {
from {
opacity: 0;
transform: translateX(40px);
}
to {
opacity: 1;
transform: translateX(0);
}
}
/* Column headers */
.column-header {
font-weight: 700;
font-size: 0.95rem;
color: #2c3e50;
text-align: center;
padding: 10px;
background: linear-gradient(135deg, #ecf0f1 0%, #bdc3c7 100%);
border-radius: 10px;
margin: 4px;
text-transform: uppercase;
letter-spacing: 1px;
}
/* Metric containers */
.metric-row {
margin-bottom: 1rem;
padding: 1rem;
background: rgba(255, 255, 255, 0.5);
border-radius: 15px;
border: 1px solid rgba(255, 255, 255, 0.3);
}
/* Custom scrollbar */
::-webkit-scrollbar {
width: 8px;
}
::-webkit-scrollbar-track {
background: #f1f1f1;
border-radius: 10px;
}
::-webkit-scrollbar-thumb {
background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
border-radius: 10px;
}
::-webkit-scrollbar-thumb:hover {
background: linear-gradient(135deg, #764ba2 0%, #667eea 100%);
}
</style>
""", unsafe_allow_html=True)
# Initialize session state
if 'page' not in st.session_state:
st.session_state.page = 'overview'
if 'selected_user' not in st.session_state:
st.session_state.selected_user = None
if 'selected_metric' not in st.session_state:
st.session_state.selected_metric = None
if 'selected_count' not in st.session_state:
st.session_state.selected_count = 0
# Sample data
def create_sample_data():
"""Create sample data matching your query structure"""
data = {
'USER_NAME': ['john_doe', 'jane_smith', 'mike_wilson', 'sarah_jones', 'alex_brown', 'emma_davis', 'ryan_miller', 'lisa_taylor'],
'TOTAL_QUERIES': [1250, 890, 2100, 567, 1800, 945, 1567, 723],
'TOTAL_CREDITS': [125.50, 89.30, 310.75, 45.20, 198.40, 112.80, 187.90, 78.60],
'SPILLED_QUERIES': [45, 23, 89, 12, 67, 34, 56, 19],
'OVER_PROVISIONED_QUERIES': [67, 34, 123, 19, 89, 45, 78, 28],
'PEAK_HOUR_LONG_RUNNING_QUERIES': [23, 15, 56, 8, 34, 18, 41, 12],
'SELECT_STAR_QUERIES': [234, 156, 445, 89, 298, 178, 312, 134],
'UNPARTITIONED_SCAN_QUERIES': [89, 45, 167, 23, 112, 67, 98, 41],
'ZERO_RESULT_QUERIES': [34, 21, 78, 12, 45, 28, 52, 16],
'SLOW_QUERIES': [78, 45, 134, 23, 89, 56, 95, 38],
'CARTESIAN_JOIN_QUERIES': [2, 1, 8, 0, 3, 1, 5, 0],
'COST_STATUS': ['Normal', 'Normal', 'High Cost', 'Normal', 'High Cost', 'Normal', 'High Cost', 'Normal'],
'RECOMMENDATIONS': [
['Optimize memory usage or increase warehouse size', 'Specify columns instead of SELECT *'],
['Use smaller warehouses for simple queries', 'Implement query result caching'],
['Reduce data scanned with clustering', 'Schedule long queries off-peak', 'Avoid Cartesian products'],
['Apply query tags for cost attribution', 'Use materialized views for frequent queries'],
['Implement partitioning or clustering', 'Optimize slow-running queries', 'Review warehouse sizing'],
['Cache frequently accessed data', 'Use query result caching'],
['Optimize JOIN operations', 'Implement proper indexing strategies', 'Review query patterns'],
['Use column pruning techniques', 'Implement data compression']
],
'QUERY_SAMPLES': [
{
'spilled': [
{'query_id': 'Q001', 'query_text': 'SELECT cu.customer_id, cu.customer_name, o.order_date, p.product_name\nFROM customers cu\nJOIN orders o ON cu.customer_id = o.customer_id\nJOIN order_items oi ON o.order_id = oi.order_id\nJOIN products p ON oi.product_id = p.product_id\nWHERE o.order_date >= \'2024-01-01\'\nORDER BY o.order_date DESC, cu.customer_name',
'execution_time_ms': 45000, 'bytes_spilled_to_local_storage': 1000000}
],
'select_star': [
{'query_id': 'Q002', 'query_text': 'SELECT * FROM user_activity_logs\nWHERE activity_date BETWEEN \'2024-01-01\' AND \'2024-12-31\'\nLIMIT 1000',
'execution_time_ms': 12000, 'bytes_scanned': 500000000}
]
},
{
'over_provisioned': [
{'query_id': 'Q003', 'query_text': 'SELECT COUNT(*) as total_users\nFROM users\nWHERE status = \'active\'',
'execution_time_ms': 2000, 'warehouse_size': 'LARGE'}
]
},
{
'peak_hour_long_running': [
{'query_id': 'Q004', 'query_text': 'SELECT \n c.category_name,\n COUNT(p.product_id) as product_count,\n AVG(p.price) as avg_price,\n SUM(oi.quantity * p.price) as total_revenue\nFROM categories c\nJOIN products p ON c.category_id = p.category_id\nJOIN order_items oi ON p.product_id = oi.product_id\nJOIN orders o ON oi.order_id = o.order_id\nWHERE o.order_date >= \'2023-01-01\'\nGROUP BY c.category_name\nORDER BY total_revenue DESC',
'execution_time_ms': 450000, 'start_time': '2024-01-15 14:30:00'}
],
'cartesian_join': [
{'query_id': 'Q005', 'query_text': 'SELECT u.username, p.product_name\nFROM users u\nCROSS JOIN products p\nWHERE u.created_date > \'2024-01-01\'',
'execution_time_ms': 180000, 'rows_produced': 1000000}
]
},
{
'zero_result': [
{'query_id': 'Q006', 'query_text': 'SELECT name, email, phone\nFROM customers\nWHERE status = \'inactive\'\n AND last_login_date < \'2020-01-01\'\n AND account_balance > 1000',
'execution_time_ms': 5000}
]
},
{
'unpartitioned_scan': [
{'query_id': 'Q007', 'query_text': 'SELECT \n event_type,\n COUNT(*) as event_count,\n DATE_TRUNC(\'day\', event_timestamp) as event_date\nFROM system_events\nWHERE event_timestamp BETWEEN \'2024-01-01\' AND \'2024-12-31\'\n AND severity_level = \'ERROR\'\nGROUP BY event_type, DATE_TRUNC(\'day\', event_timestamp)\nORDER BY event_date DESC, event_count DESC',
'execution_time_ms': 89000, 'partitions_scanned': 365, 'partitions_total': 365}
],
'slow_query': [
{'query_id': 'Q008', 'query_text': 'WITH customer_metrics AS (\n SELECT \n customer_id,\n COUNT(order_id) as total_orders,\n SUM(order_amount) as total_spent,\n AVG(order_amount) as avg_order_value\n FROM orders\n WHERE order_date >= \'2023-01-01\'\n GROUP BY customer_id\n),\nranked_customers AS (\n SELECT *,\n ROW_NUMBER() OVER (ORDER BY total_spent DESC) as spending_rank\n FROM customer_metrics\n)\nSELECT c.customer_name, rc.*\nFROM ranked_customers rc\nJOIN customers c ON rc.customer_id = c.customer_id\nWHERE rc.total_spent > 10000\nORDER BY rc.spending_rank',
'execution_time_ms': 125000, 'bytes_scanned': 2000000000}
]
},
# Add more sample data for other users
{'over_provisioned': [{'query_id': 'Q009', 'query_text': 'SELECT status FROM orders LIMIT 10', 'execution_time_ms': 1500, 'warehouse_size': 'XLARGE'}]},
{'select_star': [{'query_id': 'Q010', 'query_text': 'SELECT * FROM inventory WHERE stock_level < 10', 'execution_time_ms': 8000, 'bytes_scanned': 300000000}]},
{'peak_hour_long_running': [{'query_id': 'Q011', 'query_text': 'SELECT region, SUM(sales) FROM sales_data GROUP BY region', 'execution_time_ms': 380000, 'start_time': '2024-02-10 15:45:00'}]}
]
}
return pd.DataFrame(data)
def render_header():
"""Render the enhanced header section"""
st.markdown("""
<div class="header-container fadeInUp">
<div class="header-title">❄️ Snowflake Query Optimizer</div>
<div class="header-subtitle">Advanced Query Performance Analysis & Optimization Dashboard</div>
</div>
""", unsafe_allow_html=True)
def render_overview_metrics(df):
"""Render overview metrics with enhanced styling"""
st.markdown('<div class="metrics-container fadeInUp">', unsafe_allow_html=True)
col1, col2, col3, col4 = st.columns(4)
metrics = [
(col1, len(df), "Total Users", "👥"),
(col2, df['TOTAL_QUERIES'].sum(), "Total Queries", "🔍"),
(col3, f"${df['TOTAL_CREDITS'].sum():.2f}", "Total Credits", "💰"),
(col4, len(df[df['COST_STATUS'] == 'High Cost']), "High Cost Users", "⚠️")
]
for col, value, label, icon in metrics:
with col:
st.markdown(f"""
<div class="metric-card">
<div class="metric-value">{icon} {value:}</div>
<div class="metric-label">{label}</div>
</div>
""", unsafe_allow_html=True)
st.markdown('</div>', unsafe_allow_html=True)
def handle_cell_click(user_name, metric_name, count):
"""Handle cell click by updating session state"""
st.session_state.page = 'details'
st.session_state.selected_user = user_name
st.session_state.selected_metric = metric_name
st.session_state.selected_count = count
def render_clickable_table(df):
"""Render the main clickable table with enhanced UI"""
st.markdown('<div class="analysis-table fadeInUp">', unsafe_allow_html=True)
st.markdown('<div class="table-header">🎯 Query Performance Analysis Dashboard</div>', unsafe_allow_html=True)
# Define the 8 metrics to display
metrics = [
('SPILLED_QUERIES', 'Spilled', '💾'),
('OVER_PROVISIONED_QUERIES', 'Over Provisioned', '📈'),
('PEAK_HOUR_LONG_RUNNING_QUERIES', 'Peak Long Running', '⏰'),
('SELECT_STAR_QUERIES', 'Select *', '⭐'),
('UNPARTITIONED_SCAN_QUERIES', 'Unpartitioned Scan', '🔍'),
('ZERO_RESULT_QUERIES', 'Zero Results', '🚫'),
('SLOW_QUERIES', 'Slow Queries', '🐌'),
('CARTESIAN_JOIN_QUERIES', 'Cartesian Joins', '🔗')
]
# Create table header
cols = st.columns([2.5] + [1.3] * len(metrics) + [1.8])
cols[0].markdown('<div class="column-header">👤 User</div>', unsafe_allow_html=True)
for i, (_, label, icon) in enumerate(metrics):
cols[i+1].markdown(f'<div class="column-header">{icon} {label}</div>', unsafe_allow_html=True)
cols[-1].markdown('<div class="column-header">💸 Cost Status</div>', unsafe_allow_html=True)
# Create table rows
for idx, row in df.iterrows():
cols = st.columns([2.5] + [1.3] * len(metrics) + [1.8])
# User column with enhanced styling
cols[0].markdown(f'<div class="user-cell fadeInLeft">👤 {row["USER_NAME"]}</div>', unsafe_allow_html=True)
# Metric columns with buttons
for i, (col_name, _, icon) in enumerate(metrics):
value = int(row[col_name])
button_key = f"{row['USER_NAME']}_{col_name}_{idx}"
if value == 0:
# Display zero values as disabled buttons
cols[i+1].markdown(f'<div class="zero-button" style="text-align: center; padding: 12px; border-radius: 12px; margin: 4px; font-weight: 600;">{icon} {value}</div>', unsafe_allow_html=True)
else:
# Create clickable button for non-zero values
if cols[i+1].button(f"{icon} {value}", key=button_key, help=f"View {value} {col_name.replace('_', ' ').lower()}", use_container_width=True):
handle_cell_click(row['USER_NAME'], col_name, value)
st.rerun()
# Cost status column with enhanced styling
status_class = "status-high" if row['COST_STATUS'] == 'High Cost' else "status-normal"
status_icon = "🔥" if row['COST_STATUS'] == 'High Cost' else "✅"
cols[-1].markdown(f'<div class="{status_class} fadeInRight">{status_icon} {row["COST_STATUS"]}</div>', unsafe_allow_html=True)
st.markdown('</div>', unsafe_allow_html=True)
def render_query_details(df, user_name, metric_name, count):
"""Render detailed query information with enhanced UI"""
# Enhanced back button
col1, col2, col3 = st.columns([1, 2, 1])
with col1:
if st.button("← Back to Overview", key="back_button", help="Return to main dashboard", use_container_width=True):
st.session_state.page = 'overview'
st.rerun()
# Get user data
user_data = df[df['USER_NAME'] == user_name].iloc[0]
# Enhanced header with animation
metric_display_name = metric_name.replace('_', ' ').title()
metric_icon = {
'SPILLED_QUERIES': '💾',
'OVER_PROVISIONED_QUERIES': '📈',
'PEAK_HOUR_LONG_RUNNING_QUERIES': '⏰',
'SELECT_STAR_QUERIES': '⭐',
'UNPARTITIONED_SCAN_QUERIES': '🔍',
'ZERO_RESULT_QUERIES': '🚫',
'SLOW_QUERIES': '🐌',
'CARTESIAN_JOIN_QUERIES': '🔗'
}.get(metric_name, '📊')
st.markdown(f"""
<div class="details-container fadeInUp">
<div class="details-header">
<h1>{metric_icon} {user_name}</h1>
<h2>{metric_display_name}</h2>
<p style="font-size: 1.2rem; margin-top: 1rem; opacity: 0.9;">
Found <strong>{count:,}</strong> queries of this type
</p>
</div>
</div>
""", unsafe_allow_html=True)
# Enhanced user overview metrics
st.markdown("### 📈 User Performance Overview")
col1, col2, col3, col4 = st.columns(4)
metrics_data = [
(col1, "Total Queries", f"{user_data['TOTAL_QUERIES']:,}", "🔍"),
(col2, "Total Credits", f"${user_data['TOTAL_CREDITS']:.2f}", "💰"),
(col3, "Cost Status", user_data['COST_STATUS'], "💸"),
(col4, "Current Issue", f"{count:,} {metric_display_name}", metric_icon)
]
for col, label, value, icon in metrics_data:
with col:
st.markdown(f"""
<div class="metric-card fadeInUp">
<div class="metric-value">{icon} {value}</div>
<div class="metric-label">{label}</div>
</div>
""", unsafe_allow_html=True)
# Enhanced recommendations section
if user_data['RECOMMENDATIONS']:
st.markdown("### 🎯 Optimization Recommendations")
st.markdown('<div class="fadeInLeft">', unsafe_allow_html=True)
for i, rec in enumerate(user_data['RECOMMENDATIONS']):
st.markdown(f'<div class="recommendation-item" style="animation-delay: {i*0.1}s;">{rec}</div>', unsafe_allow_html=True)
st.markdown('</div>', unsafe_allow_html=True)
# Enhanced sample queries section
st.markdown("### 📋 Sample Query Analysis")
# Get query samples for this metric
query_samples = user_data['QUERY_SAMPLES']
if isinstance(query_samples, dict):
# Map metric names to sample keys
metric_key_mapping = {
'SPILLED_QUERIES': 'spilled',
'OVER_PROVISIONED_QUERIES': 'over_provisioned',
'PEAK_HOUR_LONG_RUNNING_QUERIES': 'peak_hour_long_running',
'SELECT_STAR_QUERIES': 'select_star',
'UNPARTITIONED_SCAN_QUERIES': 'unpartitioned_scan',
'ZERO_RESULT_QUERIES': 'zero_result',
'SLOW_QUERIES': 'slow_query',
'CARTESIAN_JOIN_QUERIES': 'cartesian_join'
}
metric_key = metric_key_mapping.get(metric_name)
if metric_key and metric_key in query_samples and query_samples[metric_key]:
samples = query_samples[metric_key]
for i, query in enumerate(samples[:3]): # Show up to 3 samples
with st.expander(f"🔍 Query Sample {i+1}: {query['query_id']}", expanded=(i==0)):
col1, col2 = st.columns([2.5, 1])
with col1:
st.markdown("**📝 Query Text:**")
st.markdown(f'<div class="query-box fadeInUp">{query["query_text"]}</div>', unsafe_allow_html=True)
with col2:
st.markdown("**📊 Performance Metrics:**")
# Create metric cards for query details
if 'execution_time_ms' in query:
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">⏱️ {query['execution_time_ms']:,}ms</div>
<div class="metric-label">Execution Time</div>
</div>
""", unsafe_allow_html=True)
if 'bytes_scanned' in query:
bytes_gb = query['bytes_scanned'] / (1024**3)
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">📊 {bytes_gb:.2f}GB</div>
<div class="metric-label">Data Scanned</div>
</div>
""", unsafe_allow_html=True)
if 'warehouse_size' in query:
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">🏭 {query['warehouse_size']}</div>
<div class="metric-label">Warehouse Size</div>
</div>
""", unsafe_allow_html=True)
if 'rows_produced' in query:
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">📈 {query['rows_produced']:,}</div>
<div class="metric-label">Rows Produced</div>
</div>
""", unsafe_allow_html=True)
if 'bytes_spilled_to_local_storage' in query:
spilled_mb = query['bytes_spilled_to_local_storage'] / (1024**2)
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">💾 {spilled_mb:.1f}MB</div>
<div class="metric-label">Spilled to Storage</div>
</div>
""", unsafe_allow_html=True)
if 'partitions_scanned' in query and 'partitions_total' in query:
efficiency = (1 - query['partitions_scanned'] / query['partitions_total']) * 100
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">🎯 {efficiency:.1f}%</div>
<div class="metric-label">Partition Efficiency</div>
</div>
""", unsafe_allow_html=True)
if 'start_time' in query:
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">🕐 {query['start_time']}</div>
<div class="metric-label">Start Time</div>
</div>
""", unsafe_allow_html=True)
else:
st.markdown("""
<div style="text-align: center; padding: 3rem; background: rgba(255, 255, 255, 0.8); border-radius: 15px; margin: 2rem 0;">
<h3>🔍 No Sample Queries Available</h3>
<p style="color: #7f8c8d; font-size: 1.1rem;">
Sample queries for this metric are not available in the current dataset.
</p>
</div>
""", unsafe_allow_html=True)
else:
st.markdown("""
<div style="text-align: center; padding: 3rem; background: rgba(255, 255, 255, 0.8); border-radius: 15px; margin: 2rem 0;">
<h3>🔍 Query Data Unavailable</h3>
<p style="color: #7f8c8d; font-size: 1.1rem;">
Query sample data structure needs to be updated for this user.
</p>
</div>
""", unsafe_allow_html=True)
# Main application logic
def main():
# Load data
df = create_sample_data()
# Render header
render_header()
# Route to appropriate page
if st.session_state.page == 'overview':
render_overview_metrics(df)
render_clickable_table(df)
# Add footer with additional info
st.markdown("""
<div style="text-align: center; margin-top: 3rem; padding: 2rem; background: rgba(255, 255, 255, 0.8); border-radius: 15px;">
<h4 style="color: #2c3e50; margin-bottom: 1rem;">💡 How to Use This Dashboard</h4>
<p style="color: #7f8c8d; font-size: 1rem; line-height: 1.6;">
Click on any non-zero metric value in the table to view detailed query samples and optimization recommendations for that specific issue type.
The dashboard helps identify performance bottlenecks and provides actionable insights to optimize your Snowflake queries.
</p>
</div>
""", unsafe_allow_html=True)
elif st.session_state.page == 'details':
render_query_details(
df,
st.session_state.selected_user,
st.session_state.selected_metric,
st.session_state.selected_count
)
if __name__ == "__main__":
main()
Armaan Khan
Posted on
Dashboard2
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)