aidp-migrate-catalog — Unity Catalog / HMS → AIDP DDL migration

The notebook migration (Pass-2) reads tables. If the schemas + tables don't exist on AIDP first, every read fails. This skill ports the metadata layer.

When to use

• BEFORE aidp-migrate-job for the first time on a new target environment.
• After the user adds new schemas/tables to the source Databricks workspace.
• After a aidp-check-data run reports many MISSING entries.

Two-stage pipeline

┌─────────────────────────────────┐    ┌─────────────────────────────────┐
│ Stage 1: extract_catalog_       │    │ Stage 2: migrate_catalog.py     │
│           databricks.py         │ → │  reads catalog_pack.json,        │
│  REST against Unity Catalog API │    │  applies 18 DDL rewrite rules,  │
│  reports/catalog_pack.json      │    │  batches into ONE WS execute    │
│                                 │    │  on the AIDP cluster            │
└─────────────────────────────────┘    └─────────────────────────────────┘

Stage 1 — extract from Databricks

python3 ${CLAUDE_PLUGIN_ROOT}/engine/scripts/extract_catalog_databricks.py \
  --catalogs "<catalog_a>,<catalog_b>" \
  --schemas-only "<catalog_a>:<schema_1>,<catalog_a>:<schema_2>" \
  --out reports/catalog_pack.json

Env required:

• DATABRICKS_HOST — https://<workspace>.cloud.databricks.com
• DATABRICKS_TOKEN — PAT with workspace + catalog read.

The extractor uses Unity Catalog's REST API (/api/2.1/unity-catalog/tables with include_delta_metadata=true) and includes exponential backoff for rate limits. Expect 1-5 min per catalog depending on table count.

Pack JSON shape

{
  "catalogs": [
    {
      "name": "<source_catalog>",
      "schemas": [
        {
          "name": "<schema>",
          "tables": [
            {
              "name": "<table>",
              "table_type": "MANAGED",
              "data_source_format": "DELTA",
              "storage_location": "s3://<bucket>/<path>",
              "columns": [{"name": "id", "type_text": "long", "nullable": true}, ...],
              "properties": {"delta.minReaderVersion": "2", ...},
              "comment": null,
              "partition_columns": ["date"]
            }
          ]
        }
      ]
    }
  ]
}

Stage 2 — rewrite + replay on AIDP

python3 ${CLAUDE_PLUGIN_ROOT}/engine/scripts/migrate_catalog.py \
  --pack reports/catalog_pack.json \
  --cluster <CLUSTER_ID> \
  --aidp-base <AIDP_BASE> \
  --datalake-ocid <DATALAKE_OCID> \
  --workspace-id <WORKSPACE_UUID> \
  --output-base <output-workspace-path> \
  --oci-profile <profile>

Useful flags:

What the 18 DDL rewrite rules do

Full details in references/ddl-rewrite-rules.md. Quick summary:

The batched-DDL workaround (important)

AIDP's Spark WS execute has a quirk: per-statement DDL is discarded if the session closes between statements. Workaround: batch all CREATE SCHEMA + CREATE TABLE (in dependency order) into ONE WS execute call. migrate_catalog.py does this automatically via --chunk-size.

This means if a single statement in a chunk fails, the WHOLE chunk's reported status may say success even though some tables aren't created. The migrator therefore probes existence post-execute for every statement and reports per-statement results.

Verify after running

After the migration, verify schemas + tables actually landed via aidp-check-data. Specifically:

spark.sql("SHOW SCHEMAS IN default").show(100, False)
spark.sql("SHOW TABLES IN default.<schema>").show(100, False)
spark.sql("DESCRIBE TABLE default.<schema>.<table>").show(100, False)

DESCRIBE TABLE confirms (a) the table exists and (b) the column types are what you expect.

What this skill does NOT migrate

• Row-level data. This is metadata only. To get rows into AIDP, use a separate copy (CTAS from external location, or oci os copy).
• Permissions / grants. UC grants don't map to AIDP's permission model. Re-apply manually.
• Stored procedures / user-defined functions. UC functions / SQL UDFs need separate porting.
• Views. Simple views work via the same rewriter. Complex views with cross-catalog refs need manual review.
• Materialized views / streaming tables. Rejected by the rewriter — flagged in the output for human re-implementation.

After this

• aidp-bucket-mapping if any tables still have s3:// locations the bucket-map didn't cover.
• aidp-check-data to verify everything landed.
• Then aidp-migrate-job for the notebook layer.