74 ) insurance project - data model explaination

 Here is a detailed, step-by-step interview answer in simple, natural English, following the exact structure you requested without complex corporate buzzwords.

Step 1: The Client's Existing System and Tables

In my previous insurance project, the client was using two completely different systems to run their daily business operations:

• Policy System (Oracle Database): This handled all customer details, agents, and active insurance policies. The main tables were:

  • Customer_Master: Stored personal details, addresses, and contact info.

  • Policy_Details: Stored policy numbers, start dates, end dates, and premium amounts.

  • Agent_Master: Stored agent IDs, commission percentages, and branch locations.

• Claims System (SQL Server Database): This handled all the money paid out for damages or accidents. The main tables were:

  • Claim_Master: Stored claim numbers, dates when accidents happened, and the policy number it was linked to.

  • Claim_Transactions: Stored the actual money paid out, repair costs, and claim status (like Approved, Rejected, or Pending).

Step 2: The Problems They Faced

Because policy data and claims data lived in two separate databases, the business teams faced major roadblocks every day:

• Data was split into silos: If managers wanted to see which specific agent brought in policies that later had the highest number of claims, they couldn't do it easily.

• Manual and slow reporting: Every month, data people had to pull manual exports from Oracle and SQL Server into local Excel files and MS Access databases. It took days just to merge these files together.

• Zero historical tracking: If a customer updated their phone number or changed their coverage plan, the old system just overwrote the old data. They had no way to look back and see what the policy looked like six months ago.

Step 3: The Client's Expectation and Why

• The Goal: They wanted a single, centralized data warehouse where policy data and claims data lived together in one place.

• The Reason: They needed fast, automatic reports to track monthly policy renewals, spot high claims payouts quickly, and check agent performance without waiting on manual Excel exports.

Step 4: The Solution Design I Proposed

To solve these problems, I designed a modern, centralized dimensional data model (often called a Star Schema) using cloud storage and a data warehouse. Here are the exact steps I followed to design it:

1. Defined the Business Process First

Instead of just copying tables over, I sat down with business users to understand what they wanted to measure. We decided our main focus areas (fact tables) would be Policy Sales/Renewals and Claim Payouts, while everything else would be descriptive details (dimension tables).

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 (The Numbers & Events)

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 pulled data changes from the Oracle and SQL Server operational databases and loaded 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.

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.