---
title: ILLEGAL_FINAL ClickHouse error
meta:
  description: Learn how to fix the ILLEGAL_FINAL error in ClickHouse and Tinybird. Understand what causes it and see examples of how to resolve it
---

# ILLEGAL_FINAL ClickHouse error

{% callout %}
This error usually means you're trying to use the FINAL keyword in a context where it's not allowed. FINAL is only supported with certain table engines and in specific query contexts.
{% /callout %}

The `ILLEGAL_FINAL` error in ClickHouse (and Tinybird) happens when you try to use the `FINAL` keyword in a context where it's not supported. The `FINAL` keyword is used to get the latest version of rows in tables with certain engines like `ReplacingMergeTree`, but it has specific usage requirements.

## What causes this error

You'll typically see it when:

* Using FINAL with table engines that don't support it
* Using FINAL in subqueries or CTEs
* Using FINAL with certain table engines
* Using FINAL in JOIN operations incorrectly
* Using FINAL in contexts where it's not allowed

{% callout type="caution" %}
FINAL should be used sparingly due to performance cost; it forces merging of parts. FINAL cannot be combined with arrayJoin or WHERE conditions before FINAL.
{% /callout %}

## Example errors

```sql {% title="Fails: FINAL with unsupported engine" %}
SELECT * FROM events FINAL WHERE user_id = 123
```

```sql {% title="Fails: FINAL in subquery" %}
SELECT * FROM (SELECT * FROM events FINAL) t
```

```sql {% title="Fails: FINAL in JOIN" %}
SELECT * FROM users JOIN events FINAL ON users.id = events.user_id
```

```sql {% title="Fails: FINAL with wrong syntax" %}
SELECT FINAL * FROM events
```

## How to fix it

### Use FINAL with supported engines

Only use FINAL with engines that support it:

```sql {% title="Fix: use with supported engine" %}
SELECT * FROM events_replacing FINAL WHERE user_id = 123
```

### Remove FINAL from unsupported contexts

Don't use FINAL in subqueries or CTEs:

```sql {% title="Fix: remove FINAL from subquery" %}
SELECT * FROM events WHERE user_id = 123
```

### Use proper FINAL syntax

Use FINAL correctly in supported contexts:

```sql {% title="Fix: correct FINAL syntax" %}
SELECT * FROM events_replacing FINAL WHERE user_id = 123
```

{% callout type="caution" %}
Use FINAL at the end of the query. `SELECT ... FROM table FINAL WHERE ...` is wrong—WHERE goes after FINAL.
{% /callout %}

### Check table engine

Verify the table engine supports FINAL:

```sql {% title="Check table engine" %}
SELECT * FROM system.tables WHERE name = 'events'
```

## Common patterns and solutions

### Using FINAL with ReplacingMergeTree

FINAL works with ReplacingMergeTree:

```sql {% title="FINAL with ReplacingMergeTree" %}
SELECT * FROM events_replacing FINAL WHERE user_id = 123
```

### Using FINAL with CollapsingMergeTree

FINAL works with CollapsingMergeTree:

```sql {% title="FINAL with CollapsingMergeTree" %}
SELECT * FROM events_collapsing FINAL WHERE user_id = 123
```

### Using FINAL with VersionedCollapsingMergeTree

FINAL works with VersionedCollapsingMergeTree:

```sql {% title="FINAL with VersionedCollapsingMergeTree" %}
SELECT * FROM events_versioned FINAL WHERE user_id = 123
```

### Using FINAL with GraphiteMergeTree

FINAL works with GraphiteMergeTree:

```sql {% title="FINAL with GraphiteMergeTree" %}
SELECT * FROM metrics_graphite FINAL WHERE timestamp >= now() - INTERVAL 1 HOUR
```

## Advanced solutions

### Using FINAL in complex queries

FINAL can be used in complex queries with supported engines:

```sql {% title="Complex query with FINAL" %}
SELECT
    user_id,
    event_type,
    timestamp
FROM events_replacing FINAL
WHERE timestamp >= now() - INTERVAL 1 DAY
ORDER BY timestamp DESC
```

### Using FINAL with aggregations

FINAL can be used with aggregate functions:

```sql {% title="FINAL with aggregations" %}
SELECT
    user_id,
    COUNT(*) as event_count
FROM events_replacing FINAL
GROUP BY user_id
```

### Using FINAL with window functions

FINAL can be used with window functions:

```sql {% title="FINAL with window functions" %}
SELECT
    user_id,
    event_type,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) as row_num
FROM events_replacing FINAL
```

## Table engines that support FINAL

### ReplacingMergeTree

For handling updates with version numbers:

```sql {% title="ReplacingMergeTree with FINAL" %}
CREATE TABLE events_replacing (
    user_id UInt32,
    event_type String,
    timestamp DateTime,
    version UInt32
) ENGINE = ReplacingMergeTree(version)
ORDER BY (user_id, timestamp)

SELECT * FROM events_replacing FINAL
```

### CollapsingMergeTree

For handling inserts and deletes:

```sql {% title="CollapsingMergeTree with FINAL" %}
CREATE TABLE events_collapsing (
    user_id UInt32,
    event_type String,
    timestamp DateTime,
    sign Int8
) ENGINE = CollapsingMergeTree(sign)
ORDER BY (user_id, timestamp)

SELECT * FROM events_collapsing FINAL
```

### VersionedCollapsingMergeTree

For handling versioned collapsing:

```sql {% title="VersionedCollapsingMergeTree with FINAL" %}
CREATE TABLE events_versioned (
    user_id UInt32,
    event_type String,
    timestamp DateTime,
    version UInt32,
    sign Int8
) ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (user_id, timestamp)

SELECT * FROM events_versioned FINAL
```

## Table engines that don't support FINAL

### MergeTree

Standard MergeTree doesn't support FINAL:

```sql {% title="MergeTree without FINAL" %}
SELECT * FROM events WHERE user_id = 123
```

### SummingMergeTree

SummingMergeTree doesn't support FINAL:

```sql {% title="SummingMergeTree without FINAL" %}
SELECT * FROM events_summing WHERE user_id = 123
```

### AggregatingMergeTree

AggregatingMergeTree doesn't support FINAL:

```sql {% title="AggregatingMergeTree without FINAL" %}
SELECT * FROM events_aggregating WHERE user_id = 123
```

## Tinybird-specific notes

In Tinybird, this error often occurs when:

* Using FINAL with Data Sources that don't support it
* Using FINAL in Pipes with unsupported table engines
* Using FINAL in Materialized Views incorrectly
* Using FINAL in API endpoints with wrong contexts

To debug in Tinybird:

1. Check your Data Source table engine for FINAL support
2. Verify Pipe operations don't use FINAL in unsupported contexts
3. Test FINAL usage in the Query Builder first
4. Use alternative approaches when FINAL isn't supported

{% callout type="tip" %}
Include guidance on using WITH FINAL for certain queries in Tinybird Pipes.
{% /callout %}

## Best practices

### FINAL usage

* Only use FINAL with supported table engines
* Don't use FINAL in subqueries or CTEs
* Use FINAL when you need the latest version of rows
* Consider performance implications of using FINAL

### Performance considerations

* FINAL can be expensive on large tables
* Consider using appropriate indexes
* Monitor query performance with FINAL
* Use FINAL only when necessary

### Error prevention

* Check table engine before using FINAL
* Test FINAL usage with sample data
* Use alternative approaches when FINAL isn't supported
* Document FINAL usage requirements

## See also

* [ClickHouse Table Engines](/sql-reference/engines)
* [Working with Data Sources](/forward/get-data-in/data-sources)
* [Common function errors](/sql-reference/clickhouse-errors/UNSUPPORTED_METHOD)