What is it?
These are two slightly different processes used to create new database objects. The difference between the two processes is where data transformations take place. ETL is the traditional method or process whereas ELT is a relatively new process.
ETL
- Extract
- Transform
- Load
This involves extracting data from various data sources, often in unstructured or semi-structured formats, transforming it into a structured format (by cleaning, restructuring etc), and then loading it into a target system such as a data warehouse, so it can be queried and used for reporting.
ELT
- Extract
- Load
- Transform
This is a popular approach in cloud-native environments. Similar to the ETL process, it involves the extraction of raw data from data sources, but the data is then loaded into a data warehouse, and transformations are then performed in the warehouse.
Why ELT may be better than ETL
There has been a shift, with more organisations following ELT rather than ETL, due to various benefits.
- It leverages the large processing power of cloud-native warehouses such as Snowflake, Redshift and BigQuery, as it allows these systems to make transformations to data on a larger scale
- It allows for faster data availability - since raw data is loaded into the warehouse before any transformations, it's accessible for analysis more quickly than ETL where there are often delays in being able to query the data while it's still being transformed
- It leverages the processing capabilities of existing cloud data warehouses, which eliminates the need for complex ETL tools or on-premises hardware, which are often costly, allowing for significant cost savings
- It allows for more flexibility when it comes to data transformations - since the data is in the warehouse before any transformations, analysts and data engineers can transform the data iteratively, without having to reload or reprocess the entire dataset. This ensures that teams always have access to the latest data and makes it easier to adapt to evolving business needs
- It allows for a data model that supports self service and data democratisation, as teams can access and transform data without being delayed by ETL processes. This leads to greater collaboration and agility across teams
Ultimately, the best approach is often dependent on the task or organisation, and their analytical needs.