Azure Data Factory | Copy multiple tables in Bulk with Lookup & ForEach

With Azure Data Factory Lookup and ForEach activities you can perform dynamic copies of your data tables in bulk within a single pipeline.
In this episode I will show you how to perform bulk copies with ADF.
Source code for demos: github.com/Mar...
In this episode live demo of ADF lookup activity in SQL to Blob export scenario and how to control this using metadata table.
Next steps for you after watching the video
1. ADF bulk copy scenario
- docs.microsoft...
2. Lookup Activity docs
- docs.microsoft...
3. ForEach Activity docs
- docs.microsoft...
Want to connect?
- Blog marczak.io/
- Twitter / marczakio
- Facebook / marczakio
- LinkedIn / adam-marczak
- Site azure4everyone...

[@rapchak2]

Cannot thank you enough for your incredibly well laid out, thorough explanations. The world needs more folks like you :)

[@satishutnal]

You are the example for how teaching should be. Just awesome 👍

[@AdamMarczakYT]

Wow, thank you!

[@sreejeshsreenivasan2257]

Super helpful . We are breaking our head on how to migrate 32,000 Oracle tables into ADL. this was so simple and helpful

[@albertoarellano1494]

You're the best Adam! Thanks for all the help, been watching your tutorials on ADF and they're very helpful. Keep them coming!

[@AdamMarczakYT]

My pleasure! Thanks Alberto!

[@genniferlyon8577]

Thank you Adam! I had been trying to follow some other written content to do exactly what you showed with no success. Your precise steps and explanation of the process were so helpful. I am successful now.

[@apogeeaor5531]

Thank you, Adam. I rewatch this video at least twice a year, Thank you for all you do.

[@priyankapatel9461]

You have depth knowledge in every service. I learn from scratch using your channel. Keep posting Thanks you and God bless you.

[@AdamMarczakYT]

Awesome, thanks!

[@quyenpn318]

I really really like how you guide step by step like this, it is quite easy to understand. You are the best “trainner” I’ve seen, really appreciated for your time on creating those useful videos.

[@paullevingstone4834]

Very professionally demonstrated and very clear to understand. Thank you very much

[@AdamMarczakYT]

It's my pleasure Paul! :)

[@Ro5ho19]

Thank you! It's under appreciated how important it is to name things something other than "demoDataset", but it makes a big difference both for understanding concepts, and maintainability.

[@AdamMarczakYT]

Glad it was helpful! You are of course correct, if it's not demo then take care of your naming conventions.

[@ericsalesdeandrade9420]

Amazing video. Complex topic perfectly explained. Thank you Adam

[@Vick-vf8ug]

It is extremely hard to find information online about this topic. Thank you for making it easy!

[@AdamMarczakYT]

Glad it was helpful! Thanks!

[@frenamakenson9844]

Hello Adam, thanks for this demo, Your Channel is A bless for new learner

[@shaileshsondawale2811]

What a wonderful content you have place in social media.. What a world class personality you... People certainly fall in love with your teaching..

[@naseemmca]

Adam you are just awesome man! The way you are teaching is excellent. Keep it up.. you are the best...

[@AdamMarczakYT]

Thanks! 😃

[@maimemphahlele1102]

Hi Adam Ur videos are just too brilliant. This is subscription I wouldn’t mind paying to support. Ur lessons are invaluable to learning.

[@AdamMarczakYT]

Awesome, thank you!

[@anderschristoffersen2513]

Great and simple walk through, good job Adam

[@AdamMarczakYT]

Thank you, I appreciate it! :)

[@pratibhaverma7857]

Your videos are great. This is the best channel on KZbin platform to learn about ADF. THANKS 🙏😊

[@waseemmohammed1088]

Thank you so much for the clear and nice explanation, I am new to ADF and learning a lot from your channel

[@AdamMarczakYT]

Great to hear!

[@gunturulaxmi8037]

Videos are very much clear to the people who would like to learn and practice.Thanks alot.your hard work is appreciated.

[@radiomanzel8570]

it was so perfect , I was able to follow and copy data in first attempt .thanks

[@amoldesai4605]

I am a beginner in Azure Data Engineering and you made it simple to learn all the tactics.. thanks

[@AdamMarczakYT]

Glad to hear that!

[@anilchenchu1017]

Awsome adam there cant be a way to explain better than this

[@xiaobo1134]

Thanks Adam, your tutorials are very useful, hope to see more in the future

[@AdamMarczakYT]

Glad you like them! Will do more!

[@vicvic553]

Hey, one thing about English - please guys correct me if I am wrong, but I am pretty sure what I am talking about - you shouldn't say inside a sentence "how does it work", but "what it works". Despite that, the content is awesome!

