developersIO
I tried basic operations on Delta tables on Databricks

I tried basic operations on Delta tables on Databricks

I tried practicing basic Delta Lake operations on Databricks. I went through time travel, rollback, optimization, semi-structured data support, constraint settings, and more.
モダンデータスタック(MDS)
Databricks
2026.09.23

This page has been translated by machine translation. View original

Introduction

I would like to organize the basic content about Delta Lake, and in this article I will summarize what I tried on Databricks with the following operations.

  • Time travel
  • Rollback with RESTORE
  • OPTIMIZE and VACUUM
  • Basics of VARIANT type
  • Constraints (NOT NULL / CHECK)

Overview of Delta Lake

With Parquet data files placed on a traditional data lake alone, it was not possible to achieve ACID transactions or scalable metadata processing.
Delta Lake is open-source software that provides the storage layer underlying tables in a lakehouse, and achieves these capabilities by adding a file-based transaction log to Parquet data files.

Originally a protocol developed by Databricks, it continues to be developed as an open-source project today. It operates on top of existing data lakes and is also compatible with the Apache Spark API.

https://docs.databricks.com/gcp/ja/delta/

Databricks is a cloud-based, unified, open analytics platform. It adopts Delta Lake as the default format for all tables on the platform, and if you do not explicitly specify a format when running CREATE TABLE, a table is automatically created in Delta format.

Delta Lake Structure

A Delta table consists of the following two elements:

  • Parquet data files: Delta Lake writes data in Parquet format
  • DeltaLog (transaction log): Each time a write is made to a Delta table, a log file is added to the _delta_log folder

Readers reference this _delta_log at read time to reconstruct the state of the latest version. This mechanism enables the following:

  • Data skipping: Per-file min/max statistics retained in the log allow skipping the reading of unnecessary files
  • Time travel: Since the history of all versions remains in the log, you can query the state at any point in the past

Overview of Managed Tables and External Tables

In Databricks (Unity Catalog), table types are divided into two categories, "managed" and "external," depending on where the data is stored.

https://docs.databricks.com/aws/en/tables/#table-types

The main differences are as follows.

Managed Table External Table
How to create CREATE TABLE without specifying LOCATION Specify LOCATION as in CREATE TABLE ... LOCATION 's3://...'
Storage path Automatically managed by Databricks Specified by the user (prior registration as an External Location in Unity Catalog is required)
When table is deleted Metadata + actual data (Parquet + _delta_log) are marked for deletion Only metadata is deleted; actual data is not deleted

Prerequisites

The following environment is used.

  • Databricks Free Edition
    • Serverless workspace
  • Running SQL / Python cells in a notebook on Databricks

The runtime is as follows.

> SELECT current_version()
+---------------------------------------------------------------------------------------------------------------------------+
|current_version()                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------+
|{19.6.x-aarch64-photon-scala2.13, NULL, 9dea94816c243d7b1977f03d2e6092ddedf725b6, 7a8881535ee395968babb208dc8c00ebd4e0b547}|
+---------------------------------------------------------------------------------------------------------------------------+

Validation Environment

Create a dedicated catalog (demo_catalog) for validation.

-- Create validation environment
CREATE CATALOG IF NOT EXISTS demo_catalog
COMMENT 'Catalog for Delta Lake validation';

-- Use the default schema
USE SCHEMA default;

Also, assume that an external location has already been created following the steps below.

https://dev.classmethod.jp/articles/databricks-unity-catalog-s3/

Create an External Table

Since the storage path of a managed table is managed by Unity Catalog and cannot be accessed directly, we create an external table.

https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-external-tables

Since the external location was already created in the prerequisites, you can check the created location with the following command.

> SHOW EXTERNAL LOCATIONS;
+-------------------------+----------------------------+-------+
|name                     |url                         |comment|
+-------------------------+----------------------------+-------+
|yasuhara-test-s3-location|s3://<bucket-name>/          |NULL   |
+-------------------------+----------------------------+-------+

Create an external table by specifying a path under the already-created external location in LOCATION. Parquet files and _delta_log will actually be created under this path.

-- Define the external table
CREATE OR REPLACE TABLE demo_catalog.default.orders_external (
  order_id   INT,
  item_name  STRING,
  unit_price DOUBLE,
  ordered_at DATE
)
USING DELTA
LOCATION 's3://<bucket-name>/external-tables/orders_external';

