[Request for Comment] Should destinations soft-delete or remove CDC deletions?

[evantahler]

Hello Airbyte Community!

Now that we've launched Destinations V2 (#26028) for Snowflake and Bigquery (other destinations coming soon), we are able to more easily make adjust how data is represented in your data warehouse's final tables, based on your feedback. The first area of investigation is how we represent deletions from CDC sources (e.g. from source-postgres). We are conducting a survey to learn what Airbyte users prefer.

Today, if a row is deleted in your source, we remove it in the Destination's final table as part of the deduplication process - the row will not be present in the final table. However, an alternative exists in which we leave the row in the final table, but have a "Tombstone" or "Soft Delete" column present that is either null (the row exists in the source) or non-null (the row has been deleted from the source), e.g.:

id	name	updatd_at	_ab_cdc_deleted_at
1	Evan	2023-01-01	null
2	Edward	2023-01-02	2023-01-03

In the example above, "Evan" (user 1) exists in the source, and "Edward" (user 2) has been deleted. In most cases (depending on the CDC source), the tombstone column will be _ab_cdc_deleted_at, a timestamp. You would gain the additional information about when the row was deleted as well. Should you want a view of your data in the destination which more closely resembles the source (e.g. current behavior), you can filter out WHERE _ab_cdc_deleted_at IS NULL and either make a new table or view for your downstream analysis.

Of note, we currently only remove deleted rows for CDC database sources. There are many API source which also provide a deletion/tombstone column (e.g. source-salesforce) whose records will remain in your final table. Switching CDC deletes to soft-deletes would homogenize how Airbyte works for all sources. It also has the benefit of speeding up the Typing and Deduping process.

So... which do you prefer? Respond with a 👍 to change soft-deleting (leaving the row in the final tables + tombstone column) or a 👎 to keep the existing behavior and remove deleted rows from the final table.

[evantahler]

Some of the reasons I'm on team "soft delete"

• There are real use cases for auditing deletes which Airbyte today cannot support. Soft deletes allows that
• There's a very tight coupling today between source and destination that is tricky and unnecessary. E.g if the source wants to change the name of the tombstone column from _ab_cdc_deleted_at to deleted_at, it requires breaking changes in the source and destination. API sources don't have this tight coupling today, which is why we don't treat salesforce deletes the same way.
• I want to speed up Typing and Deduping

[evantahler]

Also of note, this change is isolated only to Airbyte destinations - in transit, sources already are sending a "virtual column" for _ab_cdc_deleted_at, which you can see in the raw tables. This change is limited to what Typing and Deduplication does.

[aaronsteers]

+1 for 'soft delete'

There are definitely use cases for soft delete so if only one option is possible, I'd definitely vote soft delete. Otherwise, it's something of a feature gap for certain use cases where auditing would be required.

For instance, I've run into cases where I could not explain a change in reports from one day to the next without record of underlying data being deleted. Another reason to only soft delete is that on dimension tables, we lose ability to "left join" historical fact records back to their dimension table(s) if those dimension records later disappear by way of hard delete.

