Chapter 20
ETL System Design and Development Process and Tasks
Developing the extract, transformation, and load (ETL) system is the hidden part of the iceberg for most DW/BI projects. So many challenges are buried in the data sources and systems that developing the ETL application invariably takes more time than expected. This chapter is structured as a 10-step plan for creating the data warehouse's ETL system. The concepts and approach described in this chapter, based on content from The Data Warehouse Lifecycle Toolkit, Second Edition (Wiley, 2008), apply to systems based on an ETL tool, as well as hand-coded systems.
Chapter 20 discusses the following concepts:
- ETL system planning and design consideration
- Recommendations for one-time historic data loads
- Development tasks for incremental load processing
- Real-time data warehousing considerations
ETL Process Overview
This chapter follows the flow of planning and implementing the ETL system. We implicitly discuss the 34 ETL subsystems presented in Chapter 19: ETL Subsystems and Techniques, broadly categorized as extracting data, cleaning and conforming, delivering for presentation, and managing the ETL environment.
Before beginning the ETL system design for a dimensional model, you should have completed the logical design, drafted your high-level architecture plan, and drafted the source-to-target mapping for all data elements.
The ETL system design process is critical. Gather all the relevant information, including the processing ...
Get The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
