Schema and typing

Common issues and solutions for schema design and data typing in ClickHouse® and Tinybird.

Overview

This section covers troubleshooting for schema design issues and data type problems that can cause errors and performance issues.

Schema categories

Type mismatch

Common type mismatch issues:

  • TYPE_MISMATCH - Incompatible data types in operations
  • NO_COMMON_TYPE - No common type for operations
  • String to numeric conversion - Converting between types safely
  • Date type mismatches - Mixing different date types

View type mismatch troubleshooting →

Nullable vs not nullable

Deciding when to use nullable columns:

  • When to use nullable - Optional data and external sources
  • When to use not nullable - Required fields and performance-critical columns
  • Performance considerations - Impact of nullable columns
  • Migration strategies - Converting between nullable types

View nullable vs not nullable troubleshooting →

Inferred wrong type

Issues with ClickHouse® type inference:

  • String inferred as numeric - Type inference problems
  • Numeric inferred as string - Mixed data type issues
  • Schema hints - Using SCHEMA to override inference
  • Debugging type inference - Using toTypeName() function

View inferred wrong type troubleshooting →

How to debug types

Tools and techniques for debugging types:

  • Using toTypeName() - Checking column and expression types
  • Schema debugging - Examining table schemas
  • Data validation - Checking for type inconsistencies
  • Type conversion debugging - Understanding conversion issues

View how to debug types →

Common patterns

Type conversion issues

Strategies for handling type conversions:

  1. Use safe conversions - Use toTypeOrNull() functions
  2. Check data types - Use toTypeName() to verify types
  3. Handle nulls explicitly - Check for null values before conversions
  4. Validate inputs - Ensure data meets type requirements

Schema design principles

Best practices for schema design:

  1. Use explicit types - Don't rely on type inference
  2. Be consistent - Use same types across related tables
  3. Consider performance - Choose types based on usage patterns
  4. Plan for growth - Design schemas that can evolve
  5. Document decisions - Keep track of type choices and reasons

Best practices

  1. Always specify types - Use explicit type declarations
  2. Use safe conversions - Prefer functions that handle errors gracefully
  3. Test with real data - Verify types with actual data samples
  4. Monitor type issues - Track type-related errors and performance
  5. Document type decisions - Keep track of type choices and trade-offs
Updated