• Shouldn't those 5 missing zip codes be shown up by the RI in the OLTP database?

    If the OLTP database lacks RI it won't exist in the DW either!

    I think that the valid logical 'foreign key relation' is important in the DW though it need not be enforced by an 'actual' foreign key.

    I use a lookup function on loading at the ETL level to assign fact table surrogate key values from dimension tables and if a value is not found (ie. where RI is broken at a logical level I assign a default value so as to avoid a null: in practice the default value in the fact table might flag a foreign key relation problem presumably in the ETL region). If you need enforced foreign keys in the DW then the data loading is defective.

    It could be possible to apply RI during development and testing to ensure that data is coming in to the DW without RI problems and to remove it when the DW loading is significant. I can imagine that a fact table of perhaps 30 foreign keys to large dimension tables and millions of rows is going to be very slow on inserts and heavy on resources. To speed up the querying on the DW it is possible to use suitable indexing which can be switched off during loading.

    Having to enforce foreign key relations on all keys of all fact tables must surely overload performance.

    If the input data from the OLTP data is not 'clean' there is a problem 'outside' the DW itself.