74 ) insurance project - data model explaination

  

• 1. Step 1 : clients problem : 

    Customer has 2 databases

         Oracle ( which stores the data about policties and agents ) 
           SSMS ( which stores the data about claims ) 

He is facing a problem to  query the claims and the policies data at the same time in a single query .. he has to verify in 2 databases. So he is looking for a data warehouse which can store both claims and policy data and can be queried. 

 

2. Designed the Dimension Tables (The Context)

I created shared lookup tables so we could slice and dice our data by anything:

• Dim_Customer: Kept a full history of customer details. If a customer moved to a new city, we tracked it properly.
• Dim_Agent: Linked every sale and claim back to the correct agent and branch.
• Dim_Date: Allowed the business to run reports easily by day, month, quarter, or year.
• Dim_Policy_Type: Categorized policies into Auto, Home, or Life insurance.

3. Designed the Fact Tables :  I built transaction and monthly snapshot tables to store all the numbers:

• Fact_Policy_Transactions: Stored every new policy created and every renewal, along with the total premium amount collected.
• Fact_Claim_Transactions: Stored every claim event and the exact payout amount given to the customer.

 

4. Built the Data Pipeline (ETL Strategy)

Since data was coming from Oracle and SQL Server, I designed an automated data pipeline:

• Extract & Load: Every night, automated scripts pull the data changes from the Oracle and SQL Server operational databases and load them into a staging area.
• 
  
• Transform: We cleaned the data—fixing mismatched IDs, formatting dates uniformly, and linking the Oracle policy numbers with the SQL Server claim numbers.
• 
  
• Load to Warehouse: Finally, the clean data populated our new dimension and fact tables so it was ready for the reporting team every morning.

2.5 ) How to select which Datawarehouse

----------------------------------------------------------------------------------------------------------------------

• Choose Snowflake for Speed and Concurrency: If you have hundreds of users running heavy queries at the exact same time and need automatic, instant scaling so nobody experiences slowdowns.
• Choose Google BigQuery for Unpredictable Data Volume: If you store petabytes of data but run queries randomly, allowing you to pay only for the exact gigabytes scanned without paying for idle servers.
• Choose AWS Redshift for Huge Data Lakes: If you have massive volumes of cheap, raw files sitting in Amazon S3 and want to query them directly without the time and cost of moving them.
• Choose Azure Synapse for All-in-One Data Tooling: If you want your visual data pipelines, Spark notebooks, and SQL warehouse bundled into one screen rather than buying separate tools.

---

1 . Snowflake features (Best Overall / Cloud-Agnostic)

Snowflake is widely considered the gold standard for data warehousing due to its decoupled storage and compute architecture.

• Why it fits: It can easily ingest data from both Oracle and SSMS using modern ETL/ELT tools (like Fivetran, Airbyte, or Stitch). or using in built (snowpipe ) 

•  

2 . comparison across 

• Snowflake, 
• Synapse, 
• BigQuery, and 
• AWS (Redshift) 

for those four core features: 

Recommended Recommendation to the Client:

• Propose Snowflake if they want maximum flexibility, ease of use, and independence from a single cloud provider.

• Propose Microsoft Fabric / Azure Synapse if their current infrastructure is heavily tied to Microsoft tools (SSMS, Azure).

Interview Questions and Answers: 

Fact Tables & Data Warehousing

Q1: Can't the client just run these reports directly on the OLTP database? Why do they even need a Data Warehouse (DWH)?

Answer:

If the client runs heavy reports on their live OLTP systems (like the Oracle Policy system or SQL Server Claims system), it will slow down or crash the daily operations for customer service agents. 

OLTP databases are built for fast, single-row inserts and updates (like entering one new policy at a time), not for scanning millions of rows to calculate monthly trends or agent performance. A Data Warehouse is needed because it is optimized for heavy, read-only analytical queries without impacting the live systems used by employees.

Q2: What if you have to merge, add, or delete columns or dimensions later? Can that be done easily?

Answer:

Yes, it can be done, but it requires careful planning depending on where the change happens. If we need to add a new dimension attribute (like adding a "Customer Credit Score" column to Dim_Customer), it is very easy—we just alter the dimension table and update our ETL script for future loads. If we need to change how a fact table is structured or completely delete a core metric, we have to rewrite the ETL logic and sometimes reprocess or reload historical data so that old reports do not break or show incorrect math.

Q3: What if the fact table data needs to be ingested on a weekly, monthly, or yearly basis instead of daily? How do you handle that?

Answer:

We handle this by configuring the scheduling and partitioning strategy in our ETL pipeline and Data Warehouse:

• Daily vs. Periodic Loads: While transaction fact tables (like claims) usually load daily, summary or snapshot fact tables (like monthly account balances) can be scheduled to run on the 1st of every month.

• Partitioning: We partition our large fact tables by date (such as by year or month). When a weekly or monthly batch job runs, it only targets that specific partition block rather than scanning the entire table, which keeps the data load fast and efficient.

Q4: How do you handle late-arriving facts? For example, a claim payout happened last Tuesday, but it only entered the system today.

Answer:

We handle late-arriving facts by ensuring our fact tables use the actual business date (the date the claim actually happened) rather than just the load date (the date it entered the database). When the late record arrives during our ETL run, our pipeline reads the transaction date, places it into the correct historical partition, and recalculates the aggregates for that past period so historical reports stay accurate.

Q5: What is a "Factless Fact Table," and did you use one in your insurance project?

Answer:

A factless fact table is a table that contains no numeric metrics or measurements; it only tracks events, relationships, or coverage conditions. Yes, we used one in our insurance project. We created a Fact_Policy_Coverage table to track which policies had specific optional coverages (like roadside assistance or flood insurance) attached to them. It had no monetary value inside it, but it allowed the business to run reports on how many customers opted for specific packages.

Q6: How do you handle updates or changes to Dimension attributes that are already linked to historical Fact table records? (Slowly Changing Dimensions)

Answer:

We handle this using Slowly Changing Dimension (SCD) Type 2. If a customer changes their home address or risk category, we do not overwrite the old row in Dim_Customer. Instead, we expire the old row by setting an end-date and status flag, and we insert a brand-new row with the updated details and a new surrogate key. This way, past claims and policy sales linked to that customer will always point to the customer's address and details as they looked at the time the transaction happened.

Q7: What is the difference between an Additive, Semi-Additive, and Non-Additive Fact? Can you give examples from your project?

Answer:

• Additive Facts: These can be summed up across all dimensions. In our project, Claim_Payout_Amount was fully additive—we could sum it up by agent, branch, month, or policy type.

• Semi-Additive Facts: These can be summed up across some dimensions, but not across time. In our project, Policy_Account_Balance or Outstanding_Reserve_Amount was semi-additive. You can add them up across different branches for a specific day, but you cannot add a month-end balance across all days of the year.

• Non-Additive Facts: These cannot be summed up at all. Ratios or percentages fall here, such as a Loss_Ratio. Instead of adding ratios together, the warehouse calculates them dynamically by dividing total claims by total premiums.

Q8: How do you deal with duplicate records coming from the source Oracle or SQL Server systems into your Fact table?

Answer:

We prevent duplicates at multiple stages:

1. Staging Checks: In our staging area, we write deduplication logic using window functions (like ROW_NUMBER()) to drop exact duplicate rows before they hit the warehouse.

2. Unique Business Keys: In our fact tables, we define natural keys or composite unique constraints (such as combining Policy_ID, Transaction_Date, and Transaction_Type) so that if the same daily file is accidentally loaded twice, the load fails or skips the duplicates.

Q9: How do you handle missing or unknown Dimension keys when a Fact record arrives? (For example, a claim comes in for an agent ID that doesn't exist in the system yet).

Answer:

We handle this by using a Default or "Unknown" Member in our dimension tables (usually assigned an ID of -1 or 0 with text like "Unknown Agent" or "Not Specified"). If a fact record arrives with an unrecognized foreign key, the ETL pipeline does not fail; instead, it automatically assigns the -1 default key. This ensures the row is still loaded into the fact table without breaking reports, and the data team is alerted to fix the missing master data.

Q10: If a business user asks to add a completely new metric to an existing Fact table that requires historical data we never tracked before, how do you handle it?

Answer:

If the business wants a new metric and we never captured the underlying source data in the past, we cannot magically generate historical data. I would explain this limitation clearly to the stakeholder. The correct approach is to update the ETL pipeline and source mapping moving forward so we capture that metric from today onward, and document clearly in the reports that historical data for this specific metric is unavailable prior to the implementation date.

\\ 2 .  Insurance project and how you build data model 

---

 

 

 

•  

---

•  

 

1.  STEPS TO DESIGN DATA MODEL

 

Insurance DWH Modeling Example (End-to-End)

1. Business KT Phase

Client says He wants to track: 

Requirements 

1. Policies sold per month

2. Agent commissions per month

3. Claims submitted vs claims approved

4. Profit/Loss based on Premium Received vs Claims Paid

---

2. Identify KPIs and KPI list validation

Business Need vs Expected KPI 

Requirements 

1. Policies sold per month

2. Agent commissions per month

3. Claims submitted vs claims approved

4. Profit/Loss based on Premium Received vs Claims Paid

3. KPI list Validation

Requirement mapping	KPI	aggregation	Approved
Policies sold	Policies Sold	Count of policies issued	Yes
Policies sold	Premium Received	Sum of premium payments	Yes
Agent performance	Commission Paid	Sum of commissions paid amt	Yes
Claims efficiency	Claims Submitted	Count of claims	Yes
c) Claims efficiency	Claims Approved	Count of approved claims	Yes
c) Claims efficiency	Claims Paid	Sum of approved claim amount	Yes
c) Claims efficiency	Claim Approval %	( Approved / Submitted )* 100	Yes
Profitability	Profit %	(Premium - Claims Paid) / Premium	Yes

