Meet S04: Kiosko's fourth store

Description

Kiosko opens its fourth store: S04 Kiosko Reforma, in Mexico City. But S04 isn't a new name to anyone who's already gone through this ecosystem — it already had a first contact with Kiosko's pipeline, in data-engineering-foundations-guide (module 7), and that first contact ended badly, in a very specific and very well-documented way. This lesson tells that story precisely — quoting the exact code, the exact error message, the exact result — and then presents S04's current state: it's already in the store catalog, it can already sell without the pipeline crashing, and it's about to send its first real sales file as a recognized store.

Connection to the module. This lesson connects the vocabulary and classification from lessons 2 through 4 with the real case that lessons 6, 7, and 8 are going to diagnose. Everything that follows in this module — and much of the rest of this guide — revolves around S04 and its first file, orders_2026-08-14.csv.

The backstory, quoted precisely: S04's rejection in foundations M7

data-engineering-foundations-guide, module 7 (lesson 6, "Handling a step failure without losing the run"), added Kiosko an eighth day of data, saved as orders_2026-08-11.csv:

order_id,store_id,product_id,quantity,unit_price,order_ts
ORD-8101,S01,P001,2,0.55,2026-08-11T08:15:00
ORD-8102,S04,P002,1,1.20,2026-08-11T08:40:00

Look carefully at the second row: store_id is S04. At that point in the ecosystem, DIM_STORE — Kiosko's store catalog — only knew about S01, S02, and S03. S04 didn't exist anywhere yet. And here's the exact detail that makes this backstory relevant to this entire guide: validate_orders() did not reject that row. S04 is a non-empty string, quantity=1 is positive, unit_price=1.20 isn't negative, order_ts has a valid ISO format — the row passes the local gate's four checks with no problem, exactly as this module's lesson 4 classification predicted: validate_orders() never asked whether S04 existed in any store catalog.

The failure happened one step later, during transformation: transform_fact_orders(), built in foundations' module 4, had an explicit check — if order.store_id not in store_index: raise ValueError(f"unknown store_id: {store_id}") — and that check did throw a real exception: ValueError: unknown store_id: S04. run_pipeline(), designed in that same lesson to avoid losing the entire run over a single failure, caught the exception and returned:

PipelineResult(partition_date='2026-08-11', status='failed', rows_extracted=2, rows_valid=2, rows_rejected=0, rows_loaded=0, partition_path='', failed_step='transform')

Read that result carefully, because every field tells the full story. rows_extracted=2: both rows were read from the CSV with no problem. rows_valid=2: both passed validate_orders() — neither was rejected there. rows_loaded=0 and partition_path='': nothing was written to the warehouse, because transform_fact_orders() failed partway through its own loop, never returning any partial list. failed_step='transform': the exact point of failure, precisely recorded by the error handling that same lesson built. The net result: foundations rejected the entire file, not a single row — S01, the store that did exist, also never made it to the warehouse that day, because the transformation's atomicity guarantee (all or nothing) applied to the whole run, not row by row.

An analogy: the new employee without a badge yet

Picture an employee who already started working — they already have a desk, already have assigned tasks, are already producing — but whose access badge still isn't in the building's security system. The guard at the entrance, every morning, doesn't let them through: their name isn't on the list, no matter how much real work they're already doing once inside. The problem isn't that the employee is illegitimate — it's that the registration system (the guard's list) hasn't yet synced with reality (the employee already works there).

That's, precisely, what happened to S04 in foundations M7: the store was already selling — ORD-8102's order_ts is real, the sale happened — but Kiosko's store catalog (DIM_STORE, the equivalent of the guard's list) didn't know about it yet. The pipeline, correctly, refused to process that row as if it came from a valid store, because it had no way to confirm S04 was legitimate. This entire guide — starting with this lesson — is the story of how Kiosko fixes that desync, and of what's still missing after fixing it only halfway.

S04, onboarded: the current facts

After foundations M7's failure, someone on Kiosko's data team did the minimum necessary to stop the pipeline from crashing: they added a row to DIM_STORE by hand, with no formal process behind it. Here's the complete data for that store, exactly as it got recorded:

The country field follows the same deterministic rule lakehouse-and-iceberg-guide (module 4) already established for the three original stores — Bogotá→Colombia, Lima→Peru, Santiago→Chile —, now extended to the fourth: Ciudad de México→Mexico. It's not data invented for this lesson — it's the same deterministic function, applied to a new case.

With this, S04 now exists in DIM_STORE. Foundations M7's ValueError wouldn't happen again if run today: transform_fact_orders() would find S04 in store_index with no problem. But notice what that fix didn't do: nobody wrote a document stating what's expected of the data S04 is going to send — what arrival SLA it has, what row-count range per day is reasonable, what happens if one of its files comes in with problems. Someone simply added a row to a table, by hand, with no contract behind it. This guide's module 4 is, precisely, where that gap gets closed with a real, versioned artifact. For now, S04 is "onboarded" in the most minimal sense possible: it exists, and it no longer breaks anything on its own.

The first real file: orders_2026-08-14.csv, and why it's already late

Five days after Kiosko's canonical week closes (2026-08-03 through 2026-08-09), and one day before the P002 price change on 2026-08-15 that data-modeling-for-analytics-guide, dbt-analytics-engineering-guide, and lakehouse-and-iceberg-guide already resolved, S04 sends its first real sales file as a now-recognized store: orders_2026-08-14.csv. Lessons 6 and 7 of this module actually open it and run it — for now, there's a problem you can confirm without opening a single row of the file: when it arrives, compared to when it gets checked.

The informal agreement with S04 — not yet a formal contract, that's module 4 — is that a sales file must be available for review within 24 hours of the day it bills. S04 sold on 2026-08-14; the file should be ready for review, at the latest, on 2026-08-15. This guide's quality apparatus — the one you're going to actually run in lesson 6 — doesn't execute the same day the file arrives. It executes on 2026-08-16 at 09:00, the fixed constant you'll use throughout this guide:

# freshness_preview.py
from datetime import datetime

EXPECTED_ARRIVAL = "2026-08-14T00:00:00"   # the day S04 was due to send its first file
SLA_DEADLINE = "2026-08-15T00:00:00"       # EXPECTED_ARRIVAL + 24-hour SLA
PIPELINE_RUN_AT = "2026-08-16T09:00:00"    # this guide's fixed "now" -- never datetime.now()

deadline = datetime.fromisoformat(SLA_DEADLINE)
run_at = datetime.fromisoformat(PIPELINE_RUN_AT)
hours_past_deadline = (run_at - deadline).total_seconds() / 3600

arrival = datetime.fromisoformat(EXPECTED_ARRIVAL)
hours_since_expected = (run_at - arrival).total_seconds() / 3600

print(f"SLA_DEADLINE: {SLA_DEADLINE}")
print(f"PIPELINE_RUN_AT: {PIPELINE_RUN_AT}")
print(f"Hours elapsed since the SLA expired: {hours_past_deadline}")
print(f"Hours elapsed since the expected arrival day: {hours_since_expected}")

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

SLA_DEADLINE: 2026-08-15T00:00:00
PIPELINE_RUN_AT: 2026-08-16T09:00:00
Hours elapsed since the SLA expired: 33.0
Hours elapsed since the expected arrival day: 57.0

By the time the quality apparatus finally looks at S04's file, 33 extra hours have already passed beyond the agreed 24-hour deadline, and 57 hours since the first delivery was expected. This is the sixth quality dimension lesson 3 defined — freshness —, and it's different from the other five in something important: it doesn't depend on any specific row in the file. Even if all twelve rows of orders_2026-08-14.csv were perfect — no empty field, no duplicate, no suspicious price — the entire file would still violate the freshness SLA, just because of when someone finally reviewed it. This module doesn't build the formal freshness check yet — check_freshness() belongs to module 6 —, but the problem is already visible, with evidence, before opening a single row.

Diagram: S04's full timeline

flowchart LR
    A["2026-08-11\nfoundations M7:\nORD-8102 -> ValueError\nstatus=failed"] --> B["Someone adds S04\nto DIM_STORE by hand\n(no formal contract)"]
    B --> C["2026-08-14\nS04 sells:\norders_2026-08-14.csv"]
    C --> D["2026-08-15\nthe 24-hour\nSLA expires"]
    D --> E["2026-08-16 09:00\nPIPELINE_RUN_AT:\nfinally gets reviewed\n(57h after selling)"]
    E --> F["Lesson 6:\nvalidate_orders()\nactually run"]

Common mistakes

Thinking that adding S04 to DIM_STORE "already solved" foundations M7's problem. What happens: someone, seeing that S04 would no longer cause a ValueError if the pipeline ran today, concludes the incident is closed. Why it happens: the most visible symptom — the exception, the status='failed' — does in fact disappear with that minimal fix. How to spot it: if your reasoning stops at "it no longer crashes," you're missing the question of what guarantees that fix actually has — who decided the 24-hour SLA? Is it written anywhere any system can read, or does someone just remember it? How to fix it: adding a row to a table isn't the same as having a contract — this guide's module 4 builds that real, versioned contract, with an explicit SLA and violation policy, precisely because a hand-made fix like this lesson's isn't sustainable as Kiosko keeps growing.

Confusing foundations M7's rejection with a "quarantine." What happens: someone describes what happened to orders_2026-08-11.csv in foundations as if it had been put "in quarantine," separating good rows from bad ones. Why it happens: quarantine is a term you're going to use a lot in this guide (module 7), and it's easy to apply it retroactively to any failure. How to spot it: check the PipelineResult quoted in this lesson — rows_loaded=0. No row got loaded, not even ORD-8101 from S01, which was perfectly valid. That's not quarantine — which would separate the good from the bad —, it's a total file rejection: all or nothing, with no distinction between rows. How to fix it: use "rejection" for what happened in foundations M7 (the entire file, including the good rows, wasn't loaded), and reserve "quarantine" for the finer-grained mechanism this guide's module 7 builds, which does separate good rows from bad ones within the same file.

Skipping the lesson and assuming you already know what's wrong with S04's file. What happens: someone, familiar with this guide's pattern, assumes they can predict exactly the twelve rows of orders_2026-08-14.csv without having seen them, and jumps straight to lesson 7. Why it happens: the pattern of "a file with rows deliberately broken" is already familiar after four lessons of this module and all the experience with foundations. How to spot it: if you can't name, precisely, how many rows the file has and how many of them break each specific dimension, you don't have the diagnosis yet — you only have a reasonable expectation. How to fix it: lesson 6 opens the real file, row by row, and actually runs validate_orders() against it — don't skip that evidence, even if the general pattern already feels familiar.

Exercises

Exercise 1 — Recalculate the timeline with a different SLA. Using this lesson's freshness_preview.py script, change SLA_DEADLINE to "2026-08-14T12:00:00" (a stricter SLA, only 12 hours) and recalculate hours_past_deadline. Does the file still violate the SLA?

STRICT_SLA_DEADLINE = "2026-08-14T12:00:00"
strict_deadline = datetime.fromisoformat(STRICT_SLA_DEADLINE)
print((run_at - strict_deadline).total_seconds() / 3600)

45.0

Exercise 2 — Argue why rows_loaded=0 also affected S01. In foundations M7's quoted PipelineResult, S01's row (ORD-8101) was perfectly valid, and yet rows_loaded=0 for the entire 2026-08-11 day. In 2-3 sentences, explain why foundations' run_pipeline() design produces that result, instead of loading ORD-8101 and only rejecting ORD-8102.

Exercise 3 — Predict which quality dimension S04's file violates first, without having opened it. Based only on what you already know from this lesson — without looking at the real file, which lesson 6 opens —, which of the six data quality dimensions do you already know, with certainty, that orders_2026-08-14.csv violates, even before reading a single row? Justify your answer.

Summary and next step

In this lesson you met S04 Kiosko Reforma, Kiosko's fourth store, and its full history within the ecosystem: the total rejection of orders_2026-08-11.csv in foundations M7 (ValueError: unknown store_id: S04, status='failed', rows_loaded=0), the minimal fix that added it to DIM_STORE by hand, and the freshness violation you can already confirm, with evidence, before opening the first real file it sends as a recognized store.

Before moving on you should be able to: tell S04's story in foundations M7 by quoting the exact error message; explain the difference between "adding a row to a table" and "having a real contract"; and calculate, as the worked example did, how many hours late S04's file is relative to the informal SLA.

You have the full context. Lesson 6 finally opens orders_2026-08-14.csv — the exact twelve lines —, and runs foundations' validate_orders(), with no changes, to really see what it catches.

Resources

• data-engineering-foundations-guide, module 7, lesson 6 ("Handling a step failure without losing the run") — the literal source of S04's rejection quoted in this lesson. src/guides/data-engineering-foundations-guide/workbook/module-07-partitioning-and-orchestration/es/06-handling-a-failed-step-without-losing-the-run.md. In Spanish.
• lakehouse-and-iceberg-guide DESIGN — the source of the deterministic country function derived from city (Bogotá→Colombia, Lima→Peru, Santiago→Chile), extended here to Ciudad de México→Mexico. src/guides/lakehouse-and-iceberg-guide/DISENO.md. In Spanish.
• Python — official datetime documentation and timedelta arithmetic, the basis for this lesson's hour calculations. docs.python.org/3/library/datetime.html. In English.
• This guide's DESIGN — the exact timeline of S04's incident, including the twelve-line structure lesson 6 opens. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.