Dealing with duplicate rows, gaps, and data that arrives late

Real-world data pipelines in Microsoft Fabric must account for duplicates, nulls, and data that arrives after its expected processing window. Fabric provides tools across Dataflow Gen2, pipelines, and Spark notebooks to detect and remediate these quality issues before data lands in the lakehouse or warehouse. Choosing the right technique (dedup keys, watermarking, imputation) depends on the ingestion pattern (batch vs. streaming) and downstream analytical requirements.

1 · Learn the must-know

• 1Deduplication can be handled in Dataflow Gen2 using the 'Remove Duplicates' transformation, or in Spark/SQL using dropDuplicates(), ROW_NUMBER() window functions, or MERGE/upsert logic on a business key to avoid inserting repeated rows.
• 2Missing values can be handled via Dataflow Gen2's 'Fill Down/Fill Up' and 'Replace Values' transforms, or in Spark using fillna(), dropna(), or conditional imputation logic, depending on whether nulls should be defaulted, interpolated, or excluded.
• 3Late-arriving data in streaming scenarios (e.g., Eventstream or Spark Structured Streaming) is managed using watermarking, which defines how long the engine waits for delayed events before finalizing a windowed aggregation, trading completeness for latency.
• 4For batch pipelines, late-arriving dimension or fact records are typically handled with upsert/MERGE patterns keyed on business/natural keys plus a last-modified or ingestion timestamp, ensuring late updates overwrite or supplement existing lakehouse/warehouse rows rather than duplicating them.
• 5Delta Lake tables (the default table format in Fabric Lakehouse) natively support MERGE INTO, which is the standard mechanism for idempotently applying inserts/updates/deletes when reprocessing or handling late/duplicate data.
• 6A common gotcha: deduplication logic must define which record is 'correct' when duplicates exist (e.g., latest by timestamp) since simple distinct/removal without ordering can silently drop the wrong row.

2 · Check your understanding