Azure SQL Data Virtualization: filepath(1) filter does not perform partition elimination

Question

I am evaluating Azure SQL Database Data Virtualization against partitioned Parquet files stored in Azure Blob Storage.

My data is organized using hive-style partition folders:

• year=2021/
• year=2022/
• year=2023/
• year=2024/
• year=2025/
• year=2026/

I created the following view based on the Microsoft documentation for querying partitioned data:

CREATE VIEW [hr].[AnswerBlobs]
AS
SELECT
    ab.[AnswerKey],
    ab.[QuestionAnswer],
    ab.filepath(1) AS [PartitionYear]
FROM OPENROWSET(
    BULK 'year=*/*.parquet',
    DATA_SOURCE = 'questionnaireAnswerBlobs',
    FORMAT = 'PARQUET'
) WITH (
    [AnswerKey] INT,
    [QuestionAnswer] NVARCHAR(MAX)
) AS ab;
GO

Querying the view:

SELECT
    ab.AnswerKey,
    ab.QuestionAnswer,
    ab.PartitionYear
FROM hr.AnswerBlobs ab
WHERE ab.PartitionYear IN ('2025', '2026');

Produces an execution plan that performs a Remote Scan -> Filter.
the filter is applied after the scan and all partitions appear to be read:

To eliminate the view as a factor, I tested directly against OPENROWSET:

SELECT COUNT(*)
FROM OPENROWSET(
    BULK 'year=*/*.parquet',
    DATA_SOURCE='questionnaireAnswerBlobs',
    FORMAT='PARQUET'
)
WITH (
    AnswerKey INT
) ab
WHERE ab.filepath(1) = '2025';

Runtime approximately 43 seconds, Execution plan again appears to Remote Scan the full set -> Filter

For comparison, I queried the partition directly:

SELECT COUNT(*)
FROM OPENROWSET(
    BULK 'year=2025/*.parquet',
    DATA_SOURCE='questionnaireAnswerBlobs',
    FORMAT='PARQUET'
)
WITH (
    AnswerKey INT
) ab;

Runtime approximately 5 seconds: Execution plan Remote Scans (~58k rows)

This indicates that the filepath(1) predicate is not being pushed down to perform file/partition elimination. Instead, all partitions are scanned and then filtered.

Additional observations:

• Database collation: SQL_Latin1_General_CP1_CI_AS
• SQL_VARIANT_PROPERTY(filepath(1), 'Collation') also returns SQL_Latin1_General_CP1_CI_AS
• I tested:

    WHERE ab.filepath(1) COLLATE Latin1_General_100_BIN2_UTF8 = '2025'
  

  But the plan still resulted in a Full Remote Scan followed by a filter.

My questions are:

1. Is partition elimination using filepath() currently supported in Azure SQL Database Data Virtualization?
2. Is there a different syntax required for the optimizer to recognize the partition predicate?
3. Has anyone successfully observed partition pruning/file elimination using filepath() with Parquet files in Azure SQL Database?

Any guidance or confirmation of expected behavior would be appreciated!

Answer 1

Hi Kyler Dreyer & thx for join me at Q&A platform,

ur testing is pretty thorough, and I think u've already ruled out the most obvious query-level explanations.

Partition elimination using filepath() is documented as supported for Azure SQL Database Data Virtualization, including Parquet files accessed through OPENROWSET. Microsoft's own example uses wildcard folders and a filepath(1) predicate in the WHERE clause, so ur approach is consistent with the documented syntax.

The interesting part is that ur direct OPENROWSET query still takes around 43 seconds, compared with 5 seconds when the partition is specified directly in BULK. That makes this worth investigating as a possible optimizer or external scan pushdown issue, particularly because Azure SQL Database Data Virtualization is still in preview.

One distinction worth making, tho: seeing Remote Scan → Filter in the execution plan doesn't necessarily prove that every Parquet file was downloaded and scanned. The engine may enumerate files before applying partition selection, and the execution plan doesn't always expose how much filtering happened inside the external scan. The timing difference is compelling, but I'd want storage-side evidence before concluding that all partitions are being fully read.

I'd enable Azure Storage diagnostic logging temporarily and compare the actual Blob read operations for the wildcard query against the explicit year=2025/*.parquet query. Look at which blob paths receive read requests, how many files are accessed, and the volume of data transferred. That should help distinguish full file scanning from expensive wildcard enumeration or metadata processing.

Regarding collation, Latin1_General_100_BIN2_UTF8 is relevant to certain Parquet string predicate pushdown optimizations, but folder partition elimination through filepath() is a different mechanism. So I wouldn't expect changing the collation to fix this particular issue.

You've also tested the predicate directly against OPENROWSET, which means the view isn't the root cause. And I don't see a different documented syntax that u should need for a simple filepath(1) = '2025' filter.

As a practical workaround, if the year is known when constructing the query, use an explicit path such as year=2025/*.parquet. For multiple years, the application could generate separate queries or a UNION ALL over explicitly targeted paths. That's less convenient than automatic partition pruning, but it avoids scanning the wildcard folder structure unnecessarily.

I'd raise this with the Azure SQL Database Data Virtualization engineering team and provide the actual execution plans, query timings, storage request metrics, database compatibility level, and @@VERSION output. Specifically, ask whether filepath() partition pruning is currently functioning as documented in the Azure SQL Database preview and whether there are known limitations or regressions affecting wildcard Parquet paths.

One question: do ur storage logs show actual reads against the 2021–2024 Parquet files when filtering for 2025? If they do, that would be strong evidence that partition elimination isn't being applied, rather than just being hidden by the execution plan.

rgds,

Alex

p.s. if my answer is help - pls mark it as an answer