Table of Contents

In a world driven by analytics, our relationship with data keeps evolving. For a long time, enterprise data referred exclusively to the kind of information that you store in a relational database. You housed this data in a warehouse, which you populated with an ETL pipeline. In recent years, businesses have come to understand that everything is data. Documents, images, voice recordings, system logs – all of it has potential value. Handling this kind of unstructured data involves repositories like Data and new processes like ELT. But how does ELT work? 

How Does ELT Work?

ELT (Extract, Load, Transform) is the cousin of ETL (Extract, Transform, Load). Both of these processes involve three steps, but with a crucial difference in the order of execution: 

  • Extract: Pull data from multiple, disparate sources, such as production databases, cloud services, and other data repositories.
  • Load: Move the data to a destination repository, such as a data warehouse (for structured data) or a data lake (for unstructured data)
  • Transform: apply transformations within the data warehouse leveraging a tool like DBT.

When using ELT, you replicate the data at the destination without any transformation. This means that the data in the data warehouse or data lake is an exact replica of the original source data. 

Pros of Using ELT

ELT is a popular method of data consolidation for several reasons: 

Flexibility

ELT can theoretically work with any kind of digital information, whether structured or unstructured. It also supports semi-structured information, such as CSV or JSON files, which ETL doesn't always support. 

Transfer Speed

When it comes to moving data from A to B, ELT is inherently faster than ETL, because ETL involves an intermediary stage. ELT takes data straight from source to destination without any stops in the middle. 

Fidelity

The output of ELT is an exact replica of the source data. ETL will generally try to clean up data, which may involve removing erroneous values like duplicates or corrupt cells. 

Destination-Side JOINs

If you're integrating raw data with repository data using ETL, you may need to import some of the data from the repository. With ELT, you simply load the raw data to the repository and run the JOIN query on that side. 

Scalability

The ELT process isn't especially resource-intensive. For enterprise, this means you can scale up quickly without worrying about slowdowns in your data pipeline. 

Cons of Using ELT

However, that's not to say that ELT is perfect. There some problems that may not arise when using ETL, such as: 

Quality Issues

When data passes through ETL, it emerges in a reliable, standardized format. If data doesn't meet standards, the ETL platform will either fix it or discard it. ELT, on the other hand, is a garbage-in-garbage-out process. Whatever you feed to ELT will end up in your data lake. 

Integration

With ELT, most data integration happens at a logical level. For example, if you want to get a 360 view of a customer, you can query all available data about the customer and see a combined view in a BI tool. However, there's no physical integration of this data, and the customer details exist across dozens of different clusters. In some scenarios, this may not be ideal.

Query Speed

Data lakes return query results with incredible speed, given the complexity of the lake's data. However, it's not as fast a relational database structure such as a data warehouse. An ETL process will cleanse and integrate raw data, producing tables that ready for business. This superior data quality leads to faster query results.

Stagnation

Data governance is hard when you're working with untransformed data, and a lake can quickly stagnate. This is a data swamp, where redundant information starts to clog the repository and slow down queries. The only way to avoid this issue is to have a strong governance policy, accurate metadata, and a clear policy about when it's okay to delete things. Without a good policy, data repositories can become unusable. 

Difficulty of Use

ELT is commonly associated with data scientists and other highly skilled users. This is because ELT can be extremely challenging. You need to understand your data pipelines, methods of integrating various sources, and how to navigate a Big Data file structure. It's not something that a general business user could do. 

Building ELT Data Pipelines with Integrate.io

Integrate.io supports ETL, ReverseETL, ELT, and CDC, arrange a 7-day demo and find out how Integrate.io can help you make the most of your data.