The SQL Alchemist

The SQL Alchemist is a local Business Intelligence assistant that turns natural language questions into DuckDB SQL over flight operations data.

It currently supports two interfaces:

• a terminal experience through src/main.py (or main.py at the project root)
• a Streamlit web app through src/app.py

Both interfaces share the same analytics engine in src/core.py.

The project uses local LLMs via Ollama to generate SQL, applies fallback logic when model output fails, and provides interactive analytics for airline performance, disruption cost, and operational quality.

Screenshots

Dashboard	Natural-language chat	Airline comparison

To refresh screenshots after UI changes:

pip install playwright && playwright install chromium
python3 scripts/capture_screenshots.py

Project Structure

sql_alchemist/
├── data/
│   └── flights.csv
├── docs/
│   ├── images/              # README screenshots (PNG)
│   ├── DEPENDENCIES.md
│   ├── DEPLOYMENT.md
│   └── GITHUB_SETUP.md
├── notebooks/
│   └── main.ipynb
├── scripts/
│   ├── capture_screenshots.py
│   ├── enrich_flights_dates.py
│   └── docker-entrypoint.sh
├── src/
│   ├── core.py              # shared BI engine (ChatBI, analytics, explanations)
│   ├── health.py            # deployment health checks
│   ├── i18n.py              # EN/PT UI strings
│   ├── main.py              # CLI interface
│   └── app.py               # Streamlit interface
├── tests/
│   ├── conftest.py
│   ├── test_core.py
│   ├── test_config.py
│   ├── test_health.py
│   └── test_i18n.py
├── .github/
│   └── workflows/
│       └── ci.yml
├── Dockerfile
├── docker-compose.yml
├── docker-compose.prod.yml
├── config.py
├── main.py                  # convenience CLI entry point
├── .env.example
├── .env.production.example
├── .gitignore
├── LICENSE
├── pyproject.toml
├── README.md
├── requirements.txt
└── requirements-dev.txt

Architecture

Module	Responsibility
config.py	Environment-based configuration (OLLAMA_HOST, DATA_PATH, model chain, costs)
src/core.py	DuckDB loading, Ollama SQL generation, fallback logic, watchdog, airline comparison, KPI helpers
src/main.py	Terminal UI with Rich (/dashboard, /compare, /suggest, chat)
src/app.py	Streamlit dashboards, charts, chat, CSV export
src/health.py	Startup and HTTP health checks for deployment
src/i18n.py	EN/PT localization for CLI and Streamlit
notebooks/main.ipynb	Lightweight experimentation that imports from src/core.py
tests/	Automated tests for core, config, health, and i18n

What the Project Does

The engine loads data/flights.csv into an in-memory DuckDB table named flights, then:

1. accepts a natural-language question
2. asks a local Ollama model to generate SQL
3. sanitizes and validates the SQL
4. executes the query against DuckDB
5. returns results in either CLI or Streamlit UI
6. falls back to keyword-based SQL when model generation fails

This makes the project usable even when a model is unavailable, returns invalid SQL, or cannot be reached locally.

Interfaces

1. Terminal Interface

The CLI version in src/main.py provides a lightweight local chat workflow for asking flight-related questions directly in the terminal.

2. Streamlit Web App

The Streamlit app in src/app.py extends the project with:

• model fallback chain support
• KPI and chart-based analytics
• chat-based natural language exploration
• business impact estimation
• watchdog quality checks
• airline-vs-airline comparison
• result explanation logic
• suggested prompts for faster interaction
• CSV export for dashboard and chat results

Data Model

The main dataset is stored in:

data/flights.csv

The flights table is loaded from CSV and enriched at query time with derived analytics fields.

Source columns (from flights.csv):

• flight_id
• airline
• origin
• destination
• departure_date, arrival_date (YYYY-MM-DD)
• departure_time, arrival_time (HH:MM)
• latency_minutes
• status

