Event Sourcing SQL Server Delete

Question

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.

• The Write model Event Store is a column of type varbinary
• The Read model is an Inmon OLTP like relational database

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)

Answer 1

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.