Decision tables

A decision table is a CASE whose arms the compiler checks before the query runs: no input falls through by accident, no two rules claim the same input, and no rule is hidden behind another. Add one keyword after CASE and the checks run on every compile, every reload, and every edit of a persisted feature.

Why plain CASE is not enough

A plain CASE takes the first arm that matches and returns NULL when none does. Both behaviors are silent. This table forgets the 18-year-olds, and nothing says so:

FeatureQL
DEFINE age := INPUT(BIGINT)
RETURN
    age,
    band := CASE
        WHEN age < 18 THEN 'minor'
        WHEN age > 18 THEN 'adult'
    END
FOR age := BIND_VALUES(ARRAY(17, 18, 19))
;
Result
AGE BIGINTBAND VARCHAR
17minor
18NULL
19adult

Two rules that both match a 25-year-old, or a rule that can never fire because an earlier one is broader, compile just as quietly. The mistake shows up later, as a wrong number.

A policy keyword states what the table should be, and the compiler proves it:

PolicyResultWhen several arms matchCompile-time checks
CASE UNIQUEThe value of the only matching armNot possible: proven disjointArms never overlap. Without ELSE, the arms cover every input, NULL included
CASE FIRSTThe value of the first matching armThe earlier arm winsEvery arm can win. Without ELSE, the arms cover every input, NULL included
CASE MULTIAn array of every matching value, in arm orderAll of themEvery arm can match. Warns when a NULL input matches no arm

Prefer UNIQUE. Its result does not depend on arm order, so every rule reads on its own. Use FIRST when the table is a list of exceptions before a general rule. Use MULTI for scorecards and tagging, where several rules apply at once.

At runtime, UNIQUE and FIRST compile to an ordinary CASE WHEN, and MULTI to one IF per arm. The checks cost nothing when the query runs.

Write rules as conditions

The searched form looks like a plain CASE WHEN. Each arm names its inputs:

FeatureQL
DEFINE
    applicant_age := INPUT(BIGINT),
    credit_score := INPUT(BIGINT),
    loan_amount := INPUT(BIGINT)
RETURN
    applicant_age,
    credit_score,
    loan_amount,
    offer := CASE UNIQUE
        WHEN 18 <= applicant_age < 25 AND credit_score >= 700 THEN 'young_prime'
        WHEN applicant_age >= 25 AND credit_score >= 700 AND loan_amount < 100000 THEN 'prime_small'
        WHEN applicant_age >= 25 AND credit_score >= 700 AND loan_amount >= 100000 THEN 'prime_large'
        WHEN applicant_age >= 18 AND credit_score < 700 THEN 'subprime'
        ELSE 'no_offer'
    END
FOR (applicant_age, credit_score, loan_amount) := BIND_VALUES(ARRAY(
    ROW(22, 720, 50000),
    ROW(40, 750, 80000),
    ROW(40, 750, 250000),
    ROW(30, 610, 20000),
    ROW(16, 800, 10000)
))
;
Result
APPLICANT_AGE BIGINTCREDIT_SCORE BIGINTLOAN_AMOUNT BIGINTOFFER VARCHAR
1680010000no_offer
2272050000young_prime
3061020000subprime
40750250000prime_large
4075080000prime_small

The 16-year-old applicant matches no arm and gets ELSE. Remove the ELSE and the compiler lists every input the arms leave out, starting with applicant_age < 18.

Write wide tables as a grid

When many rules test the same inputs, name the inputs once after the policy keyword and write one cell per input in each arm:

FeatureQL
DEFINE
    applicant_age := INPUT(BIGINT),
    credit_score := INPUT(BIGINT),
    loan_amount := INPUT(BIGINT)
RETURN
    applicant_age,
    credit_score,
    loan_amount,
    offer := CASE UNIQUE (applicant_age, credit_score, loan_amount)
        WHEN (18 <= _ < 25, _ >= 700, _          ) THEN ROW('young_prime' AS rule, 0.045e0 AS rate)
        WHEN (_ >= 25,      _ >= 700, _ < 100000 ) THEN ROW('prime_small' AS rule, 0.035e0 AS rate)
        WHEN (_ >= 25,      _ >= 700, _ >= 100000) THEN ROW('prime_large' AS rule, 0.040e0 AS rate)
        WHEN (_ >= 18,      _ < 700,  _          ) THEN ROW('subprime' AS rule, 0.060e0 AS rate)
        ELSE ROW('no_offer' AS rule, NULL(DOUBLE) AS rate)
    END
