Your Satellites Don't Need End Dates
Insert-Only Loads With Timelines Computed At Query Time By LEAD()
October 12, 2026
A Data Vault satellite can give you a full timeline without ever updating a row. BimlFlex generates insert-only satellites by default: the DvEndDateSatellite setting ships off, and the effective-to date and is-current columns aren't created on hub satellites, link satellites or reference satellites. Turn on DvCreateSatelliteViews and every insert-only satellite also gets two views. The _CURR view returns the current row. The _EFDT view works out each row's end date when you query it, using LEAD() over the effective-from date. The load stays append-only, and you can still ask what was true on any given day.
The other path is physical end dating, where the load writes the end date into the table. We looked at the performance side of that choice in an earlier webcast on Data Vault end dating. This post is about what that webcast didn't show: the SQL each path generates today and how driving keys behave on both. It also covers one difference between the paths that will change your numbers if you don't know it's there.
What Ships by Default
Out of the box, a BimlFlex satellite load does one thing to the target table. It inserts new rows. Each row carries its key, its FlexRowEffectiveFromDate, its record source, its hashdiff and its attributes. No row is ever touched again. With DvEndDateSatellite off, FlexRowEffectiveToDate and FlexRowIsCurrent don't exist on the satellite at all, so there's nothing to maintain.
That's the textbook Data Vault 2.0 stance. Satellite loads can run side by side and restart cleanly, because none of them waits on another table's update. If you want the broader picture of how BimlFlex generates hubs, links and satellites from metadata, the Data Vault automation guide covers it.
Insert-only has an obvious cost. Somebody downstream wants "the current address" or "the address on March 3", and a raw insert-only satellite makes them write the windowing SQL themselves, and it's easy to get wrong. The views exist so nobody has to.
The Effectivity View
DvCreateSatelliteViews is off by default. Switch it on and the build emits two views per insert-only satellite. Names follow DvPrefixViewName + table name + suffix, so with the defaults (vw_, _CURR, _EFDT) a satellite called SAT_Customer_AWLT gets vw_SAT_Customer_AWLT_CURR and vw_SAT_Customer_AWLT_EFDT.
Here's the shape of the effectivity view BimlFlex generates for SQL Server, with the attribute list trimmed to two columns:
CREATE VIEW [dv].[vw_SAT_Customer_AWLT_EFDT] AS
SELECT SAT.[Customer_SK]
,SAT.[FlexRowEffectiveFromDate]
,SAT.[FlexRowRecordSource]
,SAT.[FlexRowHashSat]
,SAT.[EmailAddress]
,SAT.[Phone]
,COALESCE(LEAD(SAT.[FlexRowEffectiveFromDate]) OVER (PARTITION BY SAT.[Customer_SK] ORDER BY SAT.[FlexRowEffectiveFromDate]), CAST('9999-12-31' AS DATETIME2(7))) AS [FlexRowEffectiveToDate]
FROM [dv].[SAT_Customer_AWLT] SAT;
A row's end date is simply the next row's start date for the same key. The latest row has no next row, so COALESCE gives it the platform's high date. That's CAST('9999-12-31' AS DATETIME2(7)) on SQL Server and Synapse, and TO_TIMESTAMP('9999-12-31', 'YYYY-MM-DD') on Snowflake. Databricks gets TO_TIMESTAMP('9999-12-31'). For a point-in-time question you filter FlexRowEffectiveFromDate <= @AsOf AND FlexRowEffectiveToDate > @AsOf, the same predicate you'd use on a physically end-dated table.
The _CURR view takes a different route. It joins the satellite to its own MAX(FlexRowEffectiveFromDate) and returns the latest row. That's the one to hand to reporting, and it's usually the first thing people build when they start delivering data from a Data Vault.
The statement changes per platform. SQL Server and Synapse get CREATE VIEW, and Snowflake gets CREATE OR REPLACE VIEW. On Databricks the view comes out as a notebook that runs CREATE OR REPLACE VIEW through EXECUTE IMMEDIATE. On a multi-active satellite, the multi-active key joins the partition, so each set of concurrent rows gets its own timeline. The multi-active satellites webcast explains the pattern itself.
Because the end date is computed and never stored, it can't go stale. Delete a bad load's rows or backfill older history, and the next query rebuilds every timeline from the rows that are actually there.
What Physical End Dating Adds
Set DvEndDateSatellite to Y on a satellite and three things change. The table gets FlexRowEffectiveToDate and FlexRowIsCurrent columns. The build stops generating views for that satellite, since its rows now carry a real end date. And the load gets an end-dating pass after the insert, made of three statements. Trimmed for reading, with temporary table names shortened, they look like this on SQL Server.
First, find the keys that now have more than one open row:
SELECT SAT.[Customer_SK]
,SAT.[FlexRowRecordSource]
,MIN(SAT.[FlexRowEffectiveFromDate]) AS [FlexRowEffectiveFromDate]
FROM [dv].[SAT_Customer_AWLT] SAT
WHERE SAT.[FlexRowEffectiveToDate] = CAST('9999-12-31' AS DATETIME2(7))
GROUP BY SAT.[Customer_SK]
,SAT.[FlexRowRecordSource]
HAVING COUNT(*) > 1;
Then work out end dates for those keys only, starting from their earliest open row:
SELECT TGT.[Customer_SK]
,TGT.[FlexRowEffectiveFromDate]
,TGT.[FlexRowRecordSource]
,COALESCE(LEAD(TGT.[FlexRowEffectiveFromDate]) OVER (PARTITION BY TGT.[Customer_SK], TGT.[FlexRowRecordSource] ORDER BY TGT.[FlexRowEffectiveFromDate]), CAST('9999-12-31' AS DATETIME2(7))) AS [FlexRowEffectiveToDate]
FROM [dv].[SAT_Customer_AWLT] TGT
INNER JOIN #current_edt AS SRC
ON SRC.[FlexRowEffectiveFromDate] <= TGT.[FlexRowEffectiveFromDate]
AND SRC.[FlexRowRecordSource] = TGT.[FlexRowRecordSource]
AND SRC.[Customer_SK] = TGT.[Customer_SK];
Then write them back, setting the current flag in the same statement:
UPDATE TGT
SET [FlexRowEffectiveToDate] = SRC.[FlexRowEffectiveToDate]
,[FlexRowIsCurrent] = CASE WHEN SRC.[FlexRowEffectiveToDate] = CAST('9999-12-31' AS DATETIME2(7)) THEN 1 ELSE 0 END
FROM [dv].[SAT_Customer_AWLT] TGT
INNER JOIN #end_date AS SRC
ON TGT.[Customer_SK] = SRC.[Customer_SK]
AND TGT.[FlexRowEffectiveFromDate] = SRC.[FlexRowEffectiveFromDate]
AND TGT.[FlexRowRecordSource] = SRC.[FlexRowRecordSource];
The HAVING COUNT(*) > 1 keeps the pass small. A brand-new key has one open row and gets skipped. So does a key that didn't change. The update only reaches keys that just received a new version.
That same filter is also the catch. The pass only looks at keys with more than one open row. Say you back out a bad load by deleting its rows. The row before each deleted row keeps the end date it was given, pointing at a version that's gone, and the key now has no open rows at all. When the next change for that key arrives, the key has exactly one open row, so the pass skips it again. Your as-of queries now have a gap for that key, and nothing in the load will fix it. With physical end dates, every manual delete or history backfill needs its own repair step. The _EFDT view doesn't.
Physical end dating does buy you something real. Readers get a plain column with no window function, and a plain column is cheap to index and filter. When a satellite is queried constantly and loaded once a night, doing the work once per load instead of once per query is the better deal.
Driving Keys Without Updates
Links store relationships as many-to-many. Sometimes one side of the relationship decides when it ends. A product sits in one category at a time, so when a new product-category relationship shows up, the old one should close. That's a driving key, and the BimlFlex documentation on driving keys walks through how to set one in metadata.
On a link satellite with a driving key, both paths group rows by the driving key rather than by the link's own hash key. Here's the effectivity view for a LSAT_Product_ProductCategory_AWLT with Product_SK as the driving key, attributes trimmed:
SELECT SAT.[Product_ProductCategory_SK]
,SAT.[FlexRowEffectiveFromDate]
,SAT.[FlexRowRecordSource]
,COALESCE(LEAD(SAT.[FlexRowEffectiveFromDate]) OVER (PARTITION BY LNK.[Product_SK] ORDER BY SAT.[FlexRowEffectiveFromDate]), CAST('9999-12-31' AS DATETIME2(7))) AS [FlexRowEffectiveToDate]
FROM [dv].[LSAT_Product_ProductCategory_AWLT] SAT
INNER JOIN [dv].[LNK_Product_ProductCategory] LNK
ON SAT.[Product_ProductCategory_SK] = LNK.[Product_ProductCategory_SK];
The view joins to the link to get the product key, then partitions on it. Move a product from Bikes to Accessories and the new link row gets a new hash key and a new satellite row. In the view, that new row is the next row in the product's partition, so the Bikes relationship ends the moment the Accessories one starts. Nothing got updated. The physical path does the same grouping, joining the link inside the end-dating pass, and writes the result to the table.
The driving key documentation says driving-key effectivity needs end dating turned on. That describes the physical path. With DvCreateSatelliteViews on, an insert-only link satellite gets the same closure when you query it.
Where the Two Paths Disagree
Look at the PARTITION BY in the two effectivity calculations above. The physical pass partitions by key and record source. The _EFDT view partitions by key only. They aren't equivalent, and they'll give different answers on a satellite fed by more than one record source.
Take a customer key that gets rows from both a CRM and a billing system in the same satellite. Physical end dating keeps two timelines, one per source, and each source's latest row stays open. The _EFDT view builds one timeline across both sources, so a billing row closes the CRM row before it, and only the most recent row from either source is open. The _CURR view groups by record source as well, so it can return two current rows for that key.
Most Data Vault designs keep one record source per satellite, and when you do, the two paths agree. If you've combined sources into one satellite on purpose, decide which reading you mean. Separate timelines per source match the physical path. A single "latest from anywhere" timeline is what the view gives you.
How to Choose
Both settings work at the object level, so you can decide satellite by satellite.
Start insert-only with DvCreateSatelliteViews on. The load never updates a satellite, and timelines fix themselves after a delete or a backfill. Driving keys still close old relationships. What you pay is a LEAD() over the rows each query reads, on every read.
Switch a satellite to DvEndDateSatellite when that read cost starts to show: a satellite that dashboards hit all day, or one big enough that sorting it for each query hurts. Know what comes with it. The load runs an extra three-statement pass. The table gains two columns, so plan the switch like any schema change. And any row you delete or backfill by hand needs an end-date repair that the load won't do for you.
Before you switch a satellite, count its record sources. With one, the two paths agree. With more, pick the path that gives you the timeline you actually want.
