Constraints and generated columns

Why constraints

A pipeline that writes a negative amount or a null customer id produces wrong numbers for every report built on it. Validating in each job is easy to forget. A constraint lives with the table, so every writer (Spark, SQL, streaming, another team) is checked the same way, and the failing write is rejected atomically: none of its rows are committed.

NOT NULL and CHECK

spark.sql("""
    CREATE TABLE sales.orders (
      order_id    BIGINT NOT NULL,
      customer_id BIGINT NOT NULL,
      amount      DECIMAL(12,2),
      status      STRING,
      order_ts    TIMESTAMP
    ) USING delta
""")

spark.sql("ALTER TABLE sales.orders ADD CONSTRAINT amount_non_negative CHECK (amount >= 0)")
spark.sql("""
    ALTER TABLE sales.orders ADD CONSTRAINT valid_status
      CHECK (status IN ('placed', 'shipped', 'delivered', 'cancelled'))
""")

CREATE TABLE sales.orders (
  order_id    BIGINT NOT NULL,
  customer_id BIGINT NOT NULL,
  amount      DECIMAL(12,2),
  status      STRING,
  order_ts    TIMESTAMP
) USING delta;

ALTER TABLE sales.orders ADD CONSTRAINT amount_non_negative CHECK (amount >= 0);
ALTER TABLE sales.orders ADD CONSTRAINT valid_status
  CHECK (status IN ('placed', 'shipped', 'delivered', 'cancelled'));

• Adding a CHECK constraint to an existing table validates all existing rows first, and fails if any violate it.
• Constraints are stored in the table properties and in the transaction logtransaction log: The ordered list of commits that defines which files make up a Delta table at each version. Learn more → protocol: old readers that do not understand them may be blocked from writing.
• ALTER TABLE ... DROP CONSTRAINT name removes one.
• Delta does not enforce primary or foreign keys; on Databricks they are informational only. Uniqueness must be ensured by MERGE logic.

Generated columns

A generated column is computed from other columns on every write. You cannot write a different value into it.

from delta.tables import DeltaTable
(DeltaTable.create(spark).tableName("events")
    .addColumn("event_id", "BIGINT")
    .addColumn("event_ts", "TIMESTAMP")
    .addColumn("event_date", "DATE", generatedAlwaysAs="CAST(event_ts AS DATE)")
    .partitionedBy("event_date")
    .execute())

CREATE TABLE events (
  event_id   BIGINT,
  event_ts   TIMESTAMP,
  event_date DATE GENERATED ALWAYS AS (CAST(event_ts AS DATE))
) USING delta
PARTITIONED BY (event_date);

The bonus: a query that filters only on event_ts still gets partition pruning, because Delta knows how event_date is derived and adds the matching partition filter. Writers no longer need to remember to fill event_date correctly.

Identity columns

spark.sql("""
    CREATE TABLE dim_customer (
      customer_sk BIGINT GENERATED ALWAYS AS IDENTITY,
      customer_id STRING,
      name        STRING
    ) USING delta
""")

CREATE TABLE dim_customer (
  customer_sk BIGINT GENERATED ALWAYS AS IDENTITY,
  customer_id STRING,
  name        STRING
) USING delta;

Values are unique and increasing, but not consecutive: each writer tasktask: The work for one partition in one stage, run on one CPU core. Learn more → reserves its own range, so gaps are normal. Identity columns make concurrent writes harder (writers must coordinate the high-water mark), and Delta disables some concurrent-write optimisations for such tables.

In Iceberg

Iceberg tables support required (NOT NULL) fields and, in format v3, default values; CHECK constraints are not part of the spec, so value checks live in the pipeline or engine. Iceberg's hidden partitioning gives the benefit of generated partition columns without any extra column: partition by days(event_ts) and filters on event_ts prune automatically.

Common mistakes