Scenario

We want to identify policies expiring in 30 days and prepare features for a future non-renewal model.

Step 1: Define the prediction moment

For historical training:

PredictionDate = ExpiryDate minus 30 days

For current production scoring:

PredictionDate = Today
Eligible policies = Policies expiring 30 days from today

Step 2: Build a point-in-time SQL feature query

DECLARE @PredictionDate DATE = CAST(GETDATE() AS DATE);

SELECT
    p.PolicyId,
    p.CustomerId,
    p.PolicyType,

    DATEDIFF(
        YEAR,
        c.DateOfBirth,
        @PredictionDate
    ) AS CustomerAge,

    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 Payments py
        WHERE py.CustomerId = p.CustomerId
          AND py.PaymentDate < @PredictionDate
          AND py.WasDelayed = 1
    ) AS PreviousDelayedPayments,

    (
        SELECT COUNT(*)
        FROM Policies previous
        WHERE previous.CustomerId = p.CustomerId
          AND previous.PolicyId <> p.PolicyId
          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);

This query performs two jobs:

1. Select policies reaching the prediction moment. 
2. Calculate features using earlier historical data.

The AI model has not yet made a prediction.

Step 3: Create the ML.NET input class

using Microsoft.ML.Data;

public sealed class PolicyRiskInput
{
    public float CustomerAge { get; set; }
    public float PremiumAmount { get; set; }
    public float CustomerTenureMonths { get; set; }
    public float ClaimsLast12Months { get; set; }
    public float ComplaintsLast90Days { get; set; }
    public float PreviousDelayedPayments { get; set; }
    public float PreviousRenewalCount { get; set; }
    public string PolicyType { get; set; } = string.Empty;

    [ColumnName("Label")]
    public bool DidNotRenew { get; set; }
}

PolicyId and CustomerId may be retained for traceability, but should not automatically enter the feature vector.

Step 4: Create a runnable ML.NET transformation pipeline

Create the project:

dotnet new console -n FeatureEngineeringDemo
cd FeatureEngineeringDemo 
dotnet add package Microsoft.ML

Use this Program.cs:

using Microsoft.ML;
using Microsoft.ML.Data;

namespace FeatureEngineeringDemo;

public sealed class PolicyRiskInput
{
    public float CustomerAge { get; set; }
    public float PremiumAmount { get; set; }
    public float CustomerTenureMonths { get; set; }
    public float ClaimsLast12Months { get; set; }
    public float ComplaintsLast90Days { get; set; }
    public float PreviousDelayedPayments { get; set; }
    public float PreviousRenewalCount { get; set; }
    public string PolicyType { get; set; } = string.Empty;

    [ColumnName("Label")]
    public bool DidNotRenew { get; set; }
}

internal static class Program
{
    private static void Main()
    {
        var rows = new List<PolicyRiskInput>
        {
            new()
            {
                CustomerAge = 35,
                PremiumAmount = 12500,
                CustomerTenureMonths = 48,
                ClaimsLast12Months = 0,
                ComplaintsLast90Days = 0,
                PreviousDelayedPayments = 0,
                PreviousRenewalCount = 3,
                PolicyType = "Health",
                DidNotRenew = false
            },
            new()
            {
                CustomerAge = 52,
                PremiumAmount = 28500,
                CustomerTenureMonths = 18,
                ClaimsLast12Months = 3,
                ComplaintsLast90Days = 4,
                PreviousDelayedPayments = 2,
                PreviousRenewalCount = 0,
                PolicyType = "Motor",
                DidNotRenew = true
            },
            new()
            {
                CustomerAge = 43,
                PremiumAmount = 45000,
                CustomerTenureMonths = 72,
                ClaimsLast12Months = 1,
                ComplaintsLast90Days = 0,
                PreviousDelayedPayments = 0,
                PreviousRenewalCount = 5,
                PolicyType = "Life",
                DidNotRenew = false
            }
        };

        var mlContext = new MLContext(seed: 42);
        IDataView trainingData =
            mlContext.Data.LoadFromEnumerable(rows);

        string[] numericColumns =
        {
            nameof(PolicyRiskInput.CustomerAge),
            nameof(PolicyRiskInput.PremiumAmount),
            nameof(PolicyRiskInput.CustomerTenureMonths),
            nameof(PolicyRiskInput.ClaimsLast12Months),
            nameof(PolicyRiskInput.ComplaintsLast90Days),
            nameof(PolicyRiskInput.PreviousDelayedPayments),
            nameof(PolicyRiskInput.PreviousRenewalCount)
        };

        var pipeline =
            mlContext.Transforms.Categorical.OneHotEncoding(
                outputColumnName: "PolicyTypeEncoded",
                inputColumnName: nameof(PolicyRiskInput.PolicyType))
            .Append(
                mlContext.Transforms.Concatenate(
                    "NumericFeatures",
                    numericColumns))
            .Append(
                mlContext.Transforms.NormalizeMinMax(
                    "NormalizedNumericFeatures",
                    "NumericFeatures"))
            .Append(
                mlContext.Transforms.Concatenate(
                    "Features",
                    "NormalizedNumericFeatures",
                    "PolicyTypeEncoded"));

        ITransformer featureTransformer =
            pipeline.Fit(trainingData);

        IDataView transformedData =
            featureTransformer.Transform(trainingData);

        var featureVectors = transformedData
            .GetColumn<float[]>("Features")
            .ToArray();

        for (int index = 0; index < featureVectors.Length; index++)
        {
            Console.WriteLine(
                $"Row {index + 1}: " +
                string.Join(", ",
                    featureVectors[index]
                        .Select(value => value.ToString("0.000"))));
        }
    }
}

Run:

dotnet run

The pipeline performs:

PolicyType
    ↓
One-hot encoding

Numeric columns
    ↓
Concatenation
    ↓
Min-max normalization

Normalized numbers + encoded policy type
    ↓
Final Features vector

We have transformed the data, but we have not trained the classification model yet.

Step 5: Understand Fit and Transform

pipeline.Fit(trainingData);

Fit learns required transformation information from training data, such as:

• Known policy categories
• Minimum values
• Maximum values

featureTransformer.Transform(newData);

Transform applies the same learned rules to validation, test and production records.

Correct lifecycle:

Fit feature pipeline on training data
                ↓
Transform training data
Transform validation data
Transform test data
Transform production data

Never independently recalculate production normalization using one incoming policy.