Picking a transformation tool from dataflows, notebooks, KQL, or T-SQL

Microsoft Fabric offers multiple transformation engines, and the exam expects you to pick the right one based on data volume, skill set, and target workload. Dataflows Gen2 suit low-code, Power Query-based ETL; notebooks suit large-scale, code-first transformations with Spark; KQL is for high-velocity log/telemetry analytics in Eventhouse/KQL databases; T-SQL is for relational transformations in the Warehouse and SQL analytics endpoint of the Lakehouse.

1 · Learn the must-know

• 1Dataflows Gen2 use the Power Query engine, are best for citizen developers and smaller-to-medium datasets, and can output directly to a Lakehouse, Warehouse, or other destinations via data destinations.
• 2Notebooks (PySpark, Spark SQL, Scala, R) run on Spark compute and are the preferred choice for large-scale, complex, or custom transformations requiring full programmatic control and reuse of existing code.
• 3KQL (Kusto Query Language) is used specifically for querying and transforming data in KQL databases/Eventhouses, optimized for time-series, streaming, and log/telemetry data with fast ingestion and near real-time analytics.
• 4T-SQL is used in the Fabric Warehouse (read-write) and via the SQL analytics endpoint of a Lakehouse (read-only) for set-based, relational transformations by users with traditional SQL skills.
• 5Dataflows Gen2 can have higher compute cost and slower performance at scale compared to notebooks, so large or performance-critical pipelines often favor Spark notebooks instead.
• 6You cannot write to a Lakehouse table via T-SQL through the SQL analytics endpoint: write operations to Lakehouse require Spark (notebooks) or Dataflows Gen2, while the Warehouse itself fully supports T-SQL DML.

2 · Check your understanding