Scottish Water runs one of the UK's largest capital investment programmes. Its project teams had a data access problem dashboards couldn't solve. The data existed — project status, financial performance, delivery milestones, risk registers — but retrieving it meant navigating dozens of reports, hunting through SharePoint, or waiting for a data specialist. The utility built SPARK, a conversational interface on top of Databricks Genie inside Microsoft Teams. Project teams now ask plain-English questions and get governed answers in seconds.
The architecture spans four layers. A user asks a question in Teams via Copilot. A Copilot supervisor agent routes the request to a Databricks Genie Space over the Model Context Protocol (MCP) — a production MCP integration between two enterprise vendors, not a demo. Genie translates the question into SQL, executes it against Unity Catalog, and returns the result. The response surfaces back in Teams without the user leaving the chat window.
MCP keeps orchestration in Copilot while keeping data governance in Databricks. Neither vendor owns the full stack; MCP is the handoff point. That's a reusable pattern for organizations running both Microsoft 365 Copilot and a Databricks lakehouse.
Scottish Water built the governance architecture before the conversational layer. It did not expose raw Unity Catalog tables to Genie. Instead, the team curated a gold layer with only data needed for the capital investment use case, then built a semantic layer on top using Databricks metric views. Metric views standardise measures, dimensions, and business terminology — "risk exposure" and "live risk score" carry the same definition across every query. This consistency is what lets the system return answers users trust rather than plausible-sounding hallucinations.
A typical project data lookup previously required eight clicks plus dashboard load time. With SPARK, it starts with a single typed question. Report retrieval that required navigating SharePoint, the Reporting Hub, report categories, and individual report links — a 4-to-5-step process — collapses to direct question-and-answer. At 100 users asking 3 questions per week (300 requests weekly), the team projects 2 to 5 minutes saved per request. That compounds to 520–1,300 hours annually.
| Metric | Before SPARK | With SPARK |
|---|---|---|
| Project data lookup | 8 clicks + dashboard load time | 1 typed question |
| Report access path | 4–5 steps via SharePoint & Reporting Hub | Direct question-and-answer |
| Time per request | Baseline | 2–5 minutes saved |
| Weekly request volume (100 users × 3 q) | — | 300 requests / week |
| Projected annual time saved | — | 520–1,300 hours / year |
Static dashboards answer the questions their designers anticipated. Conversational interfaces answer whatever someone needs right now: "List all open project risks expiring in August, including risk owner and expiry date" or "What is the risk with the highest current exposure for project X?" Those queries don't fit neatly into pre-built reports. They fit into a well-tuned Genie Space backed by a semantic layer.
The hard part, which most teams skip, is the semantic layer. Genie generates SQL against raw tables. It can't resolve ambiguity baked into inconsistent naming conventions or conflicting business logic across reporting domains. Scottish Water's implementation worked because gold layer curation and metric view definitions happened first. The conversational interface was the last mile, not the foundation.
Architect takeaway: if you're wiring an LLM Q&A layer onto a data warehouse, the gating work is a governed gold layer and a semantic layer with standardised metric definitions. The LLM interface is straightforward once those exist.