Scenario

We want to prepare historical data for a future policy-renewal prediction model.

Create the practice table:

CREATE TABLE PolicyRenewalTrainingData
(
    PolicyId               INT PRIMARY KEY,
    CustomerAge            INT NULL,
    PremiumAmount          DECIMAL(18,2) NULL,
    ClaimCount             INT NULL,
    PreviousRenewalCount   INT NULL,
    ComplaintCount         INT NULL,
    PolicyType             VARCHAR(30) NULL,
    RenewalReceiptNumber   VARCHAR(50) NULL,
    Renewed                BIT NULL
);

Save deliberately imperfect data

INSERT INTO PolicyRenewalTrainingData
(
    PolicyId,
    CustomerAge,
    PremiumAmount,
    ClaimCount,
    PreviousRenewalCount,
    ComplaintCount,
    PolicyType,
    RenewalReceiptNumber,
    Renewed
)
VALUES
(101, 35, 12500, 0, 2, 0, 'Health', 'RCP-101', 1),
(102, 52, 18500, 3, 0, 4, 'Motor', NULL, 0),
(103, NULL, 15000, 1, 3, 1, 'health', 'RCP-103', 1),
(104, -8, 22000, 0, 1, 0, 'LIFE', NULL, 0),
(105, 43, -5000, 1, 2, 1, 'Life', 'RCP-105', 1),
(106, 39, 16500, NULL, 4, 0, NULL, 'RCP-106', 1),
(107, 61, 25000, 2, 5, 2, 'MOTOR', NULL, NULL);

Step 1: Inspect Missing values:

SELECT
    SUM(CASE WHEN CustomerAge IS NULL THEN 1 ELSE 0 END)
        AS MissingCustomerAge,
    SUM(CASE WHEN PremiumAmount IS NULL THEN 1 ELSE 0 END)
        AS MissingPremiumAmount,
    SUM(CASE WHEN ClaimCount IS NULL THEN 1 ELSE 0 END)
        AS MissingClaimCount,
    SUM(CASE WHEN PolicyType IS NULL THEN 1 ELSE 0 END)
        AS MissingPolicyType,
    SUM(CASE WHEN Renewed IS NULL THEN 1 ELSE 0 END)
        AS MissingLabel
FROM PolicyRenewalTrainingData;

A row without the Renewed label cannot normally be used directly for supervised training because its correct outcome is unknown.

Step 2: Find invalid values

SELECT *
FROM PolicyRenewalTrainingData
WHERE CustomerAge NOT BETWEEN 18 AND 100
   OR PremiumAmount <= 0
   OR ClaimCount < 0
   OR PreviousRenewalCount < 0
   OR ComplaintCount < 0;

Do not immediately change these records. First investigate whether the source data or validation rules are wrong.

Step 3: Find inconsistent categories

SELECT
    UPPER(LTRIM(RTRIM(PolicyType))) AS NormalizedPolicyType,
    COUNT(*) AS RecordCount
FROM PolicyRenewalTrainingData
WHERE PolicyType IS NOT NULL
GROUP BY UPPER(LTRIM(RTRIM(PolicyType)));
Normalization makes these equivalent:
Health
health
 HEALTH

Step 4: Identify leakage

RenewalReceiptNumber is generated after successful renewal. It reveals the outcome and should not be supplied as a feature when predicting renewal in advance.

It may remain in the operational database, but exclude it from the ML dataset.

Step 5: Create a clean dataset query

SELECT
    CustomerAge,
    CAST(PremiumAmount AS FLOAT) AS PremiumAmount,
    ISNULL(ClaimCount, 0) AS ClaimCount,
    PreviousRenewalCount,
    ComplaintCount,
    UPPER(LTRIM(RTRIM(PolicyType))) AS PolicyType,
    Renewed
FROM PolicyRenewalTrainingData
WHERE CustomerAge BETWEEN 18 AND 100
  AND PremiumAmount > 0
  AND PreviousRenewalCount >= 0
  AND ComplaintCount >= 0
  AND PolicyType IS NOT NULL
  AND Renewed IS NOT NULL;

Notice what is excluded:

  • PolicyId: identifier, not a useful feature here
  • RenewalReceiptNumber: leakage
  • Invalid records
  • Rows without labels

In a real project, preserve rejected rows in a quality report rather than silently losing them.

C# validation model

public sealed record PolicyTrainingRow(
    int PolicyId,
    int? CustomerAge,
    decimal? PremiumAmount,
    int? ClaimCount,
    int? PreviousRenewalCount,
    int? ComplaintCount,
    string? PolicyType,
    string? RenewalReceiptNumber,
    bool? Renewed);

public sealed record ValidationResult(
    int PolicyId,
    bool IsValid,
    IReadOnlyList<string> Errors);

Validator:

public static class TrainingDataValidator
{
    public static ValidationResult Validate(PolicyTrainingRow row)
    {
        var errors = new List<string>();

        if (row.CustomerAge is null)
            errors.Add("CustomerAge is missing.");
        else if (row.CustomerAge is < 18 or > 100)
            errors.Add("CustomerAge is outside the valid range.");

        if (row.PremiumAmount is null)
            errors.Add("PremiumAmount is missing.");
        else if (row.PremiumAmount <= 0)
            errors.Add("PremiumAmount must be positive.");

        if (row.ClaimCount is null)
            errors.Add("ClaimCount is missing.");
        else if (row.ClaimCount < 0)
            errors.Add("ClaimCount cannot be negative.");

        if (string.IsNullOrWhiteSpace(row.PolicyType))
            errors.Add("PolicyType is missing.");

        if (row.Renewed is null)
            errors.Add("Training label Renewed is missing.");

        return new ValidationResult(
            row.PolicyId,
            errors.Count == 0,
            errors);
    }
}

Usage:

var row = new PolicyTrainingRow(
    PolicyId: 104,
    CustomerAge: -8,
    PremiumAmount: 22000,
    ClaimCount: 0,
    PreviousRenewalCount: 1,
    ComplaintCount: 0,
    PolicyType: "LIFE",
    RenewalReceiptNumber: null,
    Renewed: false);

var result = TrainingDataValidator.Validate(row);

Console.WriteLine($"Valid: {result.IsValid}");

foreach (var error in result.Errors)
{
    Console.WriteLine($"- {error}");
}

Expected Result:

Valid: False
- CustomerAge is outside the valid range.

This is still data engineering, not model training. But it protects everything that follows.

PreviousNext