ETL and Data Warehouse Architecture: Concepts, Five‑Step Process, and Advanced Techniques

What is ETL

ETL stands for Extract, Transform, Load. It extracts data from OLTP systems, transforms and integrates data from multiple sources, and loads the consistent data into a data warehouse, effectively moving data from OLTP to OLAP.

Data Warehouse Architecture

A data warehouse (DW) is a relational database built on top of OLTP sources to support multidimensional analysis. It uses star or snowflake schemas: the star schema has a central fact table surrounded by dimension tables, while the snowflake schema normalizes dimensions into sub‑tables. Star schemas offer faster aggregation; snowflake schemas provide clearer relationships with OLTP systems.

Five‑Step Enterprise‑Level ETL Process

1. Determine the Theme

Identify the analytical subject, e.g., beer sales for a specific month and region, which becomes a data mart within the warehouse.

2. Determine Measures

Select quantitative indicators (measures) such as sales amount, and decide how they will be aggregated or calculated (e.g., sum, count, min, max).

3. Determine Fact Grain

Set the granularity of fact data to the smallest level needed (e.g., daily transaction records) to preserve detail for later analysis.

4. Determine Dimensions

Define analysis axes such as time, region, product, and design dimension hierarchies and levels. Use wide dimension tables ("fat" principle) and surrogate keys for efficient joins. Handle slowly changing dimensions (SCD) with appropriate strategies.

5. Create Fact Table

Join source tables with dimension tables to generate the fact table, containing surrogate keys and measures only ("skinny" principle). Add a unique identifier if needed for future extensions, and create appropriate primary keys and indexes.

Advanced ETL Techniques

1. Staging Area

Use a staging database to temporarily hold extracted data, reducing load on the source OLTP system and allowing intermediate transformations, temporary tables, and ETL logs.

2. Timestamps

Apply timestamps to track data changes, support SCD handling, and enable incremental extraction based on source system timestamps.

3. Log Tables

Maintain log tables to record extraction counts, success/failure rows, error details, and processing times for troubleshooting and reprocessing.

4. Scheduling

Schedule incremental updates of fact tables, considering data volume and update frequency, and ensure dimension tables are refreshed before fact tables.

ETL vs. SQL

ETL excels at multi‑source data integration, cleansing, and loading into a warehouse, while SQL offers high‑performance querying and manipulation within a single database but lacks cross‑source flexibility.

ETL Tools and Algorithms

Common commercial tools include IBM DataStage, Informatica PowerCenter, and Teradata ETL Automation; open‑source options include Pentaho Kettle (PDI). Standard ETL algorithms cover historical‑ladder (SCD), append (event tables), upsert (master tables), and full‑refresh (parameter tables), each suited to different data‑source characteristics.

Data Source Classification and File Types

Sources are classified as transaction (event) tables, status tables, or code/parameter tables. Data files may be incremental, full, or incremental with delete flags (e.g., DEL_IND='D').

Algorithm Selection for Near‑Source and Integrated Models

Various algorithms (APPEND, MERGE, regular ladder, incremental‑delete ladder, full‑refresh ladder, economic variants, PK_NOT_IN_APPEND, source‑date ladder) are applied at different modeling layers to handle inserts, updates, deletes, and historical tracking efficiently.

Conclusion

Mastering the five‑step ETL workflow, understanding warehouse schemas, and selecting appropriate algorithms and tools are essential for building reliable, high‑performance data warehouses.