Soft Deletes vs Hard Deletes in Replicated Warehouse Tables

Soft deletes versus hard deletes in replicated warehouse tables is an architectural decision about correctness, retention, and erasure. Soft deletes keep rows behind a flag, while hard deletes remove them from the destination. Both need reliable delete capture and automatic schema evolution for live CDC pipelines, because neither works if events are missed or schema changes reset the destination.

Why the delete decision cannot be left implicit in a replicated pipeline

Silent divergence begins when rows removed from the source continue answering queries in the destination. The pipeline need not fail or report an error. A table can look complete, accept queries, and return incorrect results because its deletion policy was never defined. Treating delete handling as a configuration detail therefore leaves part of the table’s meaning to a tool default.

Detection and application are separate parts of that problem. Modern log-based CDC engines capture deletes from transaction logs just as they capture inserts and updates. Query-based CDC that polls a timestamp or updated_at column cannot detect physical deletions because the row is no longer there to check. Even with log-based capture, filtering out delete events to keep history leaves the target with records the source has removed. That choice affects query correctness, compliance, storage cost, and AI/ML reliability whether or not the team intended a retention policy.

How log-based CDC surfaces a delete event and what it contains

A log-based delete event is a structured record. Depending on source configuration, it contains the deleted row’s primary key and optionally a before-image of its column values. The primary key lets a downstream consumer identify the affected record, while a before-image supplies the old values. The payload is the starting point for either strategy.

PostgreSQL exposes changes from its write-ahead log through logical decoding, with the before-image governed by the table’s replica identity setting. The default primary-key identity is enough for keyed tables, while ALTER TABLE t REPLICA IDENTITY FULL gives full old-row images to tables without primary keys or consumers that need them. Without sufficient identity, a delete cannot reliably identify its downstream row. On RDS/Aurora Postgres, logical replication also requires enabling rds.logical_replication and rebooting the instance. Amazon notes that enabling it increases WAL generation and should be reserved for situations where replication slots are genuinely in use.

MySQL records them in its binary log using row-based format, while MongoDB records item-level changes in its operations log. A CDC delete record has no surviving after-image, so its after field is null, while an available before object represents the last known state. DynamoDB Streams emits an ordered sequence of item-level changes and retains each record for 24 hours. A consumer outage beyond that window loses access to expired events, preventing reliable delete application without auxiliary infrastructure.

What soft deletes actually do to the destination table

Soft deletes translate a source delete into a destination update. Instead of removing the row, the pipeline sets metadata columns, typically _deleted and _deleted_at, to mark its status and deletion time. The deleted entity is still there for historical analysis. This supports audit trails and, where historical versions are retained, time-travel queries. An accidental deletion can also be reversed by resetting the flag on the retained row.

The destination operation is an UPSERT rather than a DELETE, which is less disruptive for most analytical databases. In Snowflake, a MERGE can use WHEN MATCHED AND _op = 'DELETE' THEN UPDATE SET _deleted = true. The retained row changes what every current-state query must mean. Such queries need WHERE _deleted = false, or they include entities that no longer exist in the source. On high-churn tables, the accumulation also increases storage costs and can degrade query performance.

Kafka log-compacted topics provide a related deletion signal through tombstones, which are null-value messages on deleted keys. A tombstone tells consumers that the key has been removed without immediately deleting the log entry. It does not replace the warehouse’s retention decision. Preserving records can look like the conservative default, but retention is beneficial only when the destination’s purpose calls for it. Where the table must represent current state or support erasure, keeping those rows is the wrong behavior.

What hard deletes actually do and where they are the right call

Hard deletes remove the destination row when the pipeline receives the corresponding source event. The table then mirrors the source’s current state. Queries do not need a deletion filter, and removed entities do not accumulate indefinitely as stored rows. Hard deletes also fit when churn makes storage costly and deleted records have no value for analysis.

In Snowflake, a MERGE can apply the operation with WHEN MATCHED AND _op = 'DELETE' THEN DELETE. Its micro-partition architecture makes point deletes reasonably efficient, though very high delete volumes can mean more micro-partitions need to be rewritten. Apache Iceberg also supports row-level deletes and MERGE, allowing CDC updates and deletes to land in transactionally consistent, engine-readable tables without full rewrites.

The destination no longer provides a record of the deleted entity. Recovery requires a separate mechanism, such as a data lake archive, a slowly changing dimension table, or a backup. Tracking deletions also needs a way that does not rely on finding the original row in the current-state table. That separation is appropriate when operational queries and historical analysis have different retention needs. For personal data subject to erasure, however, any separate history layer must also participate in deletion controls.

Why compliance law often makes soft deletes legally insufficient

