3.2.2. Transformation Tools: Dataflows, Notebooks, KQL, T-SQL

💡 First Principle: Each transformation tool has a sweet spot—match the tool to the data volume, transformation complexity, and team skills. Using Dataflow Gen2 for petabyte-scale transformations will fail; using notebooks for simple column renames is overkill.

Comparative Table: Transformation Tools

Dataflow Gen2 build steps: Get data (connect to the source), shape the query in the Power Query editor, add a data destination (lakehouse, warehouse, ...) with update method Append or Replace, then publish and refresh or schedule. Staging (per query) lands data in an internal staging lakehouse so heavy transformations can run on Fabric SQL compute instead of in-memory mashup; it is required for a Warehouse destination and off by default for a Lakehouse destination.

T-SQL Transformation Example

-- Create transformed table from staging
CREATE TABLE dbo.FactSales AS
SELECT 
    d.DateKey,
    p.ProductKey,
    c.CustomerKey,
    s.Quantity,
    s.UnitPrice,
    s.Quantity * s.UnitPrice AS SalesAmount
FROM staging.Sales s
JOIN dbo.DimDate d ON s.OrderDate = d.Date
JOIN dbo.DimProduct p ON s.ProductID = p.ProductID
JOIN dbo.DimCustomer c ON s.CustomerID = c.CustomerID;

Spark Transformation Example

# Read a CSV with a header row: header uses row 1 as column names, inferSchema detects types
# (without them the header becomes a data row and every column is a string)
df_raw = spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("Files/raw/sales/*.csv")

# Read source data
df = spark.read.format("delta").load("Tables/staging_sales")

# Transform: Add calculated column, filter nulls
df_transformed = df \
    .withColumn("SalesAmount", col("Quantity") * col("UnitPrice")) \
    .filter(col("CustomerID").isNotNull())

# Write to lakehouse
df_transformed.write.format("delta").mode("overwrite").save("Tables/fact_sales")