My biggest tip: Once you have mastered the art of the lookup transform, remove all foreign keys in your fact table. Like the author states, they are "technically right, but practically wrong" as FKs add unnecessary overhead for a check you just performed in the lookup transform.
This is from another forum, but seems germaine to this one. I would really like to understand this. I absolutely advocate the use of the standard RI in the database. In my view, it's not an ETL function directly. In theory, there should be no issues, but in theory that's also true of OLTP applications. In practice, there are always records that slip through.
If you choose not to use them, I wonder if you do, as Kimball suggests, something that tests them once per week just to ensure there are no issues. Bottom line for me is: with RI constraints I'm sure there's not a data issue. Without them, I'm not sure. Yes, there is a cost, but there's a possible cost of data issues without them. The standard for data issues due to design defect should be zero.
I don't see this any differently from those who advocate not using transaction control where necessary because it affects performance (yes, that happens). Most of the time, there is no issue. But sometimes there are issues. The performance hit to ensure these issues don't exist is worth it, and if it's not, then assuming the queries have been efficiently written, the hardware needs to be improved.