Constraints at Database Level or at ETL side .

View previous topic View next topic Go down

Constraints at Database Level or at ETL side .

Post  renjithmadhavan on Tue Oct 08, 2013 12:15 pm

I have observed that constraints in one large datawarehouse are all maintained at ETL level and not at database ( oracle ) . Especially the primary key - foreign key relationship between fact and dimension tables.

I am new to ETL and have always believed that maintaining constraints at database level is a better design .

Can someone throw the benefits of having the constraints all maintained at ETL level ?

renjithmadhavan

Posts : 1
Join date : 2013-10-08

View user profile

Back to top Go down

Re: Constraints at Database Level or at ETL side .

Post  ngalemmo on Tue Oct 08, 2013 6:27 pm

It's because the work ETL is doing to transform natural keys to surrogate keys ensures that all surrogate primary keys in the database exist.  Database constraints are redundant and only serve to significantly slow down loads into the database.

Often such constraints are declared in the database, but are disabled to prevent actual enforcement. Some DB optimizers use that information in developing a query plan.
avatar
ngalemmo

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

View user profile http://aginity.com

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