A 109.95M-event e-commerce analytics pipeline built to run on a 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
56 passed
$ docker build && docker run
GET /healthz 200 OK
GET /v1/users/.../segment 200 OK
recency_days: 51
$ python train_propensity.py (full dataset)
auc=0.7541 lift=4.60x (byte-identical, 3/3)
Not a screenshot, every line above is a real, rerunnable command against the full 109.95M-row dataset.
On-disk compression
13.7GB raw CSV → 1.82GB ZSTD Parquet, full dataset
Peak memory vs. naive load
~40GB estimated pandas load → ~3GB measured peak RSS
Propensity lift
0.7541 AUC, full 109.95M-row dataset
Bugs found & fixed
From an independent line-by-line audit
Backend tests passing
Full suite, against the rebuilt sample DB with the full-data model
Identical reruns
Byte-identical metrics.json, after the determinism fix
Full rebuild time
Sample → star schema → ML tables
Deployment verified
Live API container and live static frontend
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-RAM laptop, and this case study's own full 109.95M-row verification run went further still, completing on just a 10GB-RAM box within a 3GB DuckDB memory budget. A naive pandas.read_csv() on the full dataset would need an estimated ~40GB of RAM (measured at 389 bytes/row on a 3M-row sample, extrapolated); closing that gap is the first problem to solve before any analysis can start.
Events processed (full dataset)
Ingested and modeled end to end, not just reported
Buyers sharing one frequency value
76.7% in the tracked 1.65M-event sample, why the RFM tiebreak bug mattered
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
Single streaming DuckDB COPY: type downcast, dictionary encoding, ZSTD-3 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 ≥ 5 (full dataset), 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 routes, built from precomputed JSON/Parquet artifacts (DuckDB-WASM runs ad-hoc SQL in the browser). No backend on the request path, and it deliberately never calls the API.
4 versioned REST endpoints, read-only DuckDB connection, model cached as a singleton. Not deployed by default; it exists as a working, tested service, not the dashboard's backend.
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.
Real e-commerce clickstream data throughout, no demo or synthetic rows anywhere. The exact chart below is from the tracked 1.65M-event sample; the full 109.95M-row dataset was later downloaded and run end to end, and shows the same shape.
View-to-cart (3.77%) is the weak link here, not cart-to-purchase (41.7%). The full 109.95M-row dataset (23,016,650 sessions) shows the same shape at a different scale: 10.1% view-to-cart against a much higher 60.6% cart-to-purchase, the funnel breaks near the top, not at checkout, at either 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 5 co-occurrences on the full dataset (3 on the sample). 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 7,704 directed rules from 1.66M purchase events on the full 109.95M-row dataset (11 rules on the 1.65M-event sample, with a lower support threshold), 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 >= 5 (full dataset).8# 7,704 directed rules, full dataset. 11 on the sample.October behavior only for features, November purchases only for the label. A random split would leak future rolling-window information into training.
From the full 109.95M-row dataset, the model actually checked in. The reproducibility fix below was proven with 3 identical sample-scale retrains first, then carried straight over to this full-data run.
AUC-ROC
Held-out November test set, full 109.95M-row dataset
Top-5% conversion
vs. 8.0% November baseline
Lift
Population baseline vs. top-5% scored
Basket affinity rules
lift > 1.2, ≥ 5 co-purchases/session, full dataset (11 on the sample)
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. Full dataset, 697,470 buyers.
Champions spend 1.7x 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 to thousands of users per segment on identical input.
Champions spend 1.7x the next-highest segment
$1,642 average spend vs. $956 for "Cant Lose Them," and they are also the most recently active, the clearest signal in the segmentation.
Most buyers purchase on exactly one distinct day
76.7% of the tracked sample's buyers, 57.7% of the full 697,470-buyer population. 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, sample and full dataset alike. 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, matching 23.5% on the full 109.95M-row dataset. 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 two figures on the hero strip measure two different things, an on-disk footprint and a peak in-memory one, that the project's own earlier dashboard once conflated into a single, wrong pair of numbers (see Failures below). Here's how each is actually earned, from the full 109.95M-row run.
~40GB is pandas.read_csv() on the full dataset, extrapolated from a measured 389 bytes/row on a 3M-row sample. ~3GB is the actual measured peak resident memory of the tuned DuckDB pipeline, no swap.
Raw CSV vs. the optimized ZSTD-3 Parquet output, measured on the full 109.95M-row dataset.
optimize_dataset.py runs a single streaming DuckDB COPY that reads both raw CSVs and writes one type-optimized Parquet file. DuckDB processes the input in bounded-memory chunks and spills to disk if a step needs more than the configured limit, so the pass never holds the full 109.95M-row table in RAM.
This replaced an earlier two-pass Polars pipeline whose .cast(pl.Categorical) on the 23M-unique user_session UUID column built a 23M-entry in-memory dictionary and OOM'd machines under 16GB, the opposite of an optimization for a near-unique column.
Parsed once at optimization time, not re-parsed on every downstream query
Automatic, per Parquet row group, no explicit cast needed. user_session (a near-unique 36-char UUID) is deliberately left as a plain string instead, see the note above
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 eleven 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 full pipeline froze the machine on the first run, and it was not a config typo
Running the scripts as committed on a 10GB box, a GROUP BY over 23M session groups drove resident memory to ~8.5GB and exhausted swap, hard-freezing the OS. Root cause: two non-spillable mode() aggregates, plus a hardcoded memory limit set above the box's physical RAM.
One shared pragma-setting module now caps memory at 3GB and enables disk spill on every session; the non-spillable aggregates were replaced with a spillable equivalent. The full pipeline now completes with peak memory ~3GB and zero swap.
The README's own full-scale headline numbers were partly wrong
With the full dataset finally in hand, three marquee figures did not survive contact with the data: a claimed 15M sessions (real 23,016,650), 10M+ product pairs across 4.5M purchase events (real 7,704 rules from 1,659,788 purchases, off by three orders of magnitude), and a 12GB raw CSV (real 13.7GB).
Corrected the wrong figures across the repo and this case study, and documented the full run so the real numbers stay traceable.
The dashboard is live, and every number above reruns.