Build a Python ETL Pipeline with DuckDB

Build an ETL pipeline that cleans messy CSV data and loads it into DuckDB.

Introduction

30 Second Summary

Every company collects data, but raw data is messy. Customer names arrive misspelled, dates come in five different formats, and prices have dollar signs where numbers should be.

In this project, you will build a Python ETL pipeline that takes messy e-commerce data, cleans it up, and loads it into a local analytical database you can query with SQL.

What You'll Build

A complete ETL (Extract, Transform, Load) pipeline that processes messy e-commerce order data, transforms it into clean records, and loads it into a persistent DuckDB database for SQL analysis.

By the end of this project, you'll have:

  • A working Python ETL pipeline that extracts messy CSV data, transforms it into clean records, and loads it into a DuckDB database.
  • Hands-on experience writing SQL analytical queries (aggregations, window functions, and rankings) against a local analytics database.
  • A reusable pipeline structure (extract/transform/load pattern) that you can apply to any future data engineering project.
  • Secret Mission: Add a data quality validation layer that logs every issue found during transformation and generates a summary report.

Are there any prerequisites?

None. This guide includes setup for all required tools. You will install everything you need in Step 1, including DuckDB and a code editor.

Set Up Your Project

Before building anything, you need a clean workspace and the right tools installed. A dedicated project folder keeps your pipeline scripts, raw data, and database file organized in one place.

In this step, you'll create a project folder, install DuckDB (the analytical database your pipeline will load data into), and confirm everything is working.

In this step, get ready to:
  • Create a project folder on your Desktop and open it in VS Code.
  • Install the DuckDB Python package.
  • Verify DuckDB is working with a quick terminal check.
Create your project folder

Your pipeline will produce several files: a data generator script, the pipeline itself, an analysis script, and a database file. A dedicated folder keeps everything together and easy to find.

  • In VS Code, open the integrated terminal by pressing Ctrl+` (Windows) or Cmd+` (macOS).
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir etl-pipeline
cd etl-pipeline
  • Confirm the folder exists by running:
pwd

You should see a path ending in etl-pipeline, confirming you're inside your new project folder.

  • Now open this folder as your VS Code workspace. Click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the etl-pipeline folder, and click Open.

VS Code reloads with etl-pipeline as the workspace. The Explorer sidebar on the left shows an empty folder, ready for your files.

Don't see the folder on your Desktop?

Make sure you ran cd ~/Desktop before mkdir. Without that, the folder was created wherever your terminal opened by default (usually your home directory).

Run ls ~/Desktop to check if etl-pipeline is there. If not, run ls ~ to look in your home directory.

help me find my project folder

Install DuckDB

DuckDB is an analytical database that runs entirely inside your Python process. Unlike traditional databases, it needs no server setup.

  • Install DuckDB by running this command in your terminal:
pip install duckdb==1.5.3

What does this command do?

pip is Python's package installer. The ==1.5.3 part pins the exact version so your results match this project's instructions.

You should see output ending with a line confirming that duckdb was successfully installed.

Seeing a permission error?

If you see a permissions error, try adding --user to the end of the command. If you're using Anaconda, make sure you're running the command inside your Anaconda environment.

help me fix my pip install error

Verify DuckDB is working

A quick import check confirms that Python can find DuckDB and that you have the right version.

  • Run this command to check your DuckDB installation:
python -c "import duckdb; print(duckdb.__version__)"

You should see 1.5.3 printed in your terminal.

What is DuckDB?

DuckDB is a columnar database built for analytics. It's designed for fast aggregations and analytical queries on local data, with no server process to manage.

Think of it as the analytical counterpart to SQLite. Where SQLite excels at small transactional workloads, DuckDB excels at scanning and summarizing large datasets.

✔️ I see 1.5.3

You're all set. DuckDB is installed and ready for your pipeline to use.

ⓧ I see a ModuleNotFoundError

Python can't find the DuckDB package. This usually means the install went to a different Python environment than the one your terminal is using.

  • Check which Python you're using by running:
which python
  • If the path doesn't include anaconda or conda, activate your Anaconda environment first, then re-run the pip install command.

help me fix my DuckDB import error

Your workspace is ready. Next up, you'll create a script that generates realistic messy data for your pipeline to clean.

Generate Messy Sample Data

Your development environment is ready with DuckDB installed. Now you need some data to feed through your pipeline.

Instead of downloading a dataset from the internet, you'll generate your own messy data. This approach teaches you exactly what real-world data quality issues look like because you'll create each one intentionally.

In this step, get ready to:
  • Create a Python script that generates realistic messy e-commerce data.
  • Run the script to produce a raw CSV file.
  • Inspect the CSV to identify the data quality problems you'll fix later.
Create the data generator script

You'll build a script that creates 1000 rows of fake e-commerce order data. The script intentionally introduces common data quality issues that mirror what you'd find in production systems.

  • In VS Code, hover over the Explorer sidebar on the left and click the New File icon (a page with a plus sign).
  • Name the file generate_data.py and press Enter.
  • Paste the following code into generate_data.py to define the messy data templates:
import csv
import random
from datetime import datetime, timedelta


def generate_messy_orders(filename="raw_orders.csv", num_rows=1000):
    """Generate a messy e-commerce orders CSV with realistic data quality issues."""

    random.seed(42)

    customers = [
        ("Alice Johnson", "[email protected]"),
        ("Bob Smith", "[email protected]"),
        ("  Charlie Brown ", "[email protected]"),
        ("diana prince", "[email protected]"),
        ("EVE WILSON", "[email protected]"),
        ("Frank Miller", ""),
        ("Grace Lee", "[email protected]"),
        ("henry ford", "[email protected]"),
        ("Iris Chang", ""),
        ("JACK DANIELS", "[email protected]"),
    ]

    categories_variants = [
        ["Electronics", "electronics", "ELECTRONICS", "Electrnics"],
        ["Clothing", "clothing", "CLOTHING", "Clothng"],
        ["Home & Garden", "home & garden", "HOME & GARDEN", "Home and Garden"],
        ["Books", "books", "BOOKS", "Book"],
        ["Sports", "sports", "SPORTS", "Sport"],
    ]

    products = {
        "Electronics": ["Wireless Headphones", "USB-C Cable", "Phone Case", "Bluetooth Speaker", "Laptop Stand"],
        "Clothing": ["Cotton T-Shirt", "Denim Jeans", "Running Shoes", "Winter Jacket", "Baseball Cap"],
        "Home & Garden": ["Plant Pot", "LED Desk Lamp", "Throw Pillow", "Garden Hose", "Wall Clock"],
        "Books": ["Python Basics", "Data Engineering 101", "SQL Cookbook", "Machine Learning Guide", "Clean Code"],
        "Sports": ["Yoga Mat", "Water Bottle", "Resistance Bands", "Jump Rope", "Tennis Balls"],
    }

    countries = ["USA", "Canada", "UK", "Germany", "France", "Australia", "", "", "USA", "USA"]

    payment_statuses = ["paid", "paid", "paid", "pending", "pending", "failed"]

    date_formats = [
        lambda d: d.strftime("%Y-%m-%d"),
        lambda d: d.strftime("%m/%d/%Y"),
        lambda d: d.strftime("%d-%m-%Y"),
        lambda d: d.strftime("%B %d, %Y"),
    ]

