Updates on a column in delta table with downstream autoloader

[Wijnand]

I've got the following questions:

1. Can I pause autoloader jobs, delete cluster that was used to run these jobs, create a new cluster and run jobs with newer version cluster?

2. I have one autoloader job that ingests JSONs and transforms this to a delta table, and one autoloader job that reads the delta table and appends this data to a second delta table in another location. What to do if I want to update a column on the first delta table? I've tried this small scale, and the second job breaks because it couldn't handle the update. Threw up the following error message:

com.databricks.sql.transaction.tahoe.DeltaUnsupportedOperationException: Detected a data update (for example part-00000-6e380ba1-f9ff-4938-9152-d989ed2413ab.c000.snappy.parquet) in the source table at version 13. This is currently not supported. If you'd like to ignore updates, set the option 'skipChangeCommits' to 'true'. If you would like the data update to be reflected, please restart this query with a fresh checkpoint directory. The source table can be found at path dbfs:/user/hive/warehouse/target_table.

So I've tried restarting the query with a fresh checkpoint directory, and it reprocessed all records in delta table 1. The actual flow deals with a massive number of records, so reprocessing is very much not preferred.

[Kaniz_Fatma · Accepted solution]

Hi @Wijnand,

Pausing Autoloader Jobs and Updating Clusters: Yes, you can pause autoloader jobs, delete the cluste.... You can pause a job in the Jobs UI in the job configuration settings. There is a “schedule type” flag that allows you to select pause/manual as an option. You can also do so by updating the schedule via the Jobs API (it would be within the Cron Schedule f.... After pausing the jobs, you can delete the old cluster and create a new one.

Updating a Column in a Delta Table: If you want to update a column on the first delta table, you can.... However, the error message you’re seeing is because Delta Lake’s streaming source does not support u.... If you’d like to ignore updates, you can set the option ‘ignoreChanges’ to 'true’. But this will cause all the records from the modified file to be processed again, which includes the.... If reprocessing all records is not preferred due to the massive number of records, you might need to consider a different approach for your data pipeline. One possible solution could be to read from the raw table for further aggregations instead of readin....