How to create staging layer to handle incremental load

View previous topic View next topic Go down

How to create staging layer to handle incremental load

Post  jim123456789jim on Thu Jan 02, 2014 7:50 am

Hi,

I'm new to this forum and not sure is this a right place to post this question. Please move/delete this question if it is not relavent.

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

jim123456789jim

Posts : 2
Join date : 2014-01-02

View user profile

Back to top Go down

Re: How to create staging layer to handle incremental load

Post  BoxesAndLines on Fri Jan 03, 2014 11:06 am

Make it look like the target with the natural keys from the source. That will allow you to do lookups based on the source keys. If you find the row, check for updates. If no row found, then it is a new record. The ETL toolkit book covers this in detail.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

How to create staging layer to handle incremental load

Post  jim123456789jim on Mon Jan 06, 2014 1:03 am

Thanks. How to handle deletes. Say in these two tables(tbl_Department,tbl_Employee) if there is a delete in tbl_Employee then how to handle it(attribute table). If a delete happened in tbl_Department(driving table), then how to handle it?

Is it possible to have a generic senario to handle delete?

Regards
Jim

jim123456789jim

Posts : 2
Join date : 2014-01-02

View user profile

Back to top Go down

Re: How to create staging layer to handle incremental load

Post  ngalemmo on Mon Jan 06, 2014 1:07 am

Yes… don't.

You can flag a dimension row in some manner if you wish to indicate it has been deleted in the source system, but you should never delete dimension rows.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: How to create staging layer to handle incremental load

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum