Hi @valiro21 ,

Your scenario is a classic late-arriving and incremental refresh data challenge. While you could build custom logic to track these changes, there is a native approach in Databricks that simplifies this significantly: Lakeflow Spark Declarative Pipelines with Materialized Views. Before addressing your questions, I would like you to consider this approach first.

This approach effectively turns a complex "change-driven" problem into a simple, declarative one. Here is how I’d structure your solution to achieve the best performance-to-maintenance ratio:

The Recommended Pattern: Materialized Views

Instead of manually choosing between a "Full Reprocess" or a custom "CDC" engine, use Materialized Views (MVs). The engine automatically detects which source data has changed and refreshes only the affected monthly buckets.

Requirements:

1. Serverless Compute: Pipeline should run on serverless to unlock incremental refresh for Materialized Views.

2. Deterministic UDFs: Ensure your Python UDF is registered in Unity Catalog and marked as DETERMINISTIC. This is crucial for the engine to safely incrementalize the results.

3. The "PySpark Challenge": While your UDF works as a black box, I highly recommend rewriting the logic into native PySpark / SQL. This allows the optimizer to "see" the logic, enabling much more granular incremental updates and significantly lower compute costs. LLM assistants are great help for this.

4. Source Table Tuning: Enable Deletion Vectors and Row Tracking on your source Delta table. These features allow the engine to pinpoint exactly which rows were updated or deleted without scanning entire files.

The initial run will reprocess the whole history of the table. After new data was ingested to the source table, run the pipeline update again. Monitor the event_log to know if update was incremental. There might be some adjustments needed to achieve incrementalization. Refer to this page for more guidance. 

Hope it helps