ETL (Bite-size Article)

Introduction

Recently, the new project I’m involved in has come to include more and more data‑analysis tasks. Up to now, I’ve mainly worked on web development and design, so I haven’t had much hands‑on experience with data analysis or machine learning. That’s why I’ve started studying these topics little by little while pushing the project forward.

One term I now see all the time is “ETL.” Have you heard of it?

ETL is an acronym made up of three words:

• E = Extract

• T = Transform

• L = Load

It describes the basic sequence of operations you follow when working with data. In this post—partly as my own memo—I’d like to give a brief overview of what ETL is and how to think about it.

What Each Step Means and Does

✅ Extract

Extract is the very first step in data analysis. The first thing you do is decide where the data you need lives and then pull out the required data.

Typical data sources include:

• Databases (MySQL, PostgreSQL, SQLite, etc.)

• CSV or Excel files

• External service APIs (for weather data, exchange rates, and so on)

• Web scraping (parsing HTML to capture information)

Extracting isn’t only about loading data; it also includes choosing which range, which columns, and which records you actually need.

🧠 Example: Reading sales data from a CSV file

import pandas as pd

# Read data from a file called sales.csv
df = pd.read_csv("sales.csv")

# Check the first 10 rows to make sure it loaded correctly
print(df.head(10))

Even a single CSV load like this counts as the “E” in ETL.
In practice, you might join multiple files, pull only a specific date range, and so on—often making the step more complex.

✅ Transform

Transform means taking the raw data you’ve extracted and reshaping it into a form that’s ready for analysis or processing. In other words, it’s turning raw data into usable data.

Common tasks in this phase include:

• Handling missing values (NaN)
    → Drop rows with missing values or fill them with averages, etc.

• Converting date or text formats
    → “2022/01/01” → convert to a date‑type column so it can be sorted chronologically

• Standardizing units
    → e.g., convert dollars to yen, change numeric strings to numeric types

• Removing unnecessary rows or columns
    → Drop columns you don’t need so the data is easier to work with

• Joining or aggregating multiple datasets
    → Group by month, sum by product, and so on

If Transform isn’t done properly, no matter how good your models or visualization tools are, the results can’t be trusted. It’s truly the linchpin of data analysis.

🧠 Example: Converting a date column and dropping missing rows

import pandas as pd

# Convert the date column to datetime
df["date"] = pd.to_datetime(df["date"])

# Remove any rows that contain NaN
df = df.dropna()

These operations may look mundane, but they are crucial for turning messy data into clean data.

✅ Load

Load is the step where you save the cleaned‑up data.

Once Transform has made the data tidy, you export or store it in a form that your next process or analysis step can readily use.

Typical destinations include:

• Databases (MySQL, PostgreSQL, SQLite, etc.)
    → Good for managing large volumes and integrating with other systems

• CSV or Excel files
    → Easy to hand off to analysts or import into BI tools

• Cloud or data warehouses (DWH)
    → Upload to BigQuery, Snowflake, Amazon Redshift, and the like

🧠 Example: Saving the cleaned data as a CSV file

# index=False prevents the row index from being saved
df.to_csv("cleaned_data.csv", index=False)

It’s a simple example, but now your transformed data is stored in cleaned_data.csv, ready to share or load into other tools.

Where Is ETL Used?

ETL thinking shows up in many scenarios that involve data:

• 🔹 Machine‑learning preprocessing
    Prepare clean data for model input—handling missing values, normalization, categorical encoding, etc.

• 🔹 Preparing data for BI tools (e.g., Tableau, Power BI)
    Reformat data for dashboards and reports.

• 🔹 Combining and aggregating data from multiple sources
    Merge data from different departments or systems so it can be analyzed in one place.

• 🔹 Automating recurring reports or batch processes
    Daily or weekly sales reports, traffic analysis, and any scheduled workflow rely on a solid ETL backbone.

Tip: What Are BI Tools?

BI tools—short for Business Intelligence tools—let you visualize data in an easy‑to‑understand way and support decision‑making. Many are no‑code, so even non‑engineers can use them.

Typical features include:

• Turning data into graphs and charts
• Filtering and slicing data on the fly
• Monitoring real‑time metrics on dashboards

Popular examples:

• Tableau – intuitive and flexible visualizations
• Microsoft Power BI – tight integration with Excel
• Looker – Google‑backed, strong for cloud analytics

The data you prep with ETL feeds directly into these BI tools.

Conclusion

In this article we looked at the basics of ETL, a workflow that comes up again and again when preparing data for analysis or machine learning:

• Extract – gather the data
• Transform – clean and reshape it
• Load – store it for the next step

Even if you’re not a data specialist, understanding ETL will help wherever data is involved. Try incorporating these ideas into your work and learning.