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 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 inferring nulls from sample data

Solution: Use explicit schema or handle nulls

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

Data validation

Checking for nulls

Issue: 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: 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: Need to replace nulls with default values

Solution: Use 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: 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: Deciding 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: Need to provide defaults for missing data

Solution: Use default values in 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