Add records to the table in two separate batches.

INSERT INTO demo_catalog.default.orders_external VALUES
  (1, 'Laptop',          128000.00, '2026-08-01'),
  (2, 'Wireless Mouse',    2980.00, '2026-08-01'),
  (3, 'USB-C Cable',        980.00, '2026-08-01');

INSERT INTO demo_catalog.default.orders_external VALUES
  (4, 'Desk Light',        3500.00, '2026-08-02');

Now let's check the S3 bucket directly.

aws s3 ls s3://<bucket-name>/external-tables/orders_external/ --recursive

The output is as follows. You can confirm that Parquet files are created directly under the path specified in LOCATION, and transaction logs (JSON) are created under _delta_log/.

s3://<bucket>/external-tables/orders_external/
2026-09-21 22:23:52       2408 external-tables/orders_external/_delta_log/00000000000000000000.crc  ← table creation
2026-09-21 22:23:51       1462 external-tables/orders_external/_delta_log/00000000000000000000.json ← table creation
2026-09-21 22:23:59       3114 external-tables/orders_external/_delta_log/00000000000000000001.crc  ← 1st INSERT
2026-09-21 22:23:59       1415 external-tables/orders_external/_delta_log/00000000000000000001.json ← 1st INSERT
2026-09-21 22:24:03       3809 external-tables/orders_external/_delta_log/00000000000000000002.crc  ← 2nd INSERT
2026-09-21 22:24:03       1409 external-tables/orders_external/_delta_log/00000000000000000002.json ← 2nd INSERT
2026-09-21 22:23:51          0 external-tables/orders_external/_delta_log/_staged_commits/
2026-09-21 22:23:57       1593 external-tables/orders_external/part-00000-3fce0554-c2d4-4352-a785-58be1c897320.c000.snappy.parquet ← created by 1st INSERT (3 records)
2026-09-21 22:24:01       1509 external-tables/orders_external/part-00000-ad75a5d0-1e7e-41b9-b221-cc1fc293a25d.c000.snappy.parquet ← created by 2nd INSERT (1 record)

Looking at the JSON (DeltaLog) and Parquet (data files) here, the following can be observed.

  • JSON: 3 files exist (00000000000000000000 through 00000000000000000002). One file is created for each of the three transactions: table creation, 1st INSERT, and 2nd INSERT.
  • Parquet: 2 files. Since table creation itself does not involve data, no Parquet file is generated; one file is created for each of the two INSERTs.

Let's take a quick look at the contents of a JSON file.

aws s3 cp s3://<bucket-name>/external-tables/orders_external/_delta_log/00000000000000000001.json - | jq .

As shown below, you can confirm that commitInfo (timestamp, operation, operation parameters), the name of the added file, and statistical information (min/max) for that file are recorded.

{
  "commitInfo": {
    "timestamp": 1789997037517,
    "userId": "<user-id>",
    "userName": "<user-name>",
    "operation": "WRITE",
    "operationParameters": {
      "mode": "Append",
      "statsOnLoad": false,
      "partitionBy": "[]"
    },
    "notebook": {
      "notebookId": "2201025823614008"
    },
    "queryHistoryStatementId": "7f44d277-add3-476a-b306-e43a2dd9ddfe",
    "clusterId": "0921-125107-tjj6uws4-v2n",
    "readVersion": 0,
    "isolationLevel": "WriteSerializable",
    "isBlindAppend": true,
    "dataChange": true,
    "operationMetrics": {
      "numFiles": "1",
      "numOutputRows": "3",
      "numOutputBytes": "1593"
    },
    "tags": {
      "noRowsCopied": "true",
      "restoresDeletedRows": "false"
    },
    "engineInfo": "Databricks-Runtime/19.6.x-aarch64-photon-scala2.13",
    "txnId": "da38bc9d-0569-423b-8cb1-e2e8c148d09c"
  }
}
{
  "add": {
    "path": "part-00000-3fce0554-c2d4-4352-a785-58be1c897320.c000.snappy.parquet",
    "partitionValues": {},
    "size": 1593,
    "modificationTime": 1789997037000,
    "dataChange": true,
    "stats": "{\"numRecords\":3,\"minValues\":{\"order_id\":1,\"item_name\":\"USB-C Cable\",\"unit_price\":980.0,\"ordered_at\":\"2026-08-01\"},\"maxValues\":{\"order_id\":3,\"item_name\":\"Wireless Mouse\",\"unit_price\":128000.0,\"ordered_at\":\"2026-08-01\"},\"nullCount\":{\"order_id\":0,\"item_name\":0,\"unit_price\":0,\"ordered_at\":0},\"tightBounds\":true}",
    "tags": {
      "INSERTION_TIME": "1789997037000000",
      "MIN_INSERTION_TIME": "1789997037000000",
      "MAX_INSERTION_TIME": "1789997037000000",
      "OPTIMIZE_TARGET_SIZE": "268435456"
    }
  }
}

