Build a Trusted Sales Data Pipeline

Clean messy sales data into a trusted Parquet report with Python and SQL.

Introduction

30 Second Summary

Sales totals often begin in spreadsheets filled out by different people. A blank price or repeated order can make a confident-looking result quietly wrong.

In this project, you will build a local sales analytics pipeline that turns messy records into a trusted regional revenue report. One run will reveal the quality problems behind the original total before producing corrected results.

What You'll Build

You run pipeline.py to turn a messy CSV into a ranked revenue chart where West stands tallest.

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

  • A quality check that explains why the naive $2,730.00 total falls to a trusted $2,490.00.
  • A clean Parquet dataset containing 10 validated orders that you can query directly with SQL.
  • A regional revenue chart showing West first at $710.00 with a ranked CSV report beside it.
  • Secret Mission: Reuse the reporting pattern to compare product categories without copying its SQL or chart logic.

What do I need before starting?

Some basic Python familiarity helps. You need internet access to install the open-source packages for this local project.

Before We Start

Before the hands-on work begins, take a moment to commit to the purpose of this pipeline. You are turning messy sales records into analytical data that supports trustworthy business decisions.

Set Up a Reproducible Python Project

A trusted report needs a setup that another person can reproduce. Unchecked Python versions can change the result before the data work begins.

You’ll create a named project folder in Visual Studio Code. An isolated virtual environment will hold the exact packages this pipeline needs.

In this step, get ready to:
  • Open the local messy-sales-pipeline folder in Visual Studio Code.
  • Verify Python 3.11 or newer from a PowerShell terminal.
  • Install the pinned pandas, DuckDB, and Matplotlib packages inside .venv.
Create the project folder and verify Python

The pinned packages require Python 3.11 or newer. Checking the interpreter first prevents an incompatible environment from shaping the rest of the project.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code into the search field.
  • Press Enter to open Visual Studio Code.

You’ll see the Visual Studio Code welcome screen. This gives you a starting point for creating the project in a location you can find again.

  • Click Open Folder on the welcome screen.
  • Select Desktop in the folder picker.
  • Click New Folder.
  • Type messy-sales-pipeline as the folder name.
  • Press Enter to create the folder.
  • Select the messy-sales-pipeline folder.
  • Click Select Folder to open it in Visual Studio Code.

The Explorer sidebar now shows messy-sales-pipeline as the open folder. Every project file you create stays together here.

  • Click Terminal in the top menu bar.
  • Click New Terminal to open the project’s PowerShell terminal.

The terminal prompt should point to your messy-sales-pipeline folder. Commands now run against this project.

  • Check the installed Python version by running this command:
py -3 --version

What does this command do?

The py -3 launcher selects an installed Python 3 interpreter. The --version option prints the interpreter version without changing your system.

✔️ I see Python 3.11 or higher

Your interpreter meets the package requirements. Keep this PowerShell terminal open for the next part.

Python is ready

A version of 3.11 or newer can install every pinned package used by this project.

ⓧ I see an older version

The installed interpreter is below the required version. Upgrade Python before creating the virtual environment.

  • Open the official Python downloads for Windows.
  • Install Python 3.11 or newer with the official Python Install Manager or current installer.
  • Close Visual Studio Code after the installation finishes.
  • Reopen Visual Studio Code through Windows search.
  • Return to the messy-sales-pipeline folder from earlier.
  • Reopen the PowerShell terminal from the Terminal menu.
  • Check the upgraded version by running this command:
py -3 --version

What should I see?

The terminal should now report Python 3.11 or newer. That version can support the pinned project packages.

ⓧ Command not found

Windows cannot find the Python launcher yet. Install a current Python release before continuing.

  • Open the official Python downloads for Windows.
  • Install Python 3.11 or newer with the official Python Install Manager or current installer.
  • Close Visual Studio Code after the installation finishes.
  • Reopen Visual Studio Code through Windows search.
  • Return to the messy-sales-pipeline folder from earlier.
  • Reopen the PowerShell terminal from the Terminal menu.
  • Confirm Windows can now find Python by running this command:
py -3 --version

What should I see?

The terminal should print Python 3.11 or newer. The launcher is now available to create the project environment.

Create the pinned requirements file

A requirements file records the package versions that produced your result. Reinstalling from the same file keeps future runs consistent.

  • Click the New File icon at the top of the Explorer sidebar.
  • Enter requirements.txt as the file name.
  • Press Enter to create the file.
  • Add the three pinned packages by pasting this content into requirements.txt:
pandas==3.1.0
duckdb==2.0.0
matplotlib==3.11.2

What does this file control?

  • The pandas==3.1.0 pin provides the tabular inspection and cleaning tools used later.
  • The duckdb==2.0.0 pin provides local SQL queries and Parquet export.
  • The matplotlib==3.11.2 pin provides the final revenue chart.
  • Save requirements.txt.
  • Confirm requirements.txt appears in the Explorer sidebar.

The editor should show three package pins on separate lines. This file is now the reproducible recipe for the project’s Python dependencies.

Requirements file look different?

Check that the file is named requirements.txt. A hidden extra extension can leave you with requirements.txt.txt.

Compare every package name and version with the reference below. One changed character can install a different dependency or stop the installation.

Help me check my requirements file.

✔️ Awesome, I've got everything!

Your requirements file is saved with all three exact package pins.

ⓧ I'd like to double check the full code

  • Compare your complete requirements.txt file with this reference:
pandas==3.1.0
duckdb==2.0.0
matplotlib==3.11.2

What should match?

Your file should contain these three lines in this order. Each package name should use two equals signs before its version.

Build and verify the isolated environment

The .venv directory keeps this project’s packages separate from other Python projects. Directly invoking its interpreter also avoids PowerShell activation policy interruptions.

  • Switch back to the PowerShell terminal from earlier.
  • Create the .venv environment by running this command:
py -3 -m venv .venv

What does this command do?

The venv module creates an isolated Python environment inside .venv. Its Scripts directory contains the Windows interpreter used throughout this project.

  • Confirm the .venv folder appears in the Explorer sidebar.

The terminal returns to the project prompt after creating the environment. The new .venv folder proves the isolated interpreter exists.

Virtual environment missing?

Check that the terminal prompt points to messy-sales-pipeline. Running the command from another folder creates .venv in the wrong location.

Help me find my virtual environment.

The new environment starts without the analytics packages. Installing from requirements.txt recreates the exact dependency set recorded in the project.

  • Install the pinned packages into .venv by running this command:
.\.venv\Scripts\python.exe -m pip install -r requirements.txt

Why use the full interpreter path?

The command runs pip through the interpreter stored inside .venv. The packages therefore stay isolated without activating the environment in PowerShell.

  • Let the package installation finish.
  • Confirm the PowerShell prompt returns without an installation error.

The terminal shows download and installation progress for the pinned packages. Returning to the prompt means the installation process has finished.

Package installation failed?

Check that requirements.txt is saved inside messy-sales-pipeline. The install command reads that file from the current terminal folder.

Confirm that .venv\Scripts\python.exe exists inside the project. A missing interpreter means the environment creation did not finish.

Help me debug my package installation.

Before you run the final check, what three package versions do you expect the isolated interpreter to report?

  • Verify the installed package versions by running this command:
.\.venv\Scripts\python.exe -c "import pandas, duckdb, matplotlib; print(pandas.__version__, duckdb.__version__, matplotlib.__version__)"

What should I see?

The terminal should print 3.1.0 2.0.0 3.11.2. This confirms that the isolated interpreter can import every required package at the pinned version.

Import check failed?

Rerun the installation command if the terminal reports a missing package. This restores anything that did not install successfully.

If DuckDB alone fails to import on Windows, review DuckDB’s Windows installation page for the required Microsoft Visual C++ Redistributable.

Help me fix the failed import check.

That’s the foundation secured. Your project now has an isolated interpreter with verified package versions.

Your reproducible Python environment is ready. Next, you’ll load the messy sales records and expose their first data quality problems.

Inspect the Messy Sales Data

Your isolated Python environment is ready. Its pinned packages give this project a reproducible foundation.

Cleaning data starts with evidence about its current condition. This step creates a visible baseline for the dataset's shape, missing values, and duplicate business keys.

In this step, get ready to:
  • Create a deliberately messy sales dataset with 12 records.
  • Build a pipeline that loads the dataset through pandas.
  • Run an inspection that reveals missing values plus duplicate order IDs.
Create the messy sales fixture

A CSV stores tabular data as comma-separated text. This fixture includes the kinds of inconsistencies that can weaken a business report.

  • Select the messy-sales-pipeline folder in the Visual Studio Code Explorer sidebar.
  • Use the folder creation control at the top of the Explorer sidebar to create data.
  • Select the new data folder in the Explorer sidebar.
  • Use the file creation control at the top of the Explorer sidebar to create sales.csv.
  • Add the 12 sales records to data/sales.csv by pasting this dataset:
order_id,order_date,region,category,units,unit_price
1001,2026-01-03,North,Hardware,2,120
1002,2026-01-04,South,Software,1,300
1003,2026-01-05,East,Hardware,3,80
1003,2026-01-05,East,Hardware,3,80
1004,2026-01-06,,Services,5,40
1005,2026-01-07,West,Software,2,250
1006,2026-01-08,North,Services,4,
1007,2026-01-09,south,Hardware,2,110
1008,2026-01-10,West,Services,6,35
1009,2026-01-11,East,Software,1,320
1010,2026-01-12,North,Hardware,1,125
1011,2026-01-13,South,Services,3,45

What makes this data messy?

  • The repeated 1003 order ID represents a duplicate business record.
  • The 1004 record has a blank region.
  • The 1006 record has a blank unit price.
  • The 1007 record uses lowercase text for its region.
  • Press Ctrl+S to save data/sales.csv.
  • Count the records below the header to confirm that the file contains 12 data rows.
  • Locate the blank region in order 1004.
  • Locate the blank unit price in order 1006.

You can now see two incomplete fields plus one repeated order ID in the source file. Those flaws give the inspection pipeline something real to detect.

Does the CSV look different?

Confirm that sales.csv sits inside the data folder. Keep the blank fields in orders 1004 and 1006 unchanged.

Check that each record occupies one line. The file should contain one header plus 12 data rows.

Still stuck? Help me check the structure of my sales CSV.

Build the inspection pipeline

The pipeline needs access to its libraries before it can read or inspect the dataset. Keeping the imports together also makes every dependency visible at the top of the script.

  • Select the messy-sales-pipeline folder in the Explorer sidebar.
  • Use the file creation control at the top of the Explorer sidebar to create pipeline.py.
  • Add the pipeline imports to pipeline.py by pasting this code:
from pathlib import Path

import duckdb
import matplotlib.pyplot as plt
import pandas as pd

What do these imports provide?

  • Path gives the script a clear way to represent its output directory.
  • DuckDB provides the analytical database tools used by the finished pipeline.
  • Matplotlib provides the charting tools used by the finished pipeline.
  • pandas loads the source file into a table that Python can inspect.
  • Press Ctrl+S to save pipeline.py.
  • Confirm that the four import lines appear at the top of pipeline.py.

Are the imports underlined?

Confirm that Visual Studio Code is still open to the messy-sales-pipeline folder. The project packages already exist inside .venv.

Check each import against the code block. A missing letter can stop Python before the inspection begins.

Need help? Help me diagnose the import warnings in pipeline.py.

Shared constants keep file locations plus required fields in one place. The CRITICAL_COLUMNS list identifies the values a sales record needs for later analysis.

  • Add the shared file paths plus critical columns below the imports by pasting this code:
