Hello ! First thank you for your great

singer-sdk

🙂 I am developing a

target

and I’m writing batches into a temporary table in my database. I would like to overwrite the final table with the newly created temporary table at the end of all batches from the

sink

. I didn’t find a function called at the end of all batches in the sdk, maybe I overlooked something ? Why : If we copy the temporary data into the target table for each batch, we obtain a target table partially filled in production Thank you 😉

Take everything in this repo with a grain of salt, but I used

clean_up

https://gitlab.com/autoidm/autoidm-target-mssql/-/blob/main/target_mssql/streams.py#L323

Thank for your answer @visch! It’s exactly what I would need, but I’m trying to keep using the

sdk

😉

yeah that’s what I started doing thanks 😉

drain_all

is called more than just there, so this may not be the answer you're looking for. https://sourcegraph.com/gitlab.com/meltano/sdk@main/-/blob/singer_sdk/target_base.py?L303:14#tab=references If your schema changes it'll get called as well

yes I’ve seen that but in our cases schema is not changing, so I think it should be ok to use it for that 🤔

@andreas_zaidan this is a good question. @aaronsteers and I discussed it in this issue a while ago. In Singer theres not a mechanism right now for targets to know when a stream is completed. Pre-sdk I built some targets with the assumption that a schema change means a stream is complete but that assumes your tap is sending streams serially, which isnt always the case. Also state messages are written out after a batch is drained so the data should be in its final state at the end of

process_batch

or theres a chance that the state bookmarks could be incorrect (i.e. state says records were written but they never got transferred from the temp table so the next sync skips those records and the target table is out of sync).

@andreas_zaidan - I think as of now, the recommendation would be to write and cleanup batches as they are completed. For full table sync operations, you might receive an

ACTIVATE_VERSION

message if the tap supports it. Are you primarily thinking of incremental or full table sync operations? For incremental operations, you might literally never finish the stream in cases like the proposed --tail mode, so it's important to be saving and cleaning up batches as you go.

Thank for your answer @pat_nadolny ! In my use case, I want to overwrite the target table by the temporary one in the case of full table sync operations so I guess that state should not be important

This sounds like an opportunity to plug into activate-version. ☝️

Alright @aaronsteers I’ll look for that feature then thank you so much

@andreas_zaidan heres some info on

ACTIVATE_VERSION

if youre not familiar - https://hub.meltano.com/singer/docs#activate-version

@andreas_zaidan one thing that will still make this tricky for you is that I don't know if there are any signals earlier in the stream that would tell you when to expect an ACTIVATE_VERSION message at its end. I'll add a comment on the working group issue here: https://github.com/MeltanoLabs/Singer-Working-Group/issues/9#issue-991554782

yeah that would be great ! So

process_endofpipe

is called before a

drain_all

?

The process_endofpipe() method invokes the last drain_all(), but in theory it could also be used for final cleanup.

I totally agree with you @aaronsteers

cleanup_target

should be aware of the ending state of the operation 😉 For example : • if failed I would like to clean up my temp tables • If successed I would like to commit the table to production

Thanks. Feel free to comment directly to the issue also if you have time. That will allow us to ping you in gitlab within the thread.