History in Fabric Lakehouse

Overview

EasyFabric tracks historical changes with insert-only history tables. To understand where these tables live, one thing matters most: history is not a layer — it is a schema inside each layer's lakehouseA place where you store both "raw" data (like files) and "organized" data (like tables). It combines the best of a File Cabinet and a Database..

Each layer is a lakehouse (typically named Bronze and Silver), and each lakehouse holds its tables in two schemas:

Schema	In Bronze	In Silver
dbo	Landing zone — the raw data that arrived from the source this run	Current-state record — materialized from Silver.his for consumer convenience
his	History — insert-only record of every change that ever landed	Cleaned history — type-converted, holds all records

Data flows through the platform as:

Bronze.dbo  →  Bronze.his  →  Silver.his  →  Silver.dbo
 (landing)     (history)      (cleaned       (current-state
                               history)       snapshot)

1. Source data lands in Bronze.dbo.
2. When keephistory: true (the default, used by the vast majority of objects), the history step compares the landing data with Bronze.his and appends only the changes.
3. Silver loads the cleaned, type-converted records from Bronze.his into Silver.his — the table that holds all records.
4. Silver.dbo is refreshed with the current state derived from Silver.his.

The rest of this page explains step 2 — how history tables are structured and maintained — using Bronze.his as the example. Silver.his uses the same SYSTEMSTATETIMESTAMP mechanism.

Structure

Location

• History tables live in the his schema of the layer's lakehouse (e.g. Bronze.his)
• Each history table corresponds to a landing table in Bronze.dbo that has history tracking enabled (keephistory: true)

Table Structure

History tables maintain the same structure as their corresponding Bronze.dbo landing table, with one additional column:

• SYSTEMSTATETIMESTAMP (bigint)
  • Format: YYYYMMDDHHMMSS (e.g., 20250104123025)
  • Negative values indicate deleted records
  • Used to track the exact moment of each change

How It Works

Timestamp Generation

• New/Updated Records: Positive timestamp (e.g., 20250104123025)
• Deleted Records: Negative timestamp (e.g., -20250104123025)

Process Flow

1. The landing table Bronze.dbo is loaded with the latest source data
2. The history step runs if keephistory: true for the table
3. The system compares Bronze.dbo with Bronze.his using the primary keys
4. Current state is determined by finding the latest timestamp for each primary key
   • Latest timestamp is determined by ordering absolute values
   • Negative timestamps indicate deleted records

Update Scenarios

New Records

• Key exists in Bronze.dbo but has no active record in Bronze.his
• Added to Bronze.his with the current positive timestamp

Updated Records

• Key exists in both Bronze.dbo and Bronze.his
• The system compares all columns for differences
• If differences are found, a new record is appended to Bronze.his with the current positive timestamp

Deleted Records

• Key is active in Bronze.his but absent from Bronze.dbo
• If the latest timestamp is positive, a new record is appended with a negative timestamp
• Indicates the record was deleted at that timestamp

note

Absence-based deletion detection is the default. When a column is marked isdeletedidentifier, deletions are driven by that soft-delete flag instead of absence, and history settings such as bronzeskipdelete or a delete-filter query change this behaviour.

Reactivated Records

• Key previously existed in Bronze.his, was deleted, and now appears in Bronze.dbo again
• Treated as a completely new record
• Previous history (including the deletion) remains intact
• A new positive timestamp marks the start of the new lifecycle

Example Timelines

Basic Update and Delete

Consider a record with ID=1:

SYSTEMSTATETIMESTAMP	ID	Name	Status
20250101090000	1	John	Active
20250102103000	1	John D.	Active
-20250103140000	1	John D.	Active

This timeline shows:

1. Record created on Jan 1, 2025
2. Name updated on Jan 2, 2025
3. Record deleted on Jan 3, 2025

Delete and Reactivation

Consider a record with ID=1 that is deleted and later reactivated:

SYSTEMSTATETIMESTAMP	ID	Name	Status	Department
20250101090000	1	John	Active	Sales
20250102103000	1	John D.	Active	Sales
-20250103140000	1	John D.	Active	Sales
20250401090000	1	John D.	Active	Marketing

This timeline shows:

1. Record created on Jan 1, 2025
2. Name updated on Jan 2, 2025
3. Record deleted on Jan 3, 2025
4. Record reactivated on Apr 1, 2025 with a new department
   • Treated as a new record
   • Previous history maintained
   • New positive timestamp indicates start of new lifecycle

Benefits

1. Complete Audit Trail

   • Every change is preserved
   • No data is ever overwritten
   • Deletion tracking maintained
   • Reactivation history clearly visible

2. Simple Implementation

   • Single timestamp column approach
   • Insert-only operations
   • No complex temporal tables required

3. Easy Querying

   • Current state can be determined by latest timestamp
   • Historical states accessible through timestamp filtering
   • Deletion status clear through timestamp sign

Best Practices

1. Primary Keys

   • Ensure stable primary keys
   • Required for accurate history tracking

2. Performance

   • Regular maintenance of history tables
   • Consider partitioning for large datasets

3. Querying

   • Always order by absolute timestamp value for accurate current state
   • Use timestamp ranges for point-in-time analysis

Common Queries

The examples below work on any history table (Bronze.his or Silver.his).

Get Current State

SELECT t.*
FROM his.table t
INNER JOIN (
    SELECT primary_key, MAX(ABS(SYSTEMSTATETIMESTAMP)) as max_ts
    FROM his.table
    GROUP BY primary_key
) latest ON t.primary_key = latest.primary_key
    AND ABS(t.SYSTEMSTATETIMESTAMP) = latest.max_ts

For Silver.his, the get_silver_snapshot helper does this for you:

from easyfabric import get_silver_snapshot

df = get_silver_snapshot("Silver.his.customers")
df = get_silver_snapshot("Silver.his.customers", include_deleted_rows=False)

Get State at Specific Time

SELECT t.*
FROM his.table t
INNER JOIN (
    SELECT primary_key, MAX(ABS(SYSTEMSTATETIMESTAMP)) as max_ts
    FROM his.table
    WHERE ABS(SYSTEMSTATETIMESTAMP) <= 20250101235959
    GROUP BY primary_key
) point_in_time ON t.primary_key = point_in_time.primary_key
    AND ABS(t.SYSTEMSTATETIMESTAMP) = point_in_time.max_ts

Note the ABS() in the WHERE clause: deletion timestamps are negative, so filtering on the raw value would incorrectly include deletions that happened after the point in time.