DATA_FILE = "data/sales.csv"
OUTPUT_DIR = Path("output")
PARQUET_FILE = "output/clean_sales.parquet"
SUMMARY_FILE = "output/regional_revenue.csv"
CHART_FILE = "output/regional_revenue.png"
CRITICAL_COLUMNS = [
    "order_id",
    "order_date",
    "category",
    "units",
    "unit_price",
]

What does this configuration do?

DATA_FILE points to the raw source file. The output constants reserve consistent locations for the trusted dataset plus its reports.

CRITICAL_COLUMNS lists the fields needed to identify an order or calculate its revenue. A missing region can be labeled later without discarding an otherwise usable sale.

  • Press Ctrl+S to save the new constants in pipeline.py.
  • Check the current script with the existing virtual environment by running this command:
.\.venv\Scripts\python.exe pipeline.py

What does this check prove?

PowerShell should return to its prompt without reporting an import or syntax problem. This confirms that Python can load the current script through the isolated environment.

Did the script report an import problem?

Confirm that the command starts with .\.venv\Scripts\python.exe. This selects the interpreter that contains the pinned packages.

Compare the imports plus constants with the code blocks above. Fix any spelling or indentation differences before continuing.

Need another pair of eyes? Help me fix the first run of pipeline.py.

A DataFrame gives pandas a table-like structure for the sales records. The loading function also converts order_date into dates before inspection.

  • Add load_sales() below CRITICAL_COLUMNS by pasting this function:
def load_sales():
    raw_sales = pd.read_csv(DATA_FILE)
    raw_sales["order_date"] = pd.to_datetime(
        raw_sales["order_date"], errors="coerce"
    )
    return raw_sales

What does this function do?

  • pd.read_csv() loads the source records from DATA_FILE.
  • pd.to_datetime() converts order_date into a date-aware pandas column.
  • errors="coerce" turns an invalid date into a missing date value that the inspection can detect.
  • raw_sales keeps the source records unchanged for later quality checks.
  • Press Ctrl+S to save load_sales().
  • Confirm that load_sales() appears below the closing bracket of CRITICAL_COLUMNS.

Does the function look misaligned?

Keep the statements inside load_sales() indented by four spaces. Keep return raw_sales aligned with the two statements above it.

Need help with the structure? Help me check the indentation in load_sales().

The inspection function turns vague concerns about data quality into counts you can verify. The entry point runs the same loading plus inspection sequence whenever you execute the script.

  • Complete pipeline.py by pasting this code below load_sales():
def inspect_data(raw_sales):
    print("\nFirst five rows:")
    print(raw_sales.head())
    print("\nMissing values by column:")
    print(raw_sales.isna().sum())
    duplicate_count = raw_sales.duplicated(
        subset=["order_id"], keep="first"
    ).sum()
    incomplete_count = raw_sales[CRITICAL_COLUMNS].isna().any(axis=1).sum()
    print(f"\nRaw rows: {len(raw_sales)}")
    print(f"Duplicate order IDs: {duplicate_count}")
    print(f"Rows missing critical values: {incomplete_count}")


def main():
    raw_sales = load_sales()
    inspect_data(raw_sales)


if __name__ == "__main__":
    main()

How does the inspection work?

  • head() displays the first five rows so you can see the inferred columns plus values.
  • isna().sum() counts missing values in each column.
  • duplicated() counts repeated order IDs while keeping the first occurrence as the original.
  • The critical-column check counts rows that cannot support a complete sales calculation.
  • main() loads the file before passing the resulting DataFrame into the inspection.
  • Press Ctrl+S to save the completed pipeline.py.

Seeing red underlines in the new functions?

Check that inspect_data() begins at the far left of the editor. Keep each statement inside the function indented by four spaces.

Confirm that main() also begins at the far left. The final call to main() stays indented inside the last condition.

Still stuck? Help me check the structure of inspect_data() and main().

Use the reference below to compare every line before running the full inspection.

✔️ Awesome, I've got everything!

Great. Double-check that pipeline.py is saved before moving to the terminal.

ⓧ I'd like to double check the full code

from pathlib import Path

import duckdb
import matplotlib.pyplot as plt
import pandas as pd

DATA_FILE = "data/sales.csv"
OUTPUT_DIR = Path("output")
PARQUET_FILE = "output/clean_sales.parquet"
SUMMARY_FILE = "output/regional_revenue.csv"
CHART_FILE = "output/regional_revenue.png"
CRITICAL_COLUMNS = [
    "order_id",
    "order_date",
    "category",
    "units",
    "unit_price",
]


def load_sales():
    raw_sales = pd.read_csv(DATA_FILE)
    raw_sales["order_date"] = pd.to_datetime(
        raw_sales["order_date"], errors="coerce"
    )
    return raw_sales


def inspect_data(raw_sales):
    print("\nFirst five rows:")
    print(raw_sales.head())
    print("\nMissing values by column:")
    print(raw_sales.isna().sum())
    duplicate_count = raw_sales.duplicated(
        subset=["order_id"], keep="first"
    ).sum()
    incomplete_count = raw_sales[CRITICAL_COLUMNS].isna().any(axis=1).sum()
    print(f"\nRaw rows: {len(raw_sales)}")
    print(f"Duplicate order IDs: {duplicate_count}")
    print(f"Rows missing critical values: {incomplete_count}")


def main():
    raw_sales = load_sales()
    inspect_data(raw_sales)


if __name__ == "__main__":
    main()

How do the pieces connect?

The constants point to the source file plus future outputs. The three functions load the records, inspect their quality, and control the execution order.

Run the baseline inspection

Before you run the pipeline, which source problems do you expect the counts to reveal?

  • Run the completed pipeline from the existing PowerShell terminal by entering this command:
.\.venv\Scripts\python.exe pipeline.py

What should I see?

The terminal first displays five sample rows. It then prints a missing-value count for every column.

  • You should see Raw rows: 12.
  • You should see unit_price with one missing value.
  • You should see Duplicate order IDs: 1.
  • You should see Rows missing critical values: 1.

