In big data scenarios, data tables often require frequent addition or modification of columns as business evolves. Traditional tables require rewriting the entire table, and the larger the data volume, the higher the cost. Lance's columnar + fragment storage solves this pain point—when adding or modifying a column, only a single column data file needs to be written, and the original data files remain untouched, significantly reducing the cost of changes.
This article focuses on the two high-frequency scenarios of adding columns (ADD COLUMNS FROM) and modifying columns (UPDATE COLUMNS FROM), providing reusable SQL paradigms and practical best practices for machine learning feature backfill scenarios that require frequent column iteration.

Why Lance can achieve low cost #

Lance is a modern columnar data format designed for AI and machine learning scenarios. Like mainstream table formats, Lance supports traditional schema evolution (adding, deleting, and modifying columns), and most operations do not require rewriting data files, making it highly efficient.
On top of this, Lance further supports data evolution, enabling not only schema changes but also backfilling new column data for existing rows without rewriting original data files. The underlying reason for this capability lies in Lance's columnar + fragment storage model:

• Column files are independent of each other. When adding or modifying a column, only a new column data file needs to be written for that column, and the original column data files remain unchanged.
• Row address is used instead of primary key for locating. Lance precisely locates the physical position of each row in the target dataset using _rowaddr (row address) and _fragid (fragment ID), allowing new column data to be "aligned" and backfilled to the correct row.
• Low write amplification. Compared to operations like MERGE INTO and UPDATE SET that rewrite entire rows or batches, Lance column-level updates affect only one column, significantly reducing the amount of data written.

About Lance-Spark connector #

Lance-spark connector is the official Spark SQL DML plugin provided by Lance, enabling Spark to directly read and write Lance-format datasets. Its core DML statements are:

• ALTER TABLE ADD COLUMNS FROM: Backfill new columns for existing data
• ALTER TABLE UPDATE COLUMNS FROM : Batch update existing columns

In conjunction with the lance-spark connector, Spark SQL can be used directly to perform large-scale column additions and modifications on Lance datasets.

The Lance Spark SQL Extension must be enabled before you can use the lance-spark-connector. When submitting a Spark job (including submission methods such as spark-submit, spark-sql, or SparkSession), configure the corresponding SQL extension. For detailed configuration steps, see Spark SQL Extensions.

Core explained in one sentence: Associate the "target table" and the "source table" using the business primary key, construct a temporary view containing _rowaddr and _fragid, and then use ALTER TABLE ... ADD/UPDATE COLUMNS ... FROM to persist the data. Lance only writes new column files, does not rewrite original data, preserves row addresses, and has extremely low write amplification.

Three key identifiers you must understand #

Understanding the following three identifiers is a prerequisite for correct usage before starting practical operation.

Add columns: ADD COLUMNS FROM #

use cases #

• Feature backfill in machine learning feature engineering: new feature columns are frequently added during model iteration, and rewriting historical data is undesirable.
• Add a feature column or label column to an existing large table, with data coming from another table or a computation.
• Any requirement for "adding a column, immediately queryable, without modifying original data".

Basic syntax #

ALTER TABLE 
<table> ADD COLUMNS <col1>, <col2>, ... FROM <source_view>;  

Usage example #

For example, to add a new column tag_0 to table a, where the data for this column is saved in table b, and tables a and b are associated via the business primary key biz_key:

-- Step 1: Construct the temporary view (temporary view)
-- Retrieve the row address (_rowaddr, _fragid) from target table a, and the new column value from data source b
-- Caution: _rowaddr and _fragid must come from target table a; you cannot use those from b
CREATE TEMPORARY VIEW tmp_view 
AS 
SELECT 
 a._rowaddr, -- Row address, used to precisely locate the physical row in the target table
 a._fragid, -- Fragment ID, used together with _rowaddr for addressing
 b.tag_0 AS tag_0 -- New column value, from data source b
FROM a 
INNER JOIN b ON a.biz_key = b.biz_key; -- Join using the business primary key (biz_key); the column will only be added to matching rows
 
-- Step 2: Perform the add column operation
-- Write tag_0 from tmp_view into table a without rewriting the original data files
ALTER TABLE a ADD COLUMNS tag_0 FROM tmp_view;

After execution, tag_0 is immediately written as a new column, with no table rewrite or data migration, and is instantly queryable.

important notes #

• The temporary view must include _rowaddr and _fragid. This is because these two columns are used to locate the rows in the target dataset where the new column data will be written.
• The new column name must not conflict with existing columns. ADD COLUMNS is for adding new columns; the target column should not already exist in the table.
• The coverage of the JOIN determines the scope of the backfill. When using INNER JOIN, only rows in b that match the biz_key will receive a tag_0 value; for unmatched rows, the value of the new column depends on the implementation semantics (typically NULL or not backfilled). To ensure all rows have values, verify whether the data source b fully covers the primary keys of a.

Update columns: UPDATE COLUMNS FROM #

use cases #

• When both the target table and the source table are large, and a large-scale batch update is required for a specific column.
• After recalculating a certain feature/label, it is necessary to overwrite the old value with the new value, but without triggering a full row rewrite.
• It is necessary to retain _rowaddr and pursue column-level batch updates with low write amplification.

Basic syntax #

ALTER TABLE <table> UPDATE COLUMNS <col1>, <col2>, ... FROM <source_view>;  

Usage example #

For example, suppose you want to modify column tag_0 in table a, with the new value saved in table b. The two tables are linked via the business primary key biz_key:

-- Step 1: Construct the temporary view (temporary view)
-- Retrieve the row address from target table a and the updated column value from data source b
-- Caution: _rowaddr and _fragid must come from target table a
CREATE TEMPORARY VIEW tmp_view 
AS 
SELECT 
 a._rowaddr, -- Row address, precisely locates the physical row to be updated in target table a
 a._fragid, -- Fragment ID, used together with _rowaddr for addressing
 b.tag_0 AS tag_0 -- New column value, sourced from data source b
FROM a 
INNER JOIN b ON a.biz_key = b.biz_key; -- Only rows matching biz_key will be updated
 
-- Step 2: Perform column modification operation
-- Use tag_0 from tmp_view to overwrite the tag_0 column of the corresponding row in table a; the original data file is not rewritten
ALTER TABLE a UPDATE COLUMNS tag_0 FROM tmp_view;

UPDATE COLUMNS also supports updating multiple columns in a single statement, as long as the temporary view contains the corresponding columns. For example:

-- Step 1: Construct a temporary view
-- Directly obtain row address from target table users, and calculate new column values using constants or expressions
-- Only process the row where id = 2 (WHERE filter)
CREATE TEMPORARY VIEW update_source AS 
SELECT 
 _rowaddr, -- Row address, sourced from target table users itself
 _fragid, -- Fragment ID
 200 AS value, -- New value for value column (constant overwrite)
 'updated' AS name -- New value for name column (constant overwrite)
FROM users 
WHERE id = 2; -- Only update the row with id=2; other rows are not affected
 
-- Step 2: Update two columns at once
-- value and name are overwritten and written simultaneously, without rewriting the entire table
ALTER TABLE users UPDATE COLUMNS value, name FROM update_source;

important notes #

• The data source must contain _rowaddr and _fragid. Used to locate the rows to be updated in the target dataset.
• The columns specified in UPDATE COLUMNS must already exist in the target table. This is the fundamental difference from ADD COLUMNS: one modifies existing columns, the other adds new columns.
• Rows that do not match are ignored. Only rows that can be matched via _rowaddr are updated; records in the data source that cannot be matched to target rows will not affect anything.

Key operation points #

View construction specifications #

• The temporary view must include _rowaddr and _fragid. This is a strict prerequisite for these two types of operations to be successfully written to the database; omission will result in the inability to address rows.
• Obtain row addresses from the target table a. When performing a JOIN, _rowaddr and _fragid must come from the target table a (the table where columns are being added or modified), not from the data source b.

Data quality requirements #

• Clarify the JOIN semantics and primary key coverage. When using INNER JOIN, only matching rows are processed; first confirm whether b covers the primary keys of a as expected to avoid unexpected NULLs or missed updates.
• Ensure that biz_key is unique on the data source side. If biz_key is duplicated in b, the JOIN will cause row expansion, resulting in a single target row being mapped to multiple values, making the result uncontrollable. It is recommended to deduplicate or validate the uniqueness of the primary key in b before backfilling.

Command selection optimization #

• Distinguish between adding columns and modifying columns. If the column does not exist, use ADD COLUMNS (add new); if the column already exists, use UPDATE COLUMNS (overwrite).
• For large table updates, prefer UPDATE COLUMNS over MERGE INTO. When both the target table and data source are large and only a few columns are involved, UPDATE COLUMNS FROM offers the most significant advantage in terms of low write amplification.
• Make good use of same-table expressions for backfilling. New column data does not necessarily have to come from an external table; it can also be calculated from the same table (such as hash(name)), following the same rule of including _rowaddr/_fragid. For example, create a temporary view as follows to compute the hash value of the name field and add it to the source table.

  -- Step 1: Construct a temporary view
  -- Directly obtain row address from target table users, and calculate new column values using expressions from the same table
  -- No need to join external tables; _rowaddr and _fragid are sourced from target table users itself
  CREATE TEMPORARY VIEW tmp_view AS 
  SELECT 
   _rowaddr, -- Row address, precisely locates the physical position of each row in the users table
   _fragid, -- Fragment ID, used together with _rowaddr for addressing
   hash(name) AS hash_name -- New column value: calculate the hash value of the name column from the same table
  FROM users; 
   
  -- Step 2: Perform column addition operation
  -- Write hash_name from tmp_view into the users table; the original data file is not rewritten
  ALTER TABLE users ADD COLUMNS hash_name FROM tmp_view;