Data Pipeline Design Patterns

Tris / December 2023 (573 Words, 4 Minutes)

data-engineering design-pattern

Data pipeline design patterns are critical for efficiently managing and processing data within various systems. They serve as the backbone for transforming raw data into valuable insights, enabling organizations to make informed decisions. Understanding different pipeline design patterns is pivotal for optimizing data management processes and ensuring smooth operations within the data engineering realm. In this blog post, I will explore four commonly used data pipeline design patterns: ETL, ELT, CDC, and EtLT.

ETL: Extract, Transform and Load

Definition

The ETL pattern is commonly used in traditional scenarios. It is a combination of extracting data from several sources, applying transformation logic, and loading the cleaned data to the target system.
Use case

The ETL pattern is widely used when the volume of data is substantial, and complex transformations are required before landing the data at the destination.

One example is an e-commerce company collecting customer data for analytics. The data team builds an ETL pipeline to extract data, transform them by cleaning and aggregating, and finally load the data into a centralized data warehouse for reporting
Not suitable for

The ETL is not used for real-time processing. Because an ETL process often involves batch processing, which takes time and probably delays

Another scenario, when ETL is not an optimal solution, is low latency is important. In that case, delays in transformation and loading prevent ETL from satisfying the requirements.

ELT: Extract, Load and Transform

Definition

The ELT pattern is an alternative approach to the ETL pattern. It initially extracts data from source systems and loads them into the target system without transformation. When data is unified, they are transformed by using the power and flexibility of the target system’s processing capabilities.
Use case

ELT is an optimal solution to take advantage of the advanced processing capabilities of the target system.

For instance, the ELT can be used to capture user activity logs from millions of users in near real-time. Raw logs are extracted and then loaded into a distributed storage system. Later on, complex transformations are applied by the platform’s processing capabilities
Not suitable for

The ELT pattern might not be your solution if your data transformation logic is resource-intensive and highly complex. Because some data warehouse systems may lack transformation capabilities. If it does, the cost is quite expensive.

CDC: Change Data Capture

Definition

It is a pattern which is used to capture all event changes made to a source system in real time. Generally, it identifies and captures the incremental changes made to the source, and then keeps the destination system synchronized with the source system
Use case

CDC pattern is not a bad choice when near real-time data synchronization is required.

For instance, a reporting system in a bank is required to be up to date with the latest transaction. The data team can implement CDC to capture and propagate the changes made to the transaction records in real time, ensuring that the reporting systems always have the most recent data.
Not suitable for

However, If your data rarely changes or if capturing changes at a granular level is not critical for your analysis, implementing a full Change Data Capture system might introduce unnecessary complexity

Moreover, for small datasets where the overhead of tracking changes outweighs the benefits, implementing a Change Data Capture mechanism might be overkill.

EtLT: Extract, transform, Load, Transform

Definition

The EtLT (Extract, transform, Load, Transform) pattern is an extended version of the traditional ETL pattern. It involves extracting data from various sources, applying transformations, loading it into a target system, and then performing additional transformations on the loaded data.
Use case

EtLT is useful when complex transformations need to be performed both before and after loading the data into the target system.
Not suitable for

If the transformation steps before and after loading data into the target system are redundant or do not add significant value, using EtLT might introduce unnecessary complexity without providing tangible benefits.

If the transformations are resource-intensive and the target system is not well-equipped to handle them efficiently, performing transformations both before and after loading might strain resources.

References

IT k Funde. (2022, January 12). *What are some common data pipeline design patterns? What is a DAG?

ETL vs ELT vs CDC (2022)*. YouTube. Retrieved December 3, 2023, from https://www.youtube.com/watch?v=v67JHa4MrnQ