AI-Assisted Modeling — Realistic Metadata Copilots (Without the Hype)

The Problem

AI-Assisted Modeling — Realistic Metadata Copilots (Without the Hype)

The Problem

Every few months, someone claims:

“AI will generate your entire data platform.”

It won’t.

Large language models do not understand your architecture.
They do not understand lineage.
They do not understand semantic stability.

They generate text.
Text is not structure.

And SQL is not just text.

The MDDE Perspective

AI becomes interesting only when it operates on structured metadata.

Once SQL has been parsed into:

entity
attribute
relationship
attribute_mapping
function_def
filter_def
entity_window
optimizer_diagnostics

The system is no longer guessing.
It is operating on a model.

That changes everything.

What AI Should Not Do

AI should not:

Generate 500-line SQL from vague prompts
Modify raw SQL directly
Invent joins without lineage validation
Change projection order arbitrarily
Create implicit filters

That is blueprint hallucination.

What AI Can Do (Realistically)

AI becomes powerful in five specific areas.

1️⃣ Metadata Interpretation

Example:

User asks:

“Add customer lifetime value to the customer entity.”

Instead of generating SQL directly, the copilot:

Queries metadata:

SELECT entity_id
FROM metadata.entity
WHERE entity_name = ‘customer’;

2. Finds relationship:

SELECT *  
FROM metadata.relationship  
WHERE source_entity = 'customer'  
  AND target_entity = 'orders';

3. Detects measure candidates:

SELECT attribute_name  
FROM metadata.attribute  
WHERE entity_name = 'orders'  
  AND data_type IN ('numeric', 'decimal');

Now AI proposes:

SUM(orders.amount)
Aggregation grain: per customer
New attribute: customer_lifetime_value
Lineage mapping defined

It generates a proposal, not direct SQL mutation.

2️⃣ Proposal Drafting (Not Enforcement)

AI generates structured proposal:

proposal:  
  type: add_attribute  
  target_entity: customer  
  attribute:  
    name: customer_lifetime_value  
    data_type: DECIMAL(18,2)  
    aggregation:  
      function: SUM  
      source_entity: orders  
      source_attribute: amount

Human reviews.
System validates.

Only then does generation occur.

3️⃣ Refactoring Suggestions

AI can analyze metadata patterns:

Example detection:

Repeated expression: quantity * unit_price
Appears in 7 entities

AI proposes:

Extract reusable function_def
Replace inline expressions with function reference

Because the metadata already tracks function usage and attribute lineage, the suggestion is structural — not textual.

4️⃣ Diagnostic Explanation

One of the most valuable uses:
Explaining diagnostics.

Example:

SELECT *  
FROM metadata.optimizer_diagnostics  
WHERE severity = 'error';

AI can translate:

“Entity sales_fact has ambiguous join between orders and customers because join_condition does not include primary key of customers.”

Instead of engineers reverse-engineering the issue, AI contextualizes it.
This is augmentation, not automation.

5️⃣ Test & Compliance Assistance

AI can propose:

Missing primary keys
Potential index candidates
Weak ORDER BY usage
Deterministic window partitioning

But validation remains metadata-driven.

AI suggests.
Metadata verifies.
CI enforces.

The Critical Architecture Boundary

AI must never bypass:

Metadata schema
Optimizer diagnostics
Compliance rules
Deterministic SQL generator

All AI interaction should occur through:

Prompt → Metadata Query → Proposal → Validation → Generation

Not:

Prompt → Raw SQL Rewrite

That distinction protects architectural integrity.

Example: Safe AI Workflow

User:

“Normalize this UNION and extract reusable filters.”

AI:

Queries metadata.entity for union entities
Validates projection match
Detects repeated filter patterns
Drafts filter_def entries
Generates proposal objects

Then:

python -m mdde.scripts.validate_proposal prop_001  
python -m mdde.scripts.apply_proposal prop_001

AI does not apply changes directly.
The engine remains deterministic.

Why This Matters

Most AI discussions ignore system boundaries.

In MDDE:

SQL is execution
Metadata is structure
AI is assistant
Generator is authority

This layered separation prevents:

Drift
Hallucinated joins
Broken lineage
Non-deterministic output

The Long-Term Vision

AI should become:

A metadata explorer
A proposal drafter
A diagnostic interpreter
A refactoring assistant
A documentation generator

Not:

An uncontrolled SQL writer

The moment AI bypasses metadata, architecture dissolves.

Key Takeaways

AI must operate on metadata, not raw SQL.
Proposals must remain separate from enforcement.
Deterministic generation is non-negotiable.
Validation always precedes application.
Copilot ≠ Autopilot.

Next in the Series

Data Vault Migration Strategy — From Ensemble Modeling to Canonical Structures

We’ll explore how metadata enables safe migration from DV patterns to normalized or dimensional targets.

🔧 Technical Appendix

LLM Prompt Design for Metadata-Driven Modeling

This appendix demonstrates how AI can safely operate inside the MDDE architecture boundary.

The key principle:

LLMs never mutate SQL directly.
They operate through metadata queries and structured proposals.

1️⃣ Safe Context Injection

Before prompting an LLM, we do not send raw SQL.

We send structured metadata.

