Adding pipeline extraction date to appended data rows

[PhilippM]

Hey there,

I succesfully managed to connect a REST API and transfer the data to a Lakehouse table. I want to do monthly snapshots and append the data to the table. The REST API does not return an extraction data field, so I am wondering how I can add the extraction date in an extra column to the appended data rows.

Any ideas would be appreciated!

[NandanHegde]

Is your ask to do this via Copy activity explicitly within Data pipeline?
You can use Dataflow Gen 2 with API as source, do Power Query transformation of adding a new column : Datetime.LocalNow()

https://learn.microsoft.com/en-us/powerquery-m/datetime-localnow

And choose sink as lakehouse

[PhilippM]

Thank you! The issue with dataflow is, that I am unable to connect my REST API, because I have to do two queries. First authorize myself and receive a bearer token, 2nd one to use the bearer token as authorization to do the actual query. At least I did not figure out a way to do this with dataflow.

With data pipeline I can chain the two queries properly. What I did now is saving the raw data I get through data pipelines in a Datalake and transforming it with dataflow as well as adding a additional column with the current date. So it is somewhat a workaround, but it is working.

[NandanHegde · Accepted solution]

You can use Copy activity with REST as Source and add UTCNow() as the Additional Column

[PhilippM]

Exactly what I was looking for! I was completely missing out on this option, thank you very much!