Semantic Metrics Modeling Assistant

A production-ready MCP agent that helps data teams define, validate, and visualize semantic metrics with enterprise-grade persistence, trust scoring, and BI integrations.

Overview

The Semantic Metrics Modeling Assistant is a Model Context Protocol (MCP) agent designed to reduce cognitive load for data teams working with semantic layers. It provides a conversational interface for defining metrics, visual feedback on lineage and dependencies, and sophisticated trust indicators with persistent storage that help teams build confidence in their data.

Key Features

🗄️ Enterprise Data Persistence

• SQLite Database - Persistent storage with full audit trails

• 5-Table Schema - Metrics, history, tests, usage, and trust scores

• Change Tracking - Complete history of all metric modifications

• Version Control Ready - Designed for Git integration and rollback

💬 Conversational Metric Definition

Define metrics naturally through conversation:

"Define 'Active Users' as daily unique logins"
"Create a metric for revenue per customer"
"What's the definition of our churn rate metric?"

📊 BI Tool Integrations

Export metrics to industry-standard platforms:

• Looker (LookML) - Generate production-ready LookML files

• Tableau (TDS) - Export as Tableau Data Source XML

• dbt - Create dbt metric YAML definitions

🎯 Enhanced Trust Scoring

Sophisticated quality assessment with weighted algorithms:

• Tests (35% weight) - Validation test coverage and pass rates

• Usage (20% weight) - Adoption across teams and dashboards

• Freshness (15% weight) - Data staleness with time-decay

• Documentation (15% weight) - Completeness and clarity

• Ownership (15% weight) - Clear accountability

• Trend Analysis - Track quality changes over time with sparklines

🎨 Visual Lineage & Dependencies

Modern visualization options for understanding metric relationships:

• Mermaid Diagrams - Beautiful flowcharts for GitHub/Notion/Confluence

• ASCII Trees - Terminal-friendly dependency visualization

• Impact Analysis - Understand downstream effects before changes

• Circular Dependency Detection - Identify and resolve conflicts

✅ Comprehensive Testing

Production-ready test coverage:

• 35+ Tests - Database, trust scoring, exporters, and tools

• pytest Framework - Industry-standard testing with fixtures

• 90%+ Coverage - Core functionality thoroughly validated

• CI/CD Ready - Automated testing for continuous integration

Why This Matters

The Problem

Data teams face systemic challenges:

• Metric proliferation - Multiple conflicting definitions for core business metrics

• Trust deficits - Lack of quality signals leads to metric shopping and inconsistent reporting

• Cognitive overhead - Complex dependency chains and lineage are difficult to reason about

• Governance fragmentation - Ownership and validation processes are ad-hoc or non-existent

The Solution

This assistant addresses these challenges through:

• Abstraction layers - Natural language interface abstracts YAML configuration complexity

• Transparency mechanisms - Multi-dimensional trust scores make quality visible

• Governance automation - Built-in prompts for ownership, testing, and documentation

• Observability instrumentation - Usage tracking and freshness monitoring

Architecture

┌─────────────────────────────────────────┐
│  Conversational Interface (MCP Tools)   │
├─────────────────────────────────────────┤
│  • define_metric()                      │
│  • validate_metric()                    │
│  • visualize_lineage()                  │
│  • check_trust_score()                  │
│  • search_metrics()                     │
└─────────────────────────────────────────┘
                    ↓
┌─────────────────────────────────────────┐
│  Metric Repository                      │
├─────────────────────────────────────────┤
│  • Stores metric definitions            │
│  • Tracks lineage and dependencies      │
│  • Collects usage and quality metadata  │
└─────────────────────────────────────────┘
                    ↓
┌─────────────────────────────────────────┐
│  Data Source Integrations               │
├─────────────────────────────────────────┤
│  • dbt project files                    │
│  • LookML models                        │
│  • YAML metric specs                    │
│  • SQL queries                          │
└─────────────────────────────────────────┘

Quick Start

Installation

# Clone the repository
git clone https://github.com/jkelleman/semantic-metrics-modeling-assistant.git
cd semantic-metrics-modeling-assistant

# Install dependencies
pip install -e .

# Run the MCP server
python -m semantic_metrics.server

# Or run tests
pytest tests/

Database Setup

The agent automatically creates a SQLite database (metrics.db) on first run with:

• Metrics definitions table

• Change history tracking

• Validation test results

• Usage statistics

• Trust score evolution

Usage Examples

Define a new metric:

define_metric(
    name="Active Users",
    description="Daily unique user logins",
    calculation="COUNT(DISTINCT user_id) WHERE login_date = CURRENT_DATE",
    owner="@data-team",
    tags=["engagement", "daily"]
)

Check enhanced trust score:

check_trust_score("Active Users")

# Returns:
# Trust Score: 87/100 ↗️
# Tests: 4 passing (35/35 points) ✅
# Usage: 12 dashboards, 8 users (18/20 points)
# Freshness: Updated 2 hours ago (14/15 points)
# Documentation: Complete with examples (15/15 points)
# Ownership: @data-team (15/15 points)
#
# Trend: ▂▄▆█ (improving over 30 days)
# Recommendations: Add integration tests for edge cases

Visualize lineage:

visualize_lineage("Revenue per Customer")

