A soft delete marks a record as no longer active or valid without actually removing it from the database. Instead of running DELETE, the application runs an UPDATE that sets a deleted_at timestamp or an is_deleted flag, and every part of the app that reads that table filters the marked rows out.
Why applications do it
- Recovery. A customer deletes a project by accident; you flip the flag back instead of restoring a backup.
- Referential integrity. Deleting a user row would orphan their orders, comments, and audit trail. Marking it deleted leaves the foreign keys intact.
- Audit and compliance. You often need to prove what existed and when, which a hard delete makes impossible.
- Performance. Cascading deletes across large tables can lock things up; a single-column update is cheap.
The cost is that your tables now contain rows nobody is supposed to see, and nothing in the schema forces you to remember that.
The analytics consequence
This is where soft deletes stop being a backend implementation detail and become your problem. The application filters deleted rows automatically. Your queries do not.
Query a soft-deleted table directly and every count, sum, and average silently includes rows that the product considers gone:
-- Overstates active customers
SELECT count(*) FROM customers;
-- What the app actually shows
SELECT count(*) FROM customers WHERE deleted_at IS NULL;
The symptom is a slow drift: your dashboard’s customer count creeps above what the app reports, and the gap grows every month as more records get soft-deleted. Nobody notices for a quarter, because the numbers are wrong by a plausible amount rather than an absurd one. Then someone reconciles churn rate against the product and you spend a day figuring out which of the two numbers to believe.
Deleted rows also skew retention and cohort analysis in a particular way — the records most likely to be soft-deleted are the ones that churned, so leaving them in makes retention look better than it is.
Handling it well
The reliable fix is to apply the filter once, centrally, rather than in every question. Build a model (or a database view) that selects only the live rows, and point your questions at that instead of the raw table. Anyone using the query builder then gets the right rows by default without knowing the convention exists.
A few things worth checking when you first connect to an application database:
- Which tables have a
deleted_at,is_deleted,archived_at, orstatuscolumn that means “gone”? - Is the convention consistent across tables, or did different teams pick different column names?
- Do joins to those tables need the filter too? A join to a soft-deleted
productsrow will quietly resurrect it in your results.
Related terms
Put it to work
- Churn rate — Metric
- Churn and retention dashboard — Dashboard
- Revenue analytics — Overview
