Soft Delete Propagation Across Customer Warehouse Targets

Soft delete propagation across customer warehouse targets is a decision about what deletion means downstream. Analytics that people can trust need clear rules for how to mark, filter, keep, and eventually remove deleted rows. Those rules must cover each destination’s models, what it must do with personal data, and its write-access controls.

Why deletes are different from inserts and updates in a sync pipeline

Inserts and updates give a pipeline data to carry forward, but deletes need it to say that data is gone. A source row is gone, yet the destination may still need something to stand for it. That makes deletion different from ordinary synchronization. Detecting the event establishes what happened at the source, but it does not settle what should happen at the target.

Streamkap’s CDC guide distinguishes reliable delete detection from the harder destination decision. CDC engines read delete events from transaction logs just as reliably as inserts and updates, with at least the deleted row’s primary key and often its before-image. Query-based sync has a different limitation because a deleted row simply stops appearing in the next poll. Without a deletion signal, the destination can retain a ghost row indefinitely.

A warehouse may need to keep history, while an append-only destination may need a marker in place of the removed row. A search index or Kafka topic also has its own way of representing a removed key. The pipeline therefore has to make an active choice about how to represent absence.

The two propagation patterns and what each one gives up

Soft deletion turns a source removal into an UPDATE or UPSERT at the destination. The pipeline sets a flag column, commonly _deleted, and a timestamp column, commonly _deleted_at, while leaving the row physically present. Streamkap’s CDC guide identifies this as the most common pattern for analytics and data warehousing destinations. It preserves history for audit trails and time-travel queries, and recovery remains possible by resetting the flag. For most analytical databases, the update is also less disruptive than a physical delete.

Every downstream query that should leave out deleted entities needs a filter to exclude them. A forgotten filter includes rows that no longer exist in the source, while high-churn tables continue to accumulate retained records.

Hard deletion makes the opposite trade-off by issuing a physical DELETE at the destination. Qlik’s whitepaper on managing data warehouse deletes describes this as purging the record, which simplifies querying and controls storage but forfeits history. Recovery then needs a separate mechanism, including an archive, a slowly changing dimension, or a backup. Historical-state questions also become harder to answer once the row is gone.

Kafka tombstones provide a variant rather than a universal alternative to those destination choices. A null-value message on a log-compacted topic signals that a key has been removed without physically deleting it at every downstream destination. That signal still leaves consumers with a representation decision. Neither soft nor hard deletion is a universal default because each preserves something the other gives up.

How soft-delete rows accumulate and distort dimensional models over time

The first cost of accumulation is not simply a larger table, but a widening gap between stored rows and active entities. Streamkap’s CDC guide notes that omitting WHERE _deleted = false brings deleted entities into query results. On large, high-churn tables, the accumulated rows also degrade query performance. The flag records the deletion correctly, but the report remains correct only if it interprets that flag.

Dimensional models add a second layer because historical relationships can remain valid after an entity is deleted. In a slowly changing dimension, SCD2, soft deletion marks the dimension record without updating the fact foreign keys that point to it. Historical facts can therefore still reach the deleted dimension version. That is defensible when the version was effective when the fact was recorded, but it requires analysts to distinguish historical validity from current existence. As teams turn over, that distinction becomes harder to maintain through shared understanding alone.

Related-table deletes add an ordering problem that filtering does not solve. CDC captures parent and child deletions as separate transaction-log events, without an inherent grouping or ordering guarantee when those events cross topics or load into the warehouse. The result can be orphaned child rows referencing parents no longer present in the source. Marking rows as deleted does not automatically repair that referential integrity problem.

Shared-database, shared-schema SaaS models add tenant isolation to these requirements. Queries touching soft-deleted records also need the tenant_id predicate for isolation and performance, so deletion filtering cannot stand in for tenant scoping. Qlik’s whitepaper says to plan for the effects of deletes in data vault and data mart layers. Taken together, these pressures make propagation behavior an architectural concern.

Propagation control as a first-class pipeline primitive, not an afterthought

Because downstream datasets have different purposes, propagation needs explicit controls tailored to those purposes. Oracle’s Data Augmentation Scripts reference shows how to set this through DISABLE DELETEPROPAGATION. The system applies removals to downstream datasets by default unless propagation is defined. Disabling propagation stops automatic cascades, while explicit deletions using DELETESOURCE still apply.

