The Delta Comparison Feature provides powerful capabilities for tracking and querying data changes in result tables based on temporal events. This functionality enables you to monitor the complete data lifecycle by identifying when rows were created, updated, or deleted.
Overview
The delta feature introduces:
•Time-based filtering: Query rows based on creation or modification timestamps.
•Deletion tracking: Retrieve information about deleted rows including when they were removed.
•Metadata columns: Access additional system-managed columns that track row lifecycle events.
•Change detection: Identify data changes within specific time spans for incremental processing.
Prerequisites
Before using the delta feature, ensure the following requirements are met:
•Primary Key Requirement: Result tables must have a primary key defined. If no primary key is explicitly defined, the system automatically uses the MD5 checksum generated from row values. However, this may result in duplicates and is not recommended for production use.
•Tenant Configuration: The delta feature must be enabled in the tenant settings. See the section below for detailed configuration instructions.
•API Key Permissions: Your API key must have appropriate permissions to access result database tables.
Enabling the Delta Feature
To enable the delta comparison feature for a tenant, two settings must be configured in the tenant settings:
1.Backups to Keep: Set the Backups to keep value to at least 1. This setting determines how many historical snapshots of the data are maintained.
2.Delta Change Tracking: Enable the Delta change tracking checkbox. This activates the tracking of row-level changes including creation, modification, and deletion timestamps.
The following screenshot shows both required settings in the tenant configuration:
|
Important: Always define an explicit primary key on your result tables to ensure accurate delta tracking and avoid potential duplicate issues when using the MD5 checksum fallback. |
Metadata Columns
When the delta feature is enabled, the following system-managed metadata columns become available:
Column Name |
Data Type |
Description |
|---|---|---|
__dh_created_at |
DateTime |
Timestamp when the row was initially created in the result table. |
__dh_last_modified |
DateTime |
Timestamp of the most recent modification to the row. |
__dh_md5_checksum |
String |
MD5 hash generated from the row's values, used for change detection. |
__dh_deletion_time |
DateTime |
Timestamp when the row was deleted (available only in deleted rows endpoints). |
API Parameters
The delta feature introduces new parameters that can be used with existing and new API endpoints. All timestamp parameters use ISO 8601 date format.
Parameters for Existing Endpoints
These parameters can be used with the standard table retrieval endpoints to filter results based on temporal criteria:
Parameter |
Description |
|---|---|
createdSince |
Retrieve only rows created on or after the specified timestamp. Format: ISO 8601 (e.g., "2024-01-15T10:30:00Z"). Can be combined with createdUntil to define a time span. |
createdUntil |
Retrieve only rows created before or on the specified timestamp. Format: ISO 8601 (e.g., "2024-01-31T23:59:59Z"). Can be combined with createdSince to define a time span. |
updatedSince |
Retrieve only rows updated on or after the specified timestamp. Format: ISO 8601 (e.g., "2024-01-15T10:30:00Z"). Can be combined with updatedUntil to define a time span. |
updatedUntil |
Retrieve only rows updated before or on the specified timestamp. Format: ISO 8601 (e.g., "2024-01-31T23:59:59Z"). Can be combined with updatedSince to define a time span. |
includeMetadata |
When set to true, includes the metadata columns (__dh_created_at, __dh_last_modified, __dh_md5_checksum) in the result set. |
Parameters for Deleted Rows Endpoints
These parameters are used with the new deleted rows endpoints:
Parameter |
Description |
|---|---|
deletedSince |
Retrieve deleted rows after the specified timestamp. Format: ISO 8601. Can be combined with deletedUntil to define a time span. |
deletedUntil |
Retrieve deleted rows before the specified timestamp. Format: ISO 8601. Can be combined with deletedSince to define a time span. |
includeMetadata |
When set to true, includes the metadata columns (__dh_md5_checksum, __dh_deletion_time) in the result set. |
New API Endpoints
The delta feature introduces new endpoints specifically for retrieving information about deleted rows. For comprehensive documentation on these endpoints, see:
•Getting Deleted Rows (Full Table)
•Getting Deleted Rows (Paged Query)
Use Cases
The delta comparison feature is particularly useful for:
•Incremental data synchronisation: Fetch only the data that has changed since the last sync operation.
•Change detection: Identify modifications within specific time periods for audit or compliance purposes.
•Delta processing: Process only new or modified records to improve performance and reduce data transfer.
•Data reconciliation: Compare snapshots from different points in time to identify changes.
•Deletion tracking: Monitor which rows have been removed from result tables and when.
Best Practises
•Define primary keys: Always specify explicit primary keys on result tables to ensure accurate tracking.
•Use time spans judiciously: Narrow time ranges improve query performance and reduce data transfer.
•Combine parameters: Use createdSince with createdUntil to define precise time windows.
•Request metadata selectively: Only include metadata columns when necessary to minimise response size.
•Use paged queries: For large result sets, use paged endpoints to manage memory and network resources effectively.
See Also
•Getting Deleted Rows (Full Table)
•Getting Deleted Rows (Paged Query)
•Getting Table Data (Paged Query)
•Getting Table Data (All Rows)