Example context payload:

{  
  "entity": {  
    "entity_name": "customer",  
    "attributes": [  
      {"name": "customer_id", "type": "VARCHAR(50)", "primary_key": true},  
      {"name": "customer_name", "type": "VARCHAR(255)"},  
      {"name": "registration_date", "type": "DATE"}  
    ]  
  },  
  "relationships": [  
    {  
      "source_entity": "customer",  
      "target_entity": "orders",  
      "cardinality": "1:N"  
    }  
  ],  
  "orders_attributes": [  
    {"name": "order_id", "type": "VARCHAR(50)"},  
    {"name": "amount", "type": "DECIMAL(18,2)"},  
    {"name": "order_date", "type": "DATE"}  
  ]  
}

We give the model structure — not text blobs.

2️⃣ Example Prompt: Add Derived Measure

User Request

Add customer lifetime value to the customer entity.

LLM System Prompt (Constrained)

You are assisting in a Metadata-Driven Data Engineering system.

Rules:  
- You may NOT generate raw SQL.  
- You must produce a structured proposal.  
- You must only use entities and attributes present in the metadata context.  
- If insufficient data exists, ask for clarification.

Output format:  
YAML proposal only.

LLM User Prompt

Using the provided metadata context, propose a new derived attribute for the customer entity that represents lifetime value.

Lifetime value should be calculated from related orders.

Return structured YAML only.

Expected LLM Output

proposal:  
  type: add_derived_attribute  
  target_entity: customer  
  attribute:  
    name: customer_lifetime_value  
    data_type: DECIMAL(18,2)  
    aggregation:  
      function: SUM  
      source_entity: orders  
      source_attribute: amount  
    join_path:  
      from: customer  
      to: orders

No SQL was generated.

The system then validates:

SELECT 1  
FROM metadata.relationship  
WHERE source_entity = 'customer'  
  AND target_entity = 'orders';

If valid → proposal can proceed.

3️⃣ Example Prompt: Refactor Repeated Expression

Metadata Detection

Repeated expression found:

quantity * unit_price  
Occurrences: 7 entities

Prompt

Given the repeated expression:

quantity * unit_price

Propose a reusable function definition using MDDE metadata conventions.  
Return YAML only.

Expected Output

proposal:  
  type: create_function  
  function:  
    name: calculate_line_amount  
    parameters:  
      - quantity  
      - unit_price  
    expression: quantity * unit_price

System then:

Creates metadata.function_def
Rewrites attribute mappings
Regenerates SQL deterministically

4️⃣ Example Prompt: Explain Diagnostic

Diagnostic Record

{  
  "entity": "sales_fact",  
  "diagnostic_type": "ambiguous_join",  
  "message": "Join between orders and customers does not reference primary key"  
}

Prompt

Explain this diagnostic in clear engineering terms.  
Suggest corrective action without generating SQL.

Expected Output

The join between orders and customers does not reference the primary key of customers.

This may result in duplicate or inconsistent aggregation results.

Recommended corrective action:  
Ensure the join condition includes customers.customer_id.

Notice:

AI interprets.
It does not modify.

5️⃣ Example Prompt: Compliance Risk Assessment

Context

{  
  "entity_name": "sales_summary",  
  "diagnostics": [  
    {"type": "select_star", "severity": "error"},  
    {"type": "missing_order_by", "severity": "warning"}  
  ]  
}

Prompt

Assess architectural risk based on diagnostics.  
Categorize as low, medium, or high risk.

Expected Output

Risk Level: High

Reason:  
SELECT * usage introduces projection instability and downstream breakage risk.

Recommendation:  
Replace SELECT * with explicit column projections before merging.

Again: interpretive assistance.

6️⃣ Prompt Guardrails (Critical)

Every LLM integration should include:

Hard Constraints:  
- Never invent entities.  
- Never invent attributes.  
- Never fabricate relationships.  
- If uncertain, request metadata query.  
- Output must be machine-parseable.

This prevents hallucinated architecture.

7️⃣ AI + MDDE Execution Pipeline

The safe loop:

User Intent  
   ↓  
Metadata Query  
   ↓  
LLM Proposal  
   ↓  
Schema Validation  
   ↓  
Compliance Check  
   ↓  
Deterministic SQL Generation

AI never bypasses validation.

8️⃣ Anti-Pattern: What Not To Do

Bad prompt:

Rewrite this SQL to optimize it.

Why bad?

No metadata context
No lineage validation
No join validation
No compliance evaluation
Non-deterministic output

This is how drift begins.

9️⃣ Minimal Copilot Architecture

Practical implementation pattern:

def mdde_ai_proposal(user_intent):  
    metadata_context = fetch_metadata_context()  
    prompt = build_constrained_prompt(metadata_context, user_intent)  
    llm_output = call_llm(prompt)  
    validated = validate_against_schema(llm_output)  
    return validated

LLM is not the system.
It is a proposal engine inside a controlled boundary.

🔟 The Real Takeaway

AI becomes powerful only when:

Architecture is explicit
Structure is queryable
Validation is deterministic
Enforcement is optional
Generation is controlled

Without metadata, AI guesses.
With metadata, AI assists.

That is the difference between hype and engineering.