In a Delta table, each transaction (in this case, table creation, 1st INSERT, and 2nd INSERT) is recorded one by one as a version in the table history. This corresponds to each JSON file under the _delta_log described earlier. This version history can also be checked with the following SQL.

(
    spark.sql("DESCRIBE HISTORY demo_catalog.default.orders_external")
    .select("version", "timestamp", "userName", "operation")
    .show(truncate=False)
)

+-------+-------------------+------------------------------+-----------------------+
|version|timestamp          |userName                      |operation              |
+-------+-------------------+------------------------------+-----------------------+
|2      |2026-09-21 13:24:03|<User>                        |WRITE                  |
|1      |2026-09-21 13:23:59|<User>                        |WRITE                  |
|0      |2026-09-21 13:23:51|<User>                        |CREATE OR REPLACE TABLE|
+-------+-------------------+------------------------------+-----------------------+

https://docs.databricks.com/aws/en/sql/language-manual/delta-describe-history

Time Travel

Time travel is a feature that allows you to query the state of a table at any point in the past. As we have seen, Delta tables record a version for each transaction, and the data corresponding to that version is retained as-is. This makes it possible to specify any version and query it.

https://docs.databricks.com/gcp/ja/tables/history

An example query specifying a version is as follows. You can query the data content corresponding to each version.

-- Version 1: After the first insert
> SELECT * FROM demo_catalog.default.orders_external VERSION AS OF 1;
+--------+----------------+----------+----------+
|order_id|       item_name|unit_price|ordered_at|
+--------+----------------+----------+----------+
|       1|          Laptop|  128000.0|2026-08-01|
|       2|  Wireless Mouse|    2980.0|2026-08-01|
|       3|    USB-C Cable |     980.0|2026-08-01|
+--------+----------------+----------+----------+

-- Version 2: After the second insert
> SELECT * FROM demo_catalog.default.orders_external VERSION AS OF 2;
+--------+----------------+----------+----------+
|order_id|       item_name|unit_price|ordered_at|
+--------+----------------+----------+----------+
|       1|          Laptop|  128000.0|2026-08-01|
|       2|  Wireless Mouse|    2980.0|2026-08-01|
|       3|      USB-C Cable|     980.0|2026-08-01|
|       4|      Desk Light|    3500.0|2026-08-02|
+--------+----------------+----------+----------+

You can also query by specifying a timestamp. In the case of a timestamp, the most recently committed version at or before the specified time is queried.

-- Specify a timestamp: returns the state before the version 0 insert
> SELECT * FROM demo_catalog.default.orders_external TIMESTAMP AS OF '2026-09-21 13:23:55';
+--------+---------+----------+----------+
|order_id|item_name|unit_price|ordered_at|
+--------+---------+----------+----------+
+--------+---------+----------+----------+

Offset specification is also possible. By combining functions such as current_timestamp() with interval, relative specification is possible.
※ TIMESTAMP AS OF can only specify a time at or before the commit time of the latest version; if the calculated result is later than that, it will be rejected with an error.

-- Specify the state 30 minutes ago
SELECT * FROM demo_catalog.default.orders_external TIMESTAMP AS OF current_timestamp() - interval 30 minutes;

-- Specify the state 1 day ago
SELECT * FROM demo_catalog.default.orders_external TIMESTAMP AS OF date_sub(current_date(), 1);

https://docs.databricks.com/gcp/en/tables/history#time-travel-syntax

Time travel can be used for auditing, debugging, and as the basis for rollbacks, but note that if old Parquet files are physically deleted by VACUUM (described later), you will no longer be able to go back to a point before that.

