• The error arises because we have streaming table for the silver table, but its source (the bronze table with dlt.apply_changes) isn't append-only.
• Streaming tables expect only new data to be added, not updates or deletes.

In simpler words the silver layer table does not expect the rows to get deleted from the source table but in your case you are trying to do a apply_changes which essentially means bronze table rows can be deleted based on the condition you define and that's the reason you are getting this error. 

1. Resolving the Issue:

• Change the silver table to a "live table" instead of a "streaming live table." Live tables inherently support updates and deletes, aligning with the bronze table's nature after dlt.apply_changes.

2. Reconsider Data Flow:

• If converting to a live table isn't feasible, restructure your pipeline:
  • Store the raw data in a separate table/layer without applying dlt.apply_changes.
  • Create a new silver table that directly consumes the raw data and handles any updates or deletes appropriately.

3. Full Refresh (Temporary Fix):

• While a full refresh can clear the error, it's a temporary workaround and not a sustainable solution.
• It clears and reloads all data, potentially causing performance issues and data loss if not handled carefully.

Personal Recommendation:

• Ensure data consistency between bronze and silver tables, especially regarding updates and deletes. 
• Have apply changes in silver layer or any other layer except bronze as it is a ideal practice to keep raw data without any transformations in bronze layer.
• Prioritize converting the silver table to a live table for a more robust and long-term solution.
• If that's not possible, consider restructuring your pipeline to manage updates and deletes effectively.
• Use full refresh only as a temporary measure and explore sustainable alternatives.