ClickHouse DB::Exception: Bad TTL expression

The "DB::Exception: Bad TTL expression" error in ClickHouse occurs when a TTL (Time To Live) clause in a table definition contains invalid syntax, references non-existent columns, or uses expressions that ClickHouse cannot evaluate as a valid TTL rule. The error code is BAD_TTL_EXPRESSION, and it prevents the table from being created or altered until the TTL definition is corrected.

Impact

When this error fires, the CREATE TABLE or ALTER TABLE statement is rejected outright. Any existing data pipeline that depends on the table being created or modified will stall. If the TTL clause is part of a migration script, the migration will fail and downstream schema changes may be blocked as well.

Common Causes

1. Referencing a non-date column -- TTL expressions must evaluate to a Date, Date32, DateTime, or DateTime64 value. Using an Int or String column without converting it to a date/time type triggers this error.
2. Invalid interval syntax -- malformed INTERVAL clauses or unsupported interval units (note that ClickHouse accepts both singular and plural keywords, so INTERVAL 30 DAY and INTERVAL 30 DAYS are equivalent).
3. Missing or incorrect column references -- referencing a column that does not exist in the table schema.
4. Using expressions that don't resolve to a DateTime -- for example, arithmetic on non-temporal columns without explicit casting.
5. Incorrect TTL clause placement -- placing the TTL clause in the wrong part of the CREATE TABLE statement or combining incompatible TTL rules.
6. TTL with unsupported engines -- applying TTL to a table engine that does not support it (only MergeTree family engines support TTL).

Troubleshooting and Resolution Steps

1. Check the TTL column type. Make sure the column referenced in the TTL expression resolves to a Date, Date32, DateTime, or DateTime64 value:

   DESCRIBE TABLE your_table;
   

2. Verify the interval syntax. Both singular and plural interval keywords are accepted (DAY/DAYS, MONTH/MONTHS), but the unit itself must be a supported one:

   -- Both of these are valid
   TTL created_at + INTERVAL 30 DAY
   TTL created_at + INTERVAL 30 DAYS
   

3. Confirm the column exists. If you recently renamed or removed a column, update the TTL clause to match:

   ALTER TABLE your_table MODIFY TTL event_time + INTERVAL 90 DAY;
   

4. Use explicit type conversion if needed. If your timestamp is stored as an integer (e.g. a Unix timestamp):

   TTL toDateTime(unix_ts_column) + INTERVAL 7 DAY
   

5. Check engine compatibility. Ensure your table uses a MergeTree-family engine:

   SELECT engine FROM system.tables WHERE name = 'your_table';
   

6. Test the expression in a SELECT first. Validate that the TTL expression produces a valid DateTime result:

   SELECT created_at + INTERVAL 30 DAY FROM your_table LIMIT 1;
   

Best Practices

• Use a Date, Date32, DateTime, or DateTime64 column as the base for TTL expressions to avoid type conversion issues.
• Make sure the TTL expression resolves to a Date, Date32, DateTime, or DateTime64 value, since that is what ClickHouse validates at DDL time.
• Test TTL expressions in a development environment before applying them to production tables.
• When adding TTL to existing tables, use ALTER TABLE ... MODIFY TTL and verify the current schema with SHOW CREATE TABLE first.
• Document your TTL policies alongside your schema definitions so that future changes account for existing rules.

Frequently Asked Questions

Q: Can I use a Date column directly in a TTL expression?
A: Yes. Date and Date32 columns are valid bases for TTL expressions, for example TTL event_date + INTERVAL 30 DAY. You only need toDateTime()/toDate() conversions when the source column is stored as an integer or string.

Q: Why does my TTL work in SELECT but fail in CREATE TABLE?
A: The TTL validation during DDL is stricter than expression evaluation in queries. The DDL parser checks that the expression can always resolve to a DateTime value at the schema level, not just for sample data.

Q: Can I have multiple TTL rules on the same table?
A: Yes. You can define column-level TTL (to set default values after expiry) and table-level TTL (to delete rows). Each must independently be a valid expression.

Q: How do I remove a TTL rule that is causing problems?
A: Use ALTER TABLE your_table REMOVE TTL; to strip all TTL rules from the table.