Migrating Wide World Importers to Databricks with DeepMig

A comprehensive tutorial for using the DeepMig AI agent to automate database and data pipeline migrations from on-premises SQL Server to Databricks.

Introduction

Migrating legacy data warehouses and ETL pipelines to modern cloud platforms like Databricks is a complex, time-consuming process that traditionally requires:

Manual analysis of source schemas and dependencies
Hand-coding of transformation scripts
Extensive testing and validation
Deep expertise in both source and target platforms

DeepMig automates this process using AI, reducing migration time from weeks to hours while maintaining data fidelity.

What You'll Learn

In this tutorial, you'll migrate the Wide World Importers (WWI) sample database from SQL Server to Databricks using DeepMig. You'll learn how to:

Prepare migration inputs (config, metadata, dependency graph)
Run the DeepMig agent to generate migration artifacts
Interpret and customize the generated migration plan
Deploy PySpark scripts to Databricks
Validate data quality post-migration

Wide World Importers Overview

Wide World Importers is a Microsoft sample database representing a wholesale novelty goods company. It includes:

OLTP Database: Operational transaction data (customers, orders, inventory)
DW Database: Pre-built data warehouse with fact and dimension tables
ETL Packages: SSIS packages for data movement

This tutorial focuses on migrating the DW database to Databricks using the medallion architecture (Bronze/Silver/Gold).

What is DeepMig?

DeepMig is an AI-powered migration orchestrator that automates the migration of legacy data platforms to modern cloud ecosystems.

Key Features

Feature	Description
Multi-Agent Architecture	Specialized agents for planning, critique, coding, and validation
Technology Agnostic	Supports SQL Server, SSIS, Informatica, SAS, and more
Skill-Based	Extensible skills for different source/target platforms
Fidelity First	Prioritizes exact replication before optimization
Resumable Workflows	Session state management for long-running migrations

Supported Migrations

Source	Target	Status
SQL Server	Databricks	Supported
SSIS	Databricks	Supported
Informatica	Snowflake	Planned
SAS	Databricks	Planned

Architecture Overview

DeepMig uses a multi-agent architecture where specialized sub-agents handle different aspects of the migration.

                    +------------------+
                    |   Main Agent     |
                    |  (Orchestrator)  |
                    +--------+---------+
                             |
         +-------------------+-------------------+
         |         |         |         |
         v         v         v         v
    +---------+ +--------+ +------+ +-----------+
    |Planning | |Critique| | Code | | Validator |
    | Agent   | | Agent  | | Agent| |   Agent   |
    +---------+ +--------+ +------+ +-----------+
         |         |         |         |
         v         v         v         v
    +--------+ +--------+ +-------+ +----------+
    |  Plan  | |Review  | |Scripts| |Validation|
    |   .md  | |Feedback| |  .py  | |  Report  |
    +--------+ +--------+ +-------+ +----------+

Agent Responsibilities

Agent	Role	Input	Output
Main Agent	Orchestrates workflow, loads skills	User commands	Delegates to sub-agents
Planning Agent	Analyzes dependencies, creates wave plan	Config, metadata, graph	`migration_plan.md`
Critique Agent	Reviews plan for completeness	Migration plan	Approval score (1-10)
Code Agent	Generates PySpark/SQL scripts	Approved plan	`scripts/wave{N}/*.py`
Validator Agent	Tests scripts, compares row counts	Scripts, config	Validation report

Workflow Stages

[Planning] --> [Critique] --> [Coding] --> [Validation] --> [Complete]
     |              |
     +-- Revise <---+  (if score < 8/10)

Prerequisites

Software Requirements

Software	Version	Purpose
Python	3.11+	Runtime for DeepMig
uv	Latest	Python package manager (recommended)
Git	Latest	Clone repository

Cloud Resources

Resource	Purpose
Azure Databricks Workspace	Target platform
Databricks Personal Access Token	API authentication
SQL Server (source)	Source database (optional for this tutorial)

Environment Variables

Create a .env file with your credentials:

