Couple points to add here. First the ETL is the only process that can change this data. Remember this is a data warehouse, one load process and the rest are just reads. As long as the dim data cant be deleted RI remains intact. Second is the fact that we build SSAS cubes on top of all this data and its not happy when a key doesnt have a match in the dim database, so that acts as a line of defense protecting RI. Also the dim keys that live in the fact data are NOT NULL so the ETL process will fail if no key is found, but since we are generating missing keys if the lookup returns null no issues there.

My biggest worry was some converting we did from some old dim data and the possibility of having a matching key but for the wrong record. A way to prevent this is to use Guids but Guids are big and slow and use a bunch of space so all keys are INT besides FK's would not have helped with this anyway.

However I do agree, design points could be missed the cube could be setup to ignore errors, and the dim keys could be set to allow NULL, in which case the idea of running a weekly dim check sounds like a great idea.

We do have databases for some custom apps and trust me RI is strictly enforced... anytime we let a developer near the data RI is a must!