Data Flow Gen 2 Issue

[Karthick_Balaje]

I used Dataflow Gen 2 to push the data from bronze to Silver with a little transformation (wrote a basic logic to perform Distinct Filtered Rows" operation - Filtering rows based on a condition (Primary Key not null and not 0)). When I select save and run, after validation, the dataflow gen 2 runs for a while, and it fails. Even after failing, I see most of the tables are pushed to Silver, and one table with 4 million rows is alone pushed. Why? 

Because of this, I am unable to run my main pipeline, and sometime I see there are duplicate rows at my target destination even after selecting "Replace" as an update method (whenver the dataflow gen 2 runs).

- Thanks,
Karthick

[v-prasare]

Hi @Karthick_Balaje ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

@BhaveshPatel & @AntoineW  thanks for your prompt response.

Thanks,

Prashanth Are

MS Fabric community support

[AntoineW]

@Karthick_Balaje,

If I understand well, you have perform a transformation to deduplicate a column but you also have deplucate in the destination. 

- Because by selecting the update method as "Replace", it will normally replace all records. Make sure your 4M-row entity is truly set to Replace (and not “Append” on an existing table with manual settings)

- If two dataflows/pipelines write to the same table (or a new run starts before the previous one finishes), you can get duplicates—especially if any writer uses Append. Make sure in the pipeline or others artefacts that is using the table, there is nothing in the "append" mode.

- Otherwise, create a tickets to microsoft support team.

Best regards,

Antoine

[Karthick_Balaje]

Hi @AntoineW, From Source (Azure SQL DB) to Bronze, I did a copy data activity here, and I saw dara getting duplicated after ingestion. Then I wrote a de-duplication logic in Dataflow gen 2 to move the data from Bronze to Silver (See the attached screenshot) to remove the duplicates. Then, again, from Silver to Gold, I made transformations with regards the required business KPIs, I made transformations and created custom columns to build KPIs using Dataflow gen 2 and pushed it to gold. When i queried the data in Gold Warehouse SQL analytics endpoint, I saw duplicates again, and I have wrote SQL logics for de-duplication. 

For the 2 dataflow Gen 2, the update method was replace the entire table in the target destination (see the attached screenshot).

[v-prasare]

Hi @Karthick_Balaje,

Did your issues got resolved or you still need any help on above scenario of yours

Thanks,

Prashanth

[BhaveshPatel]

@Karthick_Balaje Thank you. You should not have to move data from bronze to silver.

There are two ways you can achieve this: One you should use Power BI Data flow Gen 2 or the second option is Use Notebooks and divide that into bronze, silver and gold layer.

If I use Power BI Dataflow Gen 2, then do all the transformations and save the data as a Fabric SQL Database. 

one task at a time. ( One table at a time ). It can handle billions of rows at a time in a single table. 

[Karthick_Balaje]

@v-menakakota 
@BhaveshPatel 
@Vinodh247