Conflicting KRIs stalled board risk appetite — Snowflake, dbt & Power BI unify thresholds

Overview

Risk appetite alignment with the board at a multi-line insurer stalled because business units used different definitions and cut-offs for key risk metrics. Underwriting, claims, and investments produced plausible but incompatible views, and meetings focused on reconciling terms rather than making choices. Intelligex standardized definitions in a governed data mart, delivered a board-ready dashboard in Power BI with sourced risk indicators, and implemented a formal threshold sign-off workflow. The board and executives reviewed a single, cited view of risk with clear ownership, fewer definitional debates, and visible accountability for changes.

Client Profile

• Industry: Insurance (property & casualty, life, and specialty)
• Company size (range): National carrier with regional operations and shared services
• Stage: Mature organization strengthening enterprise risk and board reporting
• Department owner: Strategy, Analytics & Executive Leadership (Enterprise Strategy / ERM)
• Other stakeholders: Enterprise Risk Management, Actuarial, Underwriting, Claims, Investments, Finance/FP&A, Internal Audit, Compliance/Legal, IT/Data Platforms

The Challenge

Board conversations depended on a common understanding of risk appetite and limits across underwriting, catastrophe exposure, liquidity, and operational risk. In practice, each business unit maintained its own definitions and time windows. Underwriting used one view of rate adequacy and loss ratio stability, claims used another view of severity and frequency, investments framed market and credit risk on a separate cadence, and catastrophe modeling introduced distinct geographies and return-period assumptions. Metrics were correct locally but not comparable globally.

Materials were assembled in spreadsheets and slide decks. Threshold breaches were flagged informally, and changes to limits lacked a consistent approval trail. When the board asked whether appetite was being respected, the answer varied by source and definition. Leadership wanted to keep actuarial and modeling tools in place, but establish a governed data mart with a shared dictionary, a board-ready view of key risk indicators (KRIs), and a formal sign-off for thresholds and exceptions.

Why It Was Happening

Identity, calendars, and taxonomies were fragmented. Policy and claim identifiers did not stitch cleanly to exposure and investment holdings. Catastrophe model outputs referenced license areas and peril definitions that did not map to financial ledgers. Each team cut the data on different refresh cycles. Without a conformed layer and shared language, cross-unit reconciliation consumed time and eroded confidence.

Governance arrived late. Definitions for appetite, limit, and tolerance lived in policy documents but were not enforced in reporting. Threshold changes were decided in meetings and recorded in notes rather than in a system with owners, rationale, and effective dates. As a result, metric movements triggered repeated debates about which view was authoritative and whether a breach required action.

The Solution

We created a governed risk data mart that harmonized identities, calendars, and definitions across underwriting, claims, catastrophe exposure, and investments; encoded KRIs and thresholds in transformations; and surfaced a board-ready dashboard with sourced tiles and drill-through to evidence. A lightweight workflow captured threshold sign-offs, exceptions, and effective dates with comments from Enterprise Risk, Actuarial, Finance, and business unit leaders. Nothing was replatformed: actuarial systems, cat models, policy admin, and investment tools remained systems of record; the new layer standardized definitions, lineage, and approvals around them.

• Conformed data mart in Snowflake with mastered policy, claim, exposure, and portfolio identities and shared calendars
• Metric definitions and transformations encoded in dbt with tests for identity joins, accepted values, and stale feeds
• Board-ready dashboard in Power BI showing KRIs, appetite thresholds, and traffic-light status with drill-through to model runs and ledger views
• Threshold sign-off workflow with comments, rationale, and effective dates (for example, Power Automate Approvals)
• Definitions catalog and stewardship aligned to enterprise risk principles such as COSO ERM and regulatory expectations like NAIC ORSA
• Role-based access and data minimization with identity groups and row/column policies (for example, Okta Groups)
• Audit trail capturing dataset versions, definition changes, threshold approvals, and exception notes
• Human-in-the-loop review for ambiguous mappings and threshold exceptions

Implementation

