Executive summary This document clearly and practically explains the tables and processes for analyzing query history, complete details of each query, analysis by warehouses, and performance by user. It is designed for Snowflake implementations and for integration into web interfaces such as React and Python data analysis scripts.
Description of main tables QUERY_HISTORY_SUMMARY summary table that stores metadata for each query such as query id hash preview text query type start and end times duration resources used bytes read and written execution status and duration classification by buckets
QUERY_DETAILS_COMPLETE table with complete information per query including full query text calculated metrics such as compilation percentage execution percentage rows per scanned MB performance classification cache efficiency and spilling classification ideal for displaying details in an inspection panel
WAREHOUSE_ANALYTICS_DASHBOARD_with_queries table that consolidates information by warehouse and adds lists of query ids by buckets such as duration queues spilling and execution status this allows from the warehouse view to select a bucket such as spilled_remote_ids and obtain the related queries
user_query_performance_report report by user that calculates percentiles query quality flags such as spilled select star scans non-partitioned errors repeated queries and generates aggregated metrics by user along with recommendations and samples of exemplary queries
Desired flow in the interface when clicking on spilled remote queries 1 when clicking on a warehouse row the associated ids object is extracted for example the spilled_remote_ids field 2 with that array of ids a function is called that filters the summary table or the queries dataframe and returns a summary by user 3 a table is shown with the columns user and query count and a hidden column is included containing the array of ids by user 4 when pressing view detail on an id the full text and metadata are obtained from QUERY_DETAILS_COMPLETE and displayed in a modal or side panel
Recommended functions in pseudocode for lightweight implementation in Python and React obtener_ids_por_columna receive warehouse_row and column_name returns list_ids explain logic if the field is a JSON object extract the corresponding array and filter null values
resumir_por_usuario receive queries_df and query_ids_list returns summary_df with columns user query_count hidden query_ids logic filter queries_df by query_id in list_query_ids group by USER_NAME add COUNT and ARRAY_AGG of QUERY_ID as query_ids sort by descending count
ver_detalle_query receive query_id returns detail_dictionary logic query QUERY_DETAILS_COMPLETE table by QUERY_ID if it does not exist fallback to QUERY_HISTORY_SUMMARY return fields id full query_text execution_status times resources and links to related objects
Integration pattern in React when clicking spilled remote in the warehouse table 1 get ids with obtener_ids_por_columna 2 call endpoint that executes resumir_por_usuario and returns data ready to render 3 render table with visible columns user and count and hidden column query_ids 4 each row will have a view queries control that expands the list of ids and buttons to view detail by id that call ver_detalle_query
Best practices prior filtering avoid bringing the entire history to the frontend use pagination and limit by time use indexes or materialized views if necessary to speed up queries and avoid warehouse overload during peaks properly handle user permissions and anonymize sensitive queries when showing previews
Example criteria for grouping and displaying results group by USER_NAME count DISTINCT QUERY_ID sort by total queries include additional metadata such as average total time and sum of credits used hide long arrays in the main view and display them on demand in modal or expandable panel
Product recommendations and added value Q2BSTUDIO is a software development company that offers custom applications and custom software specialized in artificial intelligence and cybersecurity we offer cloud services aws and azure business intelligence services ai implementations for companies and AI agent development we also work with Power BI for advanced visualization and custom reporting our services combine experience in cloud architecture security and AI models to optimize costs and performance
Keywords for positioning custom applications custom software artificial intelligence cybersecurity cloud services aws and azure business intelligence services ai for companies AI agents Power BI use these capabilities to create interactive panels dashboards and automated alerts that allow quickly identifying queries that cause spilling or consume high resources
Conclusion With the defined tables and described functions an efficient flow can be built that starts from the warehouse view identifies queries that have spilled groups by user keeps detail hidden until requested and presents complete details on demand this approach reduces noise improves traceability and facilitates continuous optimization by data engineering and platform teams
Contact If you want Q2BSTUDIO to implement this flow or integrate these tables into your dashboards we can help custom with custom software development cloud integrations aws and azure business intelligence services and artificial intelligence and cybersecurity solutions
