Capstone: From Messy Export to Trusted Report
Clean, load, check and report on a messy export with a pipeline of rules, constraints and reconciliation, then score it honestly against a launch checklist.
In this lesson you will
- Build a cleaning and loading pipeline
- Reconcile counts and totals
- Produce a ranked report
- Judge readiness honestly
About 32 minutes
Friday afternoon, one more export
Maya’s cousin Dev gets the last piece of the charity project: nine rows of orders from a partner, in a text file, to be loaded and reported on by Monday. The file has duplicates, a quantity that says ‘x’, a date written the other way round, a product nobody sells and a negative price. The client wants a report and a promise that the numbers are right. This capstone puts the whole Advanced level to work: cleaning, constraints, reconciliation, a ranking query and an honest launch check. It is short on purpose, so that you can see every step.
The pipeline
- Parse the text file into rows.
- Validate each row against rules, in order: duplicate reference, quantity, date, known product, price.
- Set aside every failing row in a rejects table with its reason, and never guess a fix.
- Load the good rows into a table with constraints, as a second line of defence.
- Reconcile the loaded total with the control total from the client, and the counts with the export.
- Report with a ranking query, then score a launch checklist, honestly.
Run it
import csv, io, sqlite3
raw = """ref,customer,product,qty,price,date
A100, asha ,Fern,2,12.50,2026-10-01
A101,Ben,fern,1,12.5,2026-10-02
A101,Ben,Fern,1,12.50,2026-10-02
A102,Chloe,Cactus,x,6.00,2026-10-03
A103,Dev,Orchid,1,18.00,03/10/2026
A104,Ella,Clay pot,3,8.50,2026-10-04
A105,Femi,Unknown plant,1,5.00,2026-10-05
A106,Asha,Glass pot,1,-15.00,2026-10-06
A107,Ben,Plant food,2,5.50,2026-10-07"""
CONTROL_TOTAL = 74.00 # the figure the client says the export should add up to
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE products (name TEXT PRIMARY KEY);
INSERT INTO products VALUES ('Fern'), ('Cactus'), ('Orchid'), ('Clay pot'), ('Glass pot'), ('Plant food');
CREATE TABLE clean_orders (ref TEXT PRIMARY KEY, customer TEXT NOT NULL, product TEXT NOT NULL REFERENCES products (name),
qty INTEGER NOT NULL CHECK (qty > 0), price REAL NOT NULL CHECK (price > 0), order_date TEXT NOT NULL);
CREATE TABLE rejects (ref TEXT, reason TEXT);
""")
names = {n.lower(): n for (n,) in db.execute("SELECT name FROM products")}
seen, refused = set(), 0
for r in csv.DictReader(io.StringIO(raw)):
ref, who, prod = r["ref"].strip(), r["customer"].strip().title(), names.get(r["product"].strip().lower())
reason = None
if ref in seen: reason = "duplicate ref"
elif not r["qty"].isdigit(): reason = "bad qty"
elif not (len(r["date"]) == 10 and r["date"][4] == "-"): reason = "bad date"
elif prod is None: reason = "unknown product"
elif float(r["price"]) <= 0: reason = "bad price"
if reason:
db.execute("INSERT INTO rejects VALUES (?, ?)", (ref, reason)); continue
try:
db.execute("INSERT INTO clean_orders VALUES (?, ?, ?, ?, ?, ?)", (ref, who, prod, int(r["qty"]), float(r["price"]), r["date"]))
seen.add(ref)
except sqlite3.IntegrityError:
refused += 1
one = lambda sql: db.execute(sql).fetchone()[0]
print("rows in the export:", len(raw.splitlines()) - 1)
print("accepted:", one("SELECT COUNT(*) FROM clean_orders"))
for ref, why in db.execute("SELECT ref, reason FROM rejects ORDER BY rowid"):
print(f"rejected {ref}: {why}")
print("extra rows refused by constraints:", refused)
total = round(one("SELECT SUM(qty * price) FROM clean_orders"), 2)
print("total of accepted rows:", total)
for name, spent, pos in db.execute("""SELECT customer, SUM(qty * price) AS spent, RANK() OVER (ORDER BY SUM(qty * price) DESC)
FROM clean_orders GROUP BY customer ORDER BY 3"""):
print(f"position {pos}: {name} {spent:.2f}")
checks = {
"every rejected row has a reason": one("SELECT COUNT(*) FROM rejects WHERE reason IS NULL") == 0,
"no duplicate refs in the clean table": one("SELECT COUNT(*) - COUNT(DISTINCT ref) FROM clean_orders") == 0,
"accepted plus rejected equals the export": one("SELECT COUNT(*) FROM clean_orders") + one("SELECT COUNT(*) FROM rejects") == 9,
"the total matches the control total": total == CONTROL_TOTAL,
"tested on a fresh export": False,
}
for k, v in checks.items():
print(f"{k}: {'yes' if v else 'no'}")
print("checks passed:", sum(checks.values()), "of", len(checks))
rows in the export: 9
accepted: 4
rejected A101: duplicate ref
rejected A102: bad qty
rejected A103: bad date
rejected A105: unknown product
rejected A106: bad price
extra rows refused by constraints: 0
total of accepted rows: 74.0
position 1: Ella 25.50
position 2: Asha 25.00
position 3: Ben 23.50
every rejected row has a reason: yes
no duplicate refs in the clean table: yes
accepted plus rejected equals the export: yes
the total matches the control total: yes
tested on a fresh export: no
checks passed: 4 of 5
The export held 9 rows. 4 were accepted. The other 5 were set aside, each with a reason: A101 (duplicate ref), A102 (bad qty), A103 (bad date), A105 (unknown product), A106 (bad price). The second row for A101 was a repeat of the first, so only one copy was kept. The extra layer of defence, the table constraints, refused 0 more rows, which tells us the validation caught everything the constraints would have. The accepted rows add up to 74.0, which matches the client’s control total, and the report ranks customers by spending: position 1 Ella at 25.50, position 2 Asha at 25.00, position 3 Ben at 23.50. No ties, so the order is firm.
The honest part: is it ready?
The program ends with a checklist of five items, and 4 of 5 pass. Every rejected row has a reason. There are no duplicate references in the clean table. Accepted plus rejected equals the export, so nothing disappeared. The total matches the control total. The one failure is the same one as in earlier capstones: the pipeline has not been tested on a fresh export. These nine rows were used to write the rules, so of course the rules handle them. Another partner’s file will contain problems nobody has seen yet. The honest status is ‘works on the sample, not yet proven’. Dev should say so to the client, run the pipeline on the next real file with a person watching and keep the rejects table, since it will tell him what to add.
What you can do now
You can read how a database plans its work and design indexes for real queries. You can protect data with transactions, constraints, backups and limited access. You can clean messy data and set aside what you cannot trust. You can design a database from stories, build a reporting copy and avoid the trap of the multiplying join. You can price work honestly and plan a project around its real critical path. Above all, you check your own work: every important number in this course was confirmed a second way. Keep that habit. Tools and dialects will change, and it will not.
Where to go from here
- Practise on data you care about, with a free database tool and a copy of the data.
- Learn the dialect of the database you will use at work, and keep its documentation open.
- Read other people’s queries and rewrite them more clearly.
- Learn a language such as Python or a reporting tool, and use SQL as its data source.
- Keep a small portfolio of cleaned, documented, reconciled projects.
Declaring a pipeline finished because the sample file loaded. A pipeline is proven by the next surprise file. Keep the rejects table, review it and expect to add rules.
The export, products, prices and control total are made up, in sample money, and everything ran in an in-memory SQLite database. Real projects have larger files, more rules and more people involved. Privacy, contract and tax points in this course are outlines, not legal, tax or financial advice.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
Why does the pipeline set rows aside instead of fixing them?
- A guess could hide an error, so the owner should decide
- Fixing is impossible in SQL
- It saves disk space
- Constraints forbid it
Every rejected row kept a reason for review.
What does the control total test?
- That the database is fast
- That the loaded rows add up to the figure the client expects
- That names are unique
- That dates are valid
The accepted rows summed to 74.0, matching it.
Why is the pipeline not yet ready to launch?
- It lost rows
- Its totals do not match
- It has no constraints
- It has not been tested on a fresh export
The rules were written from these nine rows.
Why keep the rejects table?
- It slows the load
- It shows what new problems appear and what rules to add
- It holds the totals
- It replaces the clean table
It is the best guide to improving the rules.
What does “accepted plus rejected equals the export” prove?
- The totals are right
- The dates are valid
- The names are unique
- No rows vanished on the way
Four accepted and five rejected make nine.
Summary
- Validate in order and set aside what fails, with reasons.
- Constraints are a second line of defence.
- Reconcile counts and totals.
- Say “not yet proven” until a fresh file has been tested.
Questions
Why are some rows rejected instead of repaired?
Because a guess can hide an error. A person who knows the data should decide how a bad row is fixed.
What is a control total?
A figure supplied independently, such as the client's own total, that your loaded data must match. It is a simple, powerful check.
Is the data real?
No. The export and its figures are made up, in sample money.