Table Properties That Determine the Range of Time Travel

To query a past version of a table, both the log file and data files for that version must be retained.
This is controlled by the following two table properties.

  • delta.deletedFileRetentionDuration
    • The threshold before VACUUM deletes data files (Parquet) that are no longer referenced by the current table version
    • Default is interval 7 days (7 days)
  • delta.logRetentionDuration
    • The retention period for the transaction logs (JSON) themselves under _delta_log (controls how long the table history is retained)
    • Default is interval 30 days (30 days)

Based on the above, the default queryable period for time travel is 7 days. This period can be changed later as follows.

-- Change table properties
ALTER TABLE demo_catalog.default.orders_external
SET TBLPROPERTIES (
  'delta.deletedFileRetentionDuration' = 'interval 10 days',
  'delta.logRetentionDuration'         = 'interval 10 days'
);

Check the settings:

(
    spark.sql("SHOW TBLPROPERTIES demo_catalog.default.orders_external")
    .filter("key LIKE '%RetentionDuration%'")
    .show(truncate=False)
)

+----------------------------------+----------------+
|key                               |value           |
+----------------------------------+----------------+
|delta.deletedFileRetentionDuration|interval 10 days|
|delta.logRetentionDuration        |interval 10 days|
+----------------------------------+----------------+

Rollback with RESTORE

To restore a Delta table to a previous state, you can use the RESTORE command. Similar to time travel queries, restoring by specifying a previous version number or timestamp is supported.

https://docs.databricks.com/aws/en/sql/language-manual/delta-restore

Here, assuming an incorrect change was made to the orders_external table, we restore it to the state before the change.

First, let's check the current state.

> SELECT * FROM demo_catalog.default.orders_external;
+--------+----------------+----------+----------+
|order_id|       item_name|unit_price|ordered_at|
+--------+----------------+----------+----------+
|       1|          Laptop|  128000.0|2026-08-01|
|       2|  Wireless Mouse|    2980.0|2026-08-01|
|       3|      USB-C Cable|     980.0|2026-08-01|
|       4|      Desk Light|    3500.0|2026-08-02|
+--------+----------------+----------+----------+

Now suppose an UPDATE was accidentally executed without a WHERE clause. The unit_price of all records has become 0.

-- UPDATE without a WHERE clause
UPDATE demo_catalog.default.orders_external SET unit_price = 0;

-- Check the current state: unit_price is 0 for all records
> SELECT * FROM demo_catalog.default.orders_external;
+--------+----------------+----------+----------+
|order_id|item_name       |unit_price|ordered_at|
+--------+----------------+----------+----------+
|1       |Laptop          |0.0       |2026-08-01|
|2       |Wireless Mouse  |0.0       |2026-08-01|
|3       |USB-C Cable     |0.0       |2026-08-01|
|4       |Desk Light      |0.0       |2026-08-02|
+--------+----------------+----------+----------+

Check the history with DESCRIBE HISTORY and identify the version before the accident.
※ The table properties were changed several times, and these changes are also recorded.

(
    spark.sql("DESCRIBE HISTORY demo_catalog.default.orders_external")
    .select("version", "timestamp", "userName", "operation")
    .show(truncate=False)
)

+-------+-------------------+------------------------------+-----------------------+
|version|timestamp          |userName                      |operation              |
+-------+-------------------+------------------------------+-----------------------+
|7      |2026-09-22 13:06:43|<User>                        |UPDATE                 |
|6      |2026-09-22 12:54:40|<User>                        |SET TBLPROPERTIES      |
|5      |2026-09-22 07:51:56|<User>                        |SET TBLPROPERTIES      |
|4      |2026-09-22 07:47:18|<User>                        |SET TBLPROPERTIES      |
|3      |2026-09-22 07:27:02|<User>                        |SET TBLPROPERTIES      |
|2      |2026-09-21 13:24:03|<User>                        |WRITE                  |
|1      |2026-09-21 13:23:59|<User>                        |WRITE                  |
|0      |2026-09-21 13:23:51|<User>                        |CREATE OR REPLACE TABLE|
+-------+-------------------+------------------------------+-----------------------+

Since the UPDATE occurred at version 7, we RESTORE to version 6, immediately before it.

-- Restore to the version before the UPDATE
RESTORE TABLE demo_catalog.default.orders_external TO VERSION AS OF 6;

-- Check the current state: unit_price has been restored to its original values
> SELECT * FROM demo_catalog.default.orders_external;
+--------+----------------+----------+----------+
|order_id|item_name       |unit_price|ordered_at|
+--------+----------------+----------+----------+
|1       |Laptop          |128000.0  |2026-08-01|
|2       |Wireless Mouse  |2980.0    |2026-08-01|
|3       |USB-C Cable     |980.0     |2026-08-01|
|4       |Desk Light      |3500.0    |2026-08-02|
+--------+----------------+----------+----------+

The restore was successful. Checking the version history again, the restore itself is also recorded as one new transaction. This means the history of the version where the accident occurred is not erased, and it is still possible to check what happened using time travel afterward.
However, since it is recorded as a change, note that it may affect downstream jobs and similar processes.

(
    spark.sql("DESCRIBE HISTORY demo_catalog.default.orders_external")
    .select("version", "timestamp", "userName", "operation")
    .show(truncate=False)
)

+-------+-------------------+------------------------------+-----------------------+
|version|timestamp          |userName                      |operation              |
+-------+-------------------+------------------------------+-----------------------+
|8      |2026-09-22 13:10:09|<User>                        |RESTORE                |
|7      |2026-09-22 13:06:43|<User>                        |UPDATE                 |
|6      |2026-09-22 12:54:40|<User>                        |SET TBLPROPERTIES      |
|5      |2026-09-22 07:51:56|<User>                        |SET TBLPROPERTIES      |
|4      |2026-09-22 07:47:18|<User>                        |SET TBLPROPERTIES      |
|3      |2026-09-22 07:27:02|<User>                        |SET TBLPROPERTIES      |
|2      |2026-09-21 13:24:03|<User>                        |WRITE                  |
|1      |2026-09-21 13:23:59|<User>                        |WRITE                  |
|0      |2026-09-21 13:23:51|<User>                        |CREATE OR REPLACE TABLE|
+-------+-------------------+------------------------------+-----------------------+

OPTIMIZE and VACUUM

OPTIMIZE

In Delta tables, a Parquet file is generated for each DML operation. In cases where small batch writes accumulate, such as when using a streaming source, the table ends up consisting of a large number of small Parquet files, which degrades read performance.

In such cases, OPTIMIZE can consolidate small files into larger files. This file consolidation is also called compaction.

https://docs.databricks.com/aws/ja/tables/operations/optimize

Here, we create a new orders_optim_ext table (external table) and reproduce the state of accumulated small files by performing INSERT 10 times, one record at a time.

CREATE OR REPLACE TABLE demo_catalog.default.orders_optim_ext (
  order_id   INT,
  item_name  STRING,
  unit_price DOUBLE,
  ordered_at DATE
)
USING DELTA
LOCATION 's3://<バケット名>/external-tables/orders_optim_ext';

INSERT INTO demo_catalog.default.orders_optim_ext VALUES (1,  '商品01', 100.00, '2026-08-01');
INSERT INTO demo_catalog.default.orders_optim_ext VALUES (2,  '商品02', 200.00, '2026-08-01');
INSERT INTO demo_catalog.default.orders_optim_ext VALUES (3,  '商品03', 300.00, '2026-08-01');
INSERT INTO demo_catalog.default.orders_optim_ext VALUES (4,  '商品04', 400.00, '2026-08-01');
INSERT INTO demo_catalog.default.orders_optim_ext VALUES (5,  '商品05', 500.00, '2026-08-01');
INSERT INTO demo_catalog.default.orders_optim_ext VALUES (6,  '商品06', 600.00, '2026-08-01');
INSERT INTO demo_catalog.default.orders_optim_ext VALUES (7,  '商品07', 700.00, '2026-08-01');
INSERT INTO demo_catalog.default.orders_optim_ext VALUES (8,  '商品08', 800.00, '2026-08-01');
INSERT INTO demo_catalog.default.orders_optim_ext VALUES (9,  '商品09', 900.00, '2026-08-01');
INSERT INTO demo_catalog.default.orders_optim_ext VALUES (10, '商品10', 1000.00, '2026-08-01');

By checking the file count with numFiles in DESCRIBE DETAIL, you can confirm that a Parquet file is created with each INSERT.

