To address the challenges you are facing with your Delta Live Tables (DLT) pipeline, here are some steps and considerations to help you manage the incremental data reading and joining of the Apply Changes table and the streaming live table for SCD Type 1 processing:

1. Incremental Data Reading from Apply Changes Table:

   • Ensure that the Apply Changes table is set up to capture changes using the APPLY CHANGES API. This API is designed to handle change data capture (CDC) efficiently.
   • Use the apply_changes() function in Python to specify the source, keys, and sequencing for the change feed. This function will help you process changes incrementally.

2. Handling Out-of-Order Data:

   • The APPLY CHANGES API automatically handles out-of-sequence records, ensuring correct processing of CDC records. You need to specify a column in the source data to sequence records, which Delta Live Tables interprets as a monotonically increasing representation of the proper ordering of the source data.

3. Joining Tables with Time Lag:

   • To manage the time lag between the two sources, consider using a watermark to handle late data. This can help ensure that you do not lose records during the join operation.
   • Use the apply_changes() function to create a streaming table and then join it with the streaming live table. This approach ensures that both tables are processed in real-time.