ETL design is one of key processes of a DWH project. Improperly designed ETL systems lead to considerable complexity and significant operational problems in later phase of the project which costs extra time and resources due to issues and required rework.
To design a good ETL system, ETL designers should follow basic steps in respective phases of the project as below -
To design a good ETL system, ETL designers should follow basic steps in respective phases of the project as below -
Requirement Gathering
Data Analysis and Profiling(5D)
Data Integrity
Data Volume
Data Velocity
Logical Data Modelling
Source to Target Mapping
Database and ETL Design
ETL design guidelines
Extraction
Transformation
Load
ETL best practices
ETL strategies for known problems
ETL Development
ETL best practices
Development Tracker, Estimation and Project Plan
ETL Testing
Testing setup activities
Stats collection
Performance tuning
Implementation
Implementation Plan
Backout Plan
ETL support documentation