# Check file count before OPTIMIZE
(
    spark.sql("DESCRIBE DETAIL demo_catalog.default.orders_optim_ext")
    .select("numFiles", "sizeInBytes")
    .show(truncate=False)
)

+--------+-----------+
|numFiles|sizeInBytes|
+--------+-----------+
|10      |14918      |
+--------+-----------+

From this state, run OPTIMIZE.

OPTIMIZE demo_catalog.default.orders_optim_ext;

Checking numFiles again shows that the 10 files have been consolidated into 1.

# Check file count after OPTIMIZE
(
    spark.sql("DESCRIBE DETAIL demo_catalog.default.orders_optim_ext")
    .select("numFiles", "sizeInBytes")
    .show(truncate=False)
)

+--------+-----------+
|numFiles|sizeInBytes|
+--------+-----------+
|1       |1650       |
+--------+-----------+

Since OPTIMIZE is also recorded as a transaction, a new version is added to DESCRIBE HISTORY. One important point is that the 10 files before consolidation are not deleted and remain in storage (depending on the retention period, they may become candidates for physical deletion by VACUUM, described later, as unnecessary files).

(
    spark.sql("DESCRIBE HISTORY demo_catalog.default.orders_optim_ext")
    .select("version", "timestamp", "operation")
    .show(truncate=False)
)

+-------+-------------------+-----------------------+
|version|timestamp          |operation              |
+-------+-------------------+-----------------------+
|11     |2026-09-22 14:22:53|OPTIMIZE               |
|10     |2026-09-22 14:22:44|WRITE                  |
|9      |2026-09-22 14:22:40|WRITE                  |
|8      |2026-09-22 14:22:36|WRITE                  |
|7      |2026-09-22 14:22:32|WRITE                  |
|6      |2026-09-22 14:22:28|WRITE                  |
|5      |2026-09-22 14:22:25|WRITE                  |
|4      |2026-09-22 14:22:21|WRITE                  |
|3      |2026-09-22 14:22:17|WRITE                  |
|2      |2026-09-22 14:22:13|WRITE                  |
|1      |2026-09-22 14:22:09|WRITE                  |
|0      |2026-09-22 14:22:05|CREATE OR REPLACE TABLE|
+-------+-------------------+-----------------------+

By default, OPTIMIZE compacts small files into files up to a maximum of 1 GB.

https://docs.databricks.com/aws/ja/sql/language-manual/delta-optimize

VACUUM

Old files that become unnecessary after OPTIMIZE remain in storage until the retention period (default 7 days) has passed. Unless manually deleted, these files remain until VACUUM is run. VACUUM allows you to physically delete these unnecessary files. By deleting unused data files, you can reduce storage costs.

https://docs.databricks.com/aws/ja/tables/operations/vacuum

https://docs.databricks.com/aws/ja/sql/language-manual/delta-vacuum

Note that VACUUM only deletes data files; log files (JSON) are automatically cleaned up based on the log file retention period each time a checkpoint is generated.

https://docs.databricks.com/aws/en/tables/history#configure-data-retention-for-time-travel-queries

Let's try VACUUM on the previous table. Since the table was just created, each file is within the default retention period and will not be targeted for deletion, so we first change the data file retention period.

ALTER TABLE demo_catalog.default.orders_optim_ext
SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = 'interval 0 hours');

With this setting, run VACUUM.

