| name | anti-patterns |
| description | Identify SQL Server migration anti-patterns — performance blockers, architecture/security considerations, and behavioral differences — by running `scai assessment anti-patterns`, which buckets SnowConvert's already-recorded issue codes by priority and writes JSON for the parent assessment multi-report. Use for anti-patterns, performance/architecture concerns in SQL Server → Snowflake migrations; |
| parent_skill | assessment |
| license | Proprietary. See License-Skills for complete terms |
Anti-Patterns
This skill is a thin wrapper over the SCAI CLI command scai assessment anti-patterns. SCAI filters the SnowConvert issue codes already recorded on the project's code units against a curated catalog of migration-relevant anti-patterns, groups them into customer-facing categories with a priority breakdown, and writes a JSON file the parent assessment skill's multi-tab HTML report consumes.
Scope (v1): SQL Server only. The command auto-detects the project's source dialect and aborts with a clear message on non-SQL-Server projects. Redshift, Teradata, and Oracle are planned for later phases.
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. This sub-skill takes no user prompts in either mode, so the only behavioral change in sub-agent mode is the JSON return contract.
| Field | Required | Notes |
|---|
project_dir | yes | absolute path to the SCAI project root (must contain .scai/ and an initialized registry) |
On entry: call the configure MCP tool with project_dir from the context block. Snowflake credentials are not required — scai assessment anti-patterns reads only the local Code Unit Registry.
Run the SCAI command exactly as documented in Workflow Step 1.
On completion, return JSON only:
{
"sub_skill": "anti-patterns",
"status": "ok",
"output_json": "<abs path to anti-patterns-*.json>",
"summary": "<one-line: buckets, distinct flags, code units affected>",
"error": null
}
- On a non-SQL-Server project (the CLI aborts with
ASM0024): return "status": "skipped", "output_json": null, "error": null, and a summary explaining the dialect is unsupported in v1. Do not treat this as an error.
- On any other failure:
"status": "error", "output_json": null, "error": "<message>".
Critical Rules
- Use ONLY the SCAI CLI. No inline parsing, no custom analyzers, no new issue detection.
scai assessment anti-patterns is the single source of truth. The catalog of codes and their bucket/priority classifications live inside SCAI, not in this skill.
- Registry-only. Detection reads the Code Unit Registry (
/registry/).
- Inputs come from the parent. The parent
assessment skill resolves project_dir; this skill just runs SCAI.
- One artifact per run. SCAI writes a single timestamped JSON:
anti-patterns-YYYYMMDD_HHMMSS.json under <project_dir>/artifacts/assessment/.
Inputs (auto-detected by parent)
| Input | Resolution under project_dir |
|---|
| Project directory | <project_dir> — SCAI reads the registry and gates on the SQL Server dialect |
| Output location (fixed) | <project_dir>/artifacts/assessment/ — created by the command; not configurable |
Workflow
Step 1: Run SCAI
Run from inside the SCAI project directory so the command can read the registry:
scai assessment anti-patterns
There are no options in v1. The command:
- aborts immediately with
ASM0024 if the project's source dialect is not SQL Server (→ report skipped);
- otherwise scans every code unit's recorded issues, keeps the catalog-matched codes, and writes one file:
<project_dir>/artifacts/assessment/anti-patterns-YYYYMMDD_HHMMSS.json
Step 2: Locate the latest output
After the command completes, capture the path of the newest anti-patterns-*.json in <project_dir>/artifacts/assessment/ and pass it back to the parent skill. Do not parse or re-write the file.
Step 3: Hand off to the parent
Return the artifact path to the parent assessment skill. The multi-tab report auto-discovers the latest artifacts/assessment/anti-patterns-*.json from --project-dir, so no explicit flag is required; an optional --anti-patterns-json <path> override exists for non-standard locations. This skill never generates HTML directly.
SCAI Command Reference
| Option | Description |
|---|
| (none in v1) | The command takes no options. It must be run from within a SCAI project directory (RequiresProject), needs no Snowflake connection, and gates on the SQL Server source dialect. |
Output Schema
SCAI writes a single JSON file (snake_case) with the following top-level structure: schema_version, generated_at, project_name, source_dialect, summary (with roll-ups by bucket and priority), flags (each with code/title/bucket/priority/recommendations), and code_units (each with its detected flags). The parent skill's multi-tab report reads these keys directly. Do not transform or rewrite the file.
What SCAI Detects
(Documented for skill operators — SCAI handles all of this; no configuration is exposed to this skill.) The curated v1 catalog groups migration-relevant SQL Server issue codes into three customer-facing categories, each entry carrying a priority and a plain-language what/why/recommendation:
- Performance Concerns — cursor-heavy and row-by-row patterns that run poorly on Snowflake's set-based engine (e.g. cursor FOR loops, nested cursors, FETCH-in-loop,
WHILE/LOOP row-by-row, case-insensitive-collation performance, recursive-CTE keyword, clustering review).
- Architecture & Security Blockers — source features with no direct Snowflake equivalent that need a native redesign or migration decision (e.g.
CREATE TYPE AS TABLE, OPENQUERY/remote access, BULK INSERT→PUT, external-table conversion, nested transactions, CTE-in-view, GOTO into nested block, full-join DELETE, SET ROWCOUNT).
- Behavior & Semantic Differences — code that runs but can behave subtly differently (e.g. collation,
NUMBER scale lowered for arithmetic, numeric/date format differences, SELECT * over remapped system objects, SET QUOTED_IDENTIFIER, output-parameter ordering, masking-policy role, ANSI_PADDING OFF, redundant single-table views as a modernization candidate).
Findings are also rolled up by migration-impact priority (Critical / High / Medium / Low).
Failure Modes
| Symptom | Likely cause | Action |
|---|
Error: … supports SQL Server projects only (ASM0024) | Project's source dialect is not SQL Server | Expected in v1 — report status: "skipped"; not an error |
Error: Unable to resolve inputs … (ASM0022) | Registry not initialized / not a SCAI project | Verify the parent ran scai code convert successfully and a .scai/registry/ exists |
Error: Anti-patterns analysis generation failed … (ASM0023) | Unexpected I/O or registry read failure | Check the message; verify the registry is intact |
Empty flags/code_units with zeroed summary | No catalog-matched codes in this workload | Normal — a valid document is still written; proceed to report generation |
Related
- Parent:
../SKILL.md (consumes the artifact; auto-discovers it via --project-dir)
- Report tab renderer:
../scripts/anti_patterns_report/generate_anti_patterns_report_content.py
- SCAI built-in help:
scai assessment anti-patterns --help
