Skip to content

motherduckdb/agent-skills / plugins/motherduck-skills-claude/skills/motherduck-migrate-to-motherduck

motherduck-migrate-to-motherduck

Verified

Plan or implement migrations to MotherDuck with SQL translation, source-to-target validation, cutover, and rollback.

references/MIGRATION_VALIDATION.md

View on GitHub
# Migration Validation Reference

Concrete SQL patterns and a Python orchestrator for validating that a migration to MotherDuck produced correct results. Every query outputs a `pct_variance` column so the user can decide what is acceptable.

## Contents

- Row count comparison
- Side-by-side metric comparison
- Uniqueness check on target
- New / deleted records (EXCEPT queries)
- Changed records tracking (column-level and hash-based)
- Python validation orchestrator (`validate_migration`, `print_report`)
- Investigating non-zero variance

---

## Row Count Comparison

Compare total row counts between source and target with percentage variance.

```sql
WITH counts AS (
    SELECT
        'source' AS side,
        count(*) AS row_count
    FROM "source_db"."main"."orders"
    UNION ALL
    SELECT
        'target' AS side,
        count(*) AS row_count
    FROM "target_db"."main"."orders"
)
SELECT
    MAX(row_count) FILTER (WHERE side = 'source') AS source_rows,
    MAX(row_count) FILTER (WHERE side = 'target') AS target_rows,
    MAX(row_count) FILTER (WHERE side = 'target')
        - MAX(row_count) FILTER (WHERE side = 'source') AS row_diff,
    ROUND(
        100.0
        * (MAX(row_count) FILTER (WHERE side = 'target')
           - MAX(row_count) FILTER (WHERE side = 'source'))
        / NULLIF(MAX(row_count) FILTER (WHERE side = 'source'), 0),
        2
    ) AS pct_variance
FROM counts;
```

---

## Side-by-Side Metric Comparison

Compare key aggregates between source and target. Replace `amount` and `quantity` with your numeric columns.

```sql
WITH source_metrics AS (
    SELECT
        count(*) AS row_count,
        SUM(amount) AS sum_amount,
        AVG(amount) AS avg_amount,
        MIN(amount) AS min_amount,
        MAX(amount) AS max_amount,
        SUM(quantity) AS sum_quantity,
        AVG(quantity) AS avg_quantity
    FROM "source_db"."main"."orders"
),
target_metrics AS (
    SELECT
        count(*) AS row_count,
        SUM(amount) AS sum_amount,
        AVG(amount) AS avg_amount,
        MIN(amount) AS min_amount,
        MAX(amount) AS max_amount,
        SUM(quantity) AS sum_quantity,
        AVG(quantity) AS avg_quantity
    FROM "target_db"."main"."orders"
),
comparisons AS (
    SELECT unnest([
        {'metric': 'row_count',    'source': s.row_count::DOUBLE,    'target': t.row_count::DOUBLE},
        {'metric': 'sum_amount',   'source': s.sum_amount::DOUBLE,   'target': t.sum_amount::DOUBLE},
        {'metric': 'avg_amount',   'source': s.avg_amount::DOUBLE,   'target': t.avg_amount::DOUBLE},
        {'metric': 'min_amount',   'source': s.min_amount::DOUBLE,   'target': t.min_amount::DOUBLE},
        {'metric': 'max_amount',   'source': s.max_amount::DOUBLE,   'target': t.max_amount::DOUBLE},
        {'metric': 'sum_quantity', 'source': s.sum_quantity::DOUBLE, 'target': t.sum_quantity::DOUBLE},
        {'metric': 'avg_quantity', 'source': s.avg_quantity::DOUBLE, 'target': t.avg_quantity::DOUBLE}
    ]) AS r
    FROM source_metrics s, target_metrics t
)
SELECT
    r.metric AS metric_name,
    r.source AS source_value,
    r.target AS target_value,
    ROUND(r.target - r.source, 4) AS abs_diff,
    ROUND(
        100.0 * (r.target - r.source) / NULLIF(r.source, 0), 2
    ) AS pct_variance
FROM comparisons;
```