Derived columns (computed when DuckDB loads the dataset):

• route — origin → destination (e.g. CDG → JFK)
• departure_hour, departure_minute, arrival_hour, arrival_minute — parsed from time strings
• departure_time_of_day — Morning, Afternoon, Evening, or Night
• scheduled_duration_minutes — scheduled block time between departure and arrival (overnight-aware)
• day_of_week, departure_month, departure_year — calendar analytics from departure_date

Supported status values include:

• On-Time
• Delayed
• Cancelled

The dataset has been expanded with more realistic records to improve aggregate analysis, filtering, routing comparisons, and seasonal patterns.

Regenerate calendar dates after replacing the CSV with:

python3 scripts/enrich_flights_dates.py

Main Features

Natural Language to SQL

Users can ask questions in plain English, and the app uses a local Ollama model to convert the question into a DuckDB SELECT query.

SQL Safety and Fallback

To improve reliability, generated SQL is sanitized and validated before execution. If generation fails or produces invalid output, the app falls back to intent-based SQL patterns such as average latency, cancellations, delays, or counts by status.

Streamlit Analytics Layer

The web app includes:

• total flights, average latency, delayed flight KPIs
• latency visualizations
• estimated disruption cost by airline
• watchdog quality distribution
• filtered results table
• auto-charting for numeric chat results

Business Impact Estimation

The Streamlit version estimates disruption cost using configurable business rules:

• delay cost = latency_minutes × delay_cost_per_minute
• cancellation cost = fixed cancellation_cost
• total cost = delay cost + cancellation cost

These values can be changed in the sidebar of the app.

Watchdog Quality Layer

The app classifies records into operational quality groups:

• Reliable
• Review
• High Risk

This is based on airline-relative latency behavior using configurable sensitivity (relaxed, normal, strict) with percentile and standard-deviation thresholds.

Airline Comparison

The airline comparison view compares two airlines on a selected destination using:

• average latency
• on-time rate
• cancellation rate
• total disruption cost
• ranking metrics

This provides a direct route-level rivalry view for operational benchmarking.

Requirements

Before running the project, make sure you have:

• Python 3.10 or later
• Ollama installed locally
• at least one local Ollama model pulled
• a valid config.py in the project root
• the dataset available at the configured path

Installation

From the project root:

git clone https://github.com/PedroCarneiroMarques/the-sql-alchemist.git
cd the-sql-alchemist
python3 -m pip install -r requirements.txt

For development and tests:

python3 -m pip install -r requirements-dev.txt

Ollama Setup

Start the Ollama server locally:

ollama serve

Pull at least one model. Example:

ollama pull mistral:7b

You can also pull additional models used in the fallback chain, such as:

ollama pull phi4:14b
ollama pull qwen2.5-coder:14b
ollama pull deepseek-r1:8b

Configuration

Configuration lives in config.py at the project root. Values can be overridden with environment variables:

Variable	Default	Description
DATA_PATH	data/flights.csv	Path to the flights dataset
OLLAMA_HOST	http://localhost:11434	Ollama API endpoint
OLLAMA_TIMEOUT	180	Request timeout in seconds
DEFAULT_MODEL_CHAIN	comma-separated model list	Balanced profile fallback order
DEFAULT_MODEL_PROFILE	balanced	Default profile: fast, balanced, or accurate
MODEL_PROFILE_FAST	mistral:7b	Model chain for the fast profile
MODEL_PROFILE_ACCURATE	larger models first	Model chain for the accurate profile
DEFAULT_WATCHDOG_SENSITIVITY	normal	Watchdog level: relaxed, normal, or strict
LOG_LEVEL	INFO	Logging verbosity: DEBUG, INFO, WARNING, ERROR
LOG_TO_FILE	true	Write logs to logs/sql_alchemist.log
UI_LOCALE	en	UI language: en or pt (Streamlit sidebar can override per session)
APP_ENV	development	development or production (stricter validation in production)
DEPLOYMENT_SECRETS_READY	unset	Set to true in production after secrets are injected
OLLAMA_API_KEY	unset	Optional API key for authenticated Ollama endpoints
DEFAULT_DELAY_COST_PER_MINUTE	50	Delay cost per minute (€)
DEFAULT_CANCELLATION_COST	200	Fixed cancellation cost (€)

Example:

cp .env.example .env
# edit .env as needed

Or export variables directly:

export DATA_PATH="data/flights.csv"
export OLLAMA_HOST="http://localhost:11434"
export DEFAULT_MODEL_CHAIN="mistral:7b,phi4:14b,qwen2.5-coder:14b"

Running the CLI Version

Run from the project root:

python3 src/main.py

Or use the root entry point:

python3 main.py

Set UI language with UI_LOCALE=pt or python3 main.py --lang pt.

In the terminal:

• ask questions in plain English
• use /filter, /profile, /dashboard, /compare, /export, /suggest, /models, or /help
• switch model profile: /profile fast, /profile balanced, /profile accurate
• adjust watchdog sensitivity: /watchdog relaxed, /watchdog normal, /watchdog strict
• quick comparison: /compare AirlineA AirlineB Destination (alias: /wars)
• type quit, exit, or q to leave

Exported CSV files are saved under exports/ at the project root.

Running the Streamlit App

Run from the project root:

streamlit run src/app.py

Default language comes from UI_LOCALE; users can also switch language in the Streamlit sidebar.

This matters because config.py is stored in the project root and the app expects the repository root to be part of the Python path.

The app will usually open at:

http://localhost:8501

Docker Demo

Run the Streamlit app and Ollama together with Docker Compose:

docker compose up --build -d

Open:

http://localhost:8501

Pull at least one model into the Ollama container (first run only):

docker compose exec ollama ollama pull mistral:7b

Optional models for balanced/accurate profiles:

docker compose exec ollama ollama pull phi4:14b
docker compose exec ollama ollama pull qwen2.5-coder:14b

View logs:

docker compose logs -f app
docker compose logs -f ollama

Stop the stack:

docker compose down

Exported CSV files and application logs are written to exports/ and logs/ on the host via volume mounts.

Health checks

Before the Streamlit container starts, the entrypoint runs:

python -m src.health --startup

This validates configuration and confirms that DATA_PATH exists.

While the stack is running:

Check	Command / URL
Streamlit HTTP health	http://localhost:8501/_stcore/health
Config + dataset	python -m src.health --startup
Streamlit + optional Ollama	python -m src.health --http --ollama

Docker Compose configures an app healthcheck against the Streamlit endpoint. Override runtime settings by copying .env.example to .env and adjusting values to match the environment block in docker-compose.yml.

For production, see docs/DEPLOYMENT.md and docker-compose.prod.yml.

CI and GitHub

CI runs on every push/PR to main. If git push fails with a workflow scope error, follow docs/GITHUB_SETUP.md.

Streamlit Features

The Streamlit app currently includes:

• installed-model detection from local Ollama
• model profiles: fast, balanced, accurate (with optional custom chain)
• fallback model execution chain
• generated SQL preview
• results table rendering
• automatic charting for numeric chat results (bar, line, or pie based on data shape)
• suggested prompts
• business impact controls in the sidebar
• global airline filters
• route-level airline comparison
• in-session chat history (metadata-only storage; results reloaded from SQL on demand)
• CSV download buttons for filtered data, cost summaries, airline comparison, and chat results

Testing

Run the automated test suite from the project root:

python3 -m pytest tests/ -v

The tests cover SQL safety (including sqlparse guardrails), keyword fallback, few-shot prompts, watchdog logic, airline comparison, explanations, and CSV export helpers. They do not require a running Ollama instance.

CI runs automatically on GitHub Actions for Python 3.11 and 3.12 on every push and pull request to main.

