How to integrate Azure Data Lake with Synapse Serverless: step by step

João Barros 17 de October de 2026 4 min read

This guide shows how to integrate a container in Azure Data Lake Storage Gen2 with SQL Serverless in Azure Synapse Analytics to query Parquet files and create External Tables. It is useful for analytics without ETL, reducing latency and costs by reading data directly from the Data Lake.

Prerequisites

Azure subscription with permissions to create resources (Resource Group, Storage, Synapse).
Azure Data Lake Storage Gen2 with a container and some Parquet or CSV files.
Azure Synapse Analytics workspace with SQL Serverless enabled.
Access to Synapse Studio and, optionally, Azure Storage Explorer.

Step 1: Configure access (Shared Access Signature or Managed Identity)

You need to grant Synapse access to the Data Lake container. For quick development, you can generate a SAS (Shared Access Signature) with read/list permissions. In production, prefer Managed Identity and the Storage Blob Data Reader role on the container.

# Exemplo de geração de SAS via Azure CLI (opcional) az storage container generate-sas \   --name meu-container \   --account-name minhaStorageAccount \   --permissions rl \   --expiry 2030-01-01T00:00:00Z \   --https-only \   --output tsv

Step 2: Create an external Data Source in Synapse Serverless

In Synapse Studio open the Develop area and create a new SQL (serverless) query. We will register the container as an external data source to simplify queries.

-- Substituir o URL pelo endpoint do seu container + SAS (se aplicável) CREATE EXTERNAL DATA SOURCE MyDataLake WITH (   LOCATION = 'https://minhaStorageAccount.dfs.core.windows.net/meu-container' );

If using SAS, you can include the string in the LOCATION or use CREDENTIAL. For Managed Identity, ensure the workspace has the identity assigned and the role on the Storage.

Step 3: Query files with OPENROWSET (Parquet and CSV example)

OPENROWSET allows reading files directly without creating a persistent table. Useful for quick exploration and schema validation.

-- Ler ficheiros Parquet SELECT TOP 100 * FROM OPENROWSET(   BULK 'https://minhaStorageAccount.dfs.core.windows.net/meu-container/pasta/*.parquet',   FORMAT='PARQUET' ) AS [result];  -- Ler ficheiros CSV (com header e separador) SELECT * FROM OPENROWSET(   BULK 'https://minhaStorageAccount.dfs.core.windows.net/meu-container/pasta/*.csv',   FORMAT='CSV',   PARSER_VERSION='2.0' ) WITH (   Col1 int,   Col2 varchar(100),   Col3 datetime ) AS [csv];

Step 4: Create External File Format and External Table for repeated queries

If you plan frequent queries, creating an External File Format and External Table makes the experience more consistent and performant (centralized metadata).

-- External File Format para Parquet CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (   FORMAT_TYPE = PARQUET );  -- External Table apontando para o caminho no Data Lake CREATE EXTERNAL TABLE dbo.MyExternalParquet WITH (   LOCATION = 'pasta/subpasta',   DATA_SOURCE = MyDataLake,   FILE_FORMAT = ParquetFormat ) AS SELECT TOP 0 * FROM OPENROWSET(   BULK 'https://minhaStorageAccount.dfs.core.windows.net/meu-container/pasta/subpasta/*.parquet',   FORMAT='PARQUET' ) AS t;

Note: the CREATE EXTERNAL TABLE AS SELECT (CETAS) command allows creating files from a query; here we use SELECT TOP 0 to create only metadata if needed. Adjust the schema according to your file or use PolyBase/OPENROWSET to infer.

Step 5: Optimize queries and handle common errors

Common errors: 401/403 (permissions), incorrect paths, inconsistent schema across files. To optimize: partition files by folder (e.g. year=2025/month=10), use Parquet columnar, and avoid listing millions of files in a single query.

-- Exemplo de filtro por pasta para reduzir ficheiros lidos SELECT * FROM OPENROWSET(   BULK 'https://minhaStorageAccount.dfs.core.windows.net/meu-container/year=2025/month=10/*.parquet',   FORMAT='PARQUET' ) AS [result];

Verify the result

Run a simple SELECT query against the External Table or OPENROWSET to confirm the read: inspect rows and data types, check execution time and whether authorization errors appear. Also test reading a single file to validate the schema with less data.

Conclusion

With these steps you integrated Azure Data Lake with Synapse Serverless, enabling you to query Parquet/CSV files directly and create External Tables for repeated work. Next steps: migrate to Managed Identity for security, partition data and create views to simplify queries. Tip: when you see 403, first check the identity used and the roles assigned.