ClickHouse DB::Exception: Unknown table

The "DB::Exception: Unknown table" error in ClickHouse means the server could not find a table you referenced in your query. This can happen when the table does not exist, you are connected to the wrong database, or the table name is misspelled. The error code is UNKNOWN_TABLE.

Impact

Any query that references a nonexistent table fails immediately. This affects SELECT, INSERT, ALTER, and DROP statements alike. In production, a missing table can break application queries, materialized view chains, and distributed query routing.

Common Causes

Table name is misspelled -- a typo or incorrect casing in the table name.
Wrong database context -- you are connected to one database but the table lives in another.
Table was dropped or renamed -- someone else (or an automated process) removed or renamed the table.
Table not yet created -- queries referencing a table that has not been created yet, common during schema migrations.
Replicated table not synced -- on a cluster, the table may exist on some replicas but not all.
Temporary table scope -- temporary tables are session-scoped and not visible to other sessions or connections.

Troubleshooting and Resolution Steps

Verify the table exists. List tables in the current database:
```
SHOW TABLES;
```
Check the correct database. If unsure which database you are connected to:
```
SELECT currentDatabase();
```
Use a fully qualified table name. Always specify both database and table:
```
SELECT * FROM my_database.my_table LIMIT 10;
```
Search across all databases. If you are not sure where the table lives:
```
SELECT database, name FROM system.tables WHERE name = 'my_table';
```

Check for case sensitivity. Table names in ClickHouse are case-sensitive:

-- These are different tables
SELECT * FROM Events;
SELECT * FROM events;

Verify on all cluster nodes. In a distributed setup, confirm the table exists on the node handling the query:

SELECT host_name, database, name
FROM clusterAllReplicas('your_cluster', system.tables)
WHERE name = 'my_table';

Check if the table was recently dropped. Look at the query log for DROP statements:

SELECT query, event_time, user
FROM system.query_log
WHERE query LIKE '%DROP%my_table%'
ORDER BY event_time DESC
LIMIT 5;

Best Practices

Always use fully qualified table names (database.table) in application code and scheduled queries to avoid database context issues.
Implement schema migration tooling that validates table existence before running dependent queries.
On clustered deployments, use ON CLUSTER clauses for DDL to ensure tables are created on all nodes simultaneously.
Monitor schema changes with alerting on DROP TABLE and RENAME TABLE operations.
Keep a schema registry or version-controlled DDL so dropped tables can be quickly recreated.

Frequently Asked Questions

Q: My table exists but I still get UNKNOWN_TABLE. What should I check?
A: Confirm you are connected to the right database, check for case-sensitivity issues, and verify the table is accessible on the specific node your client is connected to. Also check that you have the necessary permissions -- in some configurations, lack of SELECT permission can manifest as the table not being visible.

Q: Can this error occur with Distributed tables?
A: Yes. A Distributed table routes queries to shards. If the local table referenced by the Distributed engine does not exist on a shard, you will see this error on that shard. Ensure the local table is created on all shards.

Q: How do I check if a temporary table exists?
A: Temporary tables are not listed in system.tables. They are scoped to the current session. Use EXISTS TEMPORARY TABLE table_name to check. Remember that temporary tables are invisible to other sessions.

Q: I dropped a table by accident. Can I recover it?
A: ClickHouse does not have an automatic undo for DROP TABLE. However, if you used DROP TABLE ... SYNC, the data files may still be on disk briefly. Your best option is restoring from a backup. Consider using allow_drop_detached and detaching tables instead of dropping them for an extra safety net.