Content
# MCP Semantic Layer on Databricks
> A governed MCP semantic layer on Databricks — Metric Views + Genie Space +
> UC Functions + Unity AI Gateway, wrapped with a thin custom MCP server
> that adds synonym resolution, certification signals, and prompt templates.
Real Olist Brazilian E-Commerce dataset (~100K orders, 2016-2018) deployed
end-to-end on Databricks: Delta medallion → native Metric Views → custom
MCP server as a Databricks App.
> **New here?** Start with [`docs/ONBOARDING.md`](docs/ONBOARDING.md) —
> the what, the why, setup steps, and a tour of every component.
## Architecture
```mermaid
flowchart TD
C[Claude Desktop / Cursor / Agent Bricks]
C -- "MCP (Streamable HTTP)" --> APP
subgraph APP[Custom MCP Server<br>Databricks App: mcp-olist]
S[synonym resolver]
B[certification badges]
P[prompt templates]
end
APP --> UC[UC Functions MCP]
APP --> GS[Genie Space MCP]
APP --> SQL[(Databricks SQL MCP)]
APP --> VS[(Vector Search MCP)]
subgraph GATEWAY[Unity AI Gateway]
GW[4 service policies<br>PII / cross-region / rate-limit / certification]
end
APP -- every call --> GATEWAY
subgraph UC_LAKEHOUSE[Unity Catalog<br>main.olist_ecommerce]
BR[(bronze_*)] --> SI[(silver_*)] --> GO[(gold_*)]
GO --> MV[(Metric Views)]
MV --> GENIE[Genie Space<br>Olist Business Analytics]
end
UC -.queries.-> UC_LAKEHOUSE
GS -.queries.-> UC_LAKEHOUSE
SQL -.queries.-> UC_LAKEHOUSE
```
## Why this project exists
Databricks shipped most of a semantic layer natively as of DAIS 2026 —
Metric Views, Genie One, Managed MCP servers, Unity AI Gateway.
**Three enterprise gaps remain that vendors charge $500K/year to fill:**
| Gap | What it means | This project's implementation |
|-----|---------------|-------------------------------|
| **Synonym resolution** | "AOV" / "average basket" / "avg order value" must hit the same metric | `src/synonym_resolver.py` (YAML-driven) |
| **Certification signals** | Agents must know *before querying* if a metric is "finance-grade" | `src/certification.py` (reads Metric View COMMENT tags) |
| **Prompt templates** | Reusable analytical workflows (trend, period compare, seller diagnosis) | `src/prompts.py` (4 registered prompts) |
Everything else — Metric Views, Genie Space, UC Functions MCP, Vector Search
MCP, Unity AI Gateway enforcement — is native Databricks. This project
builds ONLY the 3-gap delta, ~2K LOC of custom code.
## What's native vs. what we built
| Native from Databricks (zero code) | We built (the 3 gaps) |
|---------------------------------------------|---------------------------------------------|
| Metric Views (UC-governed metrics) | Synonym resolver (`src/synonym_resolver.py`) |
| Genie Space / Genie One | Certification badges (`src/certification.py`) |
| UC Functions MCP (governed queries) | Prompt templates (`src/prompts.py`) |
| Databricks SQL MCP | Thin FastMCP wrapper (`src/server.py`) |
| Vector Search MCP | Unity AI Gateway policy stubs (`src/policies.py`) |
| Unity Catalog (3-level namespace) | Databricks Asset Bundle (`databricks.yml`) |
| Unity AI Gateway (runtime policy enforcement) | Databricks App manifest (`app.yaml`) |
## Setup
```bash
git clone https://github.com/<you>/mcp-semantic-layer-databricks.git
cd mcp-semantic-layer-databricks
# 1. Local Python env
make setup # venv + pip install -e .[dev]
cp .env.example .env # fill in DATABRICKS_HOST/TOKEN/HTTP_PATH
# 2. Databricks CLI
databricks auth login # authenticates the auto-healer profile
# 3. Validate + deploy the whole bundle (notebooks + app + job)
databricks bundle validate
databricks bundle deploy -p auto-healer
# 4. Run the full pipeline once (bronze → silver → gold → Metric Views → UC Functions → Genie)
databricks bundle run olist_pipeline -p auto-healer
```
For granular control — run a single notebook task:
```bash
make load-bronze
make build-silver
make build-gold
make deploy-metric-views
make deploy-uc-functions
make configure-genie
```
## Example chat transcript
```
┌─── Claude Desktop (attached to mcp-olist MCP server) ──────────────────┐
│ │
│ You: What was total revenue last quarter by state? │
│ │
│ Claude: Let me check the available metrics first. │
│ │
│ [tool: list_metrics] │
│ → 🟢 authoritative `revenue_metrics` owner: @finance │
│ → 🟢 authoritative `delivery_metrics` owner: @operations │
│ → 🟡 estimated `seller_metrics` owner: @marketplace │
│ → 🟢 authoritative `customer_metrics` owner: @analytics │
│ │
│ I'll use `revenue_metrics` (🟢 authoritative), grouped by customer_state│
│ and order_quarter with quarter = 2018-Q2. │
│ │
│ [tool: query_metric(metric="total_revenue", │
│ time_grain="quarter", │
│ dimensions=["customer_state"])] │
│ │
│ 🟢 authoritative · 27 rows · time_grain=quarter │
│ │
│ | customer_state | quarter | total_revenue | │
│ | -------------- | --------- | ------------- | │
│ | SP | 2018-Q2 | 624,391.02 | │
│ | RJ | 2018-Q2 | 218,444.70 | │
│ | MG | 2018-Q2 | 193,707.66 | │
│ | ... | ... | ... | │
│ │
│ ```sql │
│ SELECT customer_state, order_quarter, SUM(total_revenue) │
│ FROM main.olist_ecommerce.revenue_metrics │
│ WHERE order_quarter = '2018-04-01' │
│ GROUP BY customer_state, order_quarter │
│ ``` │
│ │
│ São Paulo accounts for ~38% of Q2 2018 revenue. │
└─────────────────────────────────────────────────────────────────────────┘
```
## Project layout
```
mcp-semantic-layer-databricks/
├── databricks.yml # DAB: deploys the app + the pipeline job
├── app.yaml # Databricks App manifest (mcp-olist)
├── Dockerfile # container pinning (optional — DAB works without it)
├── requirements.txt # runtime deps (mirrors pyproject.toml)
├── pyproject.toml # local dev: pip install -e .
├── Makefile # setup / load-bronze / ... / deploy-mcp / test
│
├── data/olist/ # 8 Olist CSV files (real Kaggle data)
│
├── notebooks/ # 6 Databricks notebooks (deployed via DAB)
│ ├── 01_load_bronze.py
│ ├── 02_build_silver.py
│ ├── 03_build_gold.py
│ ├── 04_create_metric_views.py # native Metric Views DDL
│ ├── 05_register_uc_functions.py # governed UC SQL functions
│ └── 06_configure_genie_space.py # Genie One ontology + sample Qs
│
├── src/ # the ~2K LOC of custom code
│ ├── server.py # FastMCP entrypoint (streamable-http)
│ ├── tools.py # 5 MCP tools
│ ├── resources.py # 3 MCP resources
│ ├── prompts.py # 4 prompt templates
│ ├── synonym_resolver.py # gap 1: natural language → Metric View
│ ├── certification.py # gap 2: certification badges
│ ├── policies.py # Unity AI Gateway policy pseudocode
│ └── gen_client.py # wrapper over managed Databricks MCP servers
│
├── semantic/
│ └── synonyms.yml # synonym dictionary (read by synonym_resolver)
│
├── tests/
│ ├── unit/ # pure-Python tests (no Databricks needed)
│ ├── integration/ # require live UC connection
│ └── golden/ # hand-computed CSV comparisons
│
└── docs/
├── architecture-faq.md
└── olist-demo-walkthrough.md
```
## Testing
```bash
# Unit (synonyms, badges, prompts — pure Python, no Databricks)
make test # → pytest tests/unit
# Integration (Metric Views, UC Functions, Genie, MCP tools — needs live UC)
DATABRICKS_TOKEN=... pytest tests/integration -v
# Golden (revenue + delivery totals vs CSV-computed expected values)
DATABRICKS_TOKEN=... pytest tests/golden -v
```
## License
MIT. See `LICENSE`.
