I would use below approach
- Self hosted IR on your onPremise Network
- Use ADF Metadata Driven Copy Activity to incrementally read data from on premise and load that into files
- Store those files in ADLS
- Use Polybase to read these files and leverage ELT to transform and load the data in Synapse Dedicated Pool or you can use Spark pool to further process these files.
Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how