The Model Score Is Not the Decision: Building an Auditable Insurance Routing Pipeline with Databricks and dbt

From synthetic insurance events to fixed-artifact scoring, decision policy, review feedback, lifecycle audit and evidence dashboard

Most insurance analytics projects ask the wrong question.

They ask:

Can we predict fraud?

But in a real claims operation, that is rarely the question that matters most.

A better operational question is:

Which claims should be reviewed first, by which route, and why?

A claims team does not only need a model score. It needs an explainable routing decision, a queue, a review process, feedback capture, lifecycle tracking, and audit evidence.

That was the starting point for my portfolio project: Insurance Fraud Decision Pipeline.

The project is not a fraud-verdict engine. It does not decide that a policyholder committed fraud. Instead, it builds an auditable decision-routing pipeline for motor insurance claims.

The central principle is simple:

The model score is not the decision.

The final output is an explainable business route: standard handling, manual review, investigation queue, duplicate check, or coverage review.

Project scope

The project simulates a mid-sized French motor insurer with a synthetic motor-claims portfolio.

The synthetic data generating process includes:

• policies;
• policy events;
• claimants;
• vehicles;
• garages;
• claim notifications;
• assessments;
• payments;
• investigation outcomes;
• Nat Cat context;
• internal synthetic truth used only for calibration and audit.

The final portfolio-scale run includes:

The dataset is fully synthetic. No real customer, policyholder, claims, underwriting, reserving or internal company data is used.

Architecture overview

The project uses Azure Databricks, dbt, Delta Lake, PySpark, MLflow Registry, Databricks Jobs and Databricks SQL.

But the main architecture is not about using many tools. It is about separating responsibilities.

Synthetic insurance events
→ Raw tables
→ Bronze normalization
→ Silver business entities, labels and features
→ ML training mart
→ MLflow registered model
→ Daily fixed-artifact scoring
→ Gold decisions and adjuster queue
→ Review feedback
→ Lifecycle audit
→ Databricks SQL evidence dashboard

Executive architecture of the Insurance Fraud Decision Pipeline, separating source events, dbt transformation layers, MLflow model lineage, decision policy, operational jobs, Gold marts and evidence dashboard.

The important separation is this:

model score
≠ business decision
≠ operational queue
≠ review outcome
≠ lifecycle audit

Each layer has a different owner and a different meaning.

Data transformation with dbt

The pipeline uses dbt to organize the lakehouse into explicit layers:

• sources;
• Bronze models;
• Bronze sanity checks;
• Silver business entities;
• Silver labels;
• Silver features;
• ML training marts;
• Gold decision, queue, feedback, lifecycle and evidence marts.

The dbt DAG is connected, but execution is intentionally staged because some tables are created by Databricks notebooks between dbt stages. For example, ML scores are produced after model training and registration, and review outcomes are generated after daily decisions and queue creation.

This is why the project uses explicit stage selectors instead of blindly running a broad selector during intermediate stages.

A simplified version of the daily Gold decision model looks like this. The real model includes additional lineage fields, but the core structure is the same: scores and daily features are joined, a versioned policy assigns the route, and the output carries model, feature and policy lineage.

{{ config(materialized='table', tags=['gold', 'daily_operational']) }}

with scores as (
    select
        claim_id,
        feature_snapshot_id,
        model_run_id,
        model_name,
        model_version,
        registered_model_version,
        mlflow_run_id,
        scoring_run_id,
        fraud_probability as model_score,
        source_batch_date,
        source_batch_id
    from {{ source('daily_operational_tables', 'daily_claim_scores_baseline') }}
),

features as (
    select
        feature_snapshot_id,
        coverage_issue,
        strong_red_flag,
        feature_duplicate_suspected
    from {{ ref('daily_claim_features_silver') }}
),

scored as (
    select
        s.*,
        f.coverage_issue,
        f.strong_red_flag,
        f.feature_duplicate_suspected
    from scores s
    inner join features f
        on s.feature_snapshot_id = f.feature_snapshot_id
),

policy as (
    select
        *,

        case
            when feature_duplicate_suspected = 1
             and model_score >= {{ var('duplicate_route_min_score', 0.75) }}
            then 1 else 0
        end as duplicate_route_flag,

        case
            when coverage_issue = 1
                then 'coverage_review'

            when feature_duplicate_suspected = 1
             and model_score >= {{ var('duplicate_route_min_score', 0.75) }}
                then 'duplicate_check'

            when model_score >= {{ var('decision_high_threshold', 0.80) }}
                then 'investigation_queue'

            when model_score >= {{ var('decision_medium_threshold', 0.65) }}
                then 'manual_review'

            when model_score >= {{ var('decision_gray_zone_threshold', 0.60) }}
             and strong_red_flag = 1
                then 'manual_review'

            else 'standard_handling'
        end as final_route,

        case
            when model_score >= 0.80 then 'very_high'
            when model_score >= 0.65 then 'high'
            when model_score >= 0.50 then 'medium'
            when model_score >= 0.25 then 'low'
            else 'very_low'
        end as risk_tier
    from scored
)

