Snowflake Cortex Analyst turns plain-English questions into governed SQL. OpenAI turns the results into an executive summary. All on top of a semantic layer.
Operations Copilot is a natural-language business-intelligence application for an enterprise solar-operations scenario. A user asks a question in plain English — "Which city should operations prioritize this week?" — and the app:
- Sends the question to Snowflake Cortex Analyst, which interprets it against a semantic view and generates governed SQL.
- Executes that SQL on Snowflake and returns the result set.
- Passes the results to an LLM (OpenAI) that writes a manager-friendly executive summary — key finding, operational impact, recommended action.
- Renders the answer, the generated SQL, an interactive chart, and a downloadable table in a polished Streamlit UI.
The result is a tool that lets non-technical operations managers self-serve analytics they would otherwise have to request from a data team — without writing a line of SQL, and without the model hallucinating numbers, because every answer is grounded in the semantic layer.
ℹ️ Portfolio project / disclaimer. This is an independent demonstration project built around a fictional "Enpal One+" operations scenario using synthetic data. It is not affiliated with, endorsed by, or connected to Enpal GmbH, and contains no real or proprietary company data.
| Feature | Description | |
|---|---|---|
| 🗣️ | Natural-language → SQL | Cortex Analyst maps questions to a semantic view and generates governed SQL — no hand-written queries. |
| 🧠 | Grounded AI summaries | An LLM summarizes only the returned rows, so insights stay factual and traceable. |
| 📊 | Interactive visuals | Auto-generated bar / line / area charts with a clean dark Plotly theme. |
| 🔍 | Full transparency | Every answer exposes the Cortex interpretation, the generated SQL, and the raw result table. |
| ⚡ | Live KPIs | Rows, columns, status, and execution time surfaced per query. |
| 📥 | Export | One-click CSV download of any result set. |
| 🛠️ | Debug trace | Optional trace showing the semantic view, Cortex endpoint, and query metrics. |
| 🎨 | Production-grade UI | Custom dark theme, KPI cards, syntax-highlighted SQL, status pills. |
The pipeline is layered end-to-end, from raw files to a grounded answer:
Data Sources ─▶ AWS S3 ─▶ Snowpipe ─▶ Snowflake Raw ─▶ Analytics Layer
│
▼
Cortex Semantic View
│
User question ──▶ Streamlit ──▶ Cortex Analyst (NL → SQL) ─┘
│
Snowflake executes SQL ◀────────────┘
│
▼
OpenAI executive summary ──▶ UI (answer · SQL · chart · CSV)
| Layer | Responsibility |
|---|---|
| Data Sources | Installation records, customers, shipments, smart-meter telemetry, installer-team capacity. |
| AWS S3 | Landing zone for raw operational files. |
| Snowpipe | Auto-ingests files from S3 into Snowflake. |
| Raw Layer | RAW_INSTALLATIONS, RAW_CUSTOMERS, RAW_SHIPMENTS, RAW_SMART_METERS, RAW_INSTALLER_TEAMS. |
| Analytics Layer | ENPAL_DB.ANALYTICS — investigation views joining operational datasets + an agent-logs table. |
| Semantic AI Layer | Cortex Analyst semantic view encoding business metrics, dimensions, and relationships. |
| Application Layer | Streamlit app calling the Cortex Analyst REST API. |
| LLM Summary Layer | OpenAI converts results into an executive summary. |
| Output Layer | Root-cause analysis, shipment insights, smart-meter monitoring, workload reports, charts, CSV. |
- Snowflake Cortex Analyst — natural-language-to-SQL over a semantic view
- Snowflake — cloud data warehouse + REST API
- OpenAI — executive-summary generation (
gpt-4.1/gpt-4ofamily, selectable) - Streamlit — interactive web front end
- Plotly — interactive charting
- pandas — result handling and CSV export
- Python 3.10+
- A Snowflake account with Cortex Analyst enabled and a configured semantic view
- An OpenAI API key
git clone https://github.com/shaikfakruddin2018/operations-copilot.git
cd operations-copilot
python -m venv .venv
# Windows: .venv\Scripts\activate
# macOS/Linux: source .venv/bin/activate
pip install -r requirements.txt# copy the template, then fill in your values
cp .streamlit/secrets.toml.example .streamlit/secrets.tomlOpen .streamlit/secrets.toml and add your Snowflake + OpenAI credentials. This file is git-ignored and will never be committed.
streamlit run app.pyThe app opens at http://localhost:8501. Use the Test Snowflake button in the sidebar to verify connectivity, pick an example question, and click Run Analysis.
The app ships with 40+ curated operations questions, including:
- "Why are Berlin installations delayed?"
- "Which city should operations prioritize this week?"
- "Which delayed installations also have offline smart meters?"
- "Which installer teams are operating above capacity?"
- "Generate a management report for today's operations."
- No credentials in code. All secrets are read from
st.secrets(Streamlit secrets management). .streamlit/secrets.tomlis git-ignored — only the.exampletemplate is committed.- Authentication to the Cortex Analyst REST API uses the session token from the active Snowflake connection.
- Recommended: run under a least-privilege role scoped to the analytics schema and semantic view.
- Multi-turn conversational follow-ups
- Caching of repeated Cortex queries
- Role-based question sets (RBAC-aware)
- Scheduled email digests of daily operations
- Unit tests + CI workflow
Released under the MIT License.
Built by Shaik Fakruddin · Data & Analytics Engineer
