From Stored Procedures to Notebooks: Rebuilding Heavy T‑SQL Workloads in Microsoft Fabric

1. Why Move Stored Procedures into Fabric Notebooks?

If your data platform is SQL Server–centric, you probably have:

• Massive stored procedures doing nightly ETL
• Nested procedures calling each other in the right order
• Reporting logic embedded in T‑SQL views and functions

In Fabric, you can still run T‑SQL in a Warehouse or Lakehouse SQL endpoint, but notebooks give you more:

• Multi-language: combine T‑SQL, PySpark, and Python in one pipeline
• Lake-first: work directly on Delta tables in OneLake
• Orchestration-friendly: notebooks become first-class activities in Data Factory Pipelines
• Testability: easier to parameterise, log, and unit test than monolithic stored procedures

The goal is not to translate every line 1:1, but to:

1. Separate data movement from business logic
2. Move heavy transformations to Spark where it makes sense
3. Keep critical T‑SQL logic, but in a more modular, notebook-driven design

2. Classify Your Existing Stored Procedures

Before you open a single notebook, map what you actually have. For each stored procedure, classify it by purpose.

2.1 Common categories

• Staging / ingestion
  • Load from source systems into staging tables
  • Basic type casting, trimming, deduplication
• Transformations / business rules
  • Slowly changing dimensions
  • Fact table builds
  • Complex joins and window functions
• Reporting layer
  • Aggregations, KPIs, pre-calculated measures
  • Security filters (row-level security logic)
• Utility / housekeeping
  • Logging, audit trail, metadata updates
  • Archive / purge logic

Create a simple inventory table (Excel, OneNote, or a Fabric Lakehouse table) with columns like:

• ProcName
• Category (Staging / Transform / Reporting / Utility)
• Frequency (Daily / Hourly / On-demand)
• InputTables
• OutputTables
• Criticality (High / Medium / Low)

This gives you a migration map and hints at how to group notebook logic.

3. Map Stored Procedure Patterns to Notebook Patterns

You don’t want one giant notebook per stored procedure. Instead, map recurring T‑SQL patterns to notebook patterns.

3.1 Pattern: Staging loads

Old world (SQL Server)

• Stored procedure with INSERT INTO Stg.Table SELECT ... FROM LinkedServer...
• Maybe some simple validation and logging

Fabric notebook pattern

• Use a notebook with:
  • A parameter cell for load date / file path
  • A PySpark or T‑SQL cell to load data from source (e.g., ADLS, Lakehouse, Warehouse)
  • Basic data quality checks

Example (PySpark) staging cell:

from pyspark.sql.functions import col, current_timestamp

# Parameters (could come from pipeline)
load_date = dbutils.widgets.get("p_load_date")

raw_df = (spark.read
    .format("delta")
    .load(f"/lakehouse/raw/sales/date={load_date}")
)

stg_df = (raw_df
    .dropDuplicates(["OrderID"])
    .withColumn("LoadDate", current_timestamp())
)

(stg_df.write
    .format("delta")
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .save("/lakehouse/stg/sales")
)

3.2 Pattern: Heavy transformations

Old world

• Complex stored procedure with multiple temp tables
• Large joins, window functions, SCD logic

Fabric notebook pattern

• Use Spark DataFrames or T‑SQL on the Lakehouse SQL endpoint
• Break logic into cells: Load, Transform, Write
• Reuse transformation functions across notebooks

Example: translating a windowed aggregation from T‑SQL to PySpark.

Original T‑SQL snippet

SELECT
    c.CustomerID,
    SUM(o.Amount) AS TotalAmount,
    ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC) AS rn
INTO #CustomerLatest
FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= DATEADD(year, -1, GETDATE())
GROUP BY c.CustomerID, o.OrderDate;

PySpark equivalent in a notebook

from pyspark.sql.window import Window
from pyspark.sql.functions import sum as _sum, row_number, col

orders_df = spark.read.table("lakehouse.dbo_Orders")
customers_df = spark.read.table("lakehouse.dbo_Customers")

joined = (orders_df.alias("o")
    .join(customers_df.alias("c"), col("c.CustomerID") == col("o.CustomerID"))
    .where(col("o.OrderDate") >= expr("add_months(current_date(), -12)"))
)

w = Window.partitionBy("c.CustomerID").orderBy(col("o.OrderDate").desc())

latest_df = (joined
    .groupBy("c.CustomerID", "o.OrderDate")
    .agg(_sum("o.Amount").alias("TotalAmount"))
    .withColumn("rn", row_number().over(w))
)

latest_df.write.format("delta").mode("overwrite").save("/lakehouse/curated/customer_latest")

3.3 Pattern: Reporting logic

Old world

• Stored procedures that Power BI calls via DirectQuery
• Views that pre-calculate metrics

Fabric notebook pattern

• Materialise curated tables in Lakehouse / Warehouse
• Keep only light calculations in T‑SQL views or DAX

Example: create a SQL view on top of a curated Delta table in Fabric’s SQL endpoint:

CREATE OR ALTER VIEW rpt.SalesSummary AS
SELECT
    CustomerID,
    OrderDate,
    SUM(Amount) AS SalesAmount,
    COUNT(*) AS OrderCount
FROM dbo.FactSales
GROUP BY CustomerID, OrderDate;

Then build DAX measures in Power BI on top of this view.

4. Structuring Notebooks for Heavy Workloads

