Serverless SQL Pools filepath() function and range filters

Question

Hi,

I have a question about why Serverless SQL Pools scans all data in the source folders when I use the filepath() function as a column in a View and join a Date table to it.

EG this query will scan all data in the source folder. The FilePathDate is a column in the [LDW].[vwSalesOrdersLinesBI] view that exposes the filepath() value

select count(fct.OrderID)
from [LDW].[vwSalesOrdersLinesBI] as fct
inner join [LDW].[vwDimDate] dd on dd.Date = fct.[FilePathDate]
where dd.Date between '2016-07-08' and '2016-07-10'

However, if I change the query to use IN and remove the join, then only the specific source folders are queried and data processing is reduced:

select count(fct.OrderID)
from [LDW].[vwSalesOrdersLinesBI] as fct
--where fct.FilePathDate IN (SELECT '2016-07-08' UNION SELECT '2016-07-09' UNION SELECT '2016-07-10')
where fct.FilePathDate IN (CAST( '20160712 00:00:00' AS datetime),CAST( '20160726 00:00:00' AS datetime),CAST( '20160713 00:00:00' AS datetime))

Answer 1

Hi,

If I run that query then I get all the data being scanned 6.49GB (which is the source CSV data in the data lake).

If I run the following query then I only get 29MB scanned

select count(fct.OrderID)
from [LDW].[vwSalesOrdersLinesBI] as fct
inner join [LDW].[vwDimDate] dd on dd.Date = fct.[FilePathDate]
where dd.Date in ('2016-07-08')

It seems that if there are multiple values being passed to the DimDate table to filter, then it doesn't select only the source folders, it just scans all the source data. If a single value is passed in, then it will successfully only scan the folder it needs to.