ETL Process PostgreSQL in Azure Data Factory

Question

ETL Process PostgreSQL in Azure Data Factory

Andres Esteban 51

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-MSFT 53,261 Reputation points Microsoft Employee

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.

PRADEEPCHEEKATLA-MSFT 53,261 Reputation points Microsoft Employee

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,136 Microsoft Employee

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

Andres Esteban 51 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,136 Reputation points Microsoft Employee

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 51 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?

ETL Process PostgreSQL in Azure Data Factory

1 answer

Activity