messy_legacy.sql| Construct | Objects | Migration note |
|---|---|---|
raiserror_throw | 2 | Error raising; map to Python exceptions. |
hints | 1 | Locking hints; drop (not applicable in lakehouse). |
cursor | 1 | Row-by-row cursor; rewrite as set-based PySpark/SQL. |
temp_tables | 1 | Temp tables; replace with temp views or DataFrames. |
while_loop | 1 | Procedural loop; rewrite as set-based logic. |
dynamic_sql | 1 | Dynamic SQL; needs manual review and parameterized rewrite. |
linked_server | 1 | Cross-server access; replace with lakehouse ingestion or shortcuts. |
xp_cmdshell | 1 | OS shell access; must be re-platformed (no lakehouse equivalent). |
merge | 1 | MERGE semantics; map to Delta MERGE INTO (usually straightforward). |
explicit_transactions | 1 | Explicit transactions; Delta is ACID per table, multi-table needs design. |
try_catch | 1 | TRY/CATCH; map to Python try/except in notebooks. |
xml_processing | 1 | XML processing; parse with Spark XML or from_xml functions. |
clr | 1 | SQLCLR .NET code; rewrite as Python/Scala UDF or notebook logic. |
goto | 1 | GOTO control flow; restructure logic. |
| Type | Tables | Migration note |
|---|---|---|
ntext | 1 | Deprecated LOB; map to string. |
xml | 1 | Store as string; parse downstream. |
timestamp | 1 | Rowversion, not a datetime; map to binary or drop. |
geography | 1 | Spatial type; store as WKT/WKB string or use a geo library. |
hierarchyid | 1 | No Spark equivalent; store as string path. |
sql_variant | 1 | Variant type; split into typed columns or store as string. |
computed_columns | 1 | Review computed column logic. |
| Tier | Object | Recommended approach |
|---|---|---|
| High | dbo.usp_syncfromlegacyerpProcedure · 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 |
| High | dbo.usp_rebuildcustomertotalsProcedure · 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. |
| Medium | dbo.fn_regexmatchFunction · 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 |
| Medium | dbo.trg_salesorder_auditTrigger · 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 |
| Medium | dbo.usp_dynamicreportProcedure · 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 |
| Medium | dbo.fn_parseattributesFunction · 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 |
| Low | dbo.vopenordersView · score 3 | Low complexity; standard conversion. |