Refresh Records Have Unexpected hvr_cap_tstamp Values
Issue
The hvr_cap_tstamp values for refresh-loaded records may reflect when each operation finishes rather than when it starts. As a result, timestamps may vary across tables or slices, and refresh records may appear out of order relative to CDC records.
Environment
- HVR 6
- Versions earlier than 6.3
Resolution
To determine the order of refresh-loaded records, use hvr_change_sequence instead of hvr_cap_tstamp, particularly for timestamp channels.
In addition, verify the following:
- The refresh configuration uses the source database's
CURRENT_TIMESTAMPvalue from the start of theSELECToperation. - For Databricks targets, Location Time Zone is set to UTC in the Databricks target location settings.
If refresh and CDC timestamps remain inconsistent, contact our support team.
This issue is resolved in HVR 6.3 and later. Upgrade to a supported version that includes the fix.
Cause
Unlike CDC records, refresh-loaded records don't represent capture events because HVR reads the data directly from the source tables.
This issue occurs because HVR doesn't assign one refresh job start timestamp to all refresh-loaded rows. Instead, we determine hvr_cap_tstamp separately as each table or slice is processed, causing the values to vary across the refresh.
For more information, see Extra Columns for Capture, Fail, and History Tables.