Skip to content
Skills Library
Data & analytics · Planning

sql-migration-waves-generator

/sql-migration-waves-generator

Analyze SQL object dependencies and create deployment waves/partitions for database migrations. Use when working with SQL migration planning, SnowConvert outputs, or deployment wave creation.

Licence
Apache-2.0
Last repository change
27.10.2026

Assessment and security

95/ 100Class: good
Format20 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
6
Scripts
3
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.

Licence

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

What to check before installing
  • 3 scripts in this package. Review them before installing, as Claude may execute them.

Scripts: scripts/generate_html_report.py, scripts/load_data_html_report.py, scripts/registry_support.py

What it does and when to use it

Analyze SQL object dependencies and create deployment waves/partitions for database migrations. Use when working with SQL migration planning, SnowConvert outputs, or deployment wave creation.

Original author description

Analyze SQL object dependencies and create deployment waves/partitions for database migrations. Use when working with SQL migration planning, SnowConvert outputs, or deployment wave creation.

How it works

  1. Overview
  2. Sub-Agent Mode
  3. Prerequisites
  4. Inputs (auto-detected by parent)
  5. Required User Interactions
  6. Command Reference
  7. Command-Line Arguments
  8. Output File
  9. Algorithm Details
  10. Filtering Rules
Original SKILL.md headings · 282 lines, 1,822 words
SKILL.md preview
---
name: sql-migration-waves-generator
description: Analyze SQL object dependencies and create deployment waves/partitions for database migrations. Use when working with SQL migration planning, SnowConvert outputs, or deployment wave creation.
parent_skill: assessment
license: Proprietary. See License-Skills for complete terms
---

# Migration Waves/Partition Generator

## Overview

This skill produces a deployment wave plan for database/ETL migrations by running the SCAI CLI command `scai assessment waves` from inside a SCAI project. The command builds the dependency graph directly from the project's registry, assigns priority tiers, partitions objects into dependency-ordered waves, and emits a single consolidated JSON file.

**Use this skill when:**
- Planning SQL database and/or ETL migration deployment sequences
- Creating deployment waves that respect object dependencies
- Optimizing migration batch sizes for deployment

## Sub-Agent Mode