Do your counts differ?

Confirm that the PowerShell terminal is still inside the messy-sales-pipeline folder. The relative path in DATA_FILE depends on that location.

Compare data/sales.csv with the fixture above. Keep both 1003 rows plus the blank unit price for 1006.

Need help interpreting the result? Help me debug the inspection counts from pipeline.py.

That baseline is now working. You can point to the duplicate plus the incomplete record before making any transformation.

Next, you will calculate a plausible revenue total from these untouched rows. That result will test whether a number can look useful before the underlying data deserves trust.

Expose the Naive Revenue Report

Your first inspection proved that the CSV contains a repeated order ID and an incomplete sales record. A revenue calculation can still print a polished number while carrying those flaws into the result.

This step lets the raw rows produce a naive total. You will trace how the duplicate inflates that total while the incomplete calculation quietly disappears.

In this step, get ready to:
  • Calculate revenue directly from the uncleaned sales rows.
  • Print the naive revenue total beside the existing quality counts.
  • Trace the records that make the result unreliable.
Calculate revenue without quality rules

A naive report applies arithmetic directly to the raw DataFrame. This gives every existing data problem a chance to affect the result.

  • Add naive_report() between inspect_data() and main() in pipeline.py by pasting this code:
def naive_report(raw_sales):
    naive_sales = raw_sales.copy()
    naive_sales["revenue"] = (
        naive_sales["units"] * naive_sales["unit_price"]
    )
    return naive_sales["revenue"].sum()

What does this code do?

  • The raw_sales.copy() call creates a separate DataFrame for the trial calculation.
  • The multiplication calculates revenue for each row from units and unit_price.
  • The final sum() call combines the available row values into one total.

Seeing indentation warnings?

Make sure def naive_report(raw_sales): starts at the far left. Keep every line inside the function indented by four spaces.

If the function still looks misaligned, help me check the indentation in naive_report().

Print the naive total

The new function returns a number without printing it. The main() function needs to call it so every pipeline run exposes the naive total.

  • Find the existing main() function in pipeline.py.
  • Replace the entire function with this updated version:
def main():
    raw_sales = load_sales()
    inspect_data(raw_sales)

    naive_total = naive_report(raw_sales)
    print(f"Naive revenue total: ${naive_total:,.2f}")

How does the total reach the terminal?

  • The naive_total variable stores the value returned by naive_report().
  • The print statement formats that value with a currency symbol and two decimal places.
  • Save pipeline.py.

✔️ Awesome, I've got everything!

Your saved file now contains the naive calculation and the updated pipeline entry point.

ⓧ I'd like to double check the full code

from pathlib import Path

import duckdb
import matplotlib.pyplot as plt
import pandas as pd

DATA_FILE = "data/sales.csv"
OUTPUT_DIR = Path("output")
PARQUET_FILE = "output/clean_sales.parquet"
SUMMARY_FILE = "output/regional_revenue.csv"
CHART_FILE = "output/regional_revenue.png"
CRITICAL_COLUMNS = [
    "order_id",
    "order_date",
    "category",
    "units",
    "unit_price",
]


def load_sales():
    raw_sales = pd.read_csv(DATA_FILE)
    raw_sales["order_date"] = pd.to_datetime(
        raw_sales["order_date"], errors="coerce"
    )
    return raw_sales


def inspect_data(raw_sales):
    print("\nFirst five rows:")
    print(raw_sales.head())
    print("\nMissing values by column:")
    print(raw_sales.isna().sum())
    duplicate_count = raw_sales.duplicated(
        subset=["order_id"], keep="first"
    ).sum()
    incomplete_count = raw_sales[CRITICAL_COLUMNS].isna().any(axis=1).sum()
    print(f"\nRaw rows: {len(raw_sales)}")
    print(f"Duplicate order IDs: {duplicate_count}")
    print(f"Rows missing critical values: {incomplete_count}")


def naive_report(raw_sales):
    naive_sales = raw_sales.copy()
    naive_sales["revenue"] = (
        naive_sales["units"] * naive_sales["unit_price"]
    )
    return naive_sales["revenue"].sum()


def main():
    raw_sales = load_sales()
    inspect_data(raw_sales)

    naive_total = naive_report(raw_sales)
    print(f"Naive revenue total: ${naive_total:,.2f}")


if __name__ == "__main__":
    main()
Trace the misleading rows

The raw data already reveals why this calculation is unreliable. Pandas counts both copies of a duplicated sale while the missing price produces no usable revenue value.

  • Select data/sales.csv from the file tree in Visual Studio Code.
  • Find the first row with order_id set to 1003.
  • Compare that row with the row directly below it.
  • Find the row with order_id set to 1006.

The duplicated 1003 sale contributes $240.00 twice. The 1006 calculation has no unit_price value, so it drops out of the sum.

Before you run the pipeline, predict whether the total will look plausible while the quality counts still flag problems.

  • Return to the PowerShell terminal from earlier.
  • Expose the naive result by running this command:
.\.venv\Scripts\python.exe pipeline.py

What should you see?

  • The existing checks still report Raw rows: 12 and Duplicate order IDs: 1.
  • The missing-value check still reports Rows missing critical values: 1.
  • The new report prints Naive revenue total: $2,730.00.

You have exposed the trap: the pipeline now produces a convincing total while preserving visible evidence that the source data is unreliable.

Missing the naive total?

Check that main() calls naive_report(raw_sales). Confirm that the print statement sits inside main() with four spaces of indentation.

If the script stops before printing the total, help me debug my naive revenue report.

Your report now demonstrates how a believable metric can hide duplicate and incomplete records. Next, you will apply explicit quality rules before any row reaches the trusted result.

Clean and Export Trusted Sales Data

Your first report exposed a serious data quality problem. The duplicate order inflated the total to $2,730.00.

You will now use pandas to apply explicit cleaning rules before calculating the trusted result. You will then save the validated rows in a format that later analysis can query directly.

