Designing and Developing the ETL System

Designing and Developing the ETL System

1. Step 1 — Draw the High Level Plan

2. Step 2 — Choose an ETL Tool

   • Self-documentation that comes from using a graphical tool.
   • ETL tools offer advanced transformation logic.
   • Improved system performance at a lower level of expertise.

3. Step 3 — Develop Default Strategies

4. Step 4 — Drill Down by Target Table

   • Ensure Clean Hierarchies
   • Develop Detailed Table Schematics
   • Develop the ETL Specification Document
   • Develop a Sandbox Source System
   • Develop a Sandbox Source System

5. Step 5 — Populate Dimension Tables with Historic Data

   • Populate Type 1 Dimension Tables
     • Dimension Transformations
     • Dimension Table Loading
   • Populate Type 2 Dimension Tables History
     • Populate Date and Other Static Dimensions

6. Step 6 — Perform the Fact Table Historic Load

   • Historic Fact Table Extracts
   • Fact Table Transformations
   • Fact Table Loading
   • Test, Test, and Test Again
   • Develop Incremental ETL Processing

7. Step 7 — Dimension Table Incremental Processing

   • Identify New and Changed Dimension Rows
   • Process Changes to Dimension Attributes

8. Step 8 — Fact Table Incremental Processing

   • Fact Table Extract and Data Quality Checkpoint
   • Fact Table Transformations and Surrogate Key Pipeline
   • Late Arriving Facts and the Surrogate Key Pipeline
   • Incremental Fact Table Load
   • Speed Up the Load Cycle

9. Step 9 — Aggregate Table and OLAP Loads

10. Step 10 — ETL System Operation and Automation

    • Schedule Jobs
    • Handle Predictable Exceptions and Errors Automatically
    • Handle Unpredictable Errors Gracefully
    • Maintain Database Objects
    • Develop and Test ETL Automation

Images and files in the map