FOR (applicant_age, credit_score, loan_amount) := BIND_VALUES(ARRAY(
    ROW(22, 720, 50000),
    ROW(40, 750, 80000),
    ROW(40, 750, 250000),
    ROW(30, 610, 20000),
    ROW(16, 800, 10000)
))
;
Result
APPLICANT_AGE BIGINTCREDIT_SCORE BIGINTLOAN_AMOUNT BIGINTOFFER ROW<rule VARCHAR, rate DOUBLE>
1680010000{rule: no_offer, rate: NULL}
2272050000{rule: young_prime, rate: 0.045}
3061020000{rule: subprime, rate: 0.06}
40750250000{rule: prime_large, rate: 0.04}
4075080000{rule: prime_small, rate: 0.035}

This is the same table as above. Inside a cell, _ stands for that column's input:

CellMeaning
_Any value, NULL included
A condition on __ >= 700, 18 <= _ < 25, _ IN ('FR', 'DE'), _ IS NULL
A constantEquality: 'FR' means _ = 'FR'

A grid input can be an expression, such as CASE UNIQUE UPPER(country) WHEN 'FR' THEN …. It is computed once. Values in THEN can be anything a CASE returns, including ROW(...). All arms and ELSE must have the same type, as in a plain CASE.

TRUE in a cell is equality with TRUE, so it only fits a BOOLEAN input. To match any value, write _.

What the compiler checks

Every input is covered

Without ELSE, UNIQUE and FIRST must give a value for every possible input, NULL included:

FeatureQL
DEFINE age := INPUT(BIGINT)
RETURN band := CASE UNIQUE age
    WHEN _ < 18 THEN 'minor'
    WHEN _ > 18 THEN 'adult'
END
FOR age := BIND_VALUES(ARRAY(10, 30))
;
Result
ERROR UE/CASE-POLICY-INCOMPLETE CASE UNIQUE without ELSE must cover every input, NULL included; no arm covers AGE IS NULL (example: AGE = NULL). Add an arm for this region, or add an ELSE, when defining feature BAND. ERROR UE/CASE-POLICY-INCOMPLETE CASE UNIQUE without ELSE must cover every input, NULL included; no arm covers AGE = 18 (example: AGE = 18). Add an arm for this region, or add an ELSE, when defining feature BAND.

Each gap is its own error, with the uncovered region and a sample row you can paste into a test. When a table has several inputs, the sample gives a value for each: AGE = 18, SCORE = NULL reads "an adult with no score". At most five gaps are reported at a time.

Integers, decimals, dates and timestamps are counted step by step, so _ <= 24 and _ >= 25 leave no gap, but _ < 18 and _ > 18 leave 18. The step comes from the input's type: on DECIMAL(10,2), _ <= 99.99 and _ >= 100.00 meet exactly.

Strings are checked too. List the values you handle, then cover the rest with NOT IN and IS NULL, and the table is complete with no ELSE:

FeatureQL
DEFINE country := INPUT(VARCHAR)
RETURN
    country,
    zone := CASE UNIQUE country
        WHEN _ IN ('FR', 'DE') THEN 'eu'
        WHEN _ NOT IN ('FR', 'DE') THEN 'other'
        WHEN _ IS NULL THEN 'missing'
    END
FOR country := BIND_VALUES(ARRAY('FR', 'US', NULL))
;
Result
COUNTRY VARCHARZONE VARCHAR
FReu
NULLmissing
USother

A table that lists strings and nothing else cannot cover every string. The compiler then asks for an _ NOT IN (...) arm or an ELSE (UE/CASE-POLICY-ELSE-REQUIRED). A last arm of _ cells covers everything, so a FIRST grid that ends with WHEN (_, _) never needs an ELSE.

Rules never overlap under UNIQUE

Two UNIQUE arms that can match the same input are an error, with the shared region:

FeatureQL
DEFINE country := INPUT(VARCHAR)
RETURN zone := CASE UNIQUE country
    WHEN _ IN ('FR', 'DE') THEN 'eu'
    WHEN 'DE' THEN 'dach'
    ELSE 'other'
END
FOR country := BIND_VALUES(ARRAY('FR', 'DE'))
;
Result
ERROR UE/CASE-POLICY-OVERLAP Under CASE UNIQUE, at most one arm may match any input; arms 1 and 2 both match where COUNTRY = 'DE' (example: COUNTRY = 'DE'). Narrow the arms so they no longer share this region, or use CASE FIRST if the earlier arm should win, when defining feature ZONE.

Narrow one arm, or switch to FIRST if the earlier arm is meant to win. When three or more arms share a region, one error names them all.

Every FIRST rule can win

FIRST lets an earlier arm take precedence. That is how you write exceptions before a general rule:

FeatureQL
DEFINE country := INPUT(VARCHAR), age := INPUT(BIGINT)
RETURN
    country,
    age,
    shipping := CASE FIRST (country, age)
        WHEN ('FR', _ < 18) THEN 'blocked'
        WHEN ('FR', _     ) THEN 'domestic'
        WHEN (_,    _     ) THEN 'international'
    END
