Range Tables

Range Tables provide advanced table lookup capabilities. Unlike conventional lookup tables, which are performance-optimized for single-key, exact-match lookups, Range Tables support both range-based and interpolated table lookups. Range Tables are configured and managed similarly to other table resources. They can be referenced from any plugin and can be versioned based on effective dates using the Versioned Resource Selection feature.

Use Cases

Currently, Range Tables support two use cases:

Range-Based Lookups - A numeric input value is used to identify the appropriate row in the table by finding the row where the input value falls within the range defined by the rangeStart and rangeEnd columns. For example, a credit score factor table may define ranges of credit scores and associated factors to be applied to a premium calculation.
Interpolated Lookups - A numeric input value is used to identify two rows in the table, and the output value is calculated by interpolating between the output values of the two rows. For example, a vehicle value depreciation table may define vehicle values based on year, and for an input vehicle year that falls between two defined years, the output value is calculated by interpolating between the two defined values.

Configuration

The structure of each table is established in the configuration using the RangeTableRef under the rangeTables property of the ConfigurationRef, by declaring the name of the table along with the number and type of each column. The rangeStart and rangeEnd properties are unique to Range Table configurations, which identify the columns that define the beginning and end of the lookup range.

A typical configuration looks like this:

{
    // At the top level of the configuration
    "rangeTables": {
        "CreditScoreFactor": {
            "columns": {
                "minScore": {
                    "dataType": "decimal",
                    "isKey": false
                },
                "maxScore": {
                    "dataType": "decimal",
                    "isKey": false
                },
                "factor": {
                    "dataType": "decimal",
                    "isKey": false
                }
            },
            "selectionTimeBasis": "termStartTime",
            "rangeStart": "minScore",
            "rangeEnd": "maxScore"
        },
        "VehicleStateFactor": {
            "columns": {
                "make": {
                    "dataType": "string",
                    "isKey": true
                },
                "stateName": {
                    "dataType": "string",
                    "isKey": true
                },
                "year": {
                    "dataType": "decimal",
                    "isKey": false
                },
                "factor": {
                    "dataType": "int",
                    "isKey": false
                }
            },
            "selectionTimeBasis": "termStartTime",
            "rangeStart": "year",
            "rangeEnd": "year"
        }
    }
}

This configuration defines two tables.

The first table, CreditScoreFactor, has three columns: minScore, maxScore, and factor. The minScore and maxScore columns define the range for lookups, while the factor column provides the output value.

The second table, VehicleStateFactor, has four columns: make, stateName, year, and factor. The make and stateName columns are defined as key columns, while the year column is used for range lookups, and the factor column provides the output value.

Note

The system requires that the columns defined as rangeStart and rangeEnd be of numeric data types (int, long, decimal, etc.) to support range and interpolation calculations.

Examples

Range-Based Lookup Example

Given the CreditScoreFactor table defined above, consider the following records:

[
    { "minScore": 300, "maxScore": 579, "factor": 1.5 },
    { "minScore": 580, "maxScore": 669, "factor": 1.2 },
    { "minScore": 670, "maxScore": 739, "factor": 1.0 },
    { "minScore": 740, "maxScore": 799, "factor": 0.9 },
    { "minScore": 800, "maxScore": 850, "factor": 0.8 }
]

For an input credit score of 582, the lookup would identify the record where minScore is 580 and maxScore is 669, returning a factor of 1.2.

For example:

// Get the selector
ResourceSelector selector = ResourceSelectorFactory.getInstance().getSelector(request.quote());
RangeTableRecordFetcher<CreditScoreFactor> scoreFetcher = resourceSelector.getRangeTable(CreditScoreFactor.class);

BigDecimal lowerMidScore = BigDecimal.valueOf(582);

scoreFetcher.extrapolate(TableUtils.makeKey(), lowerMidScore, CreditScoreFactor::factor, Interpolation.stepUp).ifPresent(r -> log.info("1. Interpolated factor for lowerMidScore stepUp {}: {}", lowerMidScore, r));
scoreFetcher.extrapolate(TableUtils.makeKey(), lowerMidScore, CreditScoreFactor::factor, Interpolation.stepDown).ifPresent(r -> log.info("2. Interpolated factor for lowerMidScore stepDown {}: {}", lowerMidScore, r));
scoreFetcher.extrapolate(TableUtils.makeKey(), lowerMidScore, CreditScoreFactor::factor, Interpolation.linear).ifPresent(r -> log.info("3. Interpolated factor for lowerMidScore linear {}: {}", lowerMidScore, r));

Output:

Interpolated factor for lowerMidScore stepUp 582: 1.0
Interpolated factor for lowerMidScore stepDown 582: 1.2
Interpolated factor for lowerMidScore linear 582: 1.2

Interpolated Lookup Example

Given the VehicleStateFactor table defined above, consider the following records:

[
    { "make": "Toyota", "state": "CA", "year": 2030, "factor": 8800 },
    { "make": "Toyota", "state": "CA", "year": 2025, "factor": 7700 },
    { "make": "Toyota", "state": "CA", "year": 2020, "factor": 6600 },
    { "make": "Toyota", "state": "CA", "year": 2015, "factor": 5300 },
    { "make": "Toyota", "state": "CA", "year": 2010, "factor": 4300 },
    { "make": "Toyota", "state": "CA", "year": 2002, "factor": 3300 },
    { "make": "Toyota", "state": "CA", "year": 2001, "factor": 2200 },
    { "make": "Toyota", "state": "CA", "year": 2000, "factor": 1100 }
]

For an input vehicle year of 2017, the lookup would identify the years 2015 and 2020, and linearly interpolate the factor between 5300 and 6600, resulting in an output factor of 5820. Range Table lookups also support lookups of the ceiling and floor values by leveraging the stepUp and stepDown methods.

For example:

// Get the selector
ResourceSelector selector = ResourceSelectorFactory.getInstance().getSelector(request.quote());
RangeTableRecordFetcher<VehicleStateFactor> stateFetcher = resourceSelector.getRangeTable(VehicleStateFactor.class);

stateFetcher.interpolate(TableUtils.makeKey("Toyota", "CA"), BigDecimal.valueOf(2017), VehicleStateFactor::factor, Interpolation.linear).ifPresent(r -> log.info("1. Interpolate linear factor for 2017: {}", r));
stateFetcher.interpolate(TableUtils.makeKey("Toyota", "CA"), BigDecimal.valueOf(2017), VehicleStateFactor::factor, Interpolation.stepUp).ifPresent(r -> log.info("1. Interpolate stepUp factor for 2017: {}", r));
stateFetcher.interpolate(TableUtils.makeKey("Toyota", "CA"), BigDecimal.valueOf(2017), VehicleStateFactor::factor, Interpolation.stepDown).ifPresent(r -> log.info("1. Interpolate stepDown factor for 2017: {}", r));

Output:

Interpolate linear factor for 2017: 5820
Interpolate stepUp factor for 2017: 6600
Interpolate stepDown factor for 2017: 5300

Limitations

Tables support up to one million rows each.