IT Brief Australia logo
Technology news for Australia's largest enterprises
Story image

ETL vs ELT — What, when and why

By Contributor
Tue 29 Jun 2021

Article by SnapLogic CTO Craig Stewart.

There’s been a lot of discussion in tech industry circles recently about ETL (extract, transform, load) and ELT (extract, load, transform). 

These terms and the misunderstandings about which method are not new. However, with a renewed emphasis on collecting and using business data in the cloud to streamline processes and make better decisions, it only makes sense that the discussion has been revived.

What comes first — the T or the L?

ETL and ELT are very closely related, and there’s no right or wrong answer to which method organisations should be using. But to understand which method is a better fit, it’s important to understand what it means when one letter comes before the other. Let’s look at the common definitions of each:

  • ETL — Extract, Transform, and Load: With this approach, organisations pull data from one or more sources, but before loading it into their data warehouse or data lake, the next step is to cleanse the data for use. This entails reviewing the data, putting it into the correct categorisations and formats, and ensuring it will line up with existing target databases. The data is then loaded into the target system.
  • ELT — Extract, Load, and Transform: This method takes data from one or multiple remote data sources and loads it into a staging area in the data warehouse without looking at or changing any of the data beforehand. Once the data is loaded, organisations would then cleanse and transform the data into the specific target formats after the load, to make it usable by particular programs and team members. 

Both are similar, and have the same end goal — the usage of data to improve the way organisations work. That said, the major difference between the two — when to cleanse and transform incoming data — needs to be looked at a bit more in detail, as the organisation’s resources to handle each approach will help to dictate which is better.

When to transform data

Organisations can start by looking at the types of data they will be working with. In many cases, reviewing the following variables will inform the decision.

  • Data format: If data is unstructured, it does not neatly fit into a relational structure — which most of the analytics tools will work from. In that case, organisations will need to use the ETL approach to re-shape the data to work with a relational format, which the end-user data consumers can then utilise. If the data is already in a relational form, then it can be loaded directly ELT-style into the target, and then potentially massaged into the target form, along with any sort/aggregate/joins and cleansing operations.
  • Data size: Datasets come in many different shapes and sizes. This variable influences the ETL vs ELT decision as well. For large datasets, ELT is used so a large amount of data can be processed and transformed simultaneously. Improvements in the speed and power of processing have also made it possible for large datasets to be handled as one unit. Smaller amounts of data are often connected to the ETL approach. 
  • Cost: Alongside the above, a critically important consideration is how expensive working with data can be. Often, the data has already been landed in the target system, and the ask is to cleanse/format/aggregate the data. If there is only an ETL tool to do that, teams must physically move those bits out of the database, then into an external system to do that processing, only to then move the resulting data back into the database or warehouse. Rather, if the organisation has ‘limitless processing’ on demand in a cloud data warehouse, it may be beneficial to use the massive parallel processing capability to do what’s necessary to the data without moving it around. This is a much more efficient process, with far fewer moving parts to coordinate as well.
  • Data source: The source of the data comes into play here as well. What type of application or data source is it coming from? Does that source easily connect to what the organisation is using, or will there be a great deal of transformation work to ensure the data is usable? Is it coming from an on-premises store or the cloud? Another consideration here is that the ELT approach will use set-wise operations on the data, which are inherently very ‘batchy’. This may be fine — and even well-suited — for larger volumes of data, but is simply not a good fit if the data is more akin to streaming or messaging. In this case, the ETL style will almost invariably be the better approach.
  • Data destination: Closely tied to the source is the question of the destination of the data within the organisation. Does the data source easily connect to it? Are they from different companies with limited connective tissue? Will the data come from one product need to look completely different from being used by the data warehouse solution? ETL is often the preferred method for data with a different source and destination product; while for data that is going from apples to apples, ELT is often used.
  • Intensity: This one is more subjective, but the idea is to look at just how much work the data transformation will take to become useful for the team’s analysis and decision-making needs. Size comes into play here as well. If the transformation process is less complex, then ELT may be the right choice. If the transformations are more complex, then many organisations choose ETL, so a little is done at a time instead of all at once.

The data transformation takes place in different places depending on the method chosen. In ETL, there’s an in-between stage before the data makes it to the warehouse where the transformations are done. 

In ELT, the data warehouse does the transformation. ELT only requires raw data from the database to work and requires a great deal more power and overhead to store and transform the data. This, in turn, allows for a shorter time between extracting and using the data, and provides the option for a greater deal of customisation. Because of this, business teams can now quickly build their own data pipelines and immediately see insights that can change the business.

Pick technology tools wisely

