Support for CDC

[nixent]

Is there any plans to support incremental load based on CDC for MS SQL sources?

[dipeshkd]

I second this request

[flarco]

Thanks for the suggestion.

Thought about this over time, and landed on the philosophy of keeping things super simple. CDC has some powerful features but comes at a cost of added complexity. Additional configurations usually need to be set on the server side, which introduce hurdles to apply (e.g. rebooting a production database to apply those settings, at least in Postgres) and make the setup more averse to future schema/infra changes.

My vote is to keep Sling lean. The incremental mode likely covers 95% of all cases, where you can provide an indexed timestamp / integer column as the update_key, with a primary_key, and Sling will pick right up the changes. For heavy-duty CDC, something like Fivetran, debezium or streamkap would probably work better. With Sling, it's easy to full refresh or provide a custom SQL query, which don't work with typical CDC setups.

One feature I think about that would be great is the ability to more efficiently capture DELETE transactions. This is currently supplanted by the snapshot mode, where the full dataset is replicated and ingested with a timestamp each run. But that doesn't work so well with massive tables, so there is room for improvement.

[nixent]

Thanks for the quick response. I also like simplicity and that is one of the reasons why I started looking into Sling.
Indeed, high watermark approach is good for append/update only use cases, however, it cannot cover delete scenario. In case of large tables, CDC becomes only cost effective option. CDC config from schema/infra changes perspective is not much different from granting access to Sling sync service account.
With current implementation of custom SQL source it is possible to do upsert of new/modified records by simply reading from respective cdc table or calling cdc.fn_cdc_get_all_changes_* function. Only challenge is with deletes.

[flarco]

Interesting, so it's just stored in a table... That's pretty cool. Looking at this page: cdc-fn-cdc-get-all-changes, the DELETE transactions are not captured there? I'm not too familiar with the SQL Server CDC workflow, but wouldn't the __$operation = 1 mean delete?

[nixent]

Yes, SQL Server just stores CDC data in a system table with same schema as tracked table plus few more fields to keep DML info.
_$operation = 1 identifies deleted records but how to delete those records from the destination table with Sling?

[flarco]

Yes, I see what you mean. Ok, let me consider this. Thanks.

[dluo-sig]

Also following this.