I'm also a fan of a config option for 'hard_delete` (bool) - but I understand we want to minimize the matrix of options to support.

[aaronsteers]

I added a counter argument here as a separate reply, considering that changing from hard-delete to soft-delete should probably be considered a breaking change.

[gosusnp]

Are we thinking of an only soft or only hard solution? Soft delete makes sense to me but I wonder about cases such as privacy related data deletion for example. Not having a hard delete implies that users would need to consider an additional data removal process.

[evantahler]

I'm glad you brought this up! It is possible that the current behavior (deleting rows in the final table) may be giving users a false sense of security, as deleted rows are still present in the raw tables. If security is a concern, users have other options:

• de-select sensitive columns
• hash sensitive columns (coming soon)
• use per-schema/per-table permissions to protect data in the destination
• 0-out PII when removed in the source

[gosusnp]

Wouldn't 0-out still leave PII in the raw table?
Looking at the options, it feels like a good recommendation to have a process for handling deletion of sensitive data explicitly from the raw tables whether we're doing soft or hard delete.

[rodireich]

My thinking is that going with the "hard delete" option will keep things simple:
if a row is gone on the source side, it will be gone on destination side.
If one wants to do an audit they can still use the raw table

[aaronsteers]

On seeing others' comments, I'm going to add another take on my prior response.

While I still vote for adding the 'soft delete' option, it's worth noting that this may be considered a breaking change for users who today expect hard deletes (status quo) to continue. Specifically this point from OP text above:

Today, if a row is deleted in your source, we remove it in the Destination's final table as part of the deduplication process - the row will not be present in the final table.

So, I'll just put out there that I think this is the implementation I'd advocate for:

1. Add 'soft_delete' option to config that defaults to 'false' on all destinations which currently hard-delete by default.
   • (With a blank slate, such as with a brand new connector, I'd recommend 'hard_delete' as an opt-in behavior, but given that soft deletes are the exception today, a 'soft_delete' option as an opt-in makes more sense to me coming into our status quo.)
2. Allow users to enable the 'soft_delete' config option, on an opt-in bases.
3. If users do not opt in, then they continue to get the hard delete behavior (no breaking change).

Regarding the type of breaking change:

Important to note that users may not be reliably self-identify regarding whether they would be affected by changing to soft-delete or not. A query that works today with no problem may begin to produce incorrect results - and users will have 10s or 100s of tables being queried in different ways - by the data team themselves and by their internal customers.

It's also worth noting that the 'fix' to the breaking change is simply to train users - and/or to update dbt models and other SQL queries to include the clause WHERE _ab_deleted_at IS NULL. However, this needs to be performed potentially everywhere in the users' environment - and many users will need to go through a production change control process to do so. Importantly, they cannot do this code change ahead of time because presumably (?) the _ab_deleted_at column won't exist until after soft-deletes are enabled.

---

In summary, when reconsidering this as a potentially breaking change, I would suggest we not set soft-deletes as the new default behavior. Instead, I would vote to hold off until/unless we can add the behavior as configurable, so users are opting in consciously and when they have ability to prioritize the minor code changes which may be required downstream.

[adam-bloom]

One thing that I don't see mentioned yet - if the data is hard deleted at the source, and for any reason, you need to reset the connection in airbyte and start with an initial sync + snapshot, the deleted data is not replicated. Therefore, wouldn't it be best to delete the data by default so that the final table is consistent after a reset? Opt in soft-delete timestamps are an option, but almost all of these audit use cases are better solved by using soft-deletes at the source, which will allow the state to be maintained through a reset and initial snapshot. I think any behavior that makes the final table dependent on the state of the connection needs to be opt in.

[JasonGluck]

Agree with this entirely. This is the strongest argument in favor of deleting the records in the final table. If people are relying on soft deleted rows in the final table and need to reset their connection, they are going to lose that data. If a history of deleted records is required, it should be handled in the source or via some other process. I think allowing soft delete is setting users up for an eventual massive headache when they need to reset their connection and realize this.

[evantahler]

I very much agree that the source maintaining it's own lifecycle columns (created_at, updated_at, deleted_at) is the preferred way to go... but that's not always an option 😓

I think I'm getting lost on your message here @adam-bloom - are you in favor of removals in the destination because in the event that you need to rest your sync, you won't get that data back? Is this an argument for consistency between resets over "more information" while it is possible to have it? Presumably select * from table where _ab_cdc_deleted_at IS NULL will always return the same information between resets - in the first instance, there will be rows filtered out, and in the second there will be nothing to filter out

[adam-bloom]

I think @JasonGluck may have worded it better than I do - I think there will be lots of people who end up relying on these delete markers, but lack the understanding of where they came from, and will be totally caught off guard if they need to do a data reset and they disappear. That doesn't sound like a great user experience to me. I'm in favor of remove by default, opt in for soft deletes. Whether or not someone opting in fully understands these caveats isn't something we can fully control, but making it opt in is at least a good step.

[jeff-skoldberg-gmds]

I am team Soft Delete, all the way. Just echoing what others have said...

I see the benefit of Hard Delete: the source and destination match. It is simple. But...

I would like a record of everything that happened in my data warehouse. loaded_at, updated_at, deleted_at help us reconcile. The manager says "How is it possible that Sales went down? Shouldn't it only go up?", we can prove the source was not immutable and someone deleted a row.

In practice, I think I will always filter where not deleted, this is what I do with my fivetran tables (I do this in dbt staging models). But it is comforting to know we can reconcile changes if we need to.

[jeff-skoldberg-gmds]

Also, to save us a tiny bit of typing, I would like an is_deleted flag in addition to deleted_at

[aaronsteers]

The manager says "How is it possible that Sales went down? Shouldn't it only go up?", we can prove the source was not immutable and someone deleted a row.

Been there ☝️

Had this conversation several times, especially for sales reporting. 😅

[Lucasdvrs]

Been there, done that on sales reporting and sales comp 😅
Got tired of endless manual SQL archeology to answer questions like "Yesterday it said 100, now it says 90, what happened?"
So created an open-source tool to answer this question instantly, wdyt?

https://app.data-drift.io/42527392/Lucasdvrs/dbt-datagit/overview?snapshotDate=2023-10-11&commitSha=f81e5477a45c54d7088703e128ccfb701fe7a368

It basically works with installing a probe in your pipeline and voila.
Repo for more info https://github.com/data-drift/data-drift

[shrodingers]

Team soft-delete here as well, but best would be being able to configure wether keeping deleted (like letting airbyte handle a second pass on rows to hard delete the deleted row), by a setting in the destination

[jeff-skoldberg-gmds]

oo, nice idea!

[justin-autobinck]

+1 for soft delete (as a feature)

In my experience the data analysts that I work with are very comfortable when it comes to working with more normalised data, and data that "is there". Although from an engineering point I agree with hard delete, I think that most users of the data are not always equipped to understand the CDC process, and barely touch the raw data. A Data engineer should be the one solving this for the data users, but knowing how hard it is to find data engineers, I opt for the tool doing as much work as possible to get the data ready for consumption.

Lastly, I think @aaronsteers has a good point. Make both soft- and hard-delete possible with soft-delete disabled by default.

[VICIWUOHA]

@evantahler. I've encountered this issue previously and while a hard delete is best for the neatness of data in the destination. Soft deletes always help us track changes over time. Another option I may suggest would be configuring Soft deletes to send data to an audit_logs kind of table. This is something I've implemented in production with a bunch of on_delete triggers. This way, when something gets deleted from source.. The removal is replicated in destination, but the old record is logged In an similar table name maybe suffixed with _delete_audit_logs alongside a timestamp of when this occurred.

[evantahler]

Adding a note about making "soft delete" or "hard remove" an option: In the fullness of time, I think this is where Airbyte will go, but in the near-term, this would be a rather big change. As every source represents it's deleted_at data differently (different column names, top-level vs sub-objects, etc), we would need to:

1. Update the Airbyte Protocol to make _airbyte_deleted_indicator (bool) a part of the schema
2. Update various sources to provide _airbyte_deleted_indicator as part of the DISCOVER command
3. Update various destinations to provide the option and consider _airbyte_deleted_indicator columns for removal.

[VICIWUOHA]

This is duly noted @evantahler . Would be good to know how this would impact new connectors built afterwards.
I also think the No-Code builder should have this component too.

[jasonmaddernstudylink]

Can I ask if any definitive resolution was reached?

I have the need for the soft indicator to flow through so was wondering if this was on a roadmap?

[davinchia]

Hi, no definite resolution yet. We are still debating internally. I am happy to report there is a high chance we'll pick up this work in the next 2 months or so!

Before we do that, we'll put out a final plan incorporating all the feedback to folks in this discussion to make sure our approach covers as many use cases as possible.