Partition pruning in Synapse serverless on Deltalake

Question

Hey,

I have a storage account with 2 containers:

1. Big data with many Parquet files without any special format
2. Big data in Parquet files with Delta format (with "_delta_log" folder)

I'm trying to query the data using Synapse, I created an EXTERNAL DATA SOURCE that pints to container 2 (see above) and a VIEW with OPENROWSET that uses:

• The new external data source
• BULK '/'
• FORMAT = 'delta'

I'm able to query the data and also see my partitions, but partition pruning is not working. Even when I filter (with WHERE clause) on the partition column Synapse is scanning the entire table.

What I'm missing here?

Thanks.

Answer 1

@Shlomi Lanton Yes, that could be the issue. Please ensure partition column is of a supported data type, such as integer or date.

To enable partition pruning, please try changing the data type of the partition column to a supported data type, such as date as explained in example provide in below blog or the public document:

1. Alternatives in Serverless SQL Pools for Delta
   Example view query:

CREATE VIEW LDW.vwWebTelemetryDelta
AS
SELECT 
    UserID,
    EventType,
    ProductID,
    [URL] AS ProductURL,
    Device,
    SessionViewSeconds,
    EventYear,
    EventMonth,
    EventDate
FROM
 OPENROWSET
(
    BULK 'cleansed/webtelemetrydeltatwo',
    DATA_SOURCE = 'ExternalDataSourceDataLakeUKMI',
    FORMAT = 'DELTA'
) 
WITH
(
    UserID INT,
    EventType VARCHAR(20),
    ProductID SMALLINT,
    URL VARCHAR(25),
    Device VARCHAR(10),
    SessionViewSeconds INT,
    EventYear SMALLINT,
    EventMonth TINYINT,
    EventDate DATE
) AS fct

1. Query partitioned data
   

Hope this info helps. Let us know how it goes.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Can you verify that the columns you use in your WHERE clause match the columns used for partitioning in Delta Lake. Even a small discrepancy can lead to a full table scan.

Check the _delta_log folder to confirm that the metadata accurately reflects the current partitioning scheme.

Please check and revert to us !