# LLM Provider (choose one)
AZURE_OPENAI_ENDPOINT=https://your-resource.openai.azure.com/
AZURE_OPENAI_API_KEY=your-key-here
AZURE_OPENAI_API_VERSION=2024-08-01-preview
AZURE_OPENAI_DEPLOYMENT=gpt-4o

# Or OpenAI
# OPENAI_API_KEY=sk-your-key-here

# Databricks (for validation/deployment)
DATABRICKS_HOST=https://your-workspace.azuredatabricks.net
DATABRICKS_TOKEN=dapi-your-token
DATABRICKS_CLUSTER_ID=your-cluster-id
DATABRICKS_WAREHOUSE_ID=your-warehouse-id

# SQL Server (optional - for live data extraction)
# SQL_SERVER_HOST=your-server.database.windows.net
# SQL_SERVER_USER=your-username
# SQL_SERVER_PASSWORD=your-password
# SQL_SERVER_DATABASE_OLTP=WideWorldImportersDW

Tutorial Overview

This tutorial walks through migrating the WWI data warehouse using pre-prepared inputs:

Tutorial Steps:
1. Install DeepMig
2. Prepare Input Files (config, metadata, graph)
3. Configure the Migration
4. Run the Migration Agent
5. Review Generated Artifacts
6. Deploy to Databricks
7. Validate the Migration

Time Estimate: 30-60 minutes (depending on LLM response times)

Step 1: Install DeepMig

Option A: Clone and Install (Recommended)

# Clone the repository
git clone https://github.com/ai4data/deepmig.git
cd deepmig

# Create virtual environment and install
uv venv
uv pip install -e .

# Activate the environment
source .venv/bin/activate  # Linux/macOS/WSL
# .venv\Scripts\activate   # Windows PowerShell

# Copy and configure environment
cp .env.example .env
# Edit .env with your API keys

Option B: Direct from GitHub

pip install "git+https://github.com/ai4data/deepmig.git"

Verify Installation

deepmig --help

Expected output:

usage: deepmig [-h] [--agent AGENT] [--auto-approve] {list,help,reset} ...

DeepMig - AI-powered ETL Migration Agent

Step 2: Prepare Input Files

DeepMig requires three types of input files to understand your migration:

~/.deepagents/{agent-name}/input/
├── config/
│   └── migration_config.json    # Migration configuration
├── metadata/
│   └── source_schema.json       # Schema metadata
└── graph/
    └── graph.json               # Dependency graph

2.1 Understanding the Input Hierarchy

Input Type	Purpose	Priority
Config	Source/target platforms, credentials, scope	Highest
Metadata	Schema definitions, column types, row counts	High
Graph	Dependencies, lineage, transformation logic	High
Codebase	Raw SQL/SSIS files (fallback only)	Low

2.2 Create Your Agent Directory

# Create the agent directory structure
mkdir -p ~/.deepagents/wwi-migration/input/{config,metadata,graph}

2.3 Copy Sample Input Files

This tutorial includes sample input files. Copy them to your agent directory:

# From this tutorial directory
cp sample-inputs/config/migration_config.json ~/.deepagents/wwi-migration/input/config/
cp sample-inputs/metadata/source_schema.json ~/.deepagents/wwi-migration/input/metadata/
cp sample-inputs/graph/graph.json ~/.deepagents/wwi-migration/input/graph/

Step 3: Configure the Migration

3.1 Migration Configuration

The migration_config.json file defines your migration parameters:

{
  "project": {
    "name": "WWI Migration to Databricks",
    "description": "Migrate Wide World Importers DW to Databricks Lakehouse"
  },
  "source": {
    "type": "sql_server",
    "connection": {
      "host": "${SQL_SERVER_HOST}",
      "database": "WideWorldImportersDW",
      "schema": "Fact"
    }
  },
  "target": {
    "platform": "databricks",
    "catalog": "wwi_migration",
    "schemas": {
      "bronze": "wwi_bronze",
      "silver": "wwi_silver",
      "gold": "wwi_gold"
    }
  },
  "migration": {
    "strategy": "full_refresh",
    "waves": [
      {
        "id": 0,
        "name": "Bronze Ingestion",
        "tables": ["*"]
      }
    ]
  },
  "validation": {
    "row_count_comparison": true,
    "sample_data_check": true,
    "tolerance_percent": 0.01
  }
}

