This was my capstone for DATA 516, the scalable-data-systems course in the University of Washington's data science master's. The brief was open-ended: take a firehose of raw e-commerce events and turn it into something a data scientist can actually query, then defend every architectural choice. The numbers below come from a real run over 25.9 million synthetic events, and the scope is at the end.

The problem

A simulated e-commerce platform emits 500,000 to 750,000 events every five minutes (page views, cart actions, purchases, searches) that land in S3 as compressed JSONL. The raw data is reliable. Querying it is the problem. A simple question like "what is the conversion funnel by product?" means scanning hundreds of megabytes of nested logs, every time, and waiting. The five questions I designed the pipeline to answer (conversion funnels, hourly revenue, top products, category performance, daily active users) each meant a slow, expensive full scan. The job was to make those answers cheap and fast without throwing away the raw record.

Bronze, Silver, Gold

I built it as a medallion architecture: three layers, each with one responsibility.

Bronze is the immutable landing zone, the raw JSONL exactly as it arrived, Hive-partitioned down to the minute. Nothing in the pipeline writes back to it. If a transformation turns out to be wrong, I can reprocess from here.

Silver is the cleaned, typed layer. A single PySpark job on AWS Glue reads Bronze through the Glue Data Catalog, parses the timestamps into real datetimes, computes revenue on purchase events, drops records missing critical fields, and writes Parquet with Snappy compression, partitioned by date. Columnar Parquet is the first real lever, because Athena can prune columns and push predicates down instead of parsing whole JSON objects.

Gold is five pre-aggregated tables, one per question: product funnel, hourly revenue, product popularity, category-by-day, and daily user activity. The expensive GROUP BYs run once, in the ETL, not on every query. A query that used to scan 420 MB now reads a few kilobytes.

The whole Bronze-to-Silver-to-Gold transform is one PySpark script, so the three layers advance in a single run rather than as three jobs I have to keep in sync.

What the layering bought

I measured Bronze-direct queries against the Gold tables on the same data:

  • Query latency: about 16.5× faster on average, 10.9s down to 0.66s.
  • Data scanned: about 210,000× less, roughly 420 MB down to 2 KB for a typical query. (Per query the reductions ran from 105,000× to over a million×, since the biggest wins landed on the smallest result sets.) At Athena's $5-per-terabyte rate, that is the difference between a few tenths of a cent per query and effectively free.
  • Storage: roughly 15,000× smaller at the Gold layer, where 440 MB of Bronze becomes about 29 KB of aggregates, because the questions only need the summary, not every row.

Pre-aggregation trades ETL complexity for query performance. I moved cost from query time to ETL time and took on the maintenance of five aggregate tables, and the trade was acceptable because the five questions the pipeline answers were clear and stable. For ad-hoc exploration across the full event stream you would still go to Bronze, since the layering sits in front of the raw data rather than replacing it.

Two bugs in the incremental path

The medallion pattern is well documented, so the architecture went in roughly as planned. The time went on two bugs in the incremental path, both of which failed quietly rather than loudly.

The first was incremental processing. Glue job bookmarks track which files have already been handled so a re-run only touches new data. The catch is that when bookmarks find nothing new, the job receives an empty DataFrame and the first transformation throws on a column that "does not exist," so the pipeline fails on a no-op. The fix was to detect the empty case at the top and short-circuit to a clean job.commit(), making "no new data" a success rather than a crash. After that the incremental path behaved: a run with new data processed only the delta, and a run with nothing new exited in under a minute instead of reprocessing history.

The second was timestamps. The events carry full ISO-8601 strings with timezone offsets (2025-12-07T03:55:56.633003+00:00), and Athena's plain CAST(... AS TIMESTAMP) cannot parse them. I had to use FROM_ISO8601_TIMESTAMP in the Bronze-facing SQL and PySpark's to_timestamp in the Silver transform. It is a small thing, but it produced nulls silently rather than an error, so it survived until I checked the parsed column against the raw one.

Reproducible by design

The entire stack (the S3 bucket with lifecycle rules, the Lambda event generator, the Glue job and crawlers, the Athena workgroup) is one CloudFormation template. Tearing it down and standing it back up is a single command, which mattered for working inside a student AWS account where idle resources cost real money. The generator's schedule ships disabled by default for the same reason, so nothing runs up a bill unless I turn it on.

Scope

The architecture follows the standard medallion pattern; the deployment is a capstone. The data is synthetic, generated by a Lambda, with no real storefront behind it. It ran inside a restricted student role, so a few steps a production version would automate are manual here, and I never load-tested it under real concurrent traffic. To take it further I would add data-quality monitoring beyond null checks, alerting and retries on the ETL, cost-budget alarms, and a real orchestration layer in place of manual triggers. The pattern itself (immutable raw, typed middle, pre-aggregated serving, all defined as code) is what I would carry into a production system.