FOR (country, age) := BIND_VALUES(ARRAY(ROW('FR', 16), ROW('FR', 40), ROW('US', 16)))
;
Result
COUNTRY VARCHARAGE BIGINTSHIPPING VARCHAR
FR16blocked
FR40domestic
US16international

The order matters, so the compiler checks it. An arm that earlier arms already cover entirely, alone or together, is an error:

FeatureQL
DEFINE age := INPUT(BIGINT)
RETURN band := CASE FIRST age
    WHEN _ < 65 THEN 'working_age'
    WHEN _ < 18 THEN 'minor'
    ELSE 'senior'
END
FOR age := BIND_VALUES(ARRAY(10))
;
Result
ERROR UE/CASE-POLICY-DEAD-ARM Under CASE FIRST, every arm must be able to win; arm 2 never fires because arm 1 above it matches every input it matches. Remove it, or move it above that arm if it should take precedence, when defining feature BAND.

Handle NULL on purpose

A condition matches only when it is TRUE. A NULL input makes _ < 18, _ != 'FR' and _ NOT IN (...) NULL, not TRUE, so none of them match it. Only _ IS NULL, _ IS DISTINCT FROM ... and a bare _ cell match a NULL input.

A plain CASE WHEN country = 'FR' THEN 'domestic' ELSE 'international' END sends a missing country abroad. Under a policy, the compiler makes you decide:

FeatureQL
DEFINE country := INPUT(VARCHAR)
RETURN
    country,
    shipping := CASE UNIQUE country
        WHEN 'FR' THEN 'domestic'
        WHEN _ != 'FR' THEN 'international'
        WHEN _ IS NULL THEN 'address_missing'
    END
FOR country := BIND_VALUES(ARRAY('FR', 'DE', NULL))
;
Result
COUNTRY VARCHARSHIPPING VARCHAR
DEinternational
FRdomestic
NULLaddress_missing

Without the IS NULL arm, this table is an error: no arm covers COUNTRY IS NULL. If a missing country should ship internationally, say so with WHEN _ IS DISTINCT FROM 'FR': it matches every value but 'FR', NULL included, and the table is complete with two arms.

Collect every matching rule with MULTI

CASE MULTI returns an array of every matching value, in arm order. With no match, it returns an empty array, or ARRAY(<else value>) when there is an ELSE. Two arms that return the same value both appear. Combine it with TRANSFORM to score or rank the hits:

FeatureQL
DEFINE credit_score := INPUT(BIGINT), months_employed := INPUT(BIGINT)
RETURN
    credit_score,
    months_employed,
    risk_hits := CASE MULTI (credit_score, months_employed)
        WHEN (_ < 600,        _        ) THEN ROW('R01' AS rule, 40 AS points)
        WHEN (600 <= _ < 700, _        ) THEN ROW('R02' AS rule, 20 AS points)
        WHEN (_,              _ < 12   ) THEN ROW('R03' AS rule, 15 AS points)
        WHEN (_ IS NULL,      _        ) THEN ROW('R04' AS rule, 30 AS points)
        WHEN (_,              _ IS NULL) THEN ROW('R05' AS rule, 10 AS points)
    END,
    risk_score := COALESCE(risk_hits.TRANSFORM(RETURN SUM(points)).UNWRAP_ONE(), 0)
FOR (credit_score, months_employed) := BIND_VALUES(ARRAY(ROW(550, 6), ROW(650, 48), ROW(780, 60)))
;
Result
CREDIT_SCORE BIGINTMONTHS_EMPLOYED BIGINTRISK_HITS ARRAY<ROW<rule VARCHAR, points BIGINT>>RISK_SCORE BIGINT
5506[{rule: R01, points: 40}, {rule: R03, points: 15}]55
65048[{rule: R02, points: 20}]20
78060[]0

MULTI has no completeness rule, since an empty result is a valid answer. It warns when a NULL input would match no arm, and when two arms match exactly the same inputs. SUM over no hits is NULL, which is why the score uses COALESCE. To keep the two heaviest reasons, use TRANSFORM(RETURN rule ORDER BY points DESC LIMIT 2), with a sort key that has no ties.

What a condition may contain

The checks are exact because each condition compares one input with constants:

  • comparisons, chained comparisons (18 <= _ < 25), BETWEEN, NOT BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL, IS DISTINCT FROM and IS NOT DISTINCT FROM;
  • AND between conditions, and OR or NOT between conditions on the same input, such as age < 18 OR age > 65;
  • a bare BOOLEAN input, which means = TRUE, and NOT on it, which means = FALSE;
  • constants: literals, casts of literals (CAST(100 AS _@CURRENCY.USD) for a unit input), arithmetic on literals, and features with no input, such as cap := 100000;
  • inputs of type integer, DECIMAL, FLOAT and DOUBLE, DATE, TIMESTAMP, BOOLEAN, VARCHAR and entity keys. Strings and keys accept equality, IN and NOT IN, not ordering.

