ETL vs. ELT: What Data Teams Need to Know

Data comes in all different forms. It can be structured or unstructured, or have variations in its format (e.g. a date being written as month/day/year versus day/month/year). 

As a result, businesses need a process for data integration – which is where ETL and ELT come in. These are two different ways to integrate data from multiple sources and make it ready for analysis. But how do these methods differ, and which approach is best suited for your business? 

To help answer these questions, this article will explain:

• What is ETL and how does it work?

• What is ELT and how does it work?

• ETL vs. ELT: how they’re different?

• ETL vs. ELT: which is best?

• ETL & ELT tools

• How Segment integrates with ETL/ELT tools

What is ETL and how does it work?

ETL means “extract, transform, load.” With this approach, you would first extract data from different sources like websites, email systems, spreadsheets, and CRMs. This data would then be copied and temporarily stored in a “staging area,” like a database on a secondary processing server or in the cloud. This is where the transformation takes place, which includes cleaning and validating data, removing/masking personally identifiable information (PII), or even doing calculations. 

At this stage, you can also reformat data to make it compatible with the system it’s being sent to (e.g. your data source uses strings for a certain field but you’re sending it to a system that uses integers). 

Finally, you load the transformed, structured data into your desired system, whether it’s a relational database, data warehouse, or analytics platform that can accept a direct data feed. From there, analysts, developers, and users can access and analyze the data using business intelligence tools like Looker or Tableau.

What is ELT and how does it work?

ELT means “extract, load, transform.” In this approach, you extract raw, unstructured data from its source and load it into a cloud-based data warehouse or data lake, where it can be queried and infinitely re-queried. 

When you need to use the data in a semi-structured or structured format, you transform it right in the data warehouse or data lake.

Say you’re in e-commerce, and a marketer wants to analyze customer attributes and browsing habits to create new audience segments. You would only transform the data needed (i.e. attributes and browsing behavior) to ensure it’s in a format that’s compatible with their customer segmentation tool. You then can query and transform the same data fields again at another time for a different tool or use case. 

Transformation in ELT is much the same as in ETL: you cleanse and validate the data, convert it to match the destination’s data format, and perform calculations. You can also encrypt and anonymize the data to comply with privacy regulations.

ETL vs. ELT: how they’re different

To determine whether ETL or ELT is best for your business, consider your priorities and current capability. For example, is access to real-time data critical to your operations? If so, then you need the fast loading speed of ELT. Is the data you collect highly sensitive and subject to stringent, industry-specific privacy laws? If yes, ETL lets you set up tighter privacy guardrails.

Take a look at the table below to explore the key differences between ETL and ELT. 

Transformation process

ETL requires you to know in advance what types of data you need, what you’ll use them for, and what data formats your target systems accept. That’s because the staging area typically doesn’t store the entire raw data set you extracted. If later on you identify a new analysis case, you would need to extract the data from the source again – if it’s still there.

That means planning is paramount before setting up an ETL pipeline. At Segment, we encourage our customers to create a tracking plan that clarifies:

• The types of data to track

• Business justifications for tracking this information

• Tracking methods

• Data naming conventions

If you’re going the ETL way, check out our guide on how to create a successful data tracking plan. It comes with templates for SaaS, eCommerce, mobile, and video use cases.

ELT gives you more flexibility as it only asks you to transform data when you need to. You’ll still benefit from a tracking plan, especially for transformations you want to automate. If you need to add more data types to your analysis or implement a new analytics tool, you can go back to your data warehouse at any time to get what you need.

Privacy & compliance

ETL lets you comply with privacy rules as it removes personal identifiers from data before making it accessible. Aside from territory-based laws like GDPR, you may also need to comply with industry-specific regulations, such as the California Consumer Privacy Act (CCPA), Health Insurance Portability and Accountability Act (HIPAA), and Family Educational Rights and Privacy Act (FERPA).

If you’re using ELT and you collect personal data generated in the EU, you may need to use a data warehouse or data lake that’s hosted within the territory unless you have applied privacy safeguards approved by the European Commission. Consult a data privacy specialist to make sure your data pipeline complies with relevant laws.

ETL vs. ELT: which is best?

Your purpose for collecting and analyzing data will help you determine whether ETL or ELT is best for your organization.

When to use ETL

Choose ETL if you:

• Deal with smaller datasets and complex transformations

• Need to comply with privacy regulations like HIPAA

• Want non-technical users to analyze data using business intelligence tools

Companies that use a customer data platform (CDP) will find ETL compatible with their process. A CDP uses APIs and native connectors to ingest data from different sources.Once in the CDP, the data gets cleaned and transformed, as well as unified to create a single customer view.

The CDP also pushes data to downstream tools like email marketing, messaging, and A/B testing. That means you don’t need to repeat the ETL process every time you want to send clean data to a different tool.

When to use ELT

Choose ELT if you:

• Collect large volumes of unstructured data and need to have them in your data repository fast

• Need to re-query datasets multiple times or apply several different transformations

ELT is especially useful for massive streams of data that must be loaded in real-time – think stock-market data or information from IoT sensors. If data-intensive tech like AI, robotics, and IoT are crucial to your business operations, an ELT pipeline will give you the scale, speed, and flexibility you need. With ELT, you can send data to different downstream tools that have different requirements using either a reverse ETL tool or a CDP.

ETL & ELT tools

ETL and ELT software automate the data integration process. They use SQL queries and API connectors to extract data, and they work with the most popular sources as well as with cloud data warehouses like Amazon Redshift, Google BigQuery, and Snowflake.

Before implementing an ETL/ELT tool, check whether it integrates with your existing data systems and infrastructure, such as whether or not it works with your CDP.

Stitch ETL

Stitch is a no-code ETL tool that can extract data from any source using integrations built on Singer, an open-source standard for ETL. It’s best used for sales, marketing, and product intelligence. Pricing starts at $100/month.

Serenytics ETL

Serenytics is a low-code business intelligence platform for business users and software publishers. It lets you configure ETL as an automated process within the platform. Plans for medium-sized companies start at $490/month, and plans for enterprises start at $1,520/month.

Blendo ELT

Blendo is an ELT solution best used for integrating sales, marketing, and financial data. It automatically infers the schema of the data source and prepares the necessary tables in your warehouse, reducing the time you spend preparing and transforming data. Blendo doesn’t disclose pricing on its website.

How Segment integrates with ETL & ELT tools

Segment can serve as the source of customer data for your ETL and ELT tools. By adding Segment as a data source, you don’t need to manage multiple ETL/ELT connectors for your sources of first-party customer data. The data is cleaned and transformed in Segment and enriched through the creation of unified customer profiles and customer segments. You can then analyze customer data alongside other types of business data, such as financial and supply chain information.

Find out how Segment integrates with Stitch, Serenytics, and Blendo.