Syncing and matching local records to external database that behaves like a black hole

Question

Black hole problem

I am working on matching and daily syncing of our internal data records to an external database that behaves like a black hole, meaning, we cannot view nor extract any data from it for verification. Another of its properties is that one can only do inserts and deletes, so no direct updates.

How to CRUD

So for insertions of new data, it is easy, we just send the data records over in a file marked as create. But for updates, we need to know the original records that we want to update on (must remember and match on all columns), send those data verbatim over in a file marked as delete, once the deletion is done, follow by inserting back the updated records just as if they were new.

Question

My question is: how would you design a local database that can log both our insertions and deletions on the external database, such that when combining the chains of both operations, we know the exact set of records and data that currently exist on the external database? Would it work with two SQL tables logging inserts and deletes separately? If yes, could you give an example of how one could query both tables for the current set of records?

My current idea

Two separate SQL tables to log what I have inserted/deleted.

The inserted_records table may look like this:

    id | file_version | contract_id | customer_name | start_year
    9  | 6            | 1           | Alice         | 2015
    10 | 6            | 2           | Bob           | 2015
    11 | 6            | 3           | Charlie       | 2015

Accompanied by a separate and empty deleted_records table with almost identical columns.

Now, if I want to

• change the customer_name from Alice to Dave on line id 9
• change the start_year for Bob from 2015 to 2020 on line id 10

Two new lines in inserted_records would be generated, line 12 and 13, in turn creating a new insertion file 7.

    id | file_version | contract_id | customer_name | start_year
    9  | 6            | 1           | Alice         | 2015
    10 | 6            | 2           | Bob           | 2015
    11 | 6            | 3           | Charlie       | 2015
    12 | 7            | 1           | Dave          | 2015
    13 | 7            | 2           | Bob           | 2020

Then their original column values in line 9 and 10 are then copied onto the previously empty deleted_records, in turn creating a new deletion file 1.

    id | file_version | contract_id | customer_name | start_year
    1  | 1            | 1           | Alice         | 2015
    2  | 1            | 2           | Bob           | 2015

Now, if I were to send in the deletion file 1 first followed by the insertion file 7, I would get the result that I wanted.

But the problems in my idea are

• When merging and querying for the result of both operations, how do I know that line 1 in deleted_records wants to delete line 9 in inserted_records? A problem of how to track between records in these two tables.
• how can I indicate order of operations between these two tables? Timestamp/hash/foreign key/etc.? As in which file goes before which?

Answer 1

I would suggest to modify your approach to move inserted, deleted and updated items all in the same table. Mark deleted items by a new boolean 'is_deleted' column. That way you can find the latest version (deleted or not) with one simple query.

This reminds a lot of event sourcing, which takes these ideas much further, by sending events specific to the business domain instead of database operations. In your example you could send "Customer Name Changed" and "Customer Start Year Changed" events, for example in a Kafka topic. Then the data model of your internal data source and the black hole could chose whatever database layout is most appropriate and you don't have to worry about syncing on that level. I am not necessarily suggesting that event sourcing is the right solution for you, but might be worth the time to see whether some of its ideas might apply to your situation.