Row Presence Verification (RPV)

by

🎯 Core Issue:

Soft delete based on detected value change assumes continuity, but some cases involve disappearance, movement, or transformation that wouldn’t trigger expiration.

  • Project earned value: Measures can shift across time or phases but still belong to the same concept.
  • Invoice accounting: Amounts may be amended, reallocated, or withdrawn entirely (e.g. credit notes, reversals).
  • Activity sequencing: If a value “moves”, it’s not necessarily a change — it’s a context shift.

🔍 Risks of the Current Approach

ScenarioProblem
Value disappears entirelyYou keep the last-seen value forever unless a new version overwrites
Value moves to another keyThe old one is retained, and the new one appears as duplicate context
Periodic reporting gapsReporting shows a “stale” value that’s no longer present
Multi-row summaries (earned value)Partial values may cause duplication, under/over-reporting

✅ Recommended Pattern: Row Presence Verification (RPV)

Introduce a process step that verifies expected presence for certain value types. Instead of only watching for changes, you ask: “Should this value still exist in this context?”

🔄 How it Works

  1. During your ingestion or transformation phase:
    • Build a set of expected active keys based on the latest source snapshot.
    • Compare with current ACTIVE_FLAG = 1 rows.
  2. For any active row where the key no longer exists, and there’s no matching replacement:
    • Expire it deliberately (with a reason = 'missing_from_source')
    • Or flag it for review (AUDIT_FLAG = 1, EXPIRY_REASON = 'unexpected_absence')

🧩 Technical Implementation Suggestions

1. Presence Check Table

A temp table holding object_seq, attribute, expected_this_period = 1
Then join against *_values or *_meta to find orphaned active rows.

2. Add an EXPIRY_REASON Field

Extend your templates to track why a row was expired:

  • value_changed
  • source_disappeared
  • manual_override
  • archived_due_to_period_end

3. Run Periodic Reconciliation

Schedule a job (or make part of pipeline post-step) that checks:

  • ACTIVE_FLAG = 1 rows not found in current snapshot
  • Log/report the anomalies before automatically expiring

🛡️ Why This Matters

Without this safeguard, your model assumes “absence = unchanged”, which is brittle for:

  • Currency-based values that consolidate over time
  • Earned value measures that change source frequency
  • Reconciliations that back-date or remove items from systems like ERP or timesheet engines

Leave a Comment