In this step, get ready to:
  • Standardize inconsistent text and filter unreliable sales records.
  • Validate the clean orders and compare their revenue with the naive total.
  • Export the trusted sales data to a Parquet file.
Apply explicit cleaning rules

A trusted dataset needs a clear contract for which rows survive. The cleaning function standardizes text before removing records that violate that contract.

  • In pipeline.py find the final return naive_sales["revenue"].sum() line in naive_report().
  • Add the following function below naive_report():
def clean_sales(raw_sales):
    cleaned_sales = raw_sales.copy()
    cleaned_sales["region"] = (
        cleaned_sales["region"]
        .fillna("Unknown")
        .str.strip()
        .str.title()
    )
    cleaned_sales["category"] = (
        cleaned_sales["category"].str.strip().str.title()
    )
    cleaned_sales = cleaned_sales.drop_duplicates(
        subset=["order_id"], keep="first"
    )
    cleaned_sales = cleaned_sales.dropna(subset=CRITICAL_COLUMNS)
    cleaned_sales = cleaned_sales[
        (cleaned_sales["units"] > 0)
        & (cleaned_sales["unit_price"] > 0)
    ].copy()
    cleaned_sales["revenue"] = (
        cleaned_sales["units"] * cleaned_sales["unit_price"]
    )

    assert cleaned_sales["order_id"].is_unique
    assert cleaned_sales[CRITICAL_COLUMNS].notna().all().all()
    return cleaned_sales

How the cleaning contract works

  • The region transformation replaces a missing region with Unknown.
  • The string transformations remove surrounding spaces and standardize capitalization.
  • The duplicate filter keeps the first occurrence of each order_id.
  • The missing-value filter removes rows without every required analytical value.
  • The positive-value filter rejects rows with invalid units or prices.
  • The assertions stop the pipeline if duplicate IDs or incomplete critical values survive.
Compare the naive and clean totals

Cleaning becomes meaningful when you can measure its effect. The corrected row count and revenue total reveal exactly how the quality rules changed the report.

  • In main() find the line that prints the naive revenue total.
  • Add the following block directly below that line:
    cleaned_sales = clean_sales(raw_sales)
    clean_total = cleaned_sales["revenue"].sum()
    print(f"Clean rows: {len(cleaned_sales)}")
    print(f"Clean revenue total: ${clean_total:,.2f}")

What does this code prove?

  • The call to clean_sales() applies every quality rule before calculating revenue.
  • The clean row count shows how many validated orders remain.
  • The clean total measures revenue using only those validated orders.
  • Save pipeline.py.

Before you run the pipeline, which total do you expect to be lower after the duplicate and incomplete row are removed?

  • Run the updated pipeline in the PowerShell terminal:
.\.venv\Scripts\python.exe pipeline.py

What should you see?

You should still see Naive revenue total: $2,730.00 because the naive calculation remains unchanged.

Below it you should see Clean rows: 10 followed by Clean revenue total: $2,490.00.

Clean result missing?

Check that the new lines sit inside main() with the same indentation as the naive report lines.

Check that clean_sales() appears above main() in pipeline.py.

Help me diagnose my cleaning result.

You have now replaced an inflated metric with a validated total based on 10 reliable orders.

Export the trusted rows to Parquet

A clean result becomes reusable when you save the validated table outside the script. Parquet preserves the tabular dataset for later analysis.

DuckDB can write the cleaned DataFrame directly to output/clean_sales.parquet.

  • In pipeline.py find the end of clean_sales().
  • Add the following function above main():
def export_parquet(cleaned_sales):
    OUTPUT_DIR.mkdir(exist_ok=True)
    duckdb.sql(
        f"COPY (SELECT * FROM cleaned_sales) "
        f"TO '{PARQUET_FILE}' (FORMAT parquet)"
    )

How the export works

  • The directory step creates output when it does not already exist.
  • The duckdb.sql call reads cleaned_sales from the current Python scope.
  • The copy statement writes those trusted rows to the configured Parquet path.
  • In main() find the line that prints the clean revenue total.
  • Add the following lines directly below it:
    export_parquet(cleaned_sales)
    print(f"\nSaved {PARQUET_FILE}")

What does this addition do?

The first line exports the same cleaned rows used for the corrected total. The second line confirms the destination after the export succeeds.

  • Save pipeline.py.

Before you run the complete pipeline, what file do you expect to find beside the corrected terminal result?

  • Run the complete pipeline in the PowerShell terminal:
.\.venv\Scripts\python.exe pipeline.py

What should you see?

You should see Clean rows: 10 and Clean revenue total: $2,490.00 after the naive result.

The final line should say Saved output/clean_sales.parquet.

  • In the Visual Studio Code Explorer sidebar expand the output folder.

You should see clean_sales.parquet inside the folder. That file contains the 10 validated orders behind the corrected total.

Parquet file not appearing?

Check that export_parquet(cleaned_sales) sits inside main() after the clean total is calculated.

Check that export_parquet() uses OUTPUT_DIR and PARQUET_FILE exactly as shown.

Help me fix my Parquet export.

✔️ Awesome, I've got everything!

Your pipeline now cleans the raw records and saves 10 validated orders to output/clean_sales.parquet.

ⓧ I'd like to double check the full code

  • Compare your pipeline.py with this complete reference:
from pathlib import Path

import duckdb
import matplotlib.pyplot as plt
import pandas as pd

DATA_FILE = "data/sales.csv"
OUTPUT_DIR = Path("output")
PARQUET_FILE = "output/clean_sales.parquet"
SUMMARY_FILE = "output/regional_revenue.csv"
CHART_FILE = "output/regional_revenue.png"
CRITICAL_COLUMNS = [
    "order_id",
    "order_date",
    "category",
    "units",
    "unit_price",
]


