Enterprise Data Model (EDM)
is the 'Source of Truth' that defines how an organization’s data fits together. It’s the blueprint that prevents different departments from using conflicting definitions for the same business concepts.
Eg . Insurance project
The Core Difference: EDM vs. OLTP Tables
The EDM (The Blueprint): It doesn't have "tables." It has Entities (like Policy, Claim, Party, Coverage). It defines the "what" and the "why"—it’s a business-level map that ensures everyone defines a "Policy" the same way, regardless of the system.
The OLTP Tables (The Implementation): These are the physical database tables (like T_PLCY_HDR, T_CLM_DTL) that store real-time data. They are highly normalized (usually 3rd Normal Form) to ensure speed and data integrity when an agent is typing in a new claim.
Scribd
2. Concrete Example: Insurance System
If you are asked, "What tables are in the OLTP, and what is in the EDM?", give this answer:
"In the EDM, I don't look at table names; I look at core Business Entities." For example:
Entity Party: Defines a person or organization (could be a customer, an agent, or a beneficiary).
Entity Policy: The contract between the insurer and the party.
Entity Claim: The request for coverage after a loss event.
"In the OLTP system, that same model translates into specific physical tables designed for high-speed performance."
T_POLICY_MASTER: Stores the contract ID, effective date, and status.
T_POLICY_COVERAGE: A child table linked via POLICY_ID to store the specific risks covered (e.g., Fire, Theft).
T_CLAIM_EVENT: Captures the timestamp and location of the loss.
T_CLAIM_PAYMENT_LOG: A transaction table that records every single payout, ensuring ACID compliance so money isn't paid twice.
ThoughtSpot
3. The Process: How to Begin the EDM
Don't say "we started coding." Say: "We started with the business domain, not the database."
Scope the Domain: Identify the business areas (e.g., Policy Administration, Claims, Billing).
MediumDefine Business Entities: Bring stakeholders together (Underwriting vs. Claims) to agree on definitions. Use an industry standard like ACORD as a starting point to avoid reinventing the wheel.
Map Relationships (Conceptual Layer): Create a high-level diagram showing how Party relates to Policy and how Policy relates to Claim.
Logical Modeling: Expand these into specific attributes (e.g., Policy_Number, Claim_Amount) and apply business rules (e.g., "A claim cannot be filed unless the policy is active").
ThoughtSpotPhysical Design (The OLTP part): Only after the logical model is approved do we decide on the database schema. We map the entities to physical tables, choose data types (e.g., VARCHAR, DECIMAL), and set indexes to keep the system fast.
ThoughtSpot
Summary for your interview: "The EDM is the dictionary for the company. The OLTP is the library where we actually store the data. I build the dictionary first so that when we build the library, everyone knows exactly where to put the books and how to label them."
Key Differences
Senior Data Architect would answer this standard behavioral interview question.
Scenario 1 : insurance
Scenario: Implementing an EDM for Life Insurance Provider
The Client:
A major multinational insurance provider offering personal auto, commercial property, and group life insurance products.
The Challenge:
The insurance company bought many smaller companies over many years. Because of this, different types of insurance (like car, home, and life) were managed on different computer systems that could not talk to each other.
This caused two major problems:
Bad Pricing: The team setting the prices could not see a customer's whole history. They did not know if a person was a safe driver or a risky property owner, so they could not price the insurance accurately.
Lost Money to Fraud: Dishonest people were tricking the company. A fraudster could lie about a car theft in one system and a house fire in another system at the same time. The company lost millions of dollars because the claims teams could not see these matching patterns.
1. The Existing Architecture (Before EDM)
The architecture was split by product line, with each line operating as an independent company:
Personal Auto: Handled by a very old mainframe computer system (COBOL/DB2) that processed data in big batches only once a night.
Commercial Property: Managed via a standard vendor software package (Guidewire PolicyCenter) backed by an on-premises Microsoft SQL Server database.
Life & Annuities: Run on a separate legacy computer infrastructure (AS400) that saved and exchanged data using basic flat text files.
Claims Processing: Run on a completely separate, old claims platform that could not talk to the underwriting or policy systems.
Data Integration: Hundreds of messy, custom code pathways (SSIS packages and scripts) moved data directly into isolated, localized reporting databases.
2. Issues Faced with the Existing Architecture
Inability to Calculate "Total Insured Value" (TIV): If a corporate client bought car, property, and life insurance, the executive board could not easily calculate the total amount of financial risk the company held with that one client.
Leaking Claims to Fraud: Dishonest people were tricking the company. A fraudster could file a fake car theft claim in one system and a fake house fire claim in another system at the same time. The claims teams lost millions of dollars because they had no shared system to see these matching patterns.
Slow Time-to-Market for New Products: Launching a bundled insurance product (like giving a discount for buying both car and home insurance together) took over 18 months because it required manually coding data bridges between different backend software engines.
3. How Requirements Were Captured
We conducted an intensive 10-week structural modeling and discovery phase:
Framework Alignment: We leveraged a famous global standard for insurance definitions called the ACORD Framework. This gave us a ready-made list of common insurance words and rules so everyone used the same vocabulary.
Data Lineage Mapping: We used automated software tools to trace data backward. We followed it from the final financial reports all the way back to the initial paper or digital forms filled out by customers.
Defining the "Party" Concept: The hardest part was mapping roles. An individual could be a policyholder on a car policy, a driver on a spouse’s policy, a claimant making a claim, and a beneficiary on a life policy. We designed a universal Party entity in the model to separate the person's true identity from their temporary situational role.
4. How the Solution Was Designed
We designed a centralized, regulatory-compliant Data Lakehouse on AWS utilizing an extended Inmon Corporate Information Factory (CIF) architecture to handle historical depth and strict auditability.
[Mainframe Auto DB2] ---\
[Guidewire Property] ----> [ AWS Glue / EMR ] ---> [ Amazon S3 Lakehouse (Delta Format) ] ---> [ Financial Reporting ]
[AS400 Life Files] -----/ (ACORD-Aligned EDM) (Core Corporate Model Layer) [ Actuarial Modeling ]
Ingestion Layer: Built using AWS Glue and Apache Spark tools to pull data continuously from relational tables and parse complex, fixed-width mainframe flat files.
Storage & Core EDM Layer: Implemented inside Amazon S3 using Delta Lake format. The core layer was modeled using a 3rd Normal Form (3NF) relational structure to ensure absolute data accuracy for corporate accounting and strict regulatory laws (like IFRS 17 / Solvency II).
Core Entities: Designed around five core pillars: Party (Who), Policy (What contract), Risk Unit/Object (What item is insured), Claim (What accident happened), and Premium/Financial Transaction (The money).
Access Layer: Transformed the relational 3NF core into highly optimized, dimensional data marts (Star Schema charts) so business analysts and math experts could easily read them and build reports.
5. Changes Suggested by the Client
During our architectural and executive steering reviews, the client’s internal risk and compliance boards required major shifts in the layout:
Mandatory "As-Of" Bitemporal Modeling: The math experts and legal teams rejected a standard history tracking model. In insurance, you must know what the data looked like on the exact day an accident happened, as well as what changes were applied later. We had to refactor the entire EDM to support Bitemporal Logging (tracking both transaction time and valid business time for every row).
External Data Integration: The underwriting division requested an immediate extension to the core model. They wanted placeholders to add outside data streams (like geographic flood zone maps, vehicle history reports, and credit scores) directly into the core Risk Unit entity rather than keeping them in a separate system.
Strict Column-Level Masking for Medical Data: The life insurance division handled highly confidential medical histories, which fell under strict privacy laws. The security compliance team forced us to split the Party data so that medical diagnostics were stored in a separately encrypted table, visible only to authorized medical underwriters via dynamic column-level security.
Scenario 2: Retailer
Implementing an EDM for a Global Retailer
The Challenge: A global retail client had data siloed across an e-commerce platform, a legacy brick-and-mortar ERP system, and a third-party supply chain tool. They could not accurately track a single customer's lifetime value or get a real-time view of global inventory.
The Approach:
Conceptual Phase: We identified the core business entities: Customer, Product, Order, and Location.
Logical Phase: We standardized definitions. For example, "Revenue" meant gross sales in the app model, but we standardized it as net sales after discounts in the EDM.
Physical Phase: We mapped these entities into a centralized cloud data warehouse (Snowflake) using a Data Vault 2.0 architecture to allow scalability. [1]
The Result: The EDM eliminated 40% of redundant data pipelines. It reduced monthly financial reporting times from 12 days to 2 days by providing a single source of truth.
what were the business processes for that client
For the global retail client scenario, we mapped the Enterprise Data Model (EDM) across four core end-to-end business processes. Standardizing these processes was critical because data crossed multiple legacy systems at each step.
1. Procure-to-Pay (Supply Chain & Inventory)
This process managed how the company bought and tracked goods before they reached consumers.
The Workflow: Vendor onboarding, purchase order creation, goods receiving at warehouses, and supplier invoice settlement.
EDM Impact: We standardized the Product and Supplier entities. This stopped the warehouse system and the finance system from using different ID numbers for the exact same item. [1, 2, 3, 4]
2. Order-to-Cash (Omnichannel Sales)
This process covered everything from a customer placing an order to the company recognizing revenue. [1, 2, 3]
The Workflow: Customer checkout (online or in-store), payment processing, order fulfillment, shipping, and returns/refunds.
EDM Impact: We created a unified Order entity. This combined online web sales (NoSQL database) and brick-and-mortar register sales (relational SQL database) into one single format. [1, 2, 3, 4]
so for these processes
Scenario 1 : Retail data model
what was the existing architecture ?
what issues was he facing with existing architecture?
how did you capture the requirement list?
How did you design the solution to the client and what changes did the client suggest?
1. The Existing Architecture (Before EDM)
The client operated on a highly fragmented, decentralized architecture built up over a decade of rapid growth and corporate acquisitions. It consisted of three major disconnected silos:
Brick-and-Mortar Retail: Run on a legacy, on-premises SAP ERP system using a traditional relational database (Oracle).
E-Commerce: A modern web storefront running on AWS, utilizing a PostgreSQL database for transactions and MongoDB (NoSQL) for product catalogs.
Supply Chain & Logistics: Managed by a third-party SaaS platform (Blue Yonder) with its own isolated cloud database.
Data Integration: Dozens of fragile, point-to-point ETL (Extract, Transform, Load) scripts and nightly batch file transfers (SFTP) moved data between systems. There was no centralized data warehouse. [1]
2. Issues Faced with the Existing Architecture
Data Friction & Delays: Data was synchronized only once every 24 hours. Inventory levels on the website were constantly inaccurate, leading to customers ordering out-of-stock items.
Split Customer Personas: A customer buying a jacket online and returning it in-store could not be tracked as the same person. The e-commerce system used email as an ID, while the physical store used a loyalty card number.
Conflicting Financial Metrics: The finance team reported revenue based on the ERP ledger. The marketing team reported revenue based on e-commerce checkout logs. The two numbers never matched due to different definitions of refunds and discounts.
High Maintenance Costs: Changing a single column or product attribute in one system broke multiple downstream point-to-point data pipelines.
3. How Requirements Were Captured
We used a hybrid top-down (business strategy) and bottom-up (data realities) approach over a 6-week discovery phase:
Event Storming Workshops: We put business leaders from finance, logistics, and sales in a room to visually map out their workflows using sticky notes. This helped us identify major business events (e.g., "Order Placed," "Inventory Deducted").
Data Profiling: We ran automated SQL queries against the existing Oracle and PostgreSQL databases to look at the actual data. This revealed hidden issues, like columns labeled "Customer_ID" containing text in one system and numbers in another.
Glossary Building: We forced stakeholders to agree on single business definitions. We documented these in an enterprise data dictionary (using a tool like Collibra) before writing any code. [1]
4. How the Solution Was Designed
We designed a modern, centralized data architecture using a hub-and-spoke pattern: [1]
[Legacy ERP] -----\
[E-Commerce] -----> [ Kafka (Real-Time Ingestion) ] ---> [ Snowflake Cloud Data Warehouse ] ---> [ Power BI / Analytics ]
[Supply Chain] ---/ (Data Vault 2.0 Core EDM)
Ingestion Layer: We replaced nightly batches with Apache Kafka for real-time Event-Driven ingestion.
Storage & Core EDM Layer: We built a centralized cloud data warehouse on Snowflake. We used the Data Vault 2.0 modeling pattern because it splits data into:
Hubs: Unique business keys (e.g., Global Customer ID).
Links: Transactions and relationships (e.g., Customer purchased Product).
Satellites: Descriptive data that changes over time (e.g., Customer Address history). [1, 2]
Access Layer: We created clean, user-friendly star-schema data marts (Fact and Dimension tables) on top of the Data Vault so business analysts could easily build reports. [1]
5. Changes Suggested by the Client
During our design review workshops, the client's leadership and engineering teams requested three major adjustments to our initial proposal:
Phase out Real-Time for Finance: We originally proposed streaming all financial transactions in real-time. The CFO rejected this, stating that financial data must only be updated and verified in batches at the end of the day to ensure reconciliation control. We pivoted to a hybrid architecture: real-time for inventory/sales, batch for finance.
Softer Migration Path: The IT team refused a "big bang" switchover because they feared disrupting daily store operations. They requested we build the EDM alongside the old systems, running them in parallel for 3 months to validate data accuracy before turning off the old pipelines.
Stricter Data Privacy Controls: The legal team noted that our master customer model mixed European and American customer data in the same cloud storage region. They forced us to implement dynamic row-level security and data masking to comply with GDPR regulations
----
MORE EXAMPLES
1. Banking
Party: Represents any person or organization interacting with the bank.
Examples: Customer, Employee, Beneficiary, Guarantor.
Product: Represents the financial offerings managed by the bank.
Examples: Loan (Mortgage/Personal), Fixed Deposit (FD), Current Account, Savings Account, Credit Card.
Agreement: Represents the contractual link between a Party and a Product.
Examples: Loan Contract, Account Opening Agreement.
Event/Transaction: Represents the movement or status change of money.
Examples: Deposit, Withdrawal, Fund Transfer, Interest Accrual.
2. Healthcare
Party: Represents all human participants in the medical ecosystem.
Examples: Patient, Physician, Nurse, Specialist, Administrator.
Clinical Object: Represents the medical conditions or treatment assets.
Examples: Diagnosis, Medication, Procedure, Lab Test, Vaccine.
Encounter: Represents the context or "session" of care.
Examples: Outpatient Visit, Emergency Room Admission, Telehealth Consultation, Surgery.
Facility: Represents the physical or operational location.
Examples: Hospital Ward, Operating Theater, Pharmacy, Clinic.
3. Retail
Party: Represents the individuals and companies involved in the supply/demand chain.
Examples: Customer, Vendor, Store Manager, Delivery Driver.
Product: Represents the items offered for trade.
Examples: SKU (Stock Keeping Unit), Gift Card, Subscription Service, Bundle.
Order: Represents the demand for products.
Examples: Sales Order, Purchase Order (from supplier), Return Order, Backorder.
Inventory/Location: Represents the physical placement and availability.
Examples: Warehouse Shelf, Distribution Center, Physical Storefront, Online Inventory.
4. Finance (Corporate)
Party: Represents the legal and operational entities.
Examples: Subsidiary, Shareholder, Regulatory Body, Auditor.
Financial Instrument: Represents the asset or liability definition.
Examples: Bond, Equity, Derivative, Treasury Bill.
Accounting Unit: Represents the structures for financial tracking.
Examples: Cost Center, Profit Center, General Ledger Account, Department.
Financial Event: Represents the movement of value.
Examples: Journal Entry, Dividend Payment, Asset Depreciation, Tax Filing.
How to use this in your interview
When you group them this way, you show the interviewer that you understand Data Architecture.
You can say: "In my experience building an EDM, the first step is to categorize all business data into Subject Areas. For example, in a Banking model, I group everything under high-level entities like Party (which covers customers, employees, and account holders), Product (which covers loans, FDs, and savings), and Events (which covers transactions). This makes the entire model modular—if the business adds a new product, I don't have to redesign the whole model; I just add a new instance under the existing Product subject area."
Enterprise Data Modeling Specification: Integrated Insurance Management Ecosystem
Version: 1.0 (Enterprise Standard)
Domain: Property & Casualty (P&C), Life Insurance, Actuarial Risk, and Claims Processing
Classification: Confidential - Technical Architecture Document
Phase 1: Discovery & Requirements (Underwriting & Claims Logic)
The insurance domain requires a highly resilient data structure capable of managing the long-term lifecycle of a policy—from initial quote to multi-year claims handling. Unlike retail, data integrity in insurance must account for policy amendments (endorsements) and complex risk assessment variables.
Phase 2: Source System Analysis (Legacy Core Migration)
Legacy insurance systems often store policy data in flat files or rigid COBOL-based databases. We mapped these to modern relational structures while maintaining historical accuracy for actuarial reporting.
Phase 3: Core Modeling Challenges & Solutions
Challenge 1: Coverage Variation
Difficulty: Different policy types (Home vs. Auto) have entirely different risk variables (Square footage vs. Engine size).
Solution: Implement an Attribute-Value Model (EAV) for policy features or a Super-type/Sub-type hierarchy for different lines of business to maintain a clean central policy header.
Challenge 2: Multi-Party Claims Settlement
Difficulty: Tracking which portion of a claim payment goes to a vendor, a customer, or an attorney while ensuring the total doesn't exceed coverage limits.
Solution: Develop a Claims Sub-Ledger that validates every transaction against the associated Policy Coverage Limit in real-time.
Phase 4: Resume Impact Statements
Project Requirement:
Architected a multi-line insurance data model for an enterprise platform to manage policy lifecycles, risk underwriting, and claims processing. The system integrated legacy data with modern cloud analytics to improve claims turnaround time and actuarial accuracy.
Enterprise Policy Schema: Developed a robust 20+ entity relational model (Policy, Coverage, Claim, Insured) using Erwin to support high-frequency endorsements and renewals.
Claims Settlement Architecture: Engineered a precise payment ledger to handle multi-party distributions, ensuring absolute alignment with policy limits and regulatory audit standards.
Actuarial Data Foundation: Implemented a three-tier Medallion architecture (Bronze, Silver, Gold) on AWS/Azure to transform transactional policy data into analytics-ready risk models.
No comments:
Post a Comment