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.
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 LEFT OUTER JOIN example__SAT 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 LEFT OUTER JOIN example__SAT 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 ,effective_from_date ,lead(effective_from_date) OVER (partition by example_id ORDER by effective_from_date) effective_to_date ,is_tombstone ,other_columns FROM example__SAT ) SELECT sat.* FROM example__HUB LEFT OUTER JOIN sat 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)