Hi all, for the last week I have been trying to implement an EL pattern using only core Meltano features and I am struggling. Before I migrated our team to using Meltano for data loads, we would link a tables load record (aka state) to a pre-action. Primarily, if no state record was found we would truncate the target table before inserting. It was an implicit flag saying "please do a full cleanout of the target and re-sync" without us having to both clear the state and truncate the table by hand. With Meltano, the target does not have visibility into the starting replication value (or lack thereof) and I cannot find a way to communicate it over safely. I imagine this is because it is an anti-pattern for meltano and I am trying to force to do something it shouldn't. I have seen archived conversations regarding implementing

hard_delete

but that does not work for my use case because record by record deletions are prohibitively expensive for OLAP databases (my target). Plus that would only delete records that are still in the source and we work primarily with sources that allow deletes. How do others manage this sort of flow when using Meltano as your primary EL tool? I am relatively new to the field (8 years experience), so it may be that this flow is bad practice and there are better ways.

Hi Adam, I would explore

hard_delete

some more - I don't think you are limited to record-by-record deletes in the implementation, unless you are saying that is the only way data can be deleted in your database? I would have thought a

DROP TABLE

or equivalent for selected streams would cover this. FWIW, I believe the cleanest way is by handling the ACTIVATE_VERSION message, but unfortunately it's not a "one size fits all" solution as both the tap and target would have to support it.

Let me take a deeper look into

hard_delete

, I am never surprised when I misunderstand how an implementation works. When I read it in several taps, my understanding was that it checked for a flag in each record and then the target could delete that record if the flag was set. Our target is Redshift which is rather ... particular for lack of a better word. It cannot manage individual deletes with any degree of efficiency so that has always been a no-go for us. In addition, tables pretty much need to be permanent fixtures because the Redshift optimizer relies on table stats over time to make its decisions. If you drop the table and then try to query it, it runs magnitudes slower. So, you are left with tables that cannot be replaced and bulk load/truncate operations only.

> When I read it in several taps, my understanding was that it checked for a flag in each record and then the target could delete that record if the flag was set. Those sound like custom implementations. Looks like the SDK only respects

hard_delete

automatically in the in-built

ACTIVATE_VERSION

handling logic: https://github.com/meltano/sdk/blob/3365ce6280e9b62437a440771632ffa3ce78ece0/singer_sdk/sql/sink.py#L412-L418 I would assume you can apply the same logic in the

setup

method of your sink class?

Yes, our version is actually forked from that project. The primary reason is, like every company, we have our quirks that make a general solution not work 100% of the time or meet every need. It was non-negotiable with my team to adopt meltano if we couldn't keep the workflow discussed in this thread from our legacy system. So I needed to enhance the project to add some flags and logic to truncate/edit the target table based on the starting replication value. In addition, we did not want auto table creation so I needed a fork to strip that out. I chose to not request updates to the existing project because what good would it be if it had a bunch of code specifically tailored to my team 🙂

Gotcha, we did something similar with our fork of

target-snowflake

previously where we ended up exposing the required behavioural changes as new settings. That way, they could feasibly be merged back and be made available to all users running into the same issues. I guess if the target contains company-specific logic that can't be made generic in some way, then it wouldn't really be useful as a public variant anyway.