3.2 Key Configuration Options

Option	Description	Example
`source.type`	Source platform identifier	`sql_server`, `ssis`, `informatica`
`target.platform`	Target platform identifier	`databricks`, `snowflake`, `fabric`
`target.catalog`	Unity Catalog name	`wwi_migration`
`target.schemas`	Medallion layer schemas	`{bronze, silver, gold}`
`migration.strategy`	Load strategy	`full_refresh`, `incremental`

Step 4: Run the Migration Agent

4.1 Start DeepMig

# Start with your project name
deepmig --agent wwi-migration

You'll see the DeepMig banner:

  ____  ______ ______ _____  __  __ _____ _____
 |  _ \|  ____|  ____|  __ \|  \/  |_   _/ ____|
 | | | | |__  | |__  | |__) | \  / | | || |  __
 | | | |  __| |  __| |  ___/| |\/| | | || | |_ |
 | |_| | |____| |____| |    | |  | |_| || |__| |
 |____/|______|______|_|    |_|  |_|_____\_____|

Ready to migrate! What would you like to do?
  Working directory: /path/to/your/directory

4.2 Initiate the Migration

Type your migration request:

You: Start the migration for WWI to Databricks

DeepMig will:

Read your configuration file
Load appropriate skills (source-sqlserver, target-databricks)
Analyze the dependency graph
Invoke the Planning Agent

4.3 Review the Migration Plan

The Planning Agent generates a comprehensive migration plan:

Agent: I've created a migration plan. Key highlights:

## Executive Summary
- **Scope**: 12 tables (5 dimensions, 7 facts)
- **Source**: SQL Server WideWorldImportersDW
- **Target**: Databricks wwi_migration catalog
- **Strategy**: Full Refresh with Medallion Architecture

## Wave Plan
| Wave | Tables | Dependencies |
|------|--------|--------------|
| 0 | bronze.* | None |
| 1 | dim_customer, dim_employee | None |
| 2 | dim_city, dim_stock_item | Wave 1 |
| 3 | fact_sale, fact_order | All dims |

The plan has been saved to /memories/migration_plan.md

4.4 Approve or Revise

You: Review the plan and proceed if it looks good

The Critique Agent reviews the plan and scores it:

Agent: Critique completed.

## Plan Review
- **Completeness**: 9/10
- **Accuracy**: 9/10
- **Risk Assessment**: 8/10
- **Overall Score**: 8.7/10

Recommendation: APPROVED - Proceed to code generation.

4.5 Generate Code

You: Generate the PySpark scripts

The Code Agent generates scripts for each wave:

Agent: Generating scripts...

Created:
- /memories/scripts/wave0/ingest_bronze.py
- /memories/scripts/wave1/transform_dim_customer.py
- /memories/scripts/wave1/transform_dim_employee.py
- /memories/scripts/wave2/transform_dim_city.py
- /memories/scripts/wave3/transform_fact_sale.py
...

Step 5: Review Generated Artifacts

5.1 Output Directory Structure

After running DeepMig, your agent directory contains:

~/.deepagents/wwi-migration/
├── input/                      # Your input files
│   ├── config/
│   ├── metadata/
│   └── graph/
├── migration_plan.md           # Generated plan
├── scripts/                    # Generated PySpark scripts
│   ├── wave0/
│   │   └── ingest_bronze.py
│   ├── wave1/
│   │   ├── transform_dim_customer.py
│   │   └── transform_dim_employee.py
│   └── wave3/
│       └── transform_fact_sale.py
├── logs/                       # Validation logs
└── workflow_state.json         # Session state

