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.
- Author
- sfc-gh-dflippo ↗
- Repository
- snowflake-dbt-demo ↗
- Licence
- Apache-2.0
- Last repository change
- 27.10.2026
Assessment and security
- Files in this package
- 6
- Scripts
- 3
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.
Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.
- 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
- Overview
- Sub-Agent Mode
- Prerequisites
- Inputs (auto-detected by parent)
- Required User Interactions
- Command Reference
- Command-Line Arguments
- Output File
- Algorithm Details
- Filtering Rules
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,…