Extract to staging: use constraints or not?

Go down

Extract to staging: use constraints or not?

Post  gbritton on Tue Dec 17, 2013 9:18 am

Hi -- I'm looking for best practices for this problem.  

I'm building new ETL packages (MS SQL Server).  The tables in the source system may have constraints (e.g. keys, indexes, "not null", etc.)  Is the best practice to replicate those constraints in the staging area (the "E" of the ETL) or to keep things as generic as possible (e.g. no keys, indices or constraints like "not null")?

The dimensions I create will of course have keys and indexes.  What about other constraints there?  Keep 'em or dump 'em (that is, do the data quality checks in the ETL packages)?


Posts : 6
Join date : 2013-11-18

View user profile

Back to top Go down

Re: Extract to staging: use constraints or not?

Post  ngalemmo on Tue Dec 17, 2013 9:55 am

As far as staging is concerned its really up to you and the particular situation you are dealing with.  Most of the time its just a location where tables are placed for (usually) sequential processing.  Most of the time it doesn't make much sense to go overboard with constraints and indexes.

In the data warehouse itself it is common not to enforce primary and foreign key constraints because of the process of assigning surrogate keys, which ensures you have proper keys.  Any database constraints are redundant and can significantly slow down load processes.  Many databases will allow you to declare the constraints (which are often used by the optimizer and external BI tools) but deactivate them so they are not enforced.

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

View user profile http://aginity.com

Back to top Go down

Back to top

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