When invoked from a parent skill (e.g., `assessment/SKILL.md`) as a sub-agent, the parent provides a context block with the fields below. **Skip the prompts in [Required User Interactions](#required-user-interactions) whenever the corresponding fields are present in the context block** — sub-agents must not block on stdin.

| Field | Required | Notes |
|---|---|---|
| `project_dir` | yes | absolute path to the SCAI project root |
| `partition_min_size` | yes | integer; maps to `--min-size` |
| `partition_max_size` | yes | integer; maps to `--max-size` |
| `prioritization_globs` | yes | list of glob strings; emit one `--prioritize <glob>` per entry; pass nothing if empty |
| `wave_ordering` | yes | `category` (default) or `dependency` (adds `--no-category-waves`) |
| `output_dir` | yes | typically `<project_dir>/assessment` |

**On entry:** call the `configure` MCP tool with `project_dir` from the context block. Snowflake credentials are not required — `scai assessment waves` reads only the local registry.

**Pass every required input as a CLI flag.** If `scai assessment waves` still requests TTY input for any reason, fail fast and report the missing flag — do not block waiting for stdin.

**On completion**, return **JSON only** (no surrounding prose):

```json
{
  "sub_skill": "waves-generator",
  "status": "ok",
  "output_json": "<abs path to waves_analysis_*.json>",
  "summary": "<one-line counts: partitions, sccs, objects>",
  "error": null
}
```

On failure: `"status": "error"`, `"output_json": null`, `"error": "<message>"`. The parent verifies the JSON path exists before continuing.

## Prerequisites

- The working directory must be a SCAI project (i.e. `.scai/` exists and the registry is initialized).
- `scai assessment waves` reads the registry produced by `scai code convert`. The parent `assessment/SKILL.md` ensures the project is bootstrapped before routing here — do **not** prompt the user for paths.

## Inputs (auto-detected by parent)

The parent `assessment` skill has already set `project_dir` and verified the registry exists. This skill does **not** take any input paths — `scai assessment waves` reads the registry from the current project folder.

## Required User Interactions

**These steps are mandatory in inline (standalone) mode.** When invoked as a sub-agent (see [Sub-Agent Mode](#sub-agent-mode)) and the corresponding context-block fields are present, skip the prompts and use the supplied values directly.

### Before Generating Waves

You MUST confirm the following with the user before running `scai assessment waves`:

1. **Partition size** — The default is 40-80 objects per wave. Ask:
   > "The default wave size is 40-80 objects per wave. Would you like to keep these defaults, or set custom min/max limits?"

2. **Object prioritization** — Some objects may need to be deployed first. Ask:
   > "Would you like to prioritize specific objects to appear in the earliest waves? You can use name patterns, for example `"*Payroll*"` to prioritize all Payroll-related objects, or `"dbo.Customer"` for an exact match."

3. **Wave ordering strategy** — Explain the default and offer the alternative. Ask:
   > "By default, waves are grouped by category: TABLEs are deployed first, then VIEWs, then FUNCTIONs/PROCEDUREs, and ETL packages last. This ensures schema foundations exist before the code that depends on them. Alternatively, you can use a pure dependency-based approach that mixes all object types by dependency level. Would you like to keep the default category-based ordering, or switch to dependency-based?"
   >
   > If user chooses dependency-based, add `--no-category-waves` to the command.

### After Generating Waves

4. **Return to parent workflow** — After the waves JSON is written:
   > "Wave generation is complete. Would you like to generate the HTML report now?"
   >
   > If yes, return to the parent skill (`../SKILL.md`) and follow its **Report Generation** section using `generate_multi_report.py` ONLY.
   >
   > If the assessment includes other sub-skills (Object Exclusion, Dynamic SQL, SSIS), continue with the parent workflow sequence.

---

## Command Reference

Run `scai assessment waves` from inside the SCAI project root. The command must **not** be run from elsewhere, and it does not accept a `--project-dir` flag — change to the project directory first if needed.

### Basic Usage

```bash
scai assessment waves
```

**Output**: `<projectRoot>/assessment/waves_analysis_YYYYMMDD_HHMMSS.json` — a single consolidated JSON containing the graph summary, partitions, SCCs, top dependencies, and statistics.

### With Custom Partition Sizes

```bash
scai assessment waves --min-size 15 --max-size 50
```

### With Object Prioritization

Prioritize specific objects or patterns for earlier deployment in Wave 1. The flag is repeatable.

```bash
scai assessment waves \
  --prioritize "*ComputerAsset*" \
  --prioritize "*Worker*" \
  --prioritize "PKG_PAYROLL*"
```

**Supports wildcards:**
- `--prioritize "PKG_*"` — All objects starting with `PKG_`
- `--prioritize "*OrthoContract*"` — All objects containing `OrthoContract`
- `--prioritize "[SCHEMA].[TABLE]"` — Exact object name

### Disable Category-Based Wave Ordering

By default, waves are grouped by category (TABLE → VIEW → FUNCTION → PROCEDURE → ETL). To mix all object types purely by dependency level, use `--no-category-waves`:

```bash
scai assessment waves --no-category-waves
```

## Command-Line Arguments

| Argument | Default | Description |
|----------|---------|-------------|
| `--min-size <N>` | 40 | Minimum target wave size for bin-packing |
| `--max-size <N>` | 80 | Maximum wave size for bin-packing |
| `--prioritize <PATTERN>` | — | Glob pattern for user-prioritized objects (repeatable) |
| `--no-category-waves` | off | Disable category-based waves (TABLE→VIEW→FUNCTION first). Mixes all types by dependency level. |

Run `scai assessment waves -h` for the latest list of flags.

## Output File

`scai assessment waves` writes **one** file:

- `<projectRoot>/assessment/waves_analysis_YYYYMMDD_HHMMSS.json`

The consolidated JSON contains:

| Section | Purpose |
|---------|---------|
| `graph_summary` | Total nodes, edges, weakly connected components, cycle counts |
| `partitions` | Wave composition: partition number, size, type, members, root/leaf flags |
| `sccs` | Strongly connected components (circular dependencies) |
| `excluded_edges` | Edges excluded from partitioning (self-refs, undefined objects, temp tables) |
| `top_dependencies` | Objects with the most dependencies and dependents |
| `scc_priority_order` | Priority tier distribution (User-Prioritized / Regular / ETL) |
| `objects` | Per-object metadata (category, conversion status, assigned partition, root/leaf flags) |
| `statistics` | Per-category counts, size distributions, cycle/missing-dep totals |

The HTML generators (`generate_multi_report.py`, `generate_html_report.py`) read this JSON directly via the `WavesJsonAdapter`.

## Algorithm Details

The algorithm lives in the SCAI implementation of `scai assessment waves`. At a high level:

1. **Build Dependency Graph** — load deployable in-scope objects from the registry and build a directed graph from `dependencies.dependsOn`. Self-references are excluded; undefined references are tracked separately.
2. **Category-Based Wave Ordering (Default)** — TABLEs first, then VIEWs, then FUNCTIONs, with a convergence loop to handle cross-category dependencies (e.g. TABLE → FUNCTION). Disable with `--no-category-waves`.
3. **Priority Classification** —
   - Tier 0 (User-Prioritized): objects matching `--prioritize` patterns (earliest)
   - Tier 1 (Regular): DDL/DML foundations (TABLE, VIEW, PROCEDURE, FUNCTION)
   - Tier 2 (ETL): SSIS packages and ETL objects (latest — they consume the foundations)
4. **Initial Partitioning** — process prioritized objects first with their transitive dependencies; start from roots; iteratively add nodes whose dependencies are all satisfied; create partitions between `--min-size` and `--max-size`; handle cycles via iterative Tarjan SCC.
5. **Partition Merging** — merge partitions smaller than `--min-size` (excluding category waves) with adjacent ones while preserving ordering.
6. **Dependency Validation** — each partition only depends on earlier partitions (enforced by §15 invariants before emitting output).

## Filtering Rules

- **Self-references excluded**: edges where caller equals referenced object
- **CREATE statements only**: only objects with a `CREATE X` definition in the registry
- **Undefined nodes tracked**: edges to/from objects not in the registry are surfaced as missing dependencies
- **Temp tables excluded**: dynamic temp tables (e.g. `#TTableName`) are not tracked as formal objects

## Examples

### Example 1: Standard Migration Analysis

```bash
# From inside the SCAI project root
scai assessment waves
```

**Output**: deployment waves of 40-80 objects each in `<projectRoot>/assessment/waves_analysis_<timestamp>.json`.

### Example 2: Prioritize Critical ETL Processes

```bash
scai assessment waves \
  --prioritize "*ComputerAsset*" \
  --prioritize "*Worker*" \
  --prioritize "PKG_PAYROLL*"
```

**Output**: places all ComputerAsset, Worker, and Payroll-related objects (with their dependencies) in Wave 1.

### Example 3: Smaller Batch Sizes

```bash
scai assessment waves --min-size 10 --max-size 30
```

**Output**: more partitions with 10-30 objects each for incremental deployment.

### Example 4: Dependency-Based Mixing

```bash
scai assessment waves --no-category-waves
```

**Output**: waves mix all object types purely by dependency level (no TABLE→VIEW→FUNCTION ordering).

## Typical Workflow

1. **Bootstrap the project** — `scai init`, connect, register, `scai code convert` (handled by `../setup/SKILL.md`). The registry must exist.

2. **Generate deployment waves**:
   ```bash
   scai assessment waves
   ```
   Re-run with `--min-size`, `--max-size`, `--prioritize`, or `--no-category-waves` as the user requested.

3. **Review the output** — open `<projectRoot>/assessment/waves_analysis_<timestamp>.json`. Inspect the `graph_summary`, `sccs`, `excluded_edges`, and `partitions` sections.

4. **Generate the HTML report** — return to the parent skill and use `generate_multi_report.py --project-dir <projectRoot>`. The script auto-discovers the registry, reports, exclusion JSON, dynamic-SQL JSON, and the latest `waves_analysis_*.json`. Do not write HTML manually.

## Key Concepts

### Root Nodes
Objects with no dependencies (no incoming edges). Safe to deploy first.

### Leaf Nodes
Objects with no dependents (no outgoing edges). Nothing else depends on them.

### Weakly Connected Components
Separate forests/trees in the dependency graph. Each can be deployed independently.

### Strongly Connected Components (SCCs)
Circular dependencies where objects depend on each other. Requires special handling.

### Progressive Disclosure
Partitions are numbered sequentially. Each partition depends only on partitions with lower numbers.

## Troubleshooting

### `scai assessment waves` reports "must run inside a SCAI project"

**Cause**: The current directory is not a SCAI project root (no `.scai/` or registry).

**Solution**: `cd` into the project directory before running, or bootstrap via `../setup/SKILL.md`.

### Many Single-Object Partitions

**Cause**: Graph has many disconnected components or complex dependency patterns.

**Solution**: Adjust `--min-size` / `--max-size` or accept that some objects are isolated.

### Circular Dependencies Detected

**Cause**: Objects have mutual dependencies (A depends on B, B depends on A).

**Solution**: Review the `sccs` section of the JSON to identify problem objects. May require manual intervention or schema refactoring.

### High Excluded Edge Count

**Cause**: Many temp tables or dynamic objects not tracked as formal objects.

**Solution**: Normal for SQL Server migrations. Review the `excluded_edges` section of the JSON for patterns.

## Best Practices

1. **Start with default settings** (40-80 objects) and adjust based on deployment capacity.
2. **Review graph structure** first (`graph_summary`, `sccs`) to understand component count and cycles.
3. **Check excluded edges** to understand temp table usage patterns.
4. **Use prioritization for critical ETL processes** — prioritize business-critical objects (e.g., `--prioritize "*Payroll*" --prioritize "*Customer*"`) to ensure they deploy first.
5. **Review the `scc_priority_order` section** after prioritization to verify expected objects are in Tier 0.
6. **Combine prioritization with custom sizes** for fine-grained control over wave composition.
7. **Deploy partitions sequentially** respecting the partition number order.
Files in this package (6)
  • .skillignore 1 KB
  • SKILL.md 13 KB
  • issues-estimation/issues-estimation.json 235 KB
  • scripts/generate_html_report.py 243 KB
  • scripts/load_data_html_report.py 12 KB
  • scripts/registry_support.py 10 KB

Versions

The database has no other skills named sql-migration-waves-generator.

Database snapshot: 13.11.2026 · package version 4072ee0c7dd2 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 95sql-dynamic-pattern-analyzer
    Analyzes Dynamic SQL occurrences from SnowConvert issues, classifies patterns, scores complexity, and records migration considerations. Use for SQL Server, Redshift, Oracle, or Teradata to Snowflake migrations. Driven entirely by `scai assessment sql-dynamic`; no custom scripts.
  • 100sql-migration-generator
    Generate sql migration generator operations. Auto-activating skill for Backend Development. Triggers on: sql migration generator, sql migration generator Part of the Backend Development skill category. Use when working with sql migration generator functionality. Trigger with…
  • 95azure-database-migration
    Expert knowledge for Azure Database Migration Service development including troubleshooting, decision making, limits & quotas, security, integrations & coding patterns, and deployment. Use when planning Azure DMS migrations for MySQL, PostgreSQL, MongoDB, SSIS-to-Azure SQL, or…
  • 100changewaves
    Manage MSBuild Change Waves: create new waves, condition features behind opt-out flags, write tests for wave-gated features, document change waves in ChangeWaves.md, and retire expired waves. Use when adding changes that need an opt-out or rotating out old change waves. Changes…
  • 84analyze-oracle-schema
    Analyzes Oracle database schema and generates a comprehensive Markdown report for sharing. Use when you need to document Oracle schema objects including tables, triggers, custom types, PL/SQL code, and object dependencies.
  • 95data-storage-migrations
    Use this capability for database schema design, SQL/NoSQL selection, migrations, indexing, query review, connection pooling, backups, object storage, event storage, data retention, and data lifecycle planning. Trigger on database, schema, migration, index, SQL, PostgreSQL,…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.