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:
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))
;| AGE BIGINT | BAND VARCHAR |
|---|---|
| 17 | minor |
| 18 | NULL |
| 19 | adult |
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:
| Policy | Result | When several arms match | Compile-time checks |
|---|---|---|---|
CASE UNIQUE | The value of the only matching arm | Not possible: proven disjoint | Arms never overlap. Without ELSE, the arms cover every input, NULL included |
CASE FIRST | The value of the first matching arm | The earlier arm wins | Every arm can win. Without ELSE, the arms cover every input, NULL included |
CASE MULTI | An array of every matching value, in arm order | All of them | Every 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:
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)
))
;| APPLICANT_AGE BIGINT | CREDIT_SCORE BIGINT | LOAN_AMOUNT BIGINT | OFFER VARCHAR |
|---|---|---|---|
| 16 | 800 | 10000 | no_offer |
| 22 | 720 | 50000 | young_prime |
| 30 | 610 | 20000 | subprime |
| 40 | 750 | 250000 | prime_large |
| 40 | 750 | 80000 | prime_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:
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)
))
;| APPLICANT_AGE BIGINT | CREDIT_SCORE BIGINT | LOAN_AMOUNT BIGINT | OFFER ROW<rule VARCHAR, rate DOUBLE> |
|---|---|---|---|
| 16 | 800 | 10000 | {rule: no_offer, rate: NULL} |
| 22 | 720 | 50000 | {rule: young_prime, rate: 0.045} |
| 30 | 610 | 20000 | {rule: subprime, rate: 0.06} |
| 40 | 750 | 250000 | {rule: prime_large, rate: 0.04} |
| 40 | 750 | 80000 | {rule: prime_small, rate: 0.035} |
This is the same table as above. Inside a cell, _ stands for that column's input:
| Cell | Meaning |
|---|---|
_ | Any value, NULL included |
A condition on _ | _ >= 700, 18 <= _ < 25, _ IN ('FR', 'DE'), _ IS NULL |
| A constant | Equality: '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:
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))
;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:
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))
;| COUNTRY VARCHAR | ZONE VARCHAR |
|---|---|
| FR | eu |
| NULL | missing |
| US | other |
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:
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'))
;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:
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)))
;| COUNTRY VARCHAR | AGE BIGINT | SHIPPING VARCHAR |
|---|---|---|
| FR | 16 | blocked |
| FR | 40 | domestic |
| US | 16 | international |
The order matters, so the compiler checks it. An arm that earlier arms already cover entirely, alone or together, is an error:
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))
;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:
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))
;| COUNTRY VARCHAR | SHIPPING VARCHAR |
|---|---|
| DE | international |
| FR | domestic |
| NULL | address_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:
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)))
;| CREDIT_SCORE BIGINT | MONTHS_EMPLOYED BIGINT | RISK_HITS ARRAY<ROW<rule VARCHAR, points BIGINT>> | RISK_SCORE BIGINT |
|---|---|---|---|
| 550 | 6 | [{rule: R01, points: 40}, {rule: R03, points: 15}] | 55 |
| 650 | 48 | [{rule: R02, points: 20}] | 20 |
| 780 | 60 | [] | 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 FROMandIS NOT DISTINCT FROM; ANDbetween conditions, andORorNOTbetween conditions on the same input, such asage < 18 OR age > 65;- a bare
BOOLEANinput, which means= TRUE, andNOTon 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 ascap := 100000; - inputs of type integer,
DECIMAL,FLOATandDOUBLE,DATE,TIMESTAMP,BOOLEAN,VARCHARand entity keys. Strings and keys accept equality,INandNOT 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:
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))
;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:
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)))
;| LOAN_AMOUNT BIGINT | INCOME BIGINT | DECISION VARCHAR |
|---|---|---|
| 100000 | 20000 | refer |
| 50000 | 40000 | ok |
| 50000 | NULL | missing_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:
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))
;| SCORE BIGINT | STANDARD VARCHAR | HONORS VARCHAR |
|---|---|---|
| 45 | fail | fail |
| 65 | pass | fail |
| 90 | pass | pass |
| NULL | absent | absent |
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:
| Warning | Meaning |
|---|---|
CASE-POLICY-ELSE-UNREACHABLE | The arms already cover every input, so ELSE never applies |
CASE-POLICY-BOUNDARY-GAP | One value between two arms, such as 18 between _ < 18 and _ > 18, falls to ELSE |
CASE-POLICY-BOUNDARY-OVERLAP | Under FIRST, two arms share one boundary value, such as _ <= 18 and _ >= 18, and the earlier arm takes it |
CASE-MULTI-NULL-UNMATCHED | A NULL input matches no MULTI arm and returns an empty array |
CASE-MULTI-DUPLICATE-ARM | Two MULTI arms match exactly the same inputs, even when written differently (_ < 5 and _ <= 4) |
CASE-POLICY-KEYWORD-SHADOWS-FEATURE | A 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
- Semantic modeling — the other properties the compiler checks for you
- Pure transformations — plain
CASE WHEN,IF()andCOALESCE() - TRANSFORM — work with the arrays
CASE MULTIreturns