Question

A retail analytics team needs to combine a monthly Excel workbook, a SharePoint-hosted CSV, and a small on-premises SQL Server table into a single cleansed table in a Fabric Lakehouse. The transformation involves column renaming, type conversion, and merging the three sources. The team consists of business analysts who are comfortable with Power Query but have no experience writing PySpark or SQL code, and the data volumes are small (under 100,000 rows total). Which ingestion and transformation approach best fits this team and workload?

Accepted Answer

Author a Dataflow Gen2 that connects to all three sources, applies the transformations in the Power Query editor, and sets the Lakehouse table as the output destination

Answer

Create a Spark notebook that reads each source with the appropriate connector and merges them using PySpark DataFrame joins

Answer

Write a T-SQL stored procedure in a Fabric Warehouse to join and cleanse the sources, then copy the result to the Lakehouse

Answer

Build a KQL query in an Eventhouse to ingest and transform the three sources before landing them in the Lakehouse