Generates test scenarios for a stored procedure migration. Enumerates conditional branches from the procedure's logic, synthesizes minimal fixture data for each branch, and writes structured JSON to test-specs/. This skill produces scenarios only -- it does not execute stored procedures, capture ground truth, or generate dbt YAML. The /generate-tests command handles bulk execution and YAML conversion after the review loop completes.

/generating-tests <schema.table>

Argument is the fully-qualified table name (the item_id). The writer procedure is read automatically from the catalog scoping section.

• manifest.json must exist in the project root. If missing, run Command Setup DDL first.
• catalog/tables/<item_id>.json must exist. If missing, run /listing-objects list tables to see available tables.
• The table must have been scoped (Skill Analyzing Table) and profiled (Skill Profiling Table) before test generation.

uv run --project <shared-path> migrate context --table <item_id>

Output JSON contains:

For each table in source_tables, the skill reads catalog/tables/<schema>.<table>.json to get full column metadata (is_nullable, is_identity, types, auto_increment_columns).

For each statement where action == migrate, the skill identifies all conditional branches:

Output: a branch manifest with IDs, descriptions, statement indexes, and patterns.

For each branch, generates minimum synthetic input rows (1-3 per source table):

• Each scenario is self-contained -- no shared test data across scenarios
• FK-consistent within each scenario using catalog foreign_keys for topological ordering
• Type-appropriate values from catalog column metadata
• Parameters are ignored or flagged (rare in warehouse procs)

Columns excluded from fixtures:

• Computed columns (defined with AS <expression> in DDL)
• Identity columns not needed by the scenario (let SQL Server auto-generate)

NOT NULL column coverage: Every source table fixture row includes all columns where is_nullable == false, except computed and unneeded identity columns. Type-appropriate defaults are used for NOT NULL columns not referenced by the procedure:

CHECK constraint compliance: Fixture values satisfy any CHECK constraints found in the DDL (range, enum, cross-column).

Shows branch manifest, generated fixtures, and any uncovered branches or warnings. The user approves, requests edits, or declines.

Writes test-specs/<item_id>.json with the TestSpec schema. The expect field is omitted -- ground truth is captured later by the command.

Re-invoking the skill on the same table runs in merge mode: existing scenarios are preserved (including any expect blocks that already contain ground truth), and only new scenarios for uncovered branches are appended.

• Every unit_tests[] entry has at least one given entry with rows
• Every given[].rows entry includes all NOT NULL non-identity columns
• coverage set: complete when all branches have scenarios, partial otherwise
• status set: ok, partial, or error

• Test name: test_<load_pattern>_<scenario_description>
• target_table: bracket-quoted FQN, e.g., [silver].[DimProduct]
• procedure: bracket-quoted FQN, e.g., [silver].[usp_load_DimProduct]
• given[].table: bracket-quoted SQL identifier, e.g., [bronze].[SalesOrderHeader]

validation.issues[], warnings[], and errors[] use the shared diagnostics schema:

{
  "item_id": "silver.dimproduct",
  "status": "ok",
  "coverage": "complete",
  "branch_manifest": [
    {
      "id": "merge_matched_update",
      "statement_index": 0,
      "description": "MERGE WHEN MATCHED -> UPDATE existing product",
      "scenarios": ["test_merge_matched_existing_product_updated"]
    },
    {
      "id": "merge_not_matched_insert",
      "statement_index": 0,
      "description": "MERGE WHEN NOT MATCHED -> INSERT new product",
      "scenarios": ["test_merge_not_matched_new_product_inserted"]
    }
  ],
  "unit_tests": [
    {
      "name": "test_merge_matched_existing_product_updated",
      "target_table": "[silver].[DimProduct]",
      "procedure": "[silver].[usp_load_DimProduct]",
      "given": [
        {
          "table": "[bronze].[Product]",
          "rows": [
            { "ProductID": 1, "Name": "Widget", "ListPrice": 99.99, "ModifiedDate": "2024-01-15", "rowguid": "A0000000-0000-0000-0000-000000000001" }
          ]
        },
        {
          "table": "[silver].[DimProduct]",
          "rows": [
            { "ProductKey": 1, "ProductName": "Old Widget", "ListPrice": 50.00 }
          ]
        }
      ]
    },
    {
      "name": "test_merge_not_matched_new_product_inserted",
      "target_table": "[silver].[DimProduct]",
      "procedure": "[silver].[usp_load_DimProduct]",
      "given": [
        {
          "table": "[bronze].[Product]",
          "rows": [
            { "ProductID": 999, "Name": "New Widget", "ListPrice": 29.99, "ModifiedDate": "2024-06-01", "rowguid": "B0000000-0000-0000-0000-000000000001" }
          ]
        }
      ]
    }
  ],
  "uncovered_branches": [],
  "warnings": [],
  "validation": {
    "passed": true,
    "issues": []
  },
  "errors": []
}

When invoked with a feedback_for_generator JSON block (from the test reviewer sub-agent):

• uncovered_branches: list of branch IDs missing coverage. New scenarios are generated for each listed branch and added to unit_tests[].
• quality_fixes: per-scenario remediation instructions. Named scenarios are revised in unit_tests[] as directed.

Previously approved scenarios are preserved -- only additions and revisions are applied.

The test generator must not:

• Execute stored procedures or access the sandbox
• Generate dbt SQL model files
• Render YAML -- unit_tests[] is structured JSON; dbt YAML conversion happens post-execution
• Make materialization or business key decisions
• Score its own coverage authoritatively -- the test reviewer sub-agent does that