ETL vs ELT: A Detailed Guide

ETL and ELT are two distinct approaches to data transformation. What’s the difference between ETL vs ELT, and which approach should you use for your data?

In this detailed guide, you’ll learn more about:

• ETL vs ELT as well as the newer kid on the block—streaming ETL.
• The pros and cons of ETL vs ELT.
• Misconceptions about ETL vs ELT.
• When to use ETL vs ELT.

ETL vs ELT at a Glance

• ETL (Extract—Transform—Load) refers to when data transformation happens before data is loaded to storage. Data is first extracted from a source, then transformed as needed, and finally loaded into the storage destination.
• The term streaming ETL is also becoming more common. Streaming ETL uses real-time streaming instead of batch for data ingestion.
• ELT (Extract—Load—Transform) refers to when the transformation happens after data is loaded to storage. In this case, data is extracted from a source, loaded into a storage solution, and only then is transformed.

What Is ETL?

ETL stands for Extract—Transform—Load. As the order of terms suggests, data is first extracted from a source, then transformed, and finally loaded into storage. The next image shows a simple ETL pipeline.

ETL has been around since the 1970s and has traditionally used batch for transferring data. With batched data, a group of data is transformed and sent to the database all at once, often through a scheduled job. Enterprises often use external tools for ETL, and the target destination can be a data warehouse, data lake, or even a data lakehouse.

There is a misconception that ETL is slow, typically for legacy systems, and difficult to maintain. This is due in large part to the fact that many legacy systems do use ETL and require maintenance and refactoring.

But there are cutting-edge real-time ETL pipelines, and the term streaming ETL is often used to distinguish them from traditional ETL.

What Is Streaming ETL?

In recent years, streaming ETL has become increasingly common for real-time applications. Streaming ETL is distinctly different from traditional, slower batch methods for transforming data. Streaming ETL transfers and transforms data in real time, making it much more performant and complex than traditional ETL.

Note that the term extract often isn’t accurate for many streaming ETL pipelines. Extraction suggests that data is pulled from data sources, such as through an API call to the data source. However, many streaming ETL pipelines involve pushing data further down the pipeline. For example, Hydrolix includes a streaming API for receiving data from external sources.

Some kinds of data, especially log data, work best with streaming ETL. Log data is required for real-time analytics use cases, and it’s most useful when it’s standardized and enriched before storage.

What Is ELT?

ELT stands for Extract—Load—Transform. In contrast to ETL, with ELT, transformation happens after data is already in storage. With ELT, raw data is first moved into storage and then transformed. The next illustrates a basic ELT pipeline.

In this image, data transformation occurs within storage. The transformation process typically uses the compute power of the data lake or warehouse. While this graphic shows data remaining in the same storage after transformation, it can also be moved to another destination such as a data warehouse after ELT is complete.

ELT has become popular in recent years with the rise of flexible, cost-effective cloud storage. Prior to cloud storage, it was typically too expensive to store large volumes of raw data. As cloud storage has grown cheaper, it has become more cost-effective to keep large volumes of raw data and then transform it later. And cloud data platforms such as Snowflake use the computational power of the database to perform ELT transforms.

The raw data can be transformed in different ways depending on the needs of various stakeholders and teams.

ETL vs ELT Key Differences

Pros and Cons of ETL and Streaming ETL

Pros of ETL and Streaming ETL

Let’s take a look at the pros of ETL. Because streaming ETL is a type of ETL and has many of the same pros, its benefits are also included here.

• Privacy and compliance: You can mask sensitive data before storage.
• Standardization: Standardizing data ahead of time can ensure it’s consistent and make it more performant for querying use cases such as real-time analytics.
• Retain context: Sometimes data has additional context that must be added before storage. Otherwise, that context is lost. For example, log data often doesn’t include information about its source. After all, while that log data remains in the source, that information would be redundant. But once the data is sent elsewhere, that context is lost unless you add it before the data is sent to its new destination. An enrichment process can retain any necessary context.
• Optimize for real-time analytics: For log data use cases requiring real-time analytics, streaming ETL can optimize data for low-latency querying.
• Often required for data warehouses: Data warehouses typically require data that’s been standardized and cleaned up before storage.
• Compress data for cost savings and faster cloud storage: ETL pipelines can compress data, reducing cloud storage costs. It can also make storing data faster because it reduces the amount of data transferred through HTTP for distributed storage.
• Best for log data: Because streaming ETL can standardize and enrich log data for real-time analytics, streaming ETL is better for log data than ELT.

Cons of ETL and Streaming ETL

• Additional maintenance of data pipelines: ETL pipelines can become brittle as data sources and storage solutions change over time. In the case of data sources, fields can change, connectors may need updates, and sources that generate logs can have outages. In the case of storage solutions, schema and stakeholder needs can evolve over time. All of these changes require additional maintenance.
• Scalability issues: Many ETL tools can’t successfully scale up for large volumes of data. Scale can also introduce more complexity and maintenance.
• Increased latency: Even streaming ETL designed for real-time use cases adds a small amount of latency during the ingest process. And traditional batch ETL jobs are usually scheduled, leaving teams waiting hours or even days for actionable data.

Pros and Cons of ELT

Pros of ELT

