Build a Data Quality Triage Pipeline

Introduction

30 Second Summary

Support-ticket totals can look convincing at a glance. A duplicate or impossible value can quietly point a team toward the wrong problem.

In this project, you will build a Python pipeline that rejects unreliable support-ticket records before they shape an operational summary. The clean records produce SQL metrics inside a Markdown audit report.

What You'll Build

You'll open a GitHub repository where a messy ticket file becomes a rendered audit report built only from validated records.

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

• A repeatable triage run that separates five accepted records from five rejected records.
• A clean SQL summary showing grouped priority counts plus a 6.0-hour average resolution time.
• A rendered Markdown audit report showing a 50.0% quality score plus a recommendation to investigate Billing first.
• Secret Mission: Three new adversarial records that prove the existing validator catches unseen bad data without code changes.

Are there any prerequisites?

You need a computer running Windows 10 or newer. You also need Visual Studio Code plus a GitHub account.

Step 1 covers Python 3.15.0, so no existing Python installation is required.

Before We Start

Reliable ticket reports begin with a clear promise about which records deserve trust. This checkpoint defines how your data-quality pipeline protects operational decisions before you write any code.

Set Up Python and the Workspace

The pipeline needs a verified Python runtime before it can process ticket data reliably. Python 3.15.0 requires Windows 10 or newer.

A predictable workspace keeps the script beside its source data. In this step, you will verify Python 3.15.0 in Visual Studio Code. You will also create the empty project structure.

In this step, get ready to:

• Confirm that your Windows version supports Python 3.15.0.
• Prepare Python 3.15.0 for the pipeline.
• Create the empty data-quality-triage workspace.

Check Windows and Python

The compatibility check answers one question before installation begins. Your computer must run Windows 10 or newer for Python 3.15.0.

• Right-click Start on the Windows taskbar.
• Select Windows Settings.
• Select System.
• Select About.
• Find your edition under Windows specifications.

You should see Windows 10 or Windows 11. This confirms that your computer supports the required Python release.

Is Your Windows Version Unsupported?

Pause here if the listed edition predates Windows 10. Python 3.15.0 requires a supported Windows edition.

• Use Microsoft's device information guide to review the version details.

The next check lists every Python runtime available to the Windows launcher. The result determines whether you can continue or need to install Python 3.15.0.

• Press the Windows key to open the search bar.
• Type Visual Studio Code into the search bar.
• Press Enter to open Visual Studio Code.
• Select Terminal from the top menu bar.
• Select New Terminal.
• List the installed Python runtimes by running this command:

py list

What Does This Check Do?

The py list command displays the Python runtimes installed on Windows. You need an entry for Python 3.15 before building the pipeline.

✔️ I see Python 3.15.0

That is the main setup hurdle cleared. Your required Python runtime is available to Visual Studio Code.

ⓧ I see an older version

The displayed runtime is older than the release used in this project. Install Python 3.15.0 before creating the workspace.

• Open the official Python Windows downloads page in your browser.
• Select Python 3.15.0.
• Select Download Windows installer (64-bit).
• Run the downloaded installer.
• Select Install Now.
• Return to the Visual Studio Code integrated terminal from earlier.
• Refresh the runtime list by running this command:

py list

What Confirms the Installation?

The refreshed list should include Python 3.15. That entry confirms that Windows can find the required runtime.

ⓧ Command not found

The Python launcher is unavailable on your computer. Installing Python 3.15.0 provides the runtime needed throughout the project.

• Open the official Python Windows downloads page in your browser.
• Select Python 3.15.0.
• Select Download Windows installer (64-bit).
• Run the downloaded installer.
• Select Install Now.
• Return to the Visual Studio Code integrated terminal from earlier.
• Confirm that the launcher is available by running this command:

py list

What Confirms the Installation?

A runtime list containing Python 3.15 confirms that the launcher can find the required installation.

Python Still Missing?

• Restart Visual Studio Code so it can load the updated Python installation.
• Check that the Python installer completed before testing the runtime list again.

Ask for help with making Python available in the integrated terminal.

Why Use the Python Standard Library?

Python already includes the modules this pipeline needs for CSV processing. This keeps your setup focused on validation logic.

Later steps also use Python's built-in database support. You can practice SQL without installing a separate database application.

Create the project workspace

A Visual Studio Code workspace gives the editor plus its integrated terminal one project location. You will place the workspace on your Desktop so it remains easy to find.

Visual Studio Code may display a Workspace Trust dialog after opening the new folder. You can trust this workspace because it contains only the empty files you create.

• Switch back to Visual Studio Code.
• Select File from the top menu bar.
• Select Open Folder....
• Navigate to your Desktop folder in the dialog.
• Select New Folder.
• Enter data-quality-triage as the folder name.
• Select the new data-quality-triage folder.
• Select Select Folder.

• Select Yes, I trust the authors if the Workspace Trust dialog appears.

Your project now has a clear home. You should see data-quality-triage at the top of the Explorer view.

The workspace needs one source-data folder. Creating it first gives the ticket file a predictable location.

• Select the Explorer view in the Activity Bar.
• Select the data-quality-triage workspace folder.
• Select the New Folder control.
• Type data as the folder name.
• Press Enter.

The Explorer view should now show a data folder inside data-quality-triage. This confirms that the source-data location exists.

The Python script lives at the top level of the workspace. An empty file is the correct starting state for this setup step.

• Select the data-quality-triage workspace folder.
• Select the New File... control.
• Type myscript.py as the file name.
• Press Enter.

You should see myscript.py beside the data folder. The editor should show an empty first line.

The CSV file belongs inside the data folder. Its location becomes the input path used by the pipeline.

• Select the data folder in the Explorer view.
• Select the New File... control.
• Type tickets.csv as the file name.
• Press Enter.

The Explorer view should now show data/tickets.csv plus myscript.py. Both editors should contain an empty first line.

Verify the workspace and prepare GitHub

A signed-in GitHub session prepares your browser for the publishing step. The repository is created later after the pipeline produces its report.

• Open GitHub in your browser.
• Use your GitHub account to sign in.

Your browser should show your signed-in account view. No project repository should exist from this step.

The final check runs inside the project workspace. It proves that the integrated terminal can access the required Python runtime.

