Building an AI-Powered Snowflake Query Optimization Assistant with Streamlit & Cortex AI

Introduction

Build an AI-powered Streamlit application inside Snowflake that automatically detects expensive SQL queries, recommends optimizations using Cortex AI, and helps reduce warehouse costs.

Every Snowflake engineer has encountered slow and expensive SQL queries. A simple SELECT *, missing filters, inefficient joins, or poorly designed predicates can significantly increase warehouse costs and query execution time.

Although Snowflake provides excellent monitoring capabilities through the ACCOUNT_USAGE schema, identifying inefficient queries still requires manual investigation by data engineers or DBAs.

With the introduction of Snowflake Cortex AI, we now have the opportunity to automate part of this process.

In this article, I’ll demonstrate how to build an AI-Powered Query Optimization Assistant using Streamlit in Snowflake and Snowflake Cortex AI. The application analyzes query history, identifies expensive SQL statements, recommends optimizations, and helps engineering teams reduce Snowflake costs — all without leaving the Snowflake platform.

Why Build This?

Modern data platforms execute thousands of SQL queries every day. As usage grows, so does the challenge of identifying inefficient queries before they impact performance and increase cloud costs.

Some common issues include:

• Full table scans
• SELECT * statements
• Missing filter predicates
• Poor partition pruning
• Memory spilling
• Long-running warehouse usage
• High Snowflake credit consumption

Rather than manually reviewing query history, we can use AI to assist developers by analyzing SQL patterns and recommending improvements.

Solution Overview

The solution is a Streamlit application running natively inside Snowflake.

The application:

✅ Reads Snowflake Query History

✅ Calculates query cost using Query Attribution

✅ Detects expensive SQL statements

✅ Uses Snowflake Cortex AI to generate optimized SQL

✅ Compares Before vs After performance

✅ Provides tuning recommendations

Everything runs securely inside Snowflake without external APIs or additional infrastructure.

High-Level Architecture

Snowflake

          ACCOUNT_USAGE Views
                   │
        ┌──────────┼──────────┐
        │          │          │
 Query History   Query Cost   User Roles
        │          │          │
        └──────────┼──────────┘
                   │
         Streamlit in Snowflake
                   │
                   ▼
      Snowflake Cortex COMPLETE()
                   │
                   ▼
     AI Query Optimization Engine
                   │
                   ▼
          Developer Dashboard

Data Sources Used

The application uses only Snowflake system views.

ViewPurposeQUERY_HISTORYQuery text, runtime, execution statusQUERY_ATTRIBUTION_HISTORYCredit consumptionGRANTS_TO_USERSRole-based filtering

Since these are Snowflake Account Usage views, no production tables are modified.

Dashboard Features

The application contains several modules designed for both developers and administrators.

1. Recommendations

The application identifies expensive SQL queries and sends them to Snowflake Cortex AI.

Example:

Original Query

SELECT *
FROM SALES
WHERE YEAR(ORDER_DATE)=2025;

AI Recommendation

SELECT
SALE_ID,
CUSTOMER_ID,
ORDER_DATE
FROM SALES
WHERE ORDER_DATE >= '2025-01-01'
AND ORDER_DATE < '2026-01-01';

The optimized query improves partition pruning while reducing unnecessary column scanning.

2. Before vs After Comparison

Compare:

• Runtime
• Credits Consumed
• Bytes Scanned
• Spill Information

This allows developers to quantify optimization improvements.

3. Performance Advisor

The application automatically detects common Snowflake performance issues such as:

• Local Spill
• Remote Spill
• Full Table Scan
• Large Scan
• Missing Filters
• High Compilation Time

Each issue includes recommended optimization techniques.

4. Cost Analysis

Rank queries by:

• Credits Consumed
• Runtime
• Warehouse Usage

This helps engineering teams focus on the queries with the highest optimization potential.

5. Role-Based Access

Administrators can analyze all teams.

Developers only see queries executed by users within their assigned roles.

Why Snowflake Cortex AI?

Instead of simply displaying expensive queries, Cortex AI acts as an intelligent assistant by explaining why a query is inefficient and suggesting an optimized version.

This significantly reduces the effort required to review SQL manually and accelerates performance tuning across engineering teams.

Benefits

Organizations can use this solution to:

• Reduce Snowflake costs
• Improve query performance
• Increase developer productivity
• Standardize SQL optimization
• Build an AI-assisted engineering workflow

Conclusion

Combining Snowflake Streamlit, Account Usage views, and Snowflake Cortex AI enables organizations to build intelligent tools that continuously improve SQL performance while keeping all processing securely within the Snowflake ecosystem.

As AI capabilities continue to evolve, assistants like this can become valuable companions for data engineers, helping teams identify optimization opportunities faster and reduce cloud costs more effectively.

💡 My recommendation

Don’t stop at this article. If you’re building the application yourself, publish it as a series:

1. Part 1 — Building the AI Query Optimization Assistant (this article)
2. Part 2 — Building the Streamlit Dashboard
3. Part 3 — Integrating Snowflake Cortex AI
4. Part 4 — AI-Powered SQL Performance Recommendations
5. Part 5 — Deploying the Solution in Snowflake

By the end of the series, readers will be able to build the complete solution themselves, and you’ll have a substantial technical portfolio that demonstrates Snowflake architecture, performance engineering, Streamlit, and Cortex AI together.