Heyo! If I delete a column from Delta Table schema (because the column has the incorrect datatype and is causing schema mismatch issues) is there any way to query that data + new data together? For example, imagine I have a column Date that is incorrectly typed as a string, so I delete it from the Delta Table and then new data flows in with type date, so now the column gets recreated with the date type (I have merge schemas on). Now if a user queries the delta table for Date, they will only get new data right? Is there any best practice for also getting the old data (even if it means a separate dataframe?)

Dropping column in Delta Lake is designed like any SQL database.... its meant to fully drop the column and its associated data. So its not intended that the "old" data should be query-able.

I see, the issue is I want to keep the table schema clean without duplicate columns. I do know that if I drop a column, then I can't see the column in

df = spark.read.format("delta").load(path)

but I can still see the column with

spark.read.parquet(path)

, so would it make sense to use the different functions, one for current schema and one for historical data (until retention expiry?)

Sounds like you just need to do a "select ... cast(to_date) .. overwrite" (in very simple terms) on your table

technically, you should be able to • add a new column with a random name • load old data and new data into that new column • remove the old column • rename the new column to the name of the old column just make sure to enable column mapping before https://docs.databricks.com/delta/delta-column-mapping.html#how-to-enable-delta-lake-column-mapping

.

How would you have solved this on a regular RDMS system?

Our end users normally interact with Hive external tables pointing to a location like s3 bucket that has a data with a parquet format. In this scenario what I would probably do is that I would just update the table definition to remove the 10 columns , but the underlying parquet will still have the columns its just that when the user query the table, it will not show the removed columns since the hive table definition is already updated. For delta doing this will not work based on my experience since delta catalog will take precedence, thats why I was updating my delta writer version from 2 -> 5 to utilize the table column mapping. When we want to restore it since parquet still contain the data, i would just need to update the table definition. Initially i thought alter drop column will only update the delta table definition but the underlying data is not deleted, turns out my understanding is wrong post reading this thread 🙏

My question was more ... If you had a similar table on an Oracle (or other RDMS) system ... How would you have solved the situation?

Correct me if I am wrong, but normally it would be to 1. restore the table before the point of time where the drop column happened , cons is that we suffer any data generated post the event due to the restoration 2. or we can create a back up of the table per before the drop column and fill up the lost data. When we are done with filling up the missing data, we can make the switch of the original table to the backup version of the table with backfilled missing data. I guess where you are coming from is that to solve this we would need to isolate the blast radius and then made the switch of the data sources of the table once we are ready with the fixed data?

If the data from before the drop is still available (possible in the history of delta) .. I would create a temporary new table with that dataset .. merge that with the current table (so adding the 3 columns back with all the data that was available) . And then find a source for the 2 weeks of missing data for the 3 columns (you didn't loose the 2 weeks of all the other columns)

Understood, valid points. Will go through the usual data recovery process like any other RDMS. thanks for the inputs 🙏

But do have a look if you delta history has the "pre drop" data ...

Sure thats a good call out. Thank you 🙏