A 109M-event e-commerce analytics pipeline built to run on a 16GB laptop instead of a cloud warehouse, then re-verified end to end, which is how two silent non-determinism bugs were found before they could ship as "results."
$ pytest -q
30 passed in 2.1s
$ docker build && docker run
GET /healthz 200 OK
GET /v1/users/.../segment 200 OK
recency_days: 51
$ python train_propensity.py (x3)
auc=0.7203 lift=4.40x (identical, 3/3)
Not a screenshot, every line above is a real, rerunnable command against the checked-in sample dataset.
In-memory reduction
120GB naive Pandas → 3.7GB Polars-optimized
On-disk compression
12GB raw CSV → 3.2GB ZSTD Parquet
Propensity lift
0.72 AUC, verified across 3 identical reruns
Bugs found & fixed
From an independent line-by-line audit
30 tests passing
Identical reruns
Byte-identical metrics.json
Full rebuild time
Sample → star schema → ML tables
Deployment verified
Live container, real API calls
E-commerce platforms generate huge event volumes but rarely have tooling to answer strategic questions quickly: who's showing churn signals, who's likely to purchase next month, what's commonly bought together. At 100M+ rows the default reach is Snowflake, BigQuery, or Spark, valid choices, but ones that add cost and operational overhead disproportionate to early-stage analysis.
The bet: modern single-machine OLAP engines get further than assumed before distributed compute is actually necessary. The whole pipeline, ingestion through model training, is designed to run on a 16GB laptop. A naive pandas.read_csv() on the full dataset needs an estimated ~120GB of RAM; closing that gap is the first problem to solve before any analysis can start.
Events processed (sample)
109M reported at full scale
Buyers sharing one frequency value
Why the RFM tiebreak bug mattered here
6 pipeline stages produce every table the dashboard and API need. Neither consumer ever calls the other, a deliberate trade-off, not an oversight.
$ python scripts/create_cloud_database.py && python src/models/train_propensity.py
Optimize
Polars streaming scan: type downcast, dictionary encoding, ZSTD-L3 Parquet.
Star schema
dim_products, dim_users, fact_sessions, fact_daily_kpis via one shared builder module.
Segment
RFM scoring, NTILE(5) window functions, deterministic tiebreak on user_id.
Basket
Market-basket self-join, support ≥ 3, lift > 1.2, no Python graph library.
Retain
Weekly cohort retention from dim_users + events, re-enabled in sample mode.
Train
LightGBM, October features → November target, explicitly seeded and ordered.
7 pages, queries DuckDB directly. Deliberately never calls the API, zero runtime coupling.
4 versioned REST endpoints, read-only DuckDB connection, model cached as a singleton.
The star-schema and RFM SQL used to be hand-copied into two separate files. That duplication had already let a real bug ship independently in both places.
Two independent copies
Same star-schema + RFM SQL, hand-copied twice. Both carried the same CURRENT_DATE recency bug, undetected by either.
One shared, tested module
Both callers import this. A bug fixed once is fixed everywhere, and a regression test protects both consumers.
1.65M events, real, verified directly from the committed sample dataset, not the full 109M-row scale the project reports but this pass didn't re-download.
View-to-cart (3.77%) is the weak link, not cart-to-purchase (41.7%). Sessions drop off before ever reaching a cart, the same qualitative shape the project's full-scale claims describe, at this dataset's own scale.
No Python graph library. Product co-occurrence, confidence, and lift are computed via a self-join and window functions, directly in DuckDB.
Purchase pairs within the same session are self-joined, grouped, and filtered to a minimum support of 3 co-occurrences. The lift formula's derivation is worked out explicitly in a code comment rather than copy-pasted from a library, then implemented algebraically so it runs as one vectorized SQL pass.
Filtered to lift > 1.2, this produces 11 affinity rules on the 1.65M-event sample, each satisfying both thresholds, reproduced identically across every rebuild performed for this case study.
1# Lift(A -> B) = P(A and B) / (P(A) * P(B))2# High lift (> 1) means strong association.3 4confidence = pair_count / count(A)5lift = confidence / (count(B) / total_sessions)6 7# Filtered to lift > 1.2, support >= 3.8# 11 rules on the 1.65M-event sample.October behavior only for features, November purchases only for the label. A random split would leak future rolling-window information into training.
Final numbers, reproduced identically across 3 independent retrains after fixing the determinism bug below.
AUC-ROC
Identical across 3 reruns
Top-5% conversion
vs. 5.8% baseline
Lift
Population baseline vs. top-5% scored
Basket affinity rules
lift > 1.2, support ≥ 3
oct_removes (cart-abandonment count) sits at exactly zero, a reasonable feature to engineer that reproduced at zero gain across every independent retrain: a real, honest null result, left visible rather than quietly dropped.
The single most important verification pass in this case study: does the pipeline give the same answer on the same data, every time?
Rebuilding this project meant actually rerunning it, not just reading it, which is how this surfaced: neither the propensity model nor the RFM segmentation gave the same answer twice. Root cause in both cases was the same: DuckDB does not guarantee row order without an explicit ORDER BY, and a seeded shuffle or window function over unordered input is not actually seeded.
The fix: an explicit ORDER BY user_id on the training query, full LightGBM seeding, and user_id as a secondary sort key on every RFM NTILE() call. Both are now backed by regression tests that rebuild twice in a single test run and assert the outputs match.
1-- Secondary ORDER BY user_id breaks ties2-- deterministically. Without it, rows sharing a3-- recency/frequency/monetary value can land in4-- different NTILE buckets on different runs.5NTILE(5) OVER (ORDER BY recency_days DESC, user_id) AS r_score,6NTILE(5) OVER (ORDER BY frequency ASC, user_id) AS f_score,7NTILE(5) OVER (ORDER BY monetary ASC, user_id) AS m_scoreRecency, Frequency, Monetary computed per buyer via NTILE(5), then a CASE statement on Recency and Frequency assigns the segment label.
Champions spend 1.5x the next-highest segment and are the most recently active, the clearest signal in the segmentation. These exact counts held across two independent rebuilds after the tiebreak fix below; before it, they varied by hundreds of users per segment on identical input.
Champions spend 1.5x the next-highest segment
$922 average spend vs. $585 for Loyal Customers, and they are also the most recently active, the clearest signal in the segmentation.
76.7% of buyers purchased on exactly one distinct day
A frequency distribution this skewed is why an unseeded NTILE tiebreak turned into a real, load-bearing bug rather than a rounding error.
oct_removes carries zero predictive signal
A reasonable feature to engineer, cart-abandonment count, reproduced at exactly zero gain across every independent retrain. A real, honest null result.
Fully built and wired to a real query, disabled with a message claiming it needed data that, on inspection, was already available. Re-enabled below.
Week-1 (cohort-weighted): 23.3% retention, 76.7% drop-off. Retention stabilizes for later cohorts, 20-30% still returning 5-8 weeks out, suggesting the drop-off is front-loaded, not a slow bleed.
The 97% and 73% figures on the hero strip are two different measurements of two different things, an in-memory footprint and an on-disk one, that the project's own dashboard once conflated (see Failures below). Here's how each is actually earned.
pandas.read_csv() on the full dataset (estimated) vs. Polars, streamed through pl.scan_parquet(), which never materializes the full frame
Raw CSV vs. .sink_parquet() output with ZSTD compression, a separate measurement the dashboard once conflated with the in-memory figure
optimize_ecommerce_dataset reads the raw CSV through pl.scan_parquet(), a lazy, streaming scan that never materializes the full frame in memory, and rewrites it column by column before a single row of downstream SQL ever runs.
The output is written back out with .sink_parquet(), a streaming write with the same never-materialize property, so the optimization step itself never pays the ~120GB cost it's eliminating downstream.
Parsed once at optimization time, not re-parsed on every downstream query
Dictionary-encoded: each repeated string stored once, referenced by index
IDs fit comfortably in 32 bits; halves the column width
Halves the column width, no observed precision loss for pricing data
compression="zstd", compression_level=3, not the higher-ratio default. The code's own comment explains why: "level 10 was too slow." A deliberate speed-over-ratio trade-off on a step that runs before every rebuild, not an oversight.An independent audit found these by re-executing the pipeline against its own committed data, not by trusting the README. All nine below were fixed and reverified; the two most consequential got their own section above.
RFM recency silently drifted by a day, every day, since 2019
Two independent files computed recency_days as DATE_DIFF('day', MAX(event_time), CURRENT_DATE), anchored to wall-clock "today" instead of the dataset's own reference date. On 2019 data, that meant values in the thousands.
Both call sites now go through one shared, tested module anchored to MAX(event_time). Verified: recency now correctly ranges 0–60 days.
The dashboard documented customer segments that could never appear in its own chart
The RFM explainer described "Cant Lose Them" and "Hibernating", real labels, but from a different, never-deployed RFM implementation. The actually-deployed table only ever produces 6 different labels.
Rewrote the page copy to match the taxonomy that is actually in the data.
A DuckDB function that does not exist was called, caught mid-file, and left half-fixed
A feature-engineering query called mode(part_of_day(session_start)), a function DuckDB does not have. The very next line is a comment admitting it, followed by a second query that simply dropped the feature instead of fixing it.
Implemented the feature correctly with an explicit CASE / EXTRACT(HOUR) bucketing, verified against live data.
A "reproducible" sample was not reproducible
A RANDOM_SEED constant was defined, with a comment noting DuckDB dropped SET random_seed, but nothing replaced it.
Switched to DuckDB's setseed(), verified deterministic across fresh connections.
Dashboard ML metrics were hand-copied constants, disconnected from the model that shipped
Feature-importance and lift charts were literal Python dict literals commented "hardcoded from training logs," with nothing keeping them in sync with the checked-in model.
Added a metrics.json artifact the training script writes itself; wired the dashboard and glossary to read it live.
Three contradictory "storage reduction" numbers were presented as the same claim
"97% memory reduction," "87% disk reduction," and "73% storage reduction" all appeared as headline claims, sometimes on the same page, one badge mathematically not matching its own numbers.
Reconciled to two clearly-labeled, internally-consistent numbers: 97% in-memory, 73% on-disk. Applied consistently everywhere.
A fully-built visualization was disabled with a message that undersold why
A real, working cohort-retention heatmap was commented out, replaced with a message claiming it needed tables "not available in sample mode." The underlying query only needed two tables already present.
Added the retention build to the shared module and re-enabled the real heatmap, with live-queried numbers replacing two other hardcoded claims on the same page.
The checked-in model artifact did not match the script that supposedly produced it
The saved model's LightGBM params included device: 'gpu', but the training script's params dict never sets a device key, undermining the "runs on a laptop" narrative for the one step that had not actually happened on one.
Retrained from the script exactly as written, CPU-only.
The project's own local vs. deployed pipelines had quietly diverged for years
Git history shows real deployment debugging almost immediately after the first commit: DuckDB version fixes, Windows/Linux compatibility fixes, a 500 error. The full-scale pipeline and the deployed cloud pipeline were never reconciled.
Consolidated the two genuinely duplicated, actively-deployed copies into one shared module; made the config paths portable. The full-scale pipeline's own separate schema was deliberately left alone, unsafe to verify without the full dataset.
The dashboard is live, and every number above reruns.