Here are my two cents (however I am a data analyst, not a data engineer, so hopefully someone with more experience can share their views also):
- both lakehouse and warehouse use Delta tables as the underlying storage format
- the main difference is which language you prefer to work with (PySpark, Spark SQL, Scala, R for Lakehouse vs. T-SQL for Warehouse)
- There are some limitations to which T-SQL commands you can use
- I think I would start out with Lakehouse as it seems to offer more flexibility with regards to tooling and languages.
However if you prefer to work solely with T-SQL and the SQL experience (stored procedures, database schemas, etc.) I guess the warehouse is the natural choice. Note that there are some limitations with regards to the available T-SQL commands, because the underlying technology is Delta tables and Spark.
Here are some articles with more detailed information:
https://learn.microsoft.com/en-us/fabric/get-started/decision-guide-data-store
https://blog.fabric.microsoft.com/en-US/blog/lakehouse-vs-data-warehouse-deep-dive-into-use-cases-di...
https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/choosing-between-the-lakehou...
I guess I would try both options in the beginning, and then decide which one works best for your skills and requirements.
(Lakehouse can also be queried (read only) by using T-SQL in the SQL Analytics Endpoint. However only the Warehouse uses T-SQL for both writes and reads, create, control, etc.)
