Empty data problem - data layer or DAL?

Question

I designing the new App now and giving the following question a lot of thought. I consume a lot of data from the warehouse, and the entities have a lot of dictionary based values (currency, country, tax-whatever data) - dimensions. I cannot be assured though that there won't be nulls. So I am thinking:

• create an empty value in each of teh dictionaries with special keyID - ie. -1
• do the ETL (ssis) do the correct stuff and insert -1 where it needs to
• let the DAL know that -1 is special (Static const whatever thing)
• don't care in the code to check for nullness of dictionary entries because THEY will always have a value

But maybe I should be thinking:

• import data AS IS
• let the DAL do the thinking using empty record Pattern
• still don't care in the code because business layer will have what it needs from DAL.

I think is more of a approach thing but maybe i am missing something important here... What do You think? Am i clear? Please don't confuse it with empty record problem. I do use emptyCustomer think all the time and other defaults too.

Answer 1

Use a data-profiling tool and examine those dimensions. As a rule, there should be no nulls in dimension tables. It is also common to have "special" dimension rows like: 0=unknown, -1= none, etc. Your data-warehouse designer (probably) already took care of that.

If you happen to find nulls, pass the problem to the data-warehouse team to deal with it. The warehouse is supposed to act as a reference data storage and serve multiple client applications -- if every application designer has to decide how to deal with unknown values, they will eventually generate different results (reports) based on the same data-set.