CODEWITHSUNDEEP
sql-serverdatabasecqrsevent-sourcing
user8280126
user8280126

Reputation:

Event Sourcing SQL Server Delete

We utilize Event Sourcing in Microsoft SQL Server 2016 Enterprise. After three years, we would like to delete data from Write and Read model. Company is requesting this for privacy and compliance reason.

We want to delete by year of customer order date. (eg current year is 2018, any 2015 or below).

I believe the Write Event Store is immutable; insert only, cannot be deleted. Its also be needed to replay events (or we can also utilize ES snapshots).

Is Deleting in Event Sourcing event allowed, with the Immutable Write Event Store rule? What is the correct rule of deletion in MSSQL?

Certain topics:

(a) Customers can return after a year, and we only collect incremental data. So we may need to retain some eventstore data. 3 year rule can become 4

(b) Additionally, we delete by year of Order dates (which are different from Etlcreatedate and Etlupdatedates)

Upvotes: 3

Views: 919

Answers (2)

Frans van Buul
Frans van Buul

Reputation: 86

Regarding the question whether deleting is allowed, there are arguments either side but it is ultimately a matter of opinion with no general answer. I did a poll about this topic (and others) among Axon Framework users implementing event sourcing. A large group does occasionally delete events, another large group thinks this should never be done: no clear winner.

If you want to have the ability to delete events based on certain event properties (such as order date), part of the solution may be to store those fields as metadata with your events in addition to the event payload (in a separate column). This will allow for simple and efficient delete queries.

If you want to be strict in the append-only nature of your event store (no deletes and updates) but need to delete data, you may consider crypto-erasure: encrypt event fields on write, store the keys outside of the event store, delete the keys when you need to delete the data. This is something we implemented in the (commercial) AxonIQ GDPR Module.

Upvotes: 1

Constantin Galbenu
Constantin Galbenu

Reputation: 17683

The Event store is an append-only persistence. This means that the interface that it exposes does not contain delete or update operations on the persisted events; this is because such operations are not required by the normal operation of an Event store.

However, the Event store can be migrated, more exactly, the events that it contains can be modified in a new version of the Event store - the old Event store remains untouched but the modified/processed events are stored in a new Event store. Then, the new Event store can be referenced by the application's configuration and the old Event store can be archived or deleted.

This migration can be done with offline, with one-time scripts. It is not required to run on a live system.

There are a lot of transformations that can be done to the events, presented in Greg's Young book: Copy and Replace, Two Aggregates - One Stream, One Aggregate - Two Streams, Copy - Transform etc.

In your case, in order to respect privacy and compliance, you could anonimize the events that contain personal customer data. For example, you could replace customer's address, name, or any personal data with white spaces. In this way you don't mess up the replaying of the events on Aggregates and Read-models.

After the Event store is migrated, you should rebuild all affected Read-models to be sure that all the personal/sensitive/must-be-deleted information is purged.

If you really want to delete old events (i.e older than 2014), then you should collapse all those old events in a snapshot-type/init-type event that would put your Aggregates in a valid initial state. This is needed for Aggregates that should be able to receive commands in the future.

For Aggregates that should not exist any more, you can delete old events for good.

P.S. When I say "delete" I mean that you don't copy them to the new Event store, at all.

Upvotes: 2

Related Questions