Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Constraints at Database Level or at ETL side .

2 posters

Go down

Constraints at Database Level or at ETL side .  Empty Constraints at Database Level or at ETL side .

Post  renjithmadhavan 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

Back to top Go down

Constraints at Database Level or at ETL side .  Empty Re: Constraints at Database Level or at ETL side .

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Back to top

- Similar topics

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