Handle Null Values And Invalid Values in Queries

Handle null checks and invalid data in queries using the isNull() and null() functions with if()

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1{Conditional} result{{Result Set}} repo --> 1 1 --> result
logscale
validatedValue := 
  if(isNull(inputField), then=null(),  // No input, no output
        else = if(inputField < 0, then=null(),  // Invalid input, no output
        else = inputField                // Valid input passes through
    ))

Introduction

The if() function can be used with isNull() and null() to create conditional logic that handles missing or invalid data. The isNull() function checks if a field value is missing or null, while the null() function explicitly returns a null value when needed.

In this example, the if() function is combined with isNull() and null() to implement a validation chain that first checks for missing values using isNull(), then validates numeric values, using null() to handle invalid cases.

Example incoming data might look like this:

@timestampinputField
2025-10-15T10:00:00&lt;no value&gt;
2025-10-15T10:00:01-5
2025-10-15T10:00:024
2025-10-15T10:00:03-2
2025-10-15T10:00:049
2025-10-15T10:00:050

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1{Conditional} result{{Result Set}} repo --> 1 1 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    validatedValue := 
  if(isNull(inputField), then=null(),  // No input, no output
        else = if(inputField < 0, then=null(),  // Invalid input, no output
        else = inputField                // Valid input passes through
    ))

    Creates a new field named validatedValue using a combination of functions:

    • Uses isNull() to check if inputField has no value

    • If isNull() returns true, uses null() to explicitly set a null value

    • If inputField has a value, the nested if() checks if it is negative

    • For negative values, uses null() again to set a null value

    • For zero or positive values, returns the original value from inputField

    The null() function is used to explicitly return null values rather than empty strings or zeros, ensuring consistent handling of invalid cases.

  3. Event Result set.

Summary and Results

The query is used to validate input values by using isNull() to detect missing data and null() to handle both missing and invalid values consistently.

This query is useful, for example, to implement data quality rules where both missing and invalid values need to be handled explicitly, or when downstream processing requires clear distinction between valid and invalid data.

Sample output from the incoming example data:

inputFieldvalidatedValue
&lt;no value&gt;&lt;no value&gt;
-5&lt;no value&gt;
44
-2&lt;no value&gt;
99
00

Note that isNull() catches the empty value in the first row, while the negative value check catches the negative numbers. Both cases result in null values (represented as empty fields in the CSV output) in the validatedValue field.