id: hard-delete-propagation-guard
namespace: company.team
description: >
Find rows that were hard-deleted at the source and propagate those deletes to
a table that is loaded incrementally, but only after guards confirm the source
key list can be trusted.
concurrency:
limit: 1
behavior: QUEUE
inputs:
- id: demo_scenario
type: SELECT
displayName: Demo scenario
description: >
NORMAL: 14 rows were deleted at the source, and 3 recently touched rows
are missing from a lagging key list. NO_DELETES: the source still has
every row. EMPTY_SOURCE: the key list came back empty. PARTIAL_READ: the
key list stops at 40 percent of the keys. NULL_KEY: the key list contains
a NULL key.
values:
- NORMAL
- NO_DELETES
- EMPTY_SOURCE
- PARTIAL_READ
- NULL_KEY
defaults: NORMAL
- id: delete_mode
type: SELECT
displayName: Delete mode
description: SOFT keeps the row and stamps deleted_at and deleted_run. HARD
removes the row from the target.
values:
- SOFT
- HARD
defaults: SOFT
- id: max_delete_ratio
type: FLOAT
displayName: Max share of live rows to delete
description: The run is blocked when more than this share of the live target
rows is missing from the source, for example 0.05 for 5 percent. A partial
source read looks exactly like a mass delete.
defaults: 0.05
- id: grace_minutes
type: INT
displayName: Grace window in minutes
description: A row updated within this many minutes is never treated as deleted,
so replica lag or a load in flight is not mistaken for a delete. Keep it
longer than the gap between reading the target and reading the source. The
demo rows touched recently are 10 minutes old.
defaults: 60
min: 0
triggers:
- id: nightly
type: io.kestra.plugin.core.trigger.Schedule
description: Disabled. In a real pipeline, run this after the incremental load.
In this demo every execution builds new tables, so enabling it only
repeats the demo.
cron: "30 3 * * *"
disabled: true
tasks:
- id: prepare_demo
type: io.kestra.plugin.jdbc.duckdb.Queries
description: >
Stand-in for your systems. Builds a target table of 2,000 orders as an
earlier incremental load left it, a list of source keys as the source
reports them now (shaped by demo_scenario), and the expected outcome for
the rows that differ. In real use, read your own target table and your own
source key list instead.
fetchType: FETCH_ONE
outputFiles:
- target
- source_keys
- expected
sql: |
-- The target as the incremental load left it. Every order is loaded and none is deleted yet.
CREATE TABLE tgt AS
SELECT i AS order_id,
1 + (i * 7) % 500 AS customer_id,
round(5 + ((i * 37) % 4950) / 10.0, 2) AS amount,
now() - to_hours(24 + (i * 13) % 2000) AS updated_at,
CAST(NULL AS TIMESTAMPTZ) AS deleted_at,
CAST(NULL AS VARCHAR) AS deleted_run
FROM range(1, 2001) t(i);
-- 14 orders were deleted at the source long ago. The incremental load never saw that.
CREATE TABLE gone AS
SELECT * FROM (VALUES (11), (57), (230), (231), (498), (777), (801), (1203), (1500), (1666), (1777), (1888), (1950), (1999)) v(order_id);
-- 3 more orders were touched 10 minutes ago. A lagging source key list does not show them yet.
CREATE TABLE recent AS
SELECT * FROM (VALUES (42), (1004), (2000)) v(order_id);
UPDATE tgt SET updated_at = now() - INTERVAL 10 MINUTE WHERE order_id IN (SELECT order_id FROM recent);
-- The source key list, shaped by the scenario.
CREATE TABLE src AS
SELECT order_id FROM tgt
WHERE CASE '{{ inputs.demo_scenario }}'
WHEN 'NO_DELETES' THEN true
WHEN 'EMPTY_SOURCE' THEN false
WHEN 'PARTIAL_READ' THEN order_id <= 800 AND order_id NOT IN (SELECT order_id FROM gone UNION SELECT order_id FROM recent)
ELSE order_id NOT IN (SELECT order_id FROM gone UNION SELECT order_id FROM recent)
END;
INSERT INTO src SELECT CAST(NULL AS BIGINT) WHERE '{{ inputs.demo_scenario }}' = 'NULL_KEY';
-- What a correct run must do with the 17 rows that differ. A row is held when it is younger than the grace window.
CREATE TABLE expected AS
SELECT t.order_id,
CASE WHEN epoch(now() - t.updated_at) / 60 < {{ inputs.grace_minutes }} THEN 'HELD' ELSE 'DELETED' END AS outcome
FROM tgt t
WHERE t.order_id IN (SELECT order_id FROM gone UNION SELECT order_id FROM recent);
COPY tgt TO '{{ outputFiles.target }}' (FORMAT parquet);
COPY src TO '{{ outputFiles.source_keys }}' (FORMAT parquet);
COPY expected TO '{{ outputFiles.expected }}' (FORMAT parquet);
SELECT (SELECT count(*) FROM tgt) AS target_rows,
(SELECT count(*) FROM src) AS source_keys,
(SELECT count(*) FROM expected WHERE outcome = 'DELETED') AS expected_deleted,
(SELECT count(*) FROM expected WHERE outcome = 'HELD') AS expected_held;
- id: compare_keys
type: io.kestra.plugin.jdbc.duckdb.Queries
description: >
Find the live target rows whose key is missing from the source key list
and that were not updated inside the grace window, then decide: APPLY,
NOTHING_TO_DELETE, or one of three BLOCKED reasons. It changes nothing.
inputFiles:
target.parquet: "{{ outputs.prepare_demo.outputFiles.target }}"
source_keys.parquet: "{{ outputs.prepare_demo.outputFiles.source_keys }}"
fetchType: FETCH_ONE
outputFiles:
- candidates
sql: |
CREATE TABLE tgt AS SELECT * FROM read_parquet('target.parquet');
CREATE TABLE src AS SELECT order_id FROM read_parquet('source_keys.parquet');
-- NOT EXISTS, not NOT IN. With one NULL in the key list, NOT IN finds nothing at all.
CREATE TABLE candidates AS
SELECT DISTINCT t.order_id
FROM tgt t
WHERE t.order_id IS NOT NULL
AND t.deleted_at IS NULL
AND t.updated_at < now() - to_minutes({{ inputs.grace_minutes }})
AND NOT EXISTS (SELECT 1 FROM src x WHERE x.order_id = t.order_id);
COPY candidates TO '{{ outputFiles.candidates }}' (FORMAT parquet);
WITH stats AS (
SELECT
(SELECT count(*) FROM tgt WHERE deleted_at IS NULL) AS live_rows,
(SELECT count(*) FROM src) AS source_keys,
(SELECT count(*) FROM src WHERE order_id IS NULL) AS source_null_keys,
(SELECT count(*) FROM candidates) AS candidates,
(SELECT count(*) FROM tgt t
WHERE t.order_id IS NOT NULL AND t.deleted_at IS NULL
AND t.updated_at >= now() - to_minutes({{ inputs.grace_minutes }})
AND NOT EXISTS (SELECT 1 FROM src x WHERE x.order_id = t.order_id)) AS held_by_grace,
CAST({{ inputs.max_delete_ratio }} AS DOUBLE) AS max_ratio
), decided AS (
SELECT *,
coalesce(round(100.0 * candidates / nullif(live_rows, 0), 2), 0) AS candidate_pct,
round(100.0 * max_ratio, 2) AS max_pct,
CASE WHEN source_keys = 0 THEN 'BLOCKED_EMPTY_SOURCE'
WHEN source_null_keys > 0 THEN 'BLOCKED_NULL_KEYS'
WHEN candidates = 0 THEN 'NOTHING_TO_DELETE'
WHEN candidates > max_ratio * live_rows THEN 'BLOCKED_RATIO'
ELSE 'APPLY' END AS decision
FROM stats
)
SELECT live_rows, source_keys, source_null_keys, candidates, held_by_grace, candidate_pct, max_pct, decision,
CASE decision
WHEN 'BLOCKED_EMPTY_SOURCE' THEN 'The source key list is empty, so every row would look deleted. Nothing was changed.'
WHEN 'BLOCKED_NULL_KEYS' THEN 'The source key list has ' || source_null_keys || ' NULL key(s). Fix the extract first. Nothing was changed.'
WHEN 'BLOCKED_RATIO' THEN candidates || ' of ' || live_rows || ' live rows (' || candidate_pct || ' percent) would be deleted, more than the allowed ' || max_pct || ' percent. A partial source read looks exactly like this. Nothing was changed.'
WHEN 'NOTHING_TO_DELETE' THEN 'No live row is missing from the source outside the grace window.'
ELSE candidates || ' live row(s) are missing from the source and were not updated in the last {{ inputs.grace_minutes }} minutes.'
END AS reason,
(SELECT coalesce(string_agg(CAST(order_id AS VARCHAR), ', ' ORDER BY order_id), '') FROM (SELECT order_id FROM candidates ORDER BY order_id LIMIT 10)) AS sample
FROM decided;
- id: result
type: io.kestra.plugin.core.output.OutputValues
values:
row: "{{ (outputs.compare_keys.outputs | last).row | toJson }}"
- id: log_comparison
type: io.kestra.plugin.core.log.Log
message: >-
Target: {{ outputs.result.values.row | jq('.live_rows') | first }} live
rows. Source key list: {{ outputs.result.values.row | jq('.source_keys') |
first }} keys. Missing from the source and outside the grace window: {{
outputs.result.values.row | jq('.candidates') | first }} ({{
outputs.result.values.row | jq('.candidate_pct') | first }} percent, limit
{{ outputs.result.values.row | jq('.max_pct') | first }} percent). Held by
the grace window: {{ outputs.result.values.row | jq('.held_by_grace') |
first }}. Decision: {{ outputs.result.values.row | jq('.decision') | first
}}. {{ outputs.result.values.row | jq('.reason') | first }} Candidates: {{
outputs.result.values.row | jq('.sample') | first }}
- id: route
type: io.kestra.plugin.core.flow.Switch
description: Apply the deletes only when the decision is APPLY. Stop the run,
and change nothing, on any BLOCKED decision.
value: "{{ outputs.result.values.row | jq('.decision') | first }}"
cases:
APPLY:
- id: apply_deletes
type: io.kestra.plugin.jdbc.duckdb.Queries
description: >
SOFT stamps deleted_at and deleted_run on the candidate rows. HARD
removes them. Writes the new target and an audit file with one line
per row. In real use, replace this task with a DELETE or UPDATE in
your own warehouse that reads the candidates file.
inputFiles:
target.parquet: "{{ outputs.prepare_demo.outputFiles.target }}"
candidates.parquet: "{{ outputs.compare_keys.outputFiles.candidates }}"
fetchType: FETCH_ONE
outputFiles:
- target_after
- delete_audit
sql: |
CREATE TABLE tgt AS SELECT * FROM read_parquet('target.parquet');
CREATE TABLE cand AS SELECT order_id FROM read_parquet('candidates.parquet');
-- SOFT keeps every row and stamps the candidates. HARD drops the candidates.
COPY (
SELECT t.* REPLACE (
CASE WHEN c.order_id IS NOT NULL THEN now() ELSE t.deleted_at END AS deleted_at,
CASE WHEN c.order_id IS NOT NULL THEN '{{ execution.id }}' ELSE t.deleted_run END AS deleted_run)
FROM tgt t LEFT JOIN cand c ON c.order_id = t.order_id
WHERE '{{ inputs.delete_mode }}' = 'SOFT' OR c.order_id IS NULL
) TO '{{ outputFiles.target_after }}' (FORMAT parquet);
COPY (
SELECT c.order_id AS order_id,
'{{ inputs.delete_mode }}' AS mode,
t.updated_at AS last_updated_at,
now() AS applied_at,
'{{ execution.id }}' AS execution_id
FROM cand c JOIN tgt t ON t.order_id = c.order_id AND t.deleted_at IS NULL
ORDER BY c.order_id
) TO '{{ outputFiles.delete_audit }}' (HEADER, DELIMITER ',');
SELECT (SELECT count(*) FROM cand) AS applied,
'{{ inputs.delete_mode }}' AS mode,
(SELECT count(*) FROM tgt) AS rows_before;
- id: verify_demo
type: io.kestra.plugin.jdbc.duckdb.Queries
description: >
Demo only. Compare what was applied with the outcome the demo knows
is correct, check that the new target and the audit file agree with
it, and run the comparison again on the new target to prove a second
run finds nothing.
inputFiles:
target.parquet: "{{ outputs.prepare_demo.outputFiles.target }}"
source_keys.parquet: "{{ outputs.prepare_demo.outputFiles.source_keys }}"
expected.parquet: "{{ outputs.prepare_demo.outputFiles.expected }}"
candidates.parquet: "{{ outputs.compare_keys.outputFiles.candidates }}"
target_after.parquet: "{{ outputs.apply_deletes.outputFiles.target_after }}"
delete_audit.csv: "{{ outputs.apply_deletes.outputFiles.delete_audit }}"
fetchType: FETCH_ONE
sql: |
CREATE TABLE before AS SELECT * FROM read_parquet('target.parquet');
CREATE TABLE after AS SELECT * FROM read_parquet('target_after.parquet');
CREATE TABLE cand AS SELECT order_id FROM read_parquet('candidates.parquet');
CREATE TABLE expected AS SELECT * FROM read_parquet('expected.parquet');
CREATE TABLE src AS SELECT order_id FROM read_parquet('source_keys.parquet');
CREATE TABLE audit AS SELECT * FROM read_csv('delete_audit.csv', header = true);
-- The same comparison as compare_keys, run on the new target.
CREATE TABLE second_run AS
SELECT DISTINCT t.order_id
FROM after t
WHERE t.order_id IS NOT NULL
AND t.deleted_at IS NULL
AND t.updated_at < now() - to_minutes({{ inputs.grace_minutes }})
AND NOT EXISTS (SELECT 1 FROM src x WHERE x.order_id = t.order_id);
WITH checks AS (
SELECT
(SELECT count(*) FROM expected e WHERE e.outcome = 'DELETED'
AND NOT EXISTS (SELECT 1 FROM cand c WHERE c.order_id = e.order_id)) AS missing_deletes,
(SELECT count(*) FROM cand c
WHERE NOT EXISTS (SELECT 1 FROM expected e WHERE e.outcome = 'DELETED' AND e.order_id = c.order_id)) AS wrong_deletes,
(SELECT count(*) FROM before WHERE deleted_at IS NULL)
- (SELECT count(*) FROM expected WHERE outcome = 'DELETED') AS expected_live_after,
(SELECT count(*) FROM after WHERE deleted_at IS NULL) AS live_after,
(SELECT count(*) FROM before)
- CASE '{{ inputs.delete_mode }}' WHEN 'HARD' THEN (SELECT count(*) FROM cand) ELSE 0 END AS expected_rows_after,
(SELECT count(*) FROM after) AS rows_after,
(SELECT count(*) FROM cand) AS applied,
(SELECT count(*) FROM audit) AS audit_rows,
(SELECT count(*) FROM second_run) AS second_run_candidates
)
SELECT *,
missing_deletes + wrong_deletes + abs(live_after - expected_live_after) + abs(rows_after - expected_rows_after)
+ abs(audit_rows - applied) + second_run_candidates AS mismatches
FROM checks;
- id: verify_result
type: io.kestra.plugin.core.output.OutputValues
values:
row: "{{ (outputs.verify_demo.outputs | last).row | toJson }}"
- id: assert_verified
type: io.kestra.plugin.core.execution.Assert
description: Fail the run when what was applied differs from what the demo knows
is correct.
errorMessage: >-
Verification failed: {{ outputs.verify_result.values.row |
jq('.missing_deletes') | first }} deletes missing, {{
outputs.verify_result.values.row | jq('.wrong_deletes') | first }}
rows deleted that the source still has, {{
outputs.verify_result.values.row | jq('.second_run_candidates') |
first }} candidates left on a second run.
conditions:
- "{{ (outputs.verify_result.values.row | jq('.mismatches') | first)
== 0 }}"
- id: log_applied
type: io.kestra.plugin.core.log.Log
message: >-
{{ inputs.delete_mode }} delete applied to {{
outputs.verify_result.values.row | jq('.applied') | first }} rows.
Live rows after: {{ outputs.verify_result.values.row |
jq('.live_after') | first }}. Audit lines: {{
outputs.verify_result.values.row | jq('.audit_rows') | first }}. A
second comparison on the new target finds {{
outputs.verify_result.values.row | jq('.second_run_candidates') |
first }} candidates.
NOTHING_TO_DELETE:
- id: log_nothing_to_delete
type: io.kestra.plugin.core.log.Log
message: "Nothing to delete. {{ outputs.result.values.row | jq('.reason') |
first }} Held by the grace window: {{ outputs.result.values.row |
jq('.held_by_grace') | first }}."
defaults:
- id: block_delete
type: io.kestra.plugin.core.execution.Fail
errorMessage: "{{ outputs.result.values.row | jq('.decision') | first }}: {{
outputs.result.values.row | jq('.reason') | first }}"
errors:
- id: report_failure
type: io.kestra.plugin.core.log.Log
level: ERROR
message: "Hard delete propagation failed in {{ flow.id }}, execution {{
execution.id }}. Read the log_comparison and block_delete messages first:
a BLOCKED decision means the guards stopped the run and nothing was
changed, which is the expected result for the EMPTY_SOURCE, PARTIAL_READ
and NULL_KEY scenarios. Otherwise check the DuckDB task logs."