-- Check deletion targets with a dry run
> VACUUM demo_catalog.default.orders_optim_ext DRY RUN;
+-------------------------------------------------------------------------------------------------------------------------------+
|path                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------+
|s3://<バケット名>/external-tables/orders_optim_ext/part-00000-88838cdc-9c5c-40e4-b9c1-718ef4cc8c08.c000.snappy.parquet          |
|s3://<バケット名>/external-tables/orders_optim_ext/part-00000-b4bd681b-6095-45ab-9595-cf057832a810.c000.snappy.parquet          |
|s3://<バケット名>/external-tables/orders_optim_ext/part-00000-730f1992-1df1-4acd-92cd-29f6dc010203.c000.snappy.parquet          |
|s3://<バケット名>/external-tables/orders_optim_ext/part-00000-590eb48f-3e98-43a8-ad02-61b9b7680402.c000.snappy.parquet          |
|s3://<バケット名>/external-tables/orders_optim_ext/part-00000-d649c80f-c0be-4bb7-aae3-7a0654e504b1.c000.snappy.parquet          |
|s3://<バケット名>/external-tables/orders_optim_ext/part-00000-a3a5b7d6-7f24-40e3-89e6-776968f72971.c000.snappy.parquet          |
|s3://<バケット名>/external-tables/orders_optim_ext/part-00000-ee8748c8-bcdb-4637-a054-ee606bef7276.c000.snappy.parquet          |
|s3://<バケット名>/external-tables/orders_optim_ext/part-00000-7506f409-14f3-4491-8678-b7772b9199be.c000.snappy.parquet          |
|s3://<バケット名>/external-tables/orders_optim_ext/part-00000-4686ec47-96b3-455b-ac07-798d2f780af7.c000.snappy.parquet          |
|s3://<バケット名>/external-tables/orders_optim_ext/part-00000-7a5270f7-22ab-4ba2-921d-f26198926aee.c000.snappy.parquet          |
+-------------------------------------------------------------------------------------------------------------------------------+

-- Run VACUUM
VACUUM demo_catalog.default.orders_optim_ext;

Since VACUUM physically deleted the old files, attempting to time travel to a version prior to OPTIMIZE will result in an error because the corresponding files no longer exist. Note that VACUUM is an irreversible operation.

> SELECT * FROM demo_catalog.default.orders_optim_ext VERSION AS OF 10;
[DELTA_UNSUPPORTED_TIME_TRAVEL_BEYOND_DELETED_FILE_RETENTION_DURATION] Cannot time travel beyond delta.deletedFileRetentionDuration (0 HOURS) set on the table. SQLSTATE: 0AKDC

DELETE / UPDATE and Deletion Vectors

The SQL syntax for DELETE and UPDATE itself is the same as conventional SQL, but the internal implementation uses Deletion Vectors introduced in Databricks Runtime 12.2 LTS and later.

Previously, the copy-on-write approach was used, which read the entire file containing the rows to be deleted and rewrote the whole file with the post-deletion data. Now, instead of rewriting files, only a small metadata file called a Deletion Vector is newly created that records "which rows were deleted."

https://docs.databricks.com/gcp/en/tables/features/deletion-vectors

Here, we run the same DELETE on the external table created earlier (orders_external) and check the file behavior.

DELETE FROM demo_catalog.default.orders_external WHERE order_id = 4;

Immediately after running DELETE, check again with aws s3 ls. While the results below are filtered, you can see that the original Parquet file remains while a deletion vector file with a name like deletion_vector_... has been newly created.

# Filter to only file names containing deletion_vector
aws s3 ls s3://<バケット名>/external-tables/orders_external/ --recursive | grep "deletion_vector"

2026-09-23 10:40:59         43 external-tables/orders_external/deletion_vector_704765c2-5163-49c4-99bf-ae86389fa62d.bin

VARIANT Type

In Delta Lake, VARIANT can and is recommended to be used for querying semi-structured data.

https://docs.databricks.com/aws/en/tables/features/variant

https://docs.databricks.com/aws/en/semi-structured/variant

Using the following table as an example, we define a managed table containing the VARIANT type and add records.

CREATE OR REPLACE TABLE demo_catalog.default.shipment_events (
  event_id INT,
  payload  VARIANT  
) USING DELTA;

-- Use PARSE_JSON() to convert to binary representation when writing
INSERT INTO demo_catalog.default.shipment_events VALUES
  (1, PARSE_JSON('{"order_id":1,"status":"shipped","carrier":"YamatoTransport"}')),
  (2, PARSE_JSON('{"order_id":2,"status":"delivered","carrier":"SagawaExpress","delivered_at":"2026-08-03"}'));

When reading, use : to access fields and :: to cast to the desired type.

> SELECT
  payload,
  payload:order_id::int    AS order_id,
  payload:status::string   AS status,
  payload:carrier::string  AS carrier
FROM demo_catalog.default.shipment_events;
+-----------------------------------------------------------------------------------------+--------+---------+---------------+
|payload                                                                                  |order_id|status   |carrier        |
+-----------------------------------------------------------------------------------------+--------+---------+---------------+
|{"carrier":"YamatoTransport","order_id":1,"status":"shipped"}                            |1       |shipped  |YamatoTransport|
|{"carrier":"SagawaExpress","delivered_at":"2026-08-03","order_id":2,"status":"delivered"}|2       |delivered|SagawaExpress  |
+-----------------------------------------------------------------------------------------+--------+---------+---------------+

