I made the "mistake" of talking to my software engineer partner who majored in pure math about the problem.

They agreed that 
1) bringing both tables into memory is not a good idea, especially as then the diff tables will then also be held in memory
2) doing the set subtraction operations is really, really inefficient, especially as it looks like the pyspark subtract is based off SQL Except Distinct which does not use indexing, so for each row in the base table it's iterating thorugh the entire other table checking for a duplicate, and this is then done twice. Not sustainable, not scalable.

They're solution was to process the tables row by row and check for equivalence.

1. sort the source and target tables by their primary key, (there's already an issue in that i can't sort the source tables, so i have to copy the source to a local table)
2. iterate row by row across both tables simultaneously. If there is a mismatch, take the row that has the lower index - this is the row that is missing in a table - if it is from the source table, then it is an added record, if it is from the target table (reflection of the previous state), then it is a deleted row.
3. add this row to a seperate log table with the action "insert"/"delete"), and add/remove the row from the target table.
4. Adjust the iterator so that the equivalent rows are processed, and continue to the next row.

This solution iterates once through the table, with two sorts and a copy, and runs a query per row, essentially.

My concerns with this approach are mainly just that i'd be building my own iterator, rather than leveraging platform streaming capabilities, and that I'm not sure I can guarantee that my tables all have primary keys to sort by.

I'm leaving out a lot about autoincrement issues and the relevance of ordering, and what happens with schema drift etc.

A second approach would be to use Structured Streaming perroiw functionality and do a lookup per row of the source table into the target table, and if it exists, add the current date to a "last checked" column or something, such that any rows that are deleted would have an outdated "last checked" value. any rows that don't exist can be added with the current date to a "date added" column. This means doing a lookup across the target table per row of the source table. Potentially indexing the target table could help here, I don't know. It also means the source and target tables are different shapes and i'd need to exclude my metadata columns for the row-wise comparison.

Any insights into the relative efficiencies of any of these functions are still welcome. I think I'll try my partner's approach first, see how it compares to the original approach. Maybe if I have just crazy excess time I'll try out my idea and see what the impact is. If I have any interesting results to share I'll add it here.

I feel like I'm doing a lto of wheel-reinventing, though, so if anyone has a "you're thiking about this completely wrong" comment, I'm all ears.