---
title: Unexpected null troubleshooting
meta:
  description: Common issues and solutions for unexpected null values in ClickHouse and Tinybird.
---

# 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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

## Related documentation

- [Data types](/sql-reference/data-types)
- [Data source configuration](/forward/get-data-in/data-sources)