feat: `hard_delete` capability does not consider the presence of non-null `_sdc_deleted_at` values generated during LOG_BASED replication

[dvenardos]

Singer SDK Version

v0.53.4

Is this a regression?

• Yes

Python Version

3.14

Bug scope

Taps (catalog, state, etc.)

Operating System

Ubuntu

Description

Issue Summary

The hard_delete configuration option is only implemented for full table sync and not for log based replication int the class class SQLTarget(Target).
When enabled, records with _sdc_deleted_at set by the source tap should be physically deleted from the target table, but instead they are only being soft-deleted (the _sdc_deleted_at column was populated but the records remained in the table).

Root Cause

The Meltano Singer SDK's hard_delete functionality only applies to ACTIVATE_VERSION messages (version control), not to records marked as deleted by the source tap. The SDK's delete_old_versions method deletes old versions based on _sdc_table_version, but there is no built-in mechanism to delete records where _sdc_deleted_at is not NULL.

Testing

Most likely tap for testing would be a postgres tap that uses log replication to a database target. I was using the Meltano target-snowflake but would apply to any database target that uses the Sinker SDK for functionality and has not overrided the base class.

Link to Slack/Linen

https://meltano.slack.com/archives/C069RH0F95F/p1767900485734439

[edgarrmondragon]

Ok, after thinking a bit about this it seems that there's no way around iterating over the records to

1. Detect non-null _sdc_deleted_at
2. Extract the primary key values

so we can emit a DELETE FROM <table> WHERE (key_i = <record[key_i]> AND ...) OR (key_i = <record[key_i]> AND ...).

If you know a better way, do let me know 🙂

[dvenardos]

@edgarrmondragon, I was looking at the proposed modification from Claude Code. It is like a smart toddler. so needs an expert to review. Here is what it proposed.

inherited-marinating-balloon.md

What are your thoughts?

[edgarrmondragon]

@edgarrmondragon, I was looking at the proposed modification from Claude Code. It is like a smart toddler. so needs an expert to review. Here is what it proposed.

inherited-marinating-balloon.md

What are your thoughts?

We can give that a try in #3450. I wonder how deleting after inserting would play with upserts 🤔

[dvenardos]

Sounds good, I will leave it in your capable hands!

Yeah, it would need to go after updates because you could have an update, followed by delete.