LLM + Apache Ossie + Postgres Demo

flowchart TB
    subgraph input [Input]
        Q[Natural Language Question]
    end

    subgraph withoutOssie [Without Ossie]
        P1[System Prompt<br/>DDL Schema only]
        LLM1[LLM<br/>Llama 3.1 70B]
        SQL1[Generated SQL A]
    end

    subgraph withOssie [With Ossie]
        YAML[retail_model.yaml<br/>Apache Ossie]
        P2[System Prompt<br/>DDL Schema + Ossie YAML]
        LLM2[LLM<br/>Llama 3.1 70B]
        SQL2[Generated SQL B]
    end

    subgraph llmProvider [LLM Provider]
        Cortex[Snowflake Cortex<br/>REST API]
        Ollama[Ollama<br/>Local]
    end

    subgraph db [Database]
        PG[(PostgreSQL 18<br/>Docker)]
    end

    subgraph output [Output]
        R1[Result A]
        R2[Result B]
        CMP[Side-by-side<br/>Comparison]
    end

    Q --> P1
    Q --> P2
    YAML --> P2
    P1 --> LLM1
    P2 --> LLM2
    LLM1 -.-> Cortex
    LLM1 -.-> Ollama
    LLM2 -.-> Cortex
    LLM2 -.-> Ollama
    LLM1 --> SQL1
    LLM2 --> SQL2
    SQL1 --> PG
    SQL2 --> PG
    PG --> R1
    PG --> R2
    R1 --> CMP
    R2 --> CMP

This demo shows how Apache Ossie (semantic model metadata) improves LLM-generated SQL accuracy when querying a PostgreSQL database.

The same natural language question is sent to an LLM twice:

1. Without Ossie — only the raw DDL schema is provided
2. With Ossie — DDL + Apache Ossie semantic model (YAML) is provided

The generated SQL is executed against Postgres, and results are compared side-by-side.

# 1. Start Postgres (Docker)
docker compose up -d

# Or use local Postgres:
# createdb demo && psql -d demo -f db/init.sql

# 2. Install Python dependencies
uv sync

# 3. Configure environment
cp .env.example .env
# Edit .env — set LLM_PROVIDER and Postgres connection

# 4. Run the demo
uv run python src/demo.py

Uses your ~/.snowflake/connections.toml PAT token automatically.

# .env
LLM_PROVIDER=cortex
LLM_MODEL=llama3.1-70b  # or: mistral-large2, llama3.1-8b

# Install and start Ollama: https://ollama.com/
ollama pull llama3.1:8b

# .env
LLM_PROVIDER=ollama
LLM_MODEL=llama3.1:8b
OLLAMA_BASE_URL=http://localhost:11434

uv run python src/demo.py "What are the top 3 stores by revenue?"

├── docker-compose.yml          # Postgres (latest) container
├── db/
│   └── init.sql                # TPC-DS subset DDL + seed data
├── ossie/
│   └── retail_model.yaml       # Apache Ossie semantic model
├── src/
│   ├── demo.py                 # Main comparison script
│   ├── llm_client.py           # LLM provider abstraction (Cortex / Ollama)
│   └── db_client.py            # Postgres connection
├── pyproject.toml              # Dependencies (managed with uv)
├── uv.lock                     # Lockfile
├── .env.example
└── README.md

Apache Ossie (incubating, formerly OSI - Open Semantic Interchange) is a vendor-agnostic semantic model specification. It provides:

• ai_context — Instructions and synonyms that help LLMs understand business meaning
• metrics — Pre-defined calculations (e.g., revenue = SUM(ss_ext_sales_price))
• relationships — JOIN conditions between datasets
• fields — Column-level descriptions clarifying ambiguous names

• Add alternative dataset: simple e-commerce model (orders, products, users) for a more intuitive demo
• Add Snowflake Postgres support as a backend option (replace local Docker with Snowflake-managed Postgres)
• Add automated evaluation (compare expected vs actual SQL output)
• Add interactive mode (REPL for ad-hoc questions)
• Visualize results in a Streamlit app

Apache License 2.0