• Discovery: Cataloged policy admin, claims, catastrophe exposure, and investment sources; inventoried current KRIs and board materials; mapped identifiers and calendars; and reviewed appetite and limit statements to identify definitional drift and recurring reconciliation issues.
• Design: Defined mastered identities and shared cut-offs; authored KRI definitions and calculation logic; established threshold ownership and approval roles; designed the Power BI view with drill paths to evidence; and documented change control for definitions and thresholds.
• Build: Landed feeds into Snowflake; implemented dbt models for identity stitching, KRI computations, and validations; created the definitions catalog; built the Power BI dashboard; configured the sign-off workflow with comments and effective dates; and enabled role-based access and audit logging.
• Testing and QA: Replayed recent reporting cycles to reconcile KRIs with prior materials; validated joins and shared calendars; exercised threshold approvals and exception handling; and verified drill-through to model outputs and ledger detail for selected indicators.
• Rollout: Launched the dashboard in read-only mode alongside existing decks; after stakeholders validated definitions and joins, activated the threshold approval workflow for upcoming cycles; expanded coverage across lines of business with a manual exception path and post-review documentation.
• Training and hand-off: Delivered quick guides for Risk and Actuarial on KRI logic and stewardship, for Finance on tie-outs to ledgers, and for business leaders on reading appetite status. Established a cadence for definition reviews and a human-in-the-loop forum for threshold changes and exceptions.

Results

Board and executive reviews moved from definitional debates to decisions. KRIs appeared with a single definition, a visible threshold, and a drill-through to the underlying evidence. When appetite was approached or breached, the threshold ownership, rationale, and effective dates were recorded in the system, and next steps were clear. Cross-unit conversations referenced the same curated view with traceable lineage.

Operationally, the reporting team shifted from hand-reconciling spreadsheets to stewarding definitions and reviewing exceptions. Actuarial and Finance tie-outs stabilized under shared calendars, and threshold changes were discussed in advance through the approval flow. Internal Audit and Compliance saw a consistent, auditable record of definitions, data versions, and sign-offs aligned to enterprise risk principles.

What Changed for the Team

• Before: Each unit defined KRIs differently. After: A governed data mart and catalog standardized definitions and calendars.
• Before: Threshold changes were informal and hard to trace. After: A sign-off workflow captured owners, rationale, and effective dates.
• Before: Board decks stitched competing views. After: Power BI presented a single, sourced dashboard with drill-through to evidence.
• Before: Reconciliations dominated prep time. After: Teams focused on exceptions and actions with fewer rework cycles.
• Before: Accountability was diffuse. After: Ownership for KRIs and thresholds was explicit and auditable.

Key Takeaways

• Standardize KRIs in a governed data mart with a shared dictionary; comparability depends on common identities and calendars.
• Encode calculations and validations in transformations; definitions in code reduce drift and debate.
• Make thresholds explicit with an approval gate and rationale; clear ownership strengthens accountability.
• Deliver a board-ready dashboard with drill-through to evidence; transparency speeds decisions and supports oversight.
• Align to trusted frameworks to anchor language and expectations, and keep core tools while layering governance and workflow.

FAQ

What tools did this integrate with?
We conformed underwriting, claims, exposure, and investment feeds into Snowflake, encoded KRI logic and tests in dbt, delivered a board-ready view in Power BI, and captured threshold sign-offs in Power Automate Approvals. Access and scopes were governed via identity groups such as Okta Groups.

How did you handle quality control and governance?
A definitions catalog assigned owners for each KRI. dbt enforced identity stitching, accepted values, and stale-feed checks. Thresholds had named approvers and effective dates, recorded through a workflow with comments and rationale. Audit logs tied dashboard views to dataset versions, definitions, and approvals, aligning to principles in COSO ERM and regulatory expectations under NAIC ORSA.

How did you roll this out without disruption?
We ran the Power BI dashboard alongside existing board materials while teams validated joins and definitions. After trust was established, the threshold sign-off workflow was activated for the next cycle, with a manual exception lane and post-review documentation. Core actuarial, modeling, and finance tools remained unchanged; the new layer standardized language, lineage, and approvals around them.

How were thresholds set and maintained?
Enterprise Risk, Actuarial, and Finance proposed thresholds using historical distributions, model outputs, and policy constraints. Proposed changes moved through an approval step with rationale and effective date. Thresholds were versioned in the transformation layer and surfaced in the dashboard, with alerts when a proposed change affected status views.

How did you ensure consistency across lines of business?
A mastered identity model aligned policy, claim, exposure, and portfolio keys; shared calendars synchronized cut-offs; and KRI definitions included business-unit adjustments only where approved and documented. The dashboard presented a consolidated view with drill-through to unit-level breakdowns, all drawing from the same governed definitions and datasets.