Hands-On Course: Building ETL Pipelines in Python
Simple ETL Pipelines is an advanced-level course of 6 lessons (about 3 h 49 min), with 18 exercises and 18 quiz questions. Prerequisites: Data Cleaning and Preparation, Advanced SQL (CTEs, window functions). Exercises run in the browser, with nothing to install.
- Data analysis
- Advanced
- 6 lessons
- 3 h 49 min
- 18 exercises
- Exercises run in the browser
The course in brief
This course teaches you to automate data preparation with robust ETL pipelines in Python, pandas and SQLite. You will extract data from various sources, write pure, testable transformations, set up data quality checks with quarantine, load idempotently using upsert and watermarks, then orchestrate everything with logging, retries and an execution report.
What you will be able to do
- Design an ETL or ELT pipeline split into testable, idempotent functions
- Extract CSV, nested JSON and SQL data while controlling types and formats
- Write pure transformations and safe joins
- Set up data quality checks with quarantine and blocking thresholds
- Load idempotently (upsert, partition) and incrementally (watermark) inside transactions
- Orchestrate a pipeline with logging, retries, an execution report and an end-to-end test
Syllabus: 6 lessons
- Describe the extract, transform and load steps and the difference between ETL and ELT
- Structure a pipeline into testable functions
- Explain and verify the idempotency of a process
- 02
Extraction: CSV, JSON and SQL Databases
- Read French-format CSV files while enforcing types
- Flatten nested JSON data with json_normalize
- Extract incrementally from a SQL database with a parameterized query
- 03
Transformation: Cleaning, Typing and Safe Joins
- Write a pure transformation function composed of readable steps
- Normalize text, categories and units
- Make joins safe with validate and indicator
- 04
Data Quality Checks and Quarantine
- Write data quality checks: schema, uniqueness, completeness, ranges, reference lists
- Separate valid rows from rejected rows with a reason
- Define blocking thresholds to stop the pipeline
- 05
Loading: Upsert, Transactions and Incremental Loads
- Load data into an SQL database with an explicit schema
- Write an idempotent load using upsert (INSERT ... ON CONFLICT)
- Handle an incremental load with a watermark and transactions
- 06
Orchestration, Logging and Reliability
- Assemble a complete pipeline that produces an execution report
- Log with the logging module and measure each step
- Handle transient errors with retries and schedule execution
Final project
Daily Pipeline for an Online Shop's Orders
Build in Python a complete, replayable pipeline that feeds an analytical SQLite database from three sources simulated in memory: a French-format CSV export of orders, a nested JSON response from a payment API and an SQL table of customers. The pipeline must extract incrementally, transform, check quality, quarantine, load by upsert and produce a historized execution report.
Deliverables
- A Python module organized into the functions extraire, transformer, controler, charger and executer_pipeline
- The SQL schema of the target, quarantine, control (watermark) and execution history tables
- A test suite (assert or pytest) covering each step, a double run and a failure case
- A demonstration: three successive runs with new data, corrected data and a defective batch
- A one-page operations note: scheduling, alerts, procedure for replaying a past day
Assessment criteria
- The pipeline is idempotent and replayable by date
- Types, formats and joins are controlled, with no silent duplication
- Quality checks are declarative, rejects are traced and blocking thresholds are respected
- Loading and the watermark are transactional
- The log and the execution report make each run understandable without reading the code
Frequently asked questions
What is the difference between ETL and ELT?
In ETL, data is transformed before being loaded into the destination. In ELT, it is loaded raw into the warehouse and then transformed in place with SQL. The reliability principles in this course apply to both.
Do I need to know an orchestrator such as Airflow?
No. The course builds orchestration in plain Python to teach the principles (report, logging, retries), then presents the role of cron and of the orchestrators on the market.
What are the prerequisites?
Knowing how to clean data with pandas and being comfortable with advanced SQL (joins, CTEs, window functions). The exercises use pandas and in-memory SQLite.
What is an idempotent pipeline?
It is a pipeline you can rerun on the same data without changing the final result or creating duplicates, thanks to partition replacement or upsert.
This course, told for your job
Say what you want to be able to do: “Simple ETL Pipelines” takes its place in your path, with examples from your field. The concepts stay those of the course.