ETL to ELT journey: Break free your Transformations and discover Happiness and Data Zen

-

Every data integration pipeline consists of 3 stages: Data Extraction (E), Data Transformation (T) and Data Load (L)

During the Data Extraction stage, the source data is read from its origins: transactional databases, CRM or ERP systems or through data scraping from web pages.

During the Data Transformation stage, the necessary modifications are applied to the source data. This includes data filtering, enrichment or merging with existing or other source datasets, data obfuscation, dataset structure alignment or validation, fields renaming and data structuring, according to the canonical data warehouse model. 

During the Data Loading stage, the data is stored in the pipeline destination, which could be a staging area, data lake or data warehouse.

There are two principal methods for the data integration process: transferring it from where it originated to the destination, where the data will be used for analysis, ETL and ELT.

The difference between ETL and ELT pipelines lies in whether the Data Transformation stage occurs before or after the Data Loading stage. 

Let's look closer, at why this difference is significant.

ETL pipeline: data is transformed within a data integration tool and then loaded into the data storage or management solution.

Transformations coupling & data latency: During the ETL pipeline, multiple data transformations would usually be tightly linked and executed sequentially. Failure of one of them would disrupt other transformations, whether by falling the whole data flow or making monitoring the stage's success harder. Downstream data transformation availability will depend on upstream transformation execution times and delays.

Maintainability: ETL pipelines will usually have complex logic that contains multiple transformation stages making them difficult to maintain, understand or apply changes.

Ability to scale:  The ETL pipeline can be scaled as a whole regardless of the different needs of each transformation operation.

Data processing efficiency: The data transformations will be performed using the data interaction tool's ability to process the data, which would usually be iterative and not performed as a set operation, using the limited compute power of the data integration tool.

Raw data availability: If the data transformation stage is applied before data is loaded from the source system and transformation operations take time, it will take time till the raw data is available for analysis.


ELT pipeline: data is first loaded into the data storage solution and then transformed within a data store.

Transformations decoupling & data latency:  When the data is being loaded into the data warehouse independently and as a first step, this operation can have an independent schedule from the downstream transformation operations, making the raw data available for analysis with minimal latency. Subsequent data transformation stages can operate as a separate pipeline, removing data pipeline complexity and enhancing flexibility. Each transformation can run individually with its own schedule and be scaled independently. Some of them will run very frequently allowing data analysts and data scientists fast access to aggregated data without dependency on additional heavy transformations that require more time and resources to be completed.

Maintainability: ELT pipelines are typically less complex than ETL pipelines, easier to maintain and adapt to frequently changing requirements.

Ability to scale: Each transformation stage can use as many resources as it needs, balancing the pipeline cost, execution time and performance. Each pipeline can be perfectly aligned to its unique requirements and goals.

Data processing efficiency:  Transformation data processing will be performed inside the data warehouse, it will use the warehouse resources as well as various data warehouse features that enable fast data processing, such as data clustering, indexes or intelligent query optimizers.

Raw data availability: ELT pipelines load data in its raw form and make it available for analysis with very low latency, removing any impact from heavy data transformations.

Regardless, of whether we use cloud data integration platforms, like Azure Data Factory, or on-premice data integration tools, requiring dedicated hardware or virtual machine, optimizing for lower latency and faster data processing, it makes sense to separate the Extract and Load stages from Data Transformation stage, allowing different data transformation scenarios. 

Comments

Post a Comment

Popular posts from this blog

SQL Awesomeness: Finding a QUALIFY query clause in the depths of the database ocean

-
Image
After so many years of writing SQL queries, I cannot believe I never came across a QUALIFY query clause. It feels like discovering a treasure in the depths of the database ocean. Even ChatGPT was not aware of it! QUALIFY query clause was invented by Teradata and is not a part of SQL Standart but apparently, multiple database vendors are supporting it, for instance, Snowflake and Databricks. QUALIFY clause solves the challenge when applying a window function as a query filter. SQL Standart and most database vendors will not allow to run of a window function as a part of a WHERE clause because window functions get evaluated after the HAVING clause. We typically create a CTE or subquery adding a window function to be able to filter on a it at a later query stage. Here is an example of how you can use QUALIFY to piece-of-cake filter on the window function.  I will use the Snowflake database, as an example. Let us consider that we have a list of queries and we need to find the last query ex
Read more

Look back and realize how far you came

-
Image
It appeared out of nowhere while I was having a strong argument with the ton of dust that had taken over my computer room. My former favourite book, "Advanced Transact-SQL for SQL server 2000" brought an upset look to my face, the one I usually get from our NetApp admin. He usually gives me such a look when I ask him for more disk space. At the time of SQL 2000 there were far less good SQL books available than today and amazing SQL programming tricks were living on Itzik’s book pages; and, similar to the Rubeus Hagrid’s “The Monster Book of Monsters” it attacked anyone who attempted to open it, unleashing wild and exciting brilliant ideas. Nowadays there are a lot of SQL books but not all of them are worth reading. Some of them should be strongly avoided and if you touch them you should immediately wash your hands and have a drink or three. A few month ago I was asked to technically review some new SQL Server book. Reading their missleading claims of how the optim
Read more

The Greatest Reasons to use or not to use a Centralized Data Access Architecture

-
Image
When developing a modern Data Platform Layer, one of the main decisions is whether to opt for centralized or decentralized data access architecture.  There is no “one-fits-all” solution, both have advantages and disadvantages. A Centralized Data Access Architecture would usually mean duplicating data from the operational layer into the analytical layer and applying various transformations to data to support and speed up data analytics. Operational online transaction processing Layer , where all microservices and their operational databases are located. Analytical Data Layer , where we would have data lakes that support data Scientists' work and a data warehouse, that supports Business Intelligence. Transformations, ETL or ELT data pipelines , which are moving data from the operational layer into the analytical layer. I f we opt for a Centralized Data Access architecture, what would be the benefits and the drawbacks? CONSISTENCY : consolidating data into a central location can
Read more