如果使用ADF Copy管道，如何跟踪已删除的记录？ [英] How to track deleted records if ADF Copy pipeline is used?

问题描述

我有源SQL Server数据库，目前正在使用ADF将表数据复制到DW的登台表。

I have source SQL Server DB and currently copying table data with ADF to staging table of DW.

如何跟踪源中已删除的记录，以便EDW可以更新？

How to track what what records have been deleted in source so that EDW can be updated?

Kenny_I

推荐答案

你好
Kenny_I  感谢您的询问。



如果您正在做满载（替换所有接收器的内容，每次都有源，然后你不需要做任何事情。

Hello Kenny_I and thank you for your inquiry.

If you are doing full loads (replace all the contents of sink, with source, every time), then you shouldn't need to do anything.

如果你正在做增量加载，细节可能取决于你的范例和使用习惯。

If you are doing incremental loads, the details may depend upon your paradigm and usage habits.

这里有两个建议ns用于水印方法：

Here are two suggestions for the watermark methods:

实施软删除： 在源表中，不添加实际删除记录，而是添加"is_deleted"列，默认为False值。 如果要删除行，请将值设置为True并更新水印。 在
成功更新两个数据库之后，您可以返回并删除所有具有"is_deleted"的行。设为真。



实施on_delete触发器： 在源表中，设置一个触发器，以便在您尝试删除行时，将其写入新表"rows_to_delete"以及水印/时间戳。 成功递增加载新记录后，
将写入目标，在"rows_to_delete"上执行类似的增量加载，除了在目标数据库中，这些行执行一个存储过程，该过程查找并删除相应的行。目的地表。

Implement soft-deletes:  In your source table, instead of actually deleting records, add a column "is_deleted", which defaults to a False value.  When you want to delete a row, set the value to True and update the watermark.  After a successful update of both databases, you can go back and delete all rows which have "is_deleted" set to True.

Implement on_delete triggers:  In your source table, set up a trigger, so that when you try to delete a row, it is written to a new table, "rows_to_delete", along with a watermark/timestamp.  After a successful incremental load of new records are written to your destination, do a similar incremental load on "rows_to_delete", except in the destination database, these rows do a stored procedure which finds and deletes the corresponding rows in the destination table.

还有更多与特定用例有关的方法。 这些想法对你有帮助吗？

There are more methods which pertain to specific use-cases.  Do these ideas help you?

这篇关于如果使用ADF Copy管道，如何跟踪已删除的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！