# Extracting, Transforming, and Loading (ETL) Data

> Chapter 5 of the Courseiva DATA-PLUS curriculum — https://courseiva.com/learn/data-plus/extracting-transforming-and-loading-data

**Official objective:** 2.2 — Explain the ETL process and data integration techniques

## Introduction

If you cannot get your data from its source systems into a format your analysis tools can actually use, your report is worthless and your decisions will be based on guesswork. That is the problem Extracting, Transforming, and Loading (ETL) solves: it is the pipeline that moves raw, messy data from where it is created to where it can be analysed. For the DA0-002 exam, understanding ETL is essential because it is the backbone of almost every data integration task you will be tested on.

## The Kitchen Prep Line Analogy

A busy restaurant kitchen on a Friday night. The head chef has just received a stack of new orders: a salmon dish, two steaks, a vegan bowl, and a pasta special. Each order comes from a different source — one is written on a paper slip, another comes through a digital tablet, and a third is a verbal shout from the waiter.


The first step is extraction. The sous-chef collects each order from its source, no matter the format. She gathers the paper slip, reads the tablet screen, and repeats the verbal request back for confirmation. She does not start cooking yet. Her only job is to pull the raw data — the order details — from wherever they live.


Next comes transformation. The head chef takes those raw orders and standardises them. He converts the vegetarian order into a specific recipe with exact ingredient quantities. He converts the steak temperatures (rare, medium-rare) into precise cooking times. He groups orders by cooking method: grill, oven, or stove. He also cleans the data by ignoring any duplicate orders and correcting a misspelled dish name. The orders are now in a consistent, useable format.


The final step is loading. The line cooks receive the transformed orders on a unified digital screen. Each cook knows exactly what to prepare, in what order, and for which table. The complete, cleaned, standardised orders are now loaded into the cooking system, ready for action.


Without this ETL process, the kitchen would be chaos — mismatched orders, burnt steaks, and angry customers. The ETL pipeline ensures every order is consistently collected, cleaned, and delivered to the right cook.

## Core explanation

ETL stands for Extract, Transform, Load. It is a three-step process used to pull data from one or more source systems, clean and reshape that data, and then place it into a destination system — usually a data warehouse or a data lake — where it can be analysed, reported on, or used for business intelligence.


Why do we need ETL at all? Imagine a company that uses a sales system (like Salesforce), a customer support ticketing system (like Zendesk), and a financial accounting system (like QuickBooks). Each of these systems stores data in a different format. The sales system might use a proprietary database format; the support system stores tickets as JSON files; the accounting system uses a spreadsheet with a specific structure. If you try to run a report that combines sales figures, customer complaints, and revenue data, these systems cannot talk to each other directly. ETL is the process that takes data from each source, standardises it, and merges it into a single, consistent location.


Let us break down each step.


Extract: This is the first step. During extraction, data is pulled (or read) from its original source. The source could be a relational database (like MySQL or PostgreSQL), a cloud-based application (like Salesforce), a flat file (like a CSV or Excel spreadsheet), an API (an Application Programming Interface that lets two software programs talk to each other), or even a live data stream (like sensor readings). The key goal of extraction is to get the raw data out with minimal change. You might extract all records from a table, or you might use a filter to only get data from the last 24 hours. Extraction can happen in two ways:

- Full extraction: Every single record from the source is pulled, every time the ETL runs. This is simple but can be slow and resource-intensive if the source contains millions of records.
- Incremental extraction: Only the records that have changed since the last extraction (for example, new sales orders placed today) are pulled. This is far more efficient for large datasets but requires the source system to track changes (often using a timestamp or a change log).


After extraction, the raw data is typically held in a temporary staging area. A staging area is a landing zone — a separate database or storage location where the raw data sits before it is transformed. It acts as a buffer so that if something goes wrong during transformation, you do not lose the original source data.


Transform: This is the most complex and important step. Transformation is where you clean, standardise, and reshape the raw data so that it is ready for analysis. Common transformation tasks include:

- Cleaning: Removing duplicate records. Filling in missing values (or deciding to exclude incomplete records). Correcting inconsistent formats (for example, changing all dates to YYYY-MM-DD format, or converting all currency values to a single currency).
- Standardisation: Making sure that data from different sources uses the same codes or labels. One system might list gender as 'M' and 'F', while another uses 'Male' and 'Female'. Transformation converts all of them to a single standard.
- Derivation: Creating new fields from existing data. For example, calculating 'total order value' by multiplying 'unit price' by 'quantity', or extracting the year from a full date field.
- Aggregation: Summarising data. Instead of having every single sale transaction, you might transform the data into daily sales totals per product line.
- Joining: Combining data from different sources. You might take customer names from the sales system and match them with support ticket data using a common customer ID.
- Filtering: Removing rows that are not needed. For instance, you only want records from the current financial year.
- Sorting and ordering: Putting data into a specific sequence.


Load: The final step. Loading is the process of writing the transformed data into the destination system. The destination is most often a data warehouse (a special database designed for analysis and reporting), but it could also be a data lake (a storage repository that holds data in its raw or transformed format). Loading can be performed in two main ways:

- Full load: Every time the ETL runs, the entire destination table is replaced with the new dataset. This is simple but means you lose the history of previous loads.
- Incremental load: Only the newly transformed or updated records are added to the destination table, preserving historical data. This is more common in production environments.


The entire ETL process is usually automated using ETL tools such as Talend, Apache NiFi, Microsoft SQL Server Integration Services (SSIS), or cloud-native services like AWS Glue and Azure Data Factory. The process runs on a schedule — perhaps every hour, every night, or every week — to keep the data warehouse up-to-date.

## Real-world context

Let us walk through a concrete scenario. You work as a junior data analyst for 'QuickShop', an online retailer that sells electronics. QuickShop uses three separate systems:

- QuickSales: A custom-built sales database (a relational database using PostgreSQL) that stores every order, including customer name, product ID, quantity, unit price, and order date.
- SupportTracker: A cloud-based customer support tool (like Zendesk) that stores support tickets with fields like ticket ID, customer email, product model, issue description, and ticket status.
- QuickBooks: An accounting spreadsheet (a CSV file) that is emailed to you each week, containing product costs and supplier information.


The CEO asks you for a report that shows, for each product, the total sales revenue, the total cost of goods sold, the number of support tickets raised, and the net profit. None of these three systems can produce this report on their own because the data is siloed — each system stores only its own piece of the puzzle.


Using ETL, you can solve this. Here is what an IT professional would actually do, step by step:

1. Extract: You configure an ETL tool (for example, a simple Python script or a tool like Talend) to connect to the QuickSales database and pull all orders from the last month. Then you connect to the SupportTracker API and pull all tickets created in the same period. Finally, you read the QuickBooks CSV file. All three extracted datasets are dumped into a temporary staging folder — perhaps a cloud storage bucket like Amazon S3.

2. Transform: Now you clean and standardise. You check for duplicate orders in QuickSales and remove them. You notice that SupportTracker stores dates in 'MM/DD/YYYY' format while QuickSales uses 'YYYY-MM-DD'; you convert all dates to 'YYYY-MM-DD'. You see that some product names have trailing spaces and inconsistent casing ('Laptop' vs 'laptop' vs 'LAPTOP'); you trim spaces and convert all product names to title case. You then join the QuickSales data with the QuickBooks cost data using the product ID, so that each order now has both its sales price and its cost. You aggregate the data to calculate total revenue, total cost, and total number of support tickets per product. Finally, you calculate net profit (revenue minus cost) for each product.

3. Load: You write the resulting clean, aggregated dataset into a new table in a data warehouse (for example, Amazon Redshift or Snowflake). The data warehouse now contains a single table called 'product_profit_summary' that the CEO can query using a business intelligence tool like Tableau or Power BI.


In practice, the IT professional would also set up a schedule so that this ETL process runs automatically every morning before the CEO arrives. They would also add error handling and logging — if the extraction from SupportTracker fails, the script sends an email alert and does not proceed to load any data for that day.


