Beginner’s Guide: Extract Transform Load (ETL) Playbook- Incremental load design pattern Part2

The intended audience of this article are IT enthusiasts and beginner-level data engineers interested in understanding some data engineering principles

In the previous post (read here), I described the various incremental data extraction design considerations, and requirements. In this article, I will continue from where I left off to explain the sample solution approach.

Example of Incremental Source Database Table Extraction to a Staging Table using ETL tool

ETL Architecture

One of the best practices when designing an incremental extraction process from a source system that involves multiple tables is to use an ETL control table.

ETL Control Table

This is the configuration table that will control and drive the ETL process. This table normally will be at the granularity of each table that needs to be extracted. Below are a couple of fields we tend to capture in the control table

a) Source Table name: This is the name of the source table that we will extract data from

b) Staging Table Name: This is the staging table we will temporarily write the incremental records extracted from the source table.