Generated columns for Delta tables

Generated columns are special Delta table columns whose values are computed automatically from an expression derived by other columns in the same row. Instead of populating these values in every write, you define the rule once, and Delta computes and stores the value each time you insert or update a row.

Generated columns help you keep derived values consistent and simplify ingestion code.

How generated columns work

When you define a generated column, Delta stores the generation expression in the table metadata. On each write, Delta evaluates the expression and persists the result as a normal column value. Readers then treat the generated column like any other column.

Because the value is materialized on write, queries that filter on a generated column can benefit from statistics and file skipping without recomputing the expression at read time. If generated columns are potentially used in query predicates, ensure that they are file skipping eligible. See file skipping.

Common uses for generated columns include:

• Deriving a partition-friendly DATE column from a TIMESTAMP column.
• Computing a normalized or bucketed value from a raw input.
• Materializing a simple calculation that many downstream queries reuse.

Define a generated column

Use the GENERATED [ALWAYS | BY DEFAULT] AS (expression) clause to define a generated column. The expression can reference other columns in the same table, but it can't reference other tables or use nondeterministic functions.

Delta supports two generation behaviors, which apply to both expression-based generated columns and identity columns:

• GENERATED ALWAYS — Delta always computes the value. You can't supply your own value for the column, which guarantees the stored value matches the definition.
• GENERATED BY DEFAULT — Delta computes the value when you don't supply one, but you can also insert an explicit value. This behavior applies to identity columns.

The examples that follow use GENERATED ALWAYS, which is the common choice for derived columns because it keeps the value consistent with its expression.

Spark SQL

CREATE TABLE sales.events (
  event_id   BIGINT,
  event_time TIMESTAMP,
  event_date DATE GENERATED ALWAYS AS (CAST(event_time AS DATE))
)

PySpark

from delta.tables import DeltaTable
from pyspark.sql.types import LongType, TimestampType, DateType

DeltaTable.create() \
    .tableName("sales.events") \
    .addColumn("event_id", dataType=LongType()) \
    .addColumn("event_time", dataType=TimestampType()) \
    .addColumn("event_date", dataType=DateType(), generatedAlwaysAs="CAST(event_time AS DATE)") \
    .execute()

Scala

import io.delta.tables.DeltaTable
import org.apache.spark.sql.types._

DeltaTable.create()
  .tableName("sales.events")
  .addColumn("event_id", LongType)
  .addColumn("event_time", TimestampType)
  .addColumn(
    DeltaTable.columnBuilder(spark, "event_date")
      .dataType(DateType)
      .generatedAlwaysAs("CAST(event_time AS DATE)")
      .build())
  .execute()

When you write data, omit the generated column or provide a value that matches the expression. If you provide a value that conflicts with the generation expression, Delta rejects the write.

Identity columns

An identity column is a special generated column that automatically produces unique, monotonically increasing numeric values. Identity columns are a convenient way to create surrogate keys without maintaining a separate sequence.

Note

Identity columns require Fabric Spark runtime 2.0 (Delta 4.1) or later.

You can control the first value with a start and the increment with a step. Both default to 1.

Create a table with an identity column

Spark SQL

Important

In Fabric, you can create identity columns only through the DeltaTableBuilder API. Spark SQL CREATE TABLE statements don't support identity column syntax.

PySpark

from delta.tables import DeltaTable, IdentityGenerator
from pyspark.sql.types import LongType

(
    DeltaTable.create()
    .tableName("dbo.identities")
    .addColumn("sk_1", dataType=LongType(), generatedAlwaysAs=IdentityGenerator())
    .addColumn("sk_2", dataType=LongType(), generatedAlwaysAs=IdentityGenerator(start=-1, step=1))
    .addColumn("sk_3", dataType=LongType(), generatedByDefaultAs=IdentityGenerator())
    .addColumn("sk_4", dataType=LongType(), generatedByDefaultAs=IdentityGenerator(start=-1, step=1))
    .execute()
)

Scala

import io.delta.tables.DeltaTable
import org.apache.spark.sql.types.LongType

DeltaTable.create()
  .tableName("dbo.identities")
  .addColumn(DeltaTable.columnBuilder("sk_1").dataType(LongType).generatedAlwaysAsIdentity().build())
  .addColumn(DeltaTable.columnBuilder("sk_2").dataType(LongType).generatedAlwaysAsIdentity(start = -1L, step = 1L).build())
  .addColumn(DeltaTable.columnBuilder("sk_3").dataType(LongType).generatedByDefaultAsIdentity().build())
  .addColumn(DeltaTable.columnBuilder("sk_4").dataType(LongType).generatedByDefaultAsIdentity(start = -1L, step = 1L).build())
  .execute()

To let callers optionally supply their own key values, use generatedByDefaultAs in Python or generatedByDefaultAsIdentity() in Scala instead.

Understand identity column behavior

Keep these behaviors in mind when you use identity columns:

• Generated values are unique and increasing, but they aren't guaranteed to be contiguous. Gaps can appear, especially with concurrent or failed writes.
• Identity generation applies only on insert. Existing rows keep their original values.
• Identity columns must use a BIGINT (long) data type.

Best practices

• Use generated columns to materialize derived partition or filter columns so queries benefit from file skipping.
• Keep generation expressions deterministic.
• Use identity columns for surrogate keys, but don't rely on them being gap-free or sequential.
• Test generated and identity column definitions on a nonproduction table before you apply them to shared tables.

Related content

• Schema evolution for Delta tables
• Column mapping for Delta tables
• File skipping for Delta tables
• Delta Lake in Fabric overview