[@AdamMarczakYT]

You can if you ask a question. "How does it work" is a question structure, not a statement. it should be "how it works" if I'm stating a fact. You wrote "What it works" but I assume that's a typo. It's one of my common mistakes, my English teacher tries to fix it but it is still a common issue for me ;) Thanks for watching!

[@amtwork5417]

Great video, easy to follow and to the point, really helped me to quickly get up a running with data factory.

[@AdamMarczakYT]

Glad it helped!

[@wouldyoudomeakindnes]

your skills are in the tops thanks, love to see your channel grow

[@AdamMarczakYT]

I appreciate that!

[@eversilver99]

Excellent video and knowledge sharing. Great Job!

[@AdamMarczakYT]

Glad you enjoyed it!

[@geoj9716]

You are a very good teacher.

[@AdamMarczakYT]

Thank you! 😃

[@ElProgramadorOficial]

Adam, You are the best!. Thanks man!

[@AdamMarczakYT]

Thank you :)

[@avicool08]

very simple yet powerful explanation

[@AdamMarczakYT]

Glad you think so!

[@sumanthdixit1203]

Fantastic clear-cut explanation. Nice job!

[@AdamMarczakYT]

Glad it was helpful!

[@markdransfield9520]

Brilliant teaching style Adam. Very watchable. I particularly like how you explain the background. I've subscribed and will watch more of your videos.

[@ahmedroberts4883]

Excellent, Excellent video. This has truly cemented the concepts and processes you are explaining in my brain. You are awesome, Adam!

[@anacarrizo2209]

THANK YOU SO MUCH for this! The step-by-step really helped with what I needed to do.

[@jakirajam]

The way you explain is super Adam. Really nice

[@RavinderApril]

Incredibly simplified to learn. .. Great!!

[@garciaoscar7611]

This video was really helpful! you have leveled up my Azure skills, Thank you sir, you have gained another subscriber

[@mateen161]

Very well explained. Thank you!

[@AdamMarczakYT]

Glad it was helpful!

[@TheSQLPro]

Great content, easy to follow!!

[@AdamMarczakYT]

Glad you think so!

[@eatingnetwork6474]

Thanks Adam, amazing workshop, very clear and easy to follow, thanks for helping, i am wiser now :)

[@AdamMarczakYT]

Perfect! Thank you!

[@rajanarora6655]

Awesome explanation, the way you teach assuming in layman terms is pretty great, thanks!!

[@dev.gaunau]

Thank you so much for sharing these valued knowledge. It's very helpful for me.

[@AdamMarczakYT]

Glad it was helpful!

[@santanughosal9785]

I was looking for this video. Thanks for making this. It helps a lot. Thanks again.

[@deoroopnarine6232]

Your videos are awesome man. Gave me a firm grasp and encouraged me to get an azure subscription and play around some more.

[@AdamMarczakYT]

That is amazing to hear! Thank you!

[@veerboot81]

Hi Adam, very nice work this, I made this for a client of mine and found out one important thing: within the For each not all blocks are executed as if they are working together atomically. What I mean is that if you start two thinks in parallel using the For Each block and within the for each block you have two blocks - say block A and B - connected using parameters (item()) within these blocks say X and Y. Block A starting with item X will not necessarily be using the item X in block B although connected! So I want to suggest one extra advice to use only one block in a For Each block at max if using parameterazed block within or if you need more than one block start a separate pipeline within the For Each block which will have the multiple blocks. These pipelines will be started as separate childs and to the work in the correct order. With kind regards, Jeroen

[@AdamMarczakYT]

Hey, not sure I understood what you meant here. Using parameters is not making any connection between the actions.

[@veerboot81]

@@AdamMarczakYT I'm using a for each loop to load tables with dynamic statements, if I need more than one block (like a logging call to sl server, a copy block to load the data and a logging block after being done with loading these blocks can be in the for each loop itself, but if you start in parallel multiple times different load of tables the blocks will not follow each other sequencially, but will be running through each other, so the logging will not belong to the copy block for example. I will see if I can make an example if I find the time. To solve this I start always another pipeline within the for each and put the blocks in this pipeline. This will create child pipelines in the for each loop ensuring the right order of execution of the blocks (logging start, copy and logging end)

[@rubensanchez6366]

Very interesting video Adam. I found quite enlightening your idea of storing metadata. probably it could be maintained separately tracking last record loaded so we could use it as an input for delta loads through queries instead of reloading the full table on each run.

[@AdamMarczakYT]

You can either use watermark or change tracking patterns check this out docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-overview?WT.mc_id=AZ-MVP-5003556

[@CoolGuy]

You are a legend. Next level editing and explanation

[@e2ndcomingsoon655]

