Hi @rrajan ,

The simplest solution is to check the max timestamp in each gold table when processing incrementally to get source data. Here's how you can handle this (this would be your source in MERGE statement with every rum:

SELECT * FROM silver AS s
WHERE s.last_update_timestamp 
   > (SELECT MAX(last_update_timestamp) FROM gold_table_1)

Similarly for the other gold tables.

This way:

• If one gold table fails, other tables' timestamps remain unchanged
• On retry, you'll only process records newer than what's already in each gold table
• No need for additional tracking tables or complex logic

For more robust pipelines, you could consider:

1. A separate metadata table to avoid scanning full gold tables for timestamps

CREATE TABLE metadata_control (
    table_name STRING,
    last_processed_timestamp TIMESTAMP
)

In this scenario, when loading each gold table, you filter your silver source data based on the last processed timestamp for that specific gold table:

SELECT * FROM silver 
WHERE last_update_timestamp > (
    SELECT last_processed_timestamp 
    FROM metadata_control 
    WHERE table_name = 'gold_table_1'
)

After successful processing of each gold table, you update its timestamp in the metadata table. This ensures that if a later table fails, you won't reprocess already loaded data on retry.

2. You could also consider using Structured Streaming with separate checkpoints for each gold table as an alternative approach. This provides automatic failure handling and exactly-once guarantees. See the Databricks documentation on streaming writes for details: https://docs.databricks.com/en/structured-streaming/delta-lake.html