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

Extract to staging: use constraints or not?

2 posters

Go down

Extract to staging: use constraints or not? Empty Extract to staging: use constraints or not?

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

Back to top Go down

Extract to staging: use constraints or not? Empty Re: Extract to staging: use constraints or not?

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