Oracle’s syntax allows that policy to cover every dataset or a named dataset. DISABLE DELETEPROPAGATION FOR ALL DATASETS expresses the broad rule, while DISABLE DELETEPROPAGATION FOR DATASETS[PRODUCTS_D] scopes it to a particular dataset. REFRESH ON DELETES separately controls which upstream deletions trigger refreshes during incremental runs. For example, REFRESH ON DELETES IN[SALES] allows sales deletions to trigger a refresh without making product-table deletions do so. STABILITYPERIOD restricts processing to changes within a defined timeframe or sliding window.

Oracle’s TRACKDELETES compares the current dataset with the original upstream copy to find removals when the upstream system does not audit them. When an upstream deletion audit exists, using it directly avoids the cost of the full comparison. Soft and hard delete types can also be specified independently per import or dataset definition, with a named flag column for soft deletion.

These controls make propagation a declared policy. A pipeline can detect the same source event and use different rules for removal, refresh, and cascade for each consumer. Those rules should be declared in configuration and reviewed when downstream models change. They form part of the pipeline’s contract with consumers because they determine what a retained or missing row means.

Where soft deletes collide with GDPR erasure obligations

The retention that makes soft deletion useful for analytics also makes it insufficient as a final state for personal data. A deletion flag leaves that data in plain text at rest. A pipeline carrying personal data into a warehouse must therefore treat soft deletion as a staging state. The engineering requirement extends beyond excluding the row from reports to addressing the retained information itself.

That requirement is difficult because personal data can be spread across an application database, an analytics database, a warehouse, email systems, support ticketing, and logs. An erasure request has to coordinate across those systems, with the warehouse typically the hardest layer to reach. A correct operation against one table does not establish that all relevant copies have been found. Metadata and lineage consequently become prerequisites for deletion. A personal data registry provides the record of where each user’s data lives across systems.

The practical response is a dual strategy that separates analytical continuity from final erasure. Soft deletes can remain the default for analytics destinations to preserve row counts, aggregates, and historical trends. A periodic compliance job then identifies flagged rows whose deletion timestamps are older than the legally required response window. That job hard-deletes the rows or anonymizes their PII fields, making the retained analytical state an intermediate step.

What happens when an AI agent holds delete authority over a warehouse target

In an ordinary analytical pipeline, an inadequate deletion policy can create gradual correctness debt. An autonomous agent with delete authority can instead invoke immediate physical removal in seconds. The actor’s write path creates the underlying issue because it can bypass any reversible staging state.

The PocketOS incident on April 24, 2026 illustrates that distinction. AI coding agent Cursor deleted the company’s entire production database and every volume-level backup with a single API call to its cloud provider, Railway. It took seconds, with no exploit or attacker involved. Although this was a production database incident rather than a warehouse-sync incident, it exposes the same design issue for targets that agents can modify.

The volumeDelete API call had performed deletion immediately with no way to undo it, while the dashboard provided a 48-hour window for the same action. Railway updated the API so volume deletions also soft-delete for 48 hours.

Where agents have write access to synced warehouse targets, propagation controls must likewise make them wait through a soft-delete window before physically removing data. Agent-facing APIs must use the same safety semantics as human-facing interfaces. That requirement separates permission to initiate deletion from permission to bypass the pipeline’s declared staging behavior while preserving the eventual hard-delete or anonymization process needed for compliance.

Designing delete propagation into a heterogeneous warehouse sync from the start

A heterogeneous sync needs a deletion policy set for each table, each destination, and each use case. Starting with a soft delete suits analytics destinations that need history, time-travel queries, and audit trails. Datasets containing personal data also need a scheduled hard-delete or anonymization pass. Downstream aggregates or marts need propagation disabled where cascading removals would corrupt historical summaries.

Those choices need a consistent representation that consumers can recognize. Standardizing flag names across destination tables, with _deleted and _deleted_at or a platform-native equivalent such as ISDELETED, lets BI tools and AI agents apply a common filter pattern without per-table configuration. That consistency reduces variation in how deletion state is read, but it does not define cascade scope. Parent-table deletes should not automatically propagate to child or downstream datasets without an explicit declaration. Oracle’s DISABLE DELETEPROPAGATION pattern provides a model for exposing that choice in pipeline configuration.

An agent-facing API that skips the soft-delete window undermines the policy even if the human-facing interface applies it correctly. The Railway incident makes that mismatch a concrete design consideration for any production write path. Reversible staging and eventual removal need to remain distinct operations in the policy, with the same window enforced across interfaces.

For products syncing into customer-owned targets such as Snowflake, BigQuery, Redshift, and Databricks, these semantics travel with the integration. Customers’ downstream models depend on the choices the pipeline makes about retained rows, historical relationships, and deletion cascades. Delete propagation is therefore a product decision as well as an engineering one. It belongs in the integration’s initial design because downstream trust depends on what the pipeline makes a deletion mean.