RFC: new incremental mode strategy diff.

[fdcastel]

At our company, we’ve developed some internal tools that share many similarities with Sling. I’m interested in contributing some of that work back to the project and start to adopt Sling in place of our own tools.

One specific use case we handle involves comparing two snapshots of a dataset taken at different points in time.
To keep things simple, let's say that the schema remains consistent between the snapshots (i.e., no added or removed columns).

The tool analyzes the differences between the two datasets and outputs a set of inserted/updated/deleted records. These results can then be fed into another tool to merge the changes back into a target database or file.

Would a feature like this be of interest to the Sling project? If so, we’d be happy to flesh out the idea further and work on contributing it.

Some open points for discussion:

• Handling conflicts
• Schema changes (e.g., added or removed columns)

We recently ported the code to use DuckDB as the backend, so I believe adapting it to work with Sling should be fairly straightforward.

[fdcastel]

P.S.: This solution would be database-agnostic: it’s not tied to any specific database implementation and can work across databases, files, or any combination of the two.

It is, in essence, a form of CDC, though I’ve chosen not to use that term here to avoid potential misunderstandings.

[flarco]

Thanks for the suggestion and details.

Some thoughts:

• In your workplace, do you pull down both snapshot datasets (in full) in order compare? I've looked into something like this before (outside of sling), and this was a pretty good approach I found: https://reladiff.readthedocs.io/en/latest/technical-explanation.html. Essentially computing the hashes remotely and comparing after reducing.
• Also, where do you envision this diff computation would occur in the lifecycle of the incremental load? For example, when sling runs in incremental mode, it typically queries on the target table to get the max value, and then selects from source all new data after this max value.
• About We recently ported the code to use DuckDB as the backend, does this code live in a public repo? Would be great to take a look. Sling works pretty closely with the DuckDB CLI, so that could be a great way to bring this in.

[fdcastel]

• In your workplace, do you pull down both snapshot datasets (in full) in order compare?

Yes. So far, all the use cases we've had involved around ~1M rows, top. So we're fetching everything into a local .parquet file. It's simple and fast.

• Also, where do you envision this diff computation would occur in the lifecycle of the incremental load?

I don’t have any thoughts at the moment, but I’ve also studied the reladiff approach in the past, and it seemed like a good solution.

• About We recently ported the code to use DuckDB as the backend, does this code live in a public repo?

Unfortunately not. But the comparison code in DuckDB SQL may be found here.

Sling works pretty closely with the DuckDB CLI, so that could be a great way to bring this in.

Same here. DuckDB is an awesome tool!