SQL Server to (Datawarehouse vs Lakehouse)

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-differences-and-architecture-designs/

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/choosing-between-the-lakehouse-and-warehouse-in-microsoft-fabric/

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.)