Populating country in dim_store

Description

Lessons 3 and 4 built the complete schema-evolution mechanism — add, rename, drop — without populating country with any real value yet. This lesson does the work that was missing: it fills Kiosko's three stores' country column, deterministically, from city — Bogotá→Colombia, Lima→Peru, Santiago→Chile. The tool isn't any new schema operation: it's table.overwrite(), the same one you already used in module 3 for the P002 change, now applied to a table with one extra column.

Connection to the module. This is, precisely, this module's "payoff" lesson: up to here, country existed in the schema, but had no real value. This lesson closes that gap. And it does something more, almost incidentally: it demonstrates that populating a new column is a data operation, with its own snapshot, while adding that same column to the schema was a metadata operation, with no snapshot at all — the exact distinction lesson 3 introduced, now applied with a real business result.

An analogy: filling in the blank box, not redesigning the form

Picking back up the census from lesson 1: the form already has the fourth question — country — since lesson 3. What was missing was for someone, with information they already had at hand — each house's city — to fill in the blank box with the correct answer. Nobody redesigns the form again. Nobody knocks on the door again with a new question. It's simply completing what was already known, with a clear, unambiguous rule: every Kiosko city belongs to a single country, so the correct answer for each house is entirely determined by data the card already had.

Worked example: country, populated from city

Step 1 — The deterministic mapping: one city, one country, always

# populate_country.py
import os

import pyarrow as pa
from pyiceberg.catalog import load_catalog

warehouse_path = os.path.abspath("kiosko_warehouse")
catalog_db_path = os.path.abspath("kiosko_catalog.db")

catalog = load_catalog(
    "kiosko", type="sql",
    uri=f"sqlite:///{catalog_db_path}", warehouse=f"file://{warehouse_path}",
)
table = catalog.load_table("kiosko.dim_store")

# the mapping is fixed, business data -- Kiosko's three cities, each with a single country
CITY_TO_COUNTRY = {"Bogota": "Colombia", "Lima": "Peru", "Santiago": "Chile"}

DIM_STORE_WITH_COUNTRY = [
    {"store_id": "S01", "store_name": "Kiosko Centro", "city": "Bogota", "country": CITY_TO_COUNTRY["Bogota"]},
    {"store_id": "S02", "store_name": "Kiosko Norte", "city": "Lima", "country": CITY_TO_COUNTRY["Lima"]},
    {"store_id": "S03", "store_name": "Kiosko Sur", "city": "Santiago", "country": CITY_TO_COUNTRY["Santiago"]},
]

dim_store_pa_schema_v2 = 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=True),
])

pa_table = pa.Table.from_pylist(DIM_STORE_WITH_COUNTRY, schema=dim_store_pa_schema_v2)
table.overwrite(pa_table)

print("table.scan().to_arrow() after populating country:")
for row in table.scan().to_arrow().to_pylist():
    print(f"  {row['store_id']}  {row['store_name']:<15} {row['city']:<10} country={row['country']}")

What to expect (verified by running the actual script, against the table lesson 4 left):

table.scan().to_arrow() after populating country:
  S01  Kiosko Centro   Bogota     country=Colombia
  S02  Kiosko Norte    Lima       country=Peru
  S03  Kiosko Sur      Santiago   country=Chile

Kiosko's three stores, each with its correct country — with no ambiguity at all, because the CITY_TO_COUNTRY mapping covers exactly the three cities that exist in dim_store, not one more, not one less. Nothing in this step involved random, an external geolocation service, or any value depending on when you run the script: Bogota is, always, Colombia.

Step 2 — Internally, append, delete, append again

snaps = table.inspect.snapshots().select(["snapshot_id", "operation", "summary"])
print(f"\ntable.history() has {len(table.history())} entries")
for row in snaps.to_pylist():
    summary = dict(row["summary"])
    print(f"  operation={row['operation']:<8} added-records={summary.get('added-records', '-')} "
          f"deleted-records={summary.get('deleted-records', '-')} total-records={summary['total-records']}")

What to expect (verified by running the actual script; the exact snapshot_ids and committed_at are your own run's — the number of entries and the operation/summary values are deterministic):

table.history() has 3 entries
  operation=append   added-records=3 deleted-records=- total-records=3
  operation=delete   added-records=- deleted-records=3 total-records=0
  operation=append   added-records=3 deleted-records=- total-records=3

You recognize this pattern: it's exactly the same one module 3 found overwriting kiosko.dim_product with P002's V2 values — an overwrite() with no filter replaces 100% of the rows, and Iceberg resolves it internally as a full delete followed by a full append, two snapshots within a single call. This module's lesson 2 already demonstrated, with 490 concurrent reads, that no external reader ever sees that intermediate total-records=0 state as the table's current state — here, on kiosko.dim_store, exactly the same guarantee applies.

Diagram: metadata first, data later

flowchart TB
    A["Lesson 3:\nadd_column('country')\nMETADATA -- 0 snapshots,\n0 files touched"] --> B["dim_store: 4 columns,\ncountry=None on all 3 rows"]
    B --> C["Lesson 5 (this one):\ntable.overwrite(...)\nDATA -- append + delete + append"]
    C --> D["dim_store: 4 columns,\ncountry populated\nS01=Colombia, S02=Peru, S03=Chile"]
    D -.->|"ORIGINAL Parquet file\n(lesson 3, no country)"| E["still exists on disk,\nreferenced by snap_before_evolution"]
    D -.->|"NEW Parquet file\n(this lesson, with country)"| F["the one table.scan()\nreads by default now"]

Notice something important for lesson 6: the original Parquet file — the one lesson 3 created, with no country — didn't disappear. table.overwrite() stopped referencing it from the current snapshot, but the file still physically exists on disk, because snap_before_evolution — captured in lesson 3, before any evolution — still needs it to correctly answer a table.scan(snapshot_id=snap_before_evolution).

Going deeper: why this isn't "schema evolution," it's a normal write

It's worth being precise about a distinction this lesson makes, deliberately, without any drama: populating country does not use table.update_schema() at all. The column already exists in the schema since lesson 3; what this lesson does is a completely normal data write, of the same kind as any table.overwrite() you already know from module 3. The reason for splitting this into two separate lessons — adding the column (lesson 3) versus populating it (this lesson) — isn't arbitrary: they are, precisely, two different kinds of change in Iceberg's model, with different costs and guarantees. Adding a column is instant, no matter how many rows the table has, because it touches no data. Populating it with real values does have the cost of a normal write — proportional to how many rows need rewriting — exactly like any overwrite(). Confusing these two operations, treating them as if they were one, means losing sight of one of this module's central ideas.

Common mistakes

Trying to populate country with table.update_schema().update_column(). What happens: someone, looking for how to "fill" the new column, finds update_column() in PyIceberg's API reference and assumes it's for assigning values. Why it happens: the name update_column sounds, by association, like "update a column's content." How to spot it: if your code tries to pass a row value to update_column(), check its real signature: update_column(path, field_type=None, required=None, doc=None) — it doesn't take any row value, only type, requiredness, or documentation changes for the whole column. How to fix it: to populate real row values, the correct tool is a data write — table.overwrite(), as in this lesson, or table.upsert(), which module 6 of this guide teaches as a key-driven alternative — never an update_schema() operation.

Forgetting to include store_id, store_name, and city in the overwrite(), and thinking only country needs writing. What happens: someone, focused on the new column, tries to build a pa.Table with only the store_id and country columns, expecting Iceberg to "merge" that result with the existing rows. Why it happens: in some systems, an UPDATE operation only affects the columns you explicitly mention, leaving the rest intact — and it's natural to expect the same behavior here. How to spot it: if your overwrite() fails with a schema error, or produces rows with missing columns, check that your pa.Table has all four columns. How to fix it: table.overwrite() replaces the whole table (or the portion the filter you pass it covers) with the exact content of the pa.Table you give it — exactly the same behavior module 3 already warned about with DIM_PRODUCT_V2, which had to include the three unchanged rows for P001, P003, and P004 for them to survive the overwrite() for the P002 change.

Exercises

Exercise 1 — Reproduce the overwrite() yourself, and confirm the three countries. With kiosko.dim_store in the state lesson 4 left (four columns, country=None), run this lesson's script. Confirm table.scan().to_arrow() shows Colombia, Peru, and Chile, in the correct row for each store.

See solution

If you started from lesson 4's exact state, your output should match this lesson's: S01 with country=Colombia, S02 with country=Peru, S03 with country=Chile, and table.history() with three new entries — append, delete, append — added to the 1 the table already had since lesson 3.

Exercise 2 — Extend CITY_TO_COUNTRY for a hypothetical fourth store, and explain what would happen if its city weren't in the dictionary. Without running it, predict: if Kiosko opened an S04 in Medellin without adding "Medellin": "Colombia" to the CITY_TO_COUNTRY dictionary, what would happen building DIM_STORE_WITH_COUNTRY with that row included?

See solution

CITY_TO_COUNTRY["Medellin"] would throw a KeyError in Python, because the dictionary doesn't have that key — the script would fail immediately, before even trying to write anything to Iceberg. This is, actually, the desired behavior: preferring an explicit, visible error (KeyError) over silently filling in country=None for a new store, or worse, guessing a wrong value. A deterministic mapping that fails loudly on an uncovered case is safer than one that produces incomplete data with no warning.

Exercise 3 — Explain, in your own words, why this lesson doesn't use random to assign countries. In 1-2 sentences, connect this decision to the rest of this guide's hard rule about determinism.

See solution

This entire guide forbids random, datetime.now(), and time.time() in any code feeding a "What to expect" block, because the goal is for any reader to reproduce exactly the same business results on their own machine — the three correct countries, not a value that changes between runs. CITY_TO_COUNTRY is a fixed mapping, just like DIM_STORE or DIM_PRODUCT in this guide's earlier lessons: Kiosko's business data is always the same, no matter when or how many times the script runs.

Summary and next step

In this lesson you populated country with Kiosko's three real countries — Colombia, Peru, Chile — deterministically derived from city with a simple Python dictionary. You confirmed this operation, unlike adding the column in lesson 3, really is a data write — with its own append/delete/append pattern, already known from module 3. And you saw, in the diagram, that lesson 3's original Parquet file still exists on disk, with nobody having touched it.

Before moving on you should be able to: clearly tell apart "adding a column to the schema" from "populating it with real values"; and explain why the second operation does create data snapshots, while the first creates none.

kiosko.dim_store now has all four columns complete, with the three correct countries. Lesson 6 confirms something you haven't proven yet: that table.scan(snapshot_id=snap_before_evolution) — the snapshot captured in lesson 3, before country existed — still reads with its original three-column schema, with no trace of country at all.

Resources

• PyIceberg — API reference, table.overwrite(), reused unchanged from module 3 over a table with an evolved schema. py.iceberg.apache.org/api. In English.
• PyIceberg — API reference, UpdateSchema.update_column(path, field_type=None, required=None, doc=None), the schema operation this lesson clarifies is not for populating row values. py.iceberg.apache.org/api. In English.
• data-engineering-foundations-guide DESIGN doc — source of stores's original schema and Kiosko's three exact cities (Bogota, Lima, Santiago) that determine the CITY_TO_COUNTRY mapping. src/guides/data-engineering-foundations-guide/DISENO.md. In Spanish.
• This guide's DESIGN doc — the "Schema evolution" section (M4), the exact source of the country mapping this lesson populates. src/guides/lakehouse-and-iceberg-guide/DISENO.md. In Spanish.