• Return to Visual Studio Code from the workspace setup.
• Select Terminal from the top menu bar.
• Select New Terminal.

Before you run the final check, which Python release do you expect the integrated terminal to list?

• Verify the installed runtime from the workspace by running this command:

py list

What Does the Final Check Prove?

The integrated terminal belongs to the open data-quality-triage workspace. A runtime list containing Python 3.15 proves that the editor can access the required installation.

You should see Python 3.15 in the displayed runtime list. That confirms your Windows Python setup is ready for the ticket pipeline.

Runtime Missing in the Workspace Terminal?

• Restart Visual Studio Code so the integrated terminal reloads the current Windows environment.
• Confirm that the installer completed successfully before repeating the runtime check.

Ask for help with refreshing the Python runtime in Visual Studio Code.

✔️ Awesome, I've got everything!

Your Explorer view should show myscript.py plus data/tickets.csv inside the data-quality-triage workspace. Both files should be empty.

ⓧ I'd like to double check the full code

The target artifacts contain no text at this checkpoint. Use the blank editor views to compare their complete contents.

• Open data/tickets.csv from the Explorer view.

The editor should show line 1 with no text. This matches the complete CSV artifact for this step.

• Open myscript.py from the Explorer view.

The editor should show line 1 with no text. This matches the complete Python artifact for this step.

Your Python runtime is verified. Your empty workspace is ready. Next, you will turn four clean ticket records into the pipeline's first visible summary.

Build the First Ticket Summary

Your Visual Studio Code workspace is ready for its first useful result. A small ticket summary gives you a baseline before reliability problems enter the picture.

Python reads the CSV into rows. The visible four-row summary creates a useful baby version of the product before you challenge its reliability.

In this step, get ready to:

• Add four clean support-ticket records to the baseline dataset.
• Build a Python script that summarizes raw priority values.
• Run the baseline summary as an operations update.

Prepare the clean baseline

Each ticket occupies one row in the CSV. The header gives every value a named field that Python can access.

• Select data/tickets.csv from the file list on the left.
• Paste the baseline dataset into the empty file using this content:

ticket_id,created_date,priority,status,category,resolution_hours
T-1001,2026-09-01,High,Resolved,Login,4
T-1002,2026-09-01,Medium,Resolved,Billing,12
T-1003,2026-09-02,Low,Open,Access,
T-1004,2026-09-03,High,Resolved,Outage,2

What does this data contain?

• The header names the fields that describe each support ticket.
• The four ticket IDs identify four distinct records.
• The empty final field on T-1003 records an open ticket without a resolution time.

• Save data/tickets.csv using the save control in Visual Studio Code.
• Confirm the editor shows one header row plus four ticket rows.

Do the values look misaligned?

Check that every row uses commas between its six fields. Keep the final comma on the T-1003 row because its resolution time is blank.

Help me check the structure of my ticket CSV.

Build the raw priority summary

The csv.DictReader reader maps each row to its header names. A counting function can then group tickets by the value stored in priority.

• Select myscript.py from the file list on the left.
• Build the complete baseline script by pasting this code into the empty file:

import csv

DATA_FILE = "data/tickets.csv"


def read_rows():
    with open(DATA_FILE, newline="", encoding="utf-8") as csv_file:
        return list(csv.DictReader(csv_file))


def raw_priority_counts(rows):
    counts = {}
    for row in rows:
        priority = (row.get("priority") or "").strip() or "(blank)"
        counts[priority] = counts.get(priority, 0) + 1
    return counts


def main():
    rows = read_rows()
    print(f"Raw rows: {len(rows)}")
    print("Raw priority values:")
    for priority, count in sorted(raw_priority_counts(rows).items()):
        print(f"- {priority}: {count}")


if __name__ == "__main__":
    main()

What does this code do?

• The csv import provides the CSV reader from Python's standard library.
• DATA_FILE points the script to data/tickets.csv.
• read_rows() returns the ticket records as dictionaries.
• raw_priority_counts() cleans each priority value. It stores the number of times each value appears.
• main() prints the raw row count. It prints the priority values in alphabetical order.

• Save myscript.py using the save control in Visual Studio Code.
• Confirm myscript.py ends with the guard that calls main().

Does the indentation look uneven?

Keep each function body indented by four spaces. Keep the lines inside each loop indented one additional level.

Help me check the indentation in my Python summary script.

Run and read the baseline

The first run turns your four clean records into an operations-style baseline. This is the point where source data becomes a repeatable summary.

Before you run it, how many rows do you expect the script to count? Which priority names should appear?

• Return to the integrated terminal from earlier.
• Run the baseline summary with this command:

python myscript.py

What does this command prove?

Python executes myscript.py from the data-quality-triage folder. A successful result proves the script can find the CSV file.

The sorted priority output also proves every baseline row reached the counting function.

You should see Raw rows: 4. The sorted list should begin with High: 2.

The next entry should be Low: 1. The final entry should be Medium: 1.

You have your first working product slice: the source file now becomes a repeatable ticket summary.

• Read the output as if you were preparing an operations update.
• Compare each displayed count with the four source records.

Does the script fail to read the CSV?

• Check that the terminal is still inside the data-quality-triage folder.
• Check that the CSV path is exactly data/tickets.csv.
• Check that both files were saved before running the script.

Help me debug why my Python script cannot summarize the ticket CSV.

✔️ Awesome, I've got everything!

Your baseline is complete. The CSV and script now form a working four-ticket summary.

ⓧ I'd like to double check the full code

• Compare data/tickets.csv with this full reference:

ticket_id,created_date,priority,status,category,resolution_hours
T-1001,2026-09-01,High,Resolved,Login,4
T-1002,2026-09-01,Medium,Resolved,Billing,12
T-1003,2026-09-02,Low,Open,Access,
T-1004,2026-09-03,High,Resolved,Outage,2

What should match in the CSV?

The file should contain one header row followed by the four baseline tickets. The row order should match this reference.

• Compare myscript.py with this full reference:

import csv

DATA_FILE = "data/tickets.csv"


def read_rows():
    with open(DATA_FILE, newline="", encoding="utf-8") as csv_file:
        return list(csv.DictReader(csv_file))


def raw_priority_counts(rows):
    counts = {}
    for row in rows:
        priority = (row.get("priority") or "").strip() or "(blank)"
        counts[priority] = counts.get(priority, 0) + 1
    return counts


def main():
    rows = read_rows()
    print(f"Raw rows: {len(rows)}")
    print("Raw priority values:")
    for priority, count in sorted(raw_priority_counts(rows).items()):
        print(f"- {priority}: {count}")


if __name__ == "__main__":
    main()

What should match in the script?

The script should define DATA_FILE plus the three functions shown here. The final guard should call main().

Your clean baseline tells a convincing story. Next, you will test whether that story survives a much messier source file.

Expose the Bad Data

Your first summary gave the Python script a useful baseline. Four clean CSV records produced tidy counts that looked ready for an operations update.

A larger source batch now tests whether that confidence is deserved. You will keep the same script untouched while the evidence answers that question.

In this step, get ready to:

• Append six source records to data/tickets.csv.
• Run the unchanged summary against all ten records.
• Identify why the displayed totals cannot support an operations decision.

Append the next source batch

The original sample covered only clean records. The next six records challenge assumptions that the first four never tested.

• In Visual Studio Code, select data/tickets.csv from the file list.
• Place your cursor after the record that begins with T-1004.
• Append the six incoming records by pasting this data:

T-1002,2026-09-01,Medium,Resolved,Billing,12
,2026-09-04,Medium,Resolved,Login,6
T-1006,2026-13-05,Low,Resolved,Billing,8
T-1007,2026-09-06,Urgent,Resolved,Outage,1
T-1008,2026-09-07,High,Resolved,Login,-3
T-1009,2026-09-08,Medium,Open,Billing,

What Changed in the Dataset?

• These six lines extend the same source file that read_rows() already reads.
• The existing summary receives every added row without any changes to myscript.py.

• Save data/tickets.csv.
• Check the final record in data/tickets.csv.

You should see the final record begin with T-1009.

Do You Still See Only Four Records?

Place all six new lines directly beneath the T-1004 record. Save data/tickets.csv again.

Check that you edited the file inside the data folder. A different copy will not affect the script.

Help me check why my Python script still reads only four CSV records.

Run the unchanged summary

The script still performs the same two operations. It counts source rows before grouping their priority text.

Before you run the script, predict whether those two totals can expose a source record that breaks an assumption.

• Test the unchanged summary by running this command:

python myscript.py

What Does the Result Reveal?

• You found the weak spot: the terminal reports Raw rows: 10 because every source record was counted.
• The priority summary includes Urgent: 1 beside the intended priority labels.
• The unchanged script treats every priority value as a category because it has no acceptance rules.

Does the Terminal Still Report Four Rows?

Save data/tickets.csv before running the command again. Confirm the terminal remains inside the data-quality-triage folder.

Check that DATA_FILE still points to data/tickets.csv.

Help me debug why the unchanged summary does not report ten rows.

Audit the misleading result

A schema defines the values that a trustworthy ticket can use. Comparing the source with those rules turns a suspicious total into concrete evidence.

• Compare the displayed priority labels with the intended set: Low, Medium, High.
• Identify Urgent as an unsupported priority.
• Locate the second T-1002 record in data/tickets.csv.
• Locate the row with a blank ticket_id.
• Locate 2026-13-05 in the created_date column.
• Locate -3 in the resolution_hours column.

Why Is Raw Row Count a Weak Quality Metric?

The Raw rows: 10 result describes file volume. It gives each source record equal weight despite the visible violations.

An operations update needs metrics derived from records that passed explicit rules. This summary has no quality gate.

✔️ Awesome, I've got everything!

Your dataset now contains ten source records. Your Python script remains unchanged from the four-row summary.

ⓧ I'd like to double check the full code

• Compare data/tickets.csv with this complete reference:

ticket_id,created_date,priority,status,category,resolution_hours
T-1001,2026-09-01,High,Resolved,Login,4
T-1002,2026-09-01,Medium,Resolved,Billing,12
T-1003,2026-09-02,Low,Open,Access,
T-1004,2026-09-03,High,Resolved,Outage,2
T-1002,2026-09-01,Medium,Resolved,Billing,12
,2026-09-04,Medium,Resolved,Login,6
T-1006,2026-13-05,Low,Resolved,Billing,8
T-1007,2026-09-06,Urgent,Resolved,Outage,1
T-1008,2026-09-07,High,Resolved,Login,-3
T-1009,2026-09-08,Medium,Open,Billing,

• Confirm the unchanged myscript.py matches this complete reference:

import csv

DATA_FILE = "data/tickets.csv"


def read_rows():
    with open(DATA_FILE, newline="", encoding="utf-8") as csv_file:
        return list(csv.DictReader(csv_file))


def raw_priority_counts(rows):
    counts = {}
    for row in rows:
        priority = (row.get("priority") or "").strip() or "(blank)"
        counts[priority] = counts.get(priority, 0) + 1
    return counts


def main():
    rows = read_rows()
    print(f"Raw rows: {len(rows)}")
    print("Raw priority values:")
    for priority, count in sorted(raw_priority_counts(rows).items()):
        print(f"- {priority}: {count}")


if __name__ == "__main__":
    main()

• Return to the unchanged terminal output.
• Confirm the first line reads Raw rows: 10.
• Confirm the priority list contains Urgent: 1.

That shortfall is intentional. You now have evidence that a confident-looking summary can preserve bad source assumptions.

The raw summary has exposed its blind spot. Next, you will separate trustworthy records from rejected records before calculating operational metrics.

Validate Records and Query Clean Data

Your raw summary exposed the danger in trusting every source row. It counted duplicate records and malformed values as evidence.

Now you will add defensive validation to separate reliable records from rejected ones. You will load the reliable records into SQLite so every operational metric comes from clean data.

In this step, get ready to:

• Validate identifiers, dates, categories, statuses, priorities, and resolution hours.
• Load accepted records into an in-memory SQLite database.
• Calculate clean SQL metrics and generate the audit report.

Validate every source row

A validation rule turns an expected data shape into an explicit test. Each rejected row keeps its CSV line number plus a specific reason, which preserves evidence about the source problem.

• In `myscript.py`, replace the imports and constants at the top of the file with this code:

import csv
import datetime as dt
import sqlite3

DATA_FILE = "data/tickets.csv"
REPORT_FILE = "report.md"
VALID_PRIORITIES = {"Low", "Medium", "High"}
VALID_STATUSES = {"Open", "Resolved"}

What do these additions provide?

• `datetime` checks whether each date represents a real calendar date.
• `sqlite3` provides the in-memory database used for clean records.
• `VALID_PRIORITIES` and `VALID_STATUSES` define the fictional business rules for accepted categories.
• `REPORT_FILE` gives the generated Markdown report a consistent destination.

• Add `validate_rows` below `raw_priority_counts` by pasting the next three code sections in order.
• Start the function with its row collections and normalized field values:

def validate_rows(rows):
    cleaned_rows = []
    rejected_rows = []
    seen_ids = set()

    for line_number, row in enumerate(rows, start=2):
        row_errors = []
        ticket_id = (row.get("ticket_id") or "").strip()
        created_date = (row.get("created_date") or "").strip()
        priority = (row.get("priority") or "").strip()
        status = (row.get("status") or "").strip()
        category = (row.get("category") or "").strip()
        resolution_text = (row.get("resolution_hours") or "").strip()

How does validation begin?

• `cleaned_rows` holds tuples that pass every rule.
• `rejected_rows` holds line numbers with rejection reasons.
• `seen_ids` tracks identifiers that have already appeared.
• Each field is stripped before validation so surrounding spaces cannot hide a missing value.

• Continue inside the `for` loop with the identifier, date, enum, and category rules:

        if not ticket_id:
            row_errors.append("missing ticket_id")
        elif ticket_id in seen_ids:
            row_errors.append(f"duplicate ticket_id: {ticket_id}")
        else:
            seen_ids.add(ticket_id)

        try:
            dt.date.fromisoformat(created_date)
        except ValueError:
            row_errors.append(f"invalid created_date: {created_date or '(blank)'}")

        if priority not in VALID_PRIORITIES:
            row_errors.append(f"invalid priority: {priority or '(blank)'}")

        if status not in VALID_STATUSES:
            row_errors.append(f"invalid status: {status or '(blank)'}")

        if not category:
            row_errors.append("missing category")

Which source problems are caught?

• The identifier rules reject blank values plus repeated ticket IDs.
• `dt.date.fromisoformat()` rejects impossible dates such as `2026-13-05`.
• The enum checks reject values outside the allowed priority and status sets.
• The category rule prevents an uncategorized ticket from reaching analysis.

• Finish `validate_rows` with the resolution-hour rules and the accepted-or-rejected split:

        resolution_hours = None
        if resolution_text:
            try:
                resolution_hours = float(resolution_text)
                if resolution_hours < 0:
                    row_errors.append("resolution_hours must be zero or greater")
            except ValueError:
                row_errors.append(f"invalid resolution_hours: {resolution_text}")
        elif status == "Resolved":
            row_errors.append("resolved ticket is missing resolution_hours")

        if row_errors:
            rejected_rows.append((line_number, "; ".join(row_errors)))
        else:
            cleaned_rows.append(
                (
                    ticket_id,
                    created_date,
                    priority,
                    status,
                    category,
                    resolution_hours,
                )
            )

    return cleaned_rows, rejected_rows

How are the final results separated?

• Resolution hours become a number when a value is present.
• Negative numbers are rejected because they represent an impossible duration.
• Resolved tickets require a duration.
• Rows with no errors become database-ready tuples.

• In `main`, add this code immediately after the raw priority loop:

    cleaned_rows, rejected_rows = validate_rows(rows)
    print("\nValidation issues:")
    for line_number, message in rejected_rows:
        print(f"- line {line_number}: {message}")

    print(f"\nAccepted rows: {len(cleaned_rows)}")
    print(f"Rejected rows: {len(rejected_rows)}")

What does this output prove?

The script now exposes each rejected CSV line instead of silently mixing it into the summary. The accepted and rejected totals make the quality split measurable.

• Save `myscript.py`.
• Predict which five source rows will fail the new rules.
• Run the validation flow with this command:

python myscript.py

What should you see?

You should see line-specific reasons for the duplicate ID, blank ID, invalid date, unsupported priority, and negative duration. The final validation totals should show `Accepted rows: 5` plus `Rejected rows: 5`.

Validation output missing?

Check that the new call to `validate_rows(rows)` sits inside `main`. Confirm that every validation line uses the same indentation as the surrounding block.

Compare `data/tickets.csv` with the ten-row dataset from the previous step. A missing source row changes the totals.

Help me debug my Python validation function and explain why my accepted or rejected row count differs from five.

Load accepted rows and calculate SQL metrics

An in-memory database gives the accepted records a clean boundary. Only validated tuples enter the `tickets` table, so every SQL query starts from trustworthy data.

Why use an in-memory database?

This project needs relational loading and aggregation without a persistent database file. The database exists for one script run, which keeps the focus on validation and analysis.

Parameterized placeholders bind each Python value to the insert statement. This keeps data separate from the SQL text.

• Add `load_database` between `validate_rows` and `main`:

def load_database(cleaned_rows):
    connection = sqlite3.connect(":memory:")
    connection.execute(
        """
        CREATE TABLE tickets (
            ticket_id TEXT PRIMARY KEY,
            created_date TEXT NOT NULL,
            priority TEXT NOT NULL,
            status TEXT NOT NULL,
            category TEXT NOT NULL,
            resolution_hours REAL
        )
        """
    )
    connection.executemany(
        "INSERT INTO tickets VALUES (?, ?, ?, ?, ?, ?)",
        cleaned_rows,
    )
    connection.commit()
    return connection

What does the database loader do?

• `sqlite3.connect(":memory:")` creates a temporary SQLite database for the current run.
• The `tickets` table mirrors the validated tuple structure.
• The six `?` placeholders bind values from each accepted tuple.
• `commit()` completes the insert before analysis begins.

• Add `analyze` below `load_database` by pasting the next two sections in order.
• Start the function with the total, priority, and status queries:

def analyze(connection):
    total = connection.execute("SELECT COUNT(*) FROM tickets").fetchone()[0]
    priority_counts = connection.execute(
        """
        SELECT priority, COUNT(*) AS total
        FROM tickets
        GROUP BY priority
        ORDER BY total DESC, priority
        """
    ).fetchall()
    status_counts = connection.execute(
        """
        SELECT status, COUNT(*) AS total
        FROM tickets
        GROUP BY status
        ORDER BY total DESC, status
        """
    ).fetchall()

