# shyamal002/snowflake_cortex_analyst_bot architecture

> Architecture diagram of the GitHub repository shyamal002/snowflake_cortex_analyst_bot, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-07-02). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/shyamal002/snowflake_cortex_analyst_bot
- Repository: https://github.com/shyamal002/snowflake_cortex_analyst_bot

## Overview

##### Project type and purpose
- This is a full-stack, single-page analytical chatbot built on Streamlit.
- Purpose: take plain-English user questions, convert them to SQL via Snowflake Cortex Analyst, execute on Snowflake, apply semantic metadata, enforce guardrails, and render tabular/chart results.

##### File structure
- Top level:
- `pyproject.toml`, `requirements.txt` – dependency definitions
- `src/` – all application code and assets
- `src/` subfolders:
- `config/` – SQL DDL scripts and semantic-model YAML
- `guardrails/` – NeMo/LlamaGuard configuration and prompts
- `snowflake_cortex_bot.py` – the Streamlit app entrypoint
- No separate “frontend” vs. “backend” folder; both UI and logic live in a single Python process.

##### Key architecture insights from README
- Technologies: Streamlit (UI), Pandas/Matplotlib or Streamlit charts (visualization), Snowflake Python connector, Cortex Analyst SDK (LLM→SQL), NVIDIA NeMo & LlamaGuard (moderation), SpaCy (NLP helpers), session state for context, custom logging/feedback.
- External systems: Snowflake DB (tables + semantic model YAML stage), Cortex Analyst service, optional Ollama LLM host.

a. Main components
1. User Browser / Streamlit UI
2. Application Logic
– Natural-Language Router (Streamlit input → prompt builder)
– Session Memory (Streamlit session_state)
– Guardrails Module (NeMo/LlamaGuard)
– SQL Translator (Snowflake Cortex Analyst API)
– Snowflake Connector (executes SQL + loads semantic model)
– Visualization Engine (Pandas/DataFrame → charts)
– Feedback & Logging (writes to local logs or Cortex Feedback API)
3. Semantic Model Store (Snowflake stage + demo_semantic_model.yaml)
4. Snowflake Database (tables created via SQL scripts)
5. External LLM Services (Cortex Analyst, optional Ollama for guardrails)

b. Relationships & data flow
1. User enters NL query in Streamlit UI
2. App logic consults Session Memory → builds prompt
3. Prompt passes through Guardrails → moderated prompt
4. Moderated prompt sent to Cortex Analyst → returns SQL
5. SQL submitted to Snowflake via Connector
6. Snowflake returns raw data
7. App logic applies Pandas/semantic model metadata → prepares visualization
8. Results rendered back in UI
9. User feedback captured → sent to Feedback API or log file

c. Architectural patterns & principles
- Modular separation: config, guardrails, core logic in distinct folders
- Single-process microservice: UI + business logic co-located
- Externalized semantic metadata (YAML) for decoupled schema awareness
- Stateful session for multiturn conversation
- Plug-in guardrails layer to enforce safety

d. Relevant technologies/frameworks
- Streamlit – UI and session state
- Snowflake Python connector / SnowSQL CLI
- Snowflake Cortex Analyst SDK – NL→SQL
- NVIDIA NeMo & LlamaGuard – content filters
- Pandas & Streamlit charts – data rendering
- SpaCy – optional NLP pre/post-processing
- uv (alternative installer), Python venv

##### Final advice
- Be exhaustive yet readable: break the system into logical layers (presentation, processing, data).
- Essential elements listed above.
- Don’t over-modularize; focus on 6–8 core components for clarity.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Presentation Layer
    subgraph "Presentation Layer"
        direction TB
        User["User Browser"]:::external
        StreamlitUI["Streamlit UI"]:::internal
        UIAssets["UI Assets & ER Diagram"]:::data
    end

    %% Processing Layer
    subgraph "Processing Layer"
        direction TB
        NLRouter["Natural-Language Router & Prompt Builder"]:::internal
        SessionMemory["Session Memory (Streamlit session_state)"]:::internal
        GuardrailsModule["Guardrails Module (NeMo/LlamaGuard)"]:::internal
        SQLTranslator["SQL Translator (Cortex Analyst SDK)"]:::internal
        SnowflakeConnector["Snowflake Connector"]:::internal
        VisualizationEngine["Visualization Engine (Pandas/Streamlit charts)"]:::internal
        FeedbackLogging["Feedback & Logging"]:::internal
    end

    %% Data Layer
    subgraph "Data Layer"
        direction TB
        SemanticModel["Semantic Model Store"]:::data
        SnowflakeDB["Snowflake Database"]:::data
        DBScripts["Snowflake DB Setup Scripts"]:::data
    end

    %% External Services
    subgraph "External Services"
        direction TB
        CortexAnalyst["Cortex Analyst API"]:::external
        Ollama["Optional Ollama LLM"]:::external
        FeedbackAPI["Cortex Feedback API"]:::external
    end

    %% Data Flow
    User -->|"enters NL query"| StreamlitUI
    StreamlitUI -->|"retrieve session"| SessionMemory
    StreamlitUI -->|"submit query"| NLRouter
    NLRouter -->|"build prompt"| GuardrailsModule
    GuardrailsModule -->|"moderated prompt"| SQLTranslator
    SQLTranslator -->|"REST API call"| CortexAnalyst
    CortexAnalyst -->|"returns SQL"| SQLTranslator
    SQLTranslator -->|"execute SQL"| SnowflakeConnector
    SnowflakeConnector -->|"SQL + semantic metadata"| SemanticModel
    SnowflakeConnector -->|"runs DDL/data load"| DBScripts
    SnowflakeConnector -->|"query results"| SnowflakeDB
    SnowflakeDB -->|"raw data"| SnowflakeConnector
    SnowflakeConnector -->|"DataFrame"| VisualizationEngine
    VisualizationEngine -->|"render charts/tables"| StreamlitUI
    StreamlitUI -->|"capture feedback"| FeedbackLogging
    FeedbackLogging -->|"send feedback"| FeedbackAPI
    GuardrailsModule -->|"optional moderation"| Ollama

    %% Click Events
    click StreamlitUI "https://github.com/shyamal002/snowflake_cortex_analyst_bot/blob/main/src/snowflake_cortex_bot.py"
    click NLRouter "https://github.com/shyamal002/snowflake_cortex_analyst_bot/blob/main/src/snowflake_cortex_bot.py"
    click SessionMemory "https://github.com/shyamal002/snowflake_cortex_analyst_bot/blob/main/src/snowflake_cortex_bot.py"
    click SQLTranslator "https://github.com/shyamal002/snowflake_cortex_analyst_bot/blob/main/src/snowflake_cortex_bot.py"
    click SnowflakeConnector "https://github.com/shyamal002/snowflake_cortex_analyst_bot/blob/main/src/snowflake_cortex_bot.py"
    click VisualizationEngine "https://github.com/shyamal002/snowflake_cortex_analyst_bot/blob/main/src/snowflake_cortex_bot.py"
    click FeedbackLogging "https://github.com/shyamal002/snowflake_cortex_analyst_bot/blob/main/src/snowflake_cortex_bot.py"
    click GuardrailsModule "https://github.com/shyamal002/snowflake_cortex_analyst_bot/tree/main/src/guardrails/"
    click SemanticModel "https://github.com/shyamal002/snowflake_cortex_analyst_bot/blob/main/src/config/demo_semantic_model.yaml"
    click DBScripts "https://github.com/shyamal002/snowflake_cortex_analyst_bot/blob/main/src/config/00_Demo_Create_Tables.sql"
    click UIAssets "https://github.com/shyamal002/snowflake_cortex_analyst_bot/tree/main/src/images/"
    click FeedbackAPI "https://github.com/shyamal002/snowflake_cortex_analyst_bot/blob/main/src/.env_template"

    %% Styles
    classDef internal fill:#ADD8E6,stroke:#333,stroke-width:1px
    classDef data fill:#90EE90,stroke:#333,stroke-width:1px
    classDef external fill:#FFD580,stroke:#333,stroke-width:1px
```