Logging

Structured logs are written to stderr and, by default, to:

logs/sql_alchemist.log

Useful events include dataset loading, Ollama model detection failures, rejected SQL, model attempts, query timings, and keyword fallback usage. Control verbosity with:

export UI_LOCALE=pt
export LOG_LEVEL=DEBUG
export LOG_TO_FILE=false

Screenshots and Images

Screenshots live in docs/images/ and are embedded at the top of this README.

File	Description
dashboard-overview.png	KPIs, charts, watchdog, and cost analytics
chat-analysis.png	Natural-language query with SQL, table, and auto-chart
airline-comparison.png	Head-to-head airline comparison on a route

Regenerate with python3 scripts/capture_screenshots.py (requires Playwright).

Typical Questions to Ask

Examples:

• Which airlines have the highest average latency?
• How many flights were cancelled by airline?
• Show delayed flights ordered by latency.
• What is the distribution of flight statuses?
• Which routes have the highest average delay?
• What is the estimated total cost by airline?
• Which destinations have the most delayed flights?
• Which airlines have the best on-time performance?

Reliability and Fallback Strategy

To improve stability:

• model output is sanitized before execution
• only SELECT / WITH ... SELECT queries are accepted
• table whitelist (flights only) and forbidden keywords (UNION, DROP, etc.)
• non-safe SQL is rejected
• invalid model output triggers keyword-based fallback SQL
• execution errors are surfaced in the UI
• result explanations attempt to summarize returned data safely

Recent improvements include:

• shared analytics engine in src/core.py
• removal of duplicated notebook logic
• safer explanation logic for result metrics
• CSV export from the Streamlit UI
• automated pytest coverage for core behavior

Troubleshooting

1. ModuleNotFoundError: No module named 'config'

Make sure:

• config.py exists in the project root
• you run the app from the repository root, not from inside src/

Correct:

cd the-sql-alchemist
streamlit run src/app.py

2. Dataset not found

Check that DATA_PATH in config.py points to a real CSV file, for example:

"DATA_PATH": "data/flights.csv"

3. Ollama connection errors

Make sure Ollama is running:

ollama serve

Also verify the host in config.py:

"OLLAMA_HOST": "http://localhost:11434"

4. No local models available

List installed models:

ollama list

If needed, pull one:

ollama pull mistral:7b

5. Streamlit app runs but query generation fails

This usually means:

• Ollama is not running
• the selected model is not installed
• the model returned invalid SQL
• the prompt was too ambiguous

The app will try the configured fallback chain and then use keyword-based SQL if needed.

Dependencies

Runtime dependencies are listed in requirements.txt and pyproject.toml. Development extras (including pytest) are in requirements-dev.txt (pip install -e ".[dev]").

Additional dependency notes are documented in docs/DEPENDENCIES.md.

Core runtime libraries: DuckDB, Ollama, Streamlit, Plotly, Pandas, Rich, and sqlparse. Development: pytest.

Notebook

The notebook imports the production modules instead of duplicating them:

notebooks/main.ipynb

Use it to validate dataset loading, test fallback queries, and explore watchdog/cost outputs without launching the full CLI or Streamlit app.

Current Status

The project currently includes:

• shared engine in src/core.py
• CLI interface in src/main.py
• Streamlit interface in src/app.py
• local LLM integration via Ollama
• DuckDB-powered local analytics
• fallback SQL behavior
• business impact estimation
• watchdog anomaly scoring
• airline rivalry comparison
• CSV export from the web UI
• automated tests in tests/
• Docker Compose stack for Streamlit + Ollama

Tech Stack

• Python
• DuckDB
• Streamlit
• Ollama
• Plotly
• Pandas
• Rich
• pytest

Roadmap

Possible next improvements:

• automated screenshot refresh in CI
• additional airline datasets

License

See the LICENSE file for project licensing details.