Azure Synapse Serverless Pools for Lake Databases

Problem

Azure Synapse supports the concept of a lake database, which can be defined by either Spark Hive Tables or Common Data Model (CDM) exports. There is support for an active link from Data Verse to Synapse. This link allows Power Apps and/or Dynamics 365 to export data continuously to the lake database in CDM format.

Having a SPARK cluster run all the time to view read-only data can be costly. Because data is written once and read many times, how can we reduce the cost of our company's reading activities?

Solution

Azure Synapse Analytics Serverless SQL pools allow the architect to replace the interactive SPARK cluster with a lower-cost service. Below is a high-level architecture diagram of Synapse Serverless Pools. Queries submitted to the Serverless SQL pool can view data stored in any lake database format once security is set up correctly.

Summary

The MS SQL TIPS article focuses on both managed tables placed in the service storage and unmanaged tables kept in general storage. The Apache Spark engine was used to create tables using both weak and strong file types. Regardless of table type, access to both the Synapse Service and Data Lake Storage is required to query data.

The Azure Synapse Service has two layers of security. The first is the IAM layer that allows you into the workspace. The second gives you the right to work within the Synapse environment.

There are two flavors of security that you can offer your end users. Microsoft Entra ID, formally Active Directory (AD) with Password, is common nowadays. However, older applications might require Standard SQL Security, meaning the administrator must manage server logins, database users, database rights, and file credentials. Regardless of the security you use, please use AD groups and/or database roles to reduce the total management cost.