Hi
Hope you all enjoyed Tom and Jerry show !!!
In this blog I am going to explain the Importance of Knowing Inserts , Updates and Deletes using Face Book account.
Insert - I am a new user , and I am trying to Sign up using my email id XYZ.gmail.com in Face Book , I got my account created – Inserting a new account into Face Book.
Duplicate - Now I am an existing user of the Face book if I try sign up again using XYZ.gmail.com then Face Book will throw an Error says the user already in the Face Book – This is a Duplicate record for the key XYZ.gmail.com
Update - When I created my Face Book account I kept my user name as ‘aashiq amrita’ but my friends said to change the display name, so I changed the name to ‘Asik Ali’ now my name is changed – This is Updating a existing record.
Copy - When I created my Face Book account I used my Date of Birth as 06/03/1987, and now I was trying to modify with the same value ’06-03-1987′, nothing is changed – this is a copy record to my existing data.
Delete records (logically) : When I had fight with my Girl friend I went to Face Book account and I deactivated (but I can re open it later) – Logically delete
As a tester we need to ensure all the above mentioned logic are working when ETL jobs are executed.
Copy records and Duplicate records are different and we need to understand when it will happen.
Initial Day Load (Day_01) Insert Jobs will extract all the required data from the source and load them into warehouse. Day_01 load will not eliminate duplicates until otherwise it is mention as part of Filter rule. – Creating a face book a account
Delta Load (Day_02) - In delta load the Insert Job as well as Delta jobs will run in Parallel.
a) Inserts will be done when the corresponding record is not in the target tables.
b) Deltas will be done when records are in Source and Target and change in the value column .
Delta jobs are nothing like comparing what is coming in to what is already there in . If you have Copy or Duplicate records in this scenario of updating record what will happen??
Copy in updating a record :
Copy records can be from source or can be existing in the target table
I am sending update for one field of my existing Face Book account by two different browser ??? try what will happen ?
Duplicate in updating a record :
By mistake Face Book allowed me to create two accounts , and for that I am sending an update – so which of mine will be updated?
Logically delete a record :
Like deleting a a face book account you can expire a target record without updating it, this will done based on the source data instructions.
When you say a record is active ? – In most of the tables design we will have a Expiry date column – if it is as high date 9999-12-31 then it is a Active record.
When you say a record is expired ? – In most of the tables design we will have a Expiry date column – if it is not 9999-12-31 then it is a Active record.
What happen to the existing record when you do Update ? - While updating a record, it will insert a updated record with Expiry date as 9999-12-31 and expire the existing record with the date when its updated.
What happen to the existing record when you do Delete the record? While deleting a record will not insert a new record whereas it will expire the existing record by setting Expiry date as the date when it wants to be logically deleted.
Learn Inserts , Updates, Delete using your Face Book account.
Cheers
Asik
Filed under: Data Warehouse Testing - Learners Guide
