Schedule icon
Queries icon
OutputValues icon
Log icon
Switch icon
Assert icon
Fail icon

Propagate source hard deletes to an incrementally loaded table, with guards

Kestra flow that finds rows hard-deleted at the source and deletes them from an incrementally loaded table, but blocks empty, partial or NULL-key source lists.

Categories
Data

An incremental load copies rows that changed since the last run. A row deleted at the source changes nothing, so it stays in the target forever and every report that reads the target keeps counting it. The usual fixes are a CDC tool or a full reload. This flow is the fallback for sources that have neither: it compares the keys the source has now with the keys the target holds, and deletes the difference.

The comparison is the easy part. The dangerous part is trusting the key list. An extract that returns nothing, stops half way, or contains one NULL key makes the table look deleted, and a delete job that believes it empties the target. So the flow applies nothing until four guards pass, and it ends FAILED with the reason when one does not. The demo runs offline on DuckDB with no credentials.

How it works

  1. prepare_demo (io.kestra.plugin.jdbc.duckdb.Queries) is a stand-in for your systems. It writes a target table of 2,000 orders as an earlier incremental load left it, a source key list shaped by demo_scenario, and the expected outcome for the 17 rows that differ.
  2. compare_keys (io.kestra.plugin.jdbc.duckdb.Queries) lists the live target rows whose key is missing from the source and that were not updated inside the grace window. It writes them to candidates, changes nothing, and returns a decision.
  3. result and log_comparison report the counts and the decision.
  4. route (io.kestra.plugin.core.flow.Switch) acts on the decision:
    • APPLY: apply_deletes writes the new target and an audit file, verify_demo checks the outcome, assert_verified (io.kestra.plugin.core.execution.Assert) fails the run on any mismatch.
    • NOTHING_TO_DELETE: log_nothing_to_delete logs it.
    • Any BLOCKED_ decision: block_delete (io.kestra.plugin.core.execution.Fail) ends the run FAILED with the reason. Nothing was changed.
  5. The nightly Schedule is disabled.

The guards

Guard Blocks the run when Why
Empty key list the source key list has 0 keys every target row would look deleted
NULL keys the source key list has any NULL key the key list is broken, and NOT IN would silently find nothing
Ratio candidates are more than max_delete_ratio of the live rows a partial read looks exactly like a mass delete
Grace window never blocks, it excludes rows updated in the last grace_minutes a lagging replica or a load in flight is not a delete

The grace window only delays. A row held today is a candidate on the first run after its window has passed.

The comparison uses NOT EXISTS. NOT IN returns no rows at all when the list contains one NULL, which would turn a broken extract into a silent no-op.

Prerequisites

  • A worker with DuckDB available through the Kestra DuckDB plugin. No Docker image, no network.
  • Secrets: none for the demo. For your own databases, see below.

Quick start

  1. Import the flow into the company.team namespace, or paste it into the editor.
  2. Run it with the defaults. Then run the other scenarios:
Run Inputs Result
1 defaults SUCCESS. 14 of 2,000 live rows (0.7 percent) are missing from the source and are soft deleted. 3 more rows, touched 10 minutes ago, are held by the grace window. 1,986 rows stay live. The audit file has 14 lines. A second comparison on the new target finds 0 candidates.
2 delete_mode: HARD SUCCESS. The same 14 rows are removed. The new target has 1,986 rows.
3 demo_scenario: NO_DELETES SUCCESS. Nothing to delete.
4 demo_scenario: EMPTY_SOURCE FAILED at block_delete. BLOCKED_EMPTY_SOURCE. Nothing was changed.
5 demo_scenario: PARTIAL_READ FAILED at block_delete. BLOCKED_RATIO: 1,204 of 2,000 live rows (60.2 percent) against the allowed 5.0 percent.
6 demo_scenario: NULL_KEY FAILED at block_delete. BLOCKED_NULL_KEYS.
7 demo_scenario: PARTIAL_READ, max_delete_ratio: 1 FAILED at assert_verified. With the ratio guard removed the flow deletes 1,190 rows the source still has. That failure is what the guard prevents.

Failing runs 4 to 6 are the expected result. The errors block logs them.

Inputs

  • demo_scenario (SELECT, default NORMAL, allowed NORMAL, NO_DELETES, EMPTY_SOURCE, PARTIAL_READ, NULL_KEY): the shape of the source key list in the demo.
  • delete_mode (SELECT, default SOFT, allowed SOFT, HARD): SOFT sets deleted_at and deleted_run and keeps the row. HARD removes it.
  • max_delete_ratio (FLOAT, default 0.05): the largest share of live rows one run may delete. Setting it to 1 removes this guard. The empty key list and NULL key guards stay on.
  • grace_minutes (INT, default 60, minimum 0): rows updated more recently than this are never candidates. In the demo the 3 recent rows are 10 minutes old, so a value of 10 or less deletes them too, and the demo's own check expects that.

Outputs

  • outputs.result.values.row: live_rows, source_keys, source_null_keys, candidates, held_by_grace, candidate_pct, max_pct, decision, reason, sample.
  • outputs.compare_keys.outputFiles.candidates: the candidate keys as Parquet.
  • outputs.apply_deletes.outputFiles.target_after: the new target as Parquet.
  • outputs.apply_deletes.outputFiles.delete_audit: one line per row with order_id, mode, last_updated_at, applied_at and execution_id.
  • outputs.verify_result.values.row: missing_deletes, wrong_deletes, live_after, rows_after, audit_rows, second_run_candidates, mismatches.

Expected result

With the defaults the execution ends in SUCCESS. log_comparison reports 2000 live rows, 1983 source keys, 14 candidates (0.7 percent), 3 rows held by the grace window and the decision APPLY. log_applied reports that 14 rows were soft deleted, 1986 rows are live, the audit file has 14 lines and a second comparison on the new target finds 0 candidates.

Using it with your databases

  1. Replace prepare_demo with two reads that write the same two files, target and source_keys, with COPY ... (FORMAT parquet). DuckDB can attach other databases, for example ATTACH 'postgres://...' AS src (TYPE postgres, READ_ONLY). Keep credentials in secret('NAME').
  2. target needs the key column order_id, updated_at (use TIMESTAMPTZ), deleted_at and deleted_run. source_keys needs order_id.
  3. The key list must cover the same rows as the load. If the load filters by region, the key list and the target read must filter the same way.
  4. Replace apply_deletes with a DELETE or an UPDATE in your own warehouse that reads candidates. This flow only exercises the file based version.
  5. Drop verify_demo, verify_result and assert_verified, or keep them as a pattern for your own post-delete check. Keep grace_minutes longer than the gap between the two reads and longer than your replica lag.
  6. Run it after the incremental load. Enable the nightly Schedule, and send the errors block to Slack or e-mail so a blocked run is seen.

Limits

  • One key column, order_id. For a composite key, add the columns to the NOT EXISTS in compare_keys and to the joins in apply_deletes.
  • updated_at has to move forward whenever a row changes at the source, or the grace window protects the wrong rows.
  • A row that comes back at the source after a soft delete is not restored here. The incremental load has to clear deleted_at when it upserts the row.
  • The ratio guard is a net, not proof. A key list that is wrong by a few percent passes it.
  • With a small target, one deleted row can exceed the ratio. Raise max_delete_ratio on purpose.
  • Not tested: real databases, tables of millions of rows, and any DuckDB version other than the one the offline tests used.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.