Why Time Matters in Data Modeling (Ch 10)

Chapter 10 treats time as a universal dimension cutting across all six data-modeling layers. Opens with the Knight Capital disaster ($440M lost in under an hour) to illustrate catastrophic temporal mishandling.

Four Types of Time

Event time (when it happened), ingestion time (when the system received it), processing time (when computation occurred), valid time (how long a fact remained true). These diverge in every real system — a single e-commerce order triggers five different event times across payment, shipping, and review systems.

Temporality Spectrum

Non-temporal (current state only), unitemporal (single valid-time timeline), bitemporal (valid time + transaction time — enables correction without overwriting history), tritemporal (adds decision time for regulated industries). Event sourcing stores every change as an immutable append-only event, giving natural bitemporality.

Practical Guidance

Store timestamps in UTC with the “Z” suffix. Keep IANA time-zone names in a separate column. Use ISO 8601 formatting, never strings. Build a fiscal calendar dimension. Three query patterns covered: point-in-time lookups, duration calculations (watch for NULL valid_to and inclusive/exclusive counting), and time-bucketed aggregation with GROUP BY + DATE_TRUNC.

AI and Temporal Drift

ML models degrade silently as training data ages. RAG systems need valid_from/valid_to on documents to avoid retrieving stale policies. Feature stores use AS-OF joins to prevent data leakage in training sets.

RDCO Relevance

Temporal modeling is a top dbt consulting pain point — SCD Type 2 implementations, snapshot strategies, and incremental model time boundaries. Cross-ref SDG pipeline articles on snapshot freshness.