Examples
INFO
In the code below, spark refers to a Spark client session connected to the Sail server. You can refer to the Getting Started guide for how it works.
Basic Usage
The Python example writes a DataFrame to a Delta table, appends the same rows, and reads the result. The SQL example creates a table at a path before inserting and querying rows.
path = "file:///tmp/sail/users"
df = spark.createDataFrame(
[(1, "Alice"), (2, "Bob")],
schema="id INT, name STRING",
)
# This creates a new table or overwrites an existing one.
df.write.format("delta").mode("overwrite").save(path)
# This appends data to an existing table.
df.write.format("delta").mode("append").save(path)
df = spark.read.format("delta").load(path)
df.show()CREATE TABLE users (id INT, name STRING)
USING delta
LOCATION 'file:///tmp/sail/users';
INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM users;Data Partitioning
Partitioning a Delta table places rows in directories according to column values. When a query filters on those columns, Sail can skip directories that cannot contain matching rows. Here, both APIs partition metrics by year and then read only rows from 2025.
path = "file:///tmp/sail/metrics"
df = spark.createDataFrame(
[(2024, 1.0), (2025, 2.0)],
schema="year INT, value FLOAT",
)
df.write.format("delta").mode("overwrite").partitionBy("year").save(path)
df = spark.read.format("delta").load(path).filter("year > 2024")
df.show()CREATE TABLE metrics (year INT, value FLOAT)
USING delta
LOCATION 'file:///tmp/sail/metrics'
PARTITIONED BY (year);
INSERT INTO metrics VALUES (2024, 1.0), (2025, 2.0);
SELECT * FROM metrics WHERE year > 2024;Dynamic Partition Overwrite
For the metrics table from the SQL example, replace only the partitions present in the input:
replacement = spark.createDataFrame([(2025, 3.0)], "year INT, value FLOAT")
replacement.writeTo("metrics").overwritePartitions()The 2024 partition remains unchanged. For path-based writes, use .mode("overwrite").option("partitionOverwriteMode", "dynamic"). Do not combine dynamic partition overwrite with replaceWhere or overwriteSchema.
Schema Evolution
Delta Lake checks incoming data against the table schema. To add fields during an append or overwrite, set mergeSchema to true. For the users table from the basic Python example, the following append adds an age column:
path = "file:///tmp/sail/users"
df = spark.createDataFrame([(3, "Carol", 30)], "id INT, name STRING, age INT")
df.write.format("delta").mode("append").option("mergeSchema", "true").save(path)When replacing all the data, overwriteSchema can also replace the table schema:
df = spark.createDataFrame([(1, "Alice")], "id BIGINT, name STRING")
df.write.format("delta").mode("overwrite").option("overwriteSchema", "true").save(path)To apply supported type widening to an existing column, enable delta.enableTypeWidening first. For example, the SQL users table can widen id from INT to BIGINT:
ALTER TABLE users SET TBLPROPERTIES ('delta.enableTypeWidening' = 'true');
ALTER TABLE users ALTER COLUMN id TYPE BIGINT;DML Operations
DELETE, UPDATE, and MERGE INTO change rows in an existing Delta table. The following statements use the users table from the SQL example:
UPDATE users SET name = 'Robert' WHERE id = 2;
DELETE FROM users WHERE id = 1;
MERGE INTO users AS target
USING (SELECT * FROM VALUES (2, 'Bob'), (3, 'Carol') AS s(id, name)) AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);To use deletion vectors for later changes, enable them on the table:
ALTER TABLE users SET TBLPROPERTIES ('delta.enableDeletionVectors' = 'true');
UPDATE users SET name = 'Caroline' WHERE id = 3;With deletion vectors enabled, an update can append replacement rows and mark the old rows as deleted without copying unaffected rows from the same data file. See DML operations for the supported modes.
Defaults and Identity Columns
A Delta table can fill in omitted values through column defaults or generated identity columns. This table assigns an identity value to id and uses new as the default status:
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY,
status STRING DEFAULT 'new',
quantity INT
)
USING delta
LOCATION 'file:///tmp/sail/orders'
TBLPROPERTIES ('delta.feature.allowColumnDefaults' = 'supported');
INSERT INTO orders (quantity) VALUES (10), (20);
INSERT INTO orders (status, quantity) VALUES (DEFAULT, 30);
SELECT * FROM orders ORDER BY id;An identity column declared GENERATED ALWAYS rejects explicit values. Use GENERATED BY DEFAULT AS IDENTITY if inserts also need to supply an id.
Time Travel
Time travel reads an earlier version of a Delta table by version number or timestamp. A timestamp selects the latest version committed at or before that time.
df = spark.read.format("delta").option("versionAsOf", "0").load(path)
df = spark.read.format("delta").option("timestampAsOf", "2025-01-02T03:04:05.678").load(path)In SQL, use VERSION AS OF or TIMESTAMP AS OF:
SELECT * FROM users VERSION AS OF 0;
SELECT * FROM users TIMESTAMP AS OF '2025-01-02T03:04:05.678';Choose a version or timestamp from the retained table history.
Change Data Feed
Enable CDF when creating a table to include its initial writes in the change feed:
cdf_path = "file:///tmp/sail/cdf_example"
spark.range(3).write.format("delta").option(
"delta.enableChangeDataFeed", "true"
).save(cdf_path)
changes = (
spark.read.format("delta")
.option("readChangeFeed", "true")
.option("startingVersion", 0)
.load(cdf_path)
)
changes.show()The output includes the table columns plus _change_type, _commit_version, and _commit_timestamp. This is a batch read, not a streaming source. Enabling CDF on an existing table records only subsequent changes; select a range with CDF enabled and retained log/data files.
Inspection and Maintenance
Inspect the SQL users table and compact its current files:
DESCRIBE DETAIL users;
DESCRIBE HISTORY users LIMIT 10;
OPTIMIZE users;
VACUUM users DRY RUN;VACUUM ... DRY RUN lists eligible files without deleting them. Review the results and retention requirements before running VACUUM users; deleted historical files can no longer serve time travel or CDF reads. OPTIMIZE supports compaction, not ZORDER or FULL.
Column Mapping
Column mapping lets a Delta table track columns by name or ID independently of their Parquet field names. Choose name or id when creating the table. These examples use separate locations:
df.write.format("delta").option("delta.columnMapping.mode", "name").save(
"file:///tmp/sail/mapped_by_name"
)
df.write.format("delta").option("delta.columnMapping.mode", "id").save(
"file:///tmp/sail/mapped_by_id"
)Read an existing table with column mapping in the same way as any other Delta table.
Catalog-Managed Tables
Read a table with the Delta catalogManaged feature by name through a configured Unity Catalog:
df = spark.table("my_catalog.my_schema.my_table")Replace the identifier with the catalog, schema, and table name. Sail uses commits ratified by the catalog to reconstruct the table snapshot. See catalog integration for supported operations.
