ETL Process PostgreSQL in Azure Data Factory

Question

ETL Process PostgreSQL in Azure Data Factory

Andres Esteban 156

Hello community

I have to perform an ETL process which consists of copying certain data (i.e. filtering is required) from a PostgreSQL DB to Azure PostgreSQL via Azure Data Factory, and ideally only new records should be copied once a day. I have been reading and as far as I can see there are two options:

Create a Power Query: would this be possible for Postgres and would it be possible to only copy the new data with each query?
Create a Lookup + copy data: would it be possible for Postgres and would it be possible to copy only the new data with each query?

The query I make to visualise the data I want is something similar to this:

SELECT *
FROM table
where column1='condition'
and user_name<>xxx@microsoft.com'

Is an ETL process doing this kind of queries possible in Azure Data Factory?

Thank you

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-01-10T05:27:39.517+00:00
Hello @Andres Esteban ,

Following up to see if the below suggestion provided by @MarkKromer-MSFT was helpful. And, if you have any further query do let us know.

Please don't forget to click on or upvote button whenever the information provided helps you.

2 answers

Your answer

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-01-10T05:27:39.517+00:00

Hello @Andres Esteban ,

Following up to see if the below suggestion provided by @MarkKromer-MSFT was helpful. And, if you have any further query do let us know.

Please don't forget to click on or upvote button whenever the information provided helps you.

Answer 1

MarkKromer-MSFT 5,226 Microsoft Employee Moderator

Yes, you can do this using a source query either from a Copy Activity or a Data Flow inside your ADF pipeline

Andres Esteban 156 Reputation points

2023-01-08T12:45:43.773+00:00

Could you give me an example of where the query should be included? Also, could you help me with the issue of only copying new data?

Thank you very much
MarkKromer-MSFT 5,226 Reputation points Microsoft Employee Moderator

2023-01-08T22:01:50.217+00:00

In data flow activities, under source options, choose "query" for input and select "Incremental column":
Andres Esteban 156 Reputation points

2023-01-13T13:21:36.2966667+00:00

Hello and thank you for your answer. I still have the following problem, as the source is in a local PostgreSQL it won't let me add it as a source in a dataflow so the only method left would be directly a Copy Data Pipeline (is it correct?). Well, if I'm right, continuing to do my Copy Data in the source does not let me add the incremental column, so how can I do it?

I remember that my query is to copy the data from this table to another one with two conditions and only the new data. Could you give me an example of the query I would have to do?

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

ETL Process PostgreSQL in Azure Data Factory

2 answers

Your answer