---

## Uniqueness Check on Target

Verify the migration did not introduce duplicate records. Replace `order_id` with your primary key column.

```sql
SELECT
    order_id,
    count(*) AS duplicate_count
FROM "target_db"."main"."orders"
GROUP BY order_id
HAVING count(*) > 1
ORDER BY duplicate_count DESC;
```

An empty result set means no duplicates exist.

---

## New Records (In Target, Not In Source)

Identify records that appear in the target but not in the source. These may be expected (if the migration included new data) or a problem.

```sql
SELECT order_id
FROM "target_db"."main"."orders"
EXCEPT
SELECT order_id
FROM "source_db"."main"."orders";
```

Count them:

```sql
SELECT count(*) AS new_record_count
FROM (
    SELECT order_id FROM "target_db"."main"."orders"
    EXCEPT
    SELECT order_id FROM "source_db"."main"."orders"
);
```

---

## Deleted Records (In Source, Not In Target)

Identify records that exist in the source but are missing from the target.

```sql
SELECT order_id
FROM "source_db"."main"."orders"
EXCEPT
SELECT order_id
FROM "target_db"."main"."orders";
```

Count them:

```sql
SELECT count(*) AS deleted_record_count
FROM (
    SELECT order_id FROM "source_db"."main"."orders"
    EXCEPT
    SELECT order_id FROM "target_db"."main"."orders"
);
```

---

## Changed Records Tracking

Find records that exist in both source and target but have different values. Replace column names with your own.

### Column-Level Comparison

Use `IS DISTINCT FROM` instead of `<>` to handle NULLs correctly.

```sql
SELECT
    s.order_id,
    s.amount AS source_amount,
    t.amount AS target_amount,
    s.status AS source_status,
    t.status AS target_status
FROM "source_db"."main"."orders" s
JOIN "target_db"."main"."orders" t
    ON s.order_id = t.order_id
WHERE s.amount IS DISTINCT FROM t.amount
   OR s.status IS DISTINCT FROM t.status;
```

### Hash-Based Comparison for Wide Tables

When a table has many columns, compare row hashes instead of listing every column.

```sql
WITH source_hashed AS (
    SELECT
        order_id,
        md5(COLUMNS(* EXCLUDE (order_id))::VARCHAR) AS row_hash
    FROM "source_db"."main"."orders"
),
target_hashed AS (
    SELECT
        order_id,
        md5(COLUMNS(* EXCLUDE (order_id))::VARCHAR) AS row_hash
    FROM "target_db"."main"."orders"
)
SELECT
    COALESCE(s.order_id, t.order_id) AS order_id,
    s.row_hash AS source_hash,
    t.row_hash AS target_hash
FROM source_hashed s
JOIN target_hashed t
    ON s.order_id = t.order_id
WHERE s.row_hash IS DISTINCT FROM t.row_hash;
```

Once you identify changed IDs via hashing, use the column-level comparison query filtered to those IDs to see exactly what changed.

### Performance Note

For large tables, filter both sides to a date range or partition before comparing:

```sql
-- Add a WHERE clause to both source and target CTEs
WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01'
```

---

## Python Validation Orchestrator

Runs all checks and returns a structured report. Uses the DuckDB Python API.

```python
"""
Migration Validation Orchestrator
Runs source-vs-target checks and reports variance.

Install: pip install duckdb
"""

import duckdb


def validate_migration(
    source_conn: duckdb.DuckDBPyConnection,
    target_conn: duckdb.DuckDBPyConnection,
    source_table: str,
    target_table: str,
    key_column: str,
    numeric_columns: list[str],
    variance_threshold_pct: float = 0.0,
) -> dict:
    """
    Run all migration validation checks.

    Args:
        source_conn: Connection to the source database.
        target_conn: Connection to the target database.
        source_table: Fully qualified source table (e.g., '"source_db"."main"."orders"').
        target_table: Fully qualified target table (e.g., '"target_db"."main"."orders"').
        key_column: Primary key column name for record-level comparisons.
        numeric_columns: List of numeric column names for metric comparisons.
        variance_threshold_pct: Acceptable % variance. 0.0 means exact match required.

    Returns:
        Dict with results for each check and an overall pass/fail.
    """
    results = {}

    # --- Row counts ---
    source_count = source_conn.sql(f"SELECT count(*) FROM {source_table}").fetchone()[0]
    target_count = target_conn.sql(f"SELECT count(*) FROM {target_table}").fetchone()[0]
    count_variance = (
        round(100.0 * (target_count - source_count) / source_count, 2)
        if source_count > 0
        else None
    )
    results["row_counts"] = {
        "source": source_count,
        "target": target_count,
        "diff": target_count - source_count,
        "pct_variance": count_variance,
        "pass": abs(count_variance or 0) <= variance_threshold_pct,
    }

    # --- Metric comparison ---
    metrics = {}
    for col in numeric_columns:
        for agg in ["SUM", "AVG", "MIN", "MAX"]:
            source_val = source_conn.sql(
                f"SELECT {agg}({col})::DOUBLE FROM {source_table}"
            ).fetchone()[0]
            target_val = target_conn.sql(
                f"SELECT {agg}({col})::DOUBLE FROM {target_table}"
            ).fetchone()[0]
            pct = (
                round(100.0 * (target_val - source_val) / source_val, 4)
                if source_val
                else None
            )
            metric_key = f"{agg.lower()}_{col}"
            metrics[metric_key] = {
                "source": source_val,
                "target": target_val,
                "pct_variance": pct,
                "pass": abs(pct or 0) <= variance_threshold_pct,
            }
    results["metrics"] = metrics

    # --- Uniqueness ---
    dupes = target_conn.sql(
        f"SELECT {key_column}, count(*) AS cnt FROM {target_table} "
        f"GROUP BY {key_column} HAVING cnt > 1"
    ).fetchall()
    results["uniqueness"] = {
        "duplicate_count": len(dupes),
        "duplicate_keys": [row[0] for row in dupes[:20]],
        "pass": len(dupes) == 0,
    }

    # --- New records (in target, not in source) ---
    new_ids = target_conn.sql(
        f"SELECT {key_column} FROM {target_table} "
        f"EXCEPT SELECT {key_column} FROM {source_table}"
    ).fetchall()
    results["new_records"] = {
        "count": len(new_ids),
        "sample_keys": [row[0] for row in new_ids[:20]],
        "pass": len(new_ids) == 0,
    }

    # --- Deleted records (in source, not in target) ---
    deleted_ids = source_conn.sql(
        f"SELECT {key_column} FROM {source_table} "
        f"EXCEPT SELECT {key_column} FROM {target_table}"
    ).fetchall()
    results["deleted_records"] = {
        "count": len(deleted_ids),
        "sample_keys": [row[0] for row in deleted_ids[:20]],
        "pass": len(deleted_ids) == 0,
    }

    # --- Changed records (hash comparison) ---
    changed = target_conn.sql(f"""
        WITH source_h AS (
            SELECT {key_column},
                   md5(COLUMNS(* EXCLUDE ({key_column}))::VARCHAR) AS rh
            FROM {source_table}
        ),
        target_h AS (
            SELECT {key_column},
                   md5(COLUMNS(* EXCLUDE ({key_column}))::VARCHAR) AS rh
            FROM {target_table}
        )
        SELECT s.{key_column}
        FROM source_h s
        JOIN target_h t ON s.{key_column} = t.{key_column}
        WHERE s.rh IS DISTINCT FROM t.rh
    """).fetchall()
    results["changed_records"] = {
        "count": len(changed),
        "sample_keys": [row[0] for row in changed[:20]],
        "pass": len(changed) == 0,
    }

    # --- Overall ---
    results["overall_pass"] = all(
        v.get("pass", True)
        for v in results.values()
        if isinstance(v, dict) and "pass" in v
    ) and all(
        m.get("pass", True) for m in results.get("metrics", {}).values()
    )

    return results


def print_report(results: dict) -> None:
    """Print a human-readable validation report."""
    print("=== Migration Validation Report ===\n")

    rc = results["row_counts"]
    status = "PASS" if rc["pass"] else "FAIL"
    print(f"Row Counts [{status}]: source={rc['source']}  target={rc['target']}  "
          f"diff={rc['diff']}  variance={rc['pct_variance']}%")

    print("\nMetrics:")
    for name, m in results["metrics"].items():
        status = "PASS" if m["pass"] else "FAIL"
        print(f"  {name} [{status}]: source={m['source']}  target={m['target']}  "
              f"variance={m['pct_variance']}%")

    u = results["uniqueness"]
    status = "PASS" if u["pass"] else "FAIL"
    print(f"\nUniqueness [{status}]: {u['duplicate_count']} duplicate keys found")

    nr = results["new_records"]
    status = "PASS" if nr["pass"] else "FAIL"
    print(f"New Records [{status}]: {nr['count']} records in target not in source")

    dr = results["deleted_records"]
    status = "PASS" if dr["pass"] else "FAIL"
    print(f"Deleted Records [{status}]: {dr['count']} records in source not in target")

    cr = results["changed_records"]
    status = "PASS" if cr["pass"] else "FAIL"
    print(f"Changed Records [{status}]: {cr['count']} records with different values")

    overall = "PASS" if results["overall_pass"] else "FAIL"
    print(f"\n=== Overall: {overall} ===")
```

---

## Investigating Non-Zero Variance

When validation reports non-zero variance, do not treat it as an automatic failure. Investigate in order:

1. **Row count differs?** Check for new or deleted records first. Use the EXCEPT queries above to find exactly which keys are affected. New records may be expected if the migration included a data refresh.

2. **Aggregates differ but row count matches?** Run the column-level comparison to find changed rows. Common causes:
   - floating-point rounding differences between source and DuckDB (usually < 0.01%)
   - timezone handling differences on timestamps that affect date-based aggregations
   - NULL handling differences (`NULL + 5` returns `NULL` in DuckDB, some sources treat it as `5`)

3. **Small variance (< 0.5%)?** Document it and decide with the user whether it is acceptable. Many migrations accept small rounding variance on financial aggregates.

4. **Large variance (> 1%)?** Narrow it down to specific rows. Filter the metric comparison to date ranges, customer segments, or product categories to isolate the affected partition. The usual suspects are:
   - duplicate rows in either source or target
   - a WHERE clause in the migration that excluded records
   - type coercion that changed values (e.g., truncating DECIMAL precision)

5. **Hash comparison finds changed rows?** Use the column-level comparison filtered to those keys to see exactly which columns changed. This is the fastest path to root cause.

---

### Usage Example

```python
import duckdb

# Connect to both databases
USE_CASE_USER_AGENT = "agent-skills/3.2.0(harness-<harness>;llm-<llm>)"
conn = duckdb.connect(f"md:?custom_user_agent={USE_CASE_USER_AGENT}")

# If source is a Postgres database (local DuckDB only):
# conn.sql("ATTACH 'dbname=legacy host=pg.example.com' AS source_db (TYPE POSTGRES, READ_ONLY)")

results = validate_migration(
    source_conn=conn,
    target_conn=conn,
    source_table='"source_db"."main"."orders"',
    target_table='"target_db"."main"."orders"',
    key_column="order_id",
    numeric_columns=["amount", "quantity"],
    variance_threshold_pct=0.5,  # allow 0.5% variance
)

print_report(results)
```
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall