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 transactional extract. Usually you will use your ETL (Extract, Transform, Load) framework to record last extract point, for example, date, timestamp or auto-increment column value.
