Creating a Single Risk Profile for Underwriters with Delta Lake

In our previous blog, From Legacy Policy Systems to Lakehouse: Modernizing Insurance Underwriting Analytics with Delta Lake, we explored how insurance organizations can modernize fragmented policy administration systems by building a scalable Lakehouse architecture on Delta Lake.

However, today’s insurance providers have access to a rapidly growing ecosystem of information, including claims history, credit data, telematics, connected vehicle insights, IoT sensor data, and agent interactions. While each of these sources provides valuable risk signals, their true value emerges when they are combined into a single, comprehensive view of the customer.

This raises an important question:

How can insurance providers create a unified customer risk profile that gives underwriters an updated 360° view of risk?

In this blog, we’ll explore how Delta Lake can serve as the single source of truth for building a 360° customer risk view. We’ll examine how insurance providers can maintain historical risk profile changes using Slowly Changing Dimensions (SCDs) and deliver actionable insights to underwriters, claims teams, and fraud analysts from a unified Lakehouse platform.

The Need for a Unified Risk Profile

Traditionally, insurance data resides across multiple siloed systems:

• Policy administration platforms
• Claims management systems
• Third-party data providers

As a result, underwriters often spend significant time gathering and validating information before making risk decisions. For instance, a typical customer relationship looks like the following:

Each of these components contributes valuable insights into risk, but when stored separately, a lot of key points remains hidden.

Why Delta Lake Is Ideal for Single Source of Truth?

Delta Lake provides a modern data architecture capable of handling both batch and streaming data while maintaining data reliability and governance. For insurance providers, this means information from multiple systems can be continuously integrated into a central platform without sacrificing data quality.

Key benefits are:

1. Unified Data Storage: All customer-related data can be stored within a common data lake architecture, including – policy records, claims transactions, agent interactions, etc.
2. Reliable Data Consistency: Delta Lake’s ACID transaction support ensures that updates from various operational systems remain accurate and consistent.
3. Real-Time Risk Visibility: Delta Lake enables events, like weather-related exposure, claim alerts, etc. to be integrated rapidly into customer profiles, allowing underwriters and risk analysts to work with current information rather than historical snapshots.

Bringing Together Multiple Risk Signals

A robust customer risk profile combines data from multiple domains:

1. Customer Information: Core customer attributes like, demographics, occupation, tenure with the insurance provider, etc. establish a baseline risk characteristics.
2. Claims History: Historical claims often provide some of the strongest indicators of future risk.
3. Third-party information: Insurance providers leverage a lot of third-party information, like credit score, payment behavior, debt-to-income ratio, etc. to create a more nuanced assessment of risk.

Underwriting Risk Profile Pipeline

I. Bronze Layer – Raw Data Ingestion

The process begins with three raw source tables in the Bronze layer:

• bronze.policy_raw – policy information
• bronze.claim_raw – insurance claim transactions
• bronze.credit_raw – customer credit score (third-party) information

These tables contain unprocessed source data and serve as the foundation for downstream transformations.

II. Silver Layer – Data Standardization and Cleansing

This layer focuses on cleansing, standardizing, and preparing data for analytical consumption.

1. Policy Processing – The policy dataset undergoes customer ID standardization, null record removal, and duplicate policy removal.

silver_policy = (
    policy_raw
        .filter(F.col("customer_id").isNotNull())
        .withColumn(
            "customer_id",
            F.upper(F.trim(F.col("customer_id")))
        )
        .dropDuplicates(["policy_id"])
)

2. Claims Summary – Key metrics like total claims, total claim amount, last claim date, etc. are generated.

silver_claims_summary = (
    claim_raw
        .withColumn(
            "customer_id",
            F.upper(F.trim(F.col("customer_id")))
        )
        .groupBy("customer_id")
        .agg(
            F.count("*").alias("total_claims"),
            F.sum("claim_amount").alias("total_claim_amount"),
            F.max("claim_date").alias("last_claim_date")
        )
)

3. Credit Processing – Credit data is processed by standardizing customer IDs and applying a window function to identify the latest credit score for each customer.

credit_window = Window.partitionBy(
    "customer_id"
).orderBy(
    F.col("score_date").desc()
)

silver_credit = (
    credit_raw
        .withColumn(
            "customer_id",
            F.upper(F.trim(F.col("customer_id")))
        )
        .withColumn(
            "rn",
            F.row_number().over(credit_window)
        )
        .filter(F.col("rn") == 1)
        .drop("rn")
)

III. Gold Layer – Customer Risk Modeling

This generates business-ready risk metrics.

1. Customer Risk Base – Customer-level metrics such as average premium and total coverage is calculated.

customer_risk_base = (
    silver_policy.alias("p")
    .join(
        silver_claims_summary.alias("c"),
        "customer_id",
        "left"
    )
    .join(
        silver_credit.alias("cr"),
        "customer_id",
        "left"
    )
    .groupBy(
        "customer_id",
        "total_claims",
        "total_claim_amount",
        "credit_score",
        "risk_band"
    )
    .agg(
        F.avg("premium").alias("avg_premium"),
        F.sum("coverage_amount").alias("total_coverage")
    )
)

customer_risk_base = (
    customer_risk_base
    .fillna({
        "total_claims": 0,
        "total_claim_amount": 0
    })
)

2. Risk Score – A weighted risk model is formulated using the customer risk base.

