Skip to main content
GuideServerConfigure and deploy Tabsdata servers on your machine.TutorialsConfigure data integration workflows within a running Tabsdata server.API ReferenceCLI ReferenceRelease Notes
Version: 2.3.0

Schema Evolution

Every table destination takes a schema_evolution setting that decides what happens when a write brings a column the destination table does not have, or a value type wider than the column's. The table destinations are Databricks, Snowflake, BigQuery, ClickHouse, StarRocks, Postgres, MySQL, MariaDB, Microsoft SQL Server, Oracle and Db2. The same two values apply to append, replace and merge on all of them, but what each one does differs by destination. Druid cannot enforce a schema, so "update" is its only value and its default.

Policies​

schema_evolution takes one of two values:

ValueWhat it does
"strict"Refuses a new column, and refuses a wider type unless its values fit the column, with error SQL_COMMON_10, before any data is loaded.
"update"Adds new columns, widens types and makes columns nullable. It never drops or reorders columns.

Under either value, a column the batch lacks is written as NULL on append and keeps its stored value on merge.

Renamed in 2.3.0

ClickHouse and StarRocks called update "iceberg" before 2.3.0. A function that still sets schema_evolution="iceberg" fails validation after the upgrade; change it to "update".

Behavior per destination​

What each destination does with a new or wider column, and how it carries out replace:

Destinationstrict appendstrict replacestrict mergeupdatereplace mechanismreplace atomic
DatabricksFails in COPY INTO (DATABRICKS_9)Schema follows the batchRefused before the MERGE (SQL_COMMON_10)Append: mergeSchema and delta.enableTypeWidening. Merge: MERGE WITH SCHEMA EVOLUTION and wideningCREATE OR REPLACE … read_files() LIMIT 0, then COPY INTONo
SnowflakeRefused before COPYRefused: the schema check runs before the TRUNCATERefusedAppend and replace: native ENABLE_SCHEMA_EVOLUTION, adds columns and makes columns nullable. Merge: Iceberg engine, set in dest_cfg; ALTER adds columns and widens VARCHAR length and NUMBER precisionTRUNCATE, then COPY INTO; the table definition is keptNo
BigQueryFails in the load job (BIGQUERY_7)Schema replacedRefusedAppend and replace: native, adds columns and relaxes REQUIRED, never widens. Merge: Iceberg engine, set in dest_cfg; ALTER adds and widens, and REQUIRED stays REQUIREDWRITE_TRUNCATE load jobYes
ClickHouseRefused before the load on a new column, a wider type whose values do not fit, or a NULL into a non-Nullable column. Column order is ignored, a narrower type fits, a missing column is NULLSchema follows the batchNot supportedAdds Nullable columns, makes columns nullable, widens Int8 up to Int64, UInt likewise, and Float32 to Float64Stage, DROP TABLE IF EXISTS, CREATE … MergeTree() ORDER BY tuple(), INSERT by nameNo
StarRocksRefused before the load on a new column, or a wider type whose values do not fit. A missing column gets its default or NULLSame check, then TRUNCATE and INSERT; the table must existNot supportedADD COLUMN … NULL; MODIFY COLUMN widens integers, FLOAT to DOUBLE, VARCHAR and DECIMAL, waiting up to 30 minutes for the ALTER jobTRUNCATE, then INSERT … SELECT FROM FILES()No
DruidRefused at registrationRefused at registrationNot supportedThe default and only value: schema discovery adds columns, and a missing column reads as NULLindex_parallel with dropExisting over 1970 to 2100Per datasource
Postgres, MySQL, MariaDB, SQL Server, Oracle, Db2Refused on a new column, or a wider type whose values do not fit (SQL_COMMON_10)Schema follows the batchNot supportedALTER TABLE … ADD; widens integers, decimals and floats by digits or width. Db2 then runs REORG. No drops, and no retyping of non-number columnsDROP TABLE IF EXISTS, then the table is created on writePostgres, SQL Server and Db2: inside transactional. MySQL, MariaDB and Oracle: no, and their update ALTERs commit implicitly

A write to several tables runs one table after another and is not atomic across tables, except on the relational destinations with transactional=True. The Databricks and BigQuery merge staging tables are real tables named <target>_<8 hex characters>, created next to the target; only Snowflake's is TEMPORARY.

Type widening under update​

Each destination widens only these types:

DestinationWidens
SnowflakeVARCHAR length and NUMBER precision, on merge
BigQueryINT64 to BIGNUMERIC to FLOAT64, on merge
ClickHouseIntegers up to 64 bits, signed and unsigned, and Float32 to Float64
StarRocksIntegers, FLOAT to DOUBLE, VARCHAR and DECIMAL
Postgres, MySQL, MariaDB, SQL Server, Oracle, Db2Integers, decimals and floats, by digits or width

A change outside these rules fails the write.

Native and Iceberg schema evolution​

Under "update", a destination changes the table in one of two ways. Native schema evolution is the destination's own: the load or merge statement adds the columns. Iceberg schema evolution is applied by the connector: it compares the incoming and destination schemas following Apache Iceberg's evolution rules, and runs the ALTER TABLE statements before the write. It does not convert the destination into an Apache Iceberg table.

Destinationappend and replacemerge
DatabricksNativeNative
Snowflake, BigQueryNativeIceberg, set in dest_cfg
ClickHouse, StarRocks, Postgres, MySQL, MariaDB, SQL Server, Oracle, Db2IcebergNot supported
DruidNativeNot supported

Snowflake's and BigQuery's native schema evolution adds columns and makes columns nullable, but never widens a type, and does not apply to MERGE. A Snowflake or BigQuery merge under "update" needs the Iceberg engine set in dest_cfg:

dest_cfg={"snowflake.schema_evolution_engine": "iceberg"}
dest_cfg={"bigquery.schema_evolution_engine": "iceberg"}

Without it, the merge is refused at registration. The engine setting is accepted only on a Snowflake or BigQuery merge under "update"; on any other write mode or destination, and under "strict", registration refuses <database>.schema_evolution_engine.

Permissions​

update needs permission to ALTER the destination table. Db2 also runs REORG through ADMIN_CMD after an ALTER, and BigQuery creates and deletes a temporary external table. On the relational destinations, replace drops and recreates the table, so it needs DROP and CREATE.

See Merge into SQL destinations for merge, and each destination's page for its other settings.