feat: Standard configurable load methods

[pnadolny13]

Closes #1783

I was attempting to add this overwrite feature to target-pinecone MeltanoLabs/target-pinecone#4 so I decided it would probably best to draft it in the SDK while I was at it.

• Adds a standard config load_method for: append-only, upsert, overwrite
• Implements the overwrite workflow in SQL targets
• By default overwrite isnt supported until the target developer activates it. Is this what we want or should it default to True?
• I wasnt sure if the truncate logic should go in setup of the SQLSink class or prepare_table. For now I chose prepare_table
• Ideally a target would throw a nice NotImplementedError if the user choses one of the load methods that arent supported but I'm not totally sure how to do that as of right now. It might require more thought.

@edgarrmondragon let me know what you think overall and also specifically with the naming of everything.

cc @tayloramurphy in case you have opinions on naming or default behavior.

[codecov]

Codecov Report

Merging #1893 (1307a37) into main (fca070e) will increase coverage by 0.03%.
Report is 1 commits behind head on main.
The diff coverage is 100.00%.

@@            Coverage Diff             @@
##             main    #1893      +/-   ##
==========================================
+ Coverage   87.01%   87.04%   +0.03%     
==========================================
  Files          59       59              
  Lines        5097     5109      +12     
  Branches      826      827       +1     
==========================================
+ Hits         4435     4447      +12     
  Misses        465      465              
  Partials      197      197              

Files Changed	Coverage Δ
singer_sdk/connectors/sql.py	82.83% <100.00%> (+0.34%)	⬆️
singer_sdk/helpers/capabilities.py	92.42% <100.00%> (+0.62%)	⬆️
singer_sdk/target_base.py	92.33% <100.00%> (+0.02%)	⬆️

📣 We’re building smart automated test selection to slash your CI/CD build times. Learn more

[tayloramurphy]

@pnadolny13 I like overwrite! It matches Snowflake too https://docs.snowflake.com/en/sql-reference/sql/insert#optional-parameters

[edgarrmondragon]

@pnadolny13 I like the approach 👌. Perhaps we can add a test for the SQLite sample.

[pnadolny13]

@edgarrmondragon I added a test for the sqlite sample, and refactored a little. I couldnt get custom query parameterization to work for some reason so I used f strings and had to add noqa flags for ruff to pass.

[pnadolny13]

@edgarrmondragon I swapped the logic in this to use a drop/recreate flow instead of truncating. I started to implement the rename + rollback workflow but I wasnt able to find anywhere to easily put logic to rollback if an exception is thrown. I tried using the clean_up method which worked when the sync was successful to remove the backup table before exiting but I couldnt figure out how to easily catch exceptions and revert to the backup table prior to exiting. I create a draft PR to this branch with the code I was tinkering with.

[edgarrmondragon]

@pnadolny13 I think I was probably overstating the risk of data loss caused by pipeline failures when syncing in overwrite mode. If the user is OK with overriding existing data, then they're probably also OK-ish with losing data if the pipeline fails, as long as they can load data on the next run.

[edgarrmondragon]

@edgarrmondragon I swapped the logic in this to use a drop/recreate flow instead of truncating. I started to implement the rename + rollback workflow but I wasnt able to find anywhere to easily put logic to rollback if an exception is thrown. I tried using the clean_up method which worked when the sync was successful to remove the backup table before exiting but I couldnt figure out how to easily catch exceptions and revert to the backup table prior to exiting. I create a draft PR to this branch with the code I was tinkering with.

@pnadolny13 Let'd punt the rollback support and create an issue for it. We'll worry about it if users are annoyed by losing data 🙂