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.
Comments
Expanding on what others have already said:
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.