Thank you! I really appreciate all you share, it truly helps me

[@amarnadhgunakala2901]

Thanks Adam, I'm waiting like this video on ADF, Please do regularly...

[@AdamMarczakYT]

You got it!

[@hollmanalu]

Adam, thanks for all your great video's! I appreciate your work very much! Keep up your great work!

[@AdamMarczakYT]

My pleasure! Thanks!

[@RajivGuptaEverydayLearning]

Very nice video with good explanation.

[@AdamMarczakYT]

Glad you liked it

[@pdsqsql1493]

Wow! What Great video, very easy way step by step tutorials and explanations. Well done!

[@verakso2715]

Thanks for your awesome video, it helped me out a great deal

[@AdamMarczakYT]

Glad I could help

[@shivangrana02]

You are really best Adam! Your tutorial helped me a lot. Thanks

[@AdamMarczakYT]

Happy to hear that!

[@shivangrana02]

@@AdamMarczakYT You are welcome. Please keep up the good work.

[@AVADHKISHORE]

Thank you Adam!! These videos are really very helpful and builds the foundation to understand ADF.

[@AdamMarczakYT]

My pleasure!

[@nathalielink3869]

Awesome. Thank you so much Adam!

[@AdamMarczakYT]

My pleasure!

[@elisehunter3424]

Brilliant tutorial. Easy to follow and it all works like a charm. Thank you!!

[@jacobklinck8011]

Great session!! Thanks Adam.

[@AdamMarczakYT]

My pleasure!

[@NewMayapur]

fantastic video Adam!! Really helpful to understand the parametrisation in ADF.

[@AdamMarczakYT]

Great to hear that!

[@Lucas48288]

These tutorials are so useful!

[@AdamMarczakYT]

Glad you like them!

[@jozefmelichar5960]

Fine tutorial. Thanks.

[@AdamMarczakYT]

Glad it was helpful!

[@leonkriner3744]

Amazingly simple and informative!

[@szymonzabiello2622]

Hey Adam. Great video! Two questions regarding the pipeline itself. 1. How do we approach Source Version Control of the pipeline? In SSIS we could export a package and commit to Git or use TFS. How do we approach versioning in Azure? 2. What is the approach to deploy this pipeline in upper environment? Assuming that this pipeline was created in dev, how do I approach deployment in i.e. UAT?

[@AdamMarczakYT]

I think this page describes and answers both of your questions. docs.microsoft.com/en-us/azure/data-factory/continuous-integration-deployment?WT.mc_id=AZ-MVP-5003556 thanks for watching :)

[@Cheyenne9663]

Wow this was explained so well. Thank you!!!

[@sameeranavalkar9352]

Thank you so much for this. It helped a lot

[@GaneshNaik-lv6jh]

Thank You so much.... Very good explanation, Just Awesome

[@ashokveluguri1910]

You are awesome Adam. Thank you so much for detailed explanation.

[@AdamMarczakYT]

My pleasure!

[@southernfans1499]

👍👍👍 very good explanation.. 👍👍.

[@gouravjoshi3050]

Good one adam sir .

[@AdamMarczakYT]

Thank you :)

[@prasadsv3409]

Realy great stuff sir.this what am looking in youtube

[@AdamMarczakYT]

Thanks a ton!

[@leelaagrawal6375]

great teacher

[@AdamMarczakYT]

Thank you! 😃

[@pavankumars9313]

You are very good 👍 explained well thanks 😊

[@aks541]

Very well explained & succinct. One request - if possible create a video for loading ADW (Synapse) data-warehouse by ADF

[@AdamMarczakYT]

Thanks! I'm waiting for synapse new workspace experience to be released to make video about it ;)

[@satyabratabarik49]

Great Explanation !!!!

[@dataisfun4964]

Thanks, this is beautiful.

[@ivanovdenys]

It is really cool that you make it so simple :)

[@AdamMarczakYT]

Thank you! 😊

[@kamalnathvaithinathan5737]

Awesome Adam!! you are the best. Thank you so much

[@AdamMarczakYT]

My pleasure! :)

[@feliperegis9989]

Hey Adam, awesome work and explanation! Do you have another video explaining how to deal with massive data copies from tables in bulk using ADF and that may resolve issues with maximum data or rows of data? Can you make a video with a demo explaining how to deal with this kind of scenarios that you mentioned that's the story for another day? Thanks a lot in advance!! =D

[@AdamMarczakYT]

Thanks. Well, Lookup shouldn't be used for data, but for metadata driven approach, so 5000 rows limit is very good here. It is rare when you will copy over 5000 tables/files with different structure)/etc. If you do you can do different techniques but in those cases I probably would shift approach entirely. Will think about this.

