Let's get straight to the point: a boolean should never be your first move in a physical data model.
I know, I know. It's incredibly tempting. You're building a dimension table, you need to know if a row is the current one, so you slap an is_current flag on the end of the script and call it a day. It feels clean. It feels simple.
I'm not saying a boolean is never justified. There are cases where an attribute really is just true or false, no history, no drift, no reason code required. But that case has to be earned, not assumed. In my own agent instructions, a boolean (same as jsonb) requires a waiver before it's allowed into a physical model: a deliberate, written justification, not a shortcut reached for because the alternative required more thought. The default posture is no, and the burden of proof sits on the column, not on the reviewer.
Nowhere does a lazy boolean cost you more than in a dimensional model.
The OLAP Failure: Redundancy
If you have a perfectly modeled dimension table using a Slowly Changing Dimension (SCD Type 2), tracking a physical is_current boolean alongside it is fundamentally redundant.
The truth of whether a record is the current version at a given moment is already perfectly contained within your temporal boundaries (valid_from and valid_to). Adding a physical boolean flag right next to those dates introduces the very real risk of data anomalies where the flag eventually drifts out of sync with the timestamps: an ETL job dies halfway through, and now is_current = true on a row whose valid_to says otherwise.
In a strict physical model, you store only the primary source of truth: the explicit temporal versioning columns. No independently maintained derivation sits next to them.
The "Bit Bucket" Trade-off
Inevitably, someone building a dashboard on top of that dimension will push back:
"Every consumer has to remember how this dimension represents the current row. Just give us an is_current flag so every report uses the same simple predicate."
I understand the argument. But this is the same divide I wrote about back in 2008 and 2010: the database is not a Bit Bucket that exists to mirror the current report's WHERE clause. The physical model exists to be the single source of truth first, and convenient for one consumer second.
If the derivation is genuinely a performance problem, use a materialized view, a semantic-layer cache, or a generated expression that cannot drift independently from the temporal source of truth, not a physical flag sitting next to the dates it duplicates and can silently disagree with.
"But it makes it easier for the analysts!"
Inevitably, whenever I make this argument, someone across the table will push back: "But having an is_current flag just makes it easier for the analysts!"
Every single time I hear that, I cringe. The phrase that immediately comes to mind is "the soft bigotry of low expectations."
Are we really going to permanently cripple our physical data model and introduce risk of out-of-sync data anomalies because we assume an analyst is incapable of writing a WHERE valid_to IS NULL clause? Analysts are smart. They understand temporal data. Dumbing down the physical schema because we assume they can't handle reality is insulting to them, and dangerous for the database.
The Semantic Layer: Where Booleans Actually Belong
Don't get me wrong: while a raw boolean is a terrible way to store state, it remains a genuinely useful way for a human or a BI tool to consume it. End users love a good checkbox on a dashboard.
That's exactly what the semantic layer is for: abstracting complex, high-fidelity underlying reality into a simple, ephemeral business definition. Your semantic layer exposes a calculated Is Current Version dimension that evaluates valid_to IS NULL (or your system's max-date sentinel) on the fly, at query time, against the one column that's actually the source of truth.
The Idealistic Layer Boundary
If you want a physical record that never loses temporal precision or suffers from redundant flag logic, here's the boundary between physical schema and semantic abstraction:
| Modeling Challenge | The Purist Physical Reality | The Semantic Abstraction |
|---|---|---|
| State Evaluation | Temporal boundaries (valid_from to valid_to). |
Ephemeral True/False flag calculated on the fly for dashboard filtering. |
| Data Integrity | Enforced by the temporal columns themselves, single source of truth. | Enforced by a standardized definition applied consistently across every downstream BI tool. |
By keeping the boolean entirely out of the physical schema and entirely inside the semantic layer, you get the best of both worlds.
