I've had the hard delete vs. soft delete argument more times than I can count. It comes up in every database design review, every architecture meeting, every time someone starts building an MDM system. And every time, the same people make the same case for soft deletes: "but what if we need to recover the data?"
It's a reasonable question. But after implementing both approaches across multiple projects, I can tell you: soft deletes in a master data system are a trap. They feel safe on day one. By month six, you're debugging phantom records in your BI reports. This matters especially in an MDM context — if you want background on how master data entities are structured, models, entities, and attributes explains the data model that these decisions apply to.
Quick refresher for the uninitiated
Hard delete: the row is gone. DELETE FROM Branches WHERE Code = 'AMS-001'. Clean. Final.
Soft delete: the row stays, but you flip a flag. UPDATE Branches SET IsActive = 0 WHERE Code = 'AMS-001'. The data "exists" but is supposed to be invisible. Supposed to be.
Soft deletes sound careful and responsible. In certain contexts they are. But master data management is not one of those contexts, and I learned that the hard way.
The code reuse problem (this one burns)
In master data, the Code field is sacred. Branch codes, cost center codes, product SKUs. They must be unique. Your entire downstream integration depends on it.
I had a client with a branch network across the Netherlands. A branch in Amsterdam closed. The data steward deleted it. Six months later, a new branch opened at a different location in Amsterdam. They wanted the same code: AMS-001.
Unique constraint violation. The "deleted" row was still squatting on that code.
The workarounds are all ugly. You can build a compound unique constraint on Code + IsActive, but then you can have two "AMS-001" rows in the same table (one active, one not) and your integration views need to know about that. You can append a timestamp to deleted codes, turning AMS-001 into AMS-001_DEL_20260215. You can drop uniqueness entirely. Every option makes things worse.
The "WHERE IsActive = 1" tax
Once you go soft delete, every query in your application needs that filter. Every stored procedure. Every view. Every API endpoint. Every report. Miss one, and deleted records show up in production.
I've seen it happen. A reporting team built a new dashboard that pulled reference data from the entity tables. Nobody told them about the IsActive flag. For three weeks, their reports included branches that had been "deleted" months ago. The finance team was reconciling numbers against ghost locations. Fun times.
In master data, this is especially dangerous because reference data flows everywhere. Your data warehouse, your ERP, your BI layer. One missed filter and you're propagating dead records across your entire data landscape.
Dead rows are dead weight
Soft-deleted rows never leave. They pile up. And here's what most people don't realize: adding an index on IsActive barely helps. If 99% of your rows are active and 1% are deleted, the cardinality is so low that SQL Server won't even bother using the index. Your queries are now scanning dead rows for no reason.
Yes, you can use filtered indexes. Yes, you can partition. But you're adding complexity to solve a problem that doesn't need to exist.
Domain references to ghost records
This is the one that really got me. In MDM, entities reference each other. A Branch points to an Area. An Area points to a Zone. If you soft-delete a Zone, what happens to every Area that references it? The foreign key is still valid (the row exists), but the record is "deleted." Should the dropdown still show it? Should the Area's zone field show a name or an error?
With hard deletes, the database catches this for you. Try to delete a Zone that has Areas pointing to it? Foreign key violation. Clean, immediate, obvious. With soft deletes, you're writing application code to check these cascading references manually. And you will miss one eventually.
What we actually did in Primentra
The soft delete crowd has one good argument: "what if someone accidentally deletes a record?" Fair point. But you don't need to keep dead rows in your operational tables to solve that problem.
Primentra uses hard deletes with a full audit log. When you delete a record, two things happen simultaneously: a complete JSON snapshot of the record is written to the audit log (every field, every domain reference, every metadata value), and then the row is permanently removed from the entity table.
The entity table stays clean. The audit log has everything.
| Soft Delete | Hard Delete + Audit | |
|---|---|---|
| Recovery | Flip a flag back to 1 | Re-import from audit snapshot |
| Unique codes | Broken. Dead codes block reuse. | Clean. Codes freed immediately. |
| Query overhead | WHERE IsActive = 1, everywhere, forever | None. Table only has live data. |
| Table growth | Grows indefinitely with dead rows | Only active records in entity tables |
| Domain integrity | Ghost references, broken dropdowns | FK constraints do the work for you |
| What you know after deletion | That the flag was flipped. That's it. | Full record snapshot with every field value |
"But what if I need to undelete?"
This is the question everyone asks. And the answer is: the audit log has a complete JSON snapshot of the deleted record. Every field, every domain reference, resolved to human-readable values (so {AMS} Amsterdam, not just a foreign key ID). You take that snapshot and re-import it. Primentra's import function handles this natively.
The difference? Recovery in a soft-delete system is a silent flag flip. Nobody knows it happened unless they check. Recovery in Primentra is an explicit, audited action. Deleted at 9 AM by Jan. Re-imported at 2 PM by Lisa. Clear paper trail. For a system that's supposed to provide governance over your master data, which approach would you rather have?
What about audit log growth?
"Okay, but then your audit table becomes huge." I hear this too. And it would be a valid concern if the audit table was your operational data. But it's not. The audit log sits in its own table, separate from the entity data that your application queries on every page load. Nobody is joining against the audit log in a dashboard query.
Primentra also supports retention policies. Keep 90 days, 180 days, a year, whatever your compliance needs. Old entries get cleaned up automatically. Compare that to soft deletes where the bloat sits in your production entity tables, slowing down every query that touches them.
How MDS handled this (spoiler: poorly)
Microsoft MDS used soft deletes. Members could be "deactivated" instead of removed. The IsActive flag lived in the entity tables. It was baked into the versioning system, which tracked member state across model versions.
In practice? Almost nobody used the versioning. They just wanted to delete a row and move on. But MDS made them carry the overhead anyway. Inactive members cluttering up subscription views, queries that needed extra filtering, Code values locked up by ghost records. Classic case of engineering for a theoretical use case at the expense of the common one.
For the few teams that did use MDS versioning to keep point-in-time snapshots, Primentra offers entity cloning instead. One click, and you get a complete copy of an entity with all its data. The clone is independent. Edit the original, the clone stays frozen. Much simpler than version flags living alongside your live data.
When soft deletes actually make sense
I'm not saying soft deletes are always wrong. They work well for:
- Trash bin UX where users expect instant undo (email, documents, file systems)
- Regulatory environments that legally require data to stay in the original table
- Systems without unique business key constraints
But if you're building or choosing an MDM platform where Code uniqueness matters, where reference data feeds downstream systems, and where you need a real audit trail, not just a flag? Hard deletes with audit logging. Every time.
The principle
Operational tables should only contain active data. History belongs in the audit log. This keeps your entity tables lean, your queries honest, your constraints working, and your audit trail richer than any IsActive flag could ever provide.
We didn't arrive at this by reading design pattern books. We arrived at it by watching soft deletes create real problems in real MDM deployments. Primentra is built on those lessons. For context on how these design choices interact with the approval workflow layer, how approval workflows work covers that in detail.
Curious how Primentra handles data governance and audit trails in practice? The 60-day free trial is fully self-service — install it on your own SQL Server and check the audit log for yourself. Try it free →
- No cloud migration — stays on your own SQL Server
- No professional services required — self-service from day one
- No credit card for the trial