Cloud turns data transformation on its head
The traditional data transformation procedure of extract, transform and load (ETL) is rapidly being turned on its head in a modern twist enabled by cloud technologies.
The Cloud’s lower costs, its flexibility and scalability, and the huge processing capability of cloud data warehouses, have driven a major change: the ability to load all data into the cloud, before transforming it. This trend means that ETL itself has been transformed—into extract, load and transform, or ELT.
ELT offers several advantages, including retention of data granularity, reduced need for expensive software engineers and significantly reduced project turnaround times.
Data is vital for organizations, who use it to understand their customers, identify new opportunities and support decision-makers with mission-critical and up-to-date information. However, to analyze data, it must first be structured. It needs to be understood so that it can be pulled into dashboards, reports and predictive models.
The problem is that raw data doesn’t present as beautifully formatted, usable information. That is where data transformation comes in. Messy raw data needs to be transformed into representations of reality that help users accomplish specific goals.
This transformation can take place either before the data is loaded to its destination, usually a data warehouse, or afterwards
In traditional ETL, data is transformed into analysis-ready data models before it is loaded. As Charles Wang of Fivetran notes, “combining transformation with loading into the same step can preserve storage and compute resources, but introduces a great deal of brittleness into the data engineering workflow. This also means that the software used for transformations is typically written using scripting languages such as Python and Java. In addition, transformations in ETL may require a great deal of complex orchestration using tools such as Airflow.”
ETL often also entails a great deal of customized code. One of the main challenges of traditional ETL is therefore accessibility. Scarce, costly resources such as engineers and data scientists need to be involved.
Another issue concerns turnaround times. Traditional ETL procedures associated with on-premise data warehouses are usually extremely time-consuming. Using ETL also involves constant maintenance and can introduce complexity.
Storage has traditionally been prohibitively expensive. The benefit of ETL for organizations was that they did not have to load all their data to the final destination. That has now been changed by cloud technologies. We are seeing a massive increase in cloud adoption in South Africa and the costs of technology are decreasing significantly. Lower costs make it possible for organizations to load all their data to the cloud, without having to be as mindful of storage costs.
This means that in the modern ELT workflow, raw data is transformed into analysis-ready data models after it has been loaded. Once in the warehouse, data can be transformed using SQL, which, thanks to its intuitive English-based syntax, can be used by a far wider range of users.
