Case Study 2: The Timezone That Two Silver Models Disagreed About

"Both models parsed the same column from the same bronze table. One assumed New York, one assumed UTC. Both were staging models, so neither had an owner, and neither had made a decision — which is exactly why they made different ones."

Executive Summary

Kestrel's supplier feed sends delivered_at as 2026-03-14 09:00:00 — a local timestamp with no offset.

Two silver models parsed it. One assumed America/New_York; one assumed UTC. For eleven months, supplier delivery performance was reported two ways, differing by four or five hours depending on the season, and the discrepancy was attributed to "reporting lag" by everyone who noticed it.

Neither engineer made a mistake in the code. They made a decision — what timezone an ambiguous timestamp is in — in a layer whose entire premise is that no decisions are made there.

silver.stg_deliveries       PARSE(delivered_at, 'America/New_York')   -> UTC
silver.stg_supplier_sla     PARSE(delivered_at, 'UTC')                -> UTC

The discrepancy surfaced when daylight saving changed and the gap moved from five hours to four, breaking a reconciliation that had been quietly wrong all along.

The fix moved the decision to gold, where it has a name, an owner, and one implementation. The audit that followed found four more ambiguous parses in silver, two of which were also inconsistent.

Skills applied: the silver guarantee (§34.3); the test that distinguishes a cast from a business rule; layer drift (§34.13); and Chapter 30 §30.8's certified definition arriving one layer lower.

Background

The supplier feed is a CSV, delivered daily, from a partner Kestrel does not control (Chapter 30 §30.10's external source).

supplier_id,po_number,delivered_at,quantity
SUP-041,PO-889201,2026-03-14 09:00:00,240

delivered_at has no timezone. The supplier's documentation does not mention one. Nobody had asked — Chapter 30 §30.10's point about asking, unasked here too.

Two models parsed it, built eight months apart by two engineers:

silver.stg_deliveries, built first, feeds inventory. The engineer reasoned: the supplier is in New Jersey, their warehouse operates on Eastern time, this is Eastern. Defensible.

silver.stg_supplier_sla, built eight months later, feeds a supplier scorecard. That engineer reasoned: everything in this platform is UTC (Chapter 6's convention), so this is UTC. Also defensible.

Neither wrote the reasoning down, because in both cases it did not feel like a decision. It felt like parsing a timestamp, which is squarely a silver operation.

⚠️ Failure Mode — the decision that does not feel like one

This is the hardest boundary in the medallion architecture and §34.3's callout names it: if two competent people could disagree, it is a business rule.

The difficulty is that the disagreement is invisible from inside either decision. Neither engineer experienced a choice. Each saw an unambiguous parse:

text engineer 1's experience: "this is a timestamp; I know the supplier is Eastern; I write the parse." no decision felt engineer 2's experience: "this is a timestamp; this platform is UTC; I write the parse." no decision felt

A decision that feels like a decision gets documented. A decision that feels like a fact does not, and the ones that feel like facts are the ones where two people diverge, because each is applying a different obvious-to-them default.

Three markers that a "parse" is actually a rule, and any one of them should stop you:

  • The source did not tell you. A format the source specified is a parse. A format you inferred is a rule. No timezone in the file means no timezone was specified.
  • You used context to decide. "The supplier is in New Jersey" is domain knowledge, not string handling. If your reasoning left the column, you made a rule.
  • A different reasonable person might land elsewhere. The direct test, and the hardest to apply to yourself.

The second marker is the practical one, because it is checkable while writing the code: did I have to think about anything outside this column to write this line? Engineer 1 thought about New Jersey. Engineer 2 thought about a platform convention. Both had left the column.

The Problem

The gap was visible for eleven months and was explained away every time.

month        inventory says     scorecard says      gap
2025-06        14:00 UTC          09:00 UTC       5 hours
2025-09        14:00 UTC          09:00 UTC       5 hours
2025-12        14:00 UTC          09:00 UTC       5 hours
2026-03        13:00 UTC          09:00 UTC       4 hours   <-- DST

The five-hour gap had a story. Somebody had decided, early, that the scorecard used "supplier-local receipt time" and inventory used "warehouse-scan time," and that a five-hour difference between them was a processing delay. It was wrong, it was plausible, and it was repeated for a year.

The break came from daylight saving. In March, the gap moved from five hours to four, and a reconciliation that compared the two on a fixed offset began failing.

🔎 Read the Plan — the discrepancy that had an explanation was more dangerous than one that did not

An unexplained five-hour gap gets investigated. An explained one does not, and the explanation here was reasonable, offered by a senior person, and wrong.

What made it stick for eleven months:

  • It was consistent. Exactly five hours, every day, for months. Consistency reads as correctness, and a constant offset feels like a systematic property rather than a defect.
  • It was directionally sensible. Warehouse scan should be later than supplier dispatch. The sign was right.
  • Nobody owned the comparison. Inventory owned one number, the supplier team owned the other, and the gap belonged to neither — §30.4's ownership problem, applied to a relationship between two tables rather than to a table.

The daylight saving change is what broke it, and that is luck. Had both timezones been fixed-offset — UTC versus a non-DST zone — the gap would have stayed constant and the explanation would have held indefinitely.

The generalizable move is uncomfortable: be suspicious of a discrepancy that is exactly constant. Real processing delays vary. A gap of exactly 5.000 hours every day for months is not a delay; it is an offset, and an offset means two systems disagree about a unit or a reference point. Kestrel now asserts that any reconciliation gap must have non-zero variance, which sounds absurd and has fired twice more.

The Analysis

Once the question was asked, the answer took twenty minutes — and then a much longer conversation.

Step 1: which is right? Neither, and this is the important finding. The supplier had never specified a timezone, so there was no fact of the matter to discover. Both models were guessing.

Step 2: ask the supplier. §30.10's neglected option. The supplier answered in a day: their system records local warehouse time, and their warehouses are in three timezones. So the correct parse depends on which warehouse shipped, which is a column the feed does not include.

The answer to "what timezone is this?" was "it depends, and we do not send you what it depends on."

Step 3: what can actually be done? Three options, and Kestrel took the third:

A. assume Eastern              wrong for 2 of 3 warehouses
B. ask the supplier to add a   correct; requires a schema change on
   timezone column             their side; 6-month lead time
C. ask for UTC                 correct; a smaller change; and it makes
                               the column unambiguous forever

The supplier agreed to C in three weeks, which is the outcome §30.10 predicts and which nobody had attempted for eleven months because "the vendor won't change anything" was assumed rather than tested.

Step 4: what about the eleven months of history? It cannot be corrected, because the information needed to correct it — which warehouse — was never sent. Kestrel documented the ambiguity as a gotcha on the model (§30.9) and left the history as it was, with a note.

The Decision

Four changes.

One: the parse moves to gold, once. While the ambiguity persisted, the decision got a name, an owner, and one implementation:

-- models/gold/dim_supplier_delivery.sql
-- Business rule: the supplier sends local warehouse time with no offset
-- and does not send the warehouse. Until they send UTC (agreed, ETA
-- 2026-05), we assume America/New_York, which is correct for the
-- warehouse handling ~68% of volume and wrong for the other two.
-- Owner: #supply-chain. Decided 2026-03-19. Error: up to +/- 3 hours.

📐 Design Decision — a wrong answer with a name beats a wrong answer without one

The interim fix assumes Eastern, which is wrong for about a third of the volume. It is not better than what either model did before. What is better is everything around it:

  • It is in one place, so the two reports agree — which was the presenting problem and is now solved regardless of correctness.
  • It has an owner who can be asked and can change it.
  • It states its own error: ±3 hours, on a known share of volume. A number a consumer can reason about.
  • It has an expiry, tied to the supplier's change.

The team's initial instinct was to hold the fix until the supplier delivered UTC, on the grounds that shipping a knowingly-wrong parse felt worse than leaving it. That instinct is the one to resist, and the reason generalizes:

Two reports disagreeing is a worse failure than both being off by a known amount. Disagreement destroys trust in the platform; a documented, bounded error is a data quality issue somebody can plan around. And the disagreement was the thing that had actually cost eleven months.

The general form: when you cannot be correct, be consistent and say by how much. Chapter 26 §26.8's "stale over wrong" is the same trade in a different dimension.

Two: layer_check.py gains a rule. A silver model may not call a timezone-assuming parse function. The check is crude — a regex over the compiled SQL for AT TIME ZONE and to_timestamp with a literal zone — and it found the four others.

Three: the audit. Four more ambiguous parses in silver:

silver.stg_supplier_returns    date-only, assumed midnight ET     inconsistent
silver.stg_promo_calendar      date-only, assumed midnight UTC    inconsistent
silver.stg_warehouse_shifts    local, correctly documented        ok
silver.stg_carrier_scans       carrier's zone, in a column        ok, moved to gold

Two of the four disagreed with each other in the same way, on date-only columns, and nobody had noticed because the discrepancy was under a day.

Four: a bronze-level rule. Any timestamp column arriving without an offset is flagged at ingestion, and the flag appears in the catalog. This is the durable half — it makes the ambiguity visible where it enters rather than where it is resolved.

What Happened

Before After
Timezone assumptions in silver 5 0
Inconsistent pairs 2 0
Reports disagreeing on delivery time 2 0
Documented error bound none ±3 h on 32% of volume
Supplier sends UTC no agreed, ETA 8 weeks
Ambiguous timestamps flagged at ingestion 0 all of them
History correctable — no — the information was never sent

The last row is the honest one. Eleven months of delivery-performance history is ambiguous and will remain so. The supplier scorecard for that period was recomputed with the documented assumption and republished with a note, which is the best available outcome and is not a good one.

And the ingestion flag found something nobody expected. Of Kestrel's 41 timestamp columns arriving from external sources, 14 have no offset — and only the five in silver had been noticed, because the other nine were in feeds whose timestamps nobody had yet used for anything comparative.

🧱 Kestrel Platform — flagging the ambiguity where it enters

```python

platform/ingest/timestamp_audit.py

Any timestamp column arriving WITHOUT an offset is ambiguous, full

stop. It gets a catalog flag and a required decision before any model

may consume it.

AMBIGUOUS = re.compile(r"^\d{4}-\d{2}-\d{2}[ T]\d{2}:\d{2}:\d{2}$") # no Z, no +hh:mm ```

The flag does not resolve anything. It records that a decision is required, in the catalog, on the bronze table, before anyone has made the decision implicitly by writing a parse.

Nine of the fourteen flagged columns are still unresolved, and that is the correct state — nobody has needed them yet, and resolving them now would mean guessing in advance. What has changed is that the next person to use one will be told it is ambiguous, rather than discovering it eleven months later through a daylight saving transition.

This is the medallion pattern doing the one thing it is genuinely good at: it gave the ambiguity a place to live that is upstream of every decision about it. In a single-layer transformation codebase there is nowhere to record "this is ambiguous and nobody has decided yet" — the first person to touch it decides, silently, and the decision is indistinguishable from a parse.

Lessons

  1. Two silver models made the same decision differently for eleven months, and neither engineer experienced a decision.

  2. ⚠️ A decision that feels like a fact does not get documented, and those are exactly the ones where two people diverge — each applying a different obvious-to-them default.

  3. Three markers that a parse is a rule: the source did not tell you · you used context outside the column · a reasonable person might land elsewhere. The second is checkable while writing the line.

  4. 🔎 A discrepancy with an explanation is more dangerous than one without. Five hours, every day, for a year, with a plausible story attached.

  5. Be suspicious of a gap that is exactly constant. Real delays vary; a constant offset means two systems disagree about a unit or a reference point. Kestrel now asserts non-zero variance on reconciliation gaps, which sounds absurd and has fired twice more.

  6. The gap belonged to nobody, because two teams each owned one number. §30.4's ownership problem, applied to a relationship between tables.

  7. There was no fact of the matter. The supplier never specified a timezone, so both models were guessing — and the supplier's real answer was "it depends on the warehouse, and we don't send you the warehouse."

  8. Asking the supplier took a day and got UTC agreed in three weeks. §30.10, unasked for eleven months because "the vendor won't change anything" was assumed.

  9. 📐 When you cannot be correct, be consistent and say by how much. A wrong answer with an owner, a stated ±3 h error, and an expiry beats two reports disagreeing — and the disagreement was what had actually cost eleven months.

  10. The audit found four more, two of them inconsistent on date-only columns, undetected because the discrepancy was under a day.

  11. 🧱 Flag the ambiguity at ingestion, not at resolution. Of 41 external timestamp columns, 14 have no offset; nine are still unresolved, which is correct — the next person to use one will be told.

  12. This is the one thing the layers are genuinely good at: giving an unresolved ambiguity a place to live upstream of every decision about it. In a single-layer codebase, the first person to touch it decides silently.

Questions for Discussion

  1. Both engineers reasoned defensibly and reached different answers. What review mechanism would have caught the second one, eight months after the first?

  2. "Did I have to think about anything outside this column?" is offered as a checkable marker. Apply it to five transformations in your own code. How many fail?

  3. Kestrel now asserts that a reconciliation gap must have non-zero variance. Construct the false positive, and decide whether the assertion survives it.

  4. Eleven months of history is uncorrectable. Should the scorecard for that period be republished with an assumption, withdrawn, or left as it was?

  5. Nine flagged timestamp columns remain unresolved. Is "unresolved" a stable state, or does it decay?

  6. The interim fix is knowingly wrong for 32% of volume. Would you have shipped it, or waited eight weeks for the supplier?

  7. This case study argues the layers gave the ambiguity somewhere to live. Is that a property of layers specifically, or would a catalog (Chapter 30) have done the same job?