Module introduction: rebuilding `fact_orders` with the DataFrame API

Why this module exists

Module 1 installed Spark, opened Kiosko's first SparkSession, and read the seven orders files with an explicit schema — orders_df.count() == 40, the same number you already knew. Module 2 opened the box module 1 deliberately left closed: driver versus executors, the DataFrame API as the main interface, and lazy evaluation — transformations that cost nothing until an action triggers them. Neither module touched dim_store or dim_product yet, or calculated a single cent of revenue. That was deliberate: install and confirm first, understand the machinery next, and only now — with both pieces already built — do the real work.

This module 3 does that real work, and it is, in a precise sense, the most important module in this entire guide for your confidence in Spark: you're going to rebuild fact_orders — the same fact, the same grain, the same formula — for the fourth time. data-engineering-foundations-guide built it with dict and a for loop. python-for-data-engineering-guide rebuilt it two more times, with SQL over DuckDB and with Polars expressions. data-modeling-for-analytics-guide rebuilt it a third time, with three full JOINs against a star schema (dim_store, dim_product, dim_date). All three matched, exactly, down to the second decimal place: 106.15 in total revenue for the week, S01=38.3, S02=38.8, S03=29.05. This lesson uses the DataFrame API's .join(), .withColumn() to calculate revenue = quantity * unit_price, and .groupBy().agg() to sum it by store and by product — and the number that comes out, verified with executed evidence in lesson 6, has to be exactly the same. Not a similar number. The same one, down to the second decimal place.

Connection to the module. This module doesn't ask again why fact_orders has the grain it has, or why the model is a star schema instead of some other shape — data-modeling-for-analytics-guide already resolved that question, and this module inherits that answer without questioning it. What this module asks is different: what, exactly, changes when you run that same transformation with Spark's DataFrame API instead of dict, SQL, or Polars? The answer, verified with evidence in lesson 6, is: nothing about the result. Only the engine.

An analogy: the same balance sheet, calculated by four different accountants

Recall the lone accountant from module 1 — the one who keeps Kiosko's books at their own desk, with their own calculator. Now imagine that same monthly balance — the same transactions, the same week of sales — gets calculated, separately, by four different accountants: one with a pocket calculator and a notebook (dict, in foundations), one with a spreadsheet full of formulas (DuckDB and Polars, in python-for-data-engineering), one with a full accounting system that cross-references three separate ledgers — sales, branches, inventory (SQL over a star schema, in data-modeling), and now a fourth, with new software none of the previous three used (Spark, here).

If the four balances don't match, something's wrong — and it's most likely the error is in the fourth accountant, the one who just learned the new software, not in the original arithmetic. But if all four match, exactly, down to the last cent, you have something more valuable than any one of the four balances on its own: proof that the balance is correct no matter which calculator was used. That's, precisely, this module's point. You don't learn Spark's .join() and .groupBy() to get a new number — you learn them to get, with evidence, the same number as always, confirming that the transformation — not the tool — is what matters.

Worked example: this module's map, before calculating anything

Before touching orders_df, dim_store, or dim_product, it's worth seeing the full path this module covers — the same map pattern modules 1 and 2 used.

# rebuild_map.py
REBUILD_MAP = [
    (2, "Reading orders, dim_store, and dim_product with an explicit schema",
        "Three different StructType -- one per table -- the three raw pieces before joining anything"),
    (3, "Joining facts and dimensions with the DataFrame API",
        "Chained .join(): orders + dim_store + dim_product, verified with no rows lost or duplicated"),
    (4, "Computing revenue, the same way four engines later",
        ".withColumn('revenue', col('quantity') * col('unit_price')) -- the same formula as always"),
    (5, "Grouping by store and by product",
        ".groupBy().agg(F.sum(...)) -- the same breakdown you've already seen three times"),
    (6, "Verifying the same 106.15 total",
        "Compared, with assert, against dict/DuckDB/Polars/SQL -- the four previous guides"),
    (7, "Writing fact_orders as Parquet",
        "The file the lakehouse guide is going to reuse later in the ecosystem"),
    (8, "Project: Kiosko's fact_orders in Spark",
        "The previous six lessons, integrated into a single script verified end to end"),
]

print("=== Rebuilding fact_orders, before calculating anything ===\n")
for number, title, detail in REBUILD_MAP:
    print(f"L{number}. {title}")
    print(f"     {detail}\n")

print("This module's goal: the same 106.15 as always, calculated a fourth time, with a different engine.")

What to expect. Running python3 rebuild_map.py, the output is exactly this:

=== Rebuilding fact_orders, before calculating anything ===

L2. Reading orders, dim_store, and dim_product with an explicit schema
     Three different StructType -- one per table -- the three raw pieces before joining anything

L3. Joining facts and dimensions with the DataFrame API
     Chained .join(): orders + dim_store + dim_product, verified with no rows lost or duplicated

L4. Computing revenue, the same way four engines later
     .withColumn('revenue', col('quantity') * col('unit_price')) -- the same formula as always

L5. Grouping by store and by product
     .groupBy().agg(F.sum(...)) -- the same breakdown you've already seen three times

L6. Verifying the same 106.15 total
     Compared, with assert, against dict/DuckDB/Polars/SQL -- the four previous guides

L7. Writing fact_orders as Parquet
     The file the lakehouse guide is going to reuse later in the ecosystem

L8. Project: Kiosko's fact_orders in Spark
     The previous six lessons, integrated into a single script verified end to end

This module's goal: the same 106.15 as always, calculated a fourth time, with a different engine.

Notice something that does change compared to modules 1 and 2's maps: here a business number — 106.15 — does show up, and it shows up on purpose, as the whole module's explicit goal. Modules 1 and 2 avoided any revenue figure because they didn't touch fact_orders yet; this module exists, precisely, to reach that figure by a fourth path.

Diagram: where you were, where you're headed

flowchart LR
    subgraph Anteriores["Four rebuilds of fact_orders already done"]
        A["foundations: dict\n106.15"]
        B["python-for-data-engineering:\nDuckDB + Polars, 106.15"]
        C["data-modeling: SQL over\nstar schema, 106.15"]
    end

    subgraph M1M2["Modules 1-2 of this guide (already done)"]
        D["Spark installed,\norders_df read, count=40"]
        E["Execution model:\ndriver/executors, lazy eval"]
    end

    subgraph M3["This module (3 of 8)"]
        F["L2: orders + dim_store +\ndim_product read"]
        G["L3: chained join,\nthe DataFrame API"]
        H["L4-L5: revenue calculated,\ngrouped by store and product"]
        I["L6-L7: verified == 106.15,\nwritten as fact_orders.parquet"]
    end

    A --> D
    B --> D
    C --> D
    D --> E --> F --> G --> H --> I
    I -.->|"fourth confirmation\nof the same number"| A

This module's map

Lesson    What it builds
────────  ──────────────────────────────────────────────────────────────
L1        (this one) The map: fact_orders's fourth rebuild, before calculating it
L2        orders, dim_store, dim_product -- three StructType, three reads
L3        The chained join -- orders + dim_store + dim_product, no rows lost
L4        revenue = quantity * unit_price, with .withColumn -- four engines later
L5        groupBy().agg() -- by store and by product
L6        The central verification: the same 106.15, with evidence and assert
L7        fact_orders.parquet -- the file lakehouse-and-iceberg is going to reuse
L8        Project: Kiosko's fact_orders in Spark, end to end

Lessons 2 through 5 are construction: read, join, calculate, group — in that order, each building on the previous one's result. Lesson 6 is this whole module's central point: it builds nothing new, it only compares, with assert, lesson 5's result against numbers you already know from three previous guides. Lesson 7 closes with this guide's first persistent artifact — a real Parquet file, on disk — and lesson 8 pulls the seven pieces together into a project verified end to end.

Going deeper: why "the same number" matters more than "a number"

It's tempting to treat this module's lesson 6 as a formality — "I already know it's going to give 106.15, why verify it again?" It's worth resisting that temptation, for a concrete reason: cross-engine verification is, in a real data team's day-to-day work, exactly the kind of evidence that separates a trustworthy engine migration from one that silently introduces a bug. If someone migrates a pipeline from SQL to Spark and the number changes — even by a single cent — there are two possibilities: the migration has a bug, or the original pipeline had one nobody had caught. Neither gets discovered without comparing the two results with evidence, line by line if needed. This module practices that discipline on a case where you already know the correct answer — so, when the exercise comes out right, you know the verification process works, and you can trust it the next time you compare two numbers you don't already know.

There's a second reason, more specific to Spark: the DataFrame API is designed, on purpose, so that .join(), .withColumn(), and .groupBy() feel almost identical to their SQL or pandas equivalents — that familiarity is a design decision, not an accident. But "feels similar" isn't the same as "does exactly the same thing in every detail," and this module, in lesson 3, shows at least one .join() behavior that surprises anyone coming from pure SQL: what happens to duplicate columns depending on the exact way you write the JOIN condition. Seeing that detail with executed evidence, instead of assuming Spark behaves like SQL in every respect, is part of what this module builds.

Common mistakes

Treating this module as "I already know how to do a JOIN, no need to reread it." What happens: someone with prior SQL or pandas experience assumes Spark's .join() is a direct translation of what they already know, and skips lessons 2 and 3 expecting everything to behave the same way. Why it happens: the DataFrame API's syntax closely resembles, on purpose, SQL and pandas. How to spot it: if you can't predict, without running code, what happens to the store_id column when you join orders_df with dim_store_df using an explicit condition (orders_df.store_id == dim_store_df.store_id) instead of the shorthand form ("store_id"), you're missing a real detail lesson 3 shows with evidence. How to fix it: lesson 3 isn't a generic introduction to JOIN — it's documentation for a specific Spark behavior, with a real error captured on screen, worth reading even if you know SQL cold.

Expecting this module to justify, again, why the model has this shape. What happens: someone looks to this module for an explanation of why fact_orders has the grain it has, or why dim_store and dim_product exist as separate tables instead of flat columns. Why it happens: it seems reasonable that the guide rebuilding the star schema would also explain why it's a star schema. How to spot it: if you finish lesson 3 wondering "but why is the grain one row per order line?", that question already has a full, evidence-backed answer in data-modeling-for-analytics-guide, not here. How to fix it: this module treats the model as settled and focuses on execution with a different engine; if the underlying question is still open for you, the right move is rereading that guide, not expecting this module to repeat it.

Confusing "the number matches" with "no need to keep verifying." What happens: someone runs lesson 6's assert, sees it pass, and assumes the verification work is done forever — that any future change to the Spark pipeline is going to keep producing the correct number without needing to check again. Why it happens: a passing check feels like a permanent guarantee, not a snapshot of one specific moment. How to spot it: if you change any piece of the pipeline — the schema, the revenue formula, the input data — and don't rerun lesson 6's verification, you're trusting a check that no longer corresponds to the current code. How to fix it: this lesson's discipline — comparing against a known result, with assert, not eyeballing it — is a tool worth reusing every time you change something, not a one-off in this module.

Exercises

Exercise 1 — Recite, from memory, this module's three central numbers. Without rereading this lesson, write down Kiosko's total weekly revenue and the breakdown by store this module is going to reproduce with Spark.

See solution

Total revenue: 106.15. By store: S01 = 38.3, S02 = 38.8, S03 = 29.05 (38.3 + 38.8 + 29.05 = 106.15). These are the same three numbers you already calculated, or saw calculated, with dict in foundations, with DuckDB and Polars in python-for-data-engineering, and with SQL over a star schema in data-modeling — lesson 6 of this module reproduces them a fourth time, with Spark.

Exercise 2 — Explain, without code, what this module does NOT do. In 2-3 sentences, explain why this module doesn't justify fact_orders's grain or the star schema's shape again, even though it rebuilds them completely.

See solution

That justification work — why the grain is one row per order line, why the model is a star schema and not some other shape, why dim_store and dim_product exist as separate tables — was already resolved, with its own evidence, by data-modeling-for-analytics-guide, and this guide inherits it as a decision already made, not an open question. This module asks something different and narrower: given that already-justified model, what changes when you run it with Spark's DataFrame API instead of SQL, dict, or Polars? The answer, verified in lesson 6, is that the result doesn't change — only the engine does.

Exercise 3 — Predict the behavior of a JOIN with duplicate column names. Without running anything yet, predict: if you join orders_df with dim_store_df using orders_df.join(dim_store_df, orders_df.store_id == dim_store_df.store_id) — an explicit condition, not the shorthand form "store_id" — how many columns named store_id would you expect to see in the result?

See solution

Two. With an explicit equality condition (==), Spark keeps both JOIN columns — the one from orders_df and the one from dim_store_df — as separate columns in the result, even though they share a name; it doesn't automatically merge them the way the shorthand form does (.join(dim_store_df, "store_id"), used throughout the rest of this module). Lesson 3 shows, with a real error captured on screen, what happens when you later try to reference store_id without saying which of the two columns you mean.

Summary and next step

This module rebuilds fact_orders for the fourth time in the Kiosko ecosystem — the same transformation, a new engine: orders joined with dim_store and dim_product using .join(), revenue calculated with .withColumn(), and the result grouped by store and by product with .groupBy().agg(). The central point isn't learning the syntax for its own sake — that's just the vehicle — it's verifying, with executed evidence and a real assert in lesson 6, that the result is exactly the same 106.15 you already know, with the same breakdown by store, calculated with dict, SQL, DuckDB, and Polars in the three previous guides.

Before moving on you should be able to: explain why this module doesn't justify the star schema's shape again; predict what happens to a JOIN's duplicate columns depending on how you write the condition; and recite from memory the three numbers — 106.15, 38.3, 38.8, 29.05 — this module is going to reproduce with Spark.

Lesson 2 starts at the beginning: reading the three tables — orders, dim_store, dim_product — each with its own explicit schema, before joining anything.

Resources

• Apache Spark — SQL Getting Started (the general read-and-transform pattern with spark.read and the DataFrame API this module uses end to end). spark.apache.org/docs/latest/sql-getting-started.html.
• data-modeling-for-analytics-guide DESIGN doc — the source of the star schema (fact_orders, dim_store, dim_product) this module rebuilds without justifying its shape again. src/guides/data-modeling-for-analytics-guide/DISENO.md
• python-for-data-engineering-guide DESIGN doc — the source of fact_orders's second and third rebuilds (DuckDB and Polars) this module's lesson 6 verifies the result against. src/guides/python-for-data-engineering-guide/DISENO.md
• This guide's DESIGN doc (spark-and-distributed-processing-guide/DISENO.md) — this module's full objective within the eight-module plan.