customer_risk_score = (
    customer_risk_base
    .withColumn(
        "risk_score",
        (
            (F.col("credit_score") / F.lit(850.0)) * 40
            +
            (
                1 -
                (
                    F.least(
                        F.col("total_claim_amount"),
                        F.lit(50000)
                    ) / F.lit(50000)
                )
            ) * 30
            +
            (
                1 -
                (
                    F.least(
                        F.col("total_coverage"),
                        F.lit(1000000)
                    ) / F.lit(1000000)
                )
            ) * 20
            +
            10
        )
    )
    .select(
        "customer_id",
        "risk_score"
    )
)

3. Risk Profile – Finally a risk profile, using customer risk base and risk score, is generated.

gold_underwriting_risk_profile = (
    customer_risk_base.alias("b")
    .join(
        customer_risk_score.alias("r"),
        "customer_id"
    )
    .withColumn(
        "underwriting_risk_grade",
        F.when(F.col("risk_score") >= 80, "LOW")
         .when(F.col("risk_score") >= 60, "MEDIUM")
         .otherwise("HIGH")
    )
    .withColumn(
        "profile_refresh_ts",
        F.current_timestamp()
    )
    .select(
        "customer_id",
        "avg_premium",
        "total_coverage",
        "total_claims",
        "total_claim_amount",
        "credit_score",
        "risk_band",
        "risk_score",
        "underwriting_risk_grade",
        "profile_refresh_ts"
    )
)

IV. Managing Risk Profile Evolution with SCD

Customer risk is not static. For instance,

• A driver’s behavior may improve.
• A homeowner may install smart safety devices.
• A customer’s financial profile may strengthen over time.

To capture such changes accurately, insurers can leverage Slowly Changing Dimensions (SCD Type 2) within Delta Lake. Rather than overwriting historical information, SCD maintains a complete audit trail of customer profile changes.

# Add SCD Metadata
incoming_profile = (
    gold_underwriting_risk_profile
    .withColumn(
        "record_hash",
        F.sha2(
            F.concat_ws(
                "||",
                F.col("avg_premium").cast("string"),
                F.col("total_coverage").cast("string"),
                F.col("total_claims").cast("string"),
                F.col("total_claim_amount").cast("string"),
                F.col("credit_score").cast("string"),
                F.col("risk_band"),
                F.col("risk_score").cast("string"),
                F.col("underwriting_risk_grade")
            ),
            256
        )
    )
    .withColumn(
        "effective_start_date",
        F.current_timestamp()
    )
    .withColumn(
        "effective_end_date",
        F.lit(None).cast("timestamp")
    )
    .withColumn(
        "is_current",
        F.lit(True)
    )
)

# Existing Dimensions
target_table = "gold.underwriting_risk_profile_scd"
table_exists = spark.catalog.tableExists(target_table)

# Initial Load
if not table_exists:
    incoming_profile.write \
        .format("delta") \
        .mode("overwrite") \
        .saveAsTable(target_table)
# SCD Merge Logic
else:
    dim_table = DeltaTable.forName(
        spark,
        target_table
    )

    current_dim = (
        spark.table(target_table)
        .filter(F.col("is_current") == True)
    )

# Identify Changed Records
changed_records = (
    incoming_profile.alias("src")
    .join(
        current_dim.alias("tgt"),
        "customer_id"
    )
    .filter(
        F.col("src.record_hash")
        !=
        F.col("tgt.record_hash")
    )
    .select("src.*")
)

# Expire Existing Versions
dim_table.alias("tgt").merge(
    changed_records.alias("src"),
    """
    tgt.customer_id = src.customer_id
    AND tgt.is_current = true
    """
).whenMatchedUpdate(
    condition=
    """
    tgt.record_hash <> src.record_hash
    """,
    set={
        "effective_end_date": "current_timestamp()",
        "is_current": "false"
    }
).execute()

# Insert New Versions
new_versions = (
    changed_records
    .withColumn(
        "effective_start_date",
        F.current_timestamp()
    )
    .withColumn(
        "effective_end_date",
        F.lit(None).cast("timestamp")
    )
    .withColumn(
        "is_current",
        F.lit(True)
    )
)

# Insert new customers
new_customers = (
    incoming_profile.alias("src")
    .join(
        current_dim.alias("tgt"),
        "customer_id",
        "left_anti"
    )
)

For instance,

The table presents a historical view of customer risk assessments, capturing how a customer’s (like CUST028 & CUST027) risk profile evolves over time as underlying dimensions, particularly credit score, change.

Each record represents a versioned risk profile with an effective time period, with the current active profile identified by is_current = TRUE. This way SCD helps in retaining historical records along with validity period (start_date & end_date), enabling full auditability of risk changes.

V. Benefits

• Track customer risk evolution
• Support regulatory audits
• Analyze historical underwriting decisions

By preserving history, insurance providers gain a deeper understanding of how risk changes throughout a customer’s tenure.

Full pipeline code can be found here – lakeflow-insurance-underwriting.

In Summary

As insurance organizations continue to embrace data-driven decision-making, the ability to create a single, trusted customer risk profile becomes a critical competitive advantage.

By consolidating customer information, claims history, credit data, telematics, and IoT signals into Delta Lake, insurers can establish a true 360° view of risk. Combined with Slowly Changing Dimensions (SCDs), this approach preserves the evolution of customer behavior while providing underwriters with accurate, timely, and actionable insights.

The result is smarter underwriting, faster decisions, and a more resilient insurance enterprise built on a modern data foundation.

However, understanding the current state of customer risk is only part of the story. In highly regulated insurance environments, organizations must also be able to explain and reproduce the data that drove a specific underwriting decision at a given point in time.

In upcoming blog(s), we’ll explore how Delta Lake Time Travel enables insurance providers to reconstruct historical underwriting decisions, audit risk assessments, and gain deeper visibility into how customer risk profiles evolved over time. So stay tuned 🙂