5.2 Example Generated Script

Here's an example of a generated Bronze ingestion script:

# wave0/ingest_bronze.py
# Generated by DeepMig - Bronze Layer Ingestion
# Source: SQL Server WideWorldImportersDW
# Target: Databricks wwi_migration.wwi_bronze

from pyspark.sql import SparkSession
from pyspark.sql.functions import current_timestamp, lit

spark = SparkSession.builder.getOrCreate()

# JDBC connection properties
jdbc_url = "jdbc:sqlserver://${SQL_SERVER_HOST};database=WideWorldImportersDW"
connection_properties = {
    "user": "${SQL_SERVER_USER}",
    "password": "${SQL_SERVER_PASSWORD}",
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

# Tables to ingest
tables = [
    "Fact.Sale",
    "Fact.Order",
    "Dimension.Customer",
    "Dimension.City",
    "Dimension.Employee",
    "Dimension.StockItem"
]

for table in tables:
    # Read from source
    df = spark.read.jdbc(
        url=jdbc_url,
        table=table,
        properties=connection_properties
    )

    # Add metadata columns
    df = df.withColumn("_ingested_at", current_timestamp())
    df = df.withColumn("_source_table", lit(table))

    # Write to Bronze layer
    target_table = f"wwi_migration.wwi_bronze.{table.replace('.', '_').lower()}"
    df.write.mode("overwrite").format("delta").saveAsTable(target_table)

    print(f"Ingested {table} -> {target_table}")

5.3 Review the Migration Plan

Open ~/.deepagents/wwi-migration/migration_plan.md to see the full plan:

# WWI Migration Plan

## Executive Summary
- **Scope**: 12 tables
- **Source**: SQL Server WideWorldImportersDW
- **Target**: Databricks wwi_migration
- **Philosophy**: Fidelity-First (Lift & Shift)

## Wave 0: Bronze Ingestion
| Table | Source | Target | Row Count |
|-------|--------|--------|-----------|
| Fact.Sale | Fact.Sale | wwi_bronze.fact_sale | 228,265 |
| Dimension.Customer | Dimension.Customer | wwi_bronze.dim_customer | 403 |
...

## Wave 1: Independent Dimensions
...

## Type Mappings Applied
| Source Type | Target Type | Notes |
|-------------|-------------|-------|
| MONEY | DECIMAL(19,4) | Preserve precision |
| DATETIME | TIMESTAMP | Standard mapping |
| NVARCHAR | STRING | UTF-8 encoding |

Step 6: Deploy to Databricks

6.1 Upload Scripts to DBFS

# Using Databricks CLI
databricks fs cp -r ~/.deepagents/wwi-migration/scripts/ dbfs:/deepmig/wwi-migration/scripts/

Or use DeepMig's built-in skill:

You: Upload the scripts to Databricks

6.2 Create and Run Jobs

Option A: Using DeepMig Validator Agent:

You: Execute wave 0 scripts on Databricks

Option B: Manual Databricks Jobs API:

# Submit via REST API
curl -X POST "${DATABRICKS_HOST}/api/2.1/jobs/runs/submit" \
  -H "Authorization: Bearer ${DATABRICKS_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{
    "run_name": "WWI Bronze Ingestion",
    "existing_cluster_id": "'${DATABRICKS_CLUSTER_ID}'",
    "spark_python_task": {
      "python_file": "dbfs:/deepmig/wwi-migration/scripts/wave0/ingest_bronze.py"
    }
  }'

6.3 Monitor Job Execution

You: Check the status of the running jobs

DeepMig will report:

Agent: Job Status:
- wave0/ingest_bronze.py: COMPLETED (2m 34s)
- Tables created: 12
- Total rows ingested: 1,234,567

Step 7: Validate the Migration

7.1 Run Data Validation

You: Validate the migration results

DeepMig's Validator Agent performs:

Row count comparison (source vs target)
Sample data checks
Schema validation

7.2 Validation Report

Agent: Validation Results

