Knowledge Graph NL-to-SQL — Siemens Energy POC

Natural language querying across two databases — no ERD provided, relationships auto-inferred from instance data via SQL-pushed column profiling, semantic ontology built by LLM from column stats (null density, distinct ratio, value distributions), SQL generated by Claude Haiku.

---

What this does

You type a plain English question. The agent:

1. Loads a semantic ontology inferred entirely from column statistics — no hardcoded schema knowledge
2. Resolves join paths across two databases via structural FK inference
3. Generates SQL with exact enum values and correct table prefixes
4. Executes cross-DB SQL and returns results + chart config

Example queries that work today:

• "What is the breakdown of assets by equipment class?"
• "Which assets have the most critical outages?"
• "Which engineers own the most active campaigns?"
• "What product families are most involved in delayed maintenance events?"
• "Which asset equipment classes appear most in critical severity outages and have open service cases?" — 3-table cross-DB join

---

Two pipelines

This repo contains two end-to-end NL-to-SQL pipelines built on the same architecture, applied to different Siemens domains.

Pipeline 1 — Siemens Manufacturing (original)

operations.db + quality.db — machines, maintenance logs, parts inventory, production batches, defect reports. Human-in-the-loop annotation step for semantic relationship verbs.

Pipeline 2 — Siemens Energy (current, extended)

domain1.db + domain2.db — two domains: Operational & Power Grid + Execution & Energy Service Campaigns. Fully LLM-driven semantic inference — no human annotation step, no hardcoded edges. Column stats computed entirely via SQL (null density, distinct ratio, top value distributions, min/max/avg for numeric columns).

---

Architecture

Raw data (CSV / SQLite)
    ↓ generate_energy.py
domain1.db  (Power Grid)      domain2.db  (Campaigns)
    ↓ schema_profiler_v2.py   ← SQL-pushed stats per column
column_profiles.json          relationships.json
    ↓ semantic_inferrer_v2.py ← Haiku infers entity names + relationship verbs
                                from null density, distinct ratio, value distributions
ontology.json                 semantic_graph_v2.png
    ↓ query_agent_v2.py
NL → SQL → DataFrame + chart config

---

Databases

Domain 1 — Operational & Power Grid (domain1.db)

Table	Rows	Key entities
ASSET_VW	500	Physical energy assets — turbines, transformers, substations
OUTAGE__C_VW	100	Unplanned grid failures and forced outages
CASE_VW	3,000	Field service tickets and NCRs
SERVICE_WINDOW_C_VW_TEST	200	Pre-approved plant maintenance windows

Domain 2 — Execution & Energy Service Campaigns (domain2.db)

Table	Rows	Key entities
USER_VW	50	Field engineers and project managers
PRODUCT2_VW	100	Spare parts and service catalog items
CAMPAIGN_VW	30	Strategic retrofit and service campaigns
EVENT_VW	500	Field execution touchpoints

Auto-inferred relationships (7 total, zero hardcoded):

• OUTAGE__C_VW.ASSET_ID → ASSET_VW.ASSET_ID
• CASE_VW.ASSET_ID → ASSET_VW.ASSET_ID
• SERVICE_WINDOW_C_VW_TEST.ASSET_ID → ASSET_VW.ASSET_ID
• CASE_VW.OUTAGE_ID → OUTAGE__C_VW.OUTAGE_ID
• CAMPAIGN_VW.OWNER_ID → USER_VW.USER_ID (synonym: OWNER_ID ↔ USER_ID)
• EVENT_VW.PRODUCT_ID → PRODUCT2_VW.PRODUCT_ID
• EVENT_VW.CAMPAIGN_ID → CAMPAIGN_VW.CAMPAIGN_ID

---

File Reference

Pipeline 2 — Energy Domain (current)

File	Input	Output	Purpose
generate_energy.py	—	domain1.db, domain2.db	Generates Siemens Energy data with realistic value distributions, null density, and referential integrity
schema_profiler_v2.py	domain1.db, domain2.db	column_profiles.json, relationships.json	SQL-pushed column profiling: null density, distinct ratio, top value distributions, min/max/avg for numeric columns. Structural FK inference — no value sampling
semantic_inferrer_v2.py	column_profiles.json, relationships.json	ontology.json, semantic_graph_v2.png	LLM infers semantic entity names and relationship verbs from column stats alone. No hardcoded edges, no human annotation step
query_agent_v2.py	ontology.json, domain1.db, domain2.db	SQL results + chart config	NL-to-SQL agent with semantic context injection, exact enum values, cross-DB join resolution

Pipeline 1 — Manufacturing Domain (original)

File	Input	Output	Purpose
generate.py	—	data/*.csv, operations.db, quality.db	Generates Siemens manufacturing data — machines, maintenance, parts, batches, defects
schema_profiler_local.py	operations.db, quality.db	relationships.json, schema_context.json	Profiles both DBs, infers FK relationships
schema_graph.py	relationships.json, schema_context.json	schema_graph.png	Structural NetworkX graph with find_join_path()
generate_ontology_draft.py	siemens_schema.yaml	ontology_draft.json	Human-annotatable ontology draft with AUTO_PROPOSED edge markers
semantic_inferrer.py	ontology_draft.json	semantic_graph.png	Semantic graph after human annotation
query_agent.py	ontology_draft.json, operations.db, quality.db	SQL results + chart config	NL-to-SQL agent — original pipeline

---

Key design decisions

SQL-pushed column profiling

Stats are computed directly in SQL — no Python-side row fetching. For varchar columns: null_count, null_percent, distinct_count, distinct_ratio, top 10 value distributions with frequency and percentage, 10 random samples. For numeric columns: same null/distinct stats plus MIN, MAX, AVG. This matches Snowflake ETL best practices and scales to 1M+ rows without memory pressure.

Structural FK inference (zero hardcoded)

Relationships are inferred by identifying columns where exactly one side has distinct_ratio > 0.95 (true PK — nearly every row unique) and the other side references it (FK). A synonym map handles mismatched column names (OWNER_ID → USER_ID). No value overlap sampling needed — pure structural inference.

LLM-driven semantic inference

semantic_inferrer_v2.py makes two types of Haiku calls:

• Per table — feeds null density, distinct ratio, top value distributions, sample values → Haiku infers entity_label and description
• Per relationship — feeds both tables' full column profiles + join key → Haiku infers the relationship verb (AFFECTS, DOCUMENTS, REQUIRES_MAINTENANCE_ON)

No hardcoded entity names or relationship verbs anywhere in the codebase.

schema-automator integration (Pipeline 1)

schemauto generalize-tsvs runs on all CSVs to produce siemens_schema.yaml — a formal LinkML schema with inferred types, enumerations, and identifiers from instance data alone. No metadata or ERD required.

---

Setup

Prerequisites

• Python 3.10+
• Anthropic API key

Install dependencies

pip install faker pandas numpy networkx matplotlib pyyaml anthropic python-dotenv schema-automator

Environment variables

Create a .env file in the project root:

ANTHROPIC_API_KEY=your_key_here

---

Running Pipeline 2 (Energy Domain)

Run in order — each step depends on the previous output.

# Step 1 — Generate data
python generate_energy.py
# → domain1.db, domain2.db

# Step 2 — Profile schemas via SQL
python schema_profiler_v2.py
# → column_profiles.json, relationships.json

# Step 3 — LLM semantic inference
python semantic_inferrer_v2.py
# → ontology.json, semantic_graph_v2.png

# Step 4 — Run the query agent
python query_agent_v2.py
# → 7 test queries across both DBs

---

Running Pipeline 1 (Manufacturing Domain)

# Step 1 — Generate data
python generate.py

# Step 2 — Profile and build structural graph
python schema_profiler_local.py
python schema_graph.py

# Step 3 — schema-automator
schemauto generalize-tsvs data\machines.csv data\maintenance_logs.csv data\parts_inventory.csv data\production_batches.csv data\defect_reports.csv --column-separator "," -o siemens_schema.yaml

# Step 4 — Generate ontology draft + annotate
python generate_ontology_draft.py
# Open ontology_draft.json, search AUTO_PROPOSED, review 6 edges, set to REVIEWED

# Step 5 — Build semantic graph and run agent
python semantic_inferrer.py
python query_agent.py

---

How the agent works

NL query
    ↓
Semantic context injected into prompt:
  - Entity names + descriptions
  - Named relationships (LLM-inferred verbs)
  - Exact enum values per column
  - Pre-computed join paths
    ↓
Claude Haiku → JSON: { sql, chart, explanation }
    ↓
SQLite ATTACH (domain1.db attaches domain2.db as 'domain2')
    ↓
Cross-DB SQL executes natively
    ↓
DataFrame + chart config

SQL prefix rules (SQLite ATTACH):

• domain1 tables → no prefix: ASSET_VW, OUTAGE__C_VW, CASE_VW, SERVICE_WINDOW_C_VW_TEST
• domain2 tables → domain2. prefix: domain2.USER_VW, domain2.CAMPAIGN_VW, etc.

---

Semantic graph outputs

schema_graph.png — structural Tables as nodes, FK column names as edge labels. Blue = same-DB. Red dashed = cross-DB. Built from structural inference only.

semantic_graph_v2.png — semantic ontology Business entities as nodes: PowerAsset, GridOutage, ServiceCase, ServiceWindow, EnergySystemUser, Product, EnergyCampaign, MaintenanceEvent. Named relationship edges: AFFECTS, REPORTS, REQUIRES_MAINTENANCE_ON, DOCUMENTS, OWNS, INVOLVES, BELONGS_TO. Inferred by Haiku from column stats — no hardcoded labels.

---

Snowflake migration

When moving from local SQLite to Snowflake production data:

What changes	How
DB connections in query_agent_v2.py	Swap sqlite3.connect + ATTACH for snowflake.connector.connect
Schema profiler	Same SQL stat queries work natively on Snowflake — change connection only
SQL prefix rules	Drop domain2. prefix — Snowflake handles cross-schema joins via schema.table notation
FK inference	Value overlap will work at scale — large ETL tables reuse ID pools so sampling catches them automatically
schema-automator	schemauto import-sql snowflake+snowflake://...

Everything else — semantic_inferrer_v2.py, query_agent_v2.py, ontology.json — is identical.

---

What is not built yet

Component	Notes
Frontend (app.py)	Streamlit — chat input, results table, auto chart, graph viewer. ~1 session.
Annotation UI	generate_ontology_draft.py (Pipeline 1) produces AUTO_PROPOSED markers for domain expert review. Could become a simple web form.
ChromaDB embeddings	Semantic retrieval for large schemas (10+ tables). Not needed at current scale.
OAK ontology annotation	schema-automator's full annotation pipeline. Needs a manufacturing/energy-specific ontology source. Long-term production upgrade.

---

Project structure

knowledge-graph-nl-sql/
│
├── Pipeline 2 — Energy Domain (current)
│   ├── generate_energy.py
│   ├── schema_profiler_v2.py
│   ├── semantic_inferrer_v2.py
│   ├── query_agent_v2.py
│   ├── domain1.db
│   ├── domain2.db
│   ├── column_profiles.json
│   ├── relationships.json
│   ├── ontology.json
│   └── semantic_graph_v2.png
│
├── Pipeline 1 — Manufacturing Domain (original)
│   ├── generate.py
│   ├── schema_profiler_local.py
│   ├── schema_graph.py
│   ├── generate_ontology_draft.py
│   ├── semantic_inferrer.py
│   ├── query_agent.py
│   ├── operations.db
│   ├── quality.db
│   ├── siemens_schema.yaml
│   ├── ontology_draft.json
│   └── semantic_graph.png
│
├── .env
└── README.md

---

Tech stack

Layer	Tool
Data generation	Python, Faker, NumPy, Pandas
Local databases	SQLite (ATTACH for cross-DB joins)
Column profiling	SQL-pushed stats (null density, distinct ratio, top distributions, min/max/avg)
Schema inference	schema-automator (LinkML ecosystem, Monarch Initiative)
Structural graph	NetworkX, Matplotlib
Semantic inference	Claude Haiku (claude-haiku-4-5) — entity names + relationship verbs from column stats
NL-to-SQL agent	Claude Haiku via Anthropic API
Production DB (future)	Snowflake