A DOUBLE comparison runs at a precision of N decimals: _ <.2 0.5e0 compares at 2 decimals, and a bare < uses 15 and warns. Every arm must use the same N on one input, and every constant must be exact at that precision (1.00e0 at .2, not 1.004e0). The step is then one unit of the last decimal, so _ <=.2 1.00e0 and _ >=.2 1.01e0 leave no gap.

Anything else is an error that names the sub-expression and the fix. For example, loan_amount > income * 3 compares two inputs:

FeatureQL
DEFINE loan_amount := INPUT(BIGINT), income := INPUT(BIGINT)
RETURN decision := CASE UNIQUE
    WHEN loan_amount > income * 3 THEN 'refer'
    ELSE 'ok'
END
FOR CROSS loan_amount := BIND_VALUES(ARRAY(1)), income := BIND_VALUES(ARRAY(1))
;
Result
ERROR UE/CASE-POLICY-CONDITION-NOT-ANALYZABLE

Name that condition as a BOOLEAN feature and use it as an input. The table stays checkable, and the condition gets a name a reader can find:

FeatureQL
DEFINE
    loan_amount := INPUT(BIGINT),
    income := INPUT(BIGINT),
    over_leveraged := loan_amount > income * 3
RETURN
    loan_amount,
    income,
    decision := CASE UNIQUE over_leveraged
        WHEN TRUE THEN 'refer'
        WHEN FALSE THEN 'ok'
        WHEN _ IS NULL THEN 'missing_data'
    END
FOR (loan_amount, income) := BIND_VALUES(ARRAY(ROW(100000, 20000), ROW(50000, 40000), ROW(50000, NULL)))
;
Result
LOAN_AMOUNT BIGINTINCOME BIGINTDECISION VARCHAR
10000020000refer
5000040000ok
50000NULLmissing_data

LIKE and other pattern matches follow the same fix. OR across two inputs is split into two arms.

The compiler treats inputs as independent. If one input is computed from another, such as age and age_band := CASE WHEN age < 18 …, some combinations never occur, and a check could reject a correct table. Using both in one table is an error: keep only one of them.

Reuse a table with MACRO

A MACRO can take a bound as a parameter. The table is checked at each call, with that call's value:

FeatureQL
DEFINE
    score := INPUT(BIGINT),
    cutoff := INPUT(BIGINT),
    pass_fail := MACRO(CASE UNIQUE score
        WHEN _ >= cutoff THEN 'pass'
        WHEN _ < cutoff THEN 'fail'
        WHEN _ IS NULL THEN 'absent'
    END USING score, cutoff)
RETURN
    score,
    standard := pass_fail(score, 50),
    honors := pass_fail(score, 80)
FOR score := BIND_VALUES(ARRAY(45, 65, 90, NULL))
;
Result
SCORE BIGINTSTANDARD VARCHARHONORS VARCHAR
45failfail
65passfail
90passpass
NULLabsentabsent

A call whose constant leaves a gap is an error at that call. With _ > cutoff and _ < cutoff and no ELSE, pass_fail(score, 50) reports SCORE = 50 (and SCORE IS NULL). The argument must be a constant: passing another input is UE/CASE-POLICY-NON-CONSTANT-BOUND.

Warnings

These do not block the query:

WarningMeaning
CASE-POLICY-ELSE-UNREACHABLEThe arms already cover every input, so ELSE never applies
CASE-POLICY-BOUNDARY-GAPOne value between two arms, such as 18 between _ < 18 and _ > 18, falls to ELSE
CASE-POLICY-BOUNDARY-OVERLAPUnder FIRST, two arms share one boundary value, such as _ <= 18 and _ >= 18, and the earlier arm takes it
CASE-MULTI-NULL-UNMATCHEDA NULL input matches no MULTI arm and returns an empty array
CASE-MULTI-DUPLICATE-ARMTwo MULTI arms match exactly the same inputs, even when written differently (_ < 5 and _ <= 4)
CASE-POLICY-KEYWORD-SHADOWS-FEATUREA feature named first, unique or multi sits next to a policy CASE

FIRST, UNIQUE and MULTI are keywords directly after CASE. For a simple CASE on a feature with one of those names, write CASE (first) WHEN ….

Formatting and storage

A persisted decision table is stored as written, arm order included. SHOW CREATE and FORMAT print the policy form with the grid aligned in columns, and reloading the feature runs the checks again. FORMAT (FIX) writes an equality cell _ = 'FR' as the bare constant 'FR'.

Very large tables have a fixed analysis budget, counted in steps so the result is the same on every machine. A table that exceeds it is an error (UE/CASE-POLICY-ANALYSIS-BUDGET-EXCEEDED), never a silent skip of the checks. Add an ELSE or split the table.

Next