How to create a Synapse Link between OneLake and Dedicated SQL Pool in Azure Synapse Analytics
This tutorial shows how to create a Synapse Link between OneLake (or an integrated Data Lake) and a Dedicated SQL Pool in Azure Synapse Analytics to materialize analytical data. It is useful when you want to query Data Lake files with relational SQL performance without heavy ETL processes.
Prerequisites
- Azure account with permissions to create resources (Synapse Workspace, Storage/OneLake, Dedicated SQL Pool).
- Azure Synapse Workspace already created or access to create it.
- Parquet or CSV files in a OneLake/Azure Data Lake Storage Gen2 container.
- Basic familiarity with T-SQL and the Azure portal.
Step 1: Confirm files and schema in the Data Lake
Verify in OneLake/ Azure Data Lake that you have a set of files (for example Parquet) with a consistent schema. This avoids errors when creating the external table or when synchronizing data with Synapse Link.
# Example: list files with Azure CLI
az storage fs file list \
--account-name \
--file-system \
--path --output table
Step 2: Configure credentials and Linked Service in Synapse
Create a Linked Service in the Synapse Workspace that points to OneLake/ADLS Gen2 using a Service Principal or Managed Identity. This allows Synapse to access the files to materialize data in the Dedicated SQL Pool.
-- Conceptual example (in the portal: Manage > Linked services)
Provider: Azure Data Lake Storage Gen2
Authentication: Managed Identity or Service Principal
Account name:
Container:
Step 3: Create an External Data Source and External File Format
In Synapse SQL (Dedicated SQL Pool) create an EXTERNAL DATA SOURCE and EXTERNAL FILE FORMAT to point to the Parquet/CSV files. This is the foundation for creating External Tables or using Synapse Link.
-- Connect to the Dedicated SQL Pool and run:
CREATE EXTERNAL DATA SOURCE MyLakeSource
WITH (
TYPE = HADOOP,
LOCATION = 'abfss://@.dfs.core.windows.net/'
);
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
FORMAT_TYPE = PARQUET
);
Step 4: Create an External Table or use Synapse Link to materialize
Option A: create an External Table to query data in the Data Lake (does not materialize). Option B: use Synapse Link/CTAS to materialize data in the Dedicated SQL Pool (better performance for analytical queries).
-- Option A: External Table (direct query to the Data Lake)
CREATE EXTERNAL TABLE ext_sales (
saleId BIGINT,
amount FLOAT,
saleDate DATE
)
WITH (
LOCATION = '/sales/parquet/',
DATA_SOURCE = MyLakeSource,
FILE_FORMAT = ParquetFormat
);
-- Option B: Materialize data with CTAS into a table in the Dedicated SQL Pool
CREATE TABLE dbo.sales_materialized
WITH ( DISTRIBUTION = HASH(saleId), HEAP )
AS
SELECT * FROM ext_sales;
Step 5: Automate incremental updates (optional)
To keep the materialized table up to date, use a pipeline in Synapse Pipelines: read only new/modified files or use a timestamp in the source and apply MERGE in the Dedicated SQL Pool.
-- Simplified MERGE example for incremental
MERGE dbo.sales_materialized AS target
USING (SELECT * FROM ext_sales WHERE saleDate >= '2026-01-01') AS src
ON target.saleId = src.saleId
WHEN MATCHED THEN UPDATE SET target.amount = src.amount
WHEN NOT MATCHED THEN INSERT (saleId, amount, saleDate) VALUES (src.saleId, src.amount, src.saleDate);
Verify the result
Confirm that the External Table reads files and that the materialized table contains data. Run simple queries and check response times; validate counts and data samples to ensure integrity.
-- Verify External Table
SELECT TOP 10 * FROM ext_sales;
SELECT COUNT(*) FROM ext_sales;
-- Verify materialized table
SELECT TOP 10 * FROM dbo.sales_materialized;
SELECT COUNT(*) FROM dbo.sales_materialized;
Conclusion
By linking OneLake/ADLS Gen2 to the Dedicated SQL Pool via External Tables and Synapse Link (or CTAS) you can achieve better query performance without complex ETL. Next steps: automate pipelines for incremental loads, tune distribution/partitioning and measure costs. Tip: test with small datasets before materializing large volumes to choose the best strategy.