How to query Parquet files in Synapse with OPENROWSET

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

The serverless SQL pool in Azure Synapse Analytics lets you query Parquet files stored in the Data Lake directly with T-SQL, without copying the data or provisioning servers. The OPENROWSET function opens those files as if they were a table, and you pay only for the data each query processes.

Prerequisites

An Azure Synapse Analytics workspace (the Built-in serverless SQL pool is already included).
Parquet files in an ADLS Gen2 or Blob Storage account.
Read permission on the storage (Microsoft Entra identity, SAS or Managed Identity).
A SQL client: Synapse Studio, Azure Data Studio or SSMS connected to the serverless endpoint.

Step 1: Query a Parquet file

Start by reading a single file. BULK takes the full path and FORMAT the Parquet type. The alias after the parentheses is mandatory.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://minhastorage.dfs.core.windows.net/datalake/vendas/vendas.parquet',
    FORMAT = 'PARQUET'
) AS dados;

You don't need to declare columns: the serverless SQL pool reads the names and data types from the Parquet file's own metadata.

Step 2: Read a whole folder with wildcards

To treat many files as a single table, point to the folder and use the * wildcard. Put ** at the end of the path to also traverse subfolders.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://minhastorage.dfs.core.windows.net/datalake/vendas/*.parquet',
    FORMAT = 'PARQUET'
) AS dados;

When reading several files, the schema is inferred from the first file the service finds. Files whose name starts with _ or . are ignored.

Tip: always start with SELECT TOP 100 to inspect the data before running queries over large folders — that keeps the processed volume, and the cost, under control.

Step 3: Define the schema with the WITH clause

To pick only some columns and lock in the right types, add the WITH clause. In Parquet, columns are bound by name (case-sensitive), so the names must match those in the file.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://minhastorage.dfs.core.windows.net/datalake/vendas/*.parquet',
    FORMAT = 'PARQUET'
)
WITH (
    data_venda  date,
    produto     varchar(100) COLLATE Latin1_General_100_BIN2_UTF8,
    quantidade  int,
    total       decimal(10,2)
) AS dados;

The Latin1_General_100_BIN2_UTF8 collation on text columns gives a performance boost when filtering Parquet data.

Step 4: Filter partitions with filepath()

If the data is organized into folders by year and month (for example ano=2025/mes=07), the filepath(n) function returns the part of the path that matches the nth wildcard. Using it in the WHERE clause means only the needed files are read — less data processed, less cost.

SELECT
    dados.filepath(1) AS ano,
    dados.filepath(2) AS mes,
    COUNT_BIG(*)   AS linhas
FROM OPENROWSET(
    BULK 'https://minhastorage.dfs.core.windows.net/datalake/vendas/ano=*/mes=*/*.parquet',
    FORMAT = 'PARQUET'
) AS dados
WHERE dados.filepath(1) = '2025' AND dados.filepath(2) = '07'
GROUP BY dados.filepath(1), dados.filepath(2);

The filename() function complements this approach by returning the source file name of each row.

Step 5: Simplify the path with an EXTERNAL DATA SOURCE

Repeating the full URL in every query is tedious. You can create an external data source that points to the root of the container and then use relative paths.

CREATE EXTERNAL DATA SOURCE datalake
WITH ( LOCATION = 'https://minhastorage.dfs.core.windows.net/datalake' );
GO

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'vendas/*.parquet',
    DATA_SOURCE = 'datalake',
    FORMAT = 'PARQUET'
) AS dados;

Now BULK only holds the relative path and DATA_SOURCE handles the rest.

Verify the result

Run the Step 1 query: you should see the first 100 rows with the correct column names. To confirm partition filtering worked, add dados.filename() to the SELECT and check that only files from the expected folders appear. In the Synapse monitoring tab, a low data processed value confirms that pruning reduced the read.

Conclusion

With OPENROWSET you can now query Parquet in the Data Lake without moving a single byte into a database. The natural next step is to wrap the query in a VIEW or create an EXTERNAL DATA SOURCE so you don't repeat the full URL in every query. One final tip: keep the Latin1_General_100_BIN2_UTF8 collation on text columns. Which folder in your Data Lake will you explore first?