Common tools used in this scenario:
- ETL tools: Talend, Apache NiFi, Informatica PowerCenter, AWS Glue, Azure Data Factory.
- Data warehouses: Amazon Redshift, Google BigQuery, Snowflake, Microsoft Azure Synapse Analytics.
- Scripting languages: Python (with libraries like pandas for transformation), SQL (for data manipulation within the warehouse).
- Scheduling: cron jobs on Linux servers, or managed schedulers within the ETL tool.

## Exam focus

The DA0-002 exam expects you to understand the ETL process conceptually, but it also tests specific details that beginners often miss. Here is exactly what you need to know.


Key concepts that will appear in at least one exam question:

- The correct order of ETL steps: Extract, then Transform, then Load. The exam may try to trick you by presenting the steps as 'Extract, Load, Transform' (ELT) which is a separate concept. ELT is used when you load raw data first and transform it later inside the data warehouse — but the classic ETL order is strict.
- The purpose of the staging area: It is a temporary storage location used during the ETL process. It is not the final destination. The exam might describe a scenario and ask why a staging area is used.
- The difference between full and incremental extraction: Full extraction copies everything every time. Incremental extraction only copies changed data. The exam will present scenarios and ask which approach is more appropriate.
- Transformation tasks: You must be able to recognise examples of cleaning, standardisation, derivation, aggregation, joining, filtering, and sorting. Expect a question like 'Which of the following is an example of data transformation?' with options that include extraction and load activities as distractors.
- The destination of the load step: Typically a data warehouse or data mart. A data lake is also a possible destination, but be prepared to distinguish between a data warehouse (structured, for reporting) and a data lake (raw, for data science).


Common traps the exam sets:

- Trap: They describe a process that extracts data, directly loads it into a data warehouse, and then transforms it. They might label this ETL. The correct answer is that this is ELT (Extract, Load, Transform), not ETL.
- Trap: They list 'data validation' as a separate step after loading. In ETL, validation and quality checks are typically part of the transform step, not a separate fourth step.
- Trap: They ask about the 'load' step and offer 'the source system' as a possible destination. The load step always refers to writing to the destination (data warehouse), not back to the source.


Definitions you should memorise verbatim:

- ETL: A process that extracts data from source systems, transforms it into a consistent format, and loads it into a destination system for analysis.
- Staging area: A temporary storage area used during the ETL process to hold extracted data before transformation.
- Data warehouse: A central repository of integrated data from multiple sources, optimised for querying and reporting.
- Data lake: A storage repository that holds vast amounts of raw data in its native format until it is needed.
- Incremental extraction: A method of extraction that only retrieves data that has changed since the last extraction.
- Transformation: The process of cleaning, standardising, and reshaping data to make it consistent and usable.


Question types you will see:

- Scenario-based multiple choice: A company has sales data in a CRM and inventory data in an ERP. Which ETL step would combine the two datasets? Answer: Transformation (specifically, joining).
- Ordering questions: Put the three steps of ETL in the correct sequence. Answer: Extract, Transform, Load.
- Definition matching: Match the term (e.g., staging area) to its description.
- Best-practice questions: For a large dataset that changes frequently, should you use full or incremental extraction? Answer: Incremental.


The exam does NOT test specific tool names (e.g., 'Which tool is used for ETL?'). It tests concepts, not vendor-specific products. Do not waste time memorising tool names — focus on the process and the terminology.

## Step by step

