Permission Error When Running DELETE FROM

[Carsten03]

Hi,

I want to remove duplicate rows from my managed delta table in my unity catalog. I use a query on a SQL warehouse similar to this:

WITH cte AS (
SELECT 
id, ROW_NUMBER() OVER (PARTITION BY id,##,##,## ORDER BY ts) AS row_num
FROM 
    catalog.schema.table
) DELETE FROM cte WHERE row_num > 1;

Using this I get following error:

Could not verify permissions for DeleteFromTable (row_num#734 > 1)

I checked the grants where I have ALL PRIVILEGES and the IAM Role (we are on AWS) for the external location and I have permissions to delete from S3. Is there anything else I have to set to be able to delete rows or is there anything wrong with my query? I couldn't find anything online.

[Carsten03]

I have first tried to use _metadata.row_index to delete the correct rows but also this resulted in an error. My solution was now to use spark and overwrite the table.

table_name = "catalog.schema.table"
df = spark.read.table(table_name)
count_df = df.count()
dedup = df.drop_duplicates()
count_dedup = dedup.count()
count_df - count_dedup
dedup.write.mode("overwrite").saveAsTable(table_name)
assert spark.read.table(table_name).count() == count_dedup

I would still be interested if anyone has a good approach using SQL for this.

[ac0]

Pitching in here since i had the same problem: the issue you can't delete from a CTE; you need to delete from the actual table which can be joined to the CTE.