def load_sales():
    raw_sales = pd.read_csv(DATA_FILE)
    raw_sales["order_date"] = pd.to_datetime(
        raw_sales["order_date"], errors="coerce"
    )
    return raw_sales


def inspect_data(raw_sales):
    print("\nFirst five rows:")
    print(raw_sales.head())
    print("\nMissing values by column:")
    print(raw_sales.isna().sum())
    duplicate_count = raw_sales.duplicated(
        subset=["order_id"], keep="first"
    ).sum()
    incomplete_count = raw_sales[CRITICAL_COLUMNS].isna().any(axis=1).sum()
    print(f"\nRaw rows: {len(raw_sales)}")
    print(f"Duplicate order IDs: {duplicate_count}")
    print(f"Rows missing critical values: {incomplete_count}")


def naive_report(raw_sales):
    naive_sales = raw_sales.copy()
    naive_sales["revenue"] = (
        naive_sales["units"] * naive_sales["unit_price"]
    )
    return naive_sales["revenue"].sum()


def clean_sales(raw_sales):
    cleaned_sales = raw_sales.copy()
    cleaned_sales["region"] = (
        cleaned_sales["region"]
        .fillna("Unknown")
        .str.strip()
        .str.title()
    )
    cleaned_sales["category"] = (
        cleaned_sales["category"].str.strip().str.title()
    )
    cleaned_sales = cleaned_sales.drop_duplicates(
        subset=["order_id"], keep="first"
    )
    cleaned_sales = cleaned_sales.dropna(subset=CRITICAL_COLUMNS)
    cleaned_sales = cleaned_sales[
        (cleaned_sales["units"] > 0)
        & (cleaned_sales["unit_price"] > 0)
    ].copy()
    cleaned_sales["revenue"] = (
        cleaned_sales["units"] * cleaned_sales["unit_price"]
    )

    assert cleaned_sales["order_id"].is_unique
    assert cleaned_sales[CRITICAL_COLUMNS].notna().all().all()
    return cleaned_sales


def export_parquet(cleaned_sales):
    OUTPUT_DIR.mkdir(exist_ok=True)
    duckdb.sql(
        f"COPY (SELECT * FROM cleaned_sales) "
        f"TO '{PARQUET_FILE}' (FORMAT parquet)"
    )


def main():
    raw_sales = load_sales()
    inspect_data(raw_sales)

    naive_total = naive_report(raw_sales)
    print(f"Naive revenue total: ${naive_total:,.2f}")

    cleaned_sales = clean_sales(raw_sales)
    clean_total = cleaned_sales["revenue"].sum()
    print(f"Clean rows: {len(cleaned_sales)}")
    print(f"Clean revenue total: ${clean_total:,.2f}")

    export_parquet(cleaned_sales)
    print(f"\nSaved {PARQUET_FILE}")


if __name__ == "__main__":
    main()

Your trusted dataset is ready. Next, you will query the Parquet file and turn its regional totals into a report you can show.

Query and Visualize Trusted Revenue

Your cleaning rules now produce 10 validated orders with $2,490.00 in total revenue. The trusted records are stored in a Parquet file that you can analyze directly.

A trusted dataset becomes useful when analysts can answer a business question from it. Your question is which region generated the most revenue.

DuckDB will use SQL to rank the regions. Matplotlib will turn that ranking into a chart you can show someone.

In this step, get ready to:
  • Query the trusted Parquet file with SQL.
  • Save the regional ranking as a CSV report.
  • Visualize regional revenue in a saved bar chart.
Query the Parquet file with SQL

DuckDB can query the Parquet path directly. The query groups the trusted revenue by region before sorting the totals from highest to lowest.

  • In pipeline.py, find the export_parquet() function.
  • Add the regional query below export_parquet() by pasting this code:
def query_regional_revenue():
    regional_revenue = duckdb.sql(
        f"""
        SELECT region, ROUND(SUM(revenue), 2) AS revenue
        FROM '{PARQUET_FILE}'
        GROUP BY region
        ORDER BY revenue DESC
        """
    ).df()
    regional_revenue.to_csv(SUMMARY_FILE, index=False)
    return regional_revenue

What does this code do?

  • The SQL query reads trusted records directly from PARQUET_FILE.
  • The GROUP BY region clause combines orders from the same region.
  • The SUM(revenue) calculation produces one revenue total for each region.
  • The ORDER BY revenue DESC clause places the highest total first.
  • The result is saved to output/regional_revenue.csv for reuse outside Python.
  • Save pipeline.py.
  • Confirm the existing pipeline remains runnable by using this command in the PowerShell terminal from earlier:
.\.venv\Scripts\python.exe pipeline.py

What does this command do?

This command runs pipeline.py with the isolated Python interpreter from .venv. It confirms the new function has valid Python syntax.

You should still see Clean rows: 10 followed by Clean revenue total: $2,490.00.

The query function is ready. The pipeline now needs to call it after exporting the trusted Parquet file.

  • In main(), find the current final lines:
    export_parquet(cleaned_sales)
    print(f"\nSaved {PARQUET_FILE}")

What are these lines doing now?

These lines export the clean records before confirming the Parquet path. This is the current end of the pipeline.

  • Replace those final lines with this query and reporting sequence:
    export_parquet(cleaned_sales)
    regional_revenue = query_regional_revenue()
    print("\nRegional revenue:")
    print(regional_revenue.to_string(index=False))

    print(f"\nSaved {PARQUET_FILE}")
    print(f"Saved {SUMMARY_FILE}")

How does the pipeline use the query?

  • The query_regional_revenue() call creates the sorted regional report.
  • The terminal prints the complete report without the DataFrame index.
  • The final messages confirm both saved data paths.
  • Save pipeline.py.

Before you run the query, consider which region you expect to rank first.

  • Generate the regional report by running this command:
.\.venv\Scripts\python.exe pipeline.py

What does this run prove?

The command rebuilds the trusted Parquet file before querying it. The saved regional report therefore comes from the same validated records every time.