How do these queries summarize tickets?

`COUNT(*)` calculates the clean row total. The grouped queries produce one count for each priority or status.

`ORDER BY` places larger groups first. The category name breaks ties consistently.

• Finish `analyze` with the category query, resolution average, and result dictionary:

    category_counts = connection.execute(
        """
        SELECT category, COUNT(*) AS total
        FROM tickets
        GROUP BY category
        ORDER BY total DESC, category
        """
    ).fetchall()
    average_resolution = connection.execute(
        """
        SELECT AVG(resolution_hours)
        FROM tickets
        WHERE status = 'Resolved'
        """
    ).fetchone()[0]

    return {
        "total": total,
        "priority_counts": priority_counts,
        "status_counts": status_counts,
        "category_counts": category_counts,
        "average_resolution": average_resolution,
    }

Why calculate the average after validation?

The average includes clean resolved tickets only. The invalid negative duration never reaches the database, so it cannot pull the metric downward.

The returned dictionary keeps all query results together for terminal output and report generation.

• In `main`, add this code after the rejected-row totals:

    connection = load_database(cleaned_rows)
    analysis = analyze(connection)
    analysis["raw_count"] = len(rows)
    analysis["clean_count"] = len(cleaned_rows)
    analysis["rejected_count"] = len(rejected_rows)

    print("\nSQL metrics:")
    print(f"- Clean rows: {analysis['total']}")
    for priority, count in analysis["priority_counts"]:
        print(f"- {priority} priority: {count}")

    average_resolution = analysis["average_resolution"]
    average_text = (
        f"{average_resolution:.1f}"
        if average_resolution is not None
        else "Not available"
    )
    print(f"- Average resolution hours: {average_text}")

    connection.close()

How does the clean analysis flow work?

`main` loads only `cleaned_rows` before calling `analyze`. It also adds the raw and rejected totals needed by the report.

The connection closes after the metrics have been printed. This releases the in-memory database at the end of the run.

• Save `myscript.py`.
• Predict whether `Urgent` will appear among the clean priority metrics.
• Run the clean analysis with this command:

python myscript.py

What should the clean metrics show?

You should see `Clean rows: 5`. The priority section should contain High, Medium, and Low only.

You should also see `Average resolution hours: 6.0`. The accepted resolved durations are `4`, `12`, and `2` hours.

Seeing a database or SQL error?

Confirm that each cleaned tuple contains six values in the same order as the `tickets` table columns. Check that the insert statement contains exactly six placeholders.

Inspect the triple quotes around each multi-line query. A missing quote can make Python interpret SQL as application code.

Help me debug my SQLite load or analysis query.

Generate the audit report

Terminal output proves the clean analysis works. A Markdown report turns the same results into a reviewable audit artifact.

• Add `write_report` below `analyze` by pasting the next three sections in order.
• Start the function with the score calculation and report heading lines:

def write_report(analysis):
    raw_count = analysis["raw_count"]
    clean_count = analysis["clean_count"]
    rejected_count = analysis["rejected_count"]
    quality_score = clean_count / raw_count * 100 if raw_count else 0
    top_category, top_category_count = (
        analysis["category_counts"][0]
        if analysis["category_counts"]
        else ("None", 0)
    )
    average_resolution = analysis["average_resolution"]
    average_text = (
        f"{average_resolution:.1f} hours"
        if average_resolution is not None
        else "Not available"
    )

    lines = [
        "# Support Ticket Data Quality Report",
        "",
        "## Data quality",
        f"- Raw rows: {raw_count}",
        f"- Valid rows: {clean_count}",
        f"- Rejected rows: {rejected_count}",
        f"- Data quality score: {quality_score:.1f}%",
        "",
        "## Tickets by priority",
    ]

How is the quality score calculated?

The score divides valid rows by raw rows before converting the result to a percentage. Five valid records from ten source records produce `50.0%`.

The first report lines preserve the row totals beside that score. A reviewer can see the scale behind the percentage.

• Continue `write_report` with the grouped metric sections:

    for priority, count in analysis["priority_counts"]:
        lines.append(f"- {priority}: {count}")

    lines.extend(["", "## Tickets by status"])
    for status, count in analysis["status_counts"]:
        lines.append(f"- {status}: {count}")

    lines.extend(["", "## Tickets by category"])
    for category, count in analysis["category_counts"]:
        lines.append(f"- {category}: {count}")

How do query results become report sections?

Each query result is already a list of label-and-count pairs. The loops convert those pairs into Markdown bullet lines.

• Finish `write_report` with the resolution metric, recommendation, and file output:

    lines.extend(
        [
            "",
            "## Resolution performance",
            f"- Average resolution time for resolved tickets: {average_text}",
            "",
            "## Recommended action",
            (
                f"Investigate the {top_category} queue first "
                f"({top_category_count} valid tickets), then fix the "
                f"{rejected_count} rejected source rows before using this "
                "dataset for operational decisions."
            ),
        ]
    )

    with open(REPORT_FILE, "w", encoding="utf-8") as report_file:
        report_file.write("\n".join(lines) + "\n")

How is the recommendation chosen?

The first category returned by the ordered category query becomes the priority queue. Billing leads with two valid tickets, so the report recommends investigating it first.

The final file write joins every report line with a newline. The result renders as structured Markdown.

• In `main`, add these lines immediately before `connection.close()`:

    write_report(analysis)
    print(f"\nReport written to {REPORT_FILE}")

What completes the pipeline?

The same analysis dictionary now powers both terminal output and `report.md`. This keeps the two views consistent.

• Save `myscript.py`.
• Predict which raw values will disappear from the clean metrics.
• Run the completed pipeline with this command:

python myscript.py

What should the completed run show?

You should see five line-specific validation issues followed by `Accepted rows: 5` and `Rejected rows: 5`. The SQL section should show `Clean rows: 5` plus `Average resolution hours: 6.0`.

The final line should say `Report written to report.md`. That confirms the audit artifact was generated from the clean analysis.

