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
- Explain the difference between Synapse serverless and dedicated SQL pools.
- Query CSV or Parquet files in ADLS Gen2 with
OPENROWSET. - Separate SQL permissions from storage authorization.
- Reduce scanned data with format, partition, and column choices.
- Identify when another analytics engine is a better fit.
Prerequisites
- Basic SQL and ADLS Gen2 concepts.
- A Synapse workspace is required to run queries; no resources are created by this article.
- Serverless SQL charges are associated with data processed; storage and data transfer can add separate costs.
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:
- Using compressed columnar formats such as Parquet for analytical workloads.
- Filtering on partitions that match common predicates.
- Selecting only needed columns instead of
SELECT *. - Avoiding repeated exploratory scans over a broad date range.
- Keeping file sizes and partition counts practical for the query engine.
- Using cached or curated outputs for repeated, high-concurrency queries when appropriate.
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