Lake Database vs SQL Database in Synapse

[mpolonskiy5637]

I am trying to understand synapse concepts:

When talking about SQL Serverless Pools Managed Tables are not possible, so we have Unamanged or External Tables and Views.

It seems now that I can create a Lake Database for these items or a SQL Database. However since I am using SQL Serverless and all data is stored on Azure Gen2 Data Lake Storage I am wondering what the actual difference between these two types is, since they are also just "Logical" Databases, all data is still stored physically on the Data Lake. When and why would I prefer one scenario over the other? And finally, is there any way of getting managed tables without a dedicated pool?

Greetings

Michael

[Anonymous]

Hi @mpolonskiy5637 ,

Thanks for using Fabric Community.

Managed vs. External Tables/Views in Serverless SQL Pools:

• Managed Tables: 
  These reside within a dedicated SQL pool, with data physically stored in the dedicated pool's storage.
• External Tables/Views: 
  Serverless SQL pools work with your existing data in Azure Data Lake Gen2. You create external tables that point to the data location in the Data Lake. Views offer another layer of abstraction on top of the data, allowing for custom logic or filtering.

Lake vs. SQL Database in Serverless SQL Pools:

While both are logical constructs, there are key differences:

• Lake Database: 
  This is specifically designed for working with data in its native format within the Data Lake (Parquet, CSV, etc.). It provides a familiar T-SQL syntax for querying the data directly without movement. Use this when you prioritize flexibility and working with various data formats natively.
• SQL Database: 
  This offers a more traditional relational database structure. While the data physically resides in the Data Lake, the SQL Database provides a schema definition and enforces data types. Use this when you need a structured environment with defined relationships between tables and data integrity checks.

Choosing Between Lake and SQL Database:

• Flexibility vs. Structure: 
  If you need maximum flexibility and want to query various data formats directly, choose a Lake Database. If you require a structured environment with schema and data type enforcement, opt for the SQL Database.

Hope this is helpful. Please let me know incase of further queries.

[Anonymous]

Hi @mpolonskiy5637 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
Otherwise, will respond back with the more details and we will try to help .

[Anonymous]

Hi @mpolonskiy5637 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
Otherwise, will respond back with the more details and we will try to help .

[AndyDDC]

Hi @mpolonskiy5637 the difference between Synapse Lake Databaes and Synapse Serverless is that lake databases are spark based, so you can fully manage your reads/writes to tables in lake databases using spark pools.  Serverless is t-sql based and is really just for casting structure over data in a data lake and you don't have much in the way of write capabilities.

Lake databases expose their metadata to Serverless as well. So you can create a lake database, create managed or unmanaged tables in the lake database using spark pools, then those tables will be made available for querying using serverless.

think of serverless as more of a read-only service using t-sql