Microsoft Azure Learning Journey

Query Lake Data with Azure Synapse Serverless SQL

Use serverless SQL to explore files in Azure Storage, authorize the data path, and control scan-based query cost.

4 min read · 769 words
Microsoft AzureData EngineeringAnalyticsData GovernanceCloud Computing
Query Lake Data with Azure Synapse Serverless SQL cover image

Analysts sometimes need SQL access to a lake dataset before a dedicated warehouse is justified. Azure Synapse serverless SQL pool can query supported files in Azure Storage without provisioning a dedicated SQL compute pool. “Serverless” does not mean that storage access, query performance, concurrency, or cost needs no design.

What you’ll learn

Prerequisites

What serverless SQL does

The serverless SQL pool in an Azure Synapse workspace provides on-demand SQL over supported lake files. It does not require you to provision a dedicated SQL pool for each query workload. It is useful for exploration, lake querying, and some external-table scenarios; it is not a general replacement for transactional databases or a persistent, high-concurrency warehouse.

A dedicated SQL pool reserves provisioned data warehouse compute and supports a different set of workload-management and performance patterns. A serverless SQL pool analyzes external data and charges according to processed data. Compare concurrency, latency, data model, feature support, and cost profile using representative workloads.

Query Parquet in place

Create a database-scoped credential and external data source when using the Synapse workspace managed identity. An administrator must grant that identity the required storage data-plane access at an appropriate scope, and the workspace must be able to reach the storage endpoint.

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity';
GO

CREATE EXTERNAL DATA SOURCE CuratedLake
WITH (
    LOCATION = 'https://<storage-account>.dfs.core.windows.net/curated',
    CREDENTIAL = WorkspaceIdentity
);
GO

SELECT TOP (100)
    *
FROM OPENROWSET(
    BULK 'sales/year=2026/month=11/*.parquet',
    DATA_SOURCE = 'CuratedLake',
    FORMAT = 'PARQUET'
) AS sales;

Replace the account and path with the actual container/directory layout. Test authorization with the intended workspace identity rather than an administrator’s session. Some access patterns can use caller credentials or a SAS credential; choose a supported method deliberately and never embed a long-lived token in a query or notebook.

For delimited text, specify the format and options such as field terminator and header behavior explicitly. Inferencing can be convenient for exploration but brittle as a production contract. Use an explicit schema when type accuracy, stable names, or query performance requires it. Confirm column names and data types before promoting a query to a report.

Design for scan cost

Serverless SQL cost is tied to the amount of data processed under the applicable pricing model. Reduce it by:

Inspect query statistics and estimate bytes processed before scheduling a recurring query. A partition directory only helps if the file layout and query predicate let the engine prune it. A string-encoded date or inconsistent path convention can defeat pruning.

Secure the whole data path

There are at least two authorization decisions: who can execute SQL in the workspace/database, and which identity can read the storage files. Workspace access is not equivalent to storage access. Grant the Synapse managed identity only the required data role on the curated container or narrower supported scope. If ACLs are used, verify parent-directory traversal and file permissions.

Use private endpoints and managed virtual networks where needed, then test DNS and routing from the actual query service. Configure outbound access and storage firewall rules intentionally. Do not use a workspace administrator or account key as a permanent query credential.

✅ Checkpoint: Explain which principal runs the SQL statement, which principal reads the file, the storage scope granted, the bytes scanned by a representative query, and how the query will behave if its partition is missing.

Next: Choose between Synapse and Microsoft Fabric.

Series navigation: Previous: Azure Data Factory · Current: Phase 9, Part 4 · Next: Synapse or Fabric

References

Share this article

LinkedIn post draft