Deleted schema leads to DLT pipeline problems

[jorhona]

Hello. 

When testing a dlt table pipeline i accidentally mispelt the target schema. The pipeline worked and created the tables. After realising my mistake, i deleted the tables and the schema - thinking nothing of it. 

However when I run the pipeline with the corrected schema, I get the following error : 

"""

Soft-deleted MV/STs that require changes cannot be undropped directly. If you need to update the target schema of the pipeline or modify the visibility of an MV/ST while also undropping it, please invoke the undrop operation with the original schema and visibility in an update first, before applying the changes in a subsequent update.

The following soft-deleted MV/STs required changes: table1, table2 etc etc 

"""

I cant get back the tables to drop them properly.

What do i do ?

Thanks 🙂 

[SP_6721]

Hi @jorhona ,

This error happens because DLT marks deleted tables as "soft-deleted" in Unity Catalog, and they need to be restored to their original state before you can make changes.
To resolve this:

1. Recover the misspelled schema and tables:
   UNDROP SCHEMA catalog_name.<typo_schema>;
   UNDROP TABLE catalog_name.<typo_schema>.table1;
2. Then drop them properly:
   DROP TABLE catalog_name.<typo_schema>.table1;
   DROP SCHEMA catalog_name.<typo_schema>;

Once that's done, you can rerun your pipeline using the corrected schema without hitting the soft-deletion issue.

[jorhona · Accepted solution]

In the end i deleted and recreated the pipeline which fixed the problem. 

Luckily it was only in dev so didnt lose any history of pipeline success etc in prod. 

Still, is a bit of a pain for dlt, along with the problem of multiple developers not being able to work on a single pipeline "this table belongs to another pipeline".