🏗️ What you're building
NaijaCart is a Nigerian e-commerce platform serving millions of shoppers across Lagos, Abuja, Kano, Port Harcourt, and Ibadan. Every page visit generates a click event — in production, that's 100 million events per day. The data engineering team (Tunde, Funmi, and Chidi) runs a nightly batch job that rolls up those raw events into three summary tables used by product, marketing, and BI.
Today you build that batch job from scratch. You'll write synthetic click data, run three aggregation queries, and have a pipeline that is architecturally identical to what runs on the cluster — only the data source changes at 100M scale.
- NaijaCart logs every page visit — 100M events/day in production (6 pages, 5 cities, 3 device types)
- Today's task: build the daily rollup — page clicks, city x device matrix, daily unique users
- Tools: PySpark local (1M rows for dev), identical code runs on cluster at 100M rows
- Skills applied: DataFrame creation, groupBy, countDistinct, date_format, window ordering
- Output: three tables that feed NaijaCart's BI dashboards every morning
Create a new directory for this mini-build. You only need two files — the main pipeline script and an optional test file you'll write after following Lesson 12 (pytest + chispa).
The only dependency is PySpark, which you installed in Lesson 00. If you're starting fresh:
PySpark needs a JVM. The pipeline sets os.environ["JAVA_HOME"] programmatically at the top of the script so you don't have to export it in your shell. If you already have JAVA_HOME set in your ~/.zshrc, the line is a no-op. If not, the script sets it automatically before Spark initialises.
The first block of rollup.py sets up the Spark session, defines the schema, and generates N = 1_000_000 synthetic click events using Python's random module. Let's walk through the key design decisions before looking at the full script.
Why createDataFrame and not a file?
In production, Tunde's pipeline reads from spark.read.parquet('s3a://naijacart/events/'). But for local development, generating data in-process with createDataFrame is faster than writing and reading a file. The code that follows (all the groupBy and agg calls) is identical in both cases — Spark doesn't care whether the DataFrame came from a file or from a Python list.
Schema design
The schema mirrors a real NaijaCart event log. Each row represents one page view:
| Column | Type | Description | Example value |
|---|---|---|---|
user_id |
StringType | Anonymised shopper ID (50,000 unique users) | U04271 |
page |
StringType | URL path of the visited page | /cart |
city |
StringType | Shopper's city (5 cities) | Lagos |
device |
StringType | Device type (mobile, desktop, tablet) | mobile |
event_time |
StringType | Event timestamp as ISO string | 2024-01-15 14:32:00 |
time_on_page_s |
IntegerType | Seconds spent on the page (1–300) | 87 |
event_ts |
TimestampType | Parsed timestamp column (added by withColumn) | 2024-01-15 14:32:00 |
The local N = 1_000_000 constant is the only thing you change when scaling. In production, you delete the rows = [...] generation block and replace spark.createDataFrame(rows, schema) with a single spark.read.parquet() call — the rest of the script is untouched.
The first aggregation answers: "Which pages get the most clicks, and how long do shoppers spend on each?" This is the most common BI question for any e-commerce funnel.
The key functions used here:
- groupBy("page") — groups all rows with the same page URL together into a partition. With
spark.sql.shuffle.partitions=8, Spark hashes page values into 8 buckets. - count("*") — counts all rows per group (all clicks, regardless of nulls).
- avg("time_on_page_s") — computes the mean session duration per page. The
round(..., 1)wrapper trims to one decimal. - orderBy(col("clicks").desc()) — sorts the 6-row result by click volume descending.
The second aggregation answers: "How many clicks did each combination of city and device type generate?" Funmi uses this to decide how to split the mobile app budget across regions.
This is a compound groupBy — Spark groups by two keys simultaneously. The result is a 15-row matrix: 5 cities × 3 device types. The shuffle step is slightly cheaper than step 1 because count("*") is simpler than avg (no running sum needed — just an increment per row).
Step 1 computes count + avg (two aggregations) and then sorts. Step 2 computes only count and uses a compound key. The two-key groupBy produces a slightly larger intermediate result (15 groups vs 6), but the simpler aggregation function and absence of round() makes it faster overall — 0.44s vs 1.96s in our verified run.
Practical application: if Lagos/mobile drives 40% of /checkout traffic but only 20% of desktop traffic, NaijaCart's mobile engineering team gets extra headcount. The city × device matrix makes this split visible at a glance.
The third aggregation answers: "How many distinct shoppers visited NaijaCart on each day in January 2024?" Chidi tracks this metric daily as the primary growth indicator.
date_format — extracting a date string from a timestamp
date_format("event_ts", "yyyy-MM-dd") takes the parsed TimestampType column and returns a StringType in ISO date format — for example, "2024-01-15". The resulting string column is used as the groupBy key, so all events on the same calendar day end up in the same group regardless of their hour or minute.
countDistinct — the most expensive operation in this pipeline
countDistinct("user_id") counts the number of unique values in a column within each group. This is fundamentally more expensive than count("*") because Spark must:
- Hash every
user_idvalue and assign it to a partition (shuffle). - Within each partition, maintain a hash set of seen user IDs (memory).
- Merge partial hash sets from each executor into a final count (reduce).
Under the hood for very large datasets, Spark can use HyperLogLog (an approximation algorithm with configurable error bounds) when you call approx_count_distinct instead. For NaijaCart's 50,000 unique users across 28 days, the exact count is fast enough — 1.17s. At 500M rows with millions of distinct users, you'd switch to approx_count_distinct with a relativeSD of 0.01 (1% error tolerance).
HyperLogLog estimates cardinality by hashing each element and tracking the maximum number of leading zeros observed — more leading zeros in the hash space means a larger set. It uses a fixed amount of memory (a few KB) regardless of dataset size, and returns an estimate within a configurable error rate (typically 1–2%).
🚀 Full pipeline — verified output
Here is the complete rollup.py script. Copy it into your naijacart-clickstream/ directory and run it with python3 rollup.py. On a modern laptop, the full run takes about 10–12 seconds including Spark JVM startup (~6s) and data processing (~4s).
Step timing explained
Total query time is under 4 seconds for 1 million rows across 3 aggregations. The bulk of your wall-clock time (roughly 6 seconds) is Spark's JVM startup — a fixed overhead that becomes negligible at 100M rows.
📈 Scaling to 100M rows
On a 4-node Spark cluster (32 cores, 128 GB total), the same rollup.py processes 100 million rows in approximately 45 seconds for the page rollup and 3 minutes for countDistinct. The only line that changes is the data source:
Replace: df = spark.createDataFrame(rows, schema).withColumn(...)
With: df = spark.read.parquet('s3a://naijacart/events/').withColumn("event_ts", to_timestamp(col("event_time")))
Everything else — the groupBy calls, agg functions, column names, orderBy — is untouched. This is the core value proposition of PySpark: write locally at 1M, ship to cluster at 100M.
What changes between local dev and production cluster
| Setting | Local dev (this lesson) | Production cluster |
|---|---|---|
| Data source | createDataFrame(rows, schema) |
spark.read.parquet("s3a://naijacart/events/") |
| Master | local[*] (all laptop cores) |
yarn or spark://master:7077 |
| Shuffle partitions | 8 (matches 4-core laptop) | 200–400 (matches 32-core cluster) |
| Row count N | 1,000,000 | 100,000,000+ (read from Parquet) |
| AQE | Enabled (same config) | Enabled — auto-coalesces shuffle partitions |
| Output | .show() to terminal | .write.parquet() to S3 / write to Cassandra |
🎯 What to build next — Tunde's extension tasks
Add a conversion_funnel aggregation: count how many distinct users visited /home vs /checkout vs /confirm. Then compute the conversion rate as a percentage. Hint: use df.filter(col("page") == "/home") to isolate each stage, then countDistinct("user_id"), then divide. Display the funnel as a three-row DataFrame with columns stage, unique_users, and conversion_pct.
Instead of .show(), write page_rollup to a local Parquet directory partitioned by city. You'll need to join the page rollup back to the city information first (the page rollup doesn't have a city column — you'll need to aggregate differently or write a city-aware version). Then run: page_rollup.write.mode("overwrite").partitionBy("city").parquet("output/page_rollup"). Inspect the directory structure and explain why partitioning by city speeds up queries that filter on city.
Extract the daily_users logic into a function compute_daily_users(df) in rollup.py. Then in test_rollup.py, create a small synthetic DataFrame with known data (e.g., 3 users, 2 days), call compute_daily_users(), and use chispa.assert_approx_df_equality() to verify the unique user counts are exactly right. Refer to Lesson 12 for the chispa pattern.
🗺️ Where this pipeline lives in the real stack
What you built today is the batch layer of NaijaCart's data architecture. The nightly Airflow DAG runs rollup.py on a schedule (23:59 WAT), reads the previous day's events from S3 Parquet, writes three rollup tables to Cassandra, and sends a Slack alert to Chidi when done.
| Layer | Latency | Tool | When you build it |
|---|---|---|---|
| Batch (this lesson) | ~1 hour behind real-time | PySpark + S3 Parquet | Arc B (now) |
| Near-real-time | < 1 minute | Spark Structured Streaming + Kafka | Arc C (next) |
| Real-time serving | < 5 ms read | Cassandra (pre-aggregated) | Arc E |
In Arc C (starting next lesson), you'll build the streaming version of this same pipeline using Spark Structured Streaming + Kafka. Instead of reading a day's worth of Parquet files, the pipeline reads from a Kafka topic and emits rolling window aggregations every 30 seconds — giving NaijaCart near-real-time visibility into clickstream trends without waiting for the nightly batch job.
You've now written every core PySpark pattern in Arc B: DataFrames, schemas, transformations, joins, aggregations, window functions, and unit tests. Arc C takes those same skills and applies them to unbounded streams. The mental model shift: instead of a DataFrame with a fixed number of rows, you have a streaming DataFrame where new rows arrive continuously from Kafka. The aggregation API (groupBy, agg, count) is the same — only the trigger and output mode change.
⚠️ Common mistakes
countDistinct is exact but expensive — it requires a full shuffle and hash deduplication across all partitions. At more than 10M rows with millions of distinct values, switch to approx_count_distinct("user_id", rsd=0.01). This uses HyperLogLog and runs 5–10x faster with only 1% error — acceptable for daily active user dashboards..write.partitionBy("date").parquet("output/daily_users"). Then each day's job only reads one partition — O(1) instead of O(days). At 365 days of history, this is the difference between a 1-second and a 6-minute read.🧠 Quiz — 3 questions
countDistinct more expensive than count("*") in a PySpark aggregation?date_format("event_ts", "yyyy-MM-dd") return for a timestamp of 2024-01-15 14:32:00?Now run the pipeline and try Tunde's extension challenges.
🎤 Interview Q&A
There are three main strategies, applied in order of impact:
1. Switch to approx_count_distinct: approx_count_distinct("user_id", rsd=0.01) uses HyperLogLog with a 1% relative standard deviation. For daily active user dashboards, 1% error is acceptable and the function runs 5–10x faster because it avoids the full hash-based shuffle. You only use exact countDistinct when you genuinely need a bit-perfect count — billing, compliance reporting, or A/B test statistical analysis.
2. Pre-aggregate in earlier stages: If the pipeline reads from Kafka or Parquet files that are already partitioned by day, you can compute a per-partition distinct-user sketch using approx_count_distinct in a map stage, then merge sketches in the reduce stage. This is the "sketch merge" pattern used by Meta's Presto and Databricks internally.
3. Increase shuffle.partitions: For a 500M-row dataset, the default 200 shuffle partitions means each partition holds 2.5M rows. Increasing to 800–1000 partitions reduces per-partition memory pressure, which reduces spill to disk. Combine this with AQE's spark.sql.adaptive.coalescePartitions.enabled=true so Spark auto-merges small post-shuffle partitions at the end.
When you call df.groupBy("date").agg(countDistinct("user_id")) and trigger an action (like .show()), Spark generates a physical plan with these stages:
Stage 1 — Partial aggregation (map-side): Each executor reads its partition of the DataFrame and builds a local hash set of user_id values per date key. For countDistinct, this is an Expand operator that emits two rows per input row — one for the groupBy key, one for the distinct column. This is the "expand" trick that makes countDistinct work in a distributed setting.
Shuffle: Spark shuffles rows so all rows with the same date land on the same executor. Hash-partitioned by the groupBy key. This is the expensive network transfer step.
Stage 2 — Final aggregation (reduce-side): Each executor receives all rows for its assigned date values and computes the final distinct count by deduplicating the user_id hash sets. The result is one row per date with the count. You can inspect this by running daily_users.explain(True) — look for HashAggregate, Exchange hashpartitioning, and Expand operators in the plan.
AQE (spark.sql.adaptive.enabled=true, default in Spark 3.2+) rewrites the physical plan at runtime based on actual shuffle statistics. For this clickstream pipeline, AQE provides three specific benefits:
Coalescing small shuffle partitions: After the groupBy shuffle, some partitions might be tiny (e.g., if a city has very few clicks). AQE detects these small partitions and merges them before the final aggregation stage, reducing the number of tasks from 8 to as few as 2–3. This avoids the overhead of scheduling many near-empty tasks.
Skew join optimisation: Not directly applicable here (no join in this pipeline), but if you extended the pipeline to join the rollup against a product catalogue, AQE would detect if one key (e.g., "/home") has far more rows than expected and split it into sub-partitions to avoid a single slow task.
Dynamic broadcast hash join: Again, not used here, but relevant when you add enrichment joins. If one side of a join turns out to be small enough at runtime (under spark.sql.autoBroadcastJoinThreshold, default 10 MB), AQE converts a sort-merge join to a broadcast join automatically — even if the query planner estimated both sides were large before execution began.
✅ Key takeaways
- A production-grade PySpark clickstream pipeline has three layers: data ingestion (createDataFrame locally, spark.read.parquet in production), transformation (schema parsing, timestamp extraction), and aggregation (groupBy + agg).
countDistinctis fundamentally more expensive thancount("*")because it requires a hash-based shuffle and deduplication step across all partitions. At scale (>10M rows with high cardinality), switch toapprox_count_distinctwith HyperLogLog.date_format("event_ts", "yyyy-MM-dd")returns a StringType column — not a DateType. Use it as a groupBy key to aggregate events by calendar day. Always parse the raw timestamp string first withto_timestamp().- Scaling from 1M to 100M rows requires changing exactly one line — the data source. The groupBy calls, schema, and agg functions are architecturally identical. This is the core design principle behind PySpark's DataFrame API.
- The batch pipeline you built today is Arc B's capstone. In Arc C, you'll build the streaming equivalent using Spark Structured Streaming + Kafka — the same groupBy API, applied to a continuously growing stream of events with near-real-time (<1 minute) latency.
Build pipelines that handle 100 million rows
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →