Delta Live Table automatic table removal and schema update

[hayden_blair]

Hello, 

I made a delta live table workflow that created 3 streaming tables in unity catalog. I then removed the source code for the 3rd table from the workflow and reran. After about a week, the 3rd streaming table is no longer available in unity catalog and appears to have been automatically dropped by Databricks.

In this post, @Retired_mod describes this as expected behavior.

I am now trying to add that 3rd table back into the workflow, but one of the columns has changed data types (double => long). I am getting the following error:

"[DELTA_MERGE_INCOMPATIBLE_DATATYPE] Failed to merge incompatible data types DoubleType and LongType"

The 3rd table has been dropped from unity catalog, but the schema of the original table still persists somewhere. How do I restore the old records of the original table using the new schema so that I can resume streaming with the new schema, without losing historical records?

Thank you.

[raphaelblg · Accepted solution]

Hi @hayden_blair, 

I don't think that you will be able to recover your old table. But if you want to recreate it, please add it to the source code again, validate the DLT workflow by using the "validate" option and then run a FULL REFRESH operation on the target table. This should get the table recreated under the new schema. 

You can try the UNDROP command but I'm not sure if this should work, https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-undrop-table.html.

[hayden_blair]

This makes sense @raphaelblg! 

Just to confirm my understanding, is the following statement true:

If I remove the source code for a unity catalog DLT streaming table from a DLT pipeline and wait 7 days, that table will be dropped from unity catalog, and historical records will not be available without a full refresh of the pipeline.

[raphaelblg]

Correct! @hayden_blair