Unexpected null troubleshooting

Common issues and solutions for unexpected null values in ClickHouse® and Tinybird.

Common causes

Missing data in source

Issue: Source data has missing values.

Solution: Handle missing data in the schema or queries.

-- Use default values for missing data
SELECT
  COALESCE(nullable_column, 'default_value') as safe_column
FROM events

Schema inference issues

Issue: ClickHouse® infers nulls from sample data.

Solution: Use an explicit schema or handle nulls.

-- Use explicit schema
SCHEMA >
  id UInt32,
  user_id String,
  value Float64 DEFAULT 0

Data validation

Checking for nulls

Issue: You need to identify null values in data.

Solution: Use null-checking functions.

-- Check for nulls
SELECT
  column_name,
  CASE
    WHEN column_name IS NULL THEN 'NULL'
    ELSE 'NOT NULL'
  END as null_status
FROM events

Counting nulls

Issue: You need to understand null distribution.

Solution: Count null values.

-- Count nulls by column
SELECT
  count() as total_rows,
  countIf(column_name IS NULL) as null_count,
  countIf(column_name IS NOT NULL) as not_null_count
FROM events

Handling strategies

Using COALESCE

Issue: You need to replace nulls with default values.

Solution: Use the COALESCE function.

-- Replace nulls with defaults
SELECT
  COALESCE(string_column, 'unknown') as safe_string,
  COALESCE(numeric_column, 0) as safe_numeric
FROM events

Using CASE statements

Issue: You have complex null handling logic.

Solution: Use CASE statements.

-- Complex null handling
SELECT
  CASE
    WHEN column_name IS NULL THEN 'missing'
    WHEN column_name = '' THEN 'empty'
    ELSE column_name
  END as processed_column
FROM events

Schema design

Nullable vs not nullable

Issue: You need to decide when to use nullable columns.

Solution: Use nullable only for truly optional data.

-- Use nullable for optional data
CREATE TABLE events (
  id UInt32,           -- Required, not nullable
  user_id String,      -- Required, not nullable
  optional_field Nullable(String)  -- Optional, nullable
)

Default values

Issue: You need to provide defaults for missing data.

Solution: Use default values in the schema.

-- Use defaults instead of nullable
CREATE TABLE events (
  id UInt32,
  user_id String,
  value Float64 DEFAULT 0  -- Default instead of nullable
)

Best practices

  1. Validate data - Check for nulls in source data.
  2. Use defaults - Provide default values when possible.
  3. Handle nulls explicitly - Use COALESCE and CASE statements.
  4. Document expectations - Keep track of expected null behavior.
  5. Monitor null patterns - Track null distribution over time.
Updated