Multiple table copy as one storage

Question

In the source, we have multiple tables and my requirement is in target after migrating to Azure I do not want to create all those tables in the landing zone, I want to merge all these tables in to one table. I am working in banking domain, we have daily and weekly and monthly and I want all the daily in to one single table and monthly in to single monthly etc for better performance.

Answer 1

Hi Anshal ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

It seems like you want to merge multiple tables from source into one table at destination, combining daily tables as one single table , segregated from the monthly single table. Please let me know if that is not the correct understanding .

You can consider loading the tables into blob storage/ADLS as multiple files first then then use merge option in copy activity to merge into single file and load the same to target table. However, we need to make sure that the schema of all tables are same.

• Create 2 separate lookup/control tables which can be referred to in order to get the metadata about the tables that needs to be copied as daily tables and other for storing the name of monthly tables. These control tables would store information like tablename, schemaname ,query ,IsTableActive ,lastModifieddate etc
• Create 2 pipelines , one for loading daily table data , other one for monthly . Use lookup activity in the ADF pipeline to point to the lookup table.
• Use Foreach activity to iterate through the output of lookup activity
• Use copy activity inside foreach and in source setting, use @item().query which points to the query column of the control table having query like 'select * from table1_daily'.
• In the sink, point to ADLS to load as csv file parameterizing the dataset to have the target files as '@item().tablename' as file name too.
• Outside foreach, use another copy activity , point the source dataset to the folder having daily/monthly files respectively for both the pipelines. In sink settings, point to a single output file and in copy behaviour , use 'merge files' . This will create one single file having all data provided the schema of all files are same.
• Now use another copy activity to load from single file to target table.

Relevant resources: Copy behaviour in copy activity of ADF pipeline

Copy multiple tables in bulk by using Azure Data Factory

Create control table to store metadata

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou