Question

A retail company ingests daily order files into a Fabric lakehouse Delta table. Due to an upstream system retry mechanism, some order records are delivered multiple times across different daily files, and occasionally an order file arrives a day late containing corrected versions of orders already loaded earlier. Each order has a unique OrderId and a LastModifiedTimestamp. The engineering team needs the Bronze-to-Silver PySpark transformation to keep only the most recent version of each order and eliminate duplicates. Which approach best meets this requirement?

Accepted Answer

Use a MERGE operation keyed on OrderId, updating the target row only when the source LastModifiedTimestamp is greater than the existing target timestamp.

Answer

Append all incoming records to the Silver table and rely on Delta's automatic deduplication during OPTIMIZE compaction.

Answer

Apply dropDuplicates on OrderId before writing, then overwrite the entire Silver table on each run.

Answer

Filter the source to include only records whose ingestion date equals the current processing date, then append them to the Silver table.