• Select `report.md` from the VS Code file list.
• Confirm that the report shows a `50.0%` data-quality score.
• Confirm that the report shows five valid rows.
• Confirm that the report shows five rejected rows.
• Confirm that the recommendation names Billing as the first queue to investigate.

Report missing or out of date?

Confirm that `write_report(analysis)` appears before `connection.close()` in `main`. Save `myscript.py` before running the command again.

Check that the terminal remains in the `data-quality-triage` folder. The script writes `report.md` into the folder where the command runs.

Help me debug why report.md is missing or does not show the expected clean metrics.

✔️ Awesome, I've got everything!

Great work. Save `myscript.py` and `report.md`. Your pipeline now rejects unreliable rows before they can influence the SQL metrics.

ⓧ I'd like to double check the full code

Compare your complete `myscript.py` with this reference. Every function and constant should appear in the same order.

import csv
import datetime as dt
import sqlite3

DATA_FILE = "data/tickets.csv"
REPORT_FILE = "report.md"
VALID_PRIORITIES = {"Low", "Medium", "High"}
VALID_STATUSES = {"Open", "Resolved"}


def read_rows():
    with open(DATA_FILE, newline="", encoding="utf-8") as csv_file:
        return list(csv.DictReader(csv_file))


def raw_priority_counts(rows):
    counts = {}
    for row in rows:
        priority = (row.get("priority") or "").strip() or "(blank)"
        counts[priority] = counts.get(priority, 0) + 1
    return counts


def validate_rows(rows):
    cleaned_rows = []
    rejected_rows = []
    seen_ids = set()

    for line_number, row in enumerate(rows, start=2):
        row_errors = []
        ticket_id = (row.get("ticket_id") or "").strip()
        created_date = (row.get("created_date") or "").strip()
        priority = (row.get("priority") or "").strip()
        status = (row.get("status") or "").strip()
        category = (row.get("category") or "").strip()
        resolution_text = (row.get("resolution_hours") or "").strip()

        if not ticket_id:
            row_errors.append("missing ticket_id")
        elif ticket_id in seen_ids:
            row_errors.append(f"duplicate ticket_id: {ticket_id}")
        else:
            seen_ids.add(ticket_id)

        try:
            dt.date.fromisoformat(created_date)
        except ValueError:
            row_errors.append(f"invalid created_date: {created_date or '(blank)'}")

        if priority not in VALID_PRIORITIES:
            row_errors.append(f"invalid priority: {priority or '(blank)'}")

        if status not in VALID_STATUSES:
            row_errors.append(f"invalid status: {status or '(blank)'}")

        if not category:
            row_errors.append("missing category")

        resolution_hours = None
        if resolution_text:
            try:
                resolution_hours = float(resolution_text)
                if resolution_hours < 0:
                    row_errors.append("resolution_hours must be zero or greater")
            except ValueError:
                row_errors.append(f"invalid resolution_hours: {resolution_text}")
        elif status == "Resolved":
            row_errors.append("resolved ticket is missing resolution_hours")

        if row_errors:
            rejected_rows.append((line_number, "; ".join(row_errors)))
        else:
            cleaned_rows.append(
                (
                    ticket_id,
                    created_date,
                    priority,
                    status,
                    category,
                    resolution_hours,
                )
            )

    return cleaned_rows, rejected_rows


def load_database(cleaned_rows):
    connection = sqlite3.connect(":memory:")
    connection.execute(
        """
        CREATE TABLE tickets (
            ticket_id TEXT PRIMARY KEY,
            created_date TEXT NOT NULL,
            priority TEXT NOT NULL,
            status TEXT NOT NULL,
            category TEXT NOT NULL,
            resolution_hours REAL
        )
        """
    )
    connection.executemany(
        "INSERT INTO tickets VALUES (?, ?, ?, ?, ?, ?)",
        cleaned_rows,
    )
    connection.commit()
    return connection


def analyze(connection):
    total = connection.execute("SELECT COUNT(*) FROM tickets").fetchone()[0]
    priority_counts = connection.execute(
        """
        SELECT priority, COUNT(*) AS total
        FROM tickets
        GROUP BY priority
        ORDER BY total DESC, priority
        """
    ).fetchall()
    status_counts = connection.execute(
        """
        SELECT status, COUNT(*) AS total
        FROM tickets
        GROUP BY status
        ORDER BY total DESC, status
        """
    ).fetchall()
    category_counts = connection.execute(
        """
        SELECT category, COUNT(*) AS total
        FROM tickets
        GROUP BY category
        ORDER BY total DESC, category
        """
    ).fetchall()
    average_resolution = connection.execute(
        """
        SELECT AVG(resolution_hours)
        FROM tickets
        WHERE status = 'Resolved'
        """
    ).fetchone()[0]

    return {
        "total": total,
        "priority_counts": priority_counts,
        "status_counts": status_counts,
        "category_counts": category_counts,
        "average_resolution": average_resolution,
    }


def write_report(analysis):
    raw_count = analysis["raw_count"]
    clean_count = analysis["clean_count"]
    rejected_count = analysis["rejected_count"]
    quality_score = clean_count / raw_count * 100 if raw_count else 0
    top_category, top_category_count = (
        analysis["category_counts"][0]
        if analysis["category_counts"]
        else ("None", 0)
    )
    average_resolution = analysis["average_resolution"]
    average_text = (
        f"{average_resolution:.1f} hours"
        if average_resolution is not None
        else "Not available"
    )

    lines = [
        "# Support Ticket Data Quality Report",
        "",
        "## Data quality",
        f"- Raw rows: {raw_count}",
        f"- Valid rows: {clean_count}",
        f"- Rejected rows: {rejected_count}",
        f"- Data quality score: {quality_score:.1f}%",
        "",
        "## Tickets by priority",
    ]

    for priority, count in analysis["priority_counts"]:
        lines.append(f"- {priority}: {count}")

    lines.extend(["", "## Tickets by status"])
    for status, count in analysis["status_counts"]:
        lines.append(f"- {status}: {count}")

    lines.extend(["", "## Tickets by category"])
    for category, count in analysis["category_counts"]:
        lines.append(f"- {category}: {count}")

    lines.extend(
        [
            "",
            "## Resolution performance",
            f"- Average resolution time for resolved tickets: {average_text}",
            "",
            "## Recommended action",
            (
                f"Investigate the {top_category} queue first "
                f"({top_category_count} valid tickets), then fix the "
                f"{rejected_count} rejected source rows before using this "
                "dataset for operational decisions."
            ),
        ]
    )

    with open(REPORT_FILE, "w", encoding="utf-8") as report_file:
        report_file.write("\n".join(lines) + "\n")


