| name | assessment |
| description | Analyzes workloads to be migrated to Snowflake using SnowConvert assessment reports. Routes to specialized sub-skills for high-quality assessments. Use this skill when user wants to do an assessment of their code or ETL workload, waves generation, object exclusion, sql dynamic and/or ETL analysis (SSIS) |
| version | 0.1.0 |
| license | Proprietary. See License-Skills for complete terms |
Assessment
On Entry
Tell the user:
Migration Assessment — I'll analyze your converted code to generate a migration plan: dependency waves, object categorization, dynamic SQL patterns, and a summary report. This helps us prioritize what to migrate first.
End-to-end migration assessment. The user only needs to point at the source — this skill detects the project state and, if needed, drives the migration setup (connect → init → register → convert) so that the SnowConvert reports the assessment depends on are produced automatically. The user is never asked for CSV paths, registry paths, or output directories.
"I want to assess my workload" → the user provides a source → assessment runs end-to-end. Nothing else is requested.
Step 0: Configure Session
Call the configure MCP tool with project_dir (use the current directory, or ask the user if ambiguous). If snowflake_connection is not set, ask which Snowflake connection to use and call configure again. Other settings are filled in by sub-skills as the workflow progresses.
Step 1: Verify Prerequisites
If you arrived here directly (not through the setup state machine), call migration_status(mode='next_setup_task') first. If it returns anything other than assess, follow the engine's response (run init / register / convert first) and re-enter assessment when the engine routes here.
Step 2: Auto-Detect SnowConvert Outputs
Resolve all inputs from project_dir. Do not prompt the user.
| Input | Resolution |
|---|
| SCAI project root | project_dir (contains .scai/ and the registry — required by scai assessment waves) |
| SnowConvert reports dir | <project_dir>/reports/SnowConvert/ |
| Issues CSV | <project_dir>/reports/SnowConvert/Issues.*.csv (latest timestamp) |
| ETL Elements / Issues CSVs | <project_dir>/reports/SnowConvert/ETL.Elements.*.csv and ETL.Issues.*.csv (only if present — drives whether SSIS analysis is included) |
| Assessment output dir | <project_dir>/assessment/ (created by scai assessment waves; fall back to creating if missing for other sub-skills) |
Selection rules:
- Wave generation is always driven by
scai assessment waves — it reads the registry from the current project folder and writes <project_dir>/assessment/waves_analysis_*.json. There is no CSV fallback for wave creation.
- The multi-tab HTML report (
generate_multi_report.py) takes --project-dir (the scai project root) and auto-discovers everything it needs: registry/, reports/, assessment/waves_analysis_*.json, and the exclusion / dynamic-SQL JSONs. The registry is required — without it the report cannot enrich the waves JSON (which emits UUIDs) with canonical names, categories, file paths, and conversion status.
- If the registry or reports are missing after a successful-looking convert, re-run
../convert/SKILL.md once and stop if it still produces nothing.
Step 3: Confirm Scope (single, short)
Show one compact confirmation that lists what will run. This is the only confirmation between source-question and execution.
I will run:
1. Waves (dependency analysis + deployment partitioning)
2. Object Exclusion
3. Dynamic SQL Patterns
4. ETL/SSIS Assessment (only if present)
5. Informatica Assessment (only if present)
6. HTML Report
Proceed with all, or pick a subset?
Wait for "yes" or a subset selection, then run. Do not re-prompt for files or directories at any later point.
Note: "Proceed with all" is not the last prompt. The next step (Step 4) collects every input the in-scope sub-skills need so they can run as non-interactive sub-agents. After Step 4 the assessment becomes hands-off until results are surfaced in Step 8.
Step 4: Gather Sub-Skill Inputs (single batch)
Collect every answer the in-scope sub-skills need before dispatching anything. Sub-agents run non-interactively. Hold the answers in an in-message assessment_inputs block in your working context (do not write it to disk) — it is the source of truth for the context blocks emitted in Step 5.
For sub-skills excluded by the Step 3 scope answer, skip the corresponding inputs and do not dispatch in Step 5.
5.1 Waves inputs (always required when waves is in scope)
Ask the three Required User Interactions from waves-generator/SKILL.md here, in this order:
- Partition size — "The default wave size is 40-80 objects. Keep the defaults, or set custom min/max?" Record
partition_min_size (int) and partition_max_size (int).
- Prioritization — "Any objects to push into the earliest waves? Provide patterns like
*Payroll* or dbo.Customer, or say no." Record prioritization_globs (list of strings; empty if none).
- Wave ordering — "Default is category-based (TABLE → VIEW → FUNCTIONS/PROCEDURES → ETL). Switch to dependency-based?" Record
wave_ordering (category or dependency).
5.2 Dynamic SQL review (when in scope)
Ask once:
"Are you interested in Dynamic SQL code analysis? Each occurrence will be reviewed individually — pattern, complexity, migration considerations. (yes / no)"
Map the answer to dynamic_sql.review_mode:
yes → auto-review-all — generate, then loop and update each occurrence
no → generate-only — generate the JSON for report visibility only; no per-occurrence review
5.3 ETL/SSIS review (when ETL is detected)
When ETL/SSIS is in scope and ETL.*.csv files are present, always generate the JSON — it gives the user visibility into their packages: component counts, control flow / data flow DAGs, and a baseline migration view in the report. Do not ask about that step.
Beyond the baseline, ask once whether to run the deeper AI package analysis:
"I detected SSIS packages in this project. The assessment will give you visibility into them — component counts, control/data flow DAGs, and a baseline migration view in the report.
Optionally, I can run an AI-driven per-package analysis that adds package classification (Ingestion / Transformation / Export / Orchestration / Hybrid), an AI HTML summary, and effort estimates. It takes more time, but runs in the background as a sub-agent in parallel with the other assessments — it doesn't block anything else. Run the AI analysis? (yes / no)"
Map the answer to etl.review_mode:
yes → auto-review-all — generate, then per-package AI classification + AI HTML summary
no → generate-only — generate the JSON for report visibility only; no AI analysis
Capture etl_replatform_sources_path from migration_status if available (otherwise leave blank for sub-agent auto-detection).
5.4 Object exclusion (no inputs)
No prompts. Note the sub-skill is in scope.
5.5 Snapshot the inputs
Lay out the resolved values in your working context like this (text only — do not write to disk):
assessment_inputs:
project_dir: <abs>
output_dir_assessment: <project_dir>/assessment
waves:
partition_min_size: <int>
partition_max_size: <int>
prioritization_globs: [<glob>, ...]
wave_ordering: category | dependency
exclusion: {}
dynamic_sql:
review_mode: generate-only | auto-review-all | skip
output_dir: <project_dir>/assessment/json
etl:
review_mode: generate-only | auto-review-all | skip
output_dir: <project_dir>/assessment/ssis
etl_replatform_sources_path: <abs or empty>
After Step 4 completes, do not prompt the user again until Step 8.
Step 5: Parallel Sub-Agent Dispatch
In a single message, fire one Task tool call per in-scope sub-skill. Do not dispatch sequentially. After dispatching, end your turn — sub-agents run on their own and return results back to this conversation.
Each Task call uses the prompt template for its sub-skill below. Substitute every <placeholder> with the value from the assessment_inputs snapshot in Step 4. Use absolute paths only.
Important: Do not dispatch any sub-skill that is out of scope (per Step 3) or whose review_mode is skip. The parent synthesizes a "skipped" result for those in Step 6.
6.1 waves-runner prompt
Read and follow plugin/skills/migration/assessment/waves-generator/SKILL.md.
You are running in sub-agent mode — do NOT ask the user any questions.
Context (from parent):
- project_dir: <abs_path>
- partition_min_size: <int>
- partition_max_size: <int>
- prioritization_globs: <list or empty>
- wave_ordering: category | dependency
- output_dir: <project_dir>/assessment
Steps:
1. Call configure() with project_dir above. Snowflake credentials are not needed for waves generation.
2. Run `scai assessment waves` from <project_dir>, passing every required
input as a flag derived from partition_min_size, partition_max_size,
prioritization_globs (one --prioritize per glob), and wave_ordering
(--no-category-waves only when wave_ordering = dependency). If the CLI
still requests TTY input, fail fast and report the missing flag — do not
block on stdin.
3. Locate the timestamped waves_analysis_*.json the CLI wrote.
Report back JSON only:
{
"sub_skill": "waves-generator",
"status": "ok" | "error",
"output_json": "<abs_path>" | null,
"summary": "<one-line counts: partitions, sccs, objects>",
"error": "<message>" | null
}
6.2 exclusion-runner prompt
Read and follow plugin/skills/migration/assessment/object_exclusion_detection/SKILL.md.
You are running in sub-agent mode — do NOT ask the user any questions.
Context (from parent):
- project_dir: <abs_path>
- output_dir: <project_dir>/assessment
Steps:
1. Call configure() with project_dir above. Snowflake credentials are not needed for object exclusion.
2. Run:
scai assessment object-exclusion --project-dir <project_dir> -o <output_dir>
3. Locate the timestamped object_exclusion_analysis_*.json the CLI wrote.
Report back JSON only:
{
"sub_skill": "object-exclusion-detection",
"status": "ok" | "error",
"output_json": "<abs_path>" | null,
"summary": "<one-line counts: temp/staging, deprecated, testing, duplicates>",
"error": "<message>" | null
}
6.3 dynamic-sql-runner prompt
Read and follow plugin/skills/migration/assessment/analyzing-sql-dynamic-patterns/SKILL.md.
You are running in sub-agent mode — do NOT ask the user any questions.
Context (from parent):
- project_dir: <abs_path>
- output_dir: <project_dir>/assessment/json
- review_mode: generate-only | auto-review-all
Steps:
1. Call configure() with project_dir above. Snowflake credentials are not needed for dynamic-SQL analysis.
2. Run:
scai assessment sql-dynamic generate \
--project-dir <project_dir> \
--output <output_dir>/sql_dynamic_analysis.json
3. If review_mode = auto-review-all, follow the skill's per-occurrence
review loop until `stats` shows zero PENDING records. Each update is a
single-record call with its own analysis (no batching, no copy/paste).
4. Locate the analysis JSON.
Report back JSON only:
{
"sub_skill": "analyzing-sql-dynamic-patterns",
"status": "ok" | "error",
"output_json": "<abs_path>" | null,
"summary": "<one-line: total occurrences, REVIEWED, PENDING>",
"error": "<message>" | null
}
6.4 etl-runner prompt
Read and follow plugin/skills/migration/assessment/etl-assessment/SKILL.md.
You are running in sub-agent mode — do NOT ask the user any questions.
Context (from parent):
- project_dir: <abs_path>
- output_dir: <project_dir>/assessment/ssis
- etl_replatform_sources_path: <abs or empty>
- review_mode: generate-only | auto-review-all
Steps:
1. Call configure() with project_dir above. Snowflake credentials are not needed for SSIS analysis.
2. Locate ETL.Elements.*.csv and ETL.Issues.*.csv under
<project_dir>/reports/SnowConvert/. If etl_replatform_sources_path is
empty, auto-detect per the skill's Step 1.
3. Run:
uv run python -m scai_assessment_analyzer \
<ETL.Elements> <ETL.Issues> <SSIS_SOURCE_DIR> <output_dir>
4. If review_mode = auto-review-all, follow the skill's Step 3 + Step 4
to classify each package and produce ai_ssis_summary.html.
5. Locate etl_assessment_analysis.json under <output_dir>.
Report back JSON only:
{
"sub_skill": "etl-assessment",
"status": "ok" | "error",
"output_json": "<abs_path>" | null,
"summary": "<one-line: total packages, classified, pending>",
"error": "<message>" | null
}
6.5 Common rules for every dispatch
- One message, multiple Task calls. Send all in-scope dispatches in a single tool-use turn so the framework can run them in parallel.
- Absolute paths only in every context block.
- Sub-agent calls
configure() itself — do not assume MCP state is inherited.
- Each sub-agent writes only its own output JSON — no edits to the registry, source SQL, or other sub-agents' artifacts.
- JSON return only — free-form text in the report is harder to consume reliably.
End your turn after the dispatch message.
Step 6: Wait + Verify Outputs
Each Task return contains a single JSON object: {sub_skill, status, output_json, summary, error}. Collect all returns. A failing sub-agent does not block the others.
For every dispatched sub-skill, validate:
| Check | Action on failure |
|---|
| Task returned a JSON object | Mark sub-skill as failed with error: "no response" |
status == "ok" (or "skipped" for skip dispatches) | Mark as failed with the returned error text |
output_json path exists on disk | Mark as failed with error: "claimed JSON not found" |
output_json file is non-empty (size > 0) | Mark as failed with error: "JSON empty" (do NOT schema-validate) |
For sub-skills excluded by the Step 3 scope (or set to review_mode: skip in Step 4), synthesize {status: "skipped", output_json: null, summary: "<reason>"} so Step 8 has a complete row for every sub-skill.
Build a results table indexed by sub-skill name (waves-generator, object-exclusion-detection, analyzing-sql-dynamic-patterns, etl-assessment). Carry it into Step 7 and Step 8.
Step 7: Generate Unified HTML Report
Run the multi-report generator. It auto-discovers everything under --project-dir, including the JSONs from any sub-skill that succeeded. Missing JSONs degrade gracefully to placeholder/empty tabs.
uv run --project plugin/skills/migration/assessment \
python plugin/skills/migration/assessment/scripts/generate_multi_report.py \
--project-dir "<project_dir>" \
--output "<project_dir>/assessment/multi_report.html"
Do not pass per-source flags — the auto-discovery path is correct for every successful sub-skill. Do not write custom HTML.
If the report command fails, record the failure and proceed to Step 8 anyway — the user still needs the status table.
Step 8: Surface Results + Retry
Print a status table from the results collected in Step 6, one line per sub-skill, in this order: waves-generator, object-exclusion-detection, analyzing-sql-dynamic-patterns, etl-assessment.
Format:
waves-generator ok <output_json basename> (<summary>)
object-exclusion-detection ok <output_json basename> (<summary>)
analyzing-sql-dynamic-patterns FAIL <error message>
etl-assessment skip <reason>
Multi-tab report: <abs path to multi_report.html> (or "FAILED — see error above")
If any sub-skill failed, ask:
"Retry failed sub-skills? Successful JSONs will be reused — only the failed runs re-fire. (yes / no)"
On yes: re-fire only the failed sub-skills using the same single-message Task fan-out as Step 5. Reuse the assessment_inputs snapshot from Step 4 — do not re-prompt the user. After the retries return, re-run Step 6 verification, regenerate the report (Step 7), and re-print the status table.
Each retry produces a fresh timestamped JSON; old runs are not deleted (this supports diffs across runs).
After retries complete (or if no retry was requested), proceed to On Completion.
Prerequisites
Handled automatically by Steps 0–3. The skill assumes:
scai CLI is installed and available on PATH — used by setup, convert, and scai assessment waves.
- Python 3.11+ and
uv are available for the HTML report generator and the remaining assessment scripts (install uv per https://docs.astral.sh/uv/getting-started/installation/ — brew install uv on macOS, winget install astral-sh.uv on Windows, or pip install uv anywhere).
Example Prompts
Help users get the best results by understanding what they can ask:
Starting an Assessment
- "Run a quick assessment of my migration"
- "I need a comprehensive assessment with all analyses"
- "Generate deployment waves for my SQL migration"
Customizing Wave Generation
- "I want a maximum of N objects per wave"
- "Create smaller waves with 20-30 objects each"
- "Prioritize all Payroll-related objects in Wave 1"
- "Put all Customer* objects in the earliest waves"
- "Use dependency-based ordering instead of category-based"
Iterative Refinement (After Initial Results)
- "Show me which objects have circular dependencies"
- "Regenerate waves with smaller batch sizes"
- "What objects are blocking the migration?"
Working with Reports
- "Generate the HTML report"
- "Show me a summary of the assessment"
- "How many objects are flagged for exclusion?"
- "What's the breakdown by schema?"
Specific Analyses
- "Identify temporary and staging objects"
- "Find deprecated objects that can be excluded"
- "Analyze Dynamic SQL patterns in my codebase"
- "Assess my SSIS packages for migration complexity"
Critical Rules
Follow instructions of each sub-skill: Read the sub-skill first before executing any command.
NO CUSTOM SCRIPTS: Only execute existing scripts within sub-skills. Do not create automation, batch processing tools, or bash loops.
🚫 NEVER WRITE CUSTOM HTML REPORTS: When delivering results to users, you MUST use generate_multi_report.py from scripts/. Do NOT write HTML manually under any circumstances. See Report Generation section.
USER CONFIRMATION: Stop at mandatory checkpoints in sub-skills for user input.
DATA-DRIVEN: All assessments based on SnowConvert registry (JSON), CSV outputs and source code.
Intent Detection & Routing
Detect user intent and load the appropriate sub-skill:
Deployment Waves - Analyze dependencies and create deployment sequence:
- Triggers: "deployment waves", "migration waves", "dependency analysis", "deployment sequence", "wave planning"
- Load:
waves-generator/SKILL.md
Object Exclusion - Identify objects to exclude from migration:
- Triggers: "temporary objects", "staging objects", "deprecated", "exclude objects", "test objects", "cleanup"
- Load:
object_exclusion_detection/SKILL.md
Dynamic SQL Analysis - Classify and score Dynamic SQL patterns:
- Triggers: "dynamic sql", "sql dynamic patterns"
- Supports: SQL Server, Redshift, Oracle, and Teradata migrations
- Load:
analyzing-sql-dynamic-patterns/SKILL.md
ETL/SSIS Assessment - Analyze SSIS packages for migration complexity:
- Triggers: "ssis", "etl packages", "ssis analysis"
- Load:
etl-assessment/SKILL.md
Informatica Power Center Assessment - Analyze Informatica workflows/mappings for migration complexity:
- Triggers: "informatica", "power center", "powercenter", "informatica assessment", "informatica analysis"
- Load:
informatica-assessment/SKILL.md
Multiple Assessments - Load all applicable sub-skills if request requires comprehensive analysis.
Running Scripts
When running any scripts in any of the above skills, make sure to do all of the following:
- Wave generation is executed via
scai assessment waves. It must be run from inside the SCAI project directory so that it can read the registry.
- All other Python scripts in this skill and its sub-skills (report generation, ETL analysis, etc.) must be run with
uv run --project <DIRECTORY THIS SKILL.md file is in> python <DIRECTORY THIS SKILL.md file is in>/scripts/script_name.py.
- Do not
cd into another directory to run Python scripts, but run them from whatever directory you're already in. When scai assessment waves needs the project directory, cd into it only for that single invocation.
WHY: This maintains your current working context and prevents path confusion. When using uv run --project, you must provide absolute paths for BOTH the --project flag AND the script itself. Just run the script the way the skill says. Do not question it by running --help or reading the script.
Tools
generate_multi_report.py
Description: Generates unified multi-tab HTML report combining Object Exclusion, Dynamic SQL Analysis, Waves, and SSIS Assessment reports.
Location: scripts/generate_multi_report.py
When to use: After completing any requested assessment(s) (1, 2, 3, or all) to deliver results in a consistent format, or whenever the user requests a combined HTML report.
Report Generation
MANDATORY: When users request an assessment report (even if it’s only one sub-assessment), a migration report, or a combined HTML report—or when you have completed the requested assessment(s) and are ready to deliver results—you MUST use generate_multi_report.py. This is the ONLY approved method for generating consolidated assessment reports.
DO NOT:
- Write custom HTML reports manually
- Use individual sub-skill report generators in isolation
- Create new report generation scripts
Script Location: scripts/generate_multi_report.py
Usage with uv (recommended — single --project-dir argument):
uv run --project <SKILL_DIRECTORY> \
python <SKILL_DIRECTORY>/scripts/generate_multi_report.py \
--project-dir "path/to/<projectRoot>" \
--output "path/to/<projectRoot>/assessment/multi_report.html"
When --project-dir is provided, the script auto-discovers:
<projectRoot>/registry/ — registry JSONs (REQUIRED for object enrichment: names, categories, files, status, missing-deps, direct dep counts)
<projectRoot>/reports/ — SnowConvert CSVs (for EWI/FDM/PRF counts and severity)
<projectRoot>/assessment/object_exclusion_analysis_*.json — exclusion JSON (latest timestamp)
<projectRoot>/assessment/json/sql_dynamic_analysis.json — dynamic-SQL JSON
<projectRoot>/assessment/waves_analysis_*.json — waves JSON (latest timestamp)
Explicit per-source flags (below) override auto-discovery. The individual flags are only needed when you want to mix-and-match sources from non-standard locations.
Usage with explicit paths (advanced):
uv run --project <SKILL_DIRECTORY> \
python <SKILL_DIRECTORY>/scripts/generate_multi_report.py \
--project-dir "path/to/<projectRoot>" \
--waves-json "path/to/waves_analysis_TIMESTAMP.json" \
--exclusion-json "path/to/object_exclusion.json" \
--dynamic-sql-json "path/to/sql_dynamic_analysis.json" \
--snowconvert-reports-dir "path/to/reports" \
--ssis-json "path/to/ssis/etl_assessment_analysis.json" \
--output "path/to/multi_report.html"
IMPORTANT: Always pass --project-dir even when providing explicit flags — the registry at <projectRoot>/registry/ is required to enrich the waves JSON (which now emits UUIDs; canonical names, categories, file paths, and status come from the registry). Without --project-dir (or an equivalent --registry-dir), the dependencies table will show UUIDs and every row will have category "UNKNOWN".
Note: Replace <SKILL_DIRECTORY> with the absolute path to this skill directory.
Parameters:
--project-dir (recommended): Path to the scai project root. Auto-discovers registry, SnowConvert reports, and assessment artifacts. Use this alone in the common case.
--waves-json: Path to waves analysis JSON file (output from scai assessment waves, e.g. waves_analysis_<timestamp>.json). Auto-discovered from <projectRoot>/assessment/ when --project-dir is provided.
--registry-dir: Path to SnowConvert registry directory containing *.json entries. Auto-discovered from <projectRoot>/registry/ when --project-dir is provided.
--exclusion-json: Path to object exclusion JSON file. Auto-discovered.
--dynamic-sql-json: Path to dynamic SQL analysis JSON file. Auto-discovered.
--snowconvert-reports-dir: Path to SnowConvert Reports directory containing TopLevelCodeUnits.*.csv and ObjectReferences.*.csv. Auto-discovered.
--ssis-json: Path to SSIS assessment JSON file (etl_assessment_analysis.json from ETL assessment)
--informatica-json: Path to Informatica Power Center assessment JSON file (informatica_assessment_analysis.json from Informatica assessment)
--output: Output HTML file path (required)
Note: At least one data source parameter must be provided. If only partial assessment was completed, provide only the available data sources.
SSIS Report Generation: When SSIS packages are analyzed using the ETL assessment sub-skill, the resulting etl_assessment_analysis.json file should be provided via --ssis-json to include the SSIS tab in the unified report.
Informatica Report Generation: When Informatica workflows are analyzed using the Informatica assessment sub-skill, the resulting informatica_assessment_analysis.json file should be provided via --informatica-json to include the Informatica tab in the unified report.
Report Styling
When generating HTML reports, see STYLES.md for styling specifications.
Success Criteria
An assessment is complete when:
- ✅ All requested analyses have completed without errors
- ✅ For Dynamic SQL: All occurrences have status
REVIEWED (no PENDING records)
- ✅ Reports generated successfully with all requested data sources ** using
generate_multi_report.py (NOT custom HTML) **
- ✅ User has reviewed and approved findings
Pre-Completion Checklist
Before marking assessment as complete, verify:
□ Did I use generate_multi_report.py for the final report?
□ Did I pass all available JSON files to the script?
□ Did I avoid writing any custom HTML?
If any answer is "No", go back and use the correct script.
Sub-Skill Documentation
waves-generator/SKILL.md - Algorithm details, partition creation
object_exclusion_detection/SKILL.md - Pattern definitions, naming conventions
analyzing-sql-dynamic-patterns/SKILL.md - Pattern classification, complexity scoring
etl-assessment/SKILL.md - SSIS package analysis, control flow, data flow pipelines
On Completion
Present the closing message with: opening line (migration_status.in_scope objects in wave_count waves), summary table (Register, Convert, ETL conversion, Waves, Object exclusion, Dynamic SQL, SSIS/Informatica analysis, Missing objects — pull from migration_status and Code Unit Registry), key findings (2–4 bullets interpreting the data: heavy staging footprint ≥30%, unresolved external refs, conversion friction, ETL risk, circular dependencies — only when triggers fire), report path (platform-specific open command for <project_dir>/assessment/multi_report.html), and next-steps menu:
What would you like to do?
- Review the report — open HTML or ask any questions
- Modify the assessment — re-run with changed parameters
- Move on to migration — start Phase 2 (loads
../migrate-objects/SKILL.md)
Mark option (1) as (recommended) when missing > 0, otherwise (3). Wait for response.