Opensource internals

How ClickHouse Maps an INSERT onto the Table's Schema

17 min read Jay Hubbert

ClickHouse resolves an INSERT's columns against the table's schema by name into a sample block, then builds a pipeline that squashes, dedup-stamps and fans the data out to the sinks.

TL;DR: An INSERT is resolved into a sample block — a Block with the right columns and types — by looking each requested column up by name in the table’s insertable columns. That sample block is both the parser’s schema and the pipeline’s header. The pipeline then counts rows, stamps deduplication info, squashes blocks to the target size, and fans the single input stream out to one sink chain per destination.

An INSERT statement is the one query where the shape of the data is not known until the last moment. The client says INSERT INTO t VALUES (1, 'a'); the server has to work out what 1 and 'a' mean.

ClickHouse solves this with a single object: the sample block. It is a Block — a list of (name, type, empty column) — that plays three roles at once:

  1. it is the schema the input format parses against,
  2. it is the header of the insert pipeline,
  3. it is the target the conversion step casts the data into.

Everything else in the insert path is a consequence of that one idea.

The problem in three shapes

There are three ways an insert can name its columns, and they resolve differently.

No column list. INSERT INTO t VALUES (1, 'a'). The client is saying “the values are in the table’s column order”. The target is the table’s insertable columns — ordinary columns plus EPHEMERAL ones, but not MATERIALIZED and not ALIAS.

An explicit column list. INSERT INTO t (b, a) VALUES ('a', 1). The client is naming columns, and the order is theirs, not the table’s. This is the case that needs a name lookup.

A SELECT as the source. INSERT INTO t SELECT .... Here the source produces a fixed shape, and the mapping between the source’s columns and the table’s is positional — because a SELECT has no column names to speak of at the point where the pipeline is built.

The engine is responsible for the third case (the storage knows how to convert), but the first case is a table-schema question: which columns of t are insertable at all?

Which columns are insertable

The answer is in ColumnsDescription:

cpp 
// src/Storages/ColumnsDescription.cpp:615
NamesAndTypesList ColumnsDescription::getInsertable() const
{
    NamesAndTypesList ret;
    for (const auto & col : columns)
        if (col.default_desc.kind == ColumnDefaultKind::Default || col.default_desc.kind == ColumnDefaultKind::Ephemeral)
            ret.emplace_back(col.name, col.type);
    return ret;
}

So: DEFAULT columns and EPHEMERAL columns are insertable; MATERIALIZED columns are computed by the engine and cannot be written; ALIAS columns do not exist physically at all.

The sample block is built from that list:

cpp 
// src/Storages/StorageInMemoryMetadata.cpp:538
Block StorageInMemoryMetadata::getSampleBlockInsertable() const
{
    Block res;

    for (const auto & column : getColumns().getInsertable())
        res.insert({column.type->createColumn(), column.type, column.name});

    return res;
}

Note column.type->createColumn() — the sample block holds empty columns of the right type. It is a schema, not data. (Compare getSampleBlockNonMaterialized(), which is the same thing for the “ordinary” columns — used by the streaming storages that need the physical shape.)

The sample block: by name

When the insert names its columns, the lookup happens by name:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:239
Block InterpreterInsertQuery::getSampleBlock(
    const Names & names,
    const StoragePtr & table,
    const StorageMetadataPtr & metadata_snapshot,
    bool allow_virtuals,
    bool allow_materialized)
{
    std::vector<size_t> missing_positions;
    Block table_sample_insertable = metadata_snapshot->getSampleBlockInsertable();

    ColumnsWithTypeAndName res{names.size()};
    std::unordered_set<String> inserted_names;

    for (size_t i = 0; i < names.size(); i++)
    {
        const auto & current_name = names[i];
        if (!inserted_names.insert(current_name).second)
            throw Exception(
                ErrorCodes::DUPLICATE_COLUMN,
                "Column {} in table {} specified more than once",
                current_name,
                table->getStorageID().getNameForLogs());

        const ColumnWithTypeAndName * insertable_col = table_sample_insertable.findByName(current_name);
        if (!insertable_col)
            missing_positions.emplace_back(i);
        else
            res[i] = *insertable_col;
    }

Three things happen here that are worth naming.

The result is in the client’s order, not the table’s. res[i] is the column the client asked for at position i. The pipeline header is therefore INSERT INTO t (b, a) order, and the sink is the one that has to reorder. This matters for the INSERT ... SELECT case, where the conversion is positional instead.

Duplicate names are rejected. inserted_names is a set, and a second occurrence of a name throws DUPLICATE_COLUMN. Without that check, INSERT INTO t (a, a) would silently take the second a’s type.

A name that is not insertable is not immediately an error. It goes into missing_positions and is resolved in a second pass:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:269
    if (!missing_positions.empty())
    {
        Block table_sample_physical = metadata_snapshot->getSampleBlock();
        Block table_sample_virtuals;
        if (allow_virtuals)
            table_sample_virtuals = metadata_snapshot->virtuals.getSampleBlock(VirtualsKind::All, VirtualsMaterializationPlace::All);

        /// Columns are not ordinary or ephemeral
        for (auto pos : missing_positions)
        {
            const auto & current_name = names[pos];

            if (table_sample_physical.has(current_name))
            {
                /// Column is materialized
                if (!allow_materialized)
                    throw Exception(ErrorCodes::ILLEGAL_COLUMN, "Cannot insert column {}, because it is MATERIALIZED column", current_name);
                res[pos] = table_sample_physical.getByName(current_name);
            }
            else if (table_sample_virtuals.has(current_name))
            {
                res[pos] = table_sample_virtuals.getByName(current_name);
            }
            else
            {
                /// The table does not have a column with that name
                throw Exception(
                    ErrorCodes::NO_SUCH_COLUMN_IN_TABLE,
                    "No such column {} in table {}",
                    current_name,
                    table->getStorageID().getNameForLogs());
            }
        }
    }

So the resolution order is: insertable columns first, then materialized columns (if the caller allows it), then virtual columns, then an error. The allow_materialized flag is the difference between INSERT INTO t (mat_col) VALUES (...) working and failing — and it is controlled by the insert_allow_materialized_columns setting.

The two-pass structure is not an optimisation; it is a precedence rule. If a table has a column that is both insertable and (say) virtual, the insertable one wins. And the error messages are deliberately distinct: “No such column X in table Y” versus “Cannot insert column X, because it is MATERIALIZED column” — a user who typos a column name gets a different message than a user who tries to write a computed column.

From the AST to the names

Before the lookup, the column list from the query has to be normalised. The client may write qualified names, and the table may have column transformers (MATERIALIZED expr computed from other columns, ALIAS):

cpp 
// src/Interpreters/processColumnTransformers.cpp:26
    TablesWithColumns tables_with_columns;
    {
        auto table_expr = make_intrusive<ASTTableExpression>();
        table_expr->database_and_table_name = make_intrusive<ASTTableIdentifier>(storage_id);
        table_expr->children.push_back(table_expr->database_and_table_name);
        tables_with_columns.emplace_back(DatabaseAndTableWithAlias(*table_expr, current_database), names_and_types);
    }

    tables_with_columns[0].addHiddenColumns(columns.getMaterialized());
    tables_with_columns[0].addHiddenColumns(columns.getAliases());
    tables_with_columns[0].addHiddenColumns(virtual_columns);

The materialized and alias columns are added as hidden columns, so a qualified name like t.alias_col resolves to the right thing even though the column is not insertable.

The INSERT ... SELECT case: positional, not by name

When the source is a SELECT, the mapping is by position, because the SELECT’s output has no user-supplied names at that point:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:428
    auto actions_dag = ActionsDAG::makeConvertingActions(
            pipeline.getHeader().getColumnsWithTypeAndName(),
            query_sample_block.getColumnsWithTypeAndName(),
            ActionsDAG::MatchColumnsMode::Position,
            context_);
    auto actions = std::make_shared<ExpressionActions>(std::move(actions_dag), ExpressionActionsSettings(context_, CompileExpressions::yes));

    pipeline.addSimpleTransform([&](const SharedHeader & in_header) -> ProcessorPtr
    {
        return std::make_shared<ExpressionTransform>(in_header, actions);
    });

    return query_sample_block;

MatchColumnsMode::Position versus MatchColumnsMode::Name — the same function, two policies. The by-name mode is what the async insert queue uses to normalise pre-parsed blocks onto the header it computed:

cpp 
// src/Interpreters/AsynchronousInsertQueue.cpp:1199
    auto converting_dag = ActionsDAG::makeConvertingActions(
        block.getColumnsWithTypeAndName(),
        header.getColumnsWithTypeAndName(),
        ActionsDAG::MatchColumnsMode::Name,
        context);

Before the conversion, there is one subtlety: insert_null_as_default. A Nullable source column can be inserted into a non-nullable target column, with the NULLs replaced by the column’s default. The trick is to widen the target to Nullable for the conversion, and let the defaults step narrow it back:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:398
    /// Allow to insert Nullable into non-Nullable columns, NULL values will be added as defaults values.
    if (context_->getSettingsRef()[Setting::insert_null_as_default])
    {
        const auto & input_columns = pipeline.getHeader().getColumnsWithTypeAndName();
        const auto & query_columns = query_sample_block.getColumnsWithTypeAndName();
        const auto & output_columns = metadata_snapshot->getColumns();

        if (input_columns.size() == query_columns.size())
        {
            for (size_t col_idx = 0; col_idx < query_columns.size(); ++col_idx)
            {
                /// Change query sample block columns to Nullable to allow inserting nullable columns, where NULL values will be substituted with
                /// default column values (in AddingDefaultsTransform), so all values will be cast correctly.
                if (isNullableOrLowCardinalityNullable(input_columns[col_idx].type)
                    && !isNullableOrLowCardinalityNullable(query_columns[col_idx].type)
                    && !isVariant(query_columns[col_idx].type)
                    && !isDynamic(query_columns[col_idx].type)
                    && output_columns.has(query_columns[col_idx].name))
                {
                    query_sample_block.setColumn(
                        col_idx,
                        ColumnWithTypeAndName(
                            makeNullableOrLowCardinalityNullable(query_columns[col_idx].column),
                            makeNullableOrLowCardinalityNullable(query_columns[col_idx].type),
                            query_columns[col_idx].name));
                }
            }
        }
    }

The Variant and Dynamic exclusions are there because those types have their own null representation and cannot be blanket-wrapped.

The pipeline

Now the sample block becomes a pipeline. The interesting part is the shape: a single input stream, a single head, and then a fan-out to one sink chain per destination.

The head is built in buildPushPipelineFromDependencies:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1149
    /// Shrink over-allocated columns produced by parsing (e.g. String columns grown power-of-two) to
    /// fit, right after the source where the chunk is uniquely owned, to reduce peak memory usage.
    if (static_cast<double>(settings[Setting::shrink_over_allocated_columns_min_waste_ratio]) > 1.0)
        add_head_transform(std::make_shared<ShrinkColumnsTransform>(
            insert_header,
            static_cast<double>(settings[Setting::shrink_over_allocated_columns_min_waste_ratio]),
            settings[Setting::shrink_over_allocated_columns_min_waste_bytes]));

    {
        auto counting = std::make_shared<CountingTransform>(insert_header, context_->getQuota(), context_->getNormalizedQueryHash());
        counting->setProcessListElement(context_->getProcessListElement());
        counting->setProgressCallback(context_->getProgressCallback());
        add_head_transform(std::move(counting));
    }

Three transforms, in this order, and the order is a memory optimisation:

  1. Shrink — the parser allocates String columns with power-of-two growth, so a 1 KB string may live in a 2 KB buffer. Shrinking right after the source, where the chunk is uniquely owned, halves the peak memory of a wide insert. The condition > 1.0 means “only shrink if the waste ratio is worth the copy”.
  2. Count — the quota accounting. The counter is attached to the normalized query hash, so WRITTEN_ROWS quotas can be per-query-shape.
  3. Dedup stamp — the deduplication info.
cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1164
    if (!squash_with_strict_limits)
        add_head_transform(std::make_shared<AddDeduplicationInfoTransform>(
            insert_dependencies,
            insert_dependencies->getRootViewID(),
            settings[Setting::insert_deduplication_token].value,
            insert_header));

The AddDeduplicationInfoTransform stamps each chunk with the source block number — the ordinal of the block within this insert. That number is part of the deduplication token, which is what makes a retried insert recognisable as a retry rather than as new data.

Then the squashing:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1171
    if (should_squash)
    {
        bool table_prefers_large_blocks = table->prefersLargeBlocks();
        size_t min_block_size_bytes = table_prefers_large_blocks ? settings[Setting::min_insert_block_size_bytes] : 0ULL;
        /// On low-memory systems, cap squashing block size to avoid accumulating too much data.
        if (auto memory_limit = total_memory_tracker.getHardLimit(); memory_limit > 0)
            min_block_size_bytes = std::min<size_t>(min_block_size_bytes, static_cast<size_t>(static_cast<double>(memory_limit) * 0.9) / 8);
        add_head_transform(std::make_shared<PlanSquashingTransform>(
            insert_header,
            table_prefers_large_blocks ? settings[Setting::min_insert_block_size_rows] : settings[Setting::max_block_size],
            min_block_size_bytes,
            settings[Setting::max_insert_block_size],
            settings[Setting::max_insert_block_size_bytes],
            squash_with_strict_limits));
    }

The squashing transform is a plan, not a simple transform: it accumulates chunks and emits them when they reach the target size. The target comes from the table’s preference — a MergeTree wants big blocks (min_insert_block_size_rows), a Memory table does not.

The 0.9 * memory_limit / 8 cap is a nice touch: the squasher’s buffer is allowed to grow to at most 90 % of the server’s memory limit divided by 8 (roughly, eight concurrent squashing buffers).

The fan-out

After the head, the single input stream is distributed to the sink chains:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1203
    /// Distribute the single input stream across the parallel insert streams.
    std::vector<OutputPort *> stream_outputs;
    if (sink_stream_size > 1)
    {
        auto resize = std::make_shared<ResizeProcessor>(head_output->getSharedHeader(), 1, sink_stream_size);
        connect(*head_output, resize->getInputs().front());
        for (auto & output : resize->getOutputs())
            stream_outputs.push_back(&output);
        processors->emplace_back(std::move(resize));
    }
    else
    {
        stream_outputs.push_back(head_output);
    }

A ResizeProcessor with one input and N outputs is a round-robin distributor. This is how a single INSERT is written by several threads in parallel.

The number of sink streams is not simply max_insert_threads. It is a carefully-computed function of the deduplication mode, the view graph, the quorum mode and the storage topology:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1064
    const bool dedup_single_stream = !async_insert
        && ((per_branch_dedup_ids && source_deduplicates)
            || forwarded_dependent_mv_dedup_hazard
            || forwards_to_separate_context
            || hidden_views_forward_to_separate_context);

and the reason is spelled out in the long comment above it — the source block number is stamped globally in the head, but a fan-out would let two branches produce blocks with the same number, and the deduplication token would then collide, and the second block would be silently dropped as a duplicate. That is a data-loss bug, so the planner fails closed and keeps such inserts single-stream.

The quorum case has its own reason:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1070
    /// A non-parallel quorum insert (`insert_quorum >= 2` or `'auto'`, with `insert_quorum_parallel = 0`)
    /// permits a single in-flight quorum part per table: every `ReplicatedMergeTreeSink` checks in
    /// `onStart` that the quorum of all previous writes is already satisfied (`checkQuorumPrecondition`)
    /// and throws `UNSATISFIED_QUORUM_FOR_PREVIOUS_WRITE` otherwise. With a write fan-out every branch
    /// runs its own sink - including branches that receive no data - so sibling sinks of the same
    /// `INSERT` race against the not-yet-satisfied quorum node of the part committed by the branch that
    /// got the data. Keep such inserts single-stream.

and the final stream count:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1080
    const size_t insert_threads
        = (async_insert || dedup_single_stream || serial_hidden_views || sequential_quorum_insert) ? 1 : max_insert_threads;

Async inserts are always single-stream — the async queue does its own batching, so a second level of parallelism here would just complicate the flush.

The views: InsertDependenciesBuilder

The destination of an insert is rarely one table. There may be materialized views, each with its own target table. InsertDependenciesBuilder walks that graph and produces one sink chain per leaf:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1118
    auto sink_chains = insert_dependencies->createChainWithDependenciesForAllStreams();
    const size_t sink_stream_size = insert_dependencies->getSinkStreamSize();
    chassert(sink_chains.size() == sink_stream_size);
    chassert(sink_stream_size >= 1);

and each chain is terminated with an EmptySink and connected to its share of the fan-out:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1220
    /// Connect each parallel stream to its sink chain and terminate it with an empty sink.
    QueryPlanResourceHolder resources;
    size_t stream_index = 0;
    for (auto & sink_chain : sink_chains)
    {
        connect(*stream_outputs[stream_index], sink_chain.getInputPort());
        ++stream_index;

        auto sink = std::make_shared<EmptySink>(sink_chain.getOutputSharedHeader());
        connect(sink_chain.getOutputPort(), sink->getPort());

The chains are chains, not sinks: each one may contain several processors (the view’s own ExpressionTransforms, its own AddingDefaultsTransform, and so on) before the actual storage sink.

There is one more subtlety: with use_strict_insert_block_limits, the dedup stamp has to happen per branch, after the fan-out, not in the head:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1187
    /// Prepend the per-stream transforms to each sink chain. `addSource` prepends, so the
    /// resulting top-to-bottom order matches the previous single-stream pipeline:
    /// ApplySquashing -> AddDeduplicationInfo (strict) -> sink.
    for (auto & sink_chain : sink_chains)
    {
        if (squash_with_strict_limits)
            sink_chain.addSource(std::make_shared<AddDeduplicationInfoTransform>(
                insert_dependencies,
                insert_dependencies->getRootViewID(),
                settings[Setting::insert_deduplication_token].value,
                sink_chain.getInputSharedHeader()));

        if (should_squash)
            sink_chain.addSource(std::make_shared<ApplySquashingTransform>(sink_chain.getInputSharedHeader()));
    }

Strict limits mean the block boundaries are part of the deduplication semantics, so the stamp must be applied to the squashed block, in the branch that writes it.

execute(): the routing

InterpreterInsertQuery::execute is the entry point, and it does the access checks, takes the lock, and chooses between the two pipeline builders:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1492
    table->updateExternalDynamicMetadataIfExists(context);
    auto metadata_snapshot = table->getInMemoryMetadataPtr(context, false);
    auto query_sample_block = getSampleBlock(query, table, metadata_snapshot, context, no_destination, allow_materialized);

The sample block is computed before the access check, because the access check is column-aware: the user may have GRANT SELECT (a, b) but not SELECT (c), and the insert’s column list says which columns are being written:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1501
    if (!query.table_function && !skip_target_insert_access_check)
        context->checkAccess(AccessType::INSERT, query.table_id, query_sample_block.getNames());

and the storage’s own guard is checked too, because it may be a different kind of check (the source access of a URL database, say):

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1504
    /// Access the storage itself guards the write with (e.g. the source access of a table of a
    /// `URL` database). It is also checked when the sink is created, but that happens in a
    /// background flush for asynchronous inserts, so the check has to be repeated here.
    if (!query.table_function)
        table->checkInsertIsAllowed(context);

The routing itself:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1517
    BlockIO res;
    if (query.select && !query.async_insert_flush)
    {

query.select means “this insert has a SELECT source”. The !query.async_insert_flush guard is the other direction: when the async queue flushes, it builds a fresh InterpreterInsertQuery over the original query — which still has its SELECT — but at that point the data has already been materialised into a block, so the SELECT must be ignored.

If there is no SELECT, the plain insert pipeline is built:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1593
    else
    {
        res.pipeline = buildInsertPipeline(query, table);
    }

and the source is attached at the end:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1094
    if (query.hasInlinedData() && !async_insert)
    {
        auto format = getInputFormatFromASTInsertQuery(query_ptr, true, *query_sample_block, context, nullptr);

        if (settings[Setting::enable_parsing_to_custom_serialization])
            format->setSerializationHints(table->getSerializationHints());

        auto pipe = getSourceFromInputFormat(query_ptr, std::move(format), context, nullptr);
        pipeline.complete(std::move(pipe));
    }

Note that the format is asked for the sample block — so the parser’s column list and types come from the resolved schema, not from the client’s text. That is the whole point of the sample block: the client sends VALUES or CSV rows, and the server knows what they mean.

The lock

One more thing execute() does that is easy to miss: it takes a share lock on the table and holds it for the lifetime of the pipeline, not just for the build:

cpp 
// src/Interpreters/InterpreterInsertQuery.cpp:1600
    /// Keep the share lock until the pipeline finishes (not just while it is being built), so that
    /// the dependent-view discovery and the commit of the inserted data are indivisible with respect
    /// to an exclusive lock on the table. The atomic `CREATE MATERIALIZED VIEW ... POPULATE` relies
    /// on this: under its brief exclusive lock on the source, any concurrent `INSERT` has either
    /// already committed (and is covered by the pinned snapshot) or has not yet discovered the
    /// dependent views (and will see the newly registered view).
    /// Empty when the async insert queue transform took the lock over: it commits through a separate
    /// flush that takes its own lock, and the route is refused when any dependent view exists.
    if (table_lock)
    {
        QueryPlanResourceHolder insert_resources;
        insert_resources.table_locks.emplace_back(std::move(table_lock));
        res.pipeline.addResources(std::move(insert_resources));
    }

The invariant is: discovering the dependent views and committing the data must be atomic with respect to a CREATE MATERIALIZED VIEW. Otherwise a concurrent INSERT could commit before the view is registered, and the rows would never reach the view. The lock is stored in the pipeline’s resource holder so that it is released exactly when the pipeline is done — the resource holder is destroyed with the pipeline, not with the interpreter.

The whole picture

text 
   INSERT INTO t (b, a) VALUES (...)
              |
              v
   getSampleBlock(names)
     insertable columns  -> by name  -> [b, a]     (client's order!)
     else materialized / virtual / error
              |
              v
   sample block  =  Block([b:String, a:UInt64])
              |
              v
   +---------- pipeline --------------------------------+
   |  ShrinkColumns -> Counting -> AddDeduplicationInfo |
   |        -> PlanSquashing                            |
   |                  |                                  |
   |             ResizeProcessor (1 -> N)                |
   |              /        \                             |
   |      chain 0      chain 1        (one per view leaf)|
   |   [ApplySquashing] [ApplySquashing]                |
   |   [AddDedupInfo ]                                  |
   |        |                |                          |
   |   MergeTreeSink   MV target sink                   |
   +---------------------------------------------------+
              |
              v
        one part per squash

Trade-offs

What the sample block buys.

  • One place to resolve the schema. The parser, the conversion step, the pipeline header and the access check all read the same resolved block. A column that is UInt64 in the table is UInt64 everywhere.
  • By-name resolution is a hash lookup, not a scan, and it produces a specific error for each failure mode.
  • The client never has to know the table’s column order — but it can still use it, by omitting the column list.

What it costs.

  • A conversion step is always present for INSERT ... SELECT, even when the types already match. makeConvertingActions in Position mode will produce an empty DAG if everything lines up, and the ExpressionTransform becomes a no-op copy — but it is still a processor in the pipeline, with a chunk copy.
  • The sample block is a copy of the schema on every insert. For a table with 1000 columns, that is 1000 empty columns allocated per insert. This is why the metadata snapshot is taken once and reused.
  • The by-name lookup is only for the explicit column list. The positional case (INSERT INTO t VALUES) skips it entirely and just takes the insertable columns in order.

The bottom line

The insert path is a schema resolution followed by a fixed pipeline shape. The schema resolution produces a sample block; the pipeline shape is “count, stamp, squash, fan out, write”.

The subtle parts are all about not losing rows:

  • the fan-out is disabled whenever the deduplication numbering would collide between branches;
  • the fan-out is disabled for a non-parallel quorum insert, because sibling sinks would race against the quorum node;
  • the fan-out is disabled when a dependent view is hidden behind an Alias and parallel_view_processing = 0;
  • the share lock is held until the pipeline is done, so that a concurrent CREATE MATERIALIZED VIEW cannot slip between the discovery and the commit.

Every one of those is a case where the obvious implementation would silently drop data.

The thing people get wrong

INSERT INTO t (a, b) is not the same as INSERT INTO t (b, a) when the table’s column order differs, and it is not the same as INSERT INTO t VALUES at all:

sql 
CREATE TABLE t (a UInt64, b String) ENGINE = MergeTree ORDER BY a;
INSERT INTO t VALUES (1, 'x');       -- a = 1, b = 'x'   (table order)
INSERT INTO t (b, a) VALUES ('x', 1);-- b = 'x', a = 1   (named, client's order)
INSERT INTO t (b) VALUES ('x');       -- a gets its DEFAULT

and the third case is the one people get wrong: the omitted column is filled by AddingDefaultsTransform, not by leaving a gap. The sample block only contains the columns the client named, so the pipeline’s header is [b:String], and the defaults step is what widens it to the table’s full insertable shape before the sink sees it.

ClickhouseInternalsInsertSchemaPipeline