---
last_updated: "2026-09-22T11:50:48.000Z"
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 the 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® infers nulls from sample data.

**Solution**: Use an 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**: You 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**: You 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**: You need to replace nulls with default values.

**Solution**: Use the 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**: You have 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**: You need to decide 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**: You need to provide defaults for missing data.

**Solution**: Use default values in the 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/core-concepts/data-sources)