Data Warehouse Architecture: Traditional ETL vs Big Data Hybrid

• DW Flow Architecture - Traditional

           Using ETL tools like Informatica and Reporting tools like OBIEE.

 

1. Source OLTP to Stage data load using ETL process.
2. Load Dimensions using ETL process.
3. Cache dimension keys.
4. Load Facts using ETL process.
5. Load Aggregates using ETL process.
6. OBIEE connect to DW for reporting.

 

 

• DW Flow Hybrid Architecture - Bigdata integrated with Traditional Load Methods.

     Hybrid architecture blend traditional ETL load with bigdata processing techniques. Here are some of the key features.

 

1. ETL process will be used for low volume structured data loads.
2. Bigdata processing techniques used for high volume , unstructured or semi structured data loads.
3. Source OLTP to HDFS load using sqoop import.
4. Dimensions load require update / insert processing and loaded via ETL.
5. Fact tables loaded using map reduce jobs.
6. Aggregates created using Hive QLs.
7. Aggregates exported to DW using Sqoop.
8. OBIEE connect to both DW and Hive tables for reporting.

 

 

 

 

Related reading on this blog

• Modern Data Engineering Architecture: A Practical Guide
• ETL vs ELT: Differences, Examples and When to Use Each
• Big Data Transformation: From Data Warehouses to Lakehouses