Optimizing SCD Type 2:

• Ensure that the column used for sequencing is a sortable data type.
• Handle out-of-sequence records by specifying a column in the source data that represents the proper ordering of the source data.1
• Use the track_history_except_column_list option to exclude certain columns from history tracking if needed.

Example for SCD Type 2 with History Tracking:

dlt.apply_changes(

    target="target",

    source="users",

    keys=["userId"],

    sequence_by=col("sequenceNum"),

    apply_as_deletes=expr("operation = 'DELETE'"),

    except_column_list=["operation", "sequenceNum"],

    stored_as_scd_type=2,

    track_history_except_column_list=["city"]

)

By following these best practices and utilizing the APPLY CHANGES API, you can effectively implement and optimize SCD Type 2 in your Delta Live Tables pipeline with Structured Streaming. For more detailed information, you can refer to the Delta Live Tables documentation on CDC.