SIGNAL NORTH

SQL Server → Lakehouse Migration Assessment

Contoso Distribution · source: messy_legacy.sql
October 28, 2026
2 functions · 3 procedures · 4 tables · 1 trigger · 1 view
11
Objects inventoried
54/100
Complexity index
58.0
Est. effort (hours)
2
High-complexity objects

Executive summary

Contoso Distribution's SQL Server database migration assessment reveals 4 tables, 1 view, 3 procedures, 2 functions, and 1 trigger, with an overall complexity index of 54 out of 100. The migration effort is estimated to require 58.0 hours to complete, driven by high-complexity objects such as dbo.usp_syncfromlegacyerp and dbo.usp_rebuildcustomertotals. The assessment identified several risk flags, including 1 linked_server, 1 xp_cmdshell, 1 clr, and 1 xml_processing, alongside type risks like 1 geography and 1 hierarchyid. To ensure a successful lakehouse migration, expert review is required for dbo.usp_syncfromlegacyerp, dbo.fn_regexmatch, dbo.trg_salesorder_audit, dbo.usp_dynamicreport, and dbo.fn_parseattributes.
  1. Allocate the 58.0 estimated hours to address the migration of the database objects.
  2. Conduct an expert review on the objects needing review: dbo.usp_syncfromlegacyerp, dbo.fn_regexmatch, dbo.trg_salesorder_audit, dbo.usp_dynamicreport, and dbo.fn_parseattributes.
  3. Address high-risk elements including 1 linked_server, 1 xp_cmdshell, 1 clr, and 1 geography type risk during the lakehouse transition.

Recommended migration phases

1 · Foundation4 tables + 1 low-complexity objects
≈ 4.0 hrs · land Bronze/Silver Delta tables
2 · Core logic4 medium-complexity objects
≈ 22 hrs · convert to Spark SQL / PySpark
3 · Hard cases2 high-complexity objects
≈ 32 hrs · 5 flagged for expert review

Migration risk constructs

ConstructObjectsMigration note
raiserror_throw2Error raising; map to Python exceptions.
hints1Locking hints; drop (not applicable in lakehouse).
cursor1Row-by-row cursor; rewrite as set-based PySpark/SQL.
temp_tables1Temp tables; replace with temp views or DataFrames.
while_loop1Procedural loop; rewrite as set-based logic.
dynamic_sql1Dynamic SQL; needs manual review and parameterized rewrite.
linked_server1Cross-server access; replace with lakehouse ingestion or shortcuts.
xp_cmdshell1OS shell access; must be re-platformed (no lakehouse equivalent).
merge1MERGE semantics; map to Delta MERGE INTO (usually straightforward).
explicit_transactions1Explicit transactions; Delta is ACID per table, multi-table needs design.
try_catch1TRY/CATCH; map to Python try/except in notebooks.
xml_processing1XML processing; parse with Spark XML or from_xml functions.
clr1SQLCLR .NET code; rewrite as Python/Scala UDF or notebook logic.
goto1GOTO control flow; restructure logic.

Data type risks

TypeTablesMigration note
ntext1Deprecated LOB; map to string.
xml1Store as string; parse downstream.
timestamp1Rowversion, not a datetime; map to binary or drop.
geography1Spatial type; store as WKT/WKB string or use a geo library.
hierarchyid1No Spark equivalent; store as string path.
sql_variant1Variant type; split into typed columns or store as string.
computed_columns1Review computed column logic.

Code objects by complexity

TierObjectRecommended approach
Highdbo.usp_syncfromlegacyerp
Procedure · score 31
PySpark notebook — Migrate the procedure to a PySpark notebook that replaces the Linked Server and OPENQUERY connections with Spark JDBC readers. Implement the Customer table merge using Delta Lake's MERGE INTO SQL syntax, and replace the local BCP file export with a Spark write operation to ADLS or DBFS. Replace the explicit SQL transactions and TRY/CATCH block with Python try-except blocks to handle errors and log failures. Expert review
Highdbo.usp_rebuildcustomertotals
Procedure · score 17
Spark SQL view — Replace the cursor, loop, and temporary table with a declarative Spark SQL view. The view will perform a LEFT JOIN from the Customer table to the SalesOrder table, grouping by CustomerID and calculating the SUM of LineTotal to achieve the exact same result set in a single, highly optimized distributed query.
Mediumdbo.fn_regexmatch
Function · score 13
Python UDF — Migrate this CLR function by implementing a Python UDF using Python's native 're' library and registering it as a Spark SQL function. Alternatively, replace calls to this function in downstream SQL queries with Spark's native 'regexp_like' or 'rlike' functions for better performance. Ensure the regex pattern syntax is compatible between .NET and Python/Java flavors. Expert review
Mediumdbo.trg_salesorder_audit
Trigger · score 12
PySpark notebook — Migrate the trigger logic into a PySpark notebook that handles all writes to the SalesOrder Delta table. The notebook will count the incoming DataFrame records, raise a Python exception to abort the transaction if the count exceeds 1000, and otherwise perform the write operation while appending a corresponding action record ('INSERT', 'UPDATE', or 'DELETE') to the AuditLog Delta table. Expert review
Mediumdbo.usp_dynamicreport
Procedure · score 11
PySpark notebook — Recreate this procedure as a PySpark notebook that accepts table_name and where_clause as parameters. Use spark.read.table to dynamically load the target Delta table, apply the filter using the Spark SQL expression, and then execute a Delta update on the AuditLog table to record the read action. Expert review
Mediumdbo.fn_parseattributes
Function · score 7
Python UDF — Recreate this table-valued function as a PySpark User-Defined Table Function (UDTF) or a Python UDF returning an array of structs. Use Python's standard 'xml.etree.ElementTree' library to parse the XML string, extract the 'name' attribute and text value of each '/attrs/attr' element, and return them as structured rows. Expert review
Lowdbo.vopenorders
View · score 3
Low complexity; standard conversion.