If a non-existent path is specified, it does not result in an error but returns NULL.

Expanding Arrays

If a VARIANT contains an array, LATERAL VIEW EXPLODE can be used. Here, we add a record that holds the shipping progress (tracking_events) as a nested array.

INSERT INTO demo_catalog.default.shipment_events VALUES
  (3, PARSE_JSON('{
    "order_id": 3,
    "status": "in_transit",
    "carrier": "YamatoTransport",
    "tracking_events": [
      {"location": "Tokyo", "status": "picked_up",  "timestamp": "2026-08-05T09:00:00"},
      {"location": "Osaka", "status": "in_transit", "timestamp": "2026-08-06T14:00:00"}
    ]
  }'));

tracking_events is an array, and each element is itself a nested JSON (an object with location/status/timestamp). Cast it as an array using payload:tracking_events::array<variant>, expand it row by row with LATERAL VIEW EXPLODE, and then use : again on each expanded element (VARIANT) to extract the fields.

> SELECT
  event_id,
  payload:order_id::int             AS order_id,
  tracking_event:location::string   AS location,
  tracking_event:status::string     AS status,
  tracking_event:timestamp::string  AS event_timestamp
FROM demo_catalog.default.shipment_events
LATERAL VIEW EXPLODE(payload:tracking_events::array<variant>) AS tracking_event
WHERE event_id = 3;
+--------+--------+--------+-----------+-------------------+
|event_id|order_id|location|status     |event_timestamp    |
+--------+--------+--------+-----------+-------------------+
|3       |3       |Tokyo   |picked_up  |2026-08-05T09:00:00|
|3       |3       |Osaka   |in_transit |2026-08-06T14:00:00|
+--------+--------+--------+-----------+-------------------+

You can confirm that a single record (event_id = 3) has been expanded into rows equal to the number of array elements (2 rows), with access to each nested field.

https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-lateral-view

Constraints on Delta Tables in Databricks

Delta tables allow you to define constraints that enforce business rules at the value level, not just the structure (column names and data types). Databricks supports the following two representative types of constraints:

  • NOT NULL: Enforces that values in a specific column cannot be null
  • CHECK: Enforces that a specified boolean expression must be true for each input row

Primary key, foreign key, and uniqueness constraints (UNIQUE) can be set, but they are for informational purposes only and are not enforced.

https://docs.databricks.com/aws/ja/tables/constraints#適用される制約

Here, we create the following orders_validated table as a managed table.

CREATE OR REPLACE TABLE demo_catalog.default.orders_validated (
  order_id   INT,
  item_name  STRING NOT NULL,
  unit_price DOUBLE,
  status     STRING
) USING DELTA;

-- Prohibit writing rows where unit_price is 0 or less
ALTER TABLE demo_catalog.default.orders_validated
  ADD CONSTRAINT valid_unit_price CHECK (unit_price > 0);

-- Prohibit writing values other than predefined values to status
ALTER TABLE demo_catalog.default.orders_validated
  ADD CONSTRAINT valid_status CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'));

https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-alter-table-add-constraint

Attempting a write that violates a constraint results in an error.

> INSERT INTO demo_catalog.default.orders_validated VALUES
  (1, 'Laptop', 128000.00, 'pending');
+-----------------+-----------------+
|num_affected_rows|num_inserted_rows|
+-----------------+-----------------+
|1                |1                |
+-----------------+-----------------+

-- Violating write
> INSERT INTO demo_catalog.default.orders_validated VALUES
  (2, 'Wireless Mouse', -500.00, 'pending');
[DELTA_VIOLATE_CONSTRAINT_WITH_VALUES] CHECK constraint valid_unit_price (unit_price > 0) violated by row with values:
 - unit_price : -500.0. SQLSTATE: 23001

The list of constraints can be checked with SHOW TBLPROPERTIES.

SHOW TBLPROPERTIES demo_catalog.default.orders_validated;

When adding a constraint after existing data is present, all existing rows are validated against the constraint before it is added.

Conclusion

We tried basic operations such as creating tables and time travel with Delta Lake on Databricks.
We hope this content is helpful to someone.

Share this article