def main():
    rows = read_rows()
    print(f"Raw rows: {len(rows)}")
    print("Raw priority values:")
    for priority, count in sorted(raw_priority_counts(rows).items()):
        print(f"- {priority}: {count}")

    cleaned_rows, rejected_rows = validate_rows(rows)
    print("\nValidation issues:")
    for line_number, message in rejected_rows:
        print(f"- line {line_number}: {message}")

    print(f"\nAccepted rows: {len(cleaned_rows)}")
    print(f"Rejected rows: {len(rejected_rows)}")

    connection = load_database(cleaned_rows)
    analysis = analyze(connection)
    analysis["raw_count"] = len(rows)
    analysis["clean_count"] = len(cleaned_rows)
    analysis["rejected_count"] = len(rejected_rows)

    print("\nSQL metrics:")
    print(f"- Clean rows: {analysis['total']}")
    for priority, count in analysis["priority_counts"]:
        print(f"- {priority} priority: {count}")

    average_resolution = analysis["average_resolution"]
    average_text = (
        f"{average_resolution:.1f}"
        if average_resolution is not None
        else "Not available"
    )
    print(f"- Average resolution hours: {average_text}")

    write_report(analysis)
    print(f"\nReport written to {REPORT_FILE}")
    connection.close()


if __name__ == "__main__":
    main()

Your pipeline now protects every metric with explicit validation and clean-only SQL analysis. Next, you will document the workflow and publish the finished evidence to GitHub.

Generate and Publish the Audit Report

Your validator now separates five trustworthy tickets from five rejected records. Its clean SQL metrics protect the analysis from source errors.

Terminal output proves the pipeline works. A rendered Markdown report makes the result reviewable after the terminal closes. You will document the workflow before publishing the finished project on GitHub.

In this step, get ready to:

• Confirm the generated audit report.
• Create a README that documents the workflow.
• Publish the project files in a public GitHub repository.

Review the generated audit report

Your existing write_report() function turns the clean analysis into a stakeholder-facing report. Running the completed pipeline refreshes report.md from the current dataset.

Before you run this, consider whether the clean-only metrics should stay unchanged.

• Regenerate the audit report from the VS Code terminal by running this command:

python myscript.py

What Does This Run Prove?

• The pipeline validates all ten source rows before calculating its metrics.
• The in-memory SQLite database receives only the five accepted tuples.
• The final call to write_report() replaces report.md with results from the latest run.

You should see five accepted rows plus five rejected rows. The final terminal line should confirm that the report was written to report.md.

• Select report.md from the file list in Visual Studio Code.
• Press Ctrl+Shift+V to open the Markdown preview.

You will see a 50.0% data-quality score. The report lists five valid rows plus five rejected rows.

The recommended action starts with the Billing queue. This gives the stakeholder a concrete place to investigate first.

Report Missing or Outdated?

Confirm that the terminal is still inside the data-quality-triage folder. Check that the final terminal line names report.md.

If the preview shows old values, close the preview before reopening it. Help me troubleshoot a missing or outdated Markdown report.

Create the project README

A README explains the problem before a reviewer reads the implementation. Visual Studio Code can preview the document while you build it.

• Select New File... from the Explorer view.
• Enter README.md as the file name.
• Press Enter to create the file.

You will see an empty README.md editor inside the data-quality-triage workspace.

• Add the project purpose and demonstrated skills by pasting this content into README.md:

# Support Ticket Data Quality Triage

A dependency-free Python project that validates messy support-ticket data, loads clean records into an in-memory SQLite database, calculates operational metrics, and generates a Markdown audit report.

## Why this project exists

Raw row counts can look convincing even when source data contains duplicates, invalid dates, unsupported values, missing identifiers, or impossible measurements. This project demonstrates a safer workflow: profile, validate, isolate, query, and report.

## What it demonstrates

- CSV ingestion with Python
- Explicit data-quality rules
- Duplicate and missing-value detection
- ISO date and numeric validation
- Parameterized SQLite inserts
- SQL aggregation with `COUNT`, `AVG`, `GROUP BY`, and `ORDER BY`
- Stakeholder-friendly Markdown reporting

What Does This Section Explain?

• The opening sentence summarizes the finished pipeline in one place.
• The problem statement explains why raw counts can mislead a reviewer.
• The skills list connects validation work to Python plus SQL.

• Save README.md.
• Press Ctrl+K followed by V to open the preview beside the source.

You will see the project title above the problem statement. A bulleted skills section will appear below it.

README Preview Is Blank?

Confirm that the file is named README.md with the .md extension. Save the file before checking the preview again.

Help me fix a blank Markdown preview in Visual Studio Code.

• Append the validation rules and run instructions by pasting this content at the end of README.md:

## Validation rules

- `ticket_id` is required and unique.
- `created_date` must be a valid ISO date.
- `priority` must be `Low`, `Medium`, or `High`.
- `status` must be `Open` or `Resolved`.
- `category` is required.
- Resolution hours must be numeric and non-negative when present.
- Resolved tickets must include resolution hours.

## Run

From the project folder in the Visual Studio Code terminal:

```powershell
python myscript.py
```

Why Document These Rules?

• The validation list makes the fictional business rules explicit.
• The run section gives a reviewer one command for reproducing the report.
• The fenced command renders separately from the surrounding explanation.

• Save README.md to update the side preview.

The preview will now show seven validation rules. You will also see the run command in a separate code panel.

Run Command Spilling Into the Page?

Check that the line after the command contains three closing backticks. A missing closing fence causes every later section to render as code.

Help me repair the fenced command in my README.

• Complete the README by appending the expected result and portfolio talking points:

## Expected result

The included dataset contains 10 raw rows. The pipeline accepts 5, rejects 5 with specific reasons, calculates an average resolution time of 6.0 hours from clean resolved tickets, and writes `report.md`.

## Portfolio talking points

