Using Delta Lake Time Travel to Reconstruct Historical Underwriting Decisions

Imagine receiving a request from an auditor:

Explain why customer Alice (with id CUST004) was approved for coverage 6 months ago.

The problem? Today’s customer profile looks completely different.

• Their credit score has deteriorated.
• Additional claims have been filed.
• Their risk grade has changed.
• The underwriting model has been refreshed multiple times.

Looking only at today’s data can lead to incorrect conclusions about yesterday’s decisions.

This is precisely why Delta Lake’s Time Travel capability is so powerful. Instead of relying on backup copies, snapshots, or manual archives, Delta Lake allows us to query the exact version of data that existed when the underwriting decision was made.

How Underwriting Decisions Changes Over Time?

Imagine CUST004 filed a claim and had a credit score change in past. Without Time Travel, analysts would only see the latest version (Version 5). But, with Time Travel, they can inspect previous versions (Version 3 & 4) too, or any version retained in the Delta transaction log.

Recap: Insurance Data Pipeline

In our previous blog, Creating a Single Risk Profile for Underwriters with Delta Lake, we explored how an Underwriting Risk Profile data pipeline can be built using Delta Lake. The solution leveraged the Medallion Architecture, organizing data across Bronze, Silver, and Gold layers, each serving a distinct purpose in transforming raw data into actionable underwriting insights.

Before diving into Delta Lake Time Travel, let’s briefly revisit that architecture and understand how each layer contributes to the creation of a comprehensive underwriting risk profile.

Bronze Layer

Raw source systems are ingested into Delta tables:

bronze.policy_raw
bronze.claim_raw
bronze.credit_raw

These contain:

• Policy information
• Claims history
• Credit scores

All stored as Delta tables.

Silver Layer

Policy Standardization

silver_policy

Functions: 

• Removes duplicate policies 
• Standardizes customer identifiers 

Claims Aggregation

silver_claims_summary

Calculates: 

• Total claims 
• Total claim amount 
• Most recent claim date

Credit Snapshot

silver_credit

Uses window functions to identify the latest credit score per customer.

Gold Layer

Customer Risk Base

gold.customer_risk_base

Combines: 

• Policy data 
• Claims history 
• Credit information 

into a unified underwriting view.

Risk Score Calculation

The solution calculates composite risk scores using: 

• Credit score 
• Claims history
• Coverage amount

Output table:

customer_risk_score

Example:

Risk Score	Risk Grade
85	LOW
72	MEDIUM
55	HIGH

Final underwriting profiles are stored in:

gold.underwriting_risk_profile

Visual: Risk Score Generation

Real-World Use Cases

Use Case #1: Regulatory Investigation

A regulator reviews a policy issued in 2026.

Current Situation

Metric	Current Value
Customer	CUST004
Credit Score	640
Risk Band	High

At Time of Underwriting

Metric	Historical Value
Credit Score	690
Risk Band	Medium

The regulator asks:

Why was this customer approved?

Time Travel provides the answer instantly:

SELECT * 
FROM gold.underwriting_risk_profile 
VERSION AS OF 3
WHERE customer_id = 'CUST004';

No backups, no database restores, no manual investigations.

Use Case #2: Model Validation

A model governance team deploys a new underwriting model. Six months later they want to compare – Old Model vs New Model using the exact same population.

SELECT *
FROM gold.customer_risk_score 
VERSION AS OF 4;

This enables reproducible analysis using historical data exactly as it existed when the prior model was active.

Use Case #3: Claims Dispute Investigation

A customer challenges a premium increase. The insurer must determine: 

• What claims existed? 
• What credit score was available? 
• What risk category was assigned? 

Using Time Travel:

SELECT * 
FROM gold.underwriting_risk_profile 
TIMESTAMP AS OF '2026-07-13';

This reconstructs the underwriting context exactly as it existed on that date.

Adding Time Travel to This Implementation

Because all layers are stored as Delta tables, Time Travel capabilities are immediately available.

Step 1: View Delta History

DESCRIBE HISTORY gold.underwriting_risk_profile;

Example: 

Version	Timestamp
5	2026-07-13
4	2026-03-09
3	2026-01-08

Step 2: Query a Historical Version

SELECT * 
FROM gold.underwriting_risk_profile 
VERSION AS OF 5;

Returns the table exactly as it existed at Version 5.

Step 3: Compare Current vs Historical State

Current:

SELECT * 
FROM gold.underwriting_risk_profile 
WHERE customer_id='CUST004';

Historical:

SELECT * 
FROM gold.underwriting_risk_profile 
VERSION AS OF 3
WHERE customer_id='CUST004';

Analysts can quickly identify how customer risk characteristics changed over time.

Sample Delta Lake Time Travel Queries

View Table History

DESCRIBE HISTORY gold.underwriting_risk_profile;