# Returns ASCII tree:
# Revenue per Customer
#   ├── Total Revenue
#   │   ├── Order Amount (raw.orders)
#   │   └── Refunds (raw.refunds)
#   └── Customer Count
#       └── Unique Customers (raw.users)

Generate Mermaid diagram:

generate_mermaid_diagram("Revenue per Customer")

# Returns:
# ```mermaid
# flowchart TD
#     A[Revenue per Customer] --> B[Total Revenue]
#     A --> C[Customer Count]
#     B --> D[Order Amount]
#     B --> E[Refunds]
#     C --> F[Unique Customers]
# ```

Export to BI tools:

# Export to Looker
export_to_looker("Active Users")
# Generates: active_users.lkml

# Export to Tableau
export_to_tableau("Active Users")
# Generates: active_users.tds (XML)

# Export to dbt
export_to_dbt("Active Users")
# Generates: active_users.yml

Design Principles

1. Conversational First

Abstract complex YAML configurations through natural language interfaces. Reduce the learning curve by prioritizing conversational interaction over syntax memorization.

2. Show, Don't Tell

Prioritize visual representations over text descriptions. Render lineage as directed acyclic graphs (DAGs) to make dependency relationships immediately parseable.

3. Trust Through Transparency

Expose quality indicators as first-class attributes. Multi-dimensional trust scores provide actionable signals about metric reliability.

4. Progressive Disclosure

Implement information hierarchy that surfaces high-level summaries by default while maintaining drill-down access to detailed metadata.

5. Governance by Default

Design interfaces that make governance the path of least resistance. Use prompts, validation, and required fields to enforce best practices without adding friction.

Use Cases

Data Team Member

"I need to create a metric for customer lifetime value that everyone can trust."

Assistant helps:

• Define the metric in plain language

• Validate SQL logic

• Check for similar existing metrics

• Set up ownership and documentation

• Add to metric catalog

Analytics Engineer

"Why is my dashboard showing different revenue numbers than finance?"

Assistant helps:

• Compare revenue metric definitions

• Show lineage and data sources

• Identify where definitions diverge

• Recommend canonical metric

Data Leader

"Which metrics are most critical and need better governance?"

Assistant helps:

• Show metrics by usage and trust score

• Identify high-usage, low-trust metrics

• Track governance coverage

• Monitor metric health over time

Technical Stack

• Python 3.10+ - Core language

• FastMCP - MCP protocol implementation

• SQLite - Persistent database storage

• pytest - Test framework with 35+ tests

• YAML/JSON - Metric definitions and exports

• Mermaid - Modern diagram generation

MCP Tools (13 Total)

Metric Management

Quality & Trust

Visualization

Export & Integration

Advanced

What This Demonstrates

UX Skills

• Cognitive Load Reduction - Designing abstraction layers that simplify complex systems without sacrificing power

• Trust Design - Building confidence through transparent quality signals and sophisticated weighted scoring algorithms

• Progressive Disclosure - Information architecture that balances discoverability with detail

• Conversational UI - Natural language interfaces for technical configuration

Technical Skills

• MCP Development - Production-grade AI agent with 13 tools and 1,500+ lines of code

• Database Design - SQLite persistence with 5-table schema and full audit trails

• Data Modeling - Semantic layer design patterns and metric definition frameworks

• System Architecture - Governance automation and observability instrumentation

• Testing - 35+ tests with pytest, 90%+ coverage on core functionality

• Integration Patterns - BI tool exporters for Looker, Tableau, and dbt

Domain Expertise

• Metrics Governance - Establishing ownership models, validation frameworks, and documentation standards

• Data Lineage - Dependency graph construction and impact analysis

• Data Observability - Freshness tracking, quality monitoring with time-decay, and usage analytics

• Semantic Layers - Modern data stack patterns and metric modeling best practices

Why This Project Matters

As a Principal Content Designer at Microsoft working with data and AI systems, this project showcases:

1. Deep understanding of data team challenges - Direct experience with metric proliferation and trust deficits in production environments

2. UX for technical users - Designing abstraction layers that preserve system power while reducing cognitive overhead

3. Design for trust and observability - Applying enterprise-grade governance patterns through transparent quality instrumentation with weighted scoring

4. AI-augmented workflows - Leveraging MCP to enhance (not replace) human decision-making and domain expertise

5. Production-ready implementation - 35+ tests, database persistence, and BI integrations ready for real-world deployment

This represents the kind of human-centered design that enterprise data platforms need: governance that's frictionless, trust that's measurable, and complexity that's manageable.

Recent Enhancements

✅ SQLite Database - Full persistence with 5-table schema
✅ BI Integrations - Looker and Tableau export tools
✅ Enhanced Trust Scoring - Weighted algorithm with time-decay and trends
✅ Mermaid Diagrams - Modern visualizations for documentation
✅ Test Suite - 35+ tests with pytest and 90%+ coverage

See IMPLEMENTATION_SUMMARY.md for complete technical details.

About

Jen Kelleman
Staff Product Designer

I design AI and data experiences that reduce cognitive load and build trust through transparent, well-instrumented systems.

Connect

• LinkedIn

• Medium

• AI Content Design Handbook

Other Projects

• MCP-Oreilly - Three production MCP agents for content design, meeting analysis, and documentation

• AI Content Design Handbook - Comprehensive guide to UX writing for AI systems

Making data governance human-centered, one metric at a time.