1. **Extract from Sources** — The ETL process begins by connecting to each source system (databases, APIs, flat files, cloud applications) and pulling raw data. This step must preserve the original data exactly as it is. The extracted data is typically written to a staging area to avoid impacting the source system's performance.
2. **Stage the Raw Data** — The extracted data is placed into a temporary staging area, which is a separate storage location (often a database schema or a cloud storage bucket). The staging area isolates the ETL process from both the source and the destination, allowing you to re-run transformations without re-extracting data and providing a recovery point if the transformation step fails.
3. **Transform the Data** — The raw data in the staging area is now cleaned and reshaped. This step handles removing duplicates, correcting data types, standardising formats (e.g., dates, currencies), creating calculated fields, aggregating records, and joining data from multiple sources. The transformed data is ready for analytical use.
4. **Load into Destination** — The cleaned and transformed data is written to the final destination system, which is typically a data warehouse, data mart, or data lake. The load can be a full load (replacing all existing data) or an incremental load (adding or updating only changed records).
5. **Monitor and Log the Process** — After loading, the ETL process logs details about what was extracted, transformed, and loaded, including row counts, any errors encountered, and processing time. Monitoring ensures the process ran correctly and provides data lineage — a record of where the data came from and how it was changed. Alerts are set up to notify administrators if the ETL job fails.

## Comparisons

### ETL (Extract, Transform, Load) vs ELT (Extract, Load, Transform)

**ETL (Extract, Transform, Load):**
- Transformation happens before data is written to the destination.
- Requires a staging area to hold raw data during transformation.
- Best suited for structured data and on-premises data warehouses.

**ELT (Extract, Load, Transform):**
- Transformation happens after data is loaded into the destination.
- No separate staging area needed; the destination itself stores raw data.
- Best suited for unstructured data and cloud data warehouses or data lakes.

### Full Extraction vs Incremental Extraction

**Full Extraction:**
- All records from the source are pulled each time the ETL runs.
- Simpler to implement because no change tracking is needed.
- Can be slow and resource-intensive for large datasets.

**Incremental Extraction:**
- Only records that have changed since the last extraction are pulled.
- Requires the source system to track changes (e.g., timestamp or change log).
- Much faster and more efficient for large, frequently updated datasets.

### Full Load vs Incremental Load

**Full Load:**
- The entire destination table is replaced with new data each time.
- Simple to implement, but historical data is lost after each run.
- Often used for small datasets or when the data is refreshed entirely.

**Incremental Load:**
- Only new or updated records are added to the destination table.
- Preserves historical data and maintains a complete record over time.
- More complex to implement because duplicates and updates must be managed.

### Data Warehouse vs Data Lake

**Data Warehouse:**
- Stores structured, processed data optimised for querying and reporting.
- Data is stored in schema-on-write format (schema defined before loading).
- Typically stores data from ETL processes that have already been transformed.

**Data Lake:**
- Stores raw, unstructured, or semi-structured data in its native format.
- Data is stored in schema-on-read format (schema applied only when reading).
- Often stores data from ELT processes or raw data dumps for future analysis.

## Diagram

_The ETL pipeline: data flows from source systems through extraction, staging, transformation, and loading into the data warehouse for reporting._

```mermaid
flowchart TD
    A[Source Systems] --> B[Extract]
    B --> C[Staging Area]
    C --> D[Transform]
    D --> E[Load]
    E --> F[Data Warehouse]
    F --> G[Reports and Dashboards]
```

## Common misconceptions

- **Misconception:** ETL is only used when migrating data from one database system to another. **Reality:** ETL is used for any scenario where data must be moved between systems, including regular updates to a data warehouse, data integration for business intelligence, and feeding data into machine learning pipelines. Migration is just one use case. (People often encounter ETL first during database migrations (e.g., moving from Oracle to SQL Server) and assume that is its only purpose.)
- **Misconception:** The 'transform' step is optional because you can just load raw data and fix it later. **Reality:** Transformation is a core step in classic ETL. Skipping it means you load messy, inconsistent data into the destination, which defeats the purpose of having a clean, integrated dataset. ELT loads raw data and transforms it inside the warehouse, but that is a different architecture, not an omission of transformation. (With modern data lakes and ELT tools, people sometimes see raw data loaded without prior transformation and assume that is standard ETL.)
- **Misconception:** ETL runs only once and then the data is static forever. **Reality:** ETL is typically a recurring process. Most environments run ETL on a schedule (hourly, daily, weekly) to keep the data warehouse updated with new data from source systems. (Beginners often confuse a one-time data migration with the ongoing process of ETL for data warehousing.)
- **Misconception:** The load step always means inserting new rows into a table, never overwriting existing data. **Reality:** The load step can use either a full load (truncate and replace the entire table) or an incremental load (append or upsert only changed records). Both are valid, and the choice depends on business requirements. (People assume 'loading' always means 'adding', but in data warehousing, you often need to replace entire datasets to ensure consistency.)
- **Misconception:** ETL tools are only for large enterprises with massive budgets. **Reality:** Small and medium-sized businesses also use ETL. There are many open-source and low-cost ETL tools available, such as Apache NiFi, Talend Open Studio, and cloud-based services with pay-as-you-go pricing. Even a simple Python script with pandas qualifies as a basic ETL process. (Marketing around enterprise tools like Informatica can create the perception that ETL is exclusively for big companies.)