[@mosestes9417]

Really helpful! you made it very easy!

[@AdamMarczakYT]

Glad you think so!

[@abhishektrivedi3406]

You're awesome Adam, thanks for such a great tutorial. I also tweeted this video. Thanks.!!

[@AdamMarczakYT]

Awesome, thank you!

[@krzysztofrychlik9913]

dzieki! bardzo pomocne filmy!

[@AdamMarczakYT]

Dziekoweczka!

[@agnorpettersen]

Very good explanation! I will try to read a list of tables but not export but do mask certain columns. I guess i have to use a derved column inside the for each loop maybe. Three parameters: schema_name, table_name and column_name. But how to make: update . set = sh2() where Key in (select Key from othertable) in a derived column Name context?

[@agnorpettersen]

I reseached and tried one thing. Seems to work. Have the lookup and foreach like this video and inside the loop i put the function. There I make an dynamic sql statement picking up the variables from the table i have in lookup.

[@surafeltilahun7404]

Excellent!!!!!

[@AdamMarczakYT]

Many thanks! Cheers!

[@BACHARBOUAZZA]

Thank you! well done.

[@marvinvicente3138]

Hi Adam, I really appreciate you video. Thanks for your videos! I hope you can also create a video for ODBC as data source.

[@maranp6618]

Please do a video on Parameterized pipeline in detail with different parameters

[@AdamMarczakYT]

Did you had a chance to check my video on ADF parametrization? kzbin.info/www/bejne/pnq2c5qtp8mrhq8

[@matthewmark7224]

amazing work. thanks.

[@jac94]

Thanks Adam! very clear!

[@AdamMarczakYT]

Glad it was helpful!

[@shankysingh1141]

First of all thank u so much for very useful video.can you please create some video where we can connect ADF output file to powerBI for reporting and how we can auto refresh the same.

[@arjunsaagi590]

Thank you Adam, Very much informatics video

[@AdamMarczakYT]

My pleasure!

[@simonscott1121]

Reminds me so much of SSIS.

[@shruthil7913]

Hi Adam, there is a way to copy in different tables in SQL. (Example : car csv to cars SQL table and Plane csv to plane csv table automatically, without manual intervention.

[@AdamMarczakYT]

Hi there. Well this video is all about it, just reverse the order and use GetMetadata for blob storage action instead of the Lookup :)

[@silent-ghost]

Great video but where is the story for another time about large lookup scenarios that require multiple pipelines? I'm in dire need of that solution! :)

[@AdamMarczakYT]

At that point you simply would need an Azure architect to design the best solution for you. There is no single solution that works best in all cases. In larger solution you need experienced architect to understand all requirements and design the best solution available.

[@silent-ghost]

@@AdamMarczakYT Thank you for your reply but I think it would be of great interest to all of us if you could present a simple scenario where a large json file with more than 5000 ids (being used in an API call that accepts 500 ids at once) is being processed by this nested pipelines approach. There is no such example as of now anywhere....

[@nalinbuddhika]

Thanks for the vidoes. I am letting the adds runs till the end :)

[@AdamMarczakYT]

Awesome thank you for your support! ♥

[@Dadwalfamilyadventures]

Hi Adam, this is awesome session. One question: Where in Azure documentation I can find the information regarding all possible output variable of a given activity e.g. when you were explaining about lookup activity, you talked about the property "firstrow". Where can I find such properties supported by all activities in Azure documentation ?

[@AdamMarczakYT]

Thanks! For details always check the docs by googling "ADF action". For lookup this would pop up which explains everything you asked docs.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity

[@JD-xd3xp]

Very nice video, I appreciate that. do you have follow-up video for incremental copy?

[@AdamMarczakYT]

Not yet but check this tutorial from MS docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-overview?WT.mc_id=AZ-MVP-5003556 thanks for stopping by :)

[@YenBui-dn2ek]

Thanks for your great video. Is there any limitation in terms of a number of tables, or table size when we use copy multiple in bulk according to your experience?

[@abramswee]

thanks for teaching us, but can you please teach us how to do incremental load using ADF, either by datetime or change_tracking?

[@AdamMarczakYT]

Check this out docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-overview?WT.mc_id=AZ-MVP-5003556

[@sunkara2009]

Best Video watched today, Crystal clear !!. Thanks Adam. I am trying to copy multiple Azure Table storages. But struggling to get the query to list table names for azure table storage in the lookup activity. Any solutions please? Thanks in Advance!!

[@AdamMarczakYT]

Excellent. Well, in this case replace lookup with Web Activity and query table storage rest api docs.microsoft.com/en-us/rest/api/storageservices/query-tables?WT.mc_id=AZ-MVP-5003556 and loop over the response.