How to query partitioned data in Synapse serverless SQL

João Barros 06 de July de 2026 4 min read

When a Data Lake grows to millions of files, reading all of them on every query becomes slow and expensive. In the Azure Synapse serverless SQL pool you can organize files into folders and read only the ones you need, using OPENROWSET with wildcards and the filepath() function. The result is querying partitioned data quickly, paying only for the data each query scans.

Prerequisites

An Azure Synapse Analytics workspace with the serverless SQL endpoint (Built-in).
Parquet (or CSV) files in an Azure Data Lake Storage Gen2 account.
The Storage Blob Data Reader role on the storage account.
A SQL editor: Synapse Studio or Azure Data Studio.

Step 1: Organize the files into partitioned folders

Partitioning starts with how the files are stored. The most common convention is one folder per partition value, in the column=value format. For example, for sales organized by year and month:

vendas/
  ano=2025/
    mes=11/ parte-001.parquet
    mes=12/ parte-001.parquet
  ano=2026/
    mes=01/ parte-001.parquet

This pattern, known as Hive-style partitioning, is recognized by Synapse and lets a query skip whole folders when it filters by year or month. The more balanced the partitions, the better the performance.

Step 2: Read every file with OPENROWSET

Start by querying the whole folder tree using wildcards (*) in the path. A * matches one folder and *.parquet matches any Parquet file:

SELECT COUNT_BIG(*) AS total_linhas
FROM OPENROWSET(
    BULK 'https://minhaconta.dfs.core.windows.net/dados/vendas/ano=*/mes=*/*.parquet',
    FORMAT = 'PARQUET'
) AS r;

This query reads every file. There is no partition filter yet — it is just the starting point.

Step 3: Expose the partitions with filepath()

The filepath(n) function returns the value captured by the n-th wildcard in the path. Because we have two * (year and month), filepath(1) returns the year (ano) and filepath(2) the month (mes):

SELECT
    r.filepath(1) AS ano,
    r.filepath(2) AS mes,
    COUNT_BIG(*) AS linhas
FROM OPENROWSET(
    BULK 'https://minhaconta.dfs.core.windows.net/dados/vendas/ano=*/mes=*/*.parquet',
    FORMAT = 'PARQUET'
) AS r
GROUP BY r.filepath(1), r.filepath(2)
ORDER BY ano, mes;

Notice that the partition columns (year, month) are not stored inside the Parquet file — they come from the folder name through filepath().

Step 4: Filter partitions to scan less data

Here is the trick that saves time and money. When you filter by filepath() in the WHERE clause, the serverless SQL pool applies partition elimination: it works out from the path which folders are relevant and never even opens the rest.

SELECT r.filepath(2) AS mes, COUNT_BIG(*) AS linhas
FROM OPENROWSET(
    BULK 'https://minhaconta.dfs.core.windows.net/dados/vendas/ano=*/mes=*/*.parquet',
    FORMAT = 'PARQUET'
) AS r
WHERE r.filepath(1) = '2026'
GROUP BY r.filepath(2)
ORDER BY mes;

Because you filtered filepath(1) = '2026', the engine reads only the ano=2026 folder and ignores the others. Filtering filepath(2) as well would reduce the scanned data even further.

Step 5: Save the query in a VIEW

To reuse it without repeating the path, create a VIEW in a serverless SQL database:

CREATE VIEW dbo.vw_vendas AS
SELECT
    r.filepath(1) AS ano,
    r.filepath(2) AS mes,
    r.*
FROM OPENROWSET(
    BULK 'https://minhaconta.dfs.core.windows.net/dados/vendas/ano=*/mes=*/*.parquet',
    FORMAT = 'PARQUET'
) AS r;

From then on, a simple SELECT ... FROM dbo.vw_vendas WHERE ano = '2026' keeps partition elimination working.

Check the result

Confirm two things. First, that the year (ano) and month (mes) values appear correctly in the results — that proves filepath() is reading the right folders. Second, open the messages/statistics tab and compare the data processed between the unfiltered query (Step 3) and the filtered one (Step 4): the second should scan far less. In the serverless SQL pool the cost is based on data scanned, so less data means lower cost.

Conclusion

With partitioned folders, OPENROWSET and filepath(), you read only what you need, making queries faster and cheaper. The next step is to convert frequently used data to partitioned Parquet with CETAS and agree on a consistent folder convention across the team. Which dimension makes the most sense for partitioning your data — date, region or customer?