Engineering Blog · Post #9

Reference Data Management: MDM Sync, API-Driven Dropdowns, and Zero-Code LOB/Product/Broker Configuration

How InsightUW keeps every dropdown, LOB list, and broker lookup in sync with your Informatica MDM golden record — so adding "Parametric Emerging Risks" as a new line of business takes zero code changes and zero deployments.

The Problem

Every underwriting workstation has dropdowns. LOB selectors, product lists, broker lookups, state jurisdictions, coverage sub-types, exclusion codes — the list runs into the hundreds. In most legacy systems, these dropdowns are hardcoded in the UI, maintained in scattered database tables, or worse, duplicated across three systems with no single source of truth.

When your actuarial team decides to launch a new line of business — say, Parametric / Emerging Risks — the cascade of changes is staggering:

A developer updates the LOB enum in the backend code
Another developer adds the LOB to the frontend dropdown component
A DBA inserts rows into multiple reference tables
QA tests the change across every form that references LOBs
A release is scheduled, approved, and deployed
Meanwhile, the MDM system already has the LOB defined — but nobody wired it through

This process takes 2–6 weeks at most carriers. During that time, submissions for the new LOB are handled manually, tracked in spreadsheets, and reported inconsistently.

The InsightUW Approach

InsightUW treats reference data as a first-class domain entity with a dedicated sync layer that connects your Master Data Management (MDM) system — Informatica, Reltio, Semarchy, or any system with an API — directly to the application's dropdown infrastructure.

The architecture separates reference data into two categories:

MDM-Sourced (Read-Only): Golden records managed in your MDM. InsightUW consumes but never modifies these.
Local (Editable): Application-specific configuration that lives in InsightUW's own database and can be managed through the Admin UI.

graph TB subgraph MDM["Informatica MDM (Golden Source)"] A["LOB Master"] B["Broker Master"] C["Product Catalog"] D["State/Jurisdiction"] E["Coverage Types"] end subgraph Sync["Sync Layer"] F["API Pull (Scheduled)"] G["Webhook Push (Real-Time)"] H["Batch CSV (Nightly)"] end subgraph Cache["InsightUW Reference Cache"] I["Reference Data Table"] J["Redis Hot Cache TTL: 5 min"] K["Version Registry"] end subgraph UI["InsightUW UI Dropdowns"] L["LOB Selector"] M["Broker Lookup"] N["Product Picker"] O["Coverage Configurator"] end A --> F B --> G C --> F D --> H E --> F F --> I G --> I H --> I I --> J J --> L J --> M J --> N J --> O I --> K

The Reference Data Model

At the core of this architecture is a single, flexible model that can represent any reference data entity:

Key design decisions in this model:

source field distinguishes MDM-sourced (read-only in UI) from local (editable) records
metadata JSONB column allows each category to carry its own attributes without schema changes
parent code supports hierarchical reference data (e.g., sub-LOBs under a parent LOB)
version enables optimistic concurrency and audit trails
effective date / expiry date supports temporal validity — a new LOB can be configured today but only appear in dropdowns after its effective date

Sync Patterns: Three Ways to Stay Current

InsightUW supports three sync patterns, and most deployments use all three simultaneously depending on the data category and urgency requirements.

Pattern 1: API Pull (Scheduled)

The most common pattern. A background job queries the MDM REST API on a configurable schedule (default: every 15 minutes for LOBs, hourly for brokers, daily for jurisdictions).

Pattern 2: Webhook Push (Real-Time)

For high-priority changes — such as a broker being suspended or a new LOB going live — Informatica MDM pushes a webhook to InsightUW's listener endpoint.

Pattern 3: Batch CSV (Nightly)

For bulk loads and reconciliation. A nightly job pulls a full extract from MDM, compares it against the local reference data table, and reconciles differences.

sequenceDiagram participant MDM as Informatica MDM participant Job as Nightly Batch Job participant DB as Reference Data Table participant Cache as Redis Cache participant UI as InsightUW UI Note over Job: 2:00 AM ET - Batch window opens Job->>MDM: GET /api/v1/export/lob?format=csv MDM-->>Job: lob master 20260421.csv (312 records) Job->>DB: Select * From uw reference data Where category='LOB' DB-->>Job: 311 existing records Job->>Job: Diff: 1 new, 0 updated, 0 deactivated Job->>DB: Insert new LOB record (Param Emerging) Job->>Cache: Invalidate key pattern ref:LOB:* Cache-->>Job: 47 keys invalidated Note over UI: Next request loads fresh data from DB → Cache Job->>Job: Generate reconciliation report

The Scenario

Global Re Insurance decides to enter the parametric insurance market. The Chief Underwriting Officer approves the new LOB. The MDM team at Global Re adds "Parametric / Emerging Risks" to the Informatica MDM LOB master on a Monday morning.

What Happens in InsightUW (Timeline)

