MaxCompute:Delta Live Materialized View (Delta Live MV) (Invitational Preview)

How it works

Delta Live MV reads Change Data Capture (CDC) events from source tables, computes the incremental diff using the view's SQL definition, and merges the result into the materialized view. When no source data has changed, the refresh job skips execution — no compute is consumed.

Key characteristics:

• Declarative SQL — define the logic once; the engine handles both incremental and full computation automatically.

• Fully managed — auto-refresh, scheduling, and data warehouse layering are handled by MaxCompute.

• Cost-aware — auto-refresh skips the SQL job when no incremental data is detected, so idle time costs nothing.

Use cases

• Near-real-time data warehouse — evolve from T+1 batch pipelines to minute-level latency without rewriting your SQL logic.

• Unified incremental and full processing — run near-real-time incremental computation for today's partitions while optionally backfilling historical partitions for archival or data correction.

Prerequisites

Before you begin, ensure that you have:

• A MaxCompute project. See Create a MaxCompute project.

• Change Data Capture (CDC) enabled on the source table. Supported source types:

  • A Delta Table with the CDC feature explicitly enabled.

  • Another Delta Live MV (CDC is enabled by default on Delta Live MVs).

Supported SQL operators

Delta Live MV evaluates whether each SQL operator supports incremental computation at view creation time. Operators that do not support incremental computation trigger a full refresh instead.

Delta Live MVs cannot contain non-deterministic computations (for example, the RAND function) or user-defined functions (UDFs).

Limitations

• Delta Live MVs cannot be created as clustered tables.

• The enable_auto_substitute parameter cannot be set to true. Because Delta Live MV is asynchronous, its data may not reflect the latest version of the base table, which conflicts with query substitution.

Create a Delta Live MV

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [<project_name>.]<mv_name>
[LIFECYCLE <days>]
[BUILD DEFERRED]
[(<col_name> [COMMENT <col_comment>], ...)]
[DISABLE REWRITE]
[COMMENT <table_comment>]
[PARTITIONED ON/BY (<col_name> [, <col_name>, ...])]
[REFRESH EVERY <num> MINUTES|HOURS|DAYS]
TBLPROPERTIES (
  "refresh_mode" = "incremental"
  [, "enable_auto_refresh" = "true"]
  [, "refresh_cron" = "<cron_expression>"]
  [, "refresh_job_settings" = "<session_flags>"]
)
AS <select_statement>;

CREATE MATERIALIZED VIEW IF NOT EXISTS mv1
REFRESH EVERY 5 MINUTES
TBLPROPERTIES (
  "enable_auto_refresh" = "true",
  "refresh_mode" = "incremental"
)
AS
SELECT name, COUNT(*) FROM source GROUP BY name;

CREATE MATERIALIZED VIEW IF NOT EXISTS part_dlmv_department
PRIMARY KEY (dept_id)
LIFECYCLE 10
BUILD DEFERRED
PARTITIONED BY (pt)
TBLPROPERTIES (
  "refresh_mode" = "incremental",
  "refresh_job_settings" = "set odps.sql.split.size=128;set odps.sql.reshuffle.dynamicpt=false;"
)
AS SELECT *, get_setting('odps.custom.setting.department.pt') AS pt FROM t_department;

-- Create the Delta Live MV (schema only)
CREATE MATERIALIZED VIEW dlmv_pt
PRIMARY KEY (value)
BUILD DEFERRED
PARTITIONED BY (ds)
TBLPROPERTIES (
  "refresh_mode" = "incremental",
  "enable_auto_refresh" = "true"
)
AS SELECT value, AVG(value2), ds FROM dlmv_pt_src GROUP BY value, ds;

-- Refresh a single partition
ALTER MATERIALIZED VIEW dlmv_pt REBUILD PARTITION (ds='20250730');

-- Create the Delta Live MV
CREATE MATERIALIZED VIEW mv1
BUILD DEFERRED
PARTITIONED BY (ds)
REFRESH EVERY 5 MINUTES
TBLPROPERTIES (
  "enable_auto_refresh" = "true",
  "refresh_mode" = "incremental"
)
AS
SELECT A.* FROM A JOIN B ON A.c1 = B.c1
  AND A.ds = get_setting('odps.custom.setting.bizdate.a')
  AND B.ds = get_setting('odps.custom.setting.bizdate.b');

-- Refresh logic (DataWorks scheduling replaces ${biz_date} and ${yesterday} automatically)
SET odps.custom.setting.bizdate.a = ${biz_date};
SET odps.custom.setting.bizdate.b = ${yesterday};
ALTER MATERIALIZED VIEW mv1 REBUILD PARTITION (ds=${biz_date});

Manage a Delta Live MV

DROP MATERIALIZED VIEW [IF EXISTS] [<project_name>.]<mv_name>;

ALTER MATERIALIZED VIEW [<project_name>.]<mv_name>
  REBUILD [PARTITION (<ds> = max_pt(<table_name>), <expression1>, ...)];

ALTER MATERIALIZED VIEW <mv_name> SET TBLPROPERTIES ("enable_auto_refresh" = "false");

ALTER MATERIALIZED VIEW <mv_name> SET TBLPROPERTIES ("enable_auto_refresh" = "true");

ALTER MATERIALIZED VIEW <mv_name>
SET TBLPROPERTIES ("refresh_interval_minutes" = "<value>");

View refresh history and data changes

SHOW HISTORY FOR TABLE <mv_name>;

ObjectType  ObjectId                          ObjectName      VERSION(LSN)        Time                  Operation
TABLE       d95ec7015e8b432e8e0092d01da962a9  incremental_mv  0000000000000001    2024-08-18 21:06:32   CREATE
TABLE       d95ec7015e8b432e8e0092d01da962a9  incremental_mv  0000000000000002    2024-08-18 21:11:13   UPDATE

SELECT * FROM Delta_Live_MV_Refresh_History(
  ['<project_name>', '<schema_name>',] '<table_name>'
);

Billing

Delta Live MV billing covers both compute and storage. The rules align with standard materialized view billing.

Compute fees

Compute fees apply only when a refresh job actually runs — either during creation or during a scheduled or manual refresh. The fees follow the same rules as standard SQL jobs.

Auto-refresh skips the SQL job entirely when MaxCompute detects no incremental data changes in the source tables, incurring no compute fee. To track auto-refresh jobs, compute resource usage, and associated costs, place Delta Live MVs in a dedicated project.

Storage fees

Delta Live MVs are billed for storage the same way as standard materialized views and regular tables. Two additional storage factors apply:

• State tables — for certain SQL operators, the incremental computation algorithm maintains an internal state table. This state table consumes additional storage.

• CDC and time travel storage — Delta Live MVs require incremental CDC logs and time travel data, similar to standard Delta Tables.