1

We have an elt process to store data partitioned by Year in a delta lake processed through Databricks. In Databricks the queried location displays data correctly with no duplications and no total count variation. When I create a view using Synapse Serverless to the same partitioned data is displayed with duplicates after an update happens to the data, when data is created for the first time no issues whatsoever. I have troubleshot and found that it only happens when using views to partitioned data after an update. If using external table with no partition specified, the results are correct as well.

Delta Lake partitioned data overview

enter image description here

On Databricks data is correctly read.

select PKCOLUMNS, count(*) from mytable group by PKCOLUMNS having count(*)>1 -- no duplicates

select count(*) from mytable --407,421

On Synapse Serverless

CREATE VIEW MY_TABLE_VIEW AS 
SELECT *, 
results.filepath(1) as [Year]
FROM
OPENROWSET(
BULK 'mytable/Year=*/*.parquet',
DATA_SOURCE = 'DeltaLakeStorage',
FORMAT = 'PARQUET'
) 
WITH(
[param1] nvarchar(4000),
[param2] float,
[PKCOLUMNS] nvarchar(4000)
) AS [results]
GO
select PKCOLUMNS, count(*) from mytable
group by PKCOLUMNS
having count(*)>1 --duplicates
GO
select PKCOLUMNS, count(*) from mytable
group by PKCOLUMNS
having count(*)>1 --814,842
vimuth
  • 5,064
  • 33
  • 79
  • 116
devhack_wasd
  • 11
  • 1
  • 1
    You say it is Delta storage, but your OPENROWSET is using the Parquet format. If your Delta table has versions, which would happen after an update, then you are querying both the current and historical versions. Try using Format = 'delta' instead of parquet, which should just read the current version. – Joel Cochran Mar 27 '23 at 20:46

1 Answers1

0

The issue is related to the view created in Synapse Serverless.

Instead of using 'OPENROWSET' to access the Delta Lake files directly, you Can try creating an external table example (EXT.EDW_Table1) in Synapse that points to the Delta Lake files.

In this way, you can get the Delta Lake metadata to automatically partition the data and ensure that the partitions are correctly updated when data changes.

Step 1:

CREATE VIEW
CREATE EXTERNAL TABLE external_Table1 
param1 string,
param2 float,
PKCOLUMNS string
)
 PARTITIONED BY (Year string)
STORED AS PARQUET
 LOCATION 'external_Table1

In step 1 delta lake (parquet) files are stored in "external_Table1" and Partition by the YEAR Column.

enter image description here

Step 2:

Try SELECT to check for the duplicates in the VIEW.

SELECT PKCOLUMNS, COUNT(*) FROM external_Table1 
GROUP BY PKCOLUMNS HAVING COUNT(*) > 1;

enter image description here

If you still see duplicate values after the update. Tyr using the VACUUM operation on the Delta Lake files. Step 3:

%sql
VACUUM external_Table1;

Please note that the VACUUM operation can take some time to complete depending on the size of your data. enter image description here

Naveen Sharma
  • 349
  • 2
  • 4