Question

A data engineering team is ingesting 40 GB of daily gzip-compressed CSV files from an on-premises SFTP server into a Fabric Lakehouse. The files land in a landing folder, and the team must convert them into partitioned Delta tables by transaction date, apply schema validation, and reject rows with malformed numeric fields. The transformation logic is complex, involving conditional derivations across 30+ columns, and the team includes experienced PySpark developers. Which approach best meets these requirements while keeping the pipeline maintainable and performant at scale?

Accepted Answer

Use a Fabric notebook with PySpark to read the CSV files, apply validation and derivation logic, and write partitioned Delta tables using partitionBy on the transaction date column

Answer

Load the raw CSV files into a KQL database and use update policies to validate and derive columns during ingestion

Answer

Use a Dataflow Gen2 with the Power Query editor to apply all 30+ column derivations and write the result to the Lakehouse

Answer

Use a pipeline Copy activity with a mapping to convert CSV to Delta and rely on the copy schema drift setting to reject malformed rows