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