• No additional ingest latency for transformation: Because there’s no transform process, there’s no additional latency when data is written to storage.
• Increased flexibility: You can choose to transform some or all of the data, transform it in different ways, and even transform multiple times.
• Keep unaltered, raw data: You can keep the raw, unaltered data as a single source of truth.

Cons of ELT

• Potential compliance/security issues: Because there is no masking or obfuscation before storage, raw data can potentially include PII (personally identifiable information) and other data covered by compliance and privacy laws.
• Raw data takes more bandwidth and storage than compressed data: Raw, uncompressed data has a much larger footprint than transformed, compressed data. For systems using distributed storage, uncompressed data can slow down the ingest process. And the raw data also has a larger footprint in cloud storage, leading to higher storage costs.
• Context gained at ingest time will be lost: If incoming data has additional context that needs to be added (such as its source), it will be lost when it’s stored. The data needed for enrichment will no longer be available for ELT.
• Potential for data swamps: Overdependence on ELT can lead to data lakes with large volumes of unstructured data because there is no requirement to transform data before storage(or to transform it at all).

Misconceptions Around ETL and ELT

There are many misconceptions around ETL and ELT. Let’s take a look at a few of them.

ETL Misconceptions

When it comes to ETL, many of these misconceptions are based on outdated views of batch ETL as opposed to newer streaming ETL methods. And there’s the misconception that ELT is more modern, cost-effective, faster, and scalable.

It’s true that traditional batch ETL is slower and not suitable for use cases like real-time analytics. However, streaming ETL is a modern approach that allows transformations to happen in real time with minimal impact on ingest speeds. With the right tools, this approach can extremely scalable, cost-effective, and performant.

The takeaway for ETL: Don’t confuse modern streaming ETL with traditional batch ETL. Unlike traditional batch methods, streaming ETL is extremely fast and effective for real-time use cases.

ELT Misconceptions

The biggest misconception around ELT is that cloud storage makes it cost-effective and easy to use regardless of scale. While it’s true that affordable cloud storage made ELT possible, it doesn’t account for the fact that the volume of data is continuing to go up for most enterprises. 

Data lakes can turn into data swamps if data isn’t properly managed. Worse, the cost of raw data can add up if you are dealing with petabytes of data. And ELT jobs can be expensive, time-consuming, and compute-intensive across many terabytes of data.

The takeaway for ELT: ELT isn’t a magic bullet for managing high-volume data lakes. You can still end up with data swamps if you’re not careful.

Can ETL and ELT Be Used Together?

ETL and ELT aren’t mutually exclusive. Many enterprises use cloud-based data lakes for storage, and data lakes can hold both structured and unstructured data. And many cloud providers offer both ETL and ELT—AWS is just one example.

Some data sources such as log data may use ETL for standardizing and enriching data. Other sources may not need transformation until after storage (or may never undergo transformation at all).

Enterprises can also apply both ETL and ELT to the same data. For example, it’s common to apply streaming ETL to log data. Once the log data is in storage, it can then be processed and transformed for other business use cases.

Which is Better: ETL vs ELT

Whether you use ETL or ELT depends on the data source and the use case. As mentioned in the previous section, data lakes and data warehouses can use both ETL and ELT. For example, an ETL process might obfuscate PII and compress data while an ELT process can do further data cleaning and shape data for specific teams.

Here are a few specific use cases where you should choose one over the other.

When to Use ETL

• Real-time analytics: Real-time analytics involves working with log data for use cases such as observability and threat detection. Platforms such as Splunk, Elastic Cloud (using Logstash), and Hydrolix all use streaming ETL to transform log data for real-time analytics use cases. Traditional batch ETL doesn’t work for this use case.
• Log data: In general, it’s a best practice to standardize log data even if it’s not being used for real-time analytics. Standardization makes log data easier to understand, query, and reason about. If there’s not an immediate need to analyze the data, you can use either streaming ETL or batch ETL to transform it.
• High-volume data: It’s a misconception that ETL is better for smaller datasets while ELT is better for large volumes of data. Even with cost-effective cloud storage, high volumes of data can quickly lead to high costs for storage and data analysis and result in a data lake becoming a data swamp. A basic requirement for incoming data (especially data transmitted through HTTP) is compression, which lowers both throughput requirements for writing data to storage as well as storage costs.

When to Use ELT

• Source of truth: Sometimes, you’ll want to keep the raw dataset as a source of truth. You can then create transformed datasets, visualizations, and more with different slices of the data using ELT.
• Data requiring additional flexibility: In some scenarios, you may not know the shape that incoming data will take, and the data may have inconsistencies or issues that you can’t model with ETL. Or you may simply not know how you’ll want to model the data yet. In this case, you may want to store the data first and use ELT later.
• Data that needs additional cleaning and processing: As stated previously, ETL and ELT aren’t mutually exclusive. There are many reasons that you might want to perform multiple transformations on data that include both ETL and ELT. For example, you may have new schema to consider or different KPIs that require transforming your data to fit new goals.

Next Steps

• Learn more about best practices for data transformation.
• Learn about streaming real-time data at scale and the benefits of transforming data before storage.
• Learn more about Hydrolix, a platform for ingesting, transforming, storing, and analyzing high volumes of log data. Hydrolix uses streaming ETL for data transformation.