4. Source Data Analysis

Sample data Files received from the client :

1 Policy File
Policy No		Agent	Customer	Premium	Issue Date
2 Payment File
Payment ID		Policy No	Amount	Payment Date
3 Claim File
Claim No		Policy No	Claim Amount	Status	Claim Date
4 Commission File
Commission ID	Policy no	Agent	Amount	Payment Date

 

4.1 ) source OLTP table list ( if provided )

3.1 OLTP Tables List (Operational Database)

ER diagram for OLTP tables :
Designed for high-concurrency inserts and updates (approx. 15–20 tables).

• Customers: Personal info, eligibility status.

• Agent 

• Agent Commission

• Policy Plans: Master product definitions, deductible logic.

• Policies: Contract records linking members to plans.

• Claims: Header information for service requests.

• Claim history

• Billing_Invoices: Aggregated financial charges.

• Payments: Transaction logs (Patient vs. Insurance).

5.  KPI  and fact tables

• Requirement 1: Policies Sold Per Month

• FACT_POLICY_SALE

• 
  

• Business Meaning → 

• Count how many new policies are issued each month, sliced by agent, product plan, and geography.

• Identify KPI →

•  (e.g., Policies Sold per Month = COUNT(Policy_ID)).

• Dimensions Required →

•  Date, Policy, Agent, Customer, ProductPlan, Geography.

• Resulting Table →

• Fact_Premium_Sales with

• Transaction_ID

• Date_Key

• Policy_Key

• Agent_Key

• Customer_Key

• ProductPlan_Key

• Geography_Key

• Premium_Amount

• Transaction_Type

• Payment_Status

• Payment_Mode

• Commission_Amount

• Transaction_Date

• Load_Date

• Source_System_ID

---

• Requirement 2: Agent Commission Per Month

• 
  

• Business Meaning → Track total commission earned by each agent monthly, linked to policies sold.

• Identify KPI → (e.g., Agent Commission per Month = SUM(Commission_Amount)).

• Dimensions Required → Date, Agent, Policy.

• 
  

• Resulting Table →

• Fact_Agent_Commission with

• Commission_ID

• Date_Key

• Agent_Key

• Policy_Key

• Commission_Percentage

• Commission_Amount

• Commission_Date

• Payment_Status

• Load_Date

Supporting Dimensions

• Dim_Date → Date_Key, Date, Month, Year

• Dim_Policy → Policy_Key, Policy_Number, Policy_Type, Sum_Assured

• Dim_Agent → Agent_Key, Agent_Name, Region

• Dim_Customer → Customer_Key, Customer_Name, DOB, Address

• Dim_ProductPlan → ProductPlan_Key, Plan_Name, Plan_Type

• Dim_Geography → Geography_Key, Country, City

6 .  Data modeling cycle flow

Requirement → KPIs → Grain → Fact Tables → Dimensions → Reports.

 

Simple flowchart of steps

 

Business Processes
    ↓
Requirement 

↓

 KPIs
    ↓
Approved KPI Definitions
    ↓
Source Data Analysis
    ↓
Facts & Dimensions
    ↓
Star Schema / Data Model

  

  Other facts 

Fact Tables (Metrics) - as per requirement

• Fact_Claims: Claims processed, denial rates, total billed vs. allowed amounts.

• Fact_Payments: Revenue collected, reimbursement turnaround times.

• Fact_Enrollment: Membership growth, churn rates, policy renewal frequency.

Dimension Tables (Filters)

• Dim_Date: Temporal analysis (Fiscal year, quarter, month).

• Dim_Member: Demographic segmentation (Age, region, plan type).

• Dim_Provider: Network status, specialty, geographical location.

• Dim_Plan: Product tier, coverage types.