Compare two FS domains deeply

# Chosen Domains - Domain A: Consumer Banking (retail deposits, cards, payments) - Domain B: P&C Insurance (policy admin, billing, claims) Assumption: Modern cloud data platform with multiple source systems (core systems, billing, claims, GL), near-real-time ingestion, and regulatory reporting requirements. Where needed, simple numeric examples illustrate controls. ## A) Consumer Banking ### Core entities and grain - Party/Customer: one row per party; SCD2 for attributes (KYC status, segment). - Account: one row per account; relationships to party (many-to-many via Party-Account link); status/versioning over time. - Product: deposit, card, loan; shared reference dimension. - Transaction (Posting): grain = one ledger posting (idempotent event). Key dates: event_time, accounting_date, value_date; amounts + currency. - Balance Snapshot: grain = account-day (EOD) or account-intraday snapshot; derived from postings. - Merchant/Counterparty: for card/ACH; used for AML/behavior analytics. - General Ledger (GL) Mapping: chart of accounts; mapping rules from operational postings to GL entries. Derived facts - Interest accrual: interest_t = balance_{t-1} × rate/365. - Fees: per event type and product rules. ### Typical control points and reconciliations - Landing-to-staging: counts and sum(amount) per partition (date, product). Hash totals to detect truncation. - Deduplication: enforce idempotency with unique transaction_id; detect duplicates by (source_system, natural_key, amount, accounting_date). - Posting-to-balance tie-out: For each account-day: Balance_EOD_t = Balance_EOD_{t-1} + Σ(postings on day t) - Subsidiary ledger to GL: Aggregate postings by GL account and compare to GL journal totals per day. - Payments rails reconciliation: network files (e.g., ACH/card processor) vs internal postings; match on amount, date, merchant/trace. - Interest re-computation: re-calc accrual and compare to booked interest; tolerance checks. Mini example (posting-to-balance): - Start EOD 1,000; Day t postings: +200 deposit, −50 card; Expected EOD = 1,150. If stored EOD ≠ 1,150, flag variance. ### Common failure modes - Late-arriving/back-dated postings; timezone skew between event_time and accounting_date. - Duplicate or partial events (retries, partial batches); out-of-order arrivals. - Reversals/chargebacks not linked to original postings (missing reversal_of_id). - Product/GL mapping drift after code changes; orphaned product codes. - FX mispricing (missing rate at value_date); multi-currency balance rollups double-apply FX. - Customer identity churn (merge/split parties) breaking history. ## B) P&C Insurance ### Core entities and grain - Party: insured, broker, claimant; SCD2 attributes. - Policy/Term and Coverage: policy term as contract; coverage-level attributes (limits, deductibles). Versioned with endorsements. - Risk Object: location/vehicle; used for exposure and pricing features. - Premium Transaction: grain = one financial event (written, adjustment, cancellation); effective_date vs accounting_date. - Billing/Invoice and Payment: invoice-level receivable; payment-level cash application. - Claim: FNOL record; sub-entities: exposure, reserve, payment, recovery (salvage/subrogation). Payment grain = one disbursement. - Reserve Snapshot: grain = claim-exposure-month (or day) for roll-forward schedules. - Reinsurance: treaty/program; ceded premium and recoverables mappings. - GL Mapping: from policy/billing/claims to financial statements. Derived facts - Earned premium: apply an earning curve to written premium over the coverage period. - Loss ratio: incurred losses / earned premium. - Reserve rollforward: End = Begin + Incurred − Paid ± FX/assumption changes. ### Typical control points and reconciliations - Policy admin vs billing: sum(premium transactions) by policy/period equals invoiced amounts; reconcile cancellations/endorsements. - Earned premium accrual: recompute earning schedule; compare to booked earned in GL. - Claims cash control: sum(claim payments) by bank account/day equals disbursement ledger/bank statement. - Reserve rollforward: check EndReserve = BeginReserve + NewIncurred − Paid ± FX; reconcile to GL reserve accounts. - Counts/uniqueness: policy and claim count stability by cohort/date; detect duplicate claim numbers. - Reinsurance ceded: treaty rules applied correctly; ceded premium/loss aligns with treaty terms and GL. Mini example (reserve rollforward): - Begin = 500; Paid = 100; New incurred = 50; FX = 0 → End = 500 + 50 − 100 = 450. If system shows 470, variance = 20 → investigate changes in assumptions or misclassifications. ### Common failure modes - Back-dated endorsements causing historical premium restatements; missing versioning/bitemporal handling. - Claim reopen/close cycles; payments without proper reserve reductions; negative payments (recoveries) mis-coded. - Coverage splits across perils not aligning with GL mapping; leakage due to missing deductible application. - Reinsurance allocation errors (wrong attachment/limit application) leading to GL mismatches. - Late FNOL causing IBNR volatility; missing exposure keys linking risk objects to claims. - Multi-currency claims with payment vs reserve currencies misaligned. ## C) Reusable Pattern for Both Domains ### Pattern: Event-Sourced Canonical Financial Ledger with Bitemporal Dimensions Intent - Provide a shared schema and control framework to ingest heterogeneous operational events (bank postings, premiums, claim payments), support restatement, and reconcile to balances/GL. Key schema components - Party (SCD2): party_id, attributes, valid_from/valid_to. - Contract: Account (banking) or Policy (insurance); contract_id, product, status; SCD2. - FinancialEvent (fact, append-only): - event_id (idempotency key), source_system - event_type (DEPOSIT, WITHDRAWAL, INTEREST_ACCRUAL, PREMIUM_WRITTEN, PREMIUM_ADJUSTMENT, CLAIM_PAYMENT, RESERVE_CHANGE, FEE, REVERSAL) - contract_id, party_id (nullable), counterparty_id/merchant_id (nullable) - amounts: amount, currency, fx_rate_used - dates: event_time, accounting_date, effective_date - linkage: reversal_of_event_id, gl_account_map_key - status: posted, pending, reversed - PositionSnapshot (derived): - Banking: AccountBalance at grain account-day - Insurance: ReserveSnapshot at grain claim-exposure-month and EarnedPremium at policy-coverage-day - Reference/Mapping: product, GL mapping rules, earning curves, FX rates. Bitemporality and late data - Store both effective_date (business reality) and accounting_date (booking). Maintain load_timestamp for audit. Support restatement by deriving positions “as at” any date using the event stream. Core process controls 1) Ingest & standardize: schema-on-write; enforce data types; normalize dates to UTC; enrich with source metadata. 2) Idempotency & dedup: enforce unique event_id; detect near-duplicates by fuzzy keys + amount/date. 3) Three-way reconciliation per partition (e.g., by accounting_date): - Record counts: source vs landing vs conformed - Amount controls: Σ(amount) by event_type/product - Cross-system: operational sums vs GL/journal totals 4) Derived positions validation: - Banking: recompute balances from postings and compare with source EOD or GL control accounts. - Insurance: recompute earned premium from written using curves; reserve rollforward math checks. 5) Exception handling: quarantine DLQ with reason codes (duplicate, schema drift, control variance, referential miss); SLA and alerting. Small examples - Banking balance formula: Balance_EOD_t = Balance_EOD_{t-1} + Σ(postings on t) - Insurance earned premium (straight-line): Earned over n months = written / n per month; daily uses pro-rata by exposure days. Why it works across both - Both domains are driven by time-stamped financial events rolling up to positions and GL. The event ledger abstracts product-specific nuances while preserving reversals, effective dates, and restatements. ### Limits of the pattern - Domain nuance loss: Coverage-level actuarial attributes (peril, CAT modeling), complex reinsurance treaties, or card network-specific fields may require domain-specific marts beyond the canonical. - Performance at extremes: Ultra–low-latency trading or very large claim catastrophe spikes may need specialized storage/partitioning beyond a generic ledger. - Regulatory specificity: Forms (e.g., statutory schedules) need additional derived fields and transformations not captured in the canonical model. - Unstructured data: Adjuster notes, documents, and KYC files require separate content stores and NLP pipelines. - Complex pricing/actuarial models: Chain-ladder, GLM/GBM, or stochastic simulations sit on top of the ledger but are not inherent to it. ### Implementation guardrails - Use SCD2/bitemporal dims to handle back-dated changes; never hard-update facts. - Keep reversal_of_event_id and effective vs accounting dates mandatory where applicable. - Enforce currency normalization and single-source-of-truth FX tables; store both transaction and functional currency amounts. - Build domain marts from the canonical layer (banking: deposits/cards; insurance: policy/premium/claims) with star schemas for analytics. - Automate data quality checks as code with thresholds and drift detection; version control mapping rules. This event-sourced, bitemporal ledger pattern plus a disciplined control framework provides a reusable foundation across Consumer Banking and P&C Insurance, while acknowledging where domain-specific layers are still necessary.