Automated ETL Pipeline
Last reviewed
IntermediateWhat you'll learn
~40 min- Build a Python CLI that extracts data from multiple source formats
- Apply SQL transformations including JOINs, aggregations, and deduplication
- Load cleaned data into SQLite with schema validation
- Generate a data quality report flagging nulls, duplicates, and type mismatches
What you’re building
In Lesson 4, you built a report generator that turns a single CSV into a polished executive report. But where does that CSV come from? In any real organization, data lives in multiple places: a CSV export from the accounting system, a JSON feed from the CRM, a spreadsheet from the regional office that uses completely different column names. Before you can analyze anything, you need to merge, clean, and standardize that data. That process has a name: ETL — Extract, Transform, Load.
ETL is core MIS curriculum. Every data warehousing course, every BI capstone, every enterprise systems class circles back to the same problem: how do you get data from Point A, clean it up, and put it in Point B so people can actually use it? Tools like Informatica, SSIS, and dbt exist because this problem is that common.
In this lesson you will build a Python CLI tool that extracts data from multiple source formats (CSV, JSON, and a stubbed REST API), applies SQL transformations (JOINs, aggregations, deduplication), loads the result into a SQLite database, and generates an HTML data quality report. One command, multiple sources in, one clean database out.
SQLite is a file-based database that requires zero configuration, zero server setup, and zero network access. You write standard SQL, and the database is a single .db file you can copy, email, or commit to Git. For learning ETL, it removes every obstacle between you and the SQL. In production, the same SQL patterns transfer directly to PostgreSQL, MySQL, or SQL Server — the only differences are dialect-specific syntax.
This lesson builds on Lesson 4’s Python CLI pattern. If you completed Lesson 4, you already have Python 3 and pip installed. If not, verify with python3 --version and install from python.org if needed.
The showcase
When finished, your CLI tool will:
- Accept multiple input files (CSV, JSON) and an optional REST API endpoint stub via command-line arguments.
- Read a YAML configuration file that defines column mappings, type coercions, join keys, and aggregation rules.
- Extract data from each source into a normalized in-memory representation.
- Transform using SQL logic executed in SQLite:
- JOINs across sources (e.g., orders + customers + products).
- GROUP BY aggregations (revenue by region, order counts by customer).
- Deduplication using DISTINCT and window functions (ROW_NUMBER to keep the most recent record per key).
- Column renaming, type coercion, and null handling.
- Load the transformed data into a SQLite database with schema validation, upsert support (INSERT OR REPLACE), and foreign key enforcement.
- Generate an HTML data quality report showing:
- Null counts per column (with percentage).
- Duplicate row counts (exact duplicates and near-duplicates by key columns).
- Type mismatch counts (values that do not match the expected column type).
- Row counts per source file.
- A summary pass/fail status for each quality check.
Think of it as the plumbing that connects raw data sources to the dashboards and reports you built in earlier lessons.
In data warehousing courses, you learn the difference between operational databases (where transactions happen) and analytical databases (where reporting happens). ETL is the bridge between them. Every night, ETL pipelines in companies like Amazon, Walmart, and your bank extract transaction data from dozens of operational systems, transform it into a consistent schema, and load it into a data warehouse where analysts run queries. Building one yourself — even at a small scale — gives you concrete understanding of what tools like Informatica, Talend, and dbt automate at enterprise scale.
Before you start
First, if your AI tool is still open, leave it: type /exit and press Enter (if you use Antigravity, press Ctrl+D with nothing typed instead), and wait until you see the $ or % prompt. Make a clean folder for this lesson inside your course folder:
mkdir -p ~/mis-workbench/05-etl-pipeline && cd ~/mis-workbench/05-etl-pipelineOn Windows PowerShell, run mkdir -Force ~/mis-workbench/05-etl-pipeline; cd ~/mis-workbench/05-etl-pipeline, or open the folder in your desktop agent app. Then start your agent the way your track’s setup lesson showed.
Switch to plan mode
Once your agent is open, switch to Plan mode inside the session (Shift+Tab, /plan, or your desktop app’s mode picker). In plan mode it asks clarifying questions and shows you a plan before building.
The conversation
Each message below is an example. Say it in your own words.
Say what you want
Start with a short plain-English description of what you want to build:
I want to build a command-line ETL pipeline driven by config.yaml that extracts CSV, JSON, and API data, runs SQL transforms, loads a SQLite database, and writes an HTML quality report. Ask me what you need first.Answer its questions
Your agent will ask a few questions about the sources, transformations, and report. Answer each briefly:
| If it asks | You might say |
|---|---|
| What language and command-line options should it use? | Use standard Python data tools, with options for config, output database, quality report, dry-run, and verbose. |
| What sample datasets should we include to test the pipeline? | Include sample_data/orders.csv (105 rows with 5 duplicates), customers.json (30 records with nulls and mixed case), and products.csv (20 rows). |
| What SQL transformations and output tables should the pipeline build? | Build order_details, revenue_by_region, customer_summary, and category_performance after cleaning text casing and deduplicating orders by order_id. |
| How should loading into the target SQLite database work? | Validate columns, enforce primary and foreign keys, and update existing rows cleanly so re-running never duplicates data. |
| What should the HTML data quality report display? | Source row counts, per-column null percentages, duplicates removed, type mismatches, foreign key checks, and pass/warn/fail status. |
Read the plan
Before approving, check the plan for four domain essentials (your agent will typically propose Python with pandas, pyyaml, requests, sqlite3, and jinja2):
- Deduplication and cleaning run before JOINs. Staging tables standardize text casing (like
"north"to"North"), convert null synonyms ("N/A","","null") to SQLNULL, and remove the 5 duplicate orders usingROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_date DESC)before joining. - SQL transforms are driven by
config.yaml. The YAML configuration defines the sources, cleaning rules, dedup rules, and SQL queries fororder_details,revenue_by_region,customer_summary, andcategory_performance. - Loading is idempotent and enforces integrity. Target tables use
INSERT OR REPLACEinside transactions withPRAGMA foreign_keys = ON, so running the pipeline twice produces 100order_detailsrows instead of 200. - The HTML quality report audits every stage. It compares extracted versus loaded row counts, flags null percentages above the configured threshold, and reports duplicates removed and foreign key checks.
If the first plan runs JOINs before deduplication or hardcodes the SQL instead of reading config.yaml, say so briefly:
Deduplicate the staging tables before running any JOIN queries, and keep the transform queries and cleaning rules in config.yaml.When the plan covers all four, approve it: pick the option to proceed or say Yes, go ahead and build it (in Codex, press Shift+Tab first to leave plan mode). Once you verify the pipeline runs and produces warehouse.db and quality_report.html, ask your agent to save a Git checkpoint:
Save a checkpoint with Git so we can roll back if needed.In ETL work, the pipeline configuration is the specification. The code is generic plumbing; the config defines what happens. This separation means you can add new data sources, new transformations, and new quality checks by editing YAML — not by writing new code. That principle scales from this learning project to enterprise ETL platforms.
What you get
Ask your agent to set up the Python environment, install the required packages, and run the pipeline on config.yaml:
Set up a virtual environment, install what the pipeline needs, and run it with config.yaml to create warehouse.db and quality_report.html.If you prefer running the commands yourself in a terminal instead (on Windows PowerShell, activate the environment with .venv\Scripts\Activate.ps1 instead of source .venv/bin/activate):
cd etl-pipeline# Ubuntu / WSL, once: sudo apt install python3 python3-venvpython3 -m venv .venvsource .venv/bin/activatepip install -e .python -m etl_pipeline --config config.yaml --output warehouse.db --report quality_report.html --verboseExpected output
The terminal prints a stage-by-stage log output like this:
REFERENCE OUTPUT Sample stage-by-stage ETL pipeline terminal output open reference hide reference Reference example of the log output printed when the ETL pipeline finishes. Open to compare your run against each stage.
[EXTRACT] orders.csv: 105 rows, 8 columns (CSV)[EXTRACT] customers.json: 30 rows, 7 columns (JSON)[EXTRACT] products.csv: 20 rows, 6 columns (CSV)[CLEAN] Standardized 4 region values to title case in stg_customers[CLEAN] Standardized 2 status values to lower case in stg_orders[CLEAN] Replaced 3 null synonyms with NULL in stg_customers.email[DEDUP] Removed 5 duplicate rows from stg_orders (window: order_id, order_date desc)[DEDUP] 0 duplicates found in stg_customers (distinct)[DEDUP] 0 duplicates found in stg_products (distinct)[TRANSFORM] order_details: 100 rows (JOIN orders + customers + products)[TRANSFORM] revenue_by_region: 4 rows (GROUP BY region)[TRANSFORM] customer_summary: 30 rows (GROUP BY customer)[TRANSFORM] category_performance: 4 rows (GROUP BY category)[LOAD] order_details: 100 rows loaded (upsert)[LOAD] revenue_by_region: 4 rows loaded (upsert)[LOAD] customer_summary: 30 rows loaded (upsert)[LOAD] category_performance: 4 rows loaded (upsert)[VERIFY] Foreign key integrity: PASS[REPORT] Data quality report written to quality_report.html[DONE] Pipeline completed in 1.2s -- 4 tables, 138 rows loadedVerify the database
Ask your agent to query warehouse.db and verify the structural invariants, or open the database yourself with the SQLite command-line tool (sqlite3 warehouse.db):
Query warehouse.db to confirm order_details has 100 deduplicated rows with 0 duplicate order_ids, revenue_by_region includes all 4 regions, and customer_summary lists the top 5 customers.If you inspect warehouse.db directly with sqlite3 warehouse.db, check those same invariants (exact dollar totals will vary with the sample rows your agent generated, but the row counts and uniqueness invariants must hold):
REFERENCE SQL SQLite verification queries for warehouse.db open reference hide reference Reference SQL queries your agent can run for you (or you can run in sqlite3 warehouse.db) to verify row counts and invariants.
.tables-- order_details revenue_by_region customer_summary category_performance
SELECT COUNT(*) AS total_rows, COUNT(DISTINCT order_id) AS unique_ordersFROM order_details;-- 100|100 (100 deduplicated rows after dropping 5 duplicates; 0 duplicate order_ids)
SELECT region, order_count, ROUND(total_revenue, 2) AS total_revenueFROM revenue_by_region ORDER BY region;-- 4 rows: East, North, South, West (all four regions represented, order_count summing to 100)
SELECT customer_name, total_orders, ROUND(lifetime_value, 2) AS lifetime_valueFROM customer_summary ORDER BY lifetime_value DESC LIMIT 5;-- (Top 5 customers ranked by lifetime_value)
.quitThen open quality_report.html in your browser. You should see:
- Source summary showing 3 files extracted, row counts, and formats.
- Null analysis with green/yellow/red indicators — the email column in customers should show yellow (a few nulls).
- Duplicate analysis showing 5 duplicates removed from orders.
- Pipeline summary with overall PASS or WARNING status.
The most common issue with a freshly generated ETL pipeline is incorrect JOINs. After running, verify that order_details has the expected row count (100 after deduplication, not 105). If the count is higher, the JOIN is producing a cartesian product — a sign that the join keys are wrong or that deduplication ran after the JOIN instead of before. Run SELECT COUNT(*) FROM order_details; to verify.
When things go wrong
ETL pipelines introduce a specific category of issues: encoding problems, schema mismatches, and data integrity failures. Here is how to diagnose the most common problems.
When Things Go Wrong
Use the Symptom → Evidence → Request pattern: describe what you see, paste the error, then ask for a fix.
How it works
The ETL pipeline follows a strict four-stage architecture:
-
CLI (
cli.py) parses arguments with argparse, loads and validates the YAML config, and orchestrates the pipeline stages in order: extract, transform, load, report. Each stage receives the config and passes its output to the next stage. -
Extractor (
extractor.py) has one function per source type.extract_csv()usespd.read_csv()with configurable encoding and null handling.extract_json()usespd.read_json()orjson.load()with optional flattening.extract_api()usesrequests.get()with retry logic. Each function returns a DataFrame and metadata dict. -
Transformer (
transformer.py) creates an in-memory SQLite database, loads all extracted DataFrames into staging tables usingdf.to_sql(), then executes the SQL transformations defined in the config. This is where the real work happens: JOINs combine data from multiple sources, GROUP BY aggregations summarize it, and window functions deduplicate it. The transformer returns a dict of DataFrames (one per output table). -
Loader (
loader.py) creates the target SQLite database, defines the schema (CREATE TABLE with constraints), and inserts the transformed data in batches. Upsert logic (INSERT OR REPLACE) makes the pipeline idempotent — you can run it twice and get the same result. -
Quality Report (
quality_report.py) analyzes the extracted and loaded data for quality issues, then renders the Jinja2 HTML template with the results. This is the audit trail that tells you whether the pipeline worked correctly.
🔍ETL vs. ELT: The modern data warehouse patternclick to expand
The pipeline you built follows the classic ETL pattern: Extract data, Transform it in a staging area, then Load the clean result into the target database. This pattern is decades old and works well when transformation logic is complex and you want to validate data before it enters the warehouse.
Modern cloud data warehouses (Snowflake, BigQuery, Databricks) have flipped this to ELT: Extract data, Load it raw into the warehouse, then Transform it using SQL inside the warehouse. Why? Cloud warehouses have massive parallel processing power, so running transformations inside the warehouse is faster than doing it in an external tool.
The key difference:
- ETL: Transform happens outside the database (in Python, Informatica, SSIS). The database receives clean data.
- ELT: Transform happens inside the database (using dbt, SQL views, stored procedures). The database receives raw data and cleans it itself.
Tools for each approach:
- ETL: Informatica, SSIS, Talend, Apache NiFi, custom Python scripts (what you built)
- ELT: dbt (data build tool), Snowflake Tasks, BigQuery scheduled queries, Databricks notebooks
Your pipeline is actually a hybrid: it uses SQLite as the transformation engine (SQL transformations inside a database) but loads the transformed results into a separate target database. This is close to how dbt works — dbt writes SQL transformations that run inside the warehouse, and the results become new tables.
Why this matters for MIS careers: If you interview at a company using Snowflake or BigQuery, they will expect you to know the ELT pattern. If you interview at a company using SQL Server or Oracle, they will expect ETL. Knowing both patterns — and when each is appropriate — makes you versatile.
🔍Connection to enterprise toolsclick to expand
The pipeline you built is a miniature version of what enterprise ETL tools do at scale:
- Apache Airflow: An open-source orchestrator that schedules and monitors ETL pipelines. Your
cli.pyis a single-run version of what Airflow’s DAGs (Directed Acyclic Graphs) do across hundreds of tasks. - dbt (data build tool): Transforms data using SQL inside a data warehouse. Your
transformer.pySQL queries are essentially dbt models. - Informatica / SSIS / Talend: Visual ETL tools where you drag-and-drop extraction, transformation, and loading steps. Your YAML config is the text-based equivalent of their visual pipeline definitions.
- Apache Spark: Distributed data processing for datasets too large for a single machine. Your pandas DataFrames are the single-machine equivalent of Spark DataFrames.
The concepts are identical across all of these: extract from sources, stage the raw data, apply transformations, load into a target, validate quality. The tools differ in scale, interface, and ecosystem — but if you understand the pipeline you just built, you can learn any of them quickly.
Customize it
Add incremental loading
Add incremental loading that tracks a high-water mark column in a metadata table and only extracts newer rows, plus a --full-refresh flag.Add data lineage tracking
Add data lineage columns (_source_file, _extracted_at, _pipeline_run_id, _transform_steps) to every loaded row, plus a pipeline_runs audit table.Add schema migration support
Automatically run ALTER TABLE ADD COLUMN for new config columns, write a .sql migration script for column type changes, and track a schema_versions table.Add PostgreSQL adapter
Add a --target flag supporting either SQLite or PostgreSQL, using safe upserts and fast bulk loading when PostgreSQL is selected.If you enjoy building ETL pipelines more than building dashboards, you are discovering the data engineering career path. Data engineers build and maintain the pipelines that feed data to analysts and scientists. The role is directly aligned with MIS skills: SQL, database design, systems thinking, and process automation. The tools are different (Airflow, Spark, dbt, cloud platforms), but the fundamentals are exactly what you practiced in this lesson.
Try it yourself
- Build the ETL pipeline by talking your agent through the plan above.
- Run it on the included sample data and verify the output database.
- Open the data quality report. Are there any warnings? Investigate each one by querying the SQLite database directly.
- Open
warehouse.dbin SQLite and run some queries:- Which region has the highest revenue? (
SELECT * FROM revenue_by_region ORDER BY total_revenue DESC LIMIT 1;) - Who are the top 5 customers by lifetime value? (
SELECT * FROM customer_summary ORDER BY lifetime_value DESC LIMIT 5;) - Which product category has the most orders? (
SELECT * FROM category_performance ORDER BY total_orders DESC LIMIT 1;)
- Which region has the highest revenue? (
- Edit the sample data to introduce a new quality issue: add a row to
orders.csvwith acustomer_idthat does not exist incustomers.json. Re-run the pipeline. Does the quality report catch it? - Add a new data source: create a
returns.csvfile with columnsorder_id,return_date,reason,refund_amount. Editconfig.yamlto extract it, join it with order_details, and create areturn_analysisaggregation table. - Try the
--dry-runflag. It should validate everything without writing to the database.
Key Takeaways
- ETL is the most common data workflow in business. Before anyone can build a dashboard, write a report, or train a model, someone has to extract data from sources, clean it up, and put it somewhere useful. That someone is often an MIS professional.
- SQL is the transformation language. The JOINs, GROUP BYs, and window functions you used in the transformer are the same SQL you learned in database courses. ETL is where that SQL knowledge becomes directly productive.
- Configuration separates pipeline logic from pipeline definition. The YAML config defines what data to extract, how to transform it, and where to load it. The Python code is generic. This separation means non-developers can modify the pipeline by editing YAML, which is a key design principle in enterprise tools.
- Data quality is not optional. The quality report is not a nice-to-have — it is the proof that your pipeline worked. In production, pipelines that load bad data cause more damage than pipelines that fail loudly. Always validate.
- Idempotency makes pipelines safe. Using INSERT OR REPLACE means you can run the pipeline multiple times without duplicating data. This is critical for production pipelines that run on a schedule — if a run fails halfway, you re-run it from the start and get the correct result.
Your ETL pipeline joins orders with customers and produces 150 rows in the order_details table. But the orders table only has 100 rows after deduplication. What is the most likely cause?
What’s next
In the final lesson of this module, you will build a Scheduled Business Operations Orchestrator that chains this ETL pipeline with the report generator from Lesson 4 and adds automated scheduling, error handling, and notification. It is the capstone that connects every tool you have built into a single automated workflow.