> ## Documentation Index
> Fetch the complete documentation index at: https://support.entegrata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Versioned History

> Understand how Entegrata tracks changes to your data over time using versioned history

## 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.

<Info>
  Versioned history is automatically enabled when a resource is configured with **Full Load** and has **primary keys** defined.
</Info>

## Variants

Entegrata offers two variants of versioned history. Both keep a full version history of changes to existing records — they differ only in how they treat records that disappear from the source:

| Variant                            | Tracks deletions? | Behavior when a record disappears from the source                                                |
| ---------------------------------- | ----------------- | ------------------------------------------------------------------------------------------------ |
| **Versioned history**              | Yes               | Record is soft-deleted (`__sys_deleted_at` is set) and excluded from the current table           |
| **Versioned history (no deletes)** | No                | Record is left untouched — its latest version remains active in both the main and current tables |

<Tip>
  Use **Versioned history (no deletes)** when the source system reuses, archives, or filters records in ways that would otherwise produce false deletions (for example, sources that only return "active" records, or APIs whose result set varies between collections).
</Tip>

## How It Works

At a high level, each time data is collected:

<Steps>
  <Step title="Collect Full Snapshot">
    Entegrata collects a complete snapshot of the resource from your source system
  </Step>

  <Step title="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
  </Step>

  <Step title="Store New Versions">
    Only records that have actually changed get a new version stored. Unchanged records are not duplicated
  </Step>

  <Step title="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

    <Note>
      This step is skipped when the **Versioned history (no deletes)** variant is used. Records that disappear from the source remain active.
    </Note>
  </Step>

  <Step title="Rebuild Current View">
    A **current** table is rebuilt containing only the latest active version of each record, giving you a clean, deduplicated view of your data
  </Step>
</Steps>

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

In this example:

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

<Tip>
  Use the **current** table for day-to-day reporting and analytics. Use the **main** table when you need to analyze how data changed over time or perform historical audits.
</Tip>

## 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:

<ResponseField name="__sys_ingest_ts" type="timestamp">
  The timestamp when this version of the record was collected. Use this to determine when a change was detected.
</ResponseField>

<ResponseField name="__sys_deleted_at" type="timestamp">
  The timestamp when the record was soft-deleted (removed from the source). A value of `NULL` means the record is still active.
</ResponseField>

<ResponseField name="__sys_fingerprint" type="string">
  A hash of all business data fields, used for change detection. Two records with the same fingerprint have identical data.
</ResponseField>

## Change Detection

Entegrata uses **fingerprinting** to efficiently detect which records have changed between collections. Here's how it works:

1. For each incoming record, a fingerprint is calculated by hashing all business data fields (excluding system columns)
2. The new fingerprint is compared against the fingerprint of the previous version of that record
3. If the fingerprints match, the record hasn't changed and the new copy is discarded
4. If the fingerprints differ, the record has changed and a new version is stored

This means your main table only grows when data actually changes — not every time a collection runs.

<Note>
  Fingerprints are calculated from all business data columns, sorted alphabetically for consistency. Even a single character change in any field produces a different fingerprint.
</Note>

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

### Disabling Soft Deletes

When the **Versioned history (no deletes)** variant is selected, Entegrata does not track deletions:

* `__sys_deleted_at` is never set as a result of a record disappearing from the source
* A record's most recent version stays in the **current** table indefinitely, even if the source stops returning it
* Updates to records that *do* still appear in the source are versioned exactly as they are with standard versioned history

<Warning>
  With this variant you cannot rely on the main table to detect when records were removed from the source — the information is not captured. Choose this variant only when source-side absences are not meaningful deletions.
</Warning>

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

<Warning>
  After a breaking schema change, existing historical versions lose their fingerprints. This means normal change detection resumes from that point forward, but older versions are preserved as permanent historical records.
</Warning>

## 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** or **Versioned History (no deletes)**          |
| **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) |

<Info>
  Primary keys are essential because they allow Entegrata to match records across collections and determine which records have changed, been added, or been removed.
</Info>

## Best Practices

<CardGroup cols={2}>
  <Card title="Define Accurate Primary Keys" icon="key">
    Ensure your primary keys truly identify unique records. Incorrect keys lead to incorrect version tracking.
  </Card>

  <Card title="Use Current Table for Reporting" icon="table">
    The current table is optimized for queries — use it for dashboards and reports unless you specifically need historical versions.
  </Card>

  <Card title="Query History with Timestamps" icon="clock">
    When querying the main table, filter by `__sys_ingest_ts` to view data as it existed at a specific point in time.
  </Card>

  <Card title="Monitor Table Growth" icon="chart-line">
    Versioned history tables grow over time as changes accumulate. Monitor storage usage for high-change resources.
  </Card>
</CardGroup>

## Related Pages

<CardGroup cols={2}>
  <Card title="Collection Settings" icon="gear" href="/data/collector/resources/collection-settings">
    Configure load types and unique keys for resources
  </Card>

  <Card title="Viewing Resources" icon="eye" href="/data/collector/resources/viewing-resources">
    Browse and search your discovered resources
  </Card>

  <Card title="Schema Changes" icon="arrows-rotate" href="/data/collector/discovery/schema-changes">
    Learn how Entegrata handles schema evolution
  </Card>

  <Card title="Monitoring Jobs" icon="chart-line" href="/data/collector/jobs/overview">
    Track collection execution and performance
  </Card>
</CardGroup>