UNKNOWN_IDENTIFIER ClickHouse error

This error usually means you're referencing a column, table, or function that doesn't exist. Check your spelling and make sure the referenced object exists in your schema.

The UNKNOWN_IDENTIFIER error in ClickHouse (and Tinybird) happens when you reference a column, table, function, or other identifier that doesn't exist in the current context. This is the most common error users encounter when writing SQL queries.

What causes this error

You'll typically see it when:

  • Referencing a column that doesn't exist in the table
  • Calling a function that doesn't exist or is misspelled
  • Using an alias that hasn't been defined
  • Referencing a variable or parameter that doesn't exist
  • Referencing a non-existent column in CTEs or subqueries

Note: Using a table name that doesn't exist typically throws a UNKNOWN_TABLE error (code 60), not UNKNOWN_IDENTIFIER.

Example errors

Fails: column doesn't exist
SELECT user_id, email, non_existent_column FROM events
Fails: table doesn't exist
SELECT * FROM non_existent_table
Fails: function doesn't exist
SELECT non_existent_function(column) FROM events

Note: The table example above actually throws a UNKNOWN_TABLE error (code 60), not UNKNOWN_IDENTIFIER. This is because ClickHouse distinguishes between unknown tables and unknown identifiers within existing tables.

Fails: undefined alias
SELECT user_id, COUNT(*) as count FROM events GROUP BY user_id HAVING count > 10

How to fix it

Check column names

Use the system.columns table to see what columns exist:

Check table structure
SELECT * FROM system.columns WHERE table = 'events'

Check table names

List all tables in your database:

List all tables
SELECT * FROM system.tables

Check function names

Verify function names in the ClickHouse documentation or use the system.functions table:

List available functions
SELECT * FROM system.functions WHERE name LIKE '%date%'

Common patterns and solutions

Case sensitivity

ClickHouse is case-sensitive. Make sure your identifiers match exactly:

Wrong: wrong case
SELECT User_ID FROM events
Correct: proper case
SELECT user_id FROM events

Schema qualification

If you have multiple databases, qualify your table names:

Qualify with database name
SELECT * FROM my_database.events

Check for typos

Common typos that cause this error:

  • user_id vs userid
  • created_at vs createdat
  • timestamp vs time_stamp

Helpful error messages

ClickHouse often provides helpful suggestions in UNKNOWN_IDENTIFIER errors:

Error with suggestion
SELECT User_ID FROM events
-- Error: Unknown expression identifier `User_ID` in scope SELECT User_ID FROM events. 
-- Maybe you meant: ['user_id']

Common Table Expressions (CTEs)

When using CTEs with WITH, referencing non-existent columns can cause UNKNOWN_IDENTIFIER errors:

Wrong: referencing non-existent column in CTE
WITH user_stats AS (
    SELECT user_id, COUNT(*) as count FROM events GROUP BY user_id
)
SELECT non_existent_column FROM user_stats
Correct: reference only existing columns
WITH user_stats AS (
    SELECT user_id, COUNT(*) as count FROM events GROUP BY user_id
)
SELECT user_id, count FROM user_stats WHERE user_id > 10

Tinybird-specific notes

In Tinybird, this error often occurs when:

  • Referencing columns that don't exist in your Data Source schema
  • Using table names that don't match your Data Source names
  • Referencing Pipes that haven't been created yet

See also