[Task]: Adjust transformation logic to handle cascading delete scenarios

[chouinar]

Summary

Lets assume the following scenario. We previously imported and opportunity, synopsis, and a few applicant types for that synopsis.

That opportunity record gets deleted in the Oracle database. In Oracle, what will happen is that a record will be removed from the opportunity/synopsis/applicant type tables, and created in the equivalent history tables.

In our load process, we'll mark all of the non-history table rows as is_deleted=True and then in the transformation process we'll try to delete the records.

When we process the opportunity record, we delete it, and SQLAlchemy even handles deleting all of the children (the opportunity summary, and applicant types) for us. However, when we then try to process the delete events for the synopsis and applicant type records, it will error because we already deleted them. When a record errors, we continue processing, but leave that one alone, meaning it would keep occurring.

--

We need to do something to adjust this logic. A few options:

• Just don't error when we try to delete something that doesn't exist - we wrote this error logic, it might be overly restrictive
• When we do a delete on an opportunity, note down the IDs and only error if it isn't in that list - this would only work assuming we always process everything in a single batch - while we do that currently, it might be risky to do this
• something else?

Acceptance criteria

• [ ] Implemented a solution that fixes this issue
• [ ] Added tests validating this behavior

[chouinar]

This was implemented in the fork that we recently merged in: https://github.com/HHS/simpler-grants-gov/commit/c154c52260e3a8a5ed989f056b7969f172c79971