We are loading data from OLTP into Staging Tables with SSIS, then into Kimball data warehouse with ETL process.

We are inquiring about staging tables, if they should have null/not null constraints. What are the pros and cons for each aspect in debugging?

I know Data warehouse should have null/not null constraints.

There is discussion that, sometimes if one column is null, and another column is not, instead of not allowing the data to enter, people can analyze and understand why there are gaps/holes. The Staging area is a work testing/validating/debugging area to refine, cleanse, and transform data


I don't see what benefit you can draw from not having constraints. The only scenario when you can consider dropping the NOT NULL constraints is if your ETL process loads data in several steps and at some point staging tables may contain invalid data, for example, you first insert NULL values into a column, then in a separate process populate that column with correct values from a lookup or something.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.