Guidewire Tuesday - Edition - Insurance Claim Data
In General Insurance Claim Team Related Fact Dimension table with data mesh
Introduction
In the context of insurance claims, both fact and dimension tables can be managed using different types of Slowly Changing Dimensions (SCDs):
1. SCD Type 1: Overwrites old data with new data.
2. SCD Type 2: Maintains historical data by adding new rows for changes.
Example: Dimension Table for Insurance Claims
1. Dimension Table Using SCD Type 1
An SCD Type 1 dimension table for insurance claims only keeps the latest state of attributes, overwriting previous values when changes occur.
SQL Script for SCD Type 1 Dimension Table
```sql
-- Create a Dimension Table for Claims Adjuster (SCD Type 1)
CREATE TABLE IF NOT EXISTS dbo.AdjusterDimension_SCD1 (
????AdjusterKey INT IDENTITY(1,1) PRIMARY KEY,
????AdjusterID INT,
????AdjusterName NVARCHAR(100),
????AdjusterRegion NVARCHAR(50)
);
-- Insert or update AdjusterDimension_SCD1 table based on raw data
MERGE INTO dbo.AdjusterDimension_SCD1 AS target
USING dbo.RawAdjusterData AS source
ON target.AdjusterID = source.AdjusterID
WHEN MATCHED?
????THEN?
????????UPDATE SET?
????????????target.AdjusterName = source.AdjusterName,
????????????target.AdjusterRegion = source.AdjusterRegion
WHEN NOT MATCHED BY TARGET?
????THEN?
????????INSERT (AdjusterID, AdjusterName, AdjusterRegion)
????????VALUES (source.AdjusterID, source.AdjusterName, source.AdjusterRegion);
```
- How SCD Type 1 works: It updates the existing record in the dimension table when a change is detected, so the old value is overwritten, and no historical data is kept.
2. Dimension Table Using SCD Type 2
An SCD Type 2 dimension table maintains historical data by adding a new record when there is a change, while the old record is retained with an end date.
##### SQL Script for SCD Type 2 Dimension Table
```sql
-- Create a Dimension Table for Claims Adjuster (SCD Type 2)
CREATE TABLE IF NOT EXISTS dbo.AdjusterDimension_SCD2 (
????AdjusterKey INT IDENTITY(1,1) PRIMARY KEY,
????AdjusterID INT,
????AdjusterName NVARCHAR(100),
????AdjusterRegion NVARCHAR(50),
????StartDate DATE DEFAULT GETDATE(),
????EndDate DATE DEFAULT '9999-12-31',
????IsCurrent BIT DEFAULT 1
);
-- Merge data into AdjusterDimension_SCD2 with SCD Type 2 logic
MERGE INTO dbo.AdjusterDimension_SCD2 AS target
USING dbo.RawAdjusterData AS source
ON target.AdjusterID = source.AdjusterID AND target.IsCurrent = 1
WHEN MATCHED AND (
????????source.AdjusterName <> target.AdjusterName OR
????????source.AdjusterRegion <> target.AdjusterRegion
????)
????THEN?
????????UPDATE SET?
????????????target.EndDate = GETDATE(),
????????????target.IsCurrent = 0
WHEN NOT MATCHED BY TARGET?
????THEN?
????????INSERT (AdjusterID, AdjusterName, AdjusterRegion, StartDate, EndDate, IsCurrent)
????????VALUES (source.AdjusterID, source.AdjusterName, source.AdjusterRegion, GETDATE(), '9999-12-31', 1);
```
- How SCD Type 2 works: It adds a new record for every change, maintaining historical data, while marking the previous record as non-current.
Example: Fact Table for Insurance Claims
Fact tables typically contain measures, and they can reference dimension tables to provide context.
1. Fact Table Referencing SCD Type 1 Dimension Table
If the fact table references an SCD Type 1 dimension table, it will always reflect the most recent state of the dimension.
SQL Script for Fact Table with SCD Type 1 Dimension
```sql
-- Create a Fact Table for Insurance Claims (Referencing SCD Type 1)
CREATE TABLE IF NOT EXISTS dbo.ClaimsFact_SCD1 (
????ClaimID INT PRIMARY KEY,
????PolicyID INT,
????AdjusterKey INT,
????ClaimAmount DECIMAL(10, 2),
????ClaimDate DATE
);
-- Load fact data from raw claims data, referencing SCD1 Adjuster Dimension
INSERT INTO dbo.ClaimsFact_SCD1
????(ClaimID, PolicyID, AdjusterKey, ClaimAmount, ClaimDate)
SELECT?
????rc.ClaimID,
????rc.PolicyID,
????ad.AdjusterKey,
????rc.ClaimAmount,
????rc.ClaimDate
FROM?
????dbo.RawClaimsData rc
JOIN?
????dbo.AdjusterDimension_SCD1 ad ON rc.AdjusterID = ad.AdjusterID;
```
- Behavior: The fact table will have the latest dimension data, as SCD Type 1 dimension tables do not maintain history.
2. Fact Table Referencing SCD Type 2 Dimension Table
If the fact table references an SCD Type 2 dimension table, it reflects the state of the dimension at the time of the claim.
SQL Script for Fact Table with SCD Type 2 Dimension
```sql
-- Create a Fact Table for Insurance Claims (Referencing SCD Type 2)
CREATE TABLE IF NOT EXISTS dbo.ClaimsFact_SCD2 (
????ClaimID INT PRIMARY KEY,
????PolicyID INT,
????AdjusterKey INT,
????ClaimAmount DECIMAL(10, 2),
????ClaimDate DATE
);
-- Load fact data from raw claims data, referencing SCD2 Adjuster Dimension
INSERT INTO dbo.ClaimsFact_SCD2
????(ClaimID, PolicyID, AdjusterKey, ClaimAmount, ClaimDate)
SELECT?
????rc.ClaimID,
????rc.PolicyID,
????ad.AdjusterKey,
????rc.ClaimAmount,
????rc.ClaimDate
FROM?
????dbo.RawClaimsData rc
JOIN?
????dbo.AdjusterDimension_SCD2 ad?
????ON rc.AdjusterID = ad.AdjusterID AND ad.IsCurrent = 1 AND rc.ClaimDate BETWEEN ad.StartDate AND ad.EndDate;
```
- Behavior: The fact table reflects historical states of dimensions, using the appropriate version of the dimension data based on the claim date.
Summary of Differences
This approach maintains both current state (SCD 1) and historical state (SCD 2) for dimensions, and fact tables can be designed to reflect the most appropriate dimension state as per business requirements.?
create data mesh tables from dimension and fact tables
领英推荐
?
To create Data Mesh tables for insurance claims using both dimension and fact tables, you need to treat them as separate data products that represent claim-related data. Each of these tables will be domain-oriented and accessible for consumption via APIs or other interfaces, following the Data Mesh architecture.
Here’s how to create Data Mesh tables based on the previously defined SCD Type 1 and SCD Type 2 dimension and fact tables.
1. Create Data Mesh Table for Claims Adjuster Dimension (SCD Type 2)
The Data Mesh table will be created based on the SCD Type 2 Adjuster Dimension, which maintains historical changes.
SQL Script for Creating Data Mesh Table for Adjuster Dimension
```sql
-- Create the Data Mesh Table for Adjuster Dimension (SCD Type 2)
CREATE TABLE IF NOT EXISTS dbo.AdjusterDataMesh (
????AdjusterKey INT PRIMARY KEY,
????AdjusterID INT,
????AdjusterName NVARCHAR(100),
????AdjusterRegion NVARCHAR(50),
????StartDate DATE,
????EndDate DATE,
????IsCurrent BIT
);
-- Populate the Data Mesh Table for Adjuster Dimension from SCD Type 2 Dimension Table
INSERT INTO dbo.AdjusterDataMesh
????(AdjusterKey, AdjusterID, AdjusterName, AdjusterRegion, StartDate, EndDate, IsCurrent)
SELECT?
????AdjusterKey,
????AdjusterID,
????AdjusterName,
????AdjusterRegion,
????StartDate,
????EndDate,
????IsCurrent
FROM?
????dbo.AdjusterDimension_SCD2;
```
- AdjusterDataMesh: This table serves as the data product for the Adjuster domain, reflecting the complete history of adjusters, including the start and end dates for each record.
2. Create Data Mesh Table for Claims Fact Table (SCD Type 2)
The Claims Fact Data Mesh table will reference the historical state of the adjuster, capturing the claim's fact data along with the adjuster information.
SQL Script for Creating Data Mesh Table for Claims Fact
```sql
-- Create the Data Mesh Table for Claims Fact (Referencing SCD Type 2 Adjuster Dimension)
CREATE TABLE IF NOT EXISTS dbo.ClaimsFactDataMesh (
????ClaimID INT PRIMARY KEY,
????PolicyID INT,
????AdjusterKey INT,
????ClaimAmount DECIMAL(10, 2),
????ClaimDate DATE,
????AdjusterName NVARCHAR(100),
????AdjusterRegion NVARCHAR(50)
);
-- Populate the Claims Fact Data Mesh Table from the SCD Type 2 Claims Fact and Adjuster Dimension
INSERT INTO dbo.ClaimsFactDataMesh
????(ClaimID, PolicyID, AdjusterKey, ClaimAmount, ClaimDate, AdjusterName, AdjusterRegion)
SELECT?
????cf.ClaimID,
????cf.PolicyID,
????cf.AdjusterKey,
????cf.ClaimAmount,
????cf.ClaimDate,
????ad.AdjusterName,
????ad.AdjusterRegion
FROM?
????dbo.ClaimsFact_SCD2 cf
JOIN?
????dbo.AdjusterDimension_SCD2 ad?
????ON cf.AdjusterKey = ad.AdjusterKey?
????AND ad.IsCurrent = 1?
????AND cf.ClaimDate BETWEEN ad.StartDate AND ad.EndDate;
```
- ClaimsFactDataMesh: This table acts as the data product for the Claims domain, containing claim details along with the historical state of adjuster information, providing a complete view.
3. Create Data Mesh Table for Adjuster Dimension (SCD Type 1)
If the dimension is managed as SCD Type 1, then the Data Mesh table will always show the latest state of adjusters.
SQL Script for Adjuster Dimension Data Mesh (SCD Type 1)
```sql
-- Create the Data Mesh Table for Adjuster Dimension (SCD Type 1)
CREATE TABLE IF NOT EXISTS dbo.AdjusterDataMesh_SCD1 (
????AdjusterKey INT PRIMARY KEY,
????AdjusterID INT,
????AdjusterName NVARCHAR(100),
????AdjusterRegion NVARCHAR(50)
);
-- Populate the AdjusterDataMesh_SCD1 from the SCD Type 1 Dimension Table
INSERT INTO dbo.AdjusterDataMesh_SCD1
????(AdjusterKey, AdjusterID, AdjusterName, AdjusterRegion)
SELECT?
????AdjusterKey,
????AdjusterID,
????AdjusterName,
????AdjusterRegion
FROM?
????dbo.AdjusterDimension_SCD1;
```
- AdjusterDataMesh_SCD1: This table serves as the data product for the adjuster domain, representing only the most current state of adjusters without maintaining history.
4. Create Data Mesh Table for Claims Fact Table (SCD Type 1)
If the fact table references SCD Type 1 dimensions, it will only reflect the latest state of the dimension.
#### SQL Script for Claims Fact Data Mesh (SCD Type 1)
```sql
-- Create the Data Mesh Table for Claims Fact (Referencing SCD Type 1 Adjuster Dimension)
CREATE TABLE IF NOT EXISTS dbo.ClaimsFactDataMesh_SCD1 (
????ClaimID INT PRIMARY KEY,
????PolicyID INT,
????AdjusterKey INT,
????ClaimAmount DECIMAL(10, 2),
????ClaimDate DATE,
????AdjusterName NVARCHAR(100),
????AdjusterRegion NVARCHAR(50)
);
-- Populate the ClaimsFactDataMesh_SCD1 from the SCD Type 1 Claims Fact and Adjuster Dimension
INSERT INTO dbo.ClaimsFactDataMesh_SCD1
????(ClaimID, PolicyID, AdjusterKey, ClaimAmount, ClaimDate, AdjusterName, AdjusterRegion)
SELECT?
????cf.ClaimID,
????cf.PolicyID,
????cf.AdjusterKey,
????cf.ClaimAmount,
????cf.ClaimDate,
????ad.AdjusterName,
????ad.AdjusterRegion
FROM?
????dbo.ClaimsFact_SCD1 cf
JOIN?
????dbo.AdjusterDimension_SCD1 ad?
????ON cf.AdjusterKey = ad.AdjusterKey;
```
- ClaimsFactDataMesh_SCD1: This table acts as the data product for claims, reflecting only the current state of adjusters and other claim-related attributes.
Summary of Data Mesh Tables
This design aligns with the Data Mesh principles, treating each table as a data product that can be accessed independently, with well-defined APIs or data contracts.?
Read Full article here
Blog link here