What is ETL?

In the first step of ETL, the focus is on understanding where useful original data is stored, and what form or format it is in. This data can be in different formats and structures, including databases, files, applications, and real-time data streams. Then, processes are implemented to access it, through recurring batch processes, in real-time or triggered by specific events or actions.

This step involves:

• Collecting data from structured sources like relational databases, and unstructured sources such as text files and log data.
• Accessing data from transactional databases and data streams to ensure comprehensive data collection.
• Ensuring that the extracted data includes all necessary information for further processing (data analysis and integration).

In this step, the original data is cleaned, restructured, and aggregated to ensure it is in the correct format for storage in a data warehouse or other sources. This transformation process prepares the data for analysis, reporting, and use by various business applications.

Some of the key transformation processes include:

• Deriving calculated values: Creating new data points based on raw data, such as calculating totals or averages.
• Reordering or transposing the data: Changing the order or structure of data to fit the required format.
• Adding metadata or associating key-value pairs: Enriching the data with additional information to provide more context.
• Removing repetitive data or adding counts of occurrences of data: Identifying and eliminating duplicates to ensure data quality.
• Encoding or decoding data: Converting data formats to meet the destination system's requirements.
• Validating the data: Ensuring data accuracy and completeness through checks and validations.
• Performing search and replace: Modifying data values to standardize or correct entries.
• Changing the schema: Transforming data structures, such as converting text fields to numerical IDs or values.

In this step, the transformed data is stored in one or more locations where it can be accessed by applications, reporting tools, and other business processes. This storage can include unstructured object stores, text files, or complex data warehouses.

The loading process varies widely depending on the nature of the business requirements and the applications and users the data serves.

Some of the key considerations in this step include:

• Destination variety: Data can be stored in various types of repositories, including relational databases, data warehouses, data lakes, and cloud storage solutions.
• Access requirements: Data is accessible to the necessary applications and users for analytics, reporting, and other business processes.
• Performance optimization: Loading data in a way that maximizes query performance and minimizes latency.
• Data integrity: Ensuring the data remains accurate and consistent during the loading process.
• Scalability: Implementing processes that can handle growing data volumes efficiently.

Reverse ETL (rETL), also known as “Operational Analytics,” reverses the traditional ETL flow by sending cleaned and transformed data from a data warehouse into a business application. This allows organizations to access analytical data in an operational context, enabling better data-driven decisions.

ELT (Extract, Load, and Transform) is a variation of ETL where data is extracted from one or more sources, loaded into a destination database, and then transformed in place using the destination’s processing capabilities. This process is used to quickly load and integrate data from multiple sources without the need for extensive preprocessing.

The main difference between ETL and ELT is the order of data transformation and loading steps. ETL transforms data before loading, while ELT performs the transformation after loading the data into the destination.

EtLT is a sub-variation of ELT where data undergoes some basic transformations between the Extract and Load stages. However, major transformations supporting business logic or data modeling are reserved for the final transform stage. This approach balances initial data processing with the comprehensive transformations required for end-use.

• Permissions: Do your networks and systems have access and rights to the data?
• Data freshness: Are you capturing real-time data, or stale data that's no longer of value? What is the ephemeral nature of data? Are you able to capture it before the data passes its lifetime?
• Data quality and integrity: Do you have validation in place to ensure the extracted data is in an expected form? Combining data from multiple sources can be challenging due to differences in data formats, structures, and definitions. It can be difficult to identify and correct errors or inconsistencies in the data. Reasoning about the lineage of data (i.e., what the original source was) can be difficult once data sources are integrated.
• Availability and scale: Is there enough storage and computing power in your staging area to keep up with the data? (The more data that needs to be transformed, the more computationally and storage intensive it can become.)
• Filtering: Which data is important data and which can be ignored or discarded?
• Security and privacy: ETL processes often involve sensitive or confidential data. Organizations want to ensure that data is protected and accessed only by authorized users, with increased complexity when dealing with large volumes of data or data from multiple sources.
• Data governance and compliance: Data governance policies and regulations stipulate how data provenance, privacy, and security are to be maintained. Additional complexity arises from the integration of complex data sets or data subject to multiple disparate regulations.
• Data transformation and cleansing: ETL processes often require significant data transformation and cleansing in order to prepare data for analysis or integration with other systems.
• Integration with other systems: ETL processes often involve integrating data with other systems, such as data warehouses, analytics platforms, or business intelligence tools. This can be challenging, particularly when dealing with complex data structures or integration requirements.