Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mpolonskiy5637
Frequent Visitor

Lake Database vs SQL Database in Synapse

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

4 REPLIES 4
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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
Most Valuable Professional
Most Valuable Professional

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

November Update

Fabric Monthly Update - November 2024

Check out the November 2024 Fabric update to learn about new features.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.