| Parameters | ETL (Extract, Transform & Load) | ELT (Extract, Load & Transform) |
|---|---|---|
| Data size | Better suited for dealing with small to medium structured datasets that require complex transformations. | Better suited when dealing with massive amounts of structured & unstructured data |
| Order of the process | Data transformation happens after extraction in the staging area. After transformation, data is loaded into the destination system | Data is extracted, loaded into the target system and the transformed |
| Transformation process | The staging area is located on the ETL server | The staging area is located on the source or target database |
| Business Scenario | Source & target databases are different (e.g., Oracle source & Teradata target database) | Source & target databases are same (e.g., Oracle source & target databases) |
| Load Time | Longer than ELT because it’s a multi-stage process: 1. Data loads into staging area 2. Transformations take place 3. Data loads into the DWH | Data loading happens faster because there’s no waiting on transformations & data loads only one time into the target data system |
| Strengths | Facilitates data quality, Data security, Data compliance | Greater flexibility & speed, Can handle large volumes of unstructured data |
# ELT (Extract, Load, Transform)
- In the Data Lakehouse, the ELT (Extract, Load, Transform) approach is followed instead of traditional ETL.
- Data is first loaded in its raw form into the Bronze layer, and then lightweight, “just-enough” transformations are applied when moving into the Silver layer.
- The focus at this stage is on speed, scalability, and agility, ensuring that data is quickly ingested and made available for downstream use.
- Rather than applying heavy business logic early, the Silver layer performs essential cleansing and standardization only, while complex transformations and business rules are deferred to the Gold layer.
- This approach enables faster data availability and supports flexible, use-case-driven transformations later in the pipeline.