Scenario

Every morning, APW Insurance needs to:

  1. Retrieve policies reaching the 30-day prediction point.
  2. Calculate approved features.
  3. Score non-renewal risk.
  4. Save a ranked worklist.
  5. Avoid duplicate predictions.

Step 1: Make the SQL query deterministic

Avoid hiding the effective date inside GETDATE() during historical training.

Use an explicit parameter:

CREATE OR ALTER PROCEDURE dbo.GetPolicyRenewalFeatures
    @PredictionDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        p.PolicyId,
        p.CustomerId,
        p.PolicyType,
        CAST(p.PremiumAmount AS FLOAT) AS PremiumAmount,

        DATEDIFF(
            MONTH,
            c.CreatedDate,
            @PredictionDate
        ) AS CustomerTenureMonths,

        (
            SELECT COUNT(*)
            FROM Claims cl
            WHERE cl.CustomerId = p.CustomerId
              AND cl.ClaimDate < @PredictionDate
              AND cl.ClaimDate >= DATEADD(MONTH, -12, @PredictionDate)
        ) AS ClaimsLast12Months,

        (
            SELECT COUNT(*)
            FROM Complaints co
            WHERE co.CustomerId = p.CustomerId
              AND co.ComplaintDate < @PredictionDate
              AND co.ComplaintDate >= DATEADD(DAY, -90, @PredictionDate)
        ) AS ComplaintsLast90Days,

        (
            SELECT COUNT(*)
            FROM Policies previous
            WHERE previous.CustomerId = p.CustomerId
              AND previous.ExpiryDate < @PredictionDate
              AND previous.Renewed = 1
        ) AS PreviousRenewalCount

    FROM Policies p
    INNER JOIN Customers c
        ON c.CustomerId = p.CustomerId

    WHERE p.ExpiryDate >= DATEADD(DAY, 30, @PredictionDate)
      AND p.ExpiryDate <  DATEADD(DAY, 31, @PredictionDate);
END;

Now historical runs can use an explicit date:

EXEC dbo.GetPolicyRenewalFeatures 
@PredictionDate = '2026-06-30';

The same date should produce the same logical extract unless underlying historical data has changed.

Step 2: Define the feature contract

public sealed class PolicyRenewalFeatures
{
    public int PolicyId { get; init; }
    public int CustomerId { get; init; }

    public string PolicyType { get; init; } = string.Empty;
    public float PremiumAmount { get; init; }
    public float CustomerTenureMonths { get; init; }
    public float ClaimsLast12Months { get; init; }
    public float ComplaintsLast90Days { get; init; }
    public float PreviousRenewalCount { get; init; }
}

PolicyId and CustomerId support traceability. They should not automatically enter the ML feature vector.

Step 3: Define a run manifest

A run manifest records exactly what produced an artifact.

public sealed record TrainingRunManifest(
    string RunId,
    DateTimeOffset StartedAtUtc,
    DateOnly DatasetAsOfDate,
    string DatasetVersion,
    string SchemaVersion,
    string FeatureQueryVersion,
    string PipelineVersion,
    int RandomSeed,
    string CodeCommit,
    long RowCount,
    string OutputModelVersion);

Example:

var manifest = new TrainingRunManifest(
    RunId: Guid.NewGuid().ToString("N"),
    StartedAtUtc: DateTimeOffset.UtcNow,
    DatasetAsOfDate: new DateOnly(2026, 6, 30),
    DatasetVersion: "policy-renewal-data-v3",
    SchemaVersion: "2",
    FeatureQueryVersion: "4",
    PipelineVersion: "2",
    RandomSeed: 42,
    CodeCommit: "a21f9c7",
    RowCount: 48_250,
    OutputModelVersion: "policy-renewal-v7");

Serialize it:

using System.Text.Json;

string json = JsonSerializer.Serialize(
    manifest,
    new JsonSerializerOptions
    {
        WriteIndented = true
    });

Console.WriteLine(json);
Step 4: Fit transformations and model together

A simplified ML.NET training pipeline might be:

var featurePipeline =
    mlContext.Transforms.Categorical.OneHotEncoding(
        outputColumnName: "PolicyTypeEncoded",
        inputColumnName: nameof(PolicyTrainingRow.PolicyType))
    .Append(
        mlContext.Transforms.Concatenate(
            "NumericFeatures",
            nameof(PolicyTrainingRow.PremiumAmount),
            nameof(PolicyTrainingRow.CustomerTenureMonths),
            nameof(PolicyTrainingRow.ClaimsLast12Months),
            nameof(PolicyTrainingRow.ComplaintsLast90Days),
            nameof(PolicyTrainingRow.PreviousRenewalCount)))
    .Append(
        mlContext.Transforms.NormalizeMinMax(
            "NormalizedNumericFeatures",
            "NumericFeatures"))
    .Append(
        mlContext.Transforms.Concatenate(
            "Features",
            "NormalizedNumericFeatures",
            "PolicyTypeEncoded"));

var trainingPipeline = featurePipeline.Append(
    mlContext.BinaryClassification.Trainers.SdcaLogisticRegression(
        labelColumnName: "Label",
        featureColumnName: "Features"));

ITransformer model = trainingPipeline.Fit(trainingData);
Important:
Feature transformations + trained algorithm

are one saved pipeline.

This protects category mapping and normalization from being independently recreated incorrectly.

Step 5: Save the model with its schema

const string modelPath = "policy-renewal-v7.zip";

mlContext.Model.Save(
    model,
    trainingData.Schema,
    modelPath);

The saved model contains:

  • Fitted transformations
  • Category encodings
  • Normalization parameters
  • Trained classification model

Step 6: Load the same pipeline in production

DataViewSchema inputSchema;

ITransformer approvedModel =
    mlContext.Model.Load(
        "policy-renewal-v7.zip",
        out inputSchema);

Production must not create a new one-hot encoding map or normalization range. It uses the saved pipeline.

Step 7: Store an auditable prediction

Possible SQL table:

CREATE TABLE PolicyRenewalPredictions
(
    PredictionId            BIGINT IDENTITY PRIMARY KEY,
    PolicyId                INT NOT NULL,
    PredictionDate          DATE NOT NULL,
    ModelVersion            VARCHAR(50) NOT NULL,
    FeatureVersion          VARCHAR(50) NOT NULL,
    Probability             DECIMAL(9,6) NOT NULL,
    PredictedNonRenewal     BIT NOT NULL,
    ThresholdVersion        VARCHAR(50) NOT NULL,
    CreatedAtUtc            DATETIME2 NOT NULL,

    CONSTRAINT UQ_PolicyPrediction
        UNIQUE
        (
            PolicyId,
            PredictionDate,
            ModelVersion
        )
);

The unique constraint makes the scoring operation safer to rerun.

Step 8: Separate prediction from business action

Model:
Non-renewal probability = 0.78

Threshold rule:
If probability ≥ 0.70, mark high priority

Capacity rule:
Only top 200 policies can be called today

Business action:
Create retention task for approved top 200

Model output, threshold and operational action are separate versioned components.

PreviousNext