Traditional ETL vs ELT on Hadoop

Source: Bitwise Blog

The data warehouse area has been dominated by RDBMSes and traditional ETL tools. But traditional ETL tools are limited by problems related to scalability and cost overruns.

ETL: extract-transform-load

The ETL process typically extracts data from the source systems, transforms it to fit the model of data warehouse and finally loads it to the data warehouse.

Advantages of ETL

• Ease of development: process usually starts from output, backwards, as the data model for target system is predefined and involves loading just the relevant data.
• Process maturity: ETL has been the norm for data warehouse development for over two decades. This leads to wide availability of tools and expertise.

Disadvantages of ETL

• Non-flexibility: ETL process loads only important data, identified at design time. Adding new data attribute involves re-engineering the entire ETL routine.
• Hardware: most ETL tools come with their own proprietary hardware requirements.
• The maintenance, hardware and licensing costs.
• Mostly limited to relational data.

ELT: extract-load-transform

As opposed to loading just the transformed data, the ELT process loads the entire data into the data lake. Optionally, the load process can perform basic validations and data cleansing rules. The data is then transformed for analytical reporting as per demand.

Advantages of ELT on Hadoop

• Faster load times.
• Separation of concerns: ELT process separates loading and transformation into independent blocks, minimises interdependencies between them and therefore leads to easier project management.
• Flexible and future-proof: entire source data is available in the data lake. This guarantees that future requirements can easily be incorporated into the warehouse structure.
• Utilises commodity hardware: Hadoop uses the same hardware for storage as well as for processing.
• Hadoop is free and open-source.
• Not limited to relational data: can process semi-structured and unstructured data.

Disadvantages of ELT Process

• Process immaturity: ELT process as not been widely adopted. As a result of limited adoption, the number of tools and experts available is currently limited. However, this is changing fast.
• Apart from being a change in environment and technical skillset, it requires a change in mindset and approach.