Extract to staging: use constraints or not?

View previous topic View next topic 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)?

gbritton

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

- Similar topics

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