There are many variables to consider when deciding to move data into a database with either the ETL or ELT method. For some, the variables may make the choice more of a non-choice. 

However, at different points throughout an organisation’s journey, one approach may be favoured over the other. Many tools on the market can help teams either transform data before loading, or load data before transforming. 

Some solutions can make it easier for organisations to select either approach depending on the types of data and organisational requirements at the specific moment in time, instead of forcing them to choose an ETL or ELT approach forever.

The critical thing to remember is that an approach may, and should, shift over time — so invest wisely in tools that can adapt as your approach does.

Related stories
Top stories
Story image
Tech job moves - Forcepoint, Malwarebytes, SolarWinds & VMware
We round up all job appointments from May 13-20, 2022, in one place to keep you updated with the latest from across the tech industries.
Story image
Let’s clear the cloud visibility haze with app awareness
Increasingly, organisations are heading for the cloud, initiating new born-in-the-cloud architectures and migrating existing applications via ‘lift and shift’ or refactoring.
Story image
Remote Working
Successful digital transformation in the hybrid work era is about embracing shifting goalposts
As organisations embraced remote working, many discovered they lacked the infrastructure needed to support history’s first global load test of remote work capabilities.
Story image
Vectra AI
Understanding the weight on security leader’s shoulders, and how to shift it
Millions of dollars of government funding and internal budgets are being funnelled into cybersecurity to build resilience against sophisticated threats, indicating how serious this issue has become.
Story image
Nutanix study reveals financial services sector lagging with multicloud adoption
Nutanix has released new research that reveals the financial services sector is lagging behind when it comes to multicloud adoption.
Story image
A third of companies paying ransom don’t recover data - report
Veeam's report finds 76% of businesses who are victims of cyberattacks paid the ransom to recover data, but a third were still unable to get their information back.
Story image
New vulnerabilities found in Nuspire’s Q1 2022 Threat Report
“Threat actors are quickly adjusting their tactics and these exploits tend to get industry attention, but the threat posed by older and attacks still persists."
Story image
New Relic enters multi-year partnership with Microsoft Azure
New Relic has announced a strategic partnership with Microsoft to help enterprises accelerate cloud migration and multi-cloud initiatives. 
Story image
Apple previews new features for users with disabilities
Apple says new software features that offer users with disabilities new tools for navigation, health and communication, are set to come out later this year.
Story image
Grasping the opportunity to rethink the metrics of a sustainable data centre
A data centre traditionally has two distinct operations teams: the Facility Operations team, and the IT Operations team. Collaboration between them is the key to defining, measuring, and delivering long-term efficiency and sustainability improvements.
Story image
Digital Transformation
Pluralsight and Ingram Micro Cloud team up on cloud initiative
Pluralsight has teamed with Ingram Micro Cloud to build upon cloud competence and maturity internally, and externally support partners’ capabilities.
Story image
HINDSITE wins Aerospace Xelerated Pitch Challenge with solution to support Boeing
Brisbane-based startup HINDSITE was the winner of the first ever Pitch Challenge organised by Aerospace Xelerated in partnership with Queensland XR Hub. 
Story image
Telstra enters into new RSP agreement with Opticomm
Telstra has entered into an RSP agreement with Opticomm (A Uniti Group Limited subsidiary) to provide network fibre services to customers.
Story image
Zendesk announces new conversational CRM solutions
“The last few years have made it obvious that digital is the front door, convenience is paramount and relationships are anchored in conversations."
Story image
Data solutions
South Australia state satellite makes significant progress
South Australia’s first state satellite has successfully completed the Critical Design Review (CDR), moving it closer to providing tangible data solutions.
Story image
Data and analytics could be key to higher selling prices in APAC
Sisense's latest report has found that almost half of data professionals in APAC think customised data and analytics can create better selling prices for their products.
Story image
Cloud Security
Aqua Security createa unified scanner for cloud native security
“By integrating more cloud native scanning targets into Trivy, such as Kubernetes, we are simplifying cloud native security."
Story image
More than 40% of banks worried about cloud security - report
Publicis Sapient's new report finds security and the lack of cloud skills and internal understanding of business benefits are big obstacles for banks moving to the cloud.
For every 10PB of storage run on HyperDrive vs. comparable alternatives, an estimated 6,656 tonnes of CO₂ are saved by reduced energy consumption alone over its lifespan. That’s the equivalent of taking nearly 1,500 cars off the road for a year.
Link image
Story image
Data backup plans inadequate, data still at risk - study
The Apricorn 2022 Global IT Security Survey revealed that while the majority organisations have data backup plans in place, data for many are at risk.
Story image
Application Security
What are the DDoS attack trend predictions for 2022?
Mitigation and recovery are vital to ensuring brand reputation remains solid in the face of a Distributed Denial of Service (DDoS) attack and that business growth and innovation can continue.
Threat actors are exploiting weaknesses in interconnected IT/OT ecosystems. Darktrace illuminates your entire business and takes targeted action to stop emerging attacks.
Link image
Find out how a behavioural analytics-driven approach can transform security operations with the new Exabeam commissioned Forrester study.
Link image
Story image
Amazon Web Services / AWS
RedShield leverages AWS to scale cybersecurity services
"Working with AWS gives RedShield the ability to mitigate significant application layer DDoS attacks, helping leaders adopt best practices and security architectures."
Story image
Workato unveils enhancements to enterprise automation platform
"The extra layer of protection with EKM, zero-logging, and hourly key rotation gives customers a lot more visibility and control over more sensitive data."
Story image
Lightspeed launches all-in-one marketing platform in A/NZ
ECommerce provider, Lightspeed has launched a new all-in-one marketing solution, Lightspeed Marketing & Loyalty in Australia and New Zealand.
Story image
9/10 Aussies to stop spending if personal data compromised
"Based on the patterns we are seeing among Australian consumers, it is evident that trust in a brand is exceptionally important."
Story image
Sift shares crucial advice for preventing serious ATO breaches
Are you or your business struggling with Account Takeover Fraud (ATO)? One of the latest ebooks from Sift can provide readers with the tools and expertise to help launch them into the new era of account security.
Story image
Legrand unveils Nexpand, a data center cabinet platform
Legrand has unveiled a new data center cabinet platform, Nexpand, to offer the necessary scalability and future-proof architecture for digital transformation.
Story image
Could your Excel practices be harming your business?
While Excel has been the de-facto standard for budgeting, planning, and forecasting, is it alone, enough to support organisations in the global marketplace that’s facing rapid changes due to digital transformation?
Story image
Qualys updates Cloud Platform solution with rapid remediation
The new update is designed to enable organisations to fix asset misconfigurations, patch OS and third-party applications, and deploy custom software.
Story image
Digital Transformation
How to modernise legacy apps without compromising security
At a time when digital transformation has become central to business, even the most important applications come with a ‘use-by’ date.
Story image
Supply chain
Jetstack promotes better security with supply chain toolkit
The web-based resource is designed to help organisations evaluate and plan the crucial steps they need to establish effective software supply chain security.
Story image
Cybersecurity starts with education
In 2021, 80% of Australian organisations responding to the Sophos State of Ransomware study reported being hit by ransomware. 
Story image
Nozomi Networks
Nozomi Networks, Siemens reveal software integration
Nozomi Networks and Siemens have extended their partnership by embedding Nozomi Networks’ software into the Siemens Scalance LPE local processing engine.
Story image
A10 Networks finds over 15 million DDoS weapons in 2021
A10 Networks notes that in the 2H 2021 reporting period, its security research team tracked more than 15.4 million Distributed Denial-of-Service (DDoS) weapons.
Story image
Data Center
Preventing downtime costs and damage with Distributed Infrastructure Management
Distributed Infrastructure Management (DIM) can often be a lifeline for many enterprises that work with highly critical ICT infrastructure and power sources.
Story image
Digital Transformation
The impact of COVID-19 on healthcare environments and care delivery
The COVID-19 pandemic has revolutionised the healthcare industry while overcoming staff shortages, social distancing requirements, and lockdowns.
Story image
Revenue operations is taking centre stage
As the business world continues to evolve, new demands need to be met to keep up with the ever-changing landscape. 
Story image
Digital Transformation
The Huawei APAC conference kicks off with digital transformation
More than 1500 people from across APAC have gathered for the Huawei APAC Digital Innovation Congress to explore the future of digital innovation.
Story image
Power at the edge: the role of data centers in sustainability
The Singaporean moratorium on new data center projects was recently lifted, with one of the conditions being an increased focus on power efficiency and sustainability.
Story image
Customer experience
Research unveils precarious customer loyalty for retailers
New research has found customers are reassessing established brand loyalties as their priorities and behaviours shift.
Story image
Artificial Intelligence
AI-based email security platform Abnormal Security valued at $4B
"A new breed of cybersecurity solutions that leverage AI is required to change the game and stop the rising threat of sophisticated and targeted email attacks."
Story image
Rubrik Security Cloud marks 'next frontier' in cybersecurity
"The next frontier in cybersecurity pairs the investments in infrastructure security with data security giving companies security from the point of data."