You should see a regional revenue table with West first at 710.0. You should also see a confirmation for output/regional_revenue.csv.

Regional report missing?

Check that query_regional_revenue() sits above main() in pipeline.py.

Check that the query reads from PARQUET_FILE and saves to SUMMARY_FILE.

Help me debug my DuckDB regional revenue query.

Turn the regional totals into a chart

A sorted table gives you exact values. A bar chart makes the leading region visible at a glance.

  • In pipeline.py, find the end of query_regional_revenue().
  • Add the chart function below query_regional_revenue() by pasting this code:
def save_chart(regional_revenue):
    fig, ax = plt.subplots(figsize=(8, 4.5))
    ax.bar(
        regional_revenue["region"],
        regional_revenue["revenue"],
        color="#4C78A8",
    )
    ax.set(
        title="Trusted Revenue by Region",
        xlabel="Region",
        ylabel="Revenue ($)",
    )
    fig.savefig(CHART_FILE, dpi=150, bbox_inches="tight")
    plt.close(fig)

How does the chart work?

  • The figsize value creates a wide chart that fits all five region labels.
  • The ax.bar() call maps each region to its trusted revenue total.
  • The title identifies the metric as trusted regional revenue.
  • The axis labels explain the categories and dollar values.
  • The figure is saved to output/regional_revenue.png before Matplotlib releases it from memory.
  • Save pipeline.py.
  • Confirm the pipeline still prints the regional table by running this command:
.\.venv\Scripts\python.exe pipeline.py

Why run the pipeline here?

This run checks that the new chart function has valid syntax. The regional query should still complete successfully.

You should see the regional table headed by West. The terminal should finish without a Python syntax error.

The chart function is valid. The last code change connects it to the report stored in regional_revenue.

  • In main(), find the current reporting sequence:
    export_parquet(cleaned_sales)
    regional_revenue = query_regional_revenue()
    print("\nRegional revenue:")
    print(regional_revenue.to_string(index=False))

    print(f"\nSaved {PARQUET_FILE}")
    print(f"Saved {SUMMARY_FILE}")

What is missing from this sequence?

The current sequence creates the Parquet and CSV files. The chart function has not been called from main() yet.

  • Replace that reporting sequence with the completed version:
    export_parquet(cleaned_sales)
    regional_revenue = query_regional_revenue()
    print("\nRegional revenue:")
    print(regional_revenue.to_string(index=False))

    save_chart(regional_revenue)
    print(f"\nSaved {PARQUET_FILE}")
    print(f"Saved {SUMMARY_FILE}")
    print(f"Saved {CHART_FILE}")

How is the chart connected?

The save_chart(regional_revenue) call passes the sorted SQL result into Matplotlib. The final message confirms the chart path after the file is saved.

  • Save pipeline.py.
Run and inspect the trusted report

The complete pipeline now cleans the source records before answering the regional revenue question. One final run should recreate every trusted output from the original CSV.

Before you run the complete pipeline, consider which bar should be tallest in the saved chart.

  • Rebuild every output by running this command:
.\.venv\Scripts\python.exe pipeline.py

What does the complete run produce?

  • The pipeline validates 10 clean orders with $2,490.00 in revenue.
  • DuckDB writes the trusted records to output/clean_sales.parquet.
  • The SQL query writes the regional ranking to output/regional_revenue.csv.
  • Matplotlib writes the chart to output/regional_revenue.png.

You should see Clean rows: 10 and Clean revenue total: $2,490.00 before the regional table. The terminal should finish with three saved-file confirmations.

  • Expand the output folder in the Visual Studio Code file sidebar.
  • Select regional_revenue.csv to inspect the five regional totals.

What should the regional report show?

  • West ranks first with 710.0 in revenue.
  • East follows with 560.0 in revenue.
  • South follows with 505.0 in revenue.
  • North follows with 365.0 in revenue.
  • Unknown finishes with 200.0 in revenue.
  • Select regional_revenue.png in the same output folder.

You should see a chart titled Trusted Revenue by Region. West should be the tallest bar at 710.0.

Why does this report deserve trust?

The naive total included a repeated order before analysis. It also allowed an incomplete record to disappear silently from the calculation.

This report starts from unique orders with complete critical values. Its SQL table and chart both use the same validated Parquet dataset.

Chart file missing or blank?

Check that save_chart(regional_revenue) appears after the regional query inside main().

Check that fig.savefig() uses CHART_FILE.

Help me debug my saved regional revenue chart.

That completes the reporting loop. Your source CSV now becomes a validated dataset, a ranked table, and a presentation-ready chart through one reproducible command.

✔️ Awesome, I've got everything!

Great work. Save pipeline.py and keep the three files in output as evidence of the completed pipeline.

ⓧ I'd like to double check the full code

from pathlib import Path

import duckdb
import matplotlib.pyplot as plt
import pandas as pd

DATA_FILE = "data/sales.csv"
OUTPUT_DIR = Path("output")
PARQUET_FILE = "output/clean_sales.parquet"
SUMMARY_FILE = "output/regional_revenue.csv"
CHART_FILE = "output/regional_revenue.png"
CRITICAL_COLUMNS = [
    "order_id",
    "order_date",
    "category",
    "units",
    "unit_price",
]


def load_sales():
    raw_sales = pd.read_csv(DATA_FILE)
    raw_sales["order_date"] = pd.to_datetime(
        raw_sales["order_date"], errors="coerce"
    )
    return raw_sales


def inspect_data(raw_sales):
    print("\nFirst five rows:")
    print(raw_sales.head())
    print("\nMissing values by column:")
    print(raw_sales.isna().sum())
    duplicate_count = raw_sales.duplicated(
        subset=["order_id"], keep="first"
    ).sum()
    incomplete_count = raw_sales[CRITICAL_COLUMNS].isna().any(axis=1).sum()
    print(f"\nRaw rows: {len(raw_sales)}")
    print(f"Duplicate order IDs: {duplicate_count}")
    print(f"Rows missing critical values: {incomplete_count}")