What does this code do?

This first section sets up the "messiness catalog" for your data generator.

  • random.seed(42) makes the random output reproducible. Everyone running this script gets the same messy data.
  • customers has names with leading/trailing spaces, inconsistent casing (lowercase, UPPERCASE, Title Case), and some with empty email strings.
  • categories_variants includes typos like "Electrnics" and "Clothng" alongside casing variants.
  • countries includes empty strings to simulate missing data.
  • date_formats uses four different date formats, just like real systems that receive data from multiple sources.
  • Add the row generation logic and CSV writing code below the date_formats list (still inside the function):
    rows = []
    start_date = datetime(2024, 1, 1)

    for i in range(num_rows):
        customer_name, email = random.choice(customers)

        category_group = random.choice(categories_variants)
        category = random.choice(category_group)
        canonical_category = category_group[0]
        product = random.choice(products[canonical_category])

        quantity = random.randint(1, 10)
        if random.random() < 0.03:
            quantity = -quantity
        if random.random() < 0.05:
            quantity = str(quantity) + " units"

        price = round(random.uniform(5.99, 299.99), 2)
        if random.random() < 0.15:
            price = f"${price}"
        elif random.random() < 0.05:
            price = ""

        order_date = start_date + timedelta(days=random.randint(0, 365))
        date_formatter = random.choice(date_formats)
        date_str = date_formatter(order_date)

        country = random.choice(countries)

        payment = random.choice(payment_statuses)

        rows.append([
            i + 1001,
            customer_name,
            email,
            category,
            product,
            quantity,
            price,
            date_str,
            country,
            payment,
        ])

    # Add duplicate rows
    for _ in range(20):
        rows.append(random.choice(rows[:num_rows]))

    random.shuffle(rows)

    with open(filename, "w", newline="", encoding="utf-8") as f:
        writer = csv.writer(f)
        writer.writerow([
            "order_id", "customer_name", "email", "product_category",
            "product_name", "quantity", "unit_price", "order_date",
            "shipping_country", "payment_status"
        ])
        writer.writerows(rows)

    print(f"Generated {len(rows)} rows of messy data in '{filename}'")


if __name__ == "__main__":
    generate_messy_orders()

What does this code do?

This section generates the actual rows and writes them to a CSV file.

  • The loop creates 1000 rows, randomly picking from your messy templates. About 3% of quantities become negative numbers, and 5% get " units" appended as text.
  • About 15% of prices get a dollar sign prefix (turning them into strings), and 5% are left completely empty.
  • After the main loop, 20 duplicate rows are added and everything is shuffled. This mimics how duplicates end up scattered throughout real datasets.
  • The if __name__ == "__main__" block means the function runs automatically when you execute the script directly.
  • Save generate_data.py.

You won't see any output yet. The next substep runs the script to generate your CSV file.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

import csv
import random
from datetime import datetime, timedelta


def generate_messy_orders(filename="raw_orders.csv", num_rows=1000):
    """Generate a messy e-commerce orders CSV with realistic data quality issues."""

    random.seed(42)

    customers = [
        ("Alice Johnson", "[email protected]"),
        ("Bob Smith", "[email protected]"),
        ("  Charlie Brown ", "[email protected]"),
        ("diana prince", "[email protected]"),
        ("EVE WILSON", "[email protected]"),
        ("Frank Miller", ""),
        ("Grace Lee", "[email protected]"),
        ("henry ford", "[email protected]"),
        ("Iris Chang", ""),
        ("JACK DANIELS", "[email protected]"),
    ]

    categories_variants = [
        ["Electronics", "electronics", "ELECTRONICS", "Electrnics"],
        ["Clothing", "clothing", "CLOTHING", "Clothng"],
        ["Home & Garden", "home & garden", "HOME & GARDEN", "Home and Garden"],
        ["Books", "books", "BOOKS", "Book"],
        ["Sports", "sports", "SPORTS", "Sport"],
    ]

    products = {
        "Electronics": ["Wireless Headphones", "USB-C Cable", "Phone Case", "Bluetooth Speaker", "Laptop Stand"],
        "Clothing": ["Cotton T-Shirt", "Denim Jeans", "Running Shoes", "Winter Jacket", "Baseball Cap"],
        "Home & Garden": ["Plant Pot", "LED Desk Lamp", "Throw Pillow", "Garden Hose", "Wall Clock"],
        "Books": ["Python Basics", "Data Engineering 101", "SQL Cookbook", "Machine Learning Guide", "Clean Code"],
        "Sports": ["Yoga Mat", "Water Bottle", "Resistance Bands", "Jump Rope", "Tennis Balls"],
    }

    countries = ["USA", "Canada", "UK", "Germany", "France", "Australia", "", "", "USA", "USA"]

    payment_statuses = ["paid", "paid", "paid", "pending", "pending", "failed"]

    date_formats = [
        lambda d: d.strftime("%Y-%m-%d"),
        lambda d: d.strftime("%m/%d/%Y"),
        lambda d: d.strftime("%d-%m-%Y"),
        lambda d: d.strftime("%B %d, %Y"),
    ]

    rows = []
    start_date = datetime(2024, 1, 1)

    for i in range(num_rows):
        customer_name, email = random.choice(customers)

        category_group = random.choice(categories_variants)
        category = random.choice(category_group)
        canonical_category = category_group[0]
        product = random.choice(products[canonical_category])

        quantity = random.randint(1, 10)
        if random.random() < 0.03:
            quantity = -quantity
        if random.random() < 0.05:
            quantity = str(quantity) + " units"

        price = round(random.uniform(5.99, 299.99), 2)
        if random.random() < 0.15:
            price = f"${price}"
        elif random.random() < 0.05:
            price = ""

        order_date = start_date + timedelta(days=random.randint(0, 365))
        date_formatter = random.choice(date_formats)
        date_str = date_formatter(order_date)

        country = random.choice(countries)

        payment = random.choice(payment_statuses)

        rows.append([
            i + 1001,
            customer_name,
            email,
            category,
            product,
            quantity,
            price,
            date_str,
            country,
            payment,
        ])

    # Add duplicate rows
    for _ in range(20):
        rows.append(random.choice(rows[:num_rows]))

    random.shuffle(rows)

    with open(filename, "w", newline="", encoding="utf-8") as f:
        writer = csv.writer(f)
        writer.writerow([
            "order_id", "customer_name", "email", "product_category",
            "product_name", "quantity", "unit_price", "order_date",
            "shipping_country", "payment_status"
        ])
        writer.writerows(rows)

    print(f"Generated {len(rows)} rows of messy data in '{filename}'")


if __name__ == "__main__":
    generate_messy_orders()
Run the script to generate your CSV

With the script saved, you can now run it to produce the messy CSV file that your pipeline will eventually clean.

  • Open a terminal in VS Code by pressing Ctrl+` (macOS) or Ctrl+` (Windows).
  • Run the data generator by executing this command:
python generate_data.py
  • You should see this output in your terminal:
Generated 1020 rows of messy data in 'raw_orders.csv'

Why 1020 rows?

The script generates 1000 base rows plus 20 intentional duplicates. Duplicates are one of the most common data quality issues in production systems, often caused by retried API calls or overlapping data imports.

  • Confirm the CSV was created by running:
ls
  • You should see generate_data.py and raw_orders.csv in the output.

Don't see the CSV file?

  • Make sure you saved generate_data.py before running it.
  • Check that your terminal is in the same folder as your script. Run ls to verify you can see generate_data.py listed.
  • If you see a syntax error, double check your code against the full code tab above.

Still stuck? Help me debug why my generate_data.py script isn't producing output.

Inspect the raw data

Before building the pipeline, take a moment to look at the data you'll be cleaning. This helps you understand the exact problems your transform step needs to solve.

  • In the Explorer sidebar, click raw_orders.csv to open it in VS Code.
  • Scroll through the first 20-30 rows and look for these issues:
  • Customer names in different cases: "diana prince", "EVE WILSON", " Charlie Brown " (with spaces).
  • Categories with typos: "Electrnics", "Clothng", "Sport".
  • Dates in multiple formats: "2024-03-15", "03/15/2024", "15-03-2024", "March 15, 2024".
  • Prices with dollar signs like "$149.99" mixed with plain numbers.
  • Empty fields where emails or countries should be.
  • Quantities with " units" appended or negative values.

Why does this matter?

Every one of these issues would break a SQL query or produce wrong results if loaded into a database directly. A GROUP BY on product_category would treat "Electronics", "electronics", and "Electrnics" as three separate categories.

This is exactly why the Transform step exists in an ETL pipeline. Your job is to catch and fix all of these before loading into the database.

You've got a CSV full of realistic data quality problems. In the next step, you'll start building the ETL pipeline by writing the extract function that reads this raw data into Python.

Build the Extract Step

Your messy CSV is ready. Now it's time to start building the pipeline that will clean it up.

Every ETL pipeline begins with extraction. The extract step reads raw data from its source and loads it into memory so you can inspect it, profile it, and understand what needs fixing before transformation begins.

In this step, get ready to:
  • Create pipeline.py with an extract() function that reads raw CSV data.
  • Run the extract function and review the raw data profile.
  • Identify the data quality issues that need fixing in the transform step.
Create pipeline.py with the extract function

The extract function is your pipeline's entry point. It reads the raw CSV into a pandas DataFrame and prints a profile so you can see what you're working with.

  • In VS Code, create a new file called pipeline.py in your project folder.
  • Add the following code:
import pandas as pd
import duckdb
from datetime import datetime


def extract(filepath="raw_orders.csv"):
    """Extract: Read raw CSV data into a pandas DataFrame."""
    print("=" * 50)
    print("STEP 1: EXTRACT")
    print("=" * 50)

    # Read the CSV file into a DataFrame
    df = pd.read_csv(filepath)

    # Print a profile of the raw data
    print(f"Loaded {len(df)} rows and {len(df.columns)} columns")
    print(f"\nColumn types:\n{df.dtypes}")
    print(f"\nMissing values:\n{df.isnull().sum()}")
    print(f"\nFirst 5 rows:\n{df.head()}")

    return df

What does this code do?

  • pd.read_csv(filepath) reads the entire CSV file into a pandas DataFrame, which is a table-like data structure you can manipulate in Python.
  • df.dtypes shows what data type pandas assigned to each column. This helps you spot columns that should be numbers but got read as text.
  • df.isnull().sum() counts how many values are missing in each column. Missing data is one of the most common quality issues you'll deal with.
  • df.head() shows the first 5 rows so you can visually inspect the data for obvious problems.

Why import duckdb and datetime now?

You'll need both duckdb and datetime in later steps when you build the transform and load functions. Importing them now keeps all your imports at the top of the file, which is standard Python practice.

  • Save pipeline.py.

Seeing a red underline under an import?

If VS Code shows a warning under import duckdb, make sure you installed DuckDB in the previous step. Your terminal should be using the same Python environment where you ran pip install duckdb.

help me fix an import error for duckdb in VS Code

Run the extract function
  • Run the extract function by entering this command in your terminal:
python -c "from pipeline import extract; extract()"

You should see a data profile printed to the terminal showing 1020 rows, 10 columns, column types, missing value counts, and the first 5 rows of raw data.

Seeing ModuleNotFoundError?

If you see an error about pipeline not being found, make sure your terminal is in the same folder as pipeline.py. Run ls (macOS) or dir (Windows) and confirm you see both pipeline.py and raw_orders.csv listed.

help me debug a ModuleNotFoundError when running my pipeline

Identify the data quality issues

Look at the output from your extract function. The profile reveals several problems that the transform step will need to fix.

What issues should I see?

Your raw data profile exposes these common real-world data quality issues:

  • Customer names have inconsistent casing ("diana prince", "EVE WILSON", " Charlie Brown ").
  • The quantity column shows as object type instead of int, meaning some values have text mixed in ("5 units").
  • The unit_price column is also object type because some values have dollar signs ("$29.99").
  • Missing values appear in the email and shipping_country columns.
  • Product categories have typos and inconsistent casing ("Electrnics", "CLOTHING").
  • There are 20 duplicate rows that need removing.

These are exactly the kinds of issues data engineers encounter in production data every day. The transform step will address each one systematically.

✔️ Awesome, I've got everything!

Great. Double check you've saved pipeline.py before moving on.

ⓧ I'd like to double check the full code

Here is the full pipeline.py file at this point:

import pandas as pd
import duckdb
from datetime import datetime


def extract(filepath="raw_orders.csv"):
    """Extract: Read raw CSV data into a pandas DataFrame."""
    print("=" * 50)
    print("STEP 1: EXTRACT")
    print("=" * 50)

    df = pd.read_csv(filepath)

    print(f"Loaded {len(df)} rows and {len(df.columns)} columns")
    print(f"\nColumn types:\n{df.dtypes}")
    print(f"\nMissing values:\n{df.isnull().sum()}")
    print(f"\nFirst 5 rows:\n{df.head()}")

    return df

Your extract step is working and you've identified exactly what needs fixing. Next up, you'll build the transform function that tackles every one of these data quality issues.

Build the Transform Step

Your extract function pulled in the raw data, and now you can see exactly how messy it is. The transform step is where you fix all those issues before the data reaches your database.

In this step, you'll write a transform() function that handles eight different data quality problems. Each cleaning operation runs in sequence, gradually turning chaotic raw data into a consistent, analysis-ready dataset.

In this step, get ready to:
  • Add the transform() function with eight data cleaning operations.
  • Run the pipeline to see a before/after transformation summary.
  • Verify the cleaned data looks correct.
Add the transform() function

The transform function takes the raw DataFrame from your extract step and applies cleaning rules one by one. Each rule targets a specific data quality issue you saw in raw_orders.csv.

  • In pipeline.py, scroll to the bottom of the file (below the extract() function).
  • Start the transform function by adding this code that handles duplicates and customer names:


def transform(df):
    """Transform: Clean and standardize the raw data."""
    print("\n" + "=" * 50)
    print("STEP 2: TRANSFORM")
    print("=" * 50)

    initial_count = len(df)

    # 1. Remove duplicate rows
    df = df.drop_duplicates()
    dupes_removed = initial_count - len(df)
    print(f"\n[1] Removed {dupes_removed} duplicate rows")

    # 2. Standardize customer names (strip whitespace, title case)
    df["customer_name"] = df["customer_name"].str.strip().str.title()
    print("[2] Standardized customer names")

What does this code do?

  • initial_count saves the starting row count so you can report how many rows were removed at the end.
  • drop_duplicates() removes exact duplicate rows. Your data generator intentionally added 20 copies to simulate real-world duplicates.
  • str.strip().str.title() removes leading/trailing whitespace and converts names like " Charlie Brown " or "EVE WILSON" into "Charlie Brown" and "Eve Wilson".

You won't see a change yet. The next chunk adds category, quantity, and price cleaning.

  • Add the following code directly below the last line you just typed (still inside the transform function):

    # 3. Standardize product categories
    category_mapping = {
        "electronics": "Electronics",
        "electrnics": "Electronics",
        "clothing": "Clothing",
        "clothng": "Clothing",
        "home & garden": "Home & Garden",
        "home and garden": "Home & Garden",
        "books": "Books",
        "book": "Books",
        "sports": "Sports",
        "sport": "Sports",
    }
    df["product_category"] = (
        df["product_category"]
        .str.strip()
        .str.lower()
        .map(category_mapping)
        .fillna(df["product_category"].str.strip().str.title())
    )
    print("[3] Standardized product categories")

    # 4. Clean quantity column
    df["quantity"] = df["quantity"].astype(str).str.replace(" units", "", regex=False)
    df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")
    invalid_qty = df["quantity"].isna().sum() + (df["quantity"] < 0).sum()
    df = df[df["quantity"] > 0]
    df["quantity"] = df["quantity"].astype(int)
    print(f"[4] Cleaned quantities, removed {invalid_qty} invalid rows")

    # 5. Clean unit_price column
    df["unit_price"] = (
        df["unit_price"]
        .astype(str)
        .str.replace("$", "", regex=False)
        .str.strip()
    )
    df["unit_price"] = pd.to_numeric(df["unit_price"], errors="coerce")
    df = df.dropna(subset=["unit_price"])
    print("[5] Cleaned unit prices")

What does this code do?

  • category_mapping is a dictionary that maps every misspelled or differently-cased category to its correct form. The code lowercases the raw value first, then looks it up in this map.
  • The quantity cleaning first strips the " units" suffix, then converts to a number. Rows with negative or unparseable quantities are dropped entirely.
  • Price cleaning removes dollar signs, converts to numeric, and drops rows where the price was missing or couldn't be parsed.
  • errors="coerce" tells pandas to turn unparseable values into NaN instead of crashing the script.

You won't see a change yet. The next chunk adds date parsing and completes the function.

  • Add the final section of the transform function directly below the last line:

    # 6. Standardize order dates to YYYY-MM-DD
    def parse_date(date_str):
        formats = ["%Y-%m-%d", "%m/%d/%Y", "%d-%m-%Y", "%B %d, %Y"]
        for fmt in formats:
            try:
                return datetime.strptime(str(date_str), fmt).strftime("%Y-%m-%d")
            except (ValueError, TypeError):
                continue
        return None

    df["order_date"] = df["order_date"].apply(parse_date)
    df = df.dropna(subset=["order_date"])
    print("[6] Standardized date formats to YYYY-MM-DD")

    # 7. Handle missing shipping countries
    df["shipping_country"] = df["shipping_country"].replace("", pd.NA)
    missing_countries = df["shipping_country"].isna().sum()
    df["shipping_country"] = df["shipping_country"].fillna("Unknown")
    print(f"[7] Filled {missing_countries} missing countries with 'Unknown'")

    # 8. Add calculated column
    df["total_amount"] = round(df["quantity"] * df["unit_price"], 2)
    print("[8] Added total_amount column")

    print(f"\nFinal dataset: {len(df)} rows (removed {initial_count - len(df)} total)")
    print(f"Columns: {list(df.columns)}")

    return df

What does this code do?

  • parse_date() is a helper function defined inside transform(). It tries four different date formats until one works. If none match, it returns None and the row gets dropped.
  • Missing shipping countries (empty strings) get replaced with "Unknown" so they don't break downstream queries.
  • total_amount is a new calculated column (quantity multiplied by unit_price). This saves you from recalculating it in every SQL query later.
  • Save pipeline.py.

Seeing a red underline or indentation error?

  • Every line inside transform() must be indented with exactly 4 spaces. The parse_date() helper inside it needs 8 spaces (it's a function within a function).
  • Make sure the three code blocks form one continuous function. There should be no blank lines between the function signature and the return statement that don't have proper indentation.

Help me fix my indentation in the transform function.

✔️ Awesome, I've got everything!

Great. Double check you've saved pipeline.py before moving on.

ⓧ I'd like to double check the full code

Here's the complete pipeline.py file at this point:

import pandas as pd
import duckdb
from datetime import datetime


def extract(filepath="raw_orders.csv"):
    """Extract: Read raw CSV data into a pandas DataFrame."""
    print("=" * 50)
    print("STEP 1: EXTRACT")
    print("=" * 50)

    df = pd.read_csv(filepath)

    print(f"Loaded {len(df)} rows and {len(df.columns)} columns")
    print(f"\nColumn types:\n{df.dtypes}")
    print(f"\nMissing values:\n{df.isnull().sum()}")
    print(f"\nFirst 5 rows:\n{df.head()}")

    return df


def transform(df):
    """Transform: Clean and standardize the raw data."""
    print("\n" + "=" * 50)
    print("STEP 2: TRANSFORM")
    print("=" * 50)

    initial_count = len(df)

    # 1. Remove duplicate rows
    df = df.drop_duplicates()
    dupes_removed = initial_count - len(df)
    print(f"\n[1] Removed {dupes_removed} duplicate rows")

    # 2. Standardize customer names (strip whitespace, title case)
    df["customer_name"] = df["customer_name"].str.strip().str.title()
    print("[2] Standardized customer names")

    # 3. Standardize product categories
    category_mapping = {
        "electronics": "Electronics",
        "electrnics": "Electronics",
        "clothing": "Clothing",
        "clothng": "Clothing",
        "home & garden": "Home & Garden",
        "home and garden": "Home & Garden",
        "books": "Books",
        "book": "Books",
        "sports": "Sports",
        "sport": "Sports",
    }
    df["product_category"] = (
        df["product_category"]
        .str.strip()
        .str.lower()
        .map(category_mapping)
        .fillna(df["product_category"].str.strip().str.title())
    )
    print("[3] Standardized product categories")

    # 4. Clean quantity column
    df["quantity"] = df["quantity"].astype(str).str.replace(" units", "", regex=False)
    df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")
    invalid_qty = df["quantity"].isna().sum() + (df["quantity"] < 0).sum()
    df = df[df["quantity"] > 0]
    df["quantity"] = df["quantity"].astype(int)
    print(f"[4] Cleaned quantities, removed {invalid_qty} invalid rows")

    # 5. Clean unit_price column
    df["unit_price"] = (
        df["unit_price"]
        .astype(str)
        .str.replace("$", "", regex=False)
        .str.strip()
    )
    df["unit_price"] = pd.to_numeric(df["unit_price"], errors="coerce")
    df = df.dropna(subset=["unit_price"])
    print("[5] Cleaned unit prices")

    # 6. Standardize order dates to YYYY-MM-DD
    def parse_date(date_str):
        formats = ["%Y-%m-%d", "%m/%d/%Y", "%d-%m-%Y", "%B %d, %Y"]
        for fmt in formats:
            try:
                return datetime.strptime(str(date_str), fmt).strftime("%Y-%m-%d")
            except (ValueError, TypeError):
                continue
        return None

    df["order_date"] = df["order_date"].apply(parse_date)
    df = df.dropna(subset=["order_date"])
    print("[6] Standardized date formats to YYYY-MM-DD")

    # 7. Handle missing shipping countries
    df["shipping_country"] = df["shipping_country"].replace("", pd.NA)
    missing_countries = df["shipping_country"].isna().sum()
    df["shipping_country"] = df["shipping_country"].fillna("Unknown")
    print(f"[7] Filled {missing_countries} missing countries with 'Unknown'")

    # 8. Add calculated column
    df["total_amount"] = round(df["quantity"] * df["unit_price"], 2)
    print("[8] Added total_amount column")

    print(f"\nFinal dataset: {len(df)} rows (removed {initial_count - len(df)} total)")
    print(f"Columns: {list(df.columns)}")

    return df
Run the pipeline to see the before/after summary

Your transform function is complete. Time to run it against the messy data and see the cleaning in action.

  • Run the extract and transform pipeline together by running this command in your terminal:
python -c "from pipeline import extract, transform; transform(extract())"

What does this command do?

This imports your extract and transform functions from pipeline.py, then chains them together. The output of extract() feeds directly into transform().

You should see two sections of output: the EXTRACT step showing 1020 rows loaded, followed by the TRANSFORM step printing each cleaning operation.

Seeing a NameError or ImportError?

  • Make sure your terminal is in the same folder as pipeline.py. Run ls to confirm you can see the file.
  • If you see NameError: name 'datetime' is not defined, check that from datetime import datetime is still at the top of your file.
  • If you see an IndentationError, use the double-check tab above to compare your code against the full file.

Help me debug my pipeline.py transform function.

Verify the cleaned data looks correct

Look at your terminal output. The transform summary tells you exactly what happened to the data at each step.

What should I see?

Your output should show each cleaning operation in numbered order. Here are the key things to check:

  • [1] should show around 20 duplicate rows removed (your data generator added 20).
  • [4] should report a small number of invalid quantity rows removed.
  • [7] should show some missing countries filled with "Unknown".
  • The final line should report a smaller row count than the original 1020, and the columns list should include total_amount as a new column.

Why does the transform step remove rows?

In real ETL pipelines, you have a choice: fix bad data or remove it. For critical fields like quantity and price, a row with missing or negative values can't be meaningfully analyzed. Dropping those rows is the safer choice for analytics accuracy.

For less critical fields like shipping country, you fill with a placeholder ("Unknown") so the row is still usable for revenue calculations.

Your data is now clean, consistent, and ready for a database. Next up, you'll load this transformed data into DuckDB and start querying it with SQL.

Load into DuckDB

Your pipeline can extract and transform data, but there's a problem. The clean DataFrame only exists in memory while the script runs.

In this step, you'll write the final piece of the ETL puzzle: loading clean data into a persistent DuckDB database that survives after the script finishes. You'll also wire all three steps into a single main() function so the entire pipeline runs with one command.

In this step, get ready to:
  • Write a load() function that saves clean data into a DuckDB database file.
  • Add a main() function that chains extract, transform, and load together.
  • Run the full pipeline end-to-end and verify the database was created.
Add the load() function

DuckDB stores data in a single file on disk, similar to SQLite. When you connect to a file path that doesn't exist yet, DuckDB creates the database for you automatically.

  • In pipeline.py, scroll to the bottom of your transform() function.
  • Add the following load() function below it:
def load(df, db_path="ecommerce.duckdb"):
    """Load: Write the cleaned DataFrame into DuckDB."""
    print("\n" + "=" * 50)
    print("STEP 3: LOAD")
    print("=" * 50)

    con = duckdb.connect(db_path)

    con.sql("DROP TABLE IF EXISTS orders")
    con.sql("CREATE TABLE orders AS SELECT * FROM df")

    result = con.sql("SELECT COUNT(*) AS row_count FROM orders").fetchone()
    print(f"\nLoaded {result[0]} rows into 'orders' table in '{db_path}'")

    print("\nTable schema:")
    con.sql("DESCRIBE orders").show()

    con.close()
    print(f"\nDatabase saved to '{db_path}'")

What does this code do?

  • duckdb.connect(db_path) opens (or creates) a persistent database file called ecommerce.duckdb.
  • DROP TABLE IF EXISTS orders clears any old data so the pipeline is safely re-runnable.
  • CREATE TABLE orders AS SELECT * FROM df uses DuckDB's replacement scan feature to find the pandas DataFrame variable df directly from your Python process and write it into a table.
  • DESCRIBE orders prints the column names and types so you can verify the schema looks right.

What is a replacement scan?

DuckDB can automatically detect pandas DataFrames by their variable name when you reference them in SQL. You don't need to register or import the DataFrame manually. Just use the variable name (df) directly in your SQL query and DuckDB finds it.

  • Save pipeline.py.

You won't see a change yet. The next chunk adds a main() function so you can actually run the full pipeline.

Add a main() function to run the full pipeline

Right now your three ETL functions exist independently. You need a single entry point that calls them in the correct order: extract, then transform, then load.

  • At the very bottom of pipeline.py, below the load() function you just added, paste the following code:
def main():
    """Run the complete ETL pipeline."""
    print("\n🚀 Starting ETL Pipeline")
    print("=" * 50)

    raw_df = extract()
    clean_df = transform(raw_df)
    load(clean_df)

    print("\n✅ Pipeline complete!")


if __name__ == "__main__":
    main()

What does this code do?

  • main() calls each ETL function in sequence, passing the output of one as the input to the next.
  • if __name__ == "__main__" ensures main() only runs when you execute the file directly. If another script imports from this file, it won't trigger the pipeline automatically.
  • Save pipeline.py.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

Here's the complete pipeline.py file for comparison:

import pandas as pd
import duckdb
from datetime import datetime


def extract(filepath="raw_orders.csv"):
    """Extract: Read raw CSV data into a pandas DataFrame."""
    print("=" * 50)
    print("STEP 1: EXTRACT")
    print("=" * 50)

    df = pd.read_csv(filepath)

    print(f"Loaded {len(df)} rows and {len(df.columns)} columns")
    print(f"\nColumn types:\n{df.dtypes}")
    print(f"\nMissing values:\n{df.isnull().sum()}")
    print(f"\nFirst 5 rows:\n{df.head()}")

    return df


def transform(df):
    """Transform: Clean and standardize the raw data."""
    print("\n" + "=" * 50)
    print("STEP 2: TRANSFORM")
    print("=" * 50)

    initial_count = len(df)

    # 1. Remove duplicate rows
    df = df.drop_duplicates()
    dupes_removed = initial_count - len(df)
    print(f"\n[1] Removed {dupes_removed} duplicate rows")

    # 2. Standardize customer names (strip whitespace, title case)
    df["customer_name"] = df["customer_name"].str.strip().str.title()
    print("[2] Standardized customer names")

    # 3. Standardize product categories
    category_mapping = {
        "electronics": "Electronics",
        "electrnics": "Electronics",
        "clothing": "Clothing",
        "clothng": "Clothing",
        "home & garden": "Home & Garden",
        "home and garden": "Home & Garden",
        "books": "Books",
        "book": "Books",
        "sports": "Sports",
        "sport": "Sports",
    }
    df["product_category"] = (
        df["product_category"]
        .str.strip()
        .str.lower()
        .map(category_mapping)
        .fillna(df["product_category"].str.strip().str.title())
    )
    print("[3] Standardized product categories")

    # 4. Clean quantity column
    df["quantity"] = df["quantity"].astype(str).str.replace(" units", "", regex=False)
    df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")
    invalid_qty = df["quantity"].isna().sum() + (df["quantity"] < 0).sum()
    df = df[df["quantity"] > 0]
    df["quantity"] = df["quantity"].astype(int)
    print(f"[4] Cleaned quantities, removed {invalid_qty} invalid rows")

    # 5. Clean unit_price column
    df["unit_price"] = (
        df["unit_price"]
        .astype(str)
        .str.replace("$", "", regex=False)
        .str.strip()
    )
    df["unit_price"] = pd.to_numeric(df["unit_price"], errors="coerce")
    df = df.dropna(subset=["unit_price"])
    print("[5] Cleaned unit prices")

    # 6. Standardize order dates to YYYY-MM-DD
    def parse_date(date_str):
        formats = ["%Y-%m-%d", "%m/%d/%Y", "%d-%m-%Y", "%B %d, %Y"]
        for fmt in formats:
            try:
                return datetime.strptime(str(date_str), fmt).strftime("%Y-%m-%d")
            except (ValueError, TypeError):
                continue
        return None

    df["order_date"] = df["order_date"].apply(parse_date)
    df = df.dropna(subset=["order_date"])
    print("[6] Standardized date formats to YYYY-MM-DD")

    # 7. Handle missing shipping countries
    df["shipping_country"] = df["shipping_country"].replace("", pd.NA)
    missing_countries = df["shipping_country"].isna().sum()
    df["shipping_country"] = df["shipping_country"].fillna("Unknown")
    print(f"[7] Filled {missing_countries} missing countries with 'Unknown'")

    # 8. Add calculated column
    df["total_amount"] = round(df["quantity"] * df["unit_price"], 2)
    print("[8] Added total_amount column")

    print(f"\nFinal dataset: {len(df)} rows (removed {initial_count - len(df)} total)")
    print(f"Columns: {list(df.columns)}")

    return df


def load(df, db_path="ecommerce.duckdb"):
    """Load: Write the cleaned DataFrame into DuckDB."""
    print("\n" + "=" * 50)
    print("STEP 3: LOAD")
    print("=" * 50)

    con = duckdb.connect(db_path)

    con.sql("DROP TABLE IF EXISTS orders")
    con.sql("CREATE TABLE orders AS SELECT * FROM df")

    result = con.sql("SELECT COUNT(*) AS row_count FROM orders").fetchone()
    print(f"\nLoaded {result[0]} rows into 'orders' table in '{db_path}'")

    print("\nTable schema:")
    con.sql("DESCRIBE orders").show()

    con.close()
    print(f"\nDatabase saved to '{db_path}'")


def main():
    """Run the complete ETL pipeline."""
    print("\n🚀 Starting ETL Pipeline")
    print("=" * 50)

    raw_df = extract()
    clean_df = transform(raw_df)
    load(clean_df)

    print("\n✅ Pipeline complete!")


if __name__ == "__main__":
    main()
Run the full pipeline end-to-end

This is the moment of truth. One command will extract the messy CSV, clean every data quality issue, and load the results into a persistent database.

  • Run the full pipeline by executing this command in your terminal:
python pipeline.py
  • You should see output from all three steps: EXTRACT (row counts and column types), TRANSFORM (numbered cleaning steps with counts), and LOAD (rows loaded, table schema, and database path).
  • At the very end, you should see Pipeline complete! confirming all three stages finished successfully.
  • Confirm the database file was created by running:
ls
  • You should see ecommerce.duckdb listed alongside your other project files.

Why is this powerful?

Your clean data now lives in a persistent file. You can close your terminal, shut down your computer, and the ecommerce.duckdb file retains all your processed data. Any script can connect to it later for analysis without re-running the pipeline.

Seeing an error?

  • If you see NameError: name 'load' is not defined, make sure your load() function is defined at the module level (not indented inside another function).
  • If you see ModuleNotFoundError: No module named 'duckdb', DuckDB may not be installed in your active Python environment. Run pip install duckdb==1.5.3 and try again.
  • If you see an error about raw_orders.csv not found, make sure you're running the command from your project folder where the CSV lives.

Help me debug my ETL pipeline run

Your ETL pipeline is fully operational. You've gone from a messy CSV to a clean, persistent analytical database in three automated steps. Next up, you'll write SQL queries against this database to extract real business insights.

Analyze with SQL

Your ETL pipeline is working end-to-end. Raw messy data goes in, clean structured data comes out in a DuckDB database. Now it's time to prove the value of all that work.

In this step, you'll write SQL analytical queries against your clean data to extract real business insights. These are the exact query patterns interviewers test for in data engineering roles: aggregations, window functions, and rankings.

In this step, get ready to:
  • Create an analysis script with SQL queries for revenue, customers, and trends.
  • Write advanced queries using window functions for percentages and rankings.
  • Run the full analysis and review your business insights.
Create the analysis script with basic queries

The whole point of loading data into an analytical database is to ask questions of it. Your first three queries cover the most common patterns: grouping, aggregation, and sorting.

  • Create a new file called analyze.py in your project folder.
  • Add the following code to set up the connection and your first three queries:
import duckdb


def run_analysis(db_path="ecommerce.duckdb"):
    """Run analytical SQL queries against the loaded data."""

    con = duckdb.connect(db_path, read_only=True)

    print("=" * 50)
    print("DATA ANALYSIS: E-Commerce Orders")
    print("=" * 50)

    # Query 1: Revenue by category
    print("\n\U0001f4ca Revenue by Product Category:")
    print("-" * 40)
    con.sql("""
        SELECT
            product_category,
            COUNT(*) AS total_orders,
            ROUND(SUM(total_amount), 2) AS total_revenue,
            ROUND(AVG(total_amount), 2) AS avg_order_value
        FROM orders
        GROUP BY product_category
        ORDER BY total_revenue DESC
    """).show()

    # Query 2: Top 5 customers by spending
    print("\n\U0001f464 Top 5 Customers by Spending:")
    print("-" * 40)
    con.sql("""
        SELECT
            customer_name,
            COUNT(*) AS order_count,
            ROUND(SUM(total_amount), 2) AS total_spent
        FROM orders
        GROUP BY customer_name
        ORDER BY total_spent DESC
        LIMIT 5
    """).show()

    # Query 3: Monthly revenue trend
    print("\n\U0001f4c8 Monthly Revenue Trend:")
    print("-" * 40)
    con.sql("""
        SELECT
            strftime(CAST(order_date AS DATE), '%Y-%m') AS month,
            COUNT(*) AS orders,
            ROUND(SUM(total_amount), 2) AS revenue
        FROM orders
        GROUP BY month
        ORDER BY month
    """).show()

    con.close()


if __name__ == "__main__":
    run_analysis()

What does this code do?

This script opens a read-only connection to your DuckDB database. Read-only mode prevents accidental writes while you're just querying.

  • Query 1 groups orders by product_category and calculates total orders, revenue, and average order value for each.
  • Query 2 finds the top 5 biggest spenders using GROUP BY with ORDER BY ... DESC LIMIT 5.
  • Query 3 uses strftime to extract the year-month from each order date, then sums revenue by month to show the trend.
  • Save analyze.py.

Why read_only=True?

When you only need to query data, opening the connection in read-only mode is a best practice. It prevents accidental table drops or inserts from a typo in your SQL.

Add advanced queries with window functions

Window functions let you calculate values across a set of rows related to the current row. They're a step up from basic aggregations and are heavily tested in data engineering interviews.

  • In analyze.py, find the line that says con.close().
  • Add these two queries directly above con.close():
    # Query 4: Payment status breakdown with window function
    print("\n\U0001f4b3 Payment Status Breakdown:")
    print("-" * 40)
    con.sql("""
        SELECT
            payment_status,
            COUNT(*) AS count,
            ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS percentage
        FROM orders
        GROUP BY payment_status
        ORDER BY count DESC
    """).show()

    # Query 5: Revenue by country with ranking
    print("\n\U0001f30d Revenue by Country (with Rank):")
    print("-" * 40)
    con.sql("""
        SELECT
            shipping_country,
            COUNT(*) AS orders,
            ROUND(SUM(total_amount), 2) AS revenue,
            RANK() OVER (ORDER BY SUM(total_amount) DESC) AS rank
        FROM orders
        GROUP BY shipping_country
        ORDER BY rank
    """).show()

What are window functions?

A window function performs a calculation across a set of rows without collapsing them into a single output row.

  • Query 4 uses SUM(COUNT(*)) OVER () to get the total count across all groups. This lets you calculate each payment status as a percentage of the whole.
  • Query 5 uses RANK() OVER (ORDER BY SUM(total_amount) DESC) to assign a rank number to each country based on revenue. Ties get the same rank.
  • Save analyze.py.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before running it.

ⓧ I'd like to double check the full code

import duckdb


def run_analysis(db_path="ecommerce.duckdb"):
    """Run analytical SQL queries against the loaded data."""

    con = duckdb.connect(db_path, read_only=True)

    print("=" * 50)
    print("DATA ANALYSIS: E-Commerce Orders")
    print("=" * 50)

    # Query 1: Revenue by category
    print("\n\U0001f4ca Revenue by Product Category:")
    print("-" * 40)
    con.sql("""
        SELECT
            product_category,
            COUNT(*) AS total_orders,
            ROUND(SUM(total_amount), 2) AS total_revenue,
            ROUND(AVG(total_amount), 2) AS avg_order_value
        FROM orders
        GROUP BY product_category
        ORDER BY total_revenue DESC
    """).show()

    # Query 2: Top 5 customers by spending
    print("\n\U0001f464 Top 5 Customers by Spending:")
    print("-" * 40)
    con.sql("""
        SELECT
            customer_name,
            COUNT(*) AS order_count,
            ROUND(SUM(total_amount), 2) AS total_spent
        FROM orders
        GROUP BY customer_name
        ORDER BY total_spent DESC
        LIMIT 5
    """).show()

    # Query 3: Monthly revenue trend
    print("\n\U0001f4c8 Monthly Revenue Trend:")
    print("-" * 40)
    con.sql("""
        SELECT
            strftime(CAST(order_date AS DATE), '%Y-%m') AS month,
            COUNT(*) AS orders,
            ROUND(SUM(total_amount), 2) AS revenue
        FROM orders
        GROUP BY month
        ORDER BY month
    """).show()

    # Query 4: Payment status breakdown with window function
    print("\n\U0001f4b3 Payment Status Breakdown:")
    print("-" * 40)
    con.sql("""
        SELECT
            payment_status,
            COUNT(*) AS count,
            ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS percentage
        FROM orders
        GROUP BY payment_status
        ORDER BY count DESC
    """).show()

    # Query 5: Revenue by country with ranking
    print("\n\U0001f30d Revenue by Country (with Rank):")
    print("-" * 40)
    con.sql("""
        SELECT
            shipping_country,
            COUNT(*) AS orders,
            ROUND(SUM(total_amount), 2) AS revenue,
            RANK() OVER (ORDER BY SUM(total_amount) DESC) AS rank
        FROM orders
        GROUP BY shipping_country
        ORDER BY rank
    """).show()

    con.close()


if __name__ == "__main__":
    run_analysis()
Run the analysis and review your results
  • Run the analysis script by entering this command in your terminal:
python analyze.py
  • You should see five formatted tables printed to your terminal, each showing a different business insight from your clean data.

What should I see?

Your output will show five sections of results:

  • Revenue by Product Category with total orders, revenue, and average order value per category.
  • Top 5 Customers ranked by total spending.
  • Monthly Revenue Trend showing how revenue changed across 2024.
  • Payment Status Breakdown with each status as a percentage of all orders.
  • Revenue by Country with a rank column showing which countries generate the most revenue.

Seeing a 'table orders does not exist' error?

  • Make sure ecommerce.duckdb exists in the same folder as analyze.py. If it doesn't, run python pipeline.py first to create the database.
  • Check that your terminal is in the project folder where both files live.

Help me debug why my analyze.py script can't find the orders table.

You've just completed a full ETL pipeline from start to finish. Your data went from messy CSV to clean database to actionable business insights. Next up, the Secret Mission will challenge you to add data quality logging to your pipeline.

Secret mission

Add Data Quality Logging

Your pipeline silently fixes data issues, but in production, you need to know exactly what was wrong and what got fixed. Build a data quality validation layer that tracks every issue found during transformation and generates a summary report.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • Project files: generate_data.py, pipeline.py, analyze.py, raw_orders.csv.
  • DuckDB database: ecommerce.duckdb.
  • Installed Python package: duckdb.

Keep everything running

No action needed. Choose this if you plan to keep experimenting with your pipeline or adding new data sources.

  • Your project files stay on your Desktop, ready to use anytime.
  • The duckdb package remains installed in your Python environment.
  • You can re-run python pipeline.py or python analyze.py anytime to regenerate or query your data.

Pause - I'll come back to this later

Since this project is entirely local with no running processes, there is nothing to pause. Your files sit on disk and use minimal storage.

  • Close VS Code to free up memory.
  • Your project files and database will be waiting when you come back.

Delete - I don't want to use this again

Remove all project files and the installed package to start fresh.

  • Close VS Code.
  • Delete your project folder from your Desktop. On macOS/Linux, open your terminal and run:
cd ~/Desktop
rm -rf etl-pipeline-duckdb
  • On Windows, open PowerShell and run:
cd ~\Desktop
Remove-Item -Recurse -Force etl-pipeline-duckdb
  • Uninstall the DuckDB package by running:
pip uninstall duckdb
  • Type y when prompted to confirm the uninstall.

Nice Work!

Nice Work!

Nice work! You just built a complete ETL pipeline from scratch that takes messy e-commerce data and turns it into a clean, queryable analytical database.

You've learned how to:

  • Built a Python ETL pipeline with separate extract, transform, and load functions that handles 8 different data quality issues in raw CSV data.
  • Loaded clean data into a persistent DuckDB analytical database using DataFrame replacement scans.
  • Wrote SQL analytical queries with aggregations, window functions, and rankings to produce business insights from your clean data.
  • Added a data quality logging system that tracks every issue found during transformation and writes it to a separate DuckDB table.

Ready to quiz yourself?