The Fact Load Knows What Time It Is
An Effective Date Turns Fact Key Lookups Into Point In Time Joins
September 2, 2026
A fact row that lands tonight can describe something that happened in March. The order was placed March 12. The customer who placed it moved from the Southeast territory to the Midwest in June, so the customer dimension now holds two rows for her: the one that was true in March and the one that is true today. Tonight the fact loader has to pick a surrogate key. Which row does it point at?
Most teams answer this question once, informally, in the body of a lookup someone wrote in 2019, and then answer it differently in the next fact table over. BimlFlex answers it from metadata, per key, every time the model regenerates.
Two ways to resolve a key
There are two defensible answers, and they produce different reports.
Current-row resolution joins the business key and filters to the dimension's active version. It's cheap and it's correct for loads that arrive the same day the event happened, which is most of them, most of the time.
Point-in-time resolution joins the business key and a date range: give me the version of this customer that was true on the fact's own date. That's the historically correct answer, and it's the one you want the moment facts arrive late. Replay six weeks of files through a current-row lookup and every replayed order gets stamped with today's version of every customer, today's territory, today's segment. Nothing errors. The March numbers just quietly move.
The failure mode isn't picking the wrong strategy. It's that hand-built warehouses pick per developer, per fact table, per year, and nobody can say which fact resolves keys which way. In a dimensional model built with BimlFlex, the choice isn't in anyone's hands at load time. It falls out of two pieces of metadata: whether the referenced dimension tracks history, and whether the fact carries an effective date.
The join BimlFlex generates
Mark a date column on the fact source with the Effective Date change type (EFD in the Change Type dropdown). If the referenced dimension is Type 2, the generated fact load resolves that key with a date range join against the dimension's own timeline columns:
LEFT OUTER JOIN [dim].[Customer] AS SK1
ON SRC.[CustomerCode] = SK1.[CustomerCode]
AND SRC.[OrderDate] >= SK1.[RowStartDate]
AND SRC.[OrderDate] <= SK1.[RowEndDate]
The order from March 12 finds the customer row whose validity window contains March 12. Not the newest row. The right row.
RowStartDate and RowEndDate are the same documented configurations that drive the dimension's own history tracking, with start-of-time and end-of-time defaults (1900-01-01 and 9999-12-31), and you can rename or retype them if your shop has its own convention. Both bounds are inclusive, so a fact dated exactly on a version boundary resolves against the window that contains that instant.
Each dimension reference in the fact gets its own numbered alias (SK1, SK2, SK3...), so a wide fact table reads as a stack of these joins, one per foreign key, each carrying its own resolution rule.
No effective date, no time travel
Take the effective date away and the generator does the honest thing: it falls back to a current-row join.
LEFT OUTER JOIN [dim].[Customer] AS SK1
ON SRC.[CustomerCode] = SK1.[CustomerCode]
AND SK1.[RowIsCurrent] = 1
And a reference to a plain Type 1 dimension needs neither, because there's only one row per key. It joins on the business key alone.
The consequence worth noticing: a single generated fact statement can mix all three shapes. The customer key resolves through a date range, the product key through a current-row filter, the currency key through bare equality, each reference getting the strongest resolution its dimension supports. You didn't write any of those joins, and you can't accidentally write them inconsistently.
Unmatched keys have a home
Every one of those joins is a LEFT OUTER JOIN, and every resolved key is wrapped before it lands:
COALESCE(SK1.[CustomerSK], -1) AS [CustomerSK]
Early-arriving facts, a source system emitting a code the dimension has never seen, a date that falls outside every version's window: all of it lands on the -1 unknown member instead of a NULL foreign key. Fact rows never drop out of reports on inner-join semantics, totals reconcile against the source, and the unknown bucket is something you can put a monitor on. A NULL foreign key is a silent bug. A count of rows sitting on -1 is a work item.
Type 2 is a column setting
Here's the part that makes the whole thing hold together: you never declare "this dimension is SCD Type 2" anywhere. You set a Change Type per column. Update means track the latest value, the classic Type 1 attribute. Track History means version it, the classic Type 2 attribute. A dimension with any Track History column is a Type 2 dimension, and the fact resolution logic keys off exactly that inference.
So mixed dimensions aren't an edge case, they're the default outcome of honest modeling. Three columns that version and ten that update in place is just what the column settings say. We covered how BimlFlex loads those dimensions elsewhere; the point here is the other half of the star. The fact side inherits the decision automatically. Flip one column from Update to Track History, regenerate, and every fact that references that dimension switches from a current-row lookup to a point-in-time join. No fact loader gets edited. No ticket gets filed against six downstream packages.
That's the property hand-coded warehouses can't keep over time. The question "which version of the customer did this order buy from" has one answer across the entire model, because every fact load derives its join from the same column metadata, whether you're building a focused data mart or delivering marts off a Data Vault. The model declares the rule. The generator applies it everywhere, including the places nobody remembered to check.
