Cortex AI Self-Service Analytics

A natural language analytics interface for freight data, powered by Snowflake Cortex AI. Ask questions in plain English and get instant data insights — no SQL required.

Objective

Enable non-technical stakeholders to query freight data conversationally, with Snowflake Cortex AI translating natural language to SQL on the fly.

Results

• Zero SQL knowledge required — plain English queries return structured data
• Sub-second responses in local demo mode; ~2s in Snowflake Cortex mode
• 10,000+ shipment records across 5 carriers, 5 origins, 5 destinations
• Supports delay analysis, cost breakdowns, carrier comparisons, route insights, monthly trends

Architecture

User Question (plain English)
        ↓
  [LOCAL MODE]                        [SNOWFLAKE MODE]
  Keyword classifier                  Snowflake Cortex AI
  → pandas aggregation                COMPLETE('mistral-large', prompt)
        ↓                                     ↓
  Structured result DataFrame         Generated SQL → execute → DataFrame
        ↓
  Streamlit chart + summary

How It Works

Local Demo Mode (default, no Snowflake needed):

• A keyword classifier maps questions to pre-built pandas aggregations
• Instant results with auto-generated bar/line charts

Snowflake Mode (SNOWFLAKE_MODE=true):

• The question is sent to SNOWFLAKE.CORTEX.COMPLETE('mistral-large', ...) with a schema-aware prompt
• Cortex returns a SQL query which is executed against your FREIGHT table
• Results rendered in Streamlit

Setup

Prerequisites

• Python 3.9+
• For Snowflake mode: Snowflake account with Cortex AI enabled

Environment Variables (Snowflake mode only)

export SNOWFLAKE_USER=your_user
export SNOWFLAKE_PASSWORD=your_password
export SNOWFLAKE_ACCOUNT=your_account
export SNOWFLAKE_MODE=true

Run

pip install -r requirements.txt

# Generate sample data
python src/generate_data.py

# Launch the app
streamlit run src/app.py

For Snowflake mode, first run sql/setup.sql in a Snowflake worksheet to create and load the FREIGHT table.

Sample Questions

• Which carrier has the most delays?
• What is the total cost by carrier?
• Show me the top routes by shipment volume
• What is the monthly shipment trend?
• Show carrier performance comparison

Project Structure

cortex-analytics/
├── data/
│   └── freight.csv              # Generated by generate_data.py
├── sql/
│   └── setup.sql                # Snowflake table + stage setup
├── src/
│   ├── generate_data.py         # Generates 10K mock freight records
│   └── app.py                   # Streamlit app (dual local/Snowflake mode)
└── requirements.txt