ClickHouse DB::Exception: Unknown identifier

The "DB::Exception: Unknown identifier" error in ClickHouse indicates that a column name, alias, or expression referenced in your query cannot be found in the current scope. ClickHouse resolves identifiers during query analysis, and if a name does not match any column in the referenced tables or any defined alias, it raises the UNKNOWN_IDENTIFIER error.

Impact

The query is rejected before execution begins. This is a compile-time error in ClickHouse's query processing pipeline, so no data is scanned and no resources are consumed. However, in automated workflows, this error can block entire pipelines until corrected.

Common Causes

1. Misspelled column name -- a typo in the column name that does not match the table schema.
2. Column does not exist in the table -- referencing a column that was never created, was dropped, or exists in a different table.
3. Alias scoping issues -- trying to use a SELECT alias in a WHERE or GROUP BY clause where ClickHouse has not yet resolved it (though ClickHouse is more permissive than some databases here).
4. Missing table qualifier in JOINs -- when multiple tables have columns with different names and you reference one without specifying which table it belongs to.
5. Subquery column visibility -- referring to an inner subquery's column from an outer scope where it is not exposed.
6. Schema changes -- the table was altered (column renamed or dropped) after the query was written.

Troubleshooting and Resolution Steps

1. Check the exact column name. Use DESCRIBE TABLE to see all available columns:

   DESCRIBE TABLE your_database.your_table;
   

2. Verify the spelling. ClickHouse column names are case-sensitive. EventDate and eventdate are different identifiers:

   -- This will fail if the column is actually named EventDate
   SELECT eventdate FROM your_table;
   

3. Qualify column names in JOINs. When joining tables, always use table prefixes to avoid ambiguity:

   SELECT a.user_id, b.order_total
   FROM users AS a
   JOIN orders AS b ON a.user_id = b.user_id;
   

4. Check alias visibility. In ClickHouse, SELECT aliases are generally available in WHERE, GROUP BY, and HAVING. But if you are using a subquery, the alias must be part of the outer SELECT:

   -- The alias 'total' from a subquery must be in its SELECT list
   SELECT total FROM (
       SELECT sum(amount) AS total FROM orders
   );
   

5. Review recent schema changes. Check if the column was recently renamed or removed:

   SHOW CREATE TABLE your_table;
   

6. Look for invisible characters. Occasionally, copy-pasting from documents introduces non-printable characters into column names. Check your query in a plain text editor.

Best Practices

• Use DESCRIBE TABLE regularly to verify column names, especially after schema migrations.
• Adopt a consistent naming convention (e.g., all lowercase with underscores) to reduce case-sensitivity mistakes.
• Always qualify column names with table aliases in multi-table queries.
• When writing queries programmatically, validate column names against the schema before building the SQL string.
• Use ClickHouse's system.columns table for programmatic schema inspection:

  SELECT name, type FROM system.columns WHERE database = 'db' AND table = 'tbl';
  

Frequently Asked Questions

Q: ClickHouse says "Unknown identifier" but I can see the column in DESCRIBE TABLE. What is going on?
A: Check for case sensitivity issues, invisible Unicode characters, or scoping problems. Also confirm you are querying the correct database -- use fully qualified table names like database.table to be sure.

Q: Can I use SELECT aliases in the WHERE clause in ClickHouse?
A: Yes, unlike some other databases, ClickHouse allows referencing SELECT aliases in WHERE, GROUP BY, HAVING, and ORDER BY clauses within the same query level.

Q: I get this error after a JOIN. How do I figure out which table the column should come from?
A: Run DESCRIBE TABLE on each table involved in the JOIN. Then prefix the column with the appropriate table alias. If both tables have a column with the same name, ClickHouse requires disambiguation.

Q: Does this error apply to ARRAY JOIN columns?
A: Yes. When using ARRAY JOIN, the resulting column name depends on the alias you assign. If you reference the original array column name after the ARRAY JOIN, it may not resolve as expected. Use explicit aliases to be safe.