Two Banks Bank A and Bank B getting into merger as one single bank BANK X. Both these banks (A & B) have been maintaining customer data in their own respective formats (refer TABLE 1). After the merger BANK X will have to maintain the customer data of both the banks (A & B) in one format. You have been approached by them to design an efficient ETL framework.

1. List what metadata will the Bank X extract from Bank A and Bank B. [3 marks]
2. Explain in brief, what you will do (steps required) in Extraction, Transform and Load so as to meet the criteria of BANK X? [5 marks]

(HINT: You may need to work on how to get data from two different sources, removing of duplicates, merge it where ever required, create new column and develop into the new format and the storage location should be accessible to bank employees / consumers)

TABLE 1

Bank A - Sample Data
A/C no.	Surname	First Name	Branch Code	A/C Type	Aadhar no.	A/C open date	Balance
533879425	Bomai	Guru	235	Savings	124XXX568XXX	16-12-1996	50000
533965210	Krupa	Sidram	367	Savings	65XX893XXX98	07-01-2007	75000
533821764	Hegde	Seema	402	Savings	328X9X25XX7X	26-03-2001	150000
533558623	Chavala	Shruti	268	Current	624XX657X971	19-11-2002	36000
533694521	Ali	Zilnuma	684	Savings	7X5X9XX234X7	20-09-1998	42000

Bank B - Sample Data
First Name	Surname	Mobile no.	A/C Type	A/C no.	Pin Code	Customer_Since	Available_Balance
Seema	Hegde	970XXXX758	Current	411964875	125368	72 months	27000
Prasad	Rao	981XXXX512	Savings	411357986	254986	88 months	54236
Guru	Bomai	976XXXX480	Savings	411145893	127896	96 months	29563
John	Busa	996XXXX499	Savings	411782459	254631	29 months	28963
Deepak	Singh	989XXXX889	Current	411327154	372482	38 months	42789

Observation: Guru Bomai has a/c in both banks with same A/C type and Seema Hegde with different A/C type.