What Is Extract Transform and Load (ETL)? A Beginner's Guide for Data Pros

The information age has made big changes in our daily lives. We can access data about almost any topic to help us make decisions ranging from small—like what to wear for today’s weather—to the complex and consequential—like determining which neighborhood to move to in order to make sure your kids attend good schools.

Though mobile apps and websites make it appear that this data is easily accessible, the reality is that getting the information in a useable form is due to the work of data professionals who take on the critical extract, transform and load (ETL) process. While perusing job postings will make it clear that this capability is sought-after, you could use some additional clarity about what it entails, why it matters and how this work is applied. To help with that, we’ve asked data professionals to shine a light on the ETL process.

What is ETL?

“ETL is the process of retrieving data from one or more sources, collating and/or changing the data in some way and delivering it to an accessible destination,” says Margaret O’Brien, founder of Data Mining DNA.

As the abbreviation implies, there are three basic steps in this process:

1. Extraction: Identifying and capturing data from various sources
2. Transformation: The “cleaning” or standardization of data to meet the needs of the business or organization that wants to use it (e.g., converting date formats, removing duplicates)
3. Load: The loading of data into a new site or “data warehouse” that serves the business or organization’s needs

How is the ETL process used?

If you’re still a little fuzzy on what this means, let’s move from more abstract definitions and focus on how this process works in the real world. In practice, businesses and organizations often have access to data from a variety of sources. For example, an ETL process may start by pulling data from customer relationship management (CRM) databases, website analytics and mobile app data. The raw output of all these sources can be massive, repetitive and messy—which is a problem when your analysis depends on quality inputs.

“ETL is often used to solve data quality problems,” says David Wurst, founder of WebCitz. “For example, if your business wants to model customer behavior, then it needs to ensure that the data about customers is accurate and consistent across multiple sources.”

According to Wurst, this is where the transformation phase is key, as it’s necessary to consolidate the data into a single, unified store that is both easier to work with and more accurate.

“This consolidation will make sure that all data about each customer is consistent, which will allow you to build more reliable models,” Wurst explains.

The transformation could also be a matter of sorting and consolidating customer information based on key information like the date of a transaction or shipping addresses.

“The business may also need to transform the data as it loads into the target system to accommodate different variations in how data is stored,” Wurst says.

For instance, a field labeled “location” and a field labeled “address” may correspond to the same information being captured twice in different systems—so there’s a need for merging and clean-up of duplicative data.

Depending on the sources of the data, the transformation process can involve multiple steps.

“The data transformation process often entails several processes, including filtering, sorting, aggregating, joining, cleaning, deduplication and validation,” says Kavin Patel, founder of Convrrt®.

O’Brien offers an example of her own work as a founder of a data analytics firm in the genealogical industry, where taking data from many different sources—DNA testing companies, newspapers and historical archives--can help people build a clear family history.

“I extract data from multiple genealogical archives, map different pieces into meaningful information and deliver easy-to-read spreadsheets to clients,” O’Brien says.

While that’s a unique use-case for the process, the underlying purpose of the process is consistent—cutting out extraneous info, consolidating similar data and ultimately making the combined dataset more accurate and easier to work with.

Why is the ETL process important?

The shorthand phrase “garbage in, garbage out” is used often in tech roles—and it speaks directly to why the ETL process is important. The validity and value of data analysis depend on having good inputs, and the ETL process is used to clear out as much of the noise in a collection of data as possible.

“ETL boosts the quality of the data through data cleansing before loading the transformed data to a target data base,” says John Fordice, analytics lead at Bonsai. “ETL makes it easy to analyze and report on data.”

Tina Hawk, senior vice president of human resources at GoodHire®, says that ETL gives businesses a strategic edge.

“Data warehousing, via ETL, allows businesses to operate data analysis processes in an integrated manner, resulting in less fragmentation and greater processing power for large, complex datasets,” Hawk says.

Applying ETL to your career path

Now that you know more about the basics of what ETL is and why it matters, you may be wondering how this role fits into the world of tech careers and what it would take to get started. Given that this role is relatively new and not concretely defined by licensing or other legal requirements, you’ll find there’s some variance in the job titles of professionals whose work focuses on ETL. Titles may include (but are not limited to):

• ETL developer
• Data engineer
• Data architect
• SQL developer
• Database administrator
• Business intelligence analyst

No matter the title, these jobs require educated, skilled tech professionals who have a knack for problem-solving, organizational ability and efficiency.

“The most important skill is a rigorous attention to detail,” says O’Brien. “Technology and software can be learned, but a good analyst enjoys finding patterns in incomplete or inconsistent data.”

Hawk has found that organizational and problem-solving skills are essential in ETL roles, along with technical know-how.

“Competent ETL developers are first of all extremely adept at using ETL tools and software and are proficient in SQL, which is the most common database language for ETL software,” Hawk adds. “It’s also useful if developers are comfortable using other scripting languages in order to alter and edit ETL tools, many of which are open source and customizable.”

Could a data-focused career be in your future?

Think the field of data integration, ETL and data analytics might be a good fit for you? Check out our article " 12 Open-Source Data Resources for Your Next Project".

Convrrt is a registered trademark of Omazing Creations, LLC.
GoodHire is a registered trademark of Inflection, LLC.