584,524 order items from an Excel export that was 44% blank padding, accounted for row by row.
What I didBuilt end to end: ingestion, Spark jobs, warehouse, tests and CI.
Every number on this page links to the file it came from, pinned to pk-ecommerce-pipeline @ 14c5147.
Headline results
The short version
The problem
A data engineering role asks for PySpark, SQL, Docker, cloud storage and data quality. I wanted one project that shows all five on real, messy data, and that I could defend line by line.
What I built
A batch pipeline over Pakistan’s largest public e-commerce dataset: 584,524 order items from July 2016 to August 2018, in a 106 MB CSV that began life as an Excel export. Ingest splits it into monthly files, as if each month arrived separately, and lands them untouched in an S3 data lake with a manifest. PySpark reads each month with an explicit schema, cleans it and checks 16 data-quality rules. Clean rows load into a Postgres star schema in one transaction per month. Rows that fail a rule go to a quarantine table with every reason code. SQL views answer the business questions.
How I tested it
Fifty unit tests and an end-to-end test run in CI. Every stage proves its row counts add up and stops the pipeline if they do not. The final results were also checked month by month against an independent plain-Python implementation of the same rules, and all 26 months match to the paisa.
What I would do next
Upgrade to Spark 4, which retires the accepted dependency risk in the security review and moves the job to the AWS SDK v2.
How it works
Pick a request and watch it go through the system. The red ones are the requests I use to attack it. They end where the system is supposed to stop them.
One row per order item, loaded in one transaction per month. Re-running a month never creates duplicates.
- Kaggle CSV
- Ingest
- raw/
- Clean with PySpark
- silver/
- Load
- Star schema
- SQL views
A clean row travels to the warehouse. A bad row is set aside in quarantine with its reasons. Raw files are never modified, so any bug can be replayed.
Test results
11 claims, each with the bar I held it to and what I measured. 4 failed and 0 are unclear. They stay in, because a list where everything passes tells you nothing.
| Result | Claim | Expected | What I measured | Source |
|---|---|---|---|---|
| PASS | Every row is accounted for | rows read = clean + quarantined + duplicates | 584,524 read = 574,758 loaded + 9,766 quarantined | README.md ↗ @ 14c5147 |
| PASS | Revenue matches an independent implementation | n/a | all 26 months match a plain-Python check exactly, to the paisa | README.md ↗ @ 14c5147 |
| PASS | The reconciliation checks catch real bugs | n/a | the row-count check caught line breaks inside 11 quoted SKUs splitting rows in two | README.md ↗ @ 14c5147 |
| PASS | Re-running a month never duplicates rows50 unit tests plus an end-to-end test run in CI. | idempotent loads | one transaction per month, and the gold load is serialised | README.md ↗ @ 14c5147 |
| PASS | A full backfill is practical to run | n/a | 26 months in about 4 minutes on a laptop, with one command | README.md ↗ @ 14c5147 |
| PASS | The quarantine has an explanation | n/a | one rule, DISCOUNT_EXCEEDS_LINE, accounts for 9,713 of 9,766 rows (99.5%) | README.md ↗ @ 14c5147 |
| PASS | Services are locked down by default | n/a | ports on localhost only, no secrets in git, checksummed jars, dependencies scanned in CI | README.md ↗ @ 14c5147 |
| FAIL | The source file can be loaded as deliveredThe data was profiled before anything was designed, and each problem became a rule or a design decision. | n/a | no; it has 464,051 blank padding lines, Excel errors (#REF! and #N/A), and a grand_total repeated on every item | README.md ↗ @ 14c5147 |
| FAIL | Completed revenue alone shows the real trendA naive revenue chart would show sales falling to zero. The value is there, filed as in progress. | n/a | no; June to August 2018 show 0.0 because almost no orders were marked complete at export time | README.md ↗ @ 14c5147 |
| FAIL | Spark is the right size of tool for 0.5M rowsI chose Spark so the design scales, and wrote the trade-off down instead of hiding it (D7). | n/a | no; at 0.5M rows pandas would be faster, and Spark was chosen so the design scales | README.md ↗ @ 14c5147 |
| FAIL | Dependencies have no known vulnerabilitiesThe job only parses trusted batch files, and its one network service is on localhost. | n/a | no; PySpark 3.5's bundled jars and AWS SDK v1 are an accepted risk until a Spark 4 upgrade | docs/SECURITY.md ↗ @ 14c5147 |
The numbers
Each headline is the conclusion. Open “View the data” under a chart for the exact values.
White Friday 2017 alone is 31% of 26 months of completed revenue.
Completed revenue by month, PKR millions
From June 2018 almost no orders were marked complete when the data was exported, so those months show 0.0. That is not a sales collapse; the value is filed as in progress.
Prepaid orders are cancelled almost 8 times as often as cash on delivery.
Order outcomes by payment type
Share of orders by final status. Cash on delivery is refunded more, which fits returns after delivery.
One rule explains 99.5% of the quarantine.
Why 9,766 rows were quarantined
A discount larger than price times quantity gives negative revenue, so those lines are set aside rather than guessed at.
What went wrong
Spark split 11 rows into 22 broken records
- How it surfaced
- The row-count check between ingest and Spark failed. Eleven SKUs contain line breaks inside quotes, so Spark turned each of those rows into two broken records.
- What I did
- The CSV reader now sets multiLine. Raw files are immutable, so the fix was replayed from the original data rather than patched downstream.
grand_total would have counted a 3-item order three times
- How it surfaced
- grand_total is the order total repeated on every item, so summing it overstates revenue for every multi-item order.
- What I did
- Revenue is computed per item as price × qty − discount, and the fact table has one row per order item.
9,713 discounts were larger than the item they discounted
- How it surfaced
- Profiling found lines whose discount exceeds price × quantity, which gives negative revenue.
- What I did
- Those rows go to quarantine with the reason code DISCOUNT_EXCEEDS_LINE, visible and reprocessable, instead of being dropped or silently clamped.
The object store vanished two weeks before the build
- How it surfaced
- The project was designed on MinIO. MinIO deleted its images from Docker Hub on 11 September 2026.
- What I did
- Because the code only speaks the S3 API, switching to RustFS was a one-line change in docker-compose.yml, and so is moving to AWS.
The security review found vulnerable jars
- How it surfaced
- The Postgres JDBC driver 42.7.4 had three HIGH CVEs, and the AWS SDK bundle 1.12.262 shipped old Jackson, Ion and Netty with 19 HIGH or CRITICAL CVEs.
- What I did
- Upgraded to JDBC 42.7.13, which has no known CVEs, and SDK 1.12.797. Twelve findings remain, all in shaded Netty, and are written up as an accepted risk until a Spark 4 upgrade.
Data quality fell off a cliff in January 2018
- How it surfaced
- Quarantined rows jump from a handful a month to between 257 and 2,934 a month. That looks like a change in the source system.
- What I did
- Nothing to fix upstream. The quarantine table makes the change visible, measurable and reprocessable instead of silently dropped.
Choices I would defend
Keep raw data immutable
Any bug can be fixed and replayed from the original files. That was used for real to fix the CSV bug (D3).
Model revenue at the item grain
grand_total is order-level, so the fact table has one row per order item and revenue is computed from price, quantity and discount (D18).
Quarantine bad rows, never drop them
A failing row is set aside with every reason code and its raw values, so quality problems stay visible and reprocessable.
Speak only the S3 API
Storage and compute are decoupled, so swapping the object store took one line when MinIO disappeared (D4).
Make every stage prove its counts
The pipeline stops if Spark reads a different number of rows than ingest wrote, if read does not equal clean plus quarantined plus duplicates, or if loaded does not equal staged.