def naive_report(raw_sales):
    naive_sales = raw_sales.copy()
    naive_sales["revenue"] = (
        naive_sales["units"] * naive_sales["unit_price"]
    )
    return naive_sales["revenue"].sum()


def clean_sales(raw_sales):
    cleaned_sales = raw_sales.copy()
    cleaned_sales["region"] = (
        cleaned_sales["region"]
        .fillna("Unknown")
        .str.strip()
        .str.title()
    )
    cleaned_sales["category"] = (
        cleaned_sales["category"].str.strip().str.title()
    )
    cleaned_sales = cleaned_sales.drop_duplicates(
        subset=["order_id"], keep="first"
    )
    cleaned_sales = cleaned_sales.dropna(subset=CRITICAL_COLUMNS)
    cleaned_sales = cleaned_sales[
        (cleaned_sales["units"] > 0)
        & (cleaned_sales["unit_price"] > 0)
    ].copy()
    cleaned_sales["revenue"] = (
        cleaned_sales["units"] * cleaned_sales["unit_price"]
    )

    assert cleaned_sales["order_id"].is_unique
    assert cleaned_sales[CRITICAL_COLUMNS].notna().all().all()
    return cleaned_sales


def export_parquet(cleaned_sales):
    OUTPUT_DIR.mkdir(exist_ok=True)
    duckdb.sql(
        f"COPY (SELECT * FROM cleaned_sales) "
        f"TO '{PARQUET_FILE}' (FORMAT parquet)"
    )


def query_regional_revenue():
    regional_revenue = duckdb.sql(
        f"""
        SELECT region, ROUND(SUM(revenue), 2) AS revenue
        FROM '{PARQUET_FILE}'
        GROUP BY region
        ORDER BY revenue DESC
        """
    ).df()
    regional_revenue.to_csv(SUMMARY_FILE, index=False)
    return regional_revenue


def save_chart(regional_revenue):
    fig, ax = plt.subplots(figsize=(8, 4.5))
    ax.bar(
        regional_revenue["region"],
        regional_revenue["revenue"],
        color="#4C78A8",
    )
    ax.set(
        title="Trusted Revenue by Region",
        xlabel="Region",
        ylabel="Revenue ($)",
    )
    fig.savefig(CHART_FILE, dpi=150, bbox_inches="tight")
    plt.close(fig)


def main():
    raw_sales = load_sales()
    inspect_data(raw_sales)

    naive_total = naive_report(raw_sales)
    print(f"Naive revenue total: ${naive_total:,.2f}")

    cleaned_sales = clean_sales(raw_sales)
    clean_total = cleaned_sales["revenue"].sum()
    print(f"Clean rows: {len(cleaned_sales)}")
    print(f"Clean revenue total: ${clean_total:,.2f}")

    export_parquet(cleaned_sales)
    regional_revenue = query_regional_revenue()
    print("\nRegional revenue:")
    print(regional_revenue.to_string(index=False))

    save_chart(regional_revenue)
    print(f"\nSaved {PARQUET_FILE}")
    print(f"Saved {SUMMARY_FILE}")
    print(f"Saved {CHART_FILE}")


if __name__ == "__main__":
    main()

How to use this reference

Compare each function and the final main() sequence with your saved file. Matching this reference gives the pipeline the exact query and chart flow used in this step.

Secret mission

Make the Report Reusable

Turn your fixed regional report into a reusable reporting pattern. One pipeline run will create revenue tables and charts for both approved dimensions without duplicating the SQL or chart logic.

Clean Up Your Resources

Clean Up Your Resources

Your reusable pipeline and reports are saved in the messy-sales-pipeline folder. Everything runs locally, so keeping these files creates no ongoing service charges.

Resources you used:

  • A .venv environment containing pandas 3.1.0, DuckDB 2.0.0, plus Matplotlib 3.11.2.
  • The source CSV file at data/sales.csv.
  • The project files requirements.txt plus pipeline.py.
  • The output folder containing output/clean_sales.parquet, output/region_revenue.csv, output/region_revenue.png, output/category_revenue.csv, plus output/category_revenue.png.

Keep everything running

No action needed. Choose this if you plan to refine the cleaning rules or generate more reports from the same source data.

  • Keep the messy-sales-pipeline folder in its current location.
  • Keep data/sales.csv as the original input for future pipeline runs.
  • Keep the output folder if you want to retain the generated dataset and charts.

Pause - I'll come back to this later

Pausing closes your editor while preserving the complete project. No local server or cloud resource remains active.

  • Save any open files in Visual Studio Code.
  • Close Visual Studio Code.
  • Leave the messy-sales-pipeline folder in its current location for your next session.

Delete - I don't want to use this again

Deleting the full project folder is permanent once it leaves your system's recovery area. Your source data disappears with the generated reports if you choose complete removal.

  • Close Visual Studio Code.
  • Locate the current messy-sales-pipeline folder with your Windows file manager.
  • Delete the output folder if you only want to remove the generated Parquet dataset and reports.
  • Delete the .venv folder if you want to remove the packages installed for this project.
  • Delete the entire messy-sales-pipeline folder if you want to remove every source file, package, and report.
  • Confirm that the folder you selected no longer appears in its previous location.

Nice Work!

Nice Work!

Excellent work! Your local pipeline now turns messy sales records into validated data, ranked revenue reports, and presentation-ready charts.

What you learned:

  • Built a reproducible Python pipeline with pinned dependencies for consistent local results.
  • Applied data quality rules that transformed 12 messy rows into 10 validated sales records totaling $2,490.00.
  • Queried a trusted Parquet dataset with DuckDB SQL to produce ranked CSV reports plus Matplotlib charts.
  • Secret Mission: Built a reusable reporting pattern that produces region and category outputs from one validated SQL-and-chart workflow.

Ready to quiz yourself?