ClickHouse DB::Exception: Suspicious type for LowCardinality

The "DB::Exception: Suspicious type for LowCardinality" error is a safety check that ClickHouse triggers when you wrap a data type in LowCardinality() that is unlikely to benefit from dictionary encoding. LowCardinality works by storing a dictionary of unique values and replacing each row's value with a small index into that dictionary. Types that already have a small fixed size (like UInt8 or Float64) don't gain much from this encoding and may even waste memory due to dictionary overhead.

Impact

The CREATE TABLE or ALTER statement fails. This is a protective error rather than a critical one — ClickHouse is telling you that LowCardinality probably isn't helpful for this type. You can either remove LowCardinality or enable the setting to override the check.

Common Causes

1. LowCardinality on small numeric types — LowCardinality(UInt8), LowCardinality(Int16), or similar where the values are already compact.
2. LowCardinality on Float types — LowCardinality(Float32) or LowCardinality(Float64) where cardinality is typically high.
3. LowCardinality on Date/DateTime — while sometimes useful, ClickHouse flags these as suspicious because the benefit depends heavily on the data distribution.
4. Generated schemas from tools — ORMs or migration scripts that blindly apply LowCardinality to all columns.
5. LowCardinality(Nullable(UInt8)) — the combination of Nullable with a small fixed type.

Troubleshooting and Resolution Steps

1. Remove LowCardinality if it's not beneficial. For small fixed-size types, just use the base type directly:

   -- Suspicious
   CREATE TABLE t (status LowCardinality(UInt8)) ENGINE = MergeTree() ORDER BY tuple();
   
   -- Better
   CREATE TABLE t (status UInt8) ENGINE = MergeTree() ORDER BY tuple();
   

2. Use LowCardinality where it makes sense. It's most effective on String columns with a limited number of distinct values (e.g., country codes, status labels, category names):

   CREATE TABLE t (country LowCardinality(String)) ENGINE = MergeTree() ORDER BY tuple();
   

3. Override the check if you know what you're doing. If you genuinely have low-cardinality data in a numeric or date column, enable the setting:

   SET allow_suspicious_low_cardinality_types = 1;
   CREATE TABLE t (status LowCardinality(UInt8)) ENGINE = MergeTree() ORDER BY tuple();
   

   You can also set this in the server config to apply it globally.

4. Consider Enum instead. For columns with a small, known set of values, Enum8 or Enum16 may be more appropriate than LowCardinality on a numeric type:

   CREATE TABLE t (
       status Enum8('pending' = 0, 'active' = 1, 'archived' = 2)
   ) ENGINE = MergeTree() ORDER BY tuple();
   

Best Practices

• Apply LowCardinality primarily to String and FixedString columns with relatively few distinct values (up to tens of thousands).
• Don't use LowCardinality on types that are already 1-8 bytes wide unless you have profiled and confirmed the benefit.
• Prefer Enum for columns with a fixed, small set of known values.
• Review auto-generated schemas to ensure LowCardinality is applied judiciously.

Frequently Asked Questions

Q: How many distinct values is "low cardinality"?
A: There's no strict threshold, but LowCardinality is most beneficial when the number of distinct values is in the thousands or low millions. For String columns, even a few million distinct values can benefit because the dictionary avoids storing repeated long strings.

Q: Does allow_suspicious_low_cardinality_types have any downside?
A: Enabling it lets you create the table, but the LowCardinality encoding adds overhead (the dictionary) that may outweigh the benefits for compact types. Profile your actual data to decide.

Q: Can LowCardinality hurt performance?
A: In rare cases, yes. If the cardinality is very high (approaching the total row count), the dictionary becomes large and provides no compression benefit while adding indirection overhead. For String columns this is uncommon, but for numeric types it's more likely.

Q: Is LowCardinality(Nullable(String)) valid?
A: Yes. LowCardinality(Nullable(String)) is the correct way to have a nullable, dictionary-encoded string column. ClickHouse handles this combination well.