GDPR Article 17 conditionally requires erasure when one of the six grounds in Article 17(1) applies and no Article 17(3) exception permits continued retention. A row that still contains an email address, name, or other personally identifiable information has not been erased merely because _deleted is true. The data remains present behind a flag. The architectural consequence is distinct from the legal analysis of whether a particular request qualifies.

Every accessible copy of the personal data must be addressed by deletion controls, so a primary-table hard delete is not a complete compliance mechanism. Cross-system erasure orchestration, retention enforcement, and snapshot expiration must address copies elsewhere. HIPAA similarly requires protected health information to be securely disposed of when retention requirements have been met and the information is no longer needed. A soft-delete flag does not render that information unreadable, indecipherable, or impossible to reconstruct.

Anonymization quality is difficult to verify, and regulators have challenged it, making actual erasure the lower-risk path when doubt remains. The pipeline implication is explicit. Tables holding PII or PHI need hard deletes as the warehouse default where erasure obligations apply, with any separately retained archive subject to its own erasure controls. That policy belongs in pipeline configuration.

How stale or incorrectly deleted rows corrupt AI and ML outputs

The same retained rows create a reliability problem when warehouse data feeds AI and ML systems. Training data with soft-deleted entities includes records that the source has deliberately removed. Those records can contribute incorrect patterns or preserve personal data that should have been erased.

Materialization can make the error persist beyond its origin. A batch training job can copy warehouse data into a frozen feature-store snapshot for repeated reads. If that snapshot includes soft-deleted rows, correcting the warehouse later does not correct the already materialized copy. This is another retention boundary. Delete correctness must therefore be established before feature materialization, while erasure controls must also address the resulting copies.

An agent reading a soft-delete table without WHERE _deleted = false treats removed entities as active, and the error can remain invisible until a customer or audit identifies it. The warehouse needs hard deletes so materialization starts from a clean table, or a deletion filter enforced as a pipeline-level constraint.

How do you handle upstream schema changes without stopping a CDC pipeline?

Automatic schema evolution for live CDC pipelines must preserve delete behavior while source schemas change. Both strategies need to handle column additions, type changes, and table renames without resetting the destination. PostgreSQL does not include DDL statements such as ALTER TABLE and CREATE INDEX in its logical replication stream. Schema migrations therefore need explicit coordination between source and target, with tooling that detects and applies changes without a full resync.

Soft deletes add metadata that must survive that coordination. The _deleted and _deleted_at columns must stay in place and be kept correct through migrations. Dropping and re-creating the destination can erase their retained context. A tool that resumes replication after a reload has not necessarily preserved the table’s historical meaning. Continuity of ingestion and continuity of deletion history are separate acceptance criteria.

AWS DMS illustrates why migration behavior needs close examination. Changes such as added columns, modified enums, or renamed tables can suspend affected tables or require a task stop and reload. With DROP_AND_CREATE configured, a reload can re-create destination tables, removing historical context and disrupting downstream models. Provisioned DMS instances also lack built-in automatic scaling, while AWS DMS Serverless provides automatic scaling. Under high change rates, replication lag makes delete latency and application order additional concerns.

Tests should check that deletion metadata survives migration, that hard deletes are applied atomically through MERGE, and that events stay in the right order under load. Automatic scaling under high delete volumes is another criterion. Support for an upstream schema change is useful only if the destination remains correct after the change.

A decision framework for choosing between soft deletes, hard deletes, and hybrid approaches

Choose the strategy from the destination’s purpose, data sensitivity, consumer behavior, and retention needs. Soft deletes fit audit trails, historical trend analysis, and workloads where the deletion record itself has analytical value. They require reliable exclusion from current-state queries. For data containing PII or PHI, they also require a separate workflow that erases or appropriately anonymizes retained records within the applicable compliance window.

Hard deletes fit live operational mirrors, high-churn tables without valuable deletion history, and AI or ML inputs that must exclude stale entities. They are also the default for personal or health data subject to erasure requirements. A hybrid design separates those uses. A primary table can apply hard deletes while a separately governed archive retains deletion history through soft deletes. Analysts then query current state, while historical consumers use a layer whose retention and erasure controls are explicit.

Start with log-based CDC. Confirm that each source table has a primary key, or configure PostgreSQL replica identity to supply enough information for downstream identification. For soft deletes, add _deleted and _deleted_at before enabling replication and enforce exclusion at the view layer. AI and ML pipelines need that exclusion enforced so downstream jobs cannot bypass it.

For hard deletes, check that they are applied atomically through MERGE and in the right order relative to updates. For PII tables, document the erasure SLA and the pipeline’s ability to meet the compliance window. GDPR’s “without undue delay” standard requires action as soon as reasonably practicable, generally within one month, rather than prescribing sub-minute replication latency. Finally, confirm that schema evolution preserves the destination instead of resetting it, so the configured delete policy survives the next migration.