What is Versioned History?
Versioned history is a load strategy that preserves a complete history of changes to your data over time. Instead of overwriting records when they change, Entegrata keeps every version so you can see what a record looked like at any point in time. This strategy is based on a data warehousing pattern known as Slowly Changing Dimension Type 2 (SCD2), which is the industry standard for tracking historical changes.Versioned history is automatically enabled when a resource is configured with Full Load and has primary keys defined.
How It Works
At a high level, each time data is collected:Collect Full Snapshot
Entegrata collects a complete snapshot of the resource from your source system
Detect Changes
Each record is compared against its previous version using a fingerprint (a hash of all business data fields). If the fingerprint matches, the record hasn’t changed
Store New Versions
Only records that have actually changed get a new version stored. Unchanged records are not duplicated
Soft Delete Removed Records
Records that no longer appear in the source are soft-deleted — they are marked with a deletion timestamp rather than being permanently removed
Two Tables: Main and Current
When versioned history is enabled, your data is available in two tables:Main Table (Full History)
The main table contains every version of every record that has ever been collected. This is your complete audit trail.| Ingestion Timestamp | Deleted At | Key | Name | Status |
|---|---|---|---|---|
| 2025-01-01 | — | A | Acme Corp | Active |
| 2025-02-01 | — | A | Acme Corp | Inactive |
| 2025-01-01 | — | B | Beta LLC | Active |
| 2025-01-01 | 2025-03-01 | C | Gamma Inc | Active |
- Record A changed between January and February (status went from Active to Inactive), so both versions are kept
- Record B has never changed — only one version exists
- Record C was removed from the source in March — it is soft-deleted rather than removed
Current Table (Latest Only)
The current table contains only the most recent active version of each record. This is the table you should use for most reporting and analytics.| Key | Name | Status |
|---|---|---|
| A | Acme Corp | Inactive |
| B | Beta LLC | Active |
System Columns
Versioned history relies on several system columns (prefixed with__sys_) to track record metadata. The __sys_ingest_ts and __sys_deleted_at columns are present on all collected resources regardless of load strategy, while __sys_fingerprint is specific to versioned history:
The timestamp when this version of the record was collected. Use this to determine when a change was detected.
The timestamp when the record was soft-deleted (removed from the source). A value of
NULL means the record is still active.A hash of all business data fields, used for change detection. Two records with the same fingerprint have identical data.
Change Detection
Entegrata uses fingerprinting to efficiently detect which records have changed between collections. Here’s how it works:- For each incoming record, a fingerprint is calculated by hashing all business data fields (excluding system columns)
- The new fingerprint is compared against the fingerprint of the previous version of that record
- If the fingerprints match, the record hasn’t changed and the new copy is discarded
- If the fingerprints differ, the record has changed and a new version is stored
Fingerprints are calculated from all business data columns, sorted alphabetically for consistency. Even a single character change in any field produces a different fingerprint.
Soft Deletes
When a record that previously existed in the source no longer appears in a full load collection, Entegrata does not permanently delete it. Instead, the record is soft-deleted by setting the__sys_deleted_at column to the current timestamp.
This means:
- The historical record and all its versions remain in the main table for auditing
- The record is excluded from the current table (only active records appear there)
- You can query the main table to find when and which records were deleted
Schema Changes
When the data type of a field changes in your source system (for example, a field changing from text to a number), Entegrata handles this automatically:- Non-breaking changes (e.g., a small number type widening to a larger number type) are handled seamlessly — existing data and version history are preserved as-is
- Breaking changes (e.g., text to number) trigger a schema migration where existing data is re-cast to the new type. Values that cannot be converted become
NULL. A new version is created for all records at this point, effectively creating a “reset point” in the version history
When to Use Versioned History
Versioned history is ideal when you need to:- Track changes over time — See how records evolved (e.g., status changes, name updates)
- Perform historical audits — Answer questions like “What did this record look like last month?”
- Detect deleted records — Know when records were removed from the source system
- Comply with data retention requirements — Maintain a full audit trail of data changes
Requirements
For versioned history to be active on a resource, the following conditions must be met:| Requirement | Details |
|---|---|
| Load strategy | Must be set to Versioned History |
| Load type | Must be Full Load (incremental loads do not support versioned history) |
| Primary keys | The resource must have primary keys defined (either source-managed or user-defined) |
Primary keys are essential because they allow Entegrata to match records across collections and determine which records have changed, been added, or been removed.
Best Practices
Define Accurate Primary Keys
Ensure your primary keys truly identify unique records. Incorrect keys lead to incorrect version tracking.
Use Current Table for Reporting
The current table is optimized for queries — use it for dashboards and reports unless you specifically need historical versions.
Query History with Timestamps
When querying the main table, filter by
__sys_ingest_ts to view data as it existed at a specific point in time.Monitor Table Growth
Versioned history tables grow over time as changes accumulate. Monitor storage usage for high-change resources.
Related Pages
Collection Settings
Configure load types and unique keys for resources
Viewing Resources
Browse and search your discovered resources
Schema Changes
Learn how Entegrata handles schema evolution
Monitoring Jobs
Track collection execution and performance