Version	Timestamp	User Name
6	2026-07-29T16:25:09.000Z	gupta.himanshu
5	2026-07-13T19:35:22.000Z	gupta.himanshu
4	2026-03-09T19:17:03.000Z	gupta.himanshu
3	2026-01-08T19:12:31.000Z	gupta.himanshu

Query a Specific Version

SELECT * 
FROM gold.underwriting_risk_profile 
VERSION AS OF 3;

Query a Historical Timestamp

SELECT * 
FROM gold.underwriting_risk_profile 
TIMESTAMP AS OF '2026-07-29 16:25:09';

Customer ID	Total Coverage	Total Claims	Credit Score	Risk Band	Underwriting Risk Grade	Profile Refresh TS
CUST018	275000	4	740	Medium	LOW	2026-07-29T16:25:06.227Z
CUST012	350000	4	710	Medium	MEDIUM	2026-07-29T16:25:06.227Z
CUST030	375000	4	600	Medium	MEDIUM	2026-07-29T16:25:06.227Z

Investigate a Single Customer

Current Profile:

SELECT * 
FROM gold.underwriting_risk_profile 
WHERE customer_id = 'CUST004';

Historical Profile:

SELECT * 
FROM gold.underwriting_risk_profile 
VERSION AS OF 6
WHERE customer_id = 'CUST004';

Customer ID	Total Coverage	Total Claims	Credit Score	Risk Band	Underwriting Risk Grade	Profile Refresh TS
CUST004	75000	6	640	High	MEDIUM	2026-07-29T16:25:06.227Z

Review Historical Credit Data

SELECT * 
FROM silver.credit 
VERSION AS OF 3 
WHERE customer_id = 'CUST006';

Audit Historical Claims Data

SELECT * 
FROM silver.claims_summary 
VERSION AS OF 3 
WHERE customer_id = 'CUST015'; 

Compare Two Versions Side-by-Side

WITH old_data AS (
    SELECT * 
    FROM gold.underwriting_risk_profile 
    VERSION AS OF 2 
), 
new_data AS ( 
    SELECT * 
    FROM gold.underwriting_risk_profile 
    VERSION AS OF 5 
) 
SELECT 
    n.customer_id, 
    o.risk_score AS old_risk_score, 
    n.risk_score AS new_risk_score 
FROM new_data n 
JOIN old_data o 
ON n.customer_id = o.customer_id 
WHERE n.risk_score <> o.risk_score;

How Does Delta Lake Time Travel Impact Storage?

One of the most common questions architects ask is:

If Delta stores historical versions, won’t storage costs explode?

The answer is generally No.

How Delta Stores Historical Data

Delta Lake does not create a full copy of a table every time data changes. Instead it maintains:

Only modified or newly created files are written. Unchanged data files are reused across versions.

Storage Example

Traditional Snapshots

Month	Storage
Month 1	1 TB
Month 2	1 TB
Month 3	1 TB
Total	3 TB

Delta Time Travel

Component	Storage
Base Data	1 TB
Month 2 Changes	50 GB
Month 3 Changes	40 GB
Total	1.09 TB

Delta is significantly more storage-efficient than maintaining complete snapshots.

The Real Storage Driver: Retention Period 

The primary factor affecting storage is Retention Duration

Examples: 

• 30 Days 
• 90 Days 
• 1 Year 
• 7 Years 

Insurance providers often require longer retention due to: 

• Regulatory mandates 
• Audit requirements 
• Model governance 
• Claims investigations

Managing Historical Data with VACUUM

VACUUM gold.underwriting_risk_profile;

Or

VACUUM gold.underwriting_risk_profile RETAIN 720 HOURS;

After VACUUM removes historical files, older versions are no longer available for Time Travel queries.

Time Travel + SCD Type 2 = Complete Auditability

The implementation already includes SCD Type 2 tracking using:

1. effective_start_date
2. effective_end_date
3. is_current
4. record_hash

Together, Delta Time Travel and SCD Type 2 provide a robust audit framework.

Capability	Delta Time Travel	SCD Type 2
Reconstruct Exact Table State	✅	❌
Track Attribute Changes	❌	✅
Built Into Delta Lake	✅	❌
Regulatory Investigations	✅	✅
Long-Term Historical Reporting	Limited by Retention	✅
Model Validation	✅	✅

Best Practice 

Use both:

• Time Travel provides operational and investigative history. 
• SCD Type 2 provides long-term business history. 

Together they deliver a complete underwriting audit trail.

In-Summary

For underwriting systems, the most important question is often not:

What does this customer look like today?

but rather:

What did this customer look like when the underwriting decision was made?

The solution described here already maintains policy, claims, credit, risk score, and underwriting profile data in Delta tables while preserving customer history through SCD Type 2 design. By leveraging Delta Lake Time Travel, insurance providers can: 

• Reconstruct historical underwriting decisions 
• Accelerate regulatory investigations 
• Validate machine learning models 
• Resolve customer disputes 
• Establish end-to-end auditability