If the user of the data insists on a time horizon of 'since our founding', then yeah, there's an opportunity cost to keeping all of the data. The one people usually miss is that it takes log(n) time to update or insert a record because you have to update all of the indexes. So when you have 1000 times as much data, every insert takes 10x as long as it did at the beginning. Or you use partial indexes and it gets maybe 2 times slower which the users probably won't notice.
So people just give up and exfiltrate all of the data to another server to run their reports on. But as a user I still want to be able to figure out 'did I do that thing in February, or was it March?' fairly often.
One thing I've always wished database replication systems did (universally) was allow you to run different indexes on different replicas, so you don't have to export the data at all. Your insert time would still be a function of network delay + worst case index update time, but you could segregate traffic based on kind and continue to scale to a fairly large company before anyone had to mention data warehousing or data lakes.
Well, there's human-generated data, and there's computer-generated data. The former is tiny but more dynamic, and where we want all of these nice creature comforts. The latter can be large, but workflows tend to be static so there's less need for these features.
Does it remain cost effective at scale?
All the source code in the world is a drop in the bucket compared to the raw data collected by a large business.