- Why validation must happen before aggregation
- Why rejected rows are retained as evidence instead of silently dropped
- Why parameterized database inserts are safer than string-built SQL
- How adversarial records test whether validation rules generalize

What Does the Ending Add?

• The expected result gives reviewers concrete values for checking a run.
• The talking points turn implementation choices into interview-ready explanations.
• The final item connects this project to reusable adversarial testing.

• Save README.md to refresh the preview.

You will see the expected ten-row result near the bottom. Four portfolio talking points will close the document.

Final Sections Missing?

Confirm that the new content sits below the closing code fence. Search the source for ## Expected result if the section is hard to locate.

Help me find missing sections in my README preview.

✔️ Awesome, I've got everything!

Strong work. Your README now explains the data-quality problem plus the evidence produced by the pipeline.

ⓧ I'd like to double check the full code

Compare your complete README.md with this reference:

# Support Ticket Data Quality Triage

A dependency-free Python project that validates messy support-ticket data, loads clean records into an in-memory SQLite database, calculates operational metrics, and generates a Markdown audit report.

## Why this project exists

Raw row counts can look convincing even when source data contains duplicates, invalid dates, unsupported values, missing identifiers, or impossible measurements. This project demonstrates a safer workflow: profile, validate, isolate, query, and report.

## What it demonstrates

- CSV ingestion with Python
- Explicit data-quality rules
- Duplicate and missing-value detection
- ISO date and numeric validation
- Parameterized SQLite inserts
- SQL aggregation with `COUNT`, `AVG`, `GROUP BY`, and `ORDER BY`
- Stakeholder-friendly Markdown reporting

## Validation rules

- `ticket_id` is required and unique.
- `created_date` must be a valid ISO date.
- `priority` must be `Low`, `Medium`, or `High`.
- `status` must be `Open` or `Resolved`.
- `category` is required.
- Resolution hours must be numeric and non-negative when present.
- Resolved tickets must include resolution hours.

## Run

From the project folder in the Visual Studio Code terminal:

```powershell
python myscript.py
```

## Expected result

The included dataset contains 10 raw rows. The pipeline accepts 5, rejects 5 with specific reasons, calculates an average resolution time of 6.0 hours from clean resolved tickets, and writes `report.md`.

## Portfolio talking points

- Why validation must happen before aggregation
- Why rejected rows are retained as evidence instead of silently dropped
- Why parameterized database inserts are safer than string-built SQL
- How adversarial records test whether validation rules generalize

Publish the repository to GitHub

A public repository keeps the source data beside the code that evaluates it. The rendered report gives reviewers a result they can inspect without running the pipeline.

• Return to the GitHub browser session from earlier.
• Select New repository from your account dashboard.
• Enter data-quality-triage in the Repository name field.
• Select Public as the repository visibility.
• Select Create repository.

That is the repository created. Its name and public visibility now give the project a shareable home.

The browser uploader accepts files plus folders. You can preserve the data/tickets.csv path by uploading the entire data folder.

• Select Add file above the repository file list.
• Select Upload files from the dropdown.
• Switch back to the README.md tab in Visual Studio Code.
• Press Ctrl+K followed by R to reveal the file in File Explorer.
• Hold Ctrl while selecting myscript.py, README.md, report.md, and the data folder.
• Drag the selected files plus folder into the GitHub upload area.
• Enter Add data quality pipeline and audit report in the Commit message field.
• Select Propose changes to finish the upload.

You will return to the repository file list. It should contain myscript.py, README.md, report.md, and the data folder.

Upload Missing a File or Folder?

Confirm that all four selected items finish uploading before you submit the change. If data is missing, drag that folder into the upload area separately.

Help me troubleshoot an incomplete GitHub browser upload.

Before you open the report, predict which category should lead its recommendation.

• Select report.md from the GitHub repository file list.

GitHub will render the report with a 50.0% data-quality score. You will see five valid rows plus five rejected rows.

The final recommendation tells the reader to investigate Billing first. You now have a published audit trail from raw CSV through stakeholder action.

Secret mission

Prove the Validator Survives New Bad Data

Feed three unseen data-quality failures into the current dataset. Prove the unchanged validator rejects every new violation and lowers the reported quality score to 38.5%.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your local files and public GitHub repository, pause your work, or delete both copies. This project has no running services or ongoing costs.

Resources you used:

• Local data-quality-triage folder containing the source data, pipeline, documentation, and generated report.
• Public data-quality-triage GitHub repository containing the published project.

Why Does the Database Need No Cleanup?

The SQLite tickets database exists only during a script run. connection.close() releases it before myscript.py exits.

Keep everything running

No action needed. Choose this if you want to keep improving the validator or show the published project to other people.

• Keep the local data-quality-triage folder with its source files and generated report.
• Keep the public data-quality-triage repository available on GitHub.
• Return to the project whenever you want to test more adversarial records.

Pause - I'll come back to this later

Pause your work without removing any files. No process continues running after the script exits.

• Save any open files in the data-quality-triage workspace.
• Close Visual Studio Code.
• Leave the public data-quality-triage repository unchanged.

Delete - I don't want to use this again

Deletion is the only irreversible option here. These steps remove the project copies while leaving your installed development tools available.

• In GitHub, open the repository settings for the public data-quality-triage repository.
• Use the permanent repository deletion control to delete data-quality-triage.
• Return to your repositories list to confirm that data-quality-triage no longer appears.
• Close Visual Studio Code.
• In File Explorer, navigate to the parent folder that contains data-quality-triage.
• Permanently delete the data-quality-triage folder using File Explorer.
• Confirm in File Explorer that the data-quality-triage folder no longer appears in its previous location.

Nice Work!

Nice Work!

You made it! Your published support-ticket pipeline now blocks unreliable CSV records from distorting its SQL metrics.

You've learned how to:

• Experience a designed bad-data failure that proves raw row counts can create an unreliable operational story.
• Use Python to enforce defensive validation across every source field. Load only five accepted records into an in-memory SQLite database for trustworthy analysis.
• Generate a Markdown audit report with a data-quality score. Include a recommended action based only on clean metrics. Publish the complete project in a public GitHub repository.
• Secret Mission: Add three adversarial records without changing myscript.py. Prove the existing validator rejects all eight invalid rows. Confirm report.md recalculates the quality score to 38.5%.

Ready to quiz yourself?