I'm not well versed in these things, but at this point, aren't you re-inventing database systems? Talking about things like ACID transactions, schema evolution, dropping columns, ... in the context of a file-format feels bizarre to me.
At first glance, it seems like Delta Lake is inferior to a database. Most databases support multi-table transactions and Delta Lake only support transactions for single table. ACID transaction support is nothing new for a database.
Delta Lake is useful for large datasets and to keep costs low.
There are organizations that are ingesting hundreds of terabytes and petabytes of data into a Delta table every day. They're able to ingest data, perform upserts, and build realtime pipelines with this architecture.
Delta Lake is also free, so you only have to pay for storing the files in the cloud. This is a lot cheaper than a database usually.
Data warehouses are often packaged with a certain amount of shared RAM/storage. This can be a problem for a team with large workflows from many users. It's annoying to share compute with someone that's running a large experiment.
These are the main reasons enterprises shited to data lakes and now Lakehouse storage systems. See this paper to learn more: https://www.cidrdb.org/cidr2021/papers/cidr2021_paper17.pdf
Traditional RDBMSes just don’t scale so well as S3. But S3 didn’t have ACID semantics. Now it does!
If you have less than a terabyte of data, using one of those data warehouses is the smart move though.
Yes, it is basically just another relational database system. -but-, it's a database system that's optimized for a different purpose.
A traditional RDBMS is designed for OLTP workloads, and it does a great job of that. Ideally operations are small, discrete, and handled within milliseconds. In service of that speed, you also want to keep them small and lean, so that you can take maximum advantage of caching hot data in memory. Maybe on the megabytes-to-gigabytes scale.
A data warehouse is designed for more OLAP-style workloads, but the emphasis is still on real-time responses to relatively predictable requests. But it's at the more relaxed end of the "real-time" scale - a query might take a few seconds to run. You'll use extract-transform-load jobs to get the data organized into a structure that's optimized for those workloads before you load it into the warehouse. Data volumes still matter here, but they can be allowed to get quite a bit bigger than what's typical in OLTP databases. Think gigabytes-to-terabytes scale.
Lakehouses, on the other hand, are meant for more of a "get the data somewhere, and then figure out how to use it" mindset. So getting the data into it follows more of an extract-load-transform regime, meaning that significant processing and transformation of the data happens in the course of executing the query itself. The kinds of questions you want to ask are almost unconstrained, and that changes the performance situation again. Millisecond response times are now something that just never happens. Instead you're looking at seconds to minutes, perhaps even hours, being typical execution times for a query. The data also gets bigger again. People often suggest it's potentially on the terabytes-to-petabytes scale, but I haven't seen that myself. Mostly because I've never worked anywhere were anyone even wants to have that much data sitting around to have to manage and govern.
I would say don't get caught up too much on the scale consideration, though. That's real, but I think that the more interesting distinction, and the one that explains why OLTP systems and data warehouses are often implemented using the same RDBMS systems, while lakehouses really do merit a completely different tech stack, is the ETL vs ELT distinction.