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 agree to our terms of service, privacy policy and cookie policy