select
    sha2(
        concat_ws(
            '|',
            claim_id,
            feature_snapshot_id,
            model_run_id,
            scoring_run_id,
            '{{ var("decision_policy_version", "policy_v5_dup075_medium065_gray060") }}',
            'daily'
        ),
        256
    ) as decision_id,

    claim_id,
    current_timestamp() as decision_timestamp,
    model_score,
    risk_tier,

    named_struct(
        'coverage_issue', coverage_issue,
        'duplicate_route_flag', duplicate_route_flag,
        'duplicate_signal', feature_duplicate_suspected,
        'strong_red_flag', strong_red_flag
    ) as business_flags,

    case
        when final_route = 'coverage_review' then 'coverage_issue'
        when final_route = 'duplicate_check' then 'duplicate_route_flag'
        when final_route = 'investigation_queue' then 'high_model_score'
        when final_route = 'manual_review' then 'medium_or_gray_zone_score'
        else 'standard_policy'
    end as decision_reason_primary,

    final_route,

    cast(
        model_score * 100
        + case when final_route = 'investigation_queue' then 20 else 0 end
        + case when strong_red_flag = 1 then 10 else 0 end
        + case when duplicate_route_flag = 1 then 5 else 0 end
        as double
    ) as priority_score,

    model_name,
    model_version,
    registered_model_version,
    mlflow_run_id,
    scoring_run_id,
    '{{ var("decision_policy_version", "policy_v5_dup075_medium065_gray060") }}' as decision_policy_version,
    source_batch_date,
    source_batch_id,
    current_timestamp() as created_at

from policy

This example shows the main idea: the model score is only one input. The final route is produced by a versioned decision policy that also considers business flags.

MLflow boundary: train once, register once, score many times

The ML component is intentionally simple.

The baseline model is a logistic regression trained on observed review labels, not on latent synthetic truth. The output is treated as a relative risk score, not as a legal probability of fraud.

The selected model is registered in MLflow Registry and reused as a fixed artifact for daily scoring.

The rule is:

Train once, register once, score many times.

Daily scoring does not retrain.

The exact implementation has separate notebooks for backfill scoring and validation. The simplified pattern below shows the operational boundary: load the selected registered artifact, score the target batch, write scores, then validate score coverage.

import mlflow
from pyspark.sql import functions as F

MODEL_URI = "models:/ifdp_claim_routing_baseline@champion"

# Load the registered artifact selected for operational scoring.
model = mlflow.pyfunc.load_model(MODEL_URI)

# Daily features are already built in Silver.
features_df = spark.table(
    "dbw_ifdp_dev_frc_001.ifdp_azure_1250k_silver.daily_claim_features_silver"
).where(
    F.col("source_batch_date") == F.lit(batch_date)
)

# In the project, large historical scoring was materialized Spark-natively
# to avoid driver-heavy pandas-to-Spark pressure.
# Daily scoring is bounded and validates score coverage after write.
score_df = score_daily_claims_with_registered_artifact(
    features_df=features_df,
    model=model,
    model_alias="champion",
    source_batch_date=batch_date
)

(
    score_df
    .write
    .mode("overwrite")
    .option("replaceWhere", f"source_batch_date = '{batch_date}'")
    .saveAsTable(
        "dbw_ifdp_dev_frc_001.ifdp_azure_1250k_ml.daily_claim_scores_baseline"
    )
)

The important part is not the model complexity. The important part is the artifact boundary.

If the model artifact changes every day without governance, it becomes hard to explain why a claim was routed differently. With a registered artifact, the daily decision can be traced back to a model version, feature version, policy version and run lineage.

Decision policy: score plus business rules

The decision policy combines:

• model score;
• risk tier;
• duplicate risk;
• coverage uncertainty;
• red flags;
• business thresholds;
• route-specific gates.

The final routes are:

standard_handling
manual_review
investigation_queue
coverage_review
duplicate_check

The adjuster queue excludes standard-handling claims. It contains only actionable review routes.

The adjuster queue does not recalculate the decision. It filters actionable routes from the Gold decision table and ranks them using the priority score already produced by the decision policy.

{{ config(materialized='table', tags=['gold', 'daily_operational']) }}

with actionable as (
    select *
    from {{ ref('daily_claim_decisions_gold') }}
    where final_route in (
        'manual_review',
        'investigation_queue',
        'coverage_review',
        'duplicate_check'
    )
),

ranked as (
    select
        *,
        row_number() over (
            order by
                priority_score desc,
                decision_timestamp asc,
                claim_id asc
        ) as priority_rank
    from actionable
)

