229 ) If you have 500 million rows and queries filtering by Customer ID or Order ID are slow, what would you consider for performance?

 If you have 500 million rows and queries filtering by Customer ID or Order ID are slow, what

would you consider for performance?

228 ) oltp data model, enterprise data model, dimensional model differences

 

228 ) oltp data model, enterprise data model, dimensional model differences

Here is how you design the data models for life insurance policy sales across the three architectural tiers:

1. OLTP Data Model (Operational / Source System)

• Goal: Optimized for fast, transactional day-to-day data entry (e.g., core policy administration systems where agents sell policies online or via call centers). It uses 3rd Normal Form (3NF) to avoid data redundancy.
  
  
• Core Tables & Relationships:
  
  
  • Customers (CustomerID, Name, DOB, SSN, Address)
    
    
  • Agents (AgentID, AgencyID, Name, CommissionRate)
    
    
  • Products (ProductID, PolicyTypeName [Term, Whole Life], Description, BasePremium)
    
    
  • Policies (PolicyID, CustomerID, AgentID, ProductID, EffectiveDate, ExpirationDate, Status)
    
    
  • Policy_Coverages (CoverageID, PolicyID, CoverageAmount, PremiumAmount)
    
    
  • Transactions (TransactionID, PolicyID, PaymentDate, AmountPaid, PaymentType)
    
    

2. Enterprise Data Model (EDM / Canonical Layer)

• Goal: A comprehensive, organization-wide conceptual and logical model that integrates multiple source systems (e.g., merging sales from web portals, direct agents, and corporate partners). It acts as an enterprise blueprint mapping master data domains.
  
  
• Core Subject Areas:
  
  
  • Party Domain: Manages Person or Organization roles (Customer, Beneficiary, Agent, Underwriter).
    
    
  • Product Domain: Abstract catalog definitions for insurance offerings, riders, and investment-linked portfolios.
    
    
  • Agreement / Contract Domain: Master lifecycle tracking of the insurance contract, binding rules, limits, and legal obligations.
    
    
  • Financial / Event Domain: Captures monetary interactions, billing schedules, and premium receipts across all channels.
    
    

3. Dimensional Model (Data Warehouse / Gold Layer)

• Goal: Optimized for fast analytical reporting, business intelligence dashboards, and sales performance tracking (e.g., slicing sales by agent, region, product type, and time). Uses a Star Schema.
  
  

Fact Table: Fact_Policy_Sales

• Grain: One row per policy sold / issued (or per policy coverage line item).
  
  
• Foreign Keys (Dimensions):
  
  
  • Date_SK (Sale Date)
    
    
  • Customer_SK
    
    
  • Agent_SK
    
    
  • Product_SK
    
    
  • Branch_SK (Sales Region/Office)
    
    
• Measures (Metrics):
  
  
  • Face_Amount (Total coverage value)
    
    
  • Annual_Premium_Amount (APV)
    
    
  • First_Year_Commission
    
    
  • Policy_Count (Value: 1)
    
    

Dimension Tables:

• Dim_Customer (SCD Type 2: Tracks history of addresses or risk ratings)
  
  
• Dim_Agent (SCD Type 1/2: Tracks agent hierarchy, agency, and commission tier)
  
  
• Dim_Product (SCD Type 1: Policy Name, Type, Term Length, Riders included)
  
  
• Dim_Date (Calendar attributes: Year, Quarter, Month, Day, IsWeekend)