MaxCompute Delta Tables are a high-performance table format for large-scale analytical datasets, available as Append Delta Tables (no primary key) and PK Delta Tables (with primary key).
Overview
Delta Table is a high-performance table format in Alibaba Cloud MaxCompute that supports ACID transactions, incremental queries, time travel, dynamic cluster bucketing, real-time data updates, and schema evolution. The native data lakehouse and near-real-time computing capabilities in MaxCompute allow you to use standard SQL to create, update, and query Delta Tables without managing underlying storage or metadata. MaxCompute automatically maintains and optimizes them for a balance of ease of use and cost-effectiveness.
Features
Category | Feature | Append Delta Table | PK Delta Table |
Basic DML | Insert, Update, Delete, and Merge Into. | Supported | Supported |
ACID transactions | Read Committed/Snapshot Isolation. | Supported | Supported |
Primary key | Defines a primary key. | Not supported | Supports partial column updates |
Schema evolution | Add, delete, rename, reorder, and change the data type of columns. | Supported | Supported |
Data import |
| Stream/Batch Upload Data is visible immediately after a stream upload. | Upsert |
Time travel | Query historical snapshots by time point or version number for result reproduction or audit analysis. | Supported | Supported |
Incremental computing | Delta Live Materialized View (MV) and Incremental Read. | Supported (Incremental materialized view support in progress.) | Supported |
Data organization optimization | Automatically maintains incremental data files through small file merging, multi-level COMPACTION, and data sorting. Optimize data organization for Append Delta Tables and Data organization optimization for PK Delta tables. | Supported No bucket configuration required. Dynamic Bucketing adapts to data distribution automatically. | Supported |
Query performance optimization | Partition-level and file-level statistics (such as Min/Max), partition pruning, column pruning, and predicate pushdown. | Supported | Supported |
Security and compliance | Storage encryption / Dynamic data masking / Row-level access control. | Supported | Supported |
Disaster recovery and backup | Table snapshots / Local backup / Zone-redundant disaster recovery. | Supported | Supported |
Cost | AliORC column-store compression and tiered storage. | Supported | Supported |
User experience
Real-time data updates for real-time services
Delta Tables support real-time writes and upserts through Stream Upload. Data becomes visible immediately. MaxCompute balances write latency with query performance through a tiered storage strategy:
Ensure real-time writes: New data writes to non-clustered buckets without sorting, ensuring low latency and high throughput.
Improve SQL query performance: The background Incremental Reclustering service asynchronously reorganizes incremental data into sorted, clustered buckets. The query engine prunes sorted base data and scans only a small amount of incremental data, balancing freshness with efficiency.
Efficient incremental data processing and analysis
MaxCompute provides advanced incremental processing features. Combine Incremental computing and Delta Live Materialized Views (preview) to build real-time data processing pipelines.
Adapt to business growth and overcome the limits of previous table formats
Dynamic bucket allocation: Append Delta Tables dynamically allocate buckets without requiring a DDL specification. As data volume grows, the Dynamic Bucketing service automatically splits or creates buckets, adapting to changes and preventing skew and fragmentation.
Schema evolution: Delta Tables support adding, deleting, modifying, and renaming columns with full backward compatibility to prevent accidental data loss.
Unified table capabilities: A single Delta Table supports INSERT INTO, UPDATE, DELETE, and MERGE INTO with sorted clustering storage — combining capabilities that previously required separate partitioned, clustered, and transactional tables.
Multi-engine support: Access Delta Tables from MaxCompute SQL, MaxFrame, Spark on MaxCompute, and open-source engines (Flink, Spark, StarRocks) through the Spark Connector and open storage APIs.
Balance performance and reliability
Delta Tables handle terabyte-to-petabyte data volumes with fast metadata operations. Queries use partition pruning, column pruning, and predicate pushdown to minimize data scans.
ACID transactions: Delta Tables use optimistic concurrency control for multi-writer concurrency. Write conflicts are detected and retried automatically.
Security and compliance: Delta Tables support storage encryption, table-level and column-level ACLs, row-level permissions, and dynamic data masking.
Backup and rollback: A versioned recycle bin mechanism lets you roll back tables to a previous healthy state after data corruption or accidental deletion.
SQL operations
DDL
Create an Append Delta Table
-- Create an Append Delta Table
CREATE TABLE <table_name> (
<col_name <data_type> [NOT NULL] [DEFAULT <default_value>] [comment <col_comment>], ...
)
[comment <table_comment>]
[RANGE CLUSTERED BY (<col_name> [, <col_name>, ...]) ]
TBLPROPERTIES (
"table.format.version"="2"
["acid.data.retain.hours"="hours"...]
)
[LIFECYCLE <days>];TBLPROPERTIES parameters:
Parameter | Required | Description | Notes |
"table.format.version"="2" | Yes | Declares the table format as Delta Table. | |
acid.data.retain.hours | No | The default value is 24. The value range is | The time range in hours for which historical data states can be queried using Time Travel.
|
acid.incremental.query.out.of.time.range.enabled | No | The default value is | If set to true, the endTimestamp specified in an incremental query can be later than the latest commit time of the table. If the endTimestamp is later than the current time, multiple queries may return different results because new data may be inserted. You can modify the value of this parameter for a table. |
Create a PK Delta Table
-- Create a PK Delta Table
CREATE TABLE <table_name> (
<col_name <data_type> [NOT NULL] [DEFAULT <default_value>] [comment <col_comment>], ...
PRIMARY KEY (<pk_col_name>[, <pk_col_name2>, ...] )
)
[comment <table_comment>]
TBLPROPERTIES (
"table.format.version"="2"
[, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...]
)
[LIFECYCLE <days>];Parameters:
PRIMARY KEY (PK): Required for PK Delta Tables. Supports one or more columns with unique value combinations. Follows standard SQL primary key syntax. Primary key columns must be NOT NULL and cannot be modified.
After you set the primary key, data is deduplicated by primary key columns. The unique constraint applies within a single partition or across a non-partitioned table.
TBLPROPERTIES parameters:
Parameter | Required | Description | Notes |
"table.format.version"="2" | Yes | Declares the table format as Delta Table. |
|
write.bucket.num | No | The default value is 16. The value range is | The number of buckets per partition or non-partitioned table, which also sets the write concurrency. Modifiable for partitioned tables (applies to new partitions); not modifiable for non-partitioned tables. Guidelines:
|
acid.data.retain.hours | No | The default value is 24. The value range is | The time range in hours for which historical data states can be queried using Time Travel.
|
acid.incremental.query.out.of.time.range.enabled | No | The default value is | If set to true, the endTimestamp specified in an incremental query can be later than the latest commit time of the table. If the endTimestamp is later than the current time, multiple queries may return different results because new data may be inserted. You can modify the value of this parameter for a table. |
acid.write.precombine.field | No | You can specify the name of one column. | If a column name is specified, the system deduplicates data based on the primary key (PK) columns and the specified column during file processing for the same commit. This ensures data uniqueness and consistency. Note If the data volume of a single commit exceeds 128 MB, multiple files are generated. This parameter does not apply to multiple files. |
acid.partial.fields.update.enable | No | When set to | Set this parameter when you create the table. You cannot modify it after the table is created. |
Notes
Item | Append Delta Table | PK Delta Table | Clustered table |
Number of buckets | No need to specify write.bucket.num. Buckets scale dynamically with data volume. | Specify bucket count in DDL. Default: 16. | / |
Data organization policy | RANGE CLUSTERED BY only (CLUSTERED BY not supported). No SORT BY needed — data is sorted by the RANGE CLUSTERED BY fields by default. | CLUSTERED BY not allowed. Hash cluster is auto-created on the primary key. | CLUSTERED BY |
Lifecycle | Must satisfy | / | / |
You cannot directly convert an existing standard table into a Delta Table.
PK Delta Tables do not support schema evolution for primary key (PK) columns.
PK Delta Tables do not currently support the JSON data type.
CREATE TABLE AS is not supported.
DML
Delta Tables support DML operations: Insert or overwrite data (INSERT INTO | INSERT OVERWRITE), UPDATE | DELETE, and MERGE INTO.
DQL
Delta Tables support general-purpose query analysis. DQL operations (SELECT).
Data import
Append Delta Tables support batch upload and stream upload. Without a primary key, Upsert and Delete are not supported.
PK Delta Tables support the Tunnel Upsert/Delete API. Upsert inserts a row if the primary key does not exist, or updates non-primary-key fields if it does.
Data organization optimization
Append Delta Tables use Range Clustering for data organization (Optimize data organization for Append Delta Tables). By default, Row_ID is the clustering key and buckets scale dynamically. After you specify a Cluster Key, background incremental reclustering maintains data order.
PK Delta Tables use Hash Clustering on the primary key for efficient writes and updates. Data organization optimization for PK Delta tables.