Cloud · Azure in Depth

Querying data lake files with Azure Synapse Serverless SQL

azureEasyazuresynapsesqldata-lakeserverless

Interview question

How does Synapse Serverless SQL query Parquet files directly in ADLS Gen2, and how do you optimize its cost?

How to answer

The short answer

Azure Synapse Serverless SQL is an on-demand, pay-per-query engine that enables data engineers and analysts to query data lake files in ADLS Gen2 using standard T-SQL without provisioning or managing database clusters.

Querying files with OPENROWSET

The core mechanism for querying storage files is the T-SQL OPENROWSET function, which reads Parquet, Delta Lake, or CSV files directly from storage containers:

SQL
SELECT
    product_category,
    SUM(total_amount) AS revenue
FROM OPENROWSET(
    BULK 'https://stlake.dfs.core.windows.net/curated/orders/*/*.parquet',
    FORMAT = 'PARQUET'
) AS orders
WHERE filepath(1) = '2026-10'
GROUP BY product_category;

Performance and cost optimization rules

Because Synapse Serverless SQL bills at 5 dollars per terabyte of data scanned, query optimization directly controls operational expenditure:

  • : Store data in Parquet or Delta rather than CSV. Parquet stores data columnarly, allowing the engine to scan only the columns requested in the query.
  • elimination: Use filepath() in the WHERE clause to target specific folder paths, preventing the engine from scanning unrelated partition folders.
  • Explicit : When querying CSV or files, use an explicit WITH clause to define data types instead of letting the engine infer expensive character length buffers.
  • External tables: Wrap frequently queried Parquet folders into external tables within a logical serverless database, simplifying access for Power BI and dbt models.