The use of Upper comes from an implementation of this on a different database that is case sensitive (Netezza) and a bit of (buggy) transcription. Whilst of course the CTE is far more elegant than the original code, nevertheless it is simply an improved variant of the original. The point of the article is to say that such coding can be made data driven, giving the opportunity for it to be maintained by other groups, and it removes the burden of urgent code releases each time there is a change.
I'd fully agree that no matter how it is implemented it must still be tested; there is 'no royal road to testing'. However, it doesn't necessarily need to be IT that does the testing. Our organisation has a dearth of good testers but some very diligent staff in the Accounts department and it is their job to ensure that the data is delivered accurately, IT are providing a tool for them to do this.
When validating fields with two or three values one may well do it in code, once it gets to a reasonable handful or more then it should generally go into a table that authorised users can maintain. It's also a lot more fun to implement solutions using a meta-data approach, although one can go too far.
Ultimately every design decision must be taken individually, weighing up the pros and cons of each approach. This is another tool in the box that may be relevant in some cases.