select
    claim_id,
    decision_id,
    final_route,
    risk_tier,
    model_score,
    priority_score,
    priority_rank,
    decision_reason_primary,
    decision_reason,
    business_flags,
    model_name,
    model_version,
    registered_model_version,
    mlflow_run_id,
    feature_version,
    decision_policy_version,
    decision_timestamp,
    source_batch_date,
    source_batch_id,
    current_timestamp() as queue_created_at,
    'pending' as queue_status
from ranked

This is the operational layer that makes the model useful. The score helps prioritize. The route tells the business what to do next.

Operational workflow: Job A and Job B

The final operational workflow uses two Databricks Jobs.

In persisted daily and feedback tables, these dates are stored as source_batch_date.

This split was important. If lifecycle audit checks run before review feedback exists, the audit can fail for timing reasons rather than data-quality reasons. Separating Job A and Job B makes the operational boundary explicit.

Evidence dashboard

The final Databricks SQL dashboard summarizes the operational state of the pipeline.

It includes:

• YTD and MTD claim volume;
• actionable share;
• closed-review hit rate;
• latest scoring batch date;
• latest feedback batch date;
• score coverage;
• pipeline audit status;
• active model version;
• active decision policy version;
• route distribution;
• review funnel;
• lifecycle status.

Final Databricks SQL evidence dashboard showing scoring freshness, feedback freshness, score coverage, active model/policy versioning, route mix, review funnel and lifecycle audit status.

The dashboard is read-only. It does not trigger scoring, routing, review generation or lifecycle updates.

Its purpose is evidence.

It answers:

Did the pipeline run? What model and policy were active? Was score coverage complete? Did review feedback catch up? Did lifecycle audit pass? What routes were produced?

For the final evidence run, the latest operational snapshot showed:

Cost and performance evidence

The project was not designed as a production benchmark. It was designed as a controlled portfolio-scale Azure run.

Performance notes were captured around:

• 10K smoke run;
• 300K integration run;
• 1.25M target-scale run;
• daily Job A runtime;
• daily Job B runtime;
• score coverage;
• review feedback throughput.

The final observed operational runtimes were:

Cost guardrails were also documented.

The project used smaller stages before the final 1.25M run, avoided always-on services, used batch scoring instead of model serving, stopped compute after validation, and stored compact evidence artifacts rather than raw data exports.

The observed Azure Cost Management snapshot was small, but the important point is not the exact amount. The important point is that cloud cost was treated as an operational constraint.

Repository evidence

The GitHub repository includes:

• recruiter-facing README;
• architecture overview;
• business requirements;
• technical design;
• synthetic DGP design;
• table reference;
• operational runbook;
• lifecycle SCD design;
• evidence dashboard guide;
• performance notes;
• cost guardrails;
• data contracts;
• SLA/SLO notes;
• technology decisions;
• portfolio interview brief;
• sanitized Databricks Job JSON;
• final dashboard screenshot;
• final KPI, route and operational health CSV snapshots;
• final dbt manifest and run results;
• CI-lite workflow.

The CI-lite workflow does not connect to Databricks. It validates repository hygiene, required artifacts, JSON syntax, CSV evidence readability and sanitization checks.

This keeps the public repository safe and reviewable without exposing workspace-specific credentials or requiring cloud access.

What I would improve in a production version

This is a portfolio project, not a production claims platform.

If I were to move it toward production, I would focus on:

I would not start by adding a Model Serving endpoint or streaming ingestion. Daily batch decision-routing is enough for this operating model.

Main lessons learned

The stronger engineering problem is the operating system around the model.

A model score is useful only if the surrounding pipeline can explain where the data came from, which features were available at decision time, which model artifact was used, which policy converted the score into a route, and what happened after the claim was reviewed.

That is why data contracts matter. They make grain, keys, accepted values and timing assumptions explicit.

That is why feature timing matters. Outcome-time information should not leak into current scoring features.

That is why artifact lineage matters. A daily decision should be explainable by model version, feature version and policy version.

That is why fixed scoring matters. Daily operational scoring should not silently retrain and change the decision boundary without governance.

That is why feedback matters. Without review outcomes, the pipeline cannot learn whether its routing process is operationally useful.

That is why lifecycle audit matters. A queue item should not disappear from the process without a traceable status history.

And that is why evidence reporting matters. A dashboard should not just show attractive charts. It should answer whether the pipeline ran, whether coverage was complete, whether feedback caught up, and whether audit checks passed.

The final narrative of the project is not:

I built a fraud model.

The final narrative is:

I built an auditable insurance decision pipeline that separates model score from business decision, validates data quality at each layer, calibrates operational workload, captures review feedback, and separates model training from daily scoring through a registered artifact boundary.

That is the Analytics Engineering story I wanted to demonstrate.

About the author

SukHee Lee is an Senior Analytics Engineer / Data Engineer working across insurance, reinsurance and banking data domains. His work focuses on financial data pipelines, analytics engineering, dbt, Databricks, Azure and operational data quality.

GitHub: github.com/SHLee5864

Project repository: github.com/SHLee5864/insurance-fraud-decision-pipeline