skip to content

When should a Type 2 dimension's effective windows use timestamps rather than calendar dates?

level: middleimportance: nice to knowfreq 32%

answer

  1. how often can the entity change in a day?
  2. two changes, one day — what happens to the window?
  3. a zero-length period matches nothing
  4. the fact's timestamp grain has to agree
  5. one timezone, written down

basics

~20 s

Use timestamps when an entity can change more than once a day or when facts carry intra-day event times. Date-grain windows collapse same-day changes into zero-length or overlapping periods, so the point-in-time lookup cannot tell those versions apart.

solid answer

~50 s

Date-grain effective windows are fine for a nightly batch where each entity changes at most once per load — the window boundaries land on distinct days and every lookup resolves cleanly. They break as soon as two changes land on the same day: end-dating the first version to the second's start date produces a window of zero length, so the version never matches anything, and the history reads as if the intermediate state never existed. If the source is streamed or captured continuously, or if facts carry event timestamps you need to resolve precisely, make the window columns timestamps. The rule to hold on to is that **the window grain must be at least as fine as the change grain, and the fact's timestamp grain must match the window's**. Mismatches do not error — they misattribute quietly, which is why this is worth deciding explicitly rather than inheriting from whatever the first dimension happened to use.

code

text · 9 lines
text
Two captured changes for C7 on 2024-08-02 (09:00 and 16:00), day-grain windows:

  sk   | status  | effective_from | effective_to
  ---- | ------- | -------------- | ------------
  4471 | ACTIVE  | 2024-01-10     | 2024-08-02
  9088 | HOLD    | 2024-08-02     | 2024-08-02   <-- zero length, matches nothing
  9089 | CLOSED  | 2024-08-02     | 9999-12-31

At timestamp grain the HOLD version covers 09:00 .. 16:00 and is reachable.

go deeper

for a junior

Know that the effective window columns can be dates or timestamps and that the choice depends on how often the entity actually changes and whether facts carry a time of day.

for a middle

Explain the same-day collapse: end-dating two changes on one date produces a zero-length window that matches nothing, and describe the timestamp-grain fix plus the UTC discipline it brings.

for a senior

Show that you check the change grain of the source before choosing, document the collapse or the day-boundary convention, and recognise misattribution near boundaries as a grain or timezone mismatch rather than a load bug.

for a principal

Standardise window grain and timezone across the warehouse so no two marts resolve a boundary event differently, and make the choice reviewable when a source moves from nightly extracts to continuous capture.

## The two grains that must agree A Type 2 dimension has a hidden compatibility requirement between three things: how often the entity can change, the granularity of the effective-window columns, and the granularity of the timestamp the fact carries. When all three agree the point-in-time lookup is exact. When they disagree nothing throws an error — facts simply land on the wrong version, or on none. ## Where date grain is fine The classic warehouse loads dimensions once a night from a daily extract. Whatever happened to a customer during the day, the extract shows one final state, and the load records one new version effective from that day. Consecutive versions therefore always start on different days, half-open day windows tile time cleanly, and a fact carrying only an order date resolves unambiguously. Date grain also has genuine virtues: the columns are small, the values are human-readable in a report, and there is no timezone question to get wrong because there is no time-of-day component to shift. ## Where it breaks **Two changes in one day.** Suppose a customer's status changes at 09:00 and again at 16:00, and both are captured. With day-grain windows the first new version is effective from `2024-08-02`, and end-dating it against the second gives `effective_to = 2024-08-02` as well — a window of zero length under half-open semantics. It matches no instant at all. The 09:00 state is in the table but is unreachable, and any fact from that morning resolves to the 16:00 version instead. Worse, if the load computes end dates the closed-interval way ("the day before"), the first version ends *before* it starts, which some pipelines will happily insert. The usual mitigation at day grain is to **collapse same-day changes**: keep only the last state observed for each entity on each day, and accept that intra-day states are not represented. That is a legitimate modelling decision, but it must be a decision, stated in the model's documentation, rather than an accident of the end-dating arithmetic. **Continuously captured sources.** When changes arrive from a stream or a change-capture feed rather than a nightly snapshot, several versions of one entity per day is the normal case, not the exception. Day grain is simply the wrong tool. **Facts with meaningful event times.** If an event's dimension context genuinely differs by hour — a price that changed mid-morning, a device configuration that changed during a session — resolving against a date throws away exactly the precision that determines the right answer. ## Moving to timestamp grain Make `effective_from` and `effective_to` timestamps, set `effective_from` to the observed time of the change, end-date by copying the successor's timestamp, and use a far-future sentinel for the open row. Half-open semantics matter more here than at day grain, because with timestamps there is no sensible "one unit before" to decrement — the answer depends on the column's resolution, which can differ between systems and can change if the column type is ever altered. Two further disciplines come with the finer grain: **One timezone, stated.** Store windows in UTC and ensure fact timestamps are in UTC too. A window boundary compared against a local-time fact is off by the offset, which misroutes every event near the boundary — and in practice boundaries cluster around business-day starts, which is exactly where events cluster too. **A tie-break for identical timestamps.** If two changes are captured with the same timestamp, you have two zero-length or overlapping windows again, just at finer resolution. Order them by a sequence or capture offset and keep only the final state for that instant. ## Joining across a grain mismatch The mismatch to watch for is a timestamp-grain dimension joined to a date-grain fact. Every fact is then implicitly midnight, so a change made at 14:00 appears to have been in force since the start of the day, and morning events get the afternoon's attributes. If the fact genuinely has no time component, decide the convention deliberately — usually "resolve against end of day" or "resolve against start of day" — write it down, and apply it consistently, because two marts choosing differently will disagree on every boundary day. The reverse mismatch, a date-grain dimension joined to a timestamp fact, is usually benign: the date coerces to midnight and the comparison places the event in the right day's version. It only misleads when same-day changes were collapsed and the reader assumes intra-day precision that the dimension never had. ## How to decide Ask three questions. How often can this entity actually change? How often does the pipeline actually observe changes? Do the facts that reference it carry a meaningful time of day? If any answer implies sub-daily resolution, use timestamps. If all three are daily or coarser, dates are simpler, cheaper and easier to read — and choosing them consciously is better than choosing timestamps by reflex and then never populating the time component.

  • What is the day-grain workaround when a source reports several changes for one entity in a day?
    Collapse them: keep only the last observed state for that entity on that day and record one version. The intermediate states are then deliberately not represented, which is acceptable for most reporting but must be documented. The alternative — inserting each change with day-grain dates — produces zero-length or inverted windows that silently make versions unreachable.
  • What goes wrong joining a timestamp-grain dimension to a date-only fact?
    Every fact is treated as midnight, so a change that took effect at 14:00 appears to have applied all day and morning events pick up afternoon attributes. Choose a convention — resolve at start or end of day — state it in the model documentation, and apply it uniformly, because two marts choosing differently disagree on every day a change occurred.
  • Why does half-open interval semantics matter more at timestamp grain than at date grain?
    Closed intervals require end-dating to "one unit before" the successor, and at timestamp grain that unit depends on the column's resolution, which varies between systems and changes if the type is altered. Half-open windows just copy the successor's start, so the arithmetic is grain-independent and survives a later change from date to timestamp columns.

saying these in an interview costs you the question

  • Assumes one change per entity per day without checking the source
  • Inserts same-day versions at date grain and leaves zero-length windows
  • Mixes local time in the windows with UTC in the fact timestamps
  • Treats a date-only fact as midnight without stating the convention
  • Uses timestamp columns but only ever populates midnight values

context

resolving links…