## Key takeaways

- ETL stands for Extract, Transform, Load, and the steps must be performed in that exact order for classic ETL.
- Extraction pulls raw data from source systems; it can be full (all data) or incremental (only changed data).
- Transformation is the most complex step and includes cleaning, standardisation, derivation, aggregation, joining, filtering, and sorting.
- The staging area is a temporary buffer used to hold extracted data before transformation, protecting against data loss if the process fails.
- The destination of the load step is usually a data warehouse or data mart, not the original source system.
- ELT (Extract, Load, Transform) is a different architecture where raw data is loaded first and transformed inside the destination system.

## FAQ

**What is the difference between ETL and ELT?**

In ETL, you transform the data before loading it into the destination. In ELT (Extract, Load, Transform), you load raw data into the destination first and then perform the transformation inside the destination system. ELT is common in modern data lakes and cloud data warehouses where storage and processing power are cheap.

**Do I need to know how to use specific ETL tools for the DA0-002 exam?**

No. The DA0-002 exam tests your understanding of the ETL concept, terminology, and process, not your ability to use any specific tool. You should be able to identify which step of ETL a given activity belongs to.

**What is a staging area and why is it important?**

A staging area is a temporary storage location used during the ETL process to hold raw data after extraction but before transformation. It is important because it allows you to rerun transformations without re-extracting from the source, and it provides a recovery point if the transformation step fails.

**Can ETL run in real time?**

Traditional ETL runs on a scheduled basis (e.g., nightly). However, there is a variation called 'real-time ETL' or 'streaming ETL' that processes data continuously as it arrives. The DA0-002 exam focuses on batch ETL, but you should know that real-time exists as an alternative.

**Is data validation part of the ETL process?**

Yes, data validation is typically performed during the transformation step. This includes checking for missing values, ensuring data falls within expected ranges, and confirming that data types are correct.

**What happens if an ETL job fails during the load step?**

Most ETL tools support transaction handling. If the load fails partway through, the transaction is rolled back so that the destination is not left with partially loaded data. The job can then be fixed and re-run from the point of failure, often by reusing the staged data.

## Check your understanding

1. **A company extracts customer data from its CRM system, then immediately loads it into a data warehouse without any changes. Which step of the ETL process has been skipped?**

   Answer: The transformation step has been skipped. ETL requires transformation to clean and standardise data before loading.

2. **What is the purpose of the staging area in an ETL process?**

   Answer: The staging area is a temporary storage location that holds extracted raw data before transformation, providing a recovery point and allowing transformations to be rerun without re-extracting from the source.

3. **Which type of extraction is most appropriate for a massive database that updates frequently, and why?**

   Answer: Incremental extraction is most appropriate because it only pulls records that have changed since the last extraction, making it much faster and less resource-intensive than a full extraction.

4. **Name three specific tasks that occur during the transformation step of ETL.**

   Answer: Cleaning (removing duplicates), standardisation (converting date formats), and aggregation (summarising sales by product category).

5. **What is the key difference between ETL and ELT?**

   Answer: In ETL, data is transformed before being loaded into the destination. In ELT, data is first loaded into the destination and then transformed inside it.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/data-plus/extracting-transforming-and-loading-data