Delta live tables - ignore updates on some columns

[Anish_2]

Hello Team,

I have scenario where in apply_changes, i want to ignore updates on 1 column. Is there any way we can achieve this in Delta live tables?

[ashraf1395]

Hi there @Anish_2 , 
Yes you can do that 

Here is the doc link : https://docs.databricks.com/aws/en/dlt/cdc?language=Python

For python you can simply add an attribute except_columns_list like this

dlt.apply_changes(
  target = "target",
  source = "users",
  keys = ["userId"],
  sequence_by = col("sequenceNum"),
  apply_as_deletes = expr("operation = 'DELETE'"),
  apply_as_truncates = expr("operation = 'TRUNCATE'"),
  except_column_list = ["operation", "sequenceNum"],
  stored_as_scd_type = 1
)

 and for SQL you can use it like this columns * execpt
 

-- Create a streaming table, then use apply changes into to populate it:
CREATE OR REFRESH STREAMING TABLE target;

APPLY CHANGES INTO target
  FROM stream(cdc_data.users)
  KEYS (userId)
  APPLY AS DELETE WHEN operation = "DELETE"
  SEQUENCE BY sequenceNum
  COLUMNS * EXCEPT (operation, sequenceNum)
  STORED AS SCD TYPE 2
  TRACK HISTORY ON * EXCEPT (city)

[juice]

I tested adding a column to except_column_list and the behavior I experienced was that the column gets completely dropped from the target.  The functionality I need (and what OP is looking for) is for a particular column to be ignored during an update.

I'm quite stuck on this.