Data Engineering - Delta Extract

When extracting data from source databases, there are usually three approaches:

• Full extract - extracts all the records from the source table. This usually applies to small to medium size tables, for example, product tables. 
• Transactional extract - extracts all the new records from the last extract. This is good for transactional sources like banking transactions, logs, etc. 
• Delta extract - extracts all the changed records including updated, new and deleted ones. This can be used to reduce the amount of data that needs to be transferred from source system to target system. This usually involves CDC (change data capture). 

This diagram shows the high-level flow of delta extract. It is very important to include the file for deleted records unless your data source never deletes records. If your source team cannot provide the deleted records, you will need to request a full extract so that you can work out the changes by comparing with your target tables.