🌥️ Cirro

Chat with your Snowflake data in plain English. Cirro is a Streamlit app that puts three Snowflake Cortex capabilities behind one chat box — natural-language analytics, grounded retrieval, and sentiment insights — running either locally or natively inside Snowflake.

---

What it does

• Ask your data (Cortex Analyst) — natural-language questions become SQL against a governed semantic model, run read-only, and return a table. Multi-turn.
• Search your tickets (Cortex Search + COMPLETE) — retrieves the relevant records, then synthesizes a grounded, source-cited answer (RAG).
• Insights — a dashboard that runs Cortex SENTIMENT as one set-based GROUP BY (no per-row API loop).

Analyst and Insights results auto-render as a chart and export to CSV. Starter prompts are one-click. The same codebase runs locally (streamlit run app.py) or deploys as a Streamlit-in-Snowflake app — the session factory auto-detects which.

Architecture

flowchart TD
    U([User]) --> UI["Streamlit UI · app.py"]

    UI -->|Analyst| A["analyst.py"]
    UI -->|Search RAG| S["search.py + cortex.py"]
    UI -->|Insights| I["insights.py"]

    A -->|NL → SQL| CA["Cortex Analyst<br/>(semantic model)"]
    A -->|read-only SELECT| WH[("Snowflake<br/>warehouse")]
    S -->|retrieve top-k| CSVC["Cortex Search"]
    S -->|synthesize| CC["Cortex COMPLETE"]
    I -->|set-based SENTIMENT| WH
    CSVC --> WH

    UI -.session.-> CONN["connection.py<br/>local build · or · reuse in-Snowflake session"]
    CONN -.-> WH

Loading

How Search (RAG) works

sequenceDiagram
    actor U as User
    participant App as Streamlit
    participant CS as Cortex Search
    participant LLM as Cortex COMPLETE
    U->>App: question
    App->>CS: retrieve top-5 tickets
    CS-->>App: matching records
    App->>LLM: question + retrieved context
    LLM-->>App: grounded, source-cited answer
    App-->>U: answer + expandable sources

Loading

Demo

⚠️ Demo mode. The media below is the real app running with sample responses (CIRRO_DEMO=1) — not live Cortex output. The target account is trial-gated for Cortex; once it's on a paid tier these become live captures. See Status.

15-second walkthrough: Analyst → Search (RAG) → Insights. Also available as MP4.

Ask your data (Analyst)	Search — RAG	Insights
NL → read-only SQL → table + chart	retrieve, then cite sources	set-based sentiment by segment

Run it yourself with no Snowflake account: CIRRO_DEMO=1 streamlit run app.py.

Project layout

cirro/
  deps.py            dependency gate — install-or-abort before anything else
  logging_config.py  rotating combined log + errors-only log in logs/
  config.py          settings from config.json; secrets from keyring/env, never git
  connection.py      Snowpark session: reuse active (SiS) or build from config
  cortex.py          Cortex COMPLETE wrapper (RAG synthesis)
  analyst.py         Cortex Analyst REST (in-Snowflake bridge + local token), multi-turn, read-only SQL guard
  search.py          Cortex Search retrieval
  insights.py        set-based sentiment-by-segment enrichment
  cli.py             headless entry point (verify / ask / chat)
app.py               Streamlit chat UI (3 modes, auto-chart, CSV export)
sql/                 01 setup, 02 sample data, 03 cortex search service
semantic_model/      Cortex Analyst semantic model YAML
tests/               pytest suite (mocked sessions — no warehouse needed)

Status

Data plane verified live; Cortex pending a paid tier. Provisioning and the non-AI path are verified against a real Snowflake account — database/schema/stage creation, sample-data load, queries, and semantic-model upload all pass, and the code logic is covered by 18 passing unit tests. The Cortex AI functions (COMPLETE, SENTIMENT, Analyst, Search) are gated by Snowflake to non-trial accounts; on a trial account they return 399258: AI function ... not available for trial accounts. Point Cirro at a Standard/Enterprise (or otherwise Cortex-enabled) account to light up the AI features.

Setup

1. Install deps (or let deps.py do it on first run):

   pip install -r requirements.txt
   

2. Provision Snowflake — run the SQL in order, editing YOUR_SNOWFLAKE_USER:

   sql/01_setup.sql
   sql/02_sample_data.sql
   sql/03_cortex_search.sql
   

3. Upload the semantic model to the stage created in step 2:

   snow stage copy semantic_model/cirro_semantic_model.yaml @CIRRO.PUBLIC.SEMANTIC_MODELS --overwrite
   

4. Configure — copy the example and fill it in:

   cp config.example.json config.json
   

   config.json is gitignored and holds no secret. Store the password in the OS keyring:

   keyring set cirro YOUR_SNOWFLAKE_USER
   

   Or use authenticator: externalbrowser (SSO, no stored secret), set private_key_path for key-pair auth, or export CIRRO_PASSWORD.

Run

Local:

streamlit run app.py

Streamlit-in-Snowflake: create a Streamlit object in the CIRRO.PUBLIC schema, upload app.py and the cirro/ package to its stage, and set the app's main file to app.py. No config.json is needed there — connection.py reuses the active session.

CLI (headless)

python -m cirro.cli verify
python -m cirro.cli ask "how many urgent tickets by tier?"
python -m cirro.cli chat "summarize the billing complaints"

Tests

pip install -r requirements-dev.txt
python -m pytest -q

The suite mocks the Snowpark session, so it runs with no Snowflake connection (18 tests, all green).

Verify against a Cortex-enabled account

1. sql/*.sql all succeed with no errors.
2. python -m cirro.cli verify → prints ready (session + Cortex COMPLETE OK).
3. App Ask your data: "how many urgent tickets by tier?" returns SQL + a table → Analyst + semantic model OK.
4. App Search your tickets: "billing problems" returns a cited answer → Cortex Search + RAG OK.
5. App Insights → sentiment-by-segment chart renders → set-based SENTIMENT OK.

Notes

• Requires Python 3.10+ and a Cortex-enabled Snowflake account in a supported region.
• Costs: Cortex functions bill per token; Cortex Search and the warehouse bill on usage. CIRRO_WH is XSMALL with 60s auto-suspend to keep this cheap.
• Swap SUPPORT_TICKETS for your own tables by updating the semantic model and the search service — the app code doesn't change.