Let’s say I have table I enabled cdc for.
Now, if I want to add some columns to the table and also remove some columns and preserve the CDC data for the old columns, is it going to mess up the update mask values of the historical data?
Based on my tests, dropping columns can leave gaps in the column_id values and new columns receive new column_id values. The column_id field seems to work like an identity field for columns.
So, even if I re-enable cdc for a table after removing & adding columns, the column ids of new columns should be assigned new numbers therefore the masks for historic data should still work, of course as long as I have all the columns that were ever added to the table stored somewhere (cdc.captured_columns contains only the current captured columns).