ClickHouse DB::Exception: Number of columns doesn't match

The "DB::Exception: Number of columns doesn't match" error means that the column count in your INSERT statement, subquery, or data source does not agree with the column count expected by the target table or expression. ClickHouse performs a strict check — every column in the target must receive a value, unless you explicitly list a subset of columns in the INSERT clause.

Impact

This error blocks data ingestion when it occurs during INSERTs, and it can halt ETL pipelines, bulk loads, and materialized view population. Because it fails the entire statement, no rows from the batch are written.

Common Causes

INSERT without a column list when the source has fewer or more columns than the table — using INSERT INTO table SELECT ... where the SELECT returns a different number of columns.
Table schema changed after the query was written — a column was added to or removed from the table, but the INSERT statement was not updated.
CSV/TSV file with the wrong number of fields — the input file has more or fewer delimited fields than the table expects.
Materialized view mismatch — the MV's SELECT produces a different number of columns than its target table.
UNION ALL with branches of different widths — although this usually gives a different error, it can surface as a column count mismatch in some contexts.

Troubleshooting and Resolution Steps

Check the table schema:
```
DESCRIBE TABLE your_database.your_table;
```
Count the columns and compare with your INSERT source.
Use an explicit column list in INSERT. This is the safest approach and decouples your INSERT from future schema changes:
```
INSERT INTO your_table (col1, col2, col3)
SELECT a, b, c FROM source_table;
```

Verify your SELECT column count. A quick way to check:

SELECT count() FROM system.columns
WHERE database = 'your_db' AND table = 'your_table';

For file-based inserts, validate the file structure. Make sure the number of delimited fields per row matches the table. You can inspect the first few lines:
```
SELECT * FROM file('data.csv', CSV) LIMIT 5;
```
Review materialized view definitions. If the error occurs during an INSERT that triggers an MV, check that the MV SELECT produces exactly the columns the target table expects:
```
SHOW CREATE TABLE your_mv;
DESCRIBE TABLE your_mv_target;
```
Handle DEFAULT and MATERIALIZED columns. Columns with DEFAULT or MATERIALIZED expressions do not need to be supplied in the INSERT, but they still count toward the table's total column count if you omit the column list.

Best Practices

Always specify an explicit column list in INSERT statements. This makes your code resilient to schema changes and makes the mapping obvious.
When loading from external files, use input_format_with_names_use_header = 1 so ClickHouse matches columns by name rather than position.
After any ALTER TABLE that adds or drops columns, review all INSERT statements and ETL jobs that target the table.
Use DESCRIBE TABLE as a pre-flight check in automated pipelines before bulk loads.

Frequently Asked Questions

Q: Does ClickHouse allow partial column inserts?
A: Yes, if you specify a column list. Columns not listed will receive their DEFAULT, MATERIALIZED, or zero/empty value. But if you omit the column list entirely, ClickHouse expects values for every column in declaration order.

Q: I added a column with a DEFAULT value but my INSERTs still fail. Why?
A: If your INSERT does not include a column list, ClickHouse expects a value for every column, including the new one. Either add the column to your INSERT or use an explicit column list that omits it.

Q: How do I handle extra columns in a CSV file?
A: Use input_format_csv_skip_unknown_fields = 1 (available in recent ClickHouse versions) or preprocess the file to remove unwanted columns before loading.

Q: Can this error appear during a SELECT without an INSERT?
A: It is rare during standalone SELECTs. It most commonly occurs in INSERT ... SELECT, file imports, or materialized view pipelines where ClickHouse needs to map one set of columns to another.