Copy only new data via a copy data pipeline from PostgreSQL to Azure PostgreSQL

Question

Hello community:

I am trying to copy a number of tables from PostgreSQL to Azure PostgresSQL using Azure DataFactory. To do this, I create a pipeline and configure the input and output, the source table and the copied table are exactly the same. As I have a primary key, when the trigger is executed, I get an error because it copies all the data and not only the new data and therefore duplicates the primary key values, which is impossible.

My question is how to configure this pipeline so that it only copies the new data. The Primary Key column is the ID column, whose values are not incremental but unique.

It is not possible to make a DataFlow because it is not allowed as a PostgreSQL data source.

Here is a schematic of what I have set up

Thank you in advance

Answer 1

Hey,
you can use the concept of watermark :
https://www.sqlservercentral.com/articles/incremental-data-loading-using-azure-data-factory
for incremental data loading.

Answer 2

Hi @Andres Esteban ,
Thankyou for posting your query on Microsoft Q&A platform .
My understanding about your query is that you want to load incremental data from OnPremise PostGreSQL server to Az PostGreSQL server. The problem statement here is that there is no watermark column and the primary key is also not incremental column .

In order to achieve this requirement, you might need to introduce a temp table which can be treated as a full load table for the source data.
Post doing a full load to that table , you can perform MERGE i.e. Upsert on the destination table.

Click here to know about MERGE statement in PostGreSQL .

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators