https://community.databricks.com/t5/data-engineering/dlt-handling-merge/m-p/109193#M43237 <P class="_1t7bu9h1 paragraph"><SPAN>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:</SPAN></P> <OL> <LI> <P class="_1t7bu9h1 paragraph"><SPAN><STRONG>Incremental Data Reading from Apply Changes Table</STRONG>:</SPAN></P> <UL class="_1t7bu9h7 _1t7bu9h2"> <LI><SPAN>Ensure that the Apply Changes table is set up to capture changes using the <CODE>APPLY CHANGES</CODE> API. This API is designed to handle change data capture (CDC) efficiently.</SPAN></LI> <LI><SPAN>Use the <CODE>apply_changes()</CODE> function in Python to specify the source, keys, and sequencing for the change feed. This function will help you process changes incrementally.</SPAN></LI> </UL> </LI> <LI> <P class="_1t7bu9h1 paragraph"><STRONG>Handling Out-of-Order Data</STRONG>:</P> <UL class="_1t7bu9h7 _1t7bu9h2"> <LI><SPAN>The <CODE>APPLY CHANGES</CODE> 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.</SPAN></LI> </UL> </LI> <LI> <P class="_1t7bu9h1 paragraph"><STRONG>Joining Tables with Time Lag</STRONG>:</P> <UL class="_1t7bu9h7 _1t7bu9h2"> <LI>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.</LI> <LI><SPAN>Use the <CODE>apply_changes()</CODE> 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.</SPAN></LI> </UL> </LI> </OL> Thu, 06 Feb 2025 13:29:25 GMT Walter_C 2025-02-06T13:29:25Z https://community.databricks.com/t5/data-engineering/dlt-handling-merge/m-p/109188#M43235 <P>Hi,</P><P>In our DLT pipeline we are reading two tables. One a Apply Changes table Delta table and a streaming live table. We are able to read the latest records from the streaming live table incrementally but from the apply changes we are not able to read the incremental data. We now need to manage joining these two tables in real time to do SCD Type 1 in our target DLT table.<BR />Challenges: Both the source though has the common records can come with a time lag, so real time inner join we are losing records. Reading the apply changes incremental we are unable to do.&nbsp;<BR />So how to handle this situation to get the records from both the sources inserted/updated into our target DLT without losing records?</P> Thu, 06 Feb 2025 13:11:52 GMT https://community.databricks.com/t5/data-engineering/dlt-handling-merge/m-p/109188#M43235 JothyGanesan 2025-02-06T13:11:52Z https://community.databricks.com/t5/data-engineering/dlt-handling-merge/m-p/109193#M43237 <P class="_1t7bu9h1 paragraph"><SPAN>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:</SPAN></P> <OL> <LI> <P class="_1t7bu9h1 paragraph"><SPAN><STRONG>Incremental Data Reading from Apply Changes Table</STRONG>:</SPAN></P> <UL class="_1t7bu9h7 _1t7bu9h2"> <LI><SPAN>Ensure that the Apply Changes table is set up to capture changes using the <CODE>APPLY CHANGES</CODE> API. This API is designed to handle change data capture (CDC) efficiently.</SPAN></LI> <LI><SPAN>Use the <CODE>apply_changes()</CODE> function in Python to specify the source, keys, and sequencing for the change feed. This function will help you process changes incrementally.</SPAN></LI> </UL> </LI> <LI> <P class="_1t7bu9h1 paragraph"><STRONG>Handling Out-of-Order Data</STRONG>:</P> <UL class="_1t7bu9h7 _1t7bu9h2"> <LI><SPAN>The <CODE>APPLY CHANGES</CODE> 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.</SPAN></LI> </UL> </LI> <LI> <P class="_1t7bu9h1 paragraph"><STRONG>Joining Tables with Time Lag</STRONG>:</P> <UL class="_1t7bu9h7 _1t7bu9h2"> <LI>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.</LI> <LI><SPAN>Use the <CODE>apply_changes()</CODE> 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.</SPAN></LI> </UL> </LI> </OL> Thu, 06 Feb 2025 13:29:25 GMT https://community.databricks.com/t5/data-engineering/dlt-handling-merge/m-p/109193#M43237 Walter_C 2025-02-06T13:29:25Z