Forum Discussion
Loading data with append option using dataflow and problem with the deleted rows
Hi anusha_2023
Thank you very much frithjof_v and lbendlin for your prompt reply.
Your plan for handling stream updates and deletions is comprehensive.
Some suggestions for reducing computing overhead:
Make sure your Lakehouse table has a primary key constraint, which will prevent duplicate rows from being inserted.
Deduplication logic can be implemented directly in the data flow. Use Power Query to remove duplicates based on the primary key before loading the data into Lakehouse.
The separation of ETL processes into staging and transforming data streams can help optimize refresh times and reduce computational overhead.
Periodically monitor the performance of data streams and optimize queries to ensure efficient data processing.
Best practices for creating a dimensional model using dataflows - Power Query | Microsoft Learn
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi anusha_2023
Perhaps you can consider configuring incremental refreshes.
Make sure your data extraction process is incremental, meaning it only gets new records or changed records since the last load.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi anusha_2023
Thank you very much frithjof_v and lbendlin for your prompt reply.
Your plan for handling stream updates and deletions is comprehensive.
Some suggestions for reducing computing overhead:
Make sure your Lakehouse table has a primary key constraint, which will prevent duplicate rows from being inserted.
Deduplication logic can be implemented directly in the data flow. Use Power Query to remove duplicates based on the primary key before loading the data into Lakehouse.
The separation of ETL processes into staging and transforming data streams can help optimize refresh times and reduce computational overhead.
Periodically monitor the performance of data streams and optimize queries to ensure efficient data processing.
Best practices for creating a dimensional model using dataflows - Power Query | Microsoft Learn
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for the input. I end up with three elements in my pipeline.
The first dataflow extracts the latest transactions and appends them to the table in the Lakhouse.
The second dataflow is getting the deleted transactions IDs or transactions in the source table and not in the destination lakehouse and saving them in the staging Lakehouse.
In the third step, Notebook is cleaning the deleted id's first and then deleting the duplicated rows based on the latest date field for the primarykey.
I thought of changing the first dataflow, before appending the new transactions into Lakhouse check for the duplication, but I cannot find the solution. Let me know if you find any further improvement steps in this process.
Thank you!
I think in the long run it would be better to use Parquet time travel, or proper CDC.
