What does an is_current flag add to a Type 2 dimension that already stores effective dates?

## What the flag is `is_current` (sometimes `current_flag`, `is_active`, `row_is_current`) is a boolean on each row of a Type 2 dimension that is true for the single version in force right now and false for every superseded version. It is redundant by construction: in a well-formed dimension it is exactly equivalent to `effective_to = <end-of-time sentinel>`, or to `effective_to IS NULL` if that is your convention. Redundant does not mean useless. It exists because the overwhelming majority of dimension lookups are not point-in-time at all — they are "give me the current list of customers", "show me the product catalogue as it stands", "which stores are open today". Making that case a one-token filter is worth a column. ## What it buys **Query clarity.** Compare the two forms an analyst has to write: ```sql -- with the flag WHERE d.is_current -- without it WHERE CURRENT_DATE >= d.effective_from AND CURRENT_DATE < d.effective_to ``` The second is not hard, but it is two predicates, it depends on knowing the interval convention, and it is easy to write with `<=` by mistake — which silently returns two rows on the day a version changes. The flag removes a whole class of hand-written-predicate errors. **A stable name for a concept.** Semantic layers, BI tools and downstream models all want a "current dimension" object. Defining that object as `is_current = true` is self-documenting; defining it as a comparison against a magic date is not. **A cheaper, more selective filter.** An equality on a boolean is the easiest possible predicate for any engine to evaluate and to combine with other filters, and it does not depend on the current date, so a view built on it does not change meaning as the clock moves. ## What it costs **It is derived state that can drift.** The flag is the only column in a Type 2 dimension that duplicates information already present. Duplicated state goes stale when a load half-succeeds: the insert of the new version lands, the update that closes the old row does not, and now two rows claim `is_current = true`. From that moment, `WHERE is_current` returns duplicates and any join through it fans out, while the equivalent date predicate might still be correct — or vice versa. The two ways of asking "what is true now" quietly disagree. **It has to be maintained on rows nobody is otherwise touching.** Every change writes to two rows: the new version and the predecessor. That is unavoidable for end-dating anyway, but the flag doubles the number of things that must be right about the predecessor update. ## Keeping it honest Three habits cover it. 1. **Flip the flag in the same operation that end-dates the row.** Never in a separate follow-up statement that can fail independently. 2. **Assert the invariant after every load** — exactly one current row per business key: ```sql SELECT customer_id, COUNT(*) AS current_rows FROM dim_customer WHERE is_current GROUP BY customer_id HAVING COUNT(*) > 1; ``` 3. **Assert that the flag agrees with the window**, so the two representations can never disagree without the load failing: ```sql SELECT customer_sk FROM dim_customer WHERE is_current <> (effective_to = DATE '9999-12-31'); ``` Some teams sidestep the whole risk by not storing the flag at all and instead exposing a `dim_customer_current` view defined by the date predicate. That is a legitimate design: it makes the convenience available with zero drift risk, at the cost of a view that must be joined rather than a column that can be filtered alongside everything else. Where the flag is stored, the tests above are what make it trustworthy. ## What it is not for Two misuses show up in interviews. **It is not a point-in-time mechanism.** `is_current` answers only one question — "now" — and cannot answer "as of last March". Any historical report must go through the effective window or through the surrogate key already stored on the fact. Filtering a historical query by `is_current` is one of the most common ways to accidentally produce as-is numbers while believing they are as-was. **It is not a deletion marker.** A row can be current and also refer to an entity that no longer exists in the source. If you need to track that the entity was deleted upstream, that is a separate attribute (a soft-delete or status flag), not a reuse of the current flag. Conflating the two means the last known version of a deleted entity either disappears from the current view or wrongly appears in it, and there is no way to express both facts. ## The short version It is a cache of the sentence "this is the version in force". Caches are worth keeping when the query is common — this one is — and worth testing on every load, because a stale cache in a dimension shows up as duplicated rows in a report.