MetaPilot

An AI Data Engineering Copilot that uses DataHub metadata, lineage, RAG, and MCP tools to analyze data impact and generate production-ready dbt, Airflow, and SQL assets.

Comment

MetaPilot — AI Engineering Agent for Metadata Intelligence

Inspiration

Modern data teams work with hundreds or thousands of tables, schemas, pipelines, and dependencies. When a schema changes or a query fails, engineers often have to manually search through metadata, inspect schemas, trace lineage, identify downstream dependencies, and then write or modify SQL, dbt models, or Airflow pipelines.

We wanted to explore a simple question:

What if an AI agent could understand the data platform itself and help engineers act on that knowledge?

That idea led us to build MetaPilot, an AI Data Engineering Copilot powered by DataHub metadata, RAG, lineage analysis, and Model Context Protocol (MCP).

Instead of treating an LLM as a generic chatbot, MetaPilot grounds its responses in real metadata retrieved from DataHub. This allows the agent to reason about actual schemas, columns, and dependencies before generating engineering solutions.

What it does

MetaPilot provides an AI-powered workspace for data engineers that connects metadata discovery with engineering automation.

🔎 Metadata Discovery

Engineers can search and explore DataHub assets, schemas, columns, data types, platforms, and other metadata through natural language.

🌿 Lineage & Impact Analysis

MetaPilot can analyze upstream and downstream lineage to understand how changes to a table or column may affect dependent assets.

For example, when a schema changes, MetaPilot can identify downstream dependencies and highlight potential impact and schema-drift risks.

🧠 Metadata-Grounded RAG

MetaPilot retrieves relevant metadata using semantic search and provides it as context to the LLM before generating a response.

This reduces the risk of the AI inventing schemas, columns, or relationships that do not exist in the actual data environment.

⚙️ Engineering Automation

MetaPilot can generate useful engineering artifacts including:

  • SQL queries
  • SQL validation and explanations
  • dbt models
  • Airflow DAGs
  • dbt data-quality tests
  • Database index recommendations

🔌 MCP Tool Suite

MetaPilot exposes structured engineering capabilities through Model Context Protocol (MCP), including tools for:

  • Schema discovery
  • Asset search
  • Lineage retrieval
  • Impact analysis
  • SQL validation
  • SQL explanation
  • dbt generation
  • Airflow generation
  • Data-quality test generation
  • Index recommendations

This allows the AI agent to use specialized tools instead of relying only on free-form text generation.

How we built it

MetaPilot is built as a full-stack AI system with separate frontend, backend, AI, metadata, and infrastructure layers.

Frontend

The frontend is built using:

  • React
  • TypeScript
  • Vite
  • Tailwind CSS
  • Framer Motion

It provides the interactive AI engineering workspace, metadata exploration, lineage visualization, and generated engineering artifacts.

Backend

The backend is implemented using:

  • Python
  • FastAPI
  • SQLAlchemy
  • Pydantic
  • Uvicorn
  • Alembic

The backend handles authentication, AI conversations, metadata operations, lineage analysis, automation generation, and persistence.

Data & Infrastructure

We use:

  • DataHub as the primary metadata and lineage source
  • PostgreSQL for persistent application data and sessions
  • Redis for caching and rate limiting
  • ChromaDB for vector retrieval
  • SentenceTransformers for generating embeddings
  • Docker for service orchestration

AI Pipeline

The core pipeline follows:

User Query → Intent Recognition → Metadata Retrieval → Context Assembly → LLM → Validation → Engineering Artifact

Relevant DataHub metadata is embedded and stored in the vector database. When an engineer asks a question, MetaPilot retrieves the most relevant metadata and uses it as grounding context for the AI.

Gemini is used as the primary LLM provider, with the architecture supporting alternative model providers.

MCP Architecture

We implemented an MCP adapter that exposes data-engineering operations as structured tools.

This creates a separation between AI reasoning and deterministic engineering operations, making the agent more reliable and extensible.

Challenges We Ran Into

1. Grounding AI in real metadata

One of our biggest challenges was preventing the LLM from generating technically correct-looking answers that did not match the actual database environment.

We addressed this by building a metadata-grounded RAG pipeline and validating generated information against retrieved schemas.

2. Understanding lineage

Impact analysis requires more than retrieving a single table. We needed to understand relationships between upstream and downstream assets and provide the relevant dependency context to the AI.

3. Combining multiple AI capabilities

MetaPilot combines retrieval, lineage analysis, code generation, SQL validation, and engineering automation.

Designing these capabilities as structured MCP tools required careful separation between AI reasoning and deterministic operations.

4. Performance

Metadata searches and AI generation can introduce latency. We introduced Redis caching to reduce repeated metadata operations and improve application responsiveness.

5. Building an enterprise-style system during a hackathon

We wanted MetaPilot to demonstrate more than a simple chatbot prototype, so we incorporated authentication, RBAC, persistent sessions, caching, database migrations, security controls, Docker orchestration, and fallback mechanisms while working within the hackathon timeline.

Accomplishments We're Proud Of

We are proud that MetaPilot evolved beyond a simple AI chatbot into a complete AI Data Engineering Agent architecture.

🧠 Metadata-Grounded AI

The AI is grounded in real DataHub metadata and lineage information instead of operating purely on general model knowledge.

🔌 MCP-Native Engineering Tools

We built a dedicated MCP tool suite covering schema discovery, lineage, impact analysis, SQL validation, dbt generation, Airflow generation, testing, and index recommendations.

🌿 Lineage-Aware Impact Analysis

MetaPilot can reason about downstream dependencies and identify potential risks caused by schema changes.

⚙️ From Natural Language to Engineering Artifacts

A natural-language request can be transformed into practical engineering outputs such as SQL, dbt models, Airflow DAGs, and data-quality tests.

🏗️ Production-Oriented Architecture

The project uses a multi-service architecture involving FastAPI, PostgreSQL, Redis, ChromaDB, DataHub, Docker, authentication, RBAC, caching, and security controls.

Most importantly, we are proud of the core idea:

Metadata should not just describe a data platform — it should enable an AI agent to intelligently act on it.

What We Learned

Building MetaPilot taught us that building a useful AI agent is not simply about connecting an LLM to an application.

The quality of an agent depends heavily on its context, retrieval strategy, tools, validation, and system architecture.

We learned that:

  1. Grounding matters more than generation. An LLM can generate impressive-looking SQL, but grounding it in real metadata makes the output significantly more useful.

  2. Tools make agents more reliable. MCP allowed us to separate deterministic operations such as metadata retrieval and lineage analysis from probabilistic LLM reasoning.

  3. Metadata is powerful AI context. Schemas, columns, lineage, and relationships provide valuable information that an AI agent can use to make better engineering decisions.

  4. Caching matters in AI systems. Redis helped reduce repeated metadata lookups and improve responsiveness.

  5. Enterprise AI requires more than an LLM. Authentication, authorization, persistence, security, fallbacks, and failure handling are important parts of building an engineering-focused AI system.

  6. Real workflows create better AI products. The most useful AI capabilities are built around repetitive tasks engineers already perform.

What's Next for MetaPilot

MetaPilot is designed as a foundation for a broader AI-native data engineering platform.

🔄 Bi-Directional DataHub Integration

We want MetaPilot to eventually move beyond reading metadata and safely propose or execute approved catalog updates.

🤖 Autonomous Engineering Workflows

The next stage is moving from individual artifact generation to multi-step workflows such as:

Detect schema change → Analyze lineage → Identify impacted assets → Generate fixes → Validate → Create reviewable changes

📊 Better Agent Observability

We plan to add deeper monitoring for:

  • AI latency
  • Token usage
  • Retrieval quality
  • Tool calls
  • Failure rates
  • Response quality

🧪 Automated Data-Quality Remediation

Future versions could detect failing data-quality checks, investigate the relevant lineage, identify possible root causes, and generate remediation proposals.

🔐 Enterprise Governance

We plan to introduce stronger workspace isolation, fine-grained permissions, approval workflows, audit trails, and policy-aware AI actions.

🌐 More Data Platforms

MetaPilot could eventually integrate with additional metadata catalogs, data warehouses, orchestration systems, and engineering platforms.

Our long-term vision is to build an AI engineering layer on top of the modern data stack, helping data teams move from manually discovering metadata to intelligently acting on it.

Built With

Share this project:

Updates