Replaced cursor-driven cost center traversal with a recursive CTE hierarchy expansion. XML parameter parsing replaced with VARIANT + TRY_TO_NUMBER / TRY_TO_BOOLEAN. OUTPUT INTO replaced with RESULT_SCAN(LAST_QUERY_ID()). Exception handling via Snowflake EXCEPTION WHEN blocks. Transaction semantics simplified to Snowflake's single-level model.
Procedure executed successfully in 6.0s. Created a new consolidated budget header, inserted 51 consolidated line items, verified rollup of leaf-level amounts into parent and root cost centers at the (CostCenterID, GLAccountID, FiscalPeriodID) grain. No duplicate rows, all referential integrity checks passed.
Table-valued parameters replaced with temp tables (T_ENTITYLIST, T_INTERCOMPANY_PAIRS) seeded at procedure start. Locking hints removed — Snowflake's MVCC model handles isolation. Pair matching redesigned as a set-based CTE instead of cursor-driven row-by-row logic. Variance aggregation via window functions.
Procedure executed successfully in 5.0s on a controlled +100,000 / -100,000 offsetting test case. Identified 2 intercompany pairs, marked both reconciled, produced zero unreconciled balances and zero total variance. Reconciliation report returned as VARIANT with full pair-level detail.
Cursor-based rule iteration replaced with set-based execution using T_RULES and T_SOURCE_LINES temp tables, sorted by ExecutionSequence. WAITFOR DELAY / sp_getapplock dropped — no equivalent needed in Snowflake's serverless model. Dry-run mode preserved via p_dry_run flag gating the final INSERT. AllocationSourceLineID lineage preserved through explicit column mapping.
Procedure executed in 6.2s. Allocated 14 rows with dry_run=false. Source rows correctly marked as allocated. Generated rows reference valid source lines through AllocationSourceLineID. Referential integrity verified across GL accounts, cost centers, and fiscal periods. No duplicate allocated rows at expected business grain.
The same four-layer framework was applied to all three procedures, making it repeatable across different business logic domains.
- Source budget header exists and is in eligible status
- Required reference data seeded (GL accounts, cost centers, fiscal periods)
- Procedure-specific preconditions met (e.g. intercompany flags, allocation rules active)
- Procedure completes without runtime error
- Returns SUCCESS status in VARIANT output
- Key metadata fields populated (IDs, row counts, report objects)
- Output row counts match expected business logic (e.g. rollup factor, pair count)
- Business-level outcomes correct (consolidation amounts, zero variance, allocated rows)
- Source state mutations applied correctly (status updates, IsAllocated flags)
- Referential integrity across all FK relationships
- Required output columns fully populated (no NULLs in NOT NULL fields)
- No duplicate rows at expected business grain
- Regression-safe: framework reusable with different parameters
-
VARCHAR WIDTH
SQL Server
NVARCHAR(MAX)columns mapped to SnowflakeVARCHARdefaults that caused truncation on long procedure output strings. Widened selected columns to preserve data integrity without altering business logic. -
TIMESTAMP
SQL Server
DATETIME2defaults usingSYSUTCDATETIME()replaced with SnowflakeTIMESTAMP_NTZ+SYSDATE(). Behavioral equivalence confirmed through execution timing checks. - SOURCE SCHEMA Some source object definitions contained SQL Server-specific runtime assumptions that did not carry over cleanly. Adaptations documented as explicit migration assumptions, not silent schema edits, to preserve auditability.
-
VARIABLE SCOPING
SQL Server procedure variables referenced inside inner scopes without rebinding. Snowflake requires explicit colon-prefixed binding in SQL contexts (
:v_var). Refactored all variable references accordingly.
| AI Tool | Where it was useful | Where manual work was required |
|---|---|---|
| SnowConvert AI | Object scaffolding, dependency graph, initial syntax translation patterns, surfacing likely incompatibilities quickly | Complex procedures with cursors, TVPs, XML parsing, and OUTPUT INTO required full manual redesign — SnowConvert output was not executable as-is for these |
| Cortex (Snowflake) | Initial exploration of procedural rewrite options, quick lookup of Snowflake SQL equivalents | Tended to drift from actual business logic when followed literally for complex multi-step procedures. Used for exploration only, not final logic design |
| AI (general) | Drafting parts of validation documentation structure; speeding up investigation of Snowflake-specific function equivalents | All validation criteria, migration assumptions, business logic interpretation, debugging, and test data design were manual |