Incremental Extraction From MySQL Database

Full extraction of an entire table or database can be very rare. In most cases, users extract only what they need from the databases, which is usually the most recent data.

In incremental extraction, only records from the source table that have changed or been added will be extracted. The implementation details of this will vary from use case to use case, but the general idea is to use metrics such as last_update_date or is_active to filter records that have been recently changed and extract only those.

Incremental extraction is a very common method for transferring data from a transactional OLTP database into a data warehouse. We can use incremental extraction to get only the recent transactions from the production database and transfer only those to the data warehouse.

Example

Suppose we’re in charge of transferring recent activities and transactions from a production database of a stock investment company into a data warehouse for analysis and auditing reports.

To simplify this example, let's say that the production database is made of two tables Customers and Deals. The data warehouse is made of a single table named Fact_Table.
Our task is to create an ETL pipeline that extracts the most recent data from the Production database.

To know exactly which records we should extract, we use the column Close_deal_date from the data warehouse to check the last time the ETL pipeline loaded data into the data warehouse.