I can't speak for data lakes, data warehouses, and so on but for OLTP 3NF has always been sufficient for me. There are *special* cases where denormalizing can be required, but it's a niche thing. Personally I use it for denomination sub-totals and nothing else.

The database is where data integrity rules belong, period. Otherwise an application can get around them either through ignorance or malice, and neither case is of the good.

Now in a read-only environment I can see where denomalization might be helpful and application logic supplies some business logic, but absolutely not where users can change the data. Users are endlessly inventive and the old saw "make something idiot proof and the universe will provide better idiots" is so true it isn't even funny.