Choosing a Fabric transformation engine: Dataflow Gen2, notebooks, KQL or T-SQL

Once data has landed, it has to be cleaned and shaped. Fabric offers four main transformation engines, and the right one depends on where the data lives, the skills of the team and the type of transformation.

Comparison

Learn’s decision guides describe notebooks as code-first for complex transformations, Dataflow Gen2 as code-free with high transformation support, and eventstreams as medium transformation support for streams; pipelines and Apache Airflow jobs orchestrate but don’t transform.

T-SQL transformation in the warehouse

T-SQL ingestion reads tables in the same workspace by three-part name (warehouses, lakehouse SQL analytics endpoints, mirrored databases) and external files with OPENROWSET. COPY INTO gives the highest throughput for files in ADLS Gen2, Blob Storage or OneLake (CSV, JSONL, Parquet) and can use the workspace identity for source access.

CREATE TABLE gold.DailySales
AS
SELECT CAST(o.OrderDate AS DATE) AS OrderDate,
       p.Category,
       SUM(o.Amount) AS TotalAmount
FROM SalesLakehouse.dbo.Orders AS o
JOIN RefWarehouse.dbo.Product AS p ON o.ProductID = p.ProductID
GROUP BY CAST(o.OrderDate AS DATE), p.Category;

This CTAS reads a lakehouse table and a warehouse table in the same workspace and writes an aggregated table in the current warehouse. Learn advises against singleton INSERT statements for ingestion because they harm query and update performance; use set-based CTAS or INSERT...SELECT.

KQL transformation at ingestion

In an eventhouse, an update policy runs a function whenever data is ingested into a source table and writes the result to a target table:

.create function ParseRawLogs() {
    RawLogs
    | parse Record with "[" Timestamp:datetime "] " Level:string " " Message:string
    | project Timestamp, Level, Message
}
.alter table CleanLogs policy update
@'[{"IsEnabled": true, "Source": "RawLogs", "Query": "ParseRawLogs()", "IsTransactional": true}]'

The function parses a raw string column into typed columns; the policy on CleanLogs applies it to every ingestion into RawLogs. IsTransactional defaults to false; setting it to true means a failure in the policy also fails ingestion into the source table, which Learn recommends in production to avoid silently losing rows from the target. Failures appear in .show ingestion failures. Update policies, materialised views and KQL transformation syntax are covered in Chapter 10, “Transforming data with T-SQL and KQL”.

PySpark and Dataflow Gen2 in brief

Notebooks read lakehouse tables and files directly within the Fabric security context and write Delta tables back; they can be scheduled or orchestrated with a pipeline Notebook activity (Chapter 9, “Transforming data with PySpark and Spark SQL”). Dataflow Gen2 can run on a schedule or as a pipeline activity, writes to destinations such as lakehouse and warehouse tables, and stages data in workspace items whose names start with DataflowStaging, which shouldn’t be deleted.

Common trap: Planning UPDATE or MERGE statements against a lakehouse through its SQL analytics endpoint - the endpoint is read-only for data; use Spark (Delta MERGE) in the lakehouse, or load the data into a warehouse for T-SQL DML.

Common trap: Choosing a pipeline as the transformation engine - Learn’s decision guide lists pipelines (and Apache Airflow jobs) as orchestration with no transformation support; the transformation runs in the activities they call, such as a notebook, a dataflow, a stored procedure or a script.

Get the whole book

This note is one section of Ultra Transcenders DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · DP-700 terms in the glossary · All DP-700 study notes