Adding the epoch timestamp to view-based data model tables
The final tables used for the process data model are often based on views. Views in Vertica do not have the epoch timestamp column included automatically (like tables). For the delta data model load to work, all view-based tables in the data model need to have an epoch column. This epoch column needs to be added manually by adapting the SQL scripts in the workbench.
Note
Epoch represents a logical timestamp for the data in Vertica, which is implicitly stored for every row in a table. The epoch advances when the data is committed with a DML operation (Insert, Update, Merge, Copy, Delete). This approach allows for efficient detection of changed rows in Vertica.
For view definitions, we distinguish three different cases (based on our process connectors):
Simple views
For a simple view the epoch can be projected as follows in SQL (shortened example):
DROP VIEW IF EXISTS P2P_MARC; CREATE VIEW P2P_MARC AS( SELECT MARC.*, epoch FROM MARC AS MARC ...);
UNION ALL views
For views based on a UNION ALL of multiple tables the epoch is projected as follows in SQL:
DROP VIEW IF EXISTS BKPF_UNION; CREATE VIEW BKPF_UNION AS( SELECT *, epoch from BKPF_BSIK UNION ALL SELECT *, epoch from BKPF_BSAK );
JOIN views
For views based on JOINS the minimum epoch needs to be projected from the underlying base tables as follows in SQL:
DROP VIEW IF EXISTS P2P_EKPO; CREATE VIEW P2P_EKPO AS( SELECT ... EKPO.epoch FROM EKPO AS EKPO JOIN P2P_EKPO_STAGING ON 1=1 ... );