What are Tombstone Rows and why does ODE have them?

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  
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;

Also check point-in-time and start and end date satellite functions.

Tombstones

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)