Data Architecture
ETL or ELT?
When we are interested in integrating data from multiple operational systems into one centric data warehouse, the most common method is to use ETL, However - ETL is gaining massive popularity!
He
When we are interested in integrating data from multiple operational systems into one centric data warehouse, the most common method is to use ETL. ‘E’ stands for Extracting the data from the source systems, ‘T’ stands for Transforming the extracted data to a robust analytic data model, and finally the ‘L’ stands for Loading the data model into a data warehouse. After doing that, all data in the data warehouse is ready for analytics queries, and for dashboards & reports of BI tools such as Power BI, Tableau, Pyramid, Qlik, and Quick Sight.
In recent years there has been a significant development in cloud implementations, and more and more companies understand the benefits of transferring their systems to the cloud. There is also a lot of news in the field of BI!
Before using the cloud, traditional data warehouse servers such as Oracle or SQL Server were limited in resources depending on the physical server where they were installed. In order not to harm the end users due to loads on the data warehouse server, the correct method is to perform the data transformation on dedicated servers with ETL tools such as SSIS, DataStage, Informatica, etc., and only after the transformation is finished, to load the final data to the data warehouse as the last step.
Since new options have been introduced for implementing the data warehouse with scalable tools in the cloud, such as Snowflake, Synapse, Redshift, Google Bigquery – the resource limit we had has been practically eliminated, and therefore instead of going through the traditional ETL, it is now possible to change the order of operations and perform an ELT, i.e. perform the transformation phase after loading the raw data into the data warehouse, and using the resources of the data warehouse server to perform transformations.
What exactly is the news if the bottom line is the same result? Well, there are many advantages of working in the ELT method, and here are a few of them: