Rebuilding the star schema as Iceberg tables

Description

This lesson opens this module's single catalog and loads the three Kiosko tables that don't need to reproduce any evolution or time travel mechanism: kiosko.fact_orders (the same fixed week as always), kiosko.dim_store (with country populated since its first commit, module 4's final result), and kiosko.dim_date (a completely new table, August 2026's calendar). All three load with a single append() each, with no intermediate step — the mechanism of how you get to that final state was already taught, in depth, in modules 1 and 4; this lesson just applies it.

Connection to the module. This lesson answers lesson 2's brief's first requirement: "a single catalog, with the tables coexisting." It opens kiosko_warehouse/ and kiosko_catalog.db only once in this module — lessons 4, 5, and 6 are going to keep writing to this same catalog, without recreating a new one.

An analogy: the three foundations that no longer change shape

Of Kiosko's lakehouse's five tables, three are like a finished building's foundations: once poured, they don't change shape again while the building stands. fact_orders is a week that already passed's historical record — it's never going to have a 41st row. dim_store has three stores, with their country already derived — no new franchise enters this guide. dim_date is a calendar — August 2026 isn't going to have a 32nd day. The lakehouse's other two tables, dim_product and fact_orders_at_scale, do have a history to reconstruct — that's why they live in separate lessons (4 and 5). This lesson pours all three foundations, at once, with no additional scaffolding.

Worked example: fact_orders, dim_store, and dim_date in a single catalog

Step 1 — This module's single catalog

The same load_catalog() pattern you already used in every previous module — the difference this time is that this catalog is going to accumulate five tables, not one, across lessons 3 through 6:

# kiosko_star_tables.py -- module 8, lesson 3
import os
from datetime import date, datetime, timedelta

import pyarrow as pa
import pyarrow.compute as pc
from pyiceberg.catalog import load_catalog
from pyiceberg.schema import Schema
from pyiceberg.types import (
    BooleanType, DateType, DoubleType, IntegerType, NestedField, StringType, TimestampType,
)

from raw_orders import RAW_ORDERS

DIM_STORE_ROWS = [
    {"store_id": "S01", "store_name": "Kiosko Centro", "city": "Bogota", "country": "Colombia"},
    {"store_id": "S02", "store_name": "Kiosko Norte", "city": "Lima", "country": "Peru"},
    {"store_id": "S03", "store_name": "Kiosko Sur", "city": "Santiago", "country": "Chile"},
]
DAY_NAMES = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]

(raw_orders.py is exactly the same file from module 1, lesson 6 — Kiosko's forty fixed orders.)

Step 2 — The three schemas, declared explicitly

kiosko.dim_store carries country from its first NestedField — there's no update_schema().add_column() in this lesson. That mechanism — how a column gets added without rewriting existing files — was already demonstrated, step by step, in module 4; this lesson starts directly at the final state:

FACT_ORDERS_SCHEMA = Schema(
    NestedField(field_id=1, name="order_id", field_type=StringType(), required=True),
    NestedField(field_id=2, name="store_id", field_type=StringType(), required=True),
    NestedField(field_id=3, name="product_id", field_type=StringType(), required=True),
    NestedField(field_id=4, name="quantity", field_type=IntegerType(), required=True),
    NestedField(field_id=5, name="unit_price", field_type=DoubleType(), required=True),
    NestedField(field_id=6, name="revenue", field_type=DoubleType(), required=True),
    NestedField(field_id=7, name="order_ts", field_type=TimestampType(), required=True),
)
DIM_STORE_SCHEMA = Schema(
    NestedField(field_id=1, name="store_id", field_type=StringType(), required=True),
    NestedField(field_id=2, name="store_name", field_type=StringType(), required=True),
    NestedField(field_id=3, name="city", field_type=StringType(), required=True),
    NestedField(field_id=4, name="country", field_type=StringType(), required=True),
)
DIM_DATE_SCHEMA = Schema(
    NestedField(field_id=1, name="date_key", field_type=IntegerType(), required=True),
    NestedField(field_id=2, name="calendar_date", field_type=DateType(), required=True),
    NestedField(field_id=3, name="day_of_week", field_type=StringType(), required=True),
    NestedField(field_id=4, name="month", field_type=IntegerType(), required=True),
    NestedField(field_id=5, name="quarter", field_type=IntegerType(), required=True),
    NestedField(field_id=6, name="year", field_type=IntegerType(), required=True),
    NestedField(field_id=7, name="is_weekend", field_type=BooleanType(), required=True),
)

dim_date's schema is, deliberately, identical in columns to the dim_date data-modeling-for-analytics-guide (module 8) already built on DuckDB — date_key as a YYYYMMDD integer, calendar_date as a real date, day_of_week/month/quarter/year/is_weekend derived. Iceberg adds a type DuckDB didn't need to declare this explicitly: DateType(), the same type you're going to see mapped to pa.date32() on PyArrow's side.

Step 3 — The functions that build each pyarrow.Table

def fact_orders_pa_table() -> pa.Table:
    schema = pa.schema([
        pa.field("order_id", pa.string(), nullable=False),
        pa.field("store_id", pa.string(), nullable=False),
        pa.field("product_id", pa.string(), nullable=False),
        pa.field("quantity", pa.int32(), nullable=False),
        pa.field("unit_price", pa.float64(), nullable=False),
        pa.field("revenue", pa.float64(), nullable=False),
        pa.field("order_ts", pa.timestamp("us"), nullable=False),
    ])
    rows = [
        {
            "order_id": order_id, "store_id": store_id, "product_id": product_id,
            "quantity": quantity, "unit_price": unit_price,
            "revenue": round(quantity * unit_price, 10),
            "order_ts": datetime.fromisoformat(ts),
        }
        for order_id, store_id, product_id, quantity, unit_price, ts in RAW_ORDERS
    ]
    return pa.Table.from_pylist(rows, schema=schema)


def dim_store_pa_table() -> pa.Table:
    schema = pa.schema([
        pa.field("store_id", pa.string(), nullable=False),
        pa.field("store_name", pa.string(), nullable=False),
        pa.field("city", pa.string(), nullable=False),
        pa.field("country", pa.string(), nullable=False),
    ])
    return pa.Table.from_pylist(DIM_STORE_ROWS, schema=schema)


def dim_date_pa_table(start_date: str, end_date: str) -> pa.Table:
    schema = pa.schema([
        pa.field("date_key", pa.int32(), nullable=False),
        pa.field("calendar_date", pa.date32(), nullable=False),
        pa.field("day_of_week", pa.string(), nullable=False),
        pa.field("month", pa.int32(), nullable=False),
        pa.field("quarter", pa.int32(), nullable=False),
        pa.field("year", pa.int32(), nullable=False),
        pa.field("is_weekend", pa.bool_(), nullable=False),
    ])
    start, end, rows = date.fromisoformat(start_date), date.fromisoformat(end_date), []
    current = start
    while current <= end:
        weekday_index = current.weekday()
        rows.append({
            "date_key": int(current.strftime("%Y%m%d")), "calendar_date": current,
            "day_of_week": DAY_NAMES[weekday_index], "month": current.month,
            "quarter": (current.month - 1) // 3 + 1, "year": current.year,
            "is_weekend": weekday_index >= 5,
        })
        current += timedelta(days=1)
    return pa.Table.from_pylist(rows, schema=schema)

dim_date_pa_table() generates all of August 2026 — from 01 through 31 — not just the real order week. This is intentional: a real dimensional calendar covers the business's complete period, not just the dates with transactions — exactly the same decision data-modeling-for-analytics-guide made when it generated its own dim_date.

Step 4 — The catalog, the namespace, and the three loads

def main() -> None:
    print("=== Kiosko: the star -- fact_orders, dim_store (with country), dim_date ===\n")

    warehouse_path = os.path.abspath("kiosko_warehouse")
    catalog_db_path = os.path.abspath("kiosko_catalog.db")
    os.makedirs(warehouse_path, exist_ok=True)
    catalog = load_catalog(
        "kiosko", type="sql",
        uri=f"sqlite:///{catalog_db_path}", warehouse=f"file://{warehouse_path}",
    )
    catalog.create_namespace("kiosko")
    print(f"Step 1/4 -- catalog '{catalog.name}' and namespace 'kiosko' ready")

    fact_orders = catalog.create_table("kiosko.fact_orders", schema=FACT_ORDERS_SCHEMA)
    fact_orders.append(fact_orders_pa_table())
    fact_rows = fact_orders.scan().to_arrow()
    fact_revenue = round(sum(fact_rows.column("revenue").to_pylist()), 2)
    print(f"Step 2/4 -- kiosko.fact_orders loaded: {fact_rows.num_rows} rows, total revenue {fact_revenue}")

    dim_store = catalog.create_table("kiosko.dim_store", schema=DIM_STORE_SCHEMA)
    dim_store.append(dim_store_pa_table())
    store_rows = sorted(dim_store.scan().to_arrow().to_pylist(), key=lambda r: r["store_id"])
    print(f"Step 3/4 -- kiosko.dim_store loaded: {len(store_rows)} rows, with 'country' since its first commit "
          f"(without going through module 4's schema evolution -- that evolution was already demonstrated there)")
    for row in store_rows:
        print(f"           {row['store_id']}  {row['store_name']:<14} {row['city']:<10} country={row['country']}")

    dim_date = catalog.create_table("kiosko.dim_date", schema=DIM_DATE_SCHEMA)
    dim_date.append(dim_date_pa_table("2026-08-01", "2026-08-31"))
    date_rows = dim_date.scan().to_arrow()
    week_dates = pc.filter(
        date_rows, pc.and_(
            pc.greater_equal(date_rows.column("calendar_date"), pa.scalar(date(2026, 8, 3))),
            pc.less_equal(date_rows.column("calendar_date"), pa.scalar(date(2026, 8, 9))),
        ),
    )
    print(f"Step 4/4 -- kiosko.dim_date loaded: {date_rows.num_rows} rows (all of August 2026), "
          f"{week_dates.num_rows} of them cover Kiosko's real week (Aug 03-09)")

    print("\n=== Final verification ===\n")
    assert fact_rows.num_rows == 40
    assert fact_revenue == 106.15
    assert len(store_rows) == 3
    assert {r["store_id"]: r["country"] for r in store_rows} == {
        "S01": "Colombia", "S02": "Peru", "S03": "Chile",
    }
    assert date_rows.num_rows == 31
    assert week_dates.num_rows == 7

    print("All verifications passed:")
    print(f"  - kiosko.fact_orders: 40 rows, revenue={fact_revenue}")
    print("  - kiosko.dim_store: 3 rows, country populated since the first commit (Colombia/Peru/Chile)")
    print("  - kiosko.dim_date: 31 rows (August 2026), 7 cover the real order week")


if __name__ == "__main__":
    main()

What to expect (verified by running the real python3 kiosko_star_tables.py, in a new directory):

=== Kiosko: the star -- fact_orders, dim_store (with country), dim_date ===

Step 1/4 -- catalog 'kiosko' and namespace 'kiosko' ready
Step 2/4 -- kiosko.fact_orders loaded: 40 rows, total revenue 106.15
Step 3/4 -- kiosko.dim_store loaded: 3 rows, with 'country' since its first commit (without going through module 4's schema evolution -- that evolution was already demonstrated there)
           S01  Kiosko Centro  Bogota     country=Colombia
           S02  Kiosko Norte   Lima       country=Peru
           S03  Kiosko Sur     Santiago   country=Chile
Step 4/4 -- kiosko.dim_date loaded: 31 rows (all of August 2026), 7 of them cover Kiosko's real week (Aug 03-09)

=== Final verification ===

All verifications passed:
  - kiosko.fact_orders: 40 rows, revenue=106.15
  - kiosko.dim_store: 3 rows, country populated since the first commit (Colombia/Peru/Chile)
  - kiosko.dim_date: 31 rows (August 2026), 7 cover the real order week

Three tables, three append()s, three snapshots — one per table, no schema or partition evolution in this lesson. The kiosko_warehouse/kiosko/ directory ends up with three subfolders (fact_orders/, dim_store/, dim_date/), each with its own metadata → manifest list → manifest files → data files chain, exactly as module 2 mapped — the difference is that, for the first time in this guide, all three live under the same kiosko_catalog.db.

Going deeper: why dim_store doesn't repeat the schema evolution here

Someone who only saw module 4 might expect this lesson to repeat, step by step, add_column("country", ...) followed by an overwrite() populating it. It doesn't, and the reason is the same discipline you already saw in data-modeling-for-analytics-guide when its capstone rebuilt dim_product_scd directly with MERGE INTO, without repeating every individual run from that guide's module 4: a capstone rebuilds the verified final state, not the complete process that got there. This guide's module 4 already demonstrated, with its own asserts, that add_column() doesn't rewrite existing data files, that country gets correctly populated from city, and that a snapshot before the evolution keeps reading its own schema. Repeating that demonstration here wouldn't add any new evidence — it would just lengthen this module with no pedagogical gain. What is new in this lesson is something module 4, in isolation, couldn't show: dim_store with country already populated, coexisting in the same catalog as fact_orders and dim_date, ready for a real JOIN.

Common mistakes

Creating a new catalog for every lesson of this module, like modules 1 through 7 did. What happens: someone, out of habit, deletes kiosko_warehouse/ and kiosko_catalog.db before running lesson 4, expecting — like in every previous module — to start from scratch. Why it happens: each of the seven previous modules did expect you to reset the catalog in every closing project; that habit is hard to break. How to spot it: if lesson 4 fails with TableDoesNotExistError when trying catalog.load_table("kiosko.fact_orders"), you deleted the catalog this lesson just created. How to fix it: starting with this lesson, and through the end of this module (lessons 3 through 6), don't delete kiosko_warehouse/ or kiosko_catalog.db between lessons — it's the first time in this guide several consecutive lessons deliberately share the same catalog.

Loading dim_date only with the real order week (August 3-9), instead of the complete month. What happens: someone, to "save rows," generates dim_date only for the dates with real orders, instead of all of August 2026. Why it happens: it seems more efficient to only load what's going to be used in a JOIN. How to spot it: if your kiosko.dim_date has 7 rows instead of 31, and some future report needs a date range outside the order week (for example, "revenue by week for all of August," with empty weeks showing zero), your calendar isn't going to be able to answer that question. How to fix it: a dimensional dim_date covers the business's complete period, not just the dates with activity — it's exactly the same decision, with the same range, data-modeling-for-analytics-guide already made.

Exercises

Exercise 1 — Run the script yourself, from scratch. In a new directory, with raw_orders.py in the same place, run python3 kiosko_star_tables.py. Confirm you see the four steps complete and the final message with the three verifications.

See solution

If raw_orders.py is in the same directory and PyIceberg is installed, the output should exactly reproduce this lesson's structure: four numbered steps, followed by the final verification with 106.15 in revenue, the three correct countries, and 31/7 for dim_date. Don't delete this directory — lesson 4 continues exactly where this one ends.

Exercise 2 — Break an assert on purpose, and watch it fail. Temporarily change dim_date_pa_table()'s range from "2026-08-01"/"2026-08-31" to "2026-08-01"/"2026-08-15", run the script again, and observe which assert fails first. Then revert the change.

See solution

The first assert to fail is assert date_rows.num_rows == 31 — with the range trimmed to the first half of the month, dim_date would have 15 rows, not 31. assert week_dates.num_rows == 7 would still pass, because the real order week (August 03 through 09) is still fully inside the trimmed range — this exercise confirms the two asserts verify different things: one the calendar's total size, the other that the relevant business window is covered.

Exercise 3 — Explain, in your own words, why this lesson does NOT run update_schema().add_column("country", ...) again. In 2-3 sentences, justify why rebuilding dim_store's final state is a correct decision for a capstone, and not a shortcut that hides information.

See solution

This guide's module 4 already demonstrated, with its own asserts on data files before and after, that add_column("country", ...) doesn't rewrite any existing Parquet file — that evidence already exists and doesn't need repeating. A capstone that rebuilds the final state, instead of repeating every intermediate step from every module, follows the same pattern data-modeling-for-analytics-guide and dbt-analytics-engineering-guide already used: a closing module's goal is demonstrating the already-verified pieces coexist correctly, not re-proving, one by one, every individual guarantee that already has its own evidence in a dedicated module.

Summary and next step

In this lesson you opened this module's single catalog and loaded Kiosko's three tables that don't change state: kiosko.fact_orders (40 rows, 106.15), kiosko.dim_store (3 rows, country already populated), and kiosko.dim_date (31 rows, all of August 2026) — the first time in this guide three tables coexist in the same kiosko_catalog.db.

Before moving on you should be able to: name the three tables this lesson loaded and explain why none of the three needs to reproduce an evolution mechanism; and explain why this lesson, unlike previous modules' projects, doesn't delete the catalog between lessons.

Lesson 4 — this whole capstone's central piece — creates kiosko.dim_product with no history column at all, reproduces P002's real change, and joins the star's four tables to recover the correct margin (10.8) with pure time travel.

Resources

• PyIceberg — official documentation (quickstart), the load_catalog(), create_namespace(), create_table(), and append() flow this lesson integrates. py.iceberg.apache.org. In English.
• PyIceberg — API reference, types (DateType, BooleanType, IntegerType), the foundation for dim_date's schema. py.iceberg.apache.org/api. In English.
• data-modeling-for-analytics-guide DESIGN doc — source of dim_date's exact schema (date_key/calendar_date/day_of_week/month/quarter/year/is_weekend) this lesson rebuilds on Iceberg. src/guides/data-modeling-for-analytics-guide/DISENO.md. In Spanish.
• This same guide, module 4, lesson 5 — source of the complete schema evolution mechanism that populated country for the first time. ../module-04-schema-evolution-without-rewriting/en/05-adding-country-to-dim-store.md. In English.
• This guide's DESIGN doc — the full map of the eight modules, including the lesson 4 that follows. src/guides/lakehouse-and-iceberg-guide/DISENO.md. In Spanish.