ETL vs ELT: which one is right for your business?

Every company has dozens, if not thousands, of competing priorities. 

To cut through the noise, many businesses turn to either ETL or ELT processes to organize their data and uncover valuable insights. But as more and more companies turn to SaaS products to improve performance and profitability, the amount of data they have to deal with has ballooned. In the last six years companies worldwide have increased their reliance on SaaS apps by more than 13X. 

How do companies choose between ETL vs ELT when they have to deal with data from an average of 110 apps? It’s an important decision and the stakes have never been higher.

To help you make the right decision for your organization, we’ll examine the pros and cons of ETL vs ELT. Along the way we’ll provide examples of each and conclude by sharing a solution that lets you get the best of both worlds. 

What is ETL?

ETL stands for extract, transform, and load, and it refers to a specific method for formatting and organizing data in a consistent manner so that it can be analyzed for business intelligence. ETL is one way to answer the question, “How do we transport the right data from all our software to our data warehouse?” The three steps within the ETL process are:

Extract

Structured data is pulled from one or more sources in batches. The data could come from a variety of different sources and might present itself in several different formats, including JSON or XML.

Transform

In this step, the data is cleaned to match a specific set of predefined instructions that align with the needs of the business. Transformation might include filtering the data to remove duplicate records and applying rules to prevent bad data from being added to the company’s data warehouse. 

Load

In this final stage, the data is delivered to its ultimate destination, where it can be viewed and analyzed by team members with the right permissions.

Related: The differences between an iPaaS and ETL

What’s an example of an ETL process?

Picture a logistics company that specializes in cold storage of perishable foods. They have a fleet of 100 trucks that criss-cross the country 24/7/365, transporting everything from 6 oz clamshells of fresh blueberries to 5 lb bags of frozen shrimp and everything in between. 

In this business, temperature control is everything. If the temperature gets too high for too long, even for 1 hour, that could spell disaster. To ensure every shipment arrives intact, the company has installed IoT temperature monitors in their trucks, and each sensor relays the temperature to a central portal every 15 minutes.

In order to get an accurate picture of what’s going on with each shipment, the company might extract temperature data from all their sensors alongside information about their drivers. The data could then be transformed into a consistent format. Finally, the cleaned data could be loaded into the company’s data warehouse, where it would be accessible to anyone with the appropriate permissions on the company’s intranet.

In this example, analysts at the company’s headquarters might want to correlate the temperature of the food(s) in each shipment with the drivers responsible for delivering those shipments. The analysts would specify these criteria during the initial implementation of the ETL process. During this time they’d also specify how often they want the process to run, since traditional ETL processes can only deal with data in batches.

Pros and cons of ETL

Even this simple example reveals some of the pros and cons of ETL, so it’s worth taking a look at a more detailed list.

Pros:

• Easier to manage your data storage costs, since data is transformed and filtered before it’s loaded into your data warehouse. You don’t pay for data that you don’t store: duplicates, bad data, and anything else excluded by your initial criteria.
• Supports your data privacy and compliance goals in alignment with GDPR, CCPA, HIPAA, and similar regulations. Within ETL processes, sensitive data are masked or encrypted during the transformation stage.
• Safe, simple, straightforward way to transform and load data over time.
• Good choice when complex transformations are required.
• ETL has been around for decades, so it’s easy to find technology solutions and experts who can help you set up the best ETL process for your business needs.

Cons:

• Higher ongoing maintenance costs, since changing input sources will require constant updates to basic steps that define your ETL process.
• Offers less flexibility in how you analyze data, since the transformation step is baked into the entire process from the beginning.
• Doesn’t support projects that rely on machine learning or real-time analysis.
• Can only integrate your data, not the systems responsible for that data.
• Typically can’t move data across systems in real time. 
• Works best for smaller amounts of data.

What is ELT?

By this point careful readers may have noted that if ETL stands for extract, transform, and load, then ELT must refer to extract, load, and transform. Though it’s only a subtle change in the name, ELT is drastically different. The three steps within the ELT process are:

Extract

