Difficulty: Medium | Concepts: Fabric Data Pipeline, Copy Activity, Dataflow Gen2, Delta MERGE, Deduplication

"How would you load a flat file into a Lakehouse and remove duplicates?" is a deceptively simple Fabric Pipeline interview question — the trap is that Copy Activity alone can't dedupe, so interviewers use it to check whether you know when to bring in a Notebook or Dataflow.

Scenario

A vendor drops a daily CSV file into an Azure Blob container. The file sometimes contains duplicate rows (same CustomerID + OrderID appearing twice due to upstream retries). You need a Fabric Pipeline that ingests the file and lands a clean, deduplicated Delta table.

Sample Source File

CustomerID,OrderID,Amount,OrderDate
C001,O5001,120.00,2026-06-28
C002,O5002,75.50,2026-06-28
C001,O5001,120.00,2026-06-28   -- duplicate row
C003,O5003,300.00,2026-06-29

Why Copy Activity Alone Isn't Enough

Component	Can it dedupe rows?
Copy Activity	No — it's a pure data-movement engine, no row-level transformation
Dataflow Gen2	Yes — via Power Query "Remove Duplicates" step
Notebook (PySpark)	Yes — via `dropDuplicates()` or window functions

Pipeline Design

Copy Activity: source = Blob CSV, sink = Lakehouse Files/bronze/orders_raw (land it as-is, duplicates included — never transform in the same step you land raw data).
Notebook Activity: read the bronze file, dedupe, write to a silver Delta table.
Chain them with a success dependency so the Notebook only runs after Copy succeeds.

Notebook Solution (PySpark)

df = spark.read.option("header", True).csv("Files/bronze/orders_raw")

dedup_df = df.dropDuplicates(["CustomerID", "OrderID"])

dedup_df.write.format("delta") \
    .mode("overwrite") \
    .saveAsTable("orders_silver")

Alternative: Dataflow Gen2 Approach

If the team is low-code / Power Query-first, the same result is achieved with a Dataflow Gen2: connect to the CSV, add a Remove Duplicates step on the CustomerID + OrderID columns, then set the output destination to the Lakehouse table. This is often the answer interviewers expect from BI-leaning candidates, while Notebook-based dedup is expected from data-engineering-leaning candidates — mentioning both shows range.

Visual Representation

Blob Storage: orders.csv (4 rows, 1 duplicate) | Copy Activity (no transformation) | Lakehouse/Files/bronze/orders_raw (raw, 4 rows) | Notebook: dropDuplicates(["CustomerID","OrderID"]) | Lakehouse Table: orders_silver (clean, 3 rows)

Common Mistake to Avoid

A frequent mistake is deduping on all columns instead of a proper business key. Two legitimately different orders can accidentally share every column value by coincidence, or a single order can have a slightly different Amount due to a currency rounding fix — dedupe on the natural/business key (CustomerID + OrderID), not a blanket distinct().

Conclusion

The interview signal here is architectural: land raw data untouched with Copy Activity, then dedupe downstream in a Notebook or Dataflow — never try to force transformation logic into the Copy Activity itself.