Read Full article here Full Article
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
| Dimension Type | SCD Type 1 | SCD Type 2 |
|--------------------|-----------------------------------------------------|-----------------------------------------------------|
| Updates | Overwrites existing records | Adds new rows for changes, maintains history |
| Fact Table Link| Always references the latest dimension state | References the dimension state at the time of the fact event |
| Example | Adjuster’s name/region is updated without history | Adjuster’s name/region changes create new rows with validity periods |
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
| Data Mesh Table | Description |
|-----------------------------|------------------------------------------------------------------------|
| AdjusterDataMesh | Reflects historical data (SCD Type 2) for adjusters. |
| ClaimsFactDataMesh | Combines claim data with historical adjuster info (SCD Type 2). |
| AdjusterDataMesh_SCD1 | Reflects only the latest data (SCD Type 1) for adjusters. |
| ClaimsFactDataMesh_SCD1 | Combines claim data with the latest adjuster info (SCD Type 1). |
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.
Comments
Post a Comment
Share this to your friends