Forum Discussion
[Fabric] How can I load incremental data from a PostgreSQL database using Dataflows or Data Factory?
Hi luis_quiroz
In the following links, you can learn how to incrementally load data from data Warehouse to Lakehouse in fabric.
Here are the important steps to create this solution:
Select the watermark column. Select a column in the source data table that can be used to slice new or updated records for each run.
Prepare a table to store the last watermark value in the data warehouse.
Create pipelines using workflows.
You can view the link below for more details:
Incrementally load data from Data Warehouse to Lakehouse - Microsoft Fabric | Microsoft Learn
I hope you found this helpful.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the information. Unfortunately, it is not possible for us to implement this solution because several of our tables do not have a watermark. What we have found works best for loading data from PostgreSQL is using the WAL.
Not really an expert on the WAL of postgres, but would it make sense to try and leverage a native query within dataflow to the Postgresql WAL to discover what has changed and use that as an filter on the source data in combination with a append only data destination?
Is there an incremental option that does not require adding a date field to each table? For example, similar to how incremental replication schemes work in services like DMS. The current solution implies that I must ensure each object has date fields or records the last update. This is something that PostgreSQL can handle by consuming the WAL, which truly guarantees the recording of database changes and replicates them integrally.
Under the current scheme, if I have a 100GB table that takes 3 hours to transfer all the data, each time I want to update or refresh the data, I will have to wait that amount of time because it performs a full refresh. This is not optimal operationally in terms of timing with the users.
