One way I get around this is having an append only version of the main table, and then a "most current record" version of the table. My append only table will be a running record of all the data I've loaded unmodified. Then I run a create table as select statement against my append only table to generate my new version of the final table. So in essence, I'm "deleting" the old version of the record by omitting it when I create my new version of the final table.

Not necessarily. You can insert a new row with a field named ‘deleted’ or similar set to ‘true’. This is how you would implement soft deletes in an append-only table.

It doesn’t have to be. You Can write reads that don’t return entries if a separate deletion entry exists.

Can’t say I’ve worked that way in sql, but I have seen that pattern in append only data structures. With for instance a “order fulfilled” entry essentially is a delete operation on an outstanding order entry.