A common anti-pattern is a single 500-line notebook replacing a 500-line stored procedure. Instead, design notebooks for:

• Reusability
• Parameterisation
• Testability

4.1 A simple folder structure

In your Fabric workspace, you might adopt a structure like:

• 01_ingestion/ – raw → staging
• 02_transformation/ – staging → curated
• 03_marts/ – curated → marts / reporting
• 99_utility/ – metadata, logging, common functions

Each folder contains small, focused notebooks:

• 01_ingestion/load_sales_raw_to_stg
• 02_transformation/build_dim_customer
• 02_transformation/build_fact_sales
• 03_marts/build_sales_mart_daily

4.2 Parameterised notebooks

Instead of hard-coding dates, schemas, or file paths, use widgets.

# At top of notebook

dbutils.widgets.text("p_run_date", "", "Run Date (YYYY-MM-DD)")
run_date = dbutils.widgets.get("p_run_date")

if not run_date:
    from datetime import date
    run_date = date.today().isoformat()

print(f"Running for date: {run_date}")

Your pipeline can set p_run_date dynamically, similar to passing parameters into stored procedures.

4.3 Shared logic modules

If you have repeated business rules across stored procedures (e.g., standard customer deduplication), move them into a shared Python module.

# In a shared notebook or .py file

def standardize_customer(df):
    from pyspark.sql.functions import upper, trim
    return (df
        .withColumn("CustomerName", upper(trim("CustomerName")))
        .withColumn("Email", lower(trim("Email")))
    )

Then import and use in multiple notebooks.

5. Orchestrating Notebooks Instead of Nested Stored Procedures

In SQL Server, you might have a master stored procedure that calls others in sequence. In Fabric, use Data Factory Pipelines.

5.1 Typical orchestration flow

1. Ingestion pipeline
   • Notebook: load raw → staging
   • Notebook: apply basic validations
2. Transformation pipeline
   • Notebook: build dimensions
   • Notebook: build fact tables
3. Mart / reporting pipeline
   • Notebook or SQL script: build aggregated tables
   • Dataflow Gen2 / Power BI refresh

Each pipeline step can:

• Pass parameters to notebooks
• Wait for completion
• Branch on success/failure

5.2 Example: Notebook activity configuration

When adding a Notebook activity in a pipeline:

• Set Base parameters:
  • p_run_date → @pipeline().parameters.run_date
• Configure Retry and Timeout based on workload size
• Enable Output capturing if you want to write status back to a control table

You can still keep a small SQL script activity for tasks that are naturally T‑SQL, e.g.:

INSERT INTO dbo.ETL_RunLog (PipelineName, RunDate, Status, StartTime)
VALUES (@pipelineName, @runDate, 'Started', SYSUTCDATETIME());

6. Handling Performance and Cost

Moving to notebooks doesn’t automatically make things faster. You need to be deliberate about where Spark helps and where SQL is enough.

6.1 When to stay in T‑SQL

Keep logic in T‑SQL when:

• Data volume is moderate and fits well in Warehouse compute
• Logic is mostly aggregations and joins on well-indexed tables
• You rely on T‑SQL-only features (certain windowing patterns, procedural bits)

Example: a slowly changing dimension that’s already efficient in T‑SQL may not benefit from a Spark rewrite.

6.2 When Spark notebooks win

Consider Spark for:

• Very large tables where distributed compute helps
• Complex transforms involving JSON, XML, or semi-structured data
• Data science / ML steps integrated into ETL

Basic tuning tips:

• Minimise shuffles: avoid unnecessary repartition / wide joins
• Cache wisely: cache only DataFrames reused across multiple steps
• Write partitioned: partition Delta tables by natural columns (e.g., OrderDate)

(fact_df.write
    .format("delta")
    .mode("overwrite")
    .partitionBy("OrderDate")
    .save("/lakehouse/curated/fact_sales")
)

7. Testing and Validating the Migration

A common risk when rewriting stored procedures is subtle logic drift. Build a simple validation approach.

7.1 Side-by-side comparison

For a period of time, run both:

• Legacy stored procedures on SQL Server
• New Fabric notebooks

Then compare results for key tables:

• Row counts by day
• Aggregate sums (e.g., total sales per month)
• Spot-check sample records

Example validation query (run on both systems, adjust table names):

SELECT
    OrderDate,
    COUNT(*) AS RowCount,
    SUM(Amount) AS TotalAmount
FROM dbo.FactSales
GROUP BY OrderDate
ORDER BY OrderDate;

7.2 Unit-like tests in notebooks

You can add lightweight assertions in notebooks.

from pyspark.sql.functions import col

row_count = fact_df.count()
if row_count == 0:
    raise ValueError("FactSales has zero rows – aborting")

negative_amounts = fact_df.filter(col("Amount") < 0).count()
if negative_amounts > 0:
    raise ValueError(f"Found {negative_amounts} negative amounts – check source data")

If a check fails, the notebook fails, the pipeline fails, and you get a clear error instead of silent bad data.

8. One Practical Takeaway

Don’t try to “lift and shift” each stored procedure into its own notebook. Start by mapping your procedures into a small set of reusable patterns (staging, transform, marts), design a notebook layout and pipeline flow around those patterns, and only then begin rewriting. You’ll end up with a simpler, more testable Fabric solution instead of a 1:1 reimplementation of yesterday’s complexity.