Any type of data, whether structured or raw, is pulled from any source. It could be on-prem software, a SaaS solution, a private data cloud, or anything else.

Load

This data is loaded directly into a data lake without any type of filtering. Whatever data existed in the source programs, you get it all in this stage: the good, the bad, and the ugly.

Transform

Once all these data are loaded into the target destination, they can be transformed into a consistent format and analyzed in real-time. These transformations are only limited by the ingenuity of the people looking at the data. This type of flexibility is one of the big differences between ELT and ETL. 

Related: What is reverse ETL?

What’s an example of an ELT process?

Let’s continue with our example from above, only this time it’s about to get way more complex. One of the trucks from our logistics company makes a delivery to a grocery store in your neighborhood. This store is a part of a nationwide chain, and instead of managing hundreds of different food items like the logistics company, they have to manage tens of thousands. 

The logistics company had to keep track of temperatures from their IoT sensors, the items on each truck, who’s driving each truck, and each truck’s location to make their ETL process work. But ETL won’t work for the grocery store because they’re dealing with more data. Much, much more data, including:

• Vendor history and payment terms for every item
• Real-time inventory figures for every single item
• Best-by dates for every item
• Customer loyalty program
• Promotional calendars
• Employee hours
• Sales performance 
• Store layout (ie, where each item is placed within the store)
• Online ordering through their website and app

This is only a basic list, but you get the idea. If business analysts at the company’s headquarters want to understand what’s going on, first they would extract all the data in these categories for a single store. Then they’d load it into a data lake before transforming the data on demand, based on requests from different teams:

• For example, one of their category managers might ask them to track the sales performance of a specific item over the past three months as a function of who’s working the floor, and subtract out any effects from promotions.
• Two hours later, their finance team might ask them to run a report comparing sales performance for items provided by different vendors, to determine who should receive more favorable payment terms. 
• An hour after that, their marketing team asks them to compare the sales performance of items within the same department across five stores in a certain region, so they can plan a new in-app and email messaging campaign for the following month.

Dizzy yet? It would be impossible to accommodate this degree of flexibility with an ETL process. But all types of companies use ELT to handle much greater volumes of data every single day than our fictitious grocery chain.  

Pros and cons of ELT

We’ve already dropped some hints about the benefits of ELT. When you’re comparing ETL to ELT, combine our earlier list of pros and cons with the following items:

Pros:

• Real-time data analysis. With ELT, you don’t have to wait for your IT teams to extract a new batch of data. You can run experiments on all the data in your system whenever you want. 
• Much more flexibility in how you analyze data. Easily change your transformation parameters every time you have a new query.
• Work with all types of data, whether it’s structured or raw.
• Far easier to scale as you load more and more data.
• Store huge quantities of data with no problems.
• Load data as soon as it’s created. 

Cons:

• Since you’re storing all types of data, storage requirements may be much higher.
• Sensitive privacy data must be loaded into a data lake before it’s transformed, which makes it visible to SysAdmins and potentially more exposed in the event of a data breach. Extra security measures are required for ELT processes to support data compliance with GDPR, CCPA, HIPAA, and similar regulations.

Related: The key differences between an iPaaS and an enterprise automation platform

ETL vs ELT: choose either method with Workato

ETL evolved to address companies’ rapidly growing data sets. As this trend accelerated and the amount of data exploded exponentially, ELT was developed to give businesses more flexibility and agility with their data analysis. 

Both methods are used to achieve similar goals: transform large amounts of data into formats which can be more easily understood, so that you and your team can perform meaningful business intelligence. Without ETL or ELT, we’d be doomed to endure the consequences of disconnected data silos, the ultimate example of our left hand not knowing what our right hand is doing.

So, you need to choose one or the other.

Or do you?

With Workato, the leader in enterprise automation, you don’t have to choose between ETL vs ELT. 

Workato offers hundreds of pre-built connectors and thousands of automation templates This allows you to easily connect your data warehouse or data lake with the rest of your tech stack and implement either process quickly. In addition, the platform offers enterprise-grade security through features like roles-based access controls, audit logs, and lifecycle management, ensuring that you stay in compliance with data privacy regulations.