Verifying the same 106.15 total

Description

This is the whole module's central lesson — the equivalent, for this guide, of what "assembling Kiosko's first real star" lesson was for data-modeling-for-analytics-guide. It builds nothing new: it takes lesson 5's already-grouped fact_orders_df and compares it, with a real assert, against the exact numbers you already calculated with dict, DuckDB, Polars, and SQL in the three previous guides. If this lesson passes, you have evidence — not a hunch — that Spark reproduced the correct transformation.

Connection to the module. This lesson closes the arc this module's lesson 1 opened: "the same balance sheet, calculated by four different accountants." Lessons 2 through 5 built the fourth balance — with Spark; this lesson compares it, figure by figure, against the previous three.

An analogy: the fourth accountant turns in their balance

Pick back up with lesson 1's four accountants. The first three already turned in their monthly balance, each separately, and all three matched: 106.15. Now the fourth — the one who learned the new software, Spark — turns in theirs. The supervisor doesn't accept it just because "it looks reasonable" or because the fourth accountant says they're confident — they put it, number by number, next to the other three. If it matches on every figure, the balance gets approved, and you also have evidence the fourth accountant learned the new software correctly. If it doesn't match, you have to find the error before accepting anything — and, given the first three already agreed with each other, the most likely place for the error is the most recent work, not the previous three.

Worked example: the comparison with assert

Step 1 — Pick back up the full fact_orders_df, from lessons 2 through 5

# verify_same_total.py
from pyspark.sql import SparkSession
from pyspark.sql import functions as F
from pyspark.sql.functions import col
from pyspark.sql.types import (
    StructType, StructField, StringType, IntegerType, DoubleType, TimestampType,
)

spark = SparkSession.builder.appName("kiosko-spark").master("local[*]").getOrCreate()

orders_schema = StructType([
    StructField("order_id", StringType(), False),
    StructField("store_id", StringType(), False),
    StructField("product_id", StringType(), False),
    StructField("quantity", IntegerType(), False),
    StructField("unit_price", DoubleType(), False),
    StructField("order_ts", TimestampType(), False),
])
dim_store_schema = StructType([
    StructField("store_id", StringType(), False),
    StructField("store_name", StringType(), False),
    StructField("city", StringType(), False),
])
dim_product_schema = StructType([
    StructField("product_id", StringType(), False),
    StructField("product_name", StringType(), False),
    StructField("category", StringType(), False),
    StructField("unit_cost", DoubleType(), False),
])

orders_df = spark.read.csv("orders_2026-08-*.csv", schema=orders_schema, header=True, enforceSchema=False)
dim_store_df = spark.read.csv("dim_store.csv", schema=dim_store_schema, header=True, enforceSchema=False)
dim_product_df = spark.read.csv("dim_product.csv", schema=dim_product_schema, header=True, enforceSchema=False)

fact_orders_df = (
    orders_df
    .join(dim_store_df, "store_id")
    .join(dim_product_df, "product_id")
    .withColumn("revenue", col("quantity") * col("unit_price"))
)

Step 2 — The known numbers, from the three previous guides

# These numbers are NOT calculated here -- they are the already-verified
# results from foundations (dict), python-for-data-engineering (DuckDB and
# Polars), and data-modeling (SQL over the full star schema).
EXPECTED_TOTAL = 106.15
EXPECTED_BY_STORE = {"S01": 38.3, "S02": 38.8, "S03": 29.05}
EXPECTED_BY_PRODUCT = {"P001": 33.55, "P002": 21.6, "P003": 10.5, "P004": 40.5}

Step 3 — Calculate with Spark, and compare with assert

spark_total = fact_orders_df.agg(F.round(F.sum("revenue"), 2).alias("total")).collect()[0]["total"]
print(f"spark_total = {spark_total}")
assert spark_total == EXPECTED_TOTAL, f"Total does not match: {spark_total} != {EXPECTED_TOTAL}"
print(f"Verification: spark_total == {EXPECTED_TOTAL} (dict/DuckDB/Polars/SQL) -> OK\n")

spark_by_store = {
    r["store_id"]: r["total_revenue"]
    for r in (
        fact_orders_df.groupBy("store_id")
        .agg(F.round(F.sum("revenue"), 2).alias("total_revenue"))
        .orderBy("store_id")
        .collect()
    )
}
print(f"spark_by_store = {spark_by_store}")
assert spark_by_store == EXPECTED_BY_STORE, f"Breakdown by store does not match: {spark_by_store}"
print(f"Verification: spark_by_store == {EXPECTED_BY_STORE} -> OK\n")

spark_by_product = {
    r["product_id"]: r["total_revenue"]
    for r in (
        fact_orders_df.groupBy("product_id")
        .agg(F.round(F.sum("revenue"), 2).alias("total_revenue"))
        .orderBy("product_id")
        .collect()
    )
}
print(f"spark_by_product = {spark_by_product}")
assert spark_by_product == EXPECTED_BY_PRODUCT, f"Breakdown by product does not match: {spark_by_product}"
print(f"Verification: spark_by_product == {EXPECTED_BY_PRODUCT} -> OK\n")

spark.stop()

What to expect. Running python3 verify_same_total.py, the output is exactly this (executed in this run):

spark_total = 106.15
Verification: spark_total == 106.15 (dict/DuckDB/Polars/SQL) -> OK

spark_by_store = {'S01': 38.3, 'S02': 38.8, 'S03': 29.05}
Verification: spark_by_store == {'S01': 38.3, 'S02': 38.8, 'S03': 29.05} -> OK

spark_by_product = {'P001': 33.55, 'P002': 21.6, 'P003': 10.5, 'P004': 40.5}
Verification: spark_by_product == {'P001': 33.55, 'P002': 21.6, 'P003': 10.5, 'P004': 40.5} -> OK

Three asserts, three OKs — the total, the breakdown by store, and the breakdown by product, all three exactly matching the numbers you already know from dict, DuckDB, Polars, and SQL. This is this module's central proof: Kiosko's transformation — revenue = quantity * unit_price, aggregated by store and by product — produces the same result no matter which of the five engines runs it.

Diagram: four engines, one same result

flowchart TB
    A["dict + for\n(foundations)"] --> R["106.15\nS01=38.3, S02=38.8, S03=29.05"]
    B["SQL over DuckDB\n(python-for-data-engineering)"] --> R
    C["Polars expressions\n(python-for-data-engineering)"] --> R
    D["SQL over star schema\n(data-modeling)"] --> R
    E["Spark DataFrame API\n(this guide)"] --> R

    style R fill:#9f6,stroke:#333,stroke-width:3px

Going deeper: what you'd investigate if the assert failed

It's worth thinking through, even though it isn't going to happen in this lesson, what you'd do if one of the three asserts failed. The answer isn't "trust the new number because it's the most recent" — it's exactly the opposite. With three independent engines already agreeing with each other (dict, DuckDB/Polars, SQL over the star schema), the odds that all three share the same bug are far lower than the odds that the newest engine — the one you just learned, with syntax different from the other three — has a translation error. The most likely suspects, in order: a mix-up between unit_price and unit_cost (lesson 4's common mistake), a badly written JOIN condition that lost or duplicated rows (lesson 3's common mistake), or rounding applied at the wrong moment (lesson 5's common mistake).

This way of thinking — "if something doesn't match, suspect the newest code first, not the accumulated evidence" — is a real debugging skill, not Spark-specific. Any time you migrate a pipeline from one engine to another in real work, you're going to face exactly this situation: a new number that doesn't match the historical one, and the question of whether the new engine has a bug or the historical one had one nobody had caught. Having, as in this lesson, a set of already-verified, independent values to compare against is what turns that question into something you can answer with evidence, not intuition.

Common mistakes

Comparing visually, instead of with assert. What happens: someone runs lesson 5's .show(), looks at the numbers on screen, and concludes "looks right, it matches" without writing any real assert. Why it happens: when the expected number is short and memorable (106.15), formalizing the comparison with code seems unnecessary. How to spot it: if your "verification" consists of reading a number with your eyes and mentally comparing it against another one you remember, you have no check you can automatically rerun the next time you change something in the pipeline. How to fix it: an explicit assert, like this lesson's, doesn't just confirm the result once — it stays as code you can rerun every time you modify any piece of the pipeline, without relying on someone eyeballing the number each time.

Rounding the expected numbers (EXPECTED_TOTAL, etc.) so the assert "passes for sure." What happens: someone, seeing the assert fail over a tiny difference (for example, comparing an unrounded 106.14999999999999 against 106.15), decides to loosen the comparison with a wide tolerance (abs(spark_total - EXPECTED_TOTAL) < 1.0) instead of fixing the real cause. Why it happens: a comparison that fails due to a precision issue sometimes feels like a problem with the assert itself, not with the code producing the number. How to spot it: if your tolerance is much larger than what the floating-point phenomenon actually requires (which is, typically, on the order of 10⁻¹⁵, not whole units), you're hiding real bugs behind an overly generous tolerance. How to fix it: the correct fix — already applied in lessons 4 and 5 — is to round with F.round() in the calculation, not loosen the comparison afterward; with F.round(..., 2) applied correctly, this lesson's exact-equality comparison (==) works with no special tolerance at all.

Verifying only the total, without verifying the breakdown. What happens: someone runs only the first assert (spark_total == EXPECTED_TOTAL) and considers the verification done, without also comparing the breakdown by store or by product. Why it happens: the total is the most memorable number, and it seems like checking "the big number" already covers everything. How to spot it: two different bugs — for example, a row that shifted from S01 to S02 because of a badly written JOIN — could cancel out exactly in the aggregated total and still give 106.15, while the breakdown by store would reveal the bug immediately (S01 short, S02 over). How to fix it: always verify at more than one level of granularity, as this lesson does with three different levels (total, by store, by product) — the more independent angles that agree, the more real confidence you have in the result.

Exercises

Exercise 1 — Add a fourth check: revenue by category. Using lesson 5's exercise 2 result, add a fourth assert to this script comparing the breakdown by category ({"beverages": 44.05, "electronics": 40.5, "snacks": 21.6}) against the expected value.

EXPECTED_BY_CATEGORY = {"beverages": 44.05, "electronics": 40.5, "snacks": 21.6}

spark_by_category = {
    r["category"]: r["total_revenue"]
    for r in (
        fact_orders_df.groupBy("category")
        .agg(F.round(F.sum("revenue"), 2).alias("total_revenue"))
        .orderBy("category")
        .collect()
    )
}
print(f"spark_by_category = {spark_by_category}")
assert spark_by_category == EXPECTED_BY_CATEGORY
print(f"Verification: spark_by_category == {EXPECTED_BY_CATEGORY} -> OK")

spark_by_category = {'beverages': 44.05, 'electronics': 40.5, 'snacks': 21.6}
Verification: spark_by_category == {'beverages': 44.05, 'electronics': 40.5, 'snacks': 21.6} -> OK

Exercise 2 — Break the pipeline on purpose, and confirm the assert catches it. Intentionally change the revenue formula to col("quantity") * col("unit_cost") (lesson 4's common mistake), and confirm the total's assert fails, with the error message you wrote yourself.

broken_fact_orders_df = (
    orders_df
    .join(dim_store_df, "store_id")
    .join(dim_product_df, "product_id")
    .withColumn("revenue", col("quantity") * col("unit_cost"))  # bug on purpose
)
broken_total = broken_fact_orders_df.agg(F.round(F.sum("revenue"), 2).alias("total")).collect()[0]["total"]
print(f"broken_total = {broken_total}")
try:
    assert broken_total == EXPECTED_TOTAL, f"Total does not match: {broken_total} != {EXPECTED_TOTAL}"
except AssertionError as e:
    print(f"AssertionError caught (as expected): {e}")

broken_total = 59.0
AssertionError caught (as expected): Total does not match: 59.0 != 106.15

Exercise 3 — Explain, without code, why this lesson trusts three engines agreeing more than a single one. In 2-3 sentences, explain the logic behind "if three independent engines agree and one doesn't, suspect the one that doesn't first" — why would that logic be different if you only had one reference engine instead of three?

Summary and next step

This lesson closed the module's central promise: fact_orders_df, calculated end to end with Spark's DataFrame API, produces exactly the same 106.15 in total revenue, the same S01=38.3, S02=38.8, S03=29.05 by store, and the same P001=33.55, P002=21.6, P003=10.5, P004=40.5 by product you already knew from dict, DuckDB, Polars, and SQL over a full star schema — verified with assert, not by eye.

Before moving on you should be able to: explain why comparing with assert beats comparing visually; explain why this lesson would suspect Spark's code first, not the three previous engines, if some number didn't match; and recite from memory this lesson's three verification levels (total, by store, by product).

Lesson 7 takes this same, now-verified fact_orders_df, and writes it for the first time as a real Parquet file on disk — this entire guide's first persistent artifact.

Resources

• python-for-data-engineering-guide DESIGN doc — the source of the DuckDB and Polars results (106.15, S01=38.3, S02=38.8, S03=29.05) this lesson verifies Spark's result against. src/guides/python-for-data-engineering-guide/DISENO.md
• data-modeling-for-analytics-guide DESIGN doc — the source of the SQL-over-full-star-schema result, including exercise 1's breakdown by category. src/guides/data-modeling-for-analytics-guide/DISENO.md
• data-engineering-foundations-guide DESIGN doc — the original source of the pure-dict result, the first of the four engines. src/guides/data-engineering-foundations-guide/DISENO.md
• Apache Spark — pyspark.sql.functions (reference for F.round and F.sum, used throughout this lesson). spark.apache.org/docs/latest/api/python/reference/pyspark.sql/functions.html.