Jim1234 (1/2/2014)
Hi Team,We are designing a Staging layer to handle incremental load. I want to start with a simple scenario to design the staging.
In the source database There are two tables ex, tbl_Department, tbl_Employee. Both this table is loading a single table at destination database ex, tbl_EmployeRecord.
The query which is loading tbl_EmployeRecord is, SELECT EMPID,EMPNAME,DEPTNAME FROM tbl_Department D INNER JOIN tbl_Employee E ON D.DEPARTMENTID=E.DEPARTMENTID.
Now, we need to identify incremental load in tbl_Department, tbl_Employee and store it in staging and load only the incremental load to the destination.
The columns of the tables are,
tbl_Department : DEPARTMENTID,DEPTNAME
tbl_Employee : EMPID,EMPNAME,DEPARTMENTID
tbl_EmployeRecord : EMPID,EMPNAME,DEPTNAME
Kindly suggest how to design the staging for this to handle Insert, Update and Delete.
Regards
Jim
tbl_Department - I would assume that this should be done first, as you are probably enforcing referential integrity (no employee can be assigned to a Department that doesn't exist)
tbl_Employee - then process this one
tbl_EmployeeRecord - table? I think this should be just a view off the first 2 tables.
using merge as suggested.
but with something like this, I would think tracking changes would also be important.
so more thought about the overall design might be needed, or maybe I'm just trying to read to much into this.
headcount at any point in time, or knowing when and who (or what) made the change would be questions I could see coming up