INSERTS AND DELETES in a massive way for Lakeflow Spark Declarative Pipelines

[saelozanogo]

Hello,

I'm creating a Lakeflow Spark declarative pipeline to migrate a process that was previously handled by a custom pipeline. There is one component that is a bit complicated for me: we have a table that incrementally adds records but, at the same time, deletes records based on the data's lifecycle.

The process is actually very simple. We have a historical table with an integer column that expresses the DATE in YYYYMM format. For example, if records have a 2-year lifecycle and the current ingested data is 202604, then we need to DELETE data prior to 202406.

Initially, we tried using sinks with ForEachSink, but the problem is that we can't track dependencies between bronze -> silver -> because the sinks are flow-based and produce tables that are not managed by Databricks. As a result, we cannot guarantee that a bronze process runs before a silver process.

Next, we tried with CDC, which gets new records with an 'INSERT' value in a column. From the previous stage, we got the records to be deleted and masked them with 'DELETE' in the columns. Finally, we used unionByName to get the source table and use it in the CDC, but this failed due to a circular dependency.

There is an alternative of creating a support_table with the keys of the target table to avoid the circular dependency, but I think this is an anti-pattern for LSDP.

What would be a suitable solution for these deletion cases, and how can I execute this task using Lakeflow Spark Declarative Pipelines?

[binlogreader]

interesting challenge, and we will be working on something similar in coming months. I think the way out is to not model the deletions as events. They are not events arriving from a source, they are a property of the dataset ie, the table should only contain the last two years. There are two possible approaches:

(a) If queries just need to see the window, make retention part of the definition. Keep bronze append-only and define silver as a materialized view:

```sql

CREATE MATERIALIZED VIEW silver_history AS

SELECT * FROM bronze_history

WHERE yyyymm >= cast(date_format(add_months(current_date(), -24), 'yyyyMM') as int)

```

That removes the delete events, the circular dependency, and the support table, and ordering is handled by the pipeline graph. The caveat is cost. A predicate that moves with current_date() is non-deterministic, which tends to prevent incremental refresh, so on a large table each refresh can recompute the whole window.

(b) If the rows must physically leave the table, or it is too large to keep recomputing, keep the pipeline append-only and delete outside it using a scheduled `DELETE FROM silver_history WHERE yyyymm < :cutoff`, or a SQL task after the pipeline task in the same job. Cluster the table by yyyymm and the monthly delete mostly drops whole files, so it stays cheap as the table grows.