## Row Count Comparison
| Table | Source | Target | Match |
|-------|--------|--------|-------|
| fact_sale | 228,265 | 228,265 | PASS |
| dim_customer | 403 | 403 | PASS |
| dim_city | 116,295 | 116,295 | PASS |
...

## Summary
- Tables validated: 12/12
- Row count matches: 12/12
- Data quality checks: PASSED

Migration validation SUCCESSFUL.

7.3 Manual Validation Queries

Run these queries in Databricks SQL:

-- Check row counts
SELECT 'fact_sale' as table_name, COUNT(*) as row_count
FROM wwi_migration.wwi_bronze.fact_sale
UNION ALL
SELECT 'dim_customer', COUNT(*) FROM wwi_migration.wwi_bronze.dim_customer;

-- Check sample data
SELECT * FROM wwi_migration.wwi_bronze.fact_sale LIMIT 10;

-- Verify schema
DESCRIBE TABLE EXTENDED wwi_migration.wwi_bronze.fact_sale;

Troubleshooting

Common Issues

Issue	Cause	Solution
"Config not found"	Missing config file	Verify path: `/memories/input/config/migration_config.json`
"Skill not loaded"	Invalid source/target type	Check `source.type` and `target.platform` in config
"LLM timeout"	API rate limits	Increase timeout or retry
"JDBC connection failed"	Network/credentials	Verify SQL Server connectivity
"Permission denied"	Databricks ACLs	Check Unity Catalog permissions

Debug Mode

Run DeepMig with verbose output:

deepmig --agent wwi-migration --verbose

Reset Agent State

If you need to start fresh:

deepmig reset --agent wwi-migration

Check Workflow State

View current progress:

cat ~/.deepagents/wwi-migration/workflow_state.json

Next Steps

Extend the Migration

Add Silver Layer: Transform Bronze to Silver with business logic
Add Gold Layer: Create aggregated tables for analytics
Incremental Loads: Configure CDC for ongoing updates
Custom Skills: Create skills for your specific source systems

Production Considerations

Set up Azure Key Vault for secrets management
Configure Databricks Job Clusters for cost optimization
Implement data quality monitoring with Unity Catalog
Create CI/CD pipeline for script deployment
Set up alerting for job failures

Resources

License

This tutorial is provided under the MIT License. See the main DeepMig repository for full license terms.

Happy Migrating!

Name		Name	Last commit message	Last commit date
sample-inputs		sample-inputs
README.md		README.md

Latest commit

History

Folders and files

Repository files navigation

Migrating Wide World Importers to Databricks with DeepMig

Table of Contents

Introduction

What You'll Learn

Wide World Importers Overview

What is DeepMig?

Key Features

Supported Migrations

Architecture Overview

Agent Responsibilities

Workflow Stages

Prerequisites

Software Requirements

Cloud Resources

Environment Variables

Tutorial Overview

Step 1: Install DeepMig

Option A: Clone and Install (Recommended)

Option B: Direct from GitHub

Verify Installation

Step 2: Prepare Input Files

2.1 Understanding the Input Hierarchy

2.2 Create Your Agent Directory

2.3 Copy Sample Input Files

Step 3: Configure the Migration

3.1 Migration Configuration

3.2 Key Configuration Options

Step 4: Run the Migration Agent

4.1 Start DeepMig

4.2 Initiate the Migration

4.3 Review the Migration Plan

4.4 Approve or Revise

4.5 Generate Code

Step 5: Review Generated Artifacts

5.1 Output Directory Structure

5.2 Example Generated Script

5.3 Review the Migration Plan

Step 6: Deploy to Databricks

6.1 Upload Scripts to DBFS

6.2 Create and Run Jobs

6.3 Monitor Job Execution

Step 7: Validate the Migration

7.1 Run Data Validation

7.2 Validation Report

7.3 Manual Validation Queries

Troubleshooting

Common Issues

Debug Mode

Reset Agent State

Check Workflow State

Next Steps

Extend the Migration

Production Considerations

Resources

License

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors