Your Date Dimension Is Not Static

The date dimension has a reputation as the safest table in the warehouse. You generate fifty years of rows once, and it never changes again. Every tutorial calls it “static.” Every modeler treats it as a solved problem.

Most of that reputation is deserved. The trouble is the part that isn’t — and it hides inside the columns everyone is proud of.

The columns that lie

Open a mature date dimension and you’ll find it has grown to a hundred-plus columns. Somewhere in there, past the calendar attributes, are the convenient ones:

IsToday, IsCurrentMonth, IsCurrentQuarter, IsPastDate, IsFutureDate, IsLast30Days, RelativeMonthPosition, DaysFromToday…

They exist because someone, reasonably, got tired of writing the same date math in every report and pushed it into the dimension. Filter on IsCurrentMonth = 1 and you’re done. Clean. Reusable.

And silently, catastrophically, relative to today.

IsCurrentMonth is only true this month. Next month it’s true for different rows. Which means the value stored in that column is not a property of the date — it’s a property of when you happened to look. You have persisted a moving target into a table whose entire job was to hold still.

Why this is worse than it sounds

Here’s the failure, and it’s the kind that erodes trust rather than throwing an error.

A stakeholder pulls a report filtered on “current month.” Screenshots it. Comes back three weeks later, re-runs the same report for the same period — and the numbers are different. No new data landed. No fact changed. The report just drifted, because the flag it filtered on was recomputed against a new “today.”

Now you’re in the worst kind of meeting: explaining why a historical number moved when nothing happened. You can’t point at a bug, because the pipeline did exactly what it was told. The bug is architectural. You froze a value that was never supposed to be frozen, and the report inherited the lie.

The rule that prevents all of it is short:

If a column’s value depends on today’s date, it does not belong in the core table.

Split the deterministic core from the relative edge

The fix is a familiar one: separate the part that is true forever from the part that is only true right now.

Persist the deterministic core; compute the today-relative fields at the edge, where they’re allowed to change.

The static core holds attributes that are genuinely properties of the date and will never change: year, quarter, month, ISO week, day of week, day names, month names, fiscal period (relative to a fixed fiscal calendar), holiday flags. Generate it once, materialize it, trust it forever. This is the “static” table everyone thinks they already have.

The dynamic layer computes everything today-relative at query time — a view over the core, or measures, that evaluate current_date() when the query runs:

CREATE VIEW v_dim_date_dynamic AS
SELECT
  d.*,
  d.date_value = current_date()                         AS is_today,
  date_trunc('month', d.date_value)
      = date_trunc('month', current_date())             AS is_current_month,
  d.date_value <  current_date()                        AS is_past,
  datediff(d.date_value, current_date())                AS days_from_today
FROM dim_date d;

A view doesn’t need a refresh job — its expressions re-evaluate every time it’s queried, so “today” is always correct without a nightly process rewriting a hundred thousand rows. The relative fields become free and always-current; the core stays deterministic and reproducible. Reports that need “current month” query the view. Reports that need a fixed historical period join the core on the date key and are immune to what day it is.

That last part matters on its own: filter facts by the date key, never by a computed relative column. The key never moves. The relative column is a trap dressed up as a convenience.

”Won’t recomputing it every query be slow?”

This is the objection that keeps the bad columns in place, and it’s backwards.

Fifty years is around eighteen thousand rows. Recomputing a handful of boolean expressions over eighteen thousand rows is microseconds — it does not register. The cost you’re actually worried about doesn’t exist. The cost that does exist is the one you incur by materializing: a nightly refresh churning the table, and the data drift that refresh introduces the moment its timing or timezone is slightly off. You’re not saving compute by persisting relative flags. You’re spending reliability to avoid a cost that was never there.

Two gotchas before you ship it

Your engine may not let a view do this. In particular, if you’re serving a model in Direct Lake mode, it reads Delta files directly and won’t query a SQL view at all — so the view trick simply doesn’t apply. There you push the relative logic into measures that call TODAY() in the semantic layer instead. Import and DirectQuery each behave differently again. Decide where “today” gets evaluated per serving mode; there is no single answer that covers all three.

Mind the timezone of “today.” current_date() in a lakehouse SQL context is almost always UTC. If your business day rolls over at local midnight, a UTC “today” flips hours early or late, and is_today lights up on the wrong row for part of the day. Anchor the boundary explicitly:

to_date(from_utc_timestamp(current_timestamp(), 'America/Toronto')) AS today_local

Get the boundary right once, in the dynamic layer, and every relative flag inherits it correctly.

The date dimension really is one of the safest tables you’ll build. It just isn’t safe because someone called it static — it’s safe when you keep it static on purpose, and let the parts that depend on today live somewhere that’s allowed to change. Static core, dynamic edge. The same split that keeps the rest of the system honest keeps your history from moving when no one asked it to.

Companion code: ramwise-examples/date-dimension — the static-core generator and the dynamic view.

Try it

Advance the system date day-by-day to watch the IsCurrentDay and IsCurrentMonth booleans toggle. The flashing red cells highlight the rows that require physical updates in the database when the date rolls over, illustrating why relative attributes turn a static dimension table mutable. (Open it full-page.)