Applied AI-Powered Business Tools

Automated ETL Pipeline

Last reviewed

Intermediate

What 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.

ℹWhy SQLite?

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.

ℹPrerequisites

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.

ℹMIS Connection: Data Warehousing and ETL

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:

Terminal window
mkdir -p ~/mis-workbench/05-etl-pipeline && cd ~/mis-workbench/05-etl-pipeline

On 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 asksYou 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):

  1. Deduplication and cleaning run before JOINs. Staging tables standardize text casing (like "north" to "North"), convert null synonyms ("N/A", "", "null") to SQL NULL, and remove the 5 duplicate orders using ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_date DESC) before joining.
  2. SQL transforms are driven by config.yaml. The YAML configuration defines the sources, cleaning rules, dedup rules, and SQL queries for order_details, revenue_by_region, customer_summary, and category_performance.
  3. Loading is idempotent and enforces integrity. Target tables use INSERT OR REPLACE inside transactions with PRAGMA foreign_keys = ON, so running the pipeline twice produces 100 order_details rows instead of 200.
  4. 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.
💡The config.yaml is the real specification

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):

Terminal window
cd etl-pipeline
# Ubuntu / WSL, once: sudo apt install python3 python3-venv
python3 -m venv .venv
source .venv/bin/activate
pip install -e .
python -m etl_pipeline --config config.yaml --output warehouse.db --report quality_report.html --verbose

Expected 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 loaded

Verify 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_orders
FROM 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_revenue
FROM 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_value
FROM customer_summary ORDER BY lifetime_value DESC LIMIT 5;
-- (Top 5 customers ranked by lifetime_value)
.quit

Then open quality_report.html in your browser. You should see:

  1. Source summary showing 3 files extracted, row counts, and formats.
  2. Null analysis with green/yellow/red indicators — the email column in customers should show yellow (a few nulls).
  3. Duplicate analysis showing 5 duplicates removed from orders.
  4. Pipeline summary with overall PASS or WARNING status.
⚠Check the JOIN results

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.

Symptom
UnicodeDecodeError when reading CSV files
Evidence
python -m etl_pipeline --config config.yaml --output warehouse.db gives: UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 42: invalid continuation byte
What to ask the AI
"The pipeline fails with a character encoding error when reading a CSV exported from another system. Add automatic fallback for common legacy encodings (like Latin-1 and Windows-1252) and let me optionally set a file's encoding in config.yaml."
Symptom
Date columns parsed as strings after extraction
Evidence
The order_date column in the orders table shows values like '2024-03-15' but SQLite treats them as TEXT, not DATE. Queries like WHERE order_date > '2024-06-01' do string comparison instead of date comparison.
What to ask the AI
"Date columns are being stored in raw mixed formats so date range queries do not sort or filter properly. Convert all date columns to standard YYYY-MM-DD format during cleaning and allow config.yaml to list which columns hold dates."
Symptom
Foreign key constraint violations when loading
Evidence
The loader fails with: sqlite3.IntegrityError: FOREIGN KEY constraint failed when loading order_details. Some orders reference customer_ids that do not exist in the customers table.
What to ask the AI
"Loading order_details fails with a foreign key error because some orders reference customer IDs missing from customers.json. Check for orphaned IDs before loading, report them in the quality report, and add a config option to skip or fail on orphaned rows."
Symptom
Deduplication removes too many or too few rows
Evidence
The dedup step says it removed 15 rows from orders, but the sample data only has 5 intentional duplicates. Or it says 0 duplicates found when there should be 5.
What to ask the AI
"Deduplication on orders is removing the wrong number of rows. Group duplicate orders by order_id and keep only the row with the latest order_date, leaving 100 unique orders from the 105 input rows. Log the removed duplicates when --verbose is on."
Symptom
Aggregation query produces incorrect totals due to duplicate joins
Evidence
revenue_by_region shows total_revenue of $85,000 but manually summing orders.csv gives only $44,000. The numbers are roughly double what they should be.
What to ask the AI
"The regional revenue totals are inflated compared to the raw orders file. Make sure all staging tables are deduplicated before any JOIN runs, and verify that each order joins to at most one customer and one product."

How it works

The ETL pipeline follows a strict four-stage architecture:

  1. 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.

  2. Extractor (extractor.py) has one function per source type. extract_csv() uses pd.read_csv() with configurable encoding and null handling. extract_json() uses pd.read_json() or json.load() with optional flattening. extract_api() uses requests.get() with retry logic. Each function returns a DataFrame and metadata dict.

  3. Transformer (transformer.py) creates an in-memory SQLite database, loads all extracted DataFrames into staging tables using df.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).

  4. 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.

  5. 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.py is 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.py SQL 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.
ℹMIS Connection: Data Engineering Career Path

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

  1. Build the ETL pipeline by talking your agent through the plan above.
  2. Run it on the included sample data and verify the output database.
  3. Open the data quality report. Are there any warnings? Investigate each one by querying the SQLite database directly.
  4. Open warehouse.db in 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;)
  5. Edit the sample data to introduce a new quality issue: add a row to orders.csv with a customer_id that does not exist in customers.json. Re-run the pipeline. Does the quality report catch it?
  6. Add a new data source: create a returns.csv file with columns order_id, return_date, reason, refund_amount. Edit config.yaml to extract it, join it with order_details, and create a return_analysis aggregation table.
  7. Try the --dry-run flag. 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.

KNOWLEDGE CHECK

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.

intellimetrics · Arlington, VA
© 2026 intellimetrics.net
Search lessons