ETL for beginners

The term ETL seems simple, but can be tricky to implement in practice. Here’s a brief introduction on how to use it to harness your organization’s data.

ETL stands for ‘Extract’, ‘Transform’, and ‘Load’, meaning the extraction of raw data (usually from multiple sources), transformation of that into foundational data, and the loading of the finished dataset into a destination (usually a data warehouse).

Data is a crucial and growing resource in the modern world, and businesses who fail to capitalize on it effectively stand to lose out. ETL is necessary to unify and harness data, ready for it to be used for analytical purposes. Read on to find out how ETL works.

What are the three stages of ETL?

‘Extract’ is the first stage, in which the raw data is moved or copied from its original locations into interim storage. It’s essential to get the extraction stage right so that the rest of the data pipeline functions as expected. This involves a huge number of considerations, such as differences in time zones, backfilling of failed sources or missing data, handling different APIs, and compliance with data security.

During the ‘transform’ phase, the raw data is converted into foundational data. Rules are applied to prepare the extracted data for its purpose. These include cleansing, standardization, verification, formatting and sorting, labeling, and protection.

‘Loading’ is the final phase of ETL, where the transformed data is transferred into the target destination, which is usually a data warehouse or data lake. This often begins with a ‘full loading’, which includes all data, followed by regular ‘incremental loading’ of any differences. Considerations at this stage include cataloging, maintenance, archiving, and data governance. As you can see, ETL is far from simple, and we’ve only scratched the surface here.

What is ETL architecture?

ETL architecture is essentially a blueprint for your ETL process, showing how it works from beginning to end in a step-by-step manner. This includes the methodology used to transfer data, the transformation rules, and the tools and programming languages used. The more information your ETL architecture provides, the better. When designing your architecture, you also need to decide whether you’re using ETL or ELT. You can read more about this in our blog post on ETL vs. ELT.

Which ETL tool is used most?

An ETL tutorial wouldn’t be complete without mentioning ETL tools. Building an ETL pipeline from scratch often requires a number of different pieces of software, but the most popular tools are probably the programming languages used to construct and connect these together: SQL and Python. SQL (pronounced: “sequel) is a query language used to search and modify databases. Python is a versatile language that can be used for many different applications, but has a number of useful modules for handling databases, and thus finds popular use in ETL.

Is SQL an ETL tool?

Absolutely. SQL is relied upon heavily when constructing ETL pipelines. The language contains a vast number of commands for manipulating databases. A SQL tutorial is beyond the scope of this post, but an ETL example of SQL might include the following:

INSERT INTO Customers (CustomerName, City, Country)

SELECT SupplierName, City, Country FROM Suppliers

WHERE Country=’USA’;

This instructs the database management system to create new records in a table called “Customers” and to populate them with data from a table called “Suppliers”, but only if the “Country” column says “USA”.

SQL doesn’t have the ability to access datasets from disparate systems, so you need to transfer these into a warehouse first. Sometimes, SQL is thought of as the only tool required to build an ETL pipeline, but there’s a big difference between ETL and SQL! SQL is a language often used as part of an ETL pipeline, but does not constitute the whole process itself.

How do you create an ETL process in Python?

While we can’t include a full ETL tutorial in Python here, here are some examples of common commands.

Extract a CSV:

def extract_from_csv(file_to_process):

dataframe = pd.read_csv(file_to_process)

return dataframe

Convert a price into two decimal places:

def transform(data):

data[‘price’] = round(data.price, 2)

return data

Load a CSV file:

def load(targetfile,data_to_load):

data_to_load.to_csv(targetfile)

When considering ETL with Python, Pandas is a good place to start. This open-source module adds support for multi-dimensional arrays, data analysis, and ML (Machine Learning), all of which are incredibly useful in ETL.