ODE can be configured to use either End Dates or Tombstones on Satellite rows.

The ODE Default is that it will use the Server Time (UTC or Offset) to Start and End Date Satellite rows as they are loaded.

End Dates

Many systems use end dates to mark the point at which each record ceases to be effective.  This layout is simple to query but slows the load down as the load has to locate and update the records that are being retired by the new records.

When configured with end dates a satellite can either be queried by start and end date or by the is_current flag.  The is_current query is preferred unless you want to look at the specific point in time other than current.

Is Current Query

SELECT example__sat.* 
FROM example__HUB  
ON  example__SAT.ROW_IS_CURRENT = 1  
AND example__HUB.example_ID = example__SAT.example_ID;

Start and End Date Query

SELECT example__sat.* 
FROM example__HUB  
ON  example__SAT.EFFECTIVE_FROM_DATE <= sysdate
AND sysdate < example__SAT.EFFECTIVE_TO_DATE  
AND example__HUB.example_ID = example__SAT.example_ID;


If you want to speed the load up by only doing inserts, you need a mechanism to indicate that a key is logically deleted. Tombstones are special rows inserted to mark the point, in a time series, when a Key is logically deleted. When you have tombstones the satellite does not have end dates.

Note that, when querying ODE, Tombstone rows should be excluded from the result set.

WITH sat as (SELECT example_id
,lead(effective_from_date) OVER (partition by example_id ORDER by effective_from_date) effective_to_date
FROM example__SAT
SELECT sat.* 
FROM example__HUB  
ON example__HUB.example_ID = sat.example_ID
AND sat.is_tombstone = 0
AND sat.effective_from_date <= sysdate
AND sysdate < coalesce(sat.effective_to_date,sysdate + 1)