Time	Event	System Action
9:00 AM Mon	MDM team creates LOB in Informatica	LOB record `INF-MDM-LOB-0047` created with code `PARAM_EMERGING`
9:00:02 AM	Informatica fires webhook	POST to InsightUW webhook endpoint with RECORD_CREATED event
9:00:02 AM	InsightUW webhook handler validates signature	HMAC-SHA256 verification passes
9:00:03 AM	Reference Data record created	ref lob parametric emerging inserted with source=MDM, is_active=true
9:00:03 AM	Redis cache invalidated	Key pattern `ref:LOB:*` flushed; next request triggers cache rebuild
9:00:05 AM	Underwriter refreshes submission form	LOB dropdown now includes "Parametric / Emerging Risks"
9:00:05 AM	Zero code changes	No PR, no build, no deployment, no QA cycle
9:15 AM	Scheduled API pull runs	Incremental sync confirms the new LOB; version matches — no action needed
2:00 AM Tue	Nightly batch reconciliation	Full extract confirms 312 LOBs in MDM = 312 in InsightUW. Reconciliation: clean

The First Submission Arrives

At 10:30 AM on Monday — 90 minutes after the LOB was added — a broker emails a parametric weather-trigger submission for a Florida agriculture client. InsightUW's AI parser identifies the LOB as "Parametric / Emerging Risks" because it is now a valid reference data entry. The submission is created, assigned via FIFO, and the underwriter sees it in their work queue with all the correct LOB-specific metadata.

No developer touched the code. No deployment was scheduled. The MDM golden record flowed through to the UI in under 5 seconds.

MDM-Sourced vs. Local Reference Data

Not all reference data belongs in the MDM. InsightUW makes a clear architectural distinction:

Characteristic	MDM-Sourced	Local
Source of truth	Informatica MDM	InsightUW database
Editable in UI	No (read-only badge shown)	Yes (Admin > Reference Data)
Sync mechanism	API pull / webhook / batch	Direct CRUD via Admin API
Examples	LOBs, Brokers, Products, States	Submission statuses, internal tags, SLA tiers, custom fields
Deletion	Soft-deactivate only (mirrors MDM)	Soft-delete with cascade check
Audit trail	MDM audit + InsightUW sync log	InsightUW audit log only
Cache TTL	5 minutes (shorter for high-change)	15 minutes

graph LR subgraph MDMSourced["MDM-Sourced (Read-Only)"] A1["LOBs"] A2["Brokers"] A3["Products"] A4["Jurisdictions"] A5["Coverage Types"] end subgraph Local["Local (Editable)"] B1["Submission Statuses"] B2["Internal Tags"] B3["SLA Tier Config"] B4["Custom Field Defs"] B5["Notification Rules"] end subgraph Admin["Admin UI"] C["Reference Data Manager"] end C -.->|"🔒 View Only"| MDMSourced C -->|"✏️ Full Crud"| Local

API: Fetching Reference Data for Dropdowns

The frontend fetches dropdown data through a single, cacheable endpoint:

The frontend ReferenceDataDropdown component consumes this endpoint with a simple category prop:

Admin UI: Managing Local Reference Data

For reference data categories that live locally, InsightUW provides a full CRUD admin interface:

Metrics: Before and After InsightUW Reference Data Management

Metric	Before InsightUW	After InsightUW	Improvement
Time to add a new LOB	2–6 weeks (code change + deploy)	< 5 seconds (MDM sync)	99.9% faster
Dropdown consistency across modules	60–75% (manual sync)	100% (single source)	25–40% improvement
Reference data staleness	Hours to days	< 5 minutes (cache TTL)	Real-time
Developer effort for LOB changes	8–24 hours of dev + QA	0 hours	100% eliminated
MDM reconciliation accuracy	Manual quarterly audit	Automated nightly with alerts	Continuous
Number of reference data tables	15–30 scattered tables	1 unified table + cache	93% reduction
Broker lookup response time	200–800ms (DB query)	8–15ms (Redis cache hit)	95% faster
Deployment risk from ref data changes	Medium (code deploy required)	Zero (no deploy needed)	Eliminated

Architecture: Cache Invalidation Strategy

Cache invalidation is one of the hardest problems in computer science. InsightUW handles it with a layered approach:

graph TD subgraph Triggers["Cache Invalidation Triggers"] T1["Webhook Received"] T2["API Sync Completed"] T3["Batch Reconciliation"] T4["Admin UI Save"] T5["TTL Expiry (5 min)"] end subgraph Strategy["Invalidation Strategy"] S1["Pattern-Based Key Flush ref:{category}:*"] S2["Version Bump in DB Record"] S3["Event Published to Web Socket"] end subgraph Result["Downstream Effect"] R1["Next API Call Rebuilds Cache"] R2["Open Browser Tabs Refresh Dropdowns"] R3["Audit Log Entry Created"] end T1 --> S1 T2 --> S1 T3 --> S1 T4 --> S1 T5 --> S1 S1 --> R1 S1 --> S2 S2 --> S3 S3 --> R2 S1 --> R3

Key Takeaways

Reference data is a sync problem, not a coding problem. InsightUW eliminates the code-deploy cycle for LOB, product, and broker changes by syncing directly from your MDM.
Three sync patterns cover every use case. Webhooks for real-time urgency, scheduled API pulls for routine freshness, nightly batch for full reconciliation and audit.
MDM-sourced data is read-only by design. InsightUW never modifies your golden records. Local reference data is separately managed for application-specific configuration.
One model, one table, one API. The Reference Data model uses a flexible JSONB metadata column to handle any category without schema changes.
Cache-first architecture. Redis serves every dropdown request in under 15ms, with automatic invalidation on any change.

Ready to eliminate the LOB-change deployment cycle? InsightUW's reference data sync connects your MDM to every dropdown in the underwriting workstation — zero code, zero risk, zero wait.

Schedule a Reference Data Architecture Review →