@minhhung0507 

Short Answer; This requires custom design implementation:

1. DLT does not natively “auto-save” records dropped by watermark. You must configure a secondary output or pipeline to capture potentially late records (e.g., ingest the same source with a more permissive watermark or no watermark at all).
2. Once saved, reprocess those late records with APPLY CHANGES (or a merge-based approach) into your main table. Use a unique key and the DEDUPLICATE ON clause to avoid duplicates.

Details (Example):

1. Capturing Dropped Records

   • Separate “Late Events” Table: Create a second DLT table reading from the same source but with a more relaxed watermark (or no watermark) to capture events that arrive after the main pipeline’s cutoff. This effectively quarantines all potential late events.
   • Example:

     CREATE STREAMING LIVE TABLE late_events
     AS SELECT * 
     FROM STREAM(live.source_data)
     -- Optionally set a larger watermark or omit it here

      
   • Why: DLT itself does not offer a built-in “auto-save dropped records” feature, so this extra DLT table prevents data loss and isolates potentially late data.

2. Reprocessing & Updating the Final Table

   • Use APPLY CHANGES: Point it to both your main input table and the late events table—either in the same pipeline or in a separate “cleanup” pipeline.
   • Example:

     APPLY CHANGES INTO live.final_table
       FROM STREAM(live.late_events)
       KEYS (id)
       SEQUENCE BY event_time
       DEDUPLICATE ON (id)
       WHEN NOT MATCHED THEN INSERT *
       WHEN MATCHED THEN UPDATE SET *

      
   • Deduplication: Rely on a unique key (id) plus DEDUPLICATE ON to avoid duplicates when reprocessing late arrivals.

3. Auto-Triggering Updates

   • Incremental Pipeline: Schedule a job to process the quarantined late_events table on a periodic basis. This way, any new late arrivals automatically flow in.
   • Best Practice: Keep your main pipeline’s watermark at a balanced threshold for normal loads. Let the “late events” pipeline handle stragglers in batches to avoid ballooning resource usage.

4. Efficiency Tips

   • Monitoring: Track late arrivals in the “late_events” table. If volumes are consistently high, you may need to adjust your main pipeline’s watermark, so this is a great benefit.
   • Resource Usage: Keep your “late events” pipeline idle between scheduled runs if possible, minimizing overhead and costs.

By splitting your pipeline into “on-time processing” (with a reasonable watermark) and “late-arrivals handling” (with no/loose watermark), you ensure minimal data loss and an automated path to reconcile late records without duplications.

But... Having said that, simply extending the watermark might still be the simplest solution—provided the use case can handle the increased state and potential higher latency.

Questions and considerations you should consider: 

• Frequency of Late Events: How common are these late arrivals? If they’re rare, slightly extending the watermark might be enough.
• Resource Constraints: Can your cluster handle the extra memory overhead of a larger watermark window?
• Acceptable Latency: If you extend the watermark, are you prepared for a longer overall processing delay?
• Downstream Impact: Will delaying your final table updates to accommodate out-of-order data create downstream bottlenecks?
• Business Requirements: Do you actually need 100% completeness in near real-time, or can later arrived records be reconciled less frequently?

These questions help decide if adjusting the watermark alone addresses your needs or if you truly need a more involved multi-pipeline approach.

Hope this is helpful. Please remember to follow common best practices and test it in a development environment before deploying it in production; this is not a trivial change.