ETL: Extract transform load overview: Understand the foundations
Begin your Etl course with a concrete data example, then use the six chapter sections to build and check your skills.
3 extra verified coding examples with real output
Global totals require the Hostinger MySQL backend.
What we will learn
Trace extraction, transformation and loading; validate money and identifiers; account for rejected rows and safe reruns.
Before you start
No specialist prerequisites; begin by recognizing records, fields and simple computing terms.
Technical words explained
- Extract
- Read data from a source with a known format and origin.
- Transform
- Convert or validate records according to an agreed target contract.
- Load
- Write accepted records into the destination system.
- Schema
- The declared fields, types and allowed structure of a dataset.
- Idempotency
- A property that allows safe retries without duplicating their intended effect.
- Checkpoint
- A recorded progress boundary used during recovery or restart.
- Rejected record
- Input that fails a rule and is directed to an auditable error outcome.
- Lineage
- Information that traces a value from its source through processing steps.
Subchapter index
Disclaimer: Independent educational material. Examples are limited teaching demonstrations, not production-ready security advice. Verify commands with the current product version. Read the full disclaimer.
See an ETL pipeline as three separate responsibilities
ETL stands for Extract, Transform, and Load. The acronym is short, but each stage solves a different problem. Extract means reading information from a source, such as a CSV file, a database table, or an API. Transform means applying documented rules, perhaps converting dates, standardizing field names, or rejecting invalid records. Load means writing the prepared result to a destination where another system can use it. A common beginner mistake is to put all three operations into an unexplained block of code. Separating them gives you a place to inspect source errors, a place to test business rules, and a place to verify the final rows.
Suppose one file records amounts as the strings "12.50" and "8.00". Those strings may look numerical on screen, but adding them as text does not calculate a total. A transformation must explicitly convert them to numbers. If a source contains a blank value, you must decide whether to reject the row, replace the missing amount with a documented default, or route it to an exception report. Each decision has a consequence for the final totals. Before writing code, document a source schema and a target schema. The source may be inconsistent; the target should describe what your downstream report is allowed to assume.
The example below runs a complete local ETL flow from a CSV string into a list of normalized records. It records the number of rejected input rows, rather than silently pretending every input is clean. The extraction uses Python's csv module, transformation uses the decimal module to avoid floating-point surprises for monetary values, and loading appends validated dictionaries to a destination list. The destination is intentionally local memory; in a production pipeline it might be a database table or an object store. That distinction prevents a small teaching example from making false claims about distributed deployment or transaction safety.
Once the basic pipeline is visible, ask how you would rerun it tomorrow. If the source contains the same rows twice, will your load duplicate them? Does the pipeline have a stable identifier to detect already processed records? What happens if it fails halfway through? Those questions lead to idempotency, checkpoints, error queues, and observability. They are not advanced decoration; they are how you keep a nightly report from quietly doubling its numbers. The first lesson should teach you to count what entered, what failed, what was transformed, and what arrived at the destination.
Practical Etl exercise: execute the starting case
EXECUTED Python 3 EXAMPLE — standard library; no cloud or database server required unless stated
import csv, io
from decimal import Decimal
source = "id,amount\nA1,12.50\nA2,8.00\nA3,invalid\n"
rows = list(csv.DictReader(io.StringIO(source)))
loaded, rejected = [], []
for row in rows:
try:
loaded.append({"id": row["id"], "amount": Decimal(row["amount"])})
except Exception:
rejected.append(row["id"])
print("Extracted:", len(rows))
print("Loaded:", len(loaded), "Rejected:", rejected)
print("Total:", sum(item["amount"] for item in loaded))Captured Etl exercise output
Extracted: 3 Loaded: 2 Rejected: ['A3'] Total: 20.50
Explanation: The source contains three CSV records. Two amounts are valid decimals and one is the word invalid, so two rows load and A3 is rejected. Decimal preserves exact decimal arithmetic for the displayed total.
1. Trace what extraction actually guarantees
Trace what extraction actually guarantees
Extraction is the act of reading source data into a form the pipeline can examine. That might mean reading a CSV file, querying a database or receiving records from an approved API. It does not mean the input is already correct. A source file can have an unexpected delimiter, missing header, incompatible character encoding or duplicate identifier. Before starting, record where the data came from and what each input row is supposed to represent. The pipeline cannot restore information that was never available in the source.
Read a tiny CSV containing three purchase records and identify which values are text even when they look numeric. If the amount column contains 12.50, the file's character sequence must be converted into a numerical type before arithmetic. Also note whether the extraction can be repeated: a network source might change between requests, while a versioned file can be more stable. Extracting the same raw data consistently is the starting point for meaningful quality checks later in the workflow.
2. Define transformation rules that can be tested
Define transformation rules that can be tested
Transformations convert raw input into values that meet the target contract. Examples include trimming surrounding whitespace, parsing a timestamp in a known timezone and turning a monetary string into a decimal. A transformation should not silently guess a replacement for an invalid amount. Decide whether that record belongs in an error report, is allowed with a documented missing value, or should stop the whole job. The rules need to be written clearly enough for another person to reproduce the same output.
A useful teaching case converts three order rows, one of which has a malformed amount. The expected result is not necessarily three loaded rows: the pipeline should report how many were accepted and why one was rejected. Financial amounts are a good reason to introduce Python's Decimal instead of relying on binary floating-point display to imply exact decimal arithmetic. Store the original value in an error report so a later investigation can reconstruct what the source actually contained.
3. Load records without creating invisible duplicates
Load records without creating invisible duplicates
Loading writes transformed data to a destination, which might be an in-memory list for a first exercise or a real database for production work. The destination must have a schema and clear rules for identity. If the same batch is run twice, appending every record blindly can double the counts. An idempotent load is designed so that a permitted retry does not duplicate logically identical records. Beginners can model this by keeping a set of already loaded order IDs before appending new rows.
Idempotency is not the same as ignoring every repeated value. Two separate events can legitimately have the same amount and customer; their unique event identifiers distinguish them. Define what makes a record the same before you choose a deduplication rule. In production, transactions, unique keys or merge operations may provide stronger guarantees than a local Python set. A toy destination should be described as a teaching model rather than a database with durability and concurrency guarantees.
4. Measure the pipeline by stage
Measure the pipeline by stage
A successful log message is not enough evidence that an ETL process handled the source correctly. Record four numbers: rows extracted, rows accepted by transformation, rows rejected with reasons, and rows loaded into the target. If a source file contains a header and three data rows, count the data rows rather than treating the header as a transaction. Compare totals at the boundaries and explain any deliberate exclusions. A useful rule is that every input record should have an accountable outcome.
For a monetary dataset, compare the sum of accepted input amounts with the sum written to the destination. If they differ, decide whether a conversion, filter or duplicate-handling rule caused the change. Do not merely compare row counts; a pipeline can have the correct number of rows but incorrect values. Build your verification from known examples and maintain a small set of negative tests for malformed amounts and missing identifiers.
5. Handle failures and restarts deliberately
Handle failures and restarts deliberately
An ETL job can fail during extraction, halfway through transformation or after writing some target rows. Each failure point requires a different recovery strategy. A network timeout may justify retrying an extraction, whereas an invalid value may require a permanent rejection with a human-readable explanation. A load that partially committed must be able to resume without blindly duplicating its first half. Checkpoints record progress, but they must be defined carefully: a checkpoint is useful only if the corresponding destination state is consistent.
A beginner can simulate a failure after the second order and ask what the next run should do. If records carry stable IDs and the loader checks them, the restart can skip confirmed items safely. In a real system, distributed workers and transactions make the problem more complex. The essential first lesson is to distinguish an expected bad record from an operational failure and design the pipeline so neither disappears silently.
6. Build a miniature quality-controlled ETL job
Build a miniature quality-controlled ETL job
Write a small extractor for a three-row CSV and a transformer that converts amounts to Decimal, rejects invalid rows and normalizes identifiers. Use a list as the destination, explicitly labeled as an in-memory teaching output. Print extracted, loaded and rejected counts and an aggregate total. Then change the source to include one repeated order ID and document the policy for deciding whether it is a duplicate or an intentional second event. This challenge makes the entire three-stage sequence visible.
Explain why the same code cannot claim to have loaded BigQuery, MySQL or Hadoop unless those systems were actually contacted. Learning ETL is not memorizing a particular product's API; it is understanding the data contract, stage boundaries, failure behavior and evidence that the final result is correct. Subsequent chapters can replace the local source and destination with real systems without losing that reasoning discipline.
Hands-on code lab — executed examples, real output and explanations
This chapter adds 3 self-contained examples related to Extract transform load overview. Their output was obtained by running the source code when this package was built, not invented. When a cloud service or distributed database is required, local models are explicitly marked and do not claim to replace that service. Read the explanation after each output, then change an input and run the downloaded source yourself.
Download runnable Python source for this chapter
Example 1 — Extract CSV, transform types, and summarize
RUNNABLE SOURCE · Python 3 · standard library
import csv, io
csv_text = "id,amount,status\na,1,paid\nb,3,pending\nc,2,paid\n"
rows = list(csv.DictReader(io.StringIO(csv_text)))
paid = [{"id":r["id"],"amount":int(r["amount"])} for r in rows if r["status"] == "paid"]
print("extracted:", len(rows))
print("paid:", paid)
print("total:", sum(r["amount"] for r in paid))ACTUAL OUTPUT · EXECUTED DURING BUILD
extracted: 3
paid: [{'id': 'a', 'amount': 1}, {'id': 'c', 'amount': 2}]
total: 3Verified locally; no online service required
Example 2 — Deduplicate by a stable business key
RUNNABLE SOURCE · Python 3 · standard library
events = [{"id":"a","value":1},{"id":"b","value":3},{"id":"a","value":2}]
latest = {row["id"]: row["value"] for row in events}
print("deduplicated:", sorted(latest.items()))
print("distinct IDs:", len(latest))ACTUAL OUTPUT · EXECUTED DURING BUILD
deduplicated: [('a', 2), ('b', 3)]
distinct IDs: 2Verified locally; no online service required
Example 3 — Reject invalid source values explicitly
RUNNABLE SOURCE · Python 3 · standard library
raw = ["1", "unknown", "3", "", "2"]
valid, rejected = [], []
for item in raw:
try: valid.append(int(item))
except ValueError: rejected.append(item)
print("valid:", valid)
print("rejected:", rejected)
print("sum:", sum(valid))ACTUAL OUTPUT · EXECUTED DURING BUILD
valid: [1, 3, 2] rejected: ['unknown', ''] sum: 6
Verified locally; no online service required
Practice — 10 tasks with hints and explained answers
Exercise 1: Name the input and output — Extract transform load overview
Hint: Identify one data field and one resulting value
Solution and explanation: A data contract should specify the input field, its type, the transformation and the expected output. In ETL, show how it affects a validated activity record.
Exercise 2: Explain a technical term — Extract transform load overview
Hint: Connect the term to a real record
Solution and explanation: Use a small example from the chapter and show which step depends on the definition. In ETL, show how it affects a validated activity record.
Exercise 3: Predict the normal result — Extract transform load overview
Hint: Trace the displayed example line by line
Solution and explanation: Calculate or identify the selected values manually before running the operation. In ETL, show how it affects a validated activity record.
Exercise 4: Test an empty input — Extract transform load overview
Hint: Ask whether empty means valid or invalid
Solution and explanation: Return an empty valid result if the contract permits; otherwise report a documented validation error. In ETL, show how it affects a validated activity record.
Exercise 5: Change a field type — Extract transform load overview
Hint: Consider string versus numeric data
Solution and explanation: Explicitly validate or convert values and describe what happens on an unsupported type. In ETL, show how it affects a validated activity record.
Exercise 6: Detect a duplicate — Extract transform load overview
Hint: Use a stable record identifier
Solution and explanation: Check whether duplicate processing should be prevented or counted under an explicit event policy. In ETL, show how it affects a validated activity record.
Exercise 7: Investigate a missing value — Extract transform load overview
Hint: Distinguish missing from null and empty
Solution and explanation: State which cases are acceptable, rejected or filled under a documented rule. In ETL, show how it affects a validated activity record.
Exercise 8: Design a negative test — Extract transform load overview
Hint: Break exactly one assumption
Solution and explanation: Keep the expected failure observable and do not expose internal secrets. In ETL, show how it affects a validated activity record.
Exercise 9: Plan a performance experiment — Extract transform load overview
Hint: Choose one variable to measure
Solution and explanation: Record a baseline and increase input volume without changing correctness criteria. In ETL, show how it affects a validated activity record.
Exercise 10: Build a small feature — Extract transform load overview
Hint: Include a result and a failure path
Solution and explanation: Document data, code, output, tests, and at least one deployment limitation. In ETL, show how it affects a validated activity record.
Test your knowledge — 20 answered questions
Question 1. What is the chapter topic?
Answer and explanation: Extract transform load overview and its data operations.
Question 2. What skill is being practiced?
Answer and explanation: Understand the foundations.
Question 3. What is a data contract?
Answer and explanation: It states inputs, types, output shape and failure behavior.
Question 4. What is an invariant?
Answer and explanation: A rule that must remain true, such as retaining a valid identifier.
Question 5. Why inspect data types?
Answer and explanation: Strings, numbers, nulls and timestamps can behave differently.
Question 6. Can an empty result be valid?
Answer and explanation: Yes, if no records match and the contract permits an empty result.
Question 7. What is a normal test?
Answer and explanation: A permitted input with a predictable result.
Question 8. What is a negative test?
Answer and explanation: An invalid input intended to check a safe rejection path.
Question 9. Why use a small example?
Answer and explanation: It makes intermediate values and assumptions inspectable.
Question 10. Why compare predicted and actual output?
Answer and explanation: The first difference locates a mistaken assumption or implementation.
Question 11. Why examine missing fields?
Answer and explanation: Missing and null may have different meanings and consequences.
Question 12. Why check duplicate input?
Answer and explanation: Retries and repeated records can inflate counts or duplicate side effects.
Question 13. Is UI hiding sufficient authorization?
Answer and explanation: No, authorization must be enforced on the backend or service rules.
Question 14. What is lineage?
Answer and explanation: A record of where data came from and how it was changed.
Question 15. Why document prerequisites?
Answer and explanation: Examples may need a server, credentials, SDK or specific runtime.
Question 16. Should secrets be in public code?
Answer and explanation: No, store service credentials in protected configuration.
Question 17. What is a performance baseline?
Answer and explanation: A measured reference result for a defined workload and environment.
Question 18. Does a three-row test prove scalability?
Answer and explanation: No, it checks logic under a tiny input, not realistic throughput.
Question 19. What should a project report include?
Answer and explanation: Input shape, contract, implementation, expected and actual output, tests and limitations.
Question 20. How does this chapter connect to later work?
Answer and explanation: Its treatment of extract transform load overview underpins more complex ETL data workflows.
Common mistakes
Do not equate successful syntax with correct meaning. In extract transform load overview, test missing values, duplicates, authorization rules, data types and realistic costs before scaling the example. Avoid assuming that the illustrative output is a verified result from a cloud account or production server.
What we learned
You can describe extract transform load overview using the correct technical words, trace a sample, interpret an expected result, compare a coding and non-coding demonstration, design negative tests and document an applied result.
Apply your knowledge
Independent challenge: Add a small feature to a daily tutorial activity pipeline. Specify the expected record shape, implement a valid case, show its output, reject one invalid case and explain how to verify it again after a code change.
Next skill
Continue to Chapter 2 to extend this skill with another design decision.