Clearly, data is everywhere, and the ability to accurately capture, format, and analyze it to produce new information is a deciding factor in business success. Data-driven decision making guides organizations, and contextual data reveals new trends and patterns in the marketplace to identify opportunities to innovate.
However, just as the volume of data has ballooned, so too has the sources and formats of data. Data can be available from various assets and systems in structured or unstructured formats, including emails, documents, videos, images, legacy databases, websites, point of sale systems, and more. The location of this data can change, too, from internal assets to third-party sources.
Organizations turn to two data integration strategies to bring all this disparate information together into one single source of truth: ETL and ELT. Though they are related and achieve the same result, the methods by which they consolidate data is the key distinction.
Before diving into the differences, let's define each method, starting with ETL.
The extract, transform, load (ETL) process is one method that organizations use to collect data, reformat it, and store it. The data is copied from its origin during the extract phase, cleansed and structured in a staging area during the transform phase, and then moved into the data warehouse during the load phase.
ETL is a linear workflow that pairs well with relational data warehouses since they require data transformations to enforce strict schema and data quality before loading to the datastore. ELT, on the other hand, pairs best with data lakes that accept structured or unstructured data — as discussed in the next section.
This video provides a second look at the ETL process:
Extract, load, transform (ELT) is a newer method for achieving the integration of data from across an organization and preventing data silos. Data is extracted from its origin, loaded into the datastore, and transformed "at rest." Transformation will typically happen on an as-needed basis versus ETL where all data is transformed before it is stored.
The reason that ELT can switch the order of phases is that the data is typically stored in a data lake, which accepts raw data no matter its structure or format. This allows for instant loading once the data is captured and later transformation for analysis.
This video breaks down ELT after examining the traditional ETL model:
The primary differences between ETL and ELT are the order through which they accomplish data integration and the circumstances that allow both methods to be effective.
ETL is best used for on-premise data that needs to be structured before uploading it to a relational data warehouse. This method is typically implemented when datasets are small and there are clear metrics that a business values because large datasets will require more time to process and parameters are ideally defined before the transformation phase.
ELT is best suited for large volumes of data and implemented in cloud environments where the large storage and computing power available enables the data lake to quickly store and transform data as needed. ELT is also more flexible when it comes to the format of data but will require more time to process data for queries since that step only happens as needed versus ETL where the data is instantly queryable after loading.
This video examines ETL versus ELT using the metaphor of a pizza delivery business:
The advantages and disadvantages of these approaches will be explored next.
There is no clear winner in the ETL versus ELT debate. Both data management methods have pros and cons, which will be reviewed in the following sections.
Once the data is structured and transformed with ETL, data queries are much more efficient than unstructured data, which leads to faster analysis.
ETL can be implemented in either on-premise or cloud-based environments. Organizations will often use ETL to take data from on-premise systems and load it to a cloud datastore.
ETL transforms data before it reaches its destination. When companies are subject to data privacy regulations such as GDPR, ETL allows them to remove, mask, or encrypt sensitive data before it's loaded to the data warehouse to ensure compliance.
ETL was developed first and has been in practice for more than two decades. This means that there are more engineers with experience in ETL implementations and more ETL tools in the marketplace to build data pipelines within organizations.
Because data must be transformed in a staging area before it's loaded, it is not available as quickly in the datastore as opposed to ELT where data is loaded as soon as it's extracted.
If the structure of data in the warehouse does not support new queries or analyses (that are determined to be valuable), then the transformation process and schema of the data warehouse may need to be modified.
ETL is not ideal for handling large volumes of data given the time needed for transformation. Instead, it's best suited for smaller data sets that require in-depth manipulation and are known to have data relevant for analysis.
When paired with a data lake, ELT can ingest data in any format. There is no need to account for structures or schema since the data lake accepts unstructured data.
In an ELT model, transformation typically happens only when analysis is needed versus transforming all data before it's loaded, which means greater efficiency of resources.
WIth ELT, all data is loaded to the data lake, so it's always available. This allows tools that don't require structured data to interact with the loaded data immediately instead of waiting until it's transformed.
Because transformation happens "at rest," data is loaded to the data lake as soon as it's available, providing immediate access to information.
Because transformation is performed as needed, the ELT model can be applied to new sources of data to quickly capture the information in the data lake while engineers determine the best ways to query and analyze the data.
Regulations may prohibit companies from storing sensitive data, even if that information is removed in a later transformation. ELT's integration with the cloud may be a second issue since some regulations prohibit storing information on servers outside of a specific region or country's borders.
ELT has come of age as cloud computing has matured, which means it does not have as wide of a community behind it. The number of tools and professionals that support ELT is increasing, however.
Though theoretically possible in an on-premise environment, the true advantages of ELT are only possible when paired with the storage and processing power of the cloud.
Since transformation only happens after the data has been loaded and analysis is required, this may slow down the time to insight for inspecting large volumes of unstructured data. However, the computing power available in the cloud can mitigate this.
This video offers another look at the key differences between ETL and ELT:
Both the ETL and ELT methods will improve data quality and integrity. ETL's greatest strength is its structuring of data for more in-depth analysis and examination, and ELT's greatest strength is its speed and support for diverse data types.
The key is to evaluate both strategies on their merits and drawbacks and choose the best solution to fit your organization's data management needs and practices. Either will improve visibility across the enterprise and eliminate pesky data silos.