Delta Live Materialized View (DLMV) quickstart

Set the engine version (required for each execution)

The DLMV feature requires a specific SQL engine version. Before executing any DLMV-related SQL statements, you must run the following command:

SET odps.task.major.version = sql_flighting_dlmv;

How to verify the version is active: After running the set statement, if subsequent SQL statements execute without an error such as ODPS-0123XXX, the version is active. If an error indicates that the version is not supported or the feature is not enabled, submit a ticket to contact us.

Step 1: Create a source table

The source table for a DLMV must be a Delta Table (a transactional table) with Change Data Capture (CDC) enabled and a PRIMARY KEY defined. The DLMV uses the primary key to locate the target rows for incremental changes.

Run the following SQL statement to create the source table.

-- Create the source table.
CREATE TABLE IF NOT EXISTS t_department (
  dept_id     BIGINT NOT NULL PRIMARY KEY,
  name        STRING,
  description STRING
)
TBLPROPERTIES (
  'transactional'                      = 'true',
  'acid.cdc.mode.enable'               = 'true',
  'acid.cdc.build.async'               = 'false',
  'cdc.insert.into.passthrough.enable' = 'true'
)
LIFECYCLE 10;

The following table describes the key properties.

Step 2: Write initial data

INSERT OVERWRITE TABLE t_department
SELECT * FROM (
    VALUES
        (1001, 'HR Department', 'Human Resources'),
        (1002, 'IT Department', 'Information Technology'),
        (1003, 'Finance Department', 'Financial Management')
) AS t (dept_id, name, description);

-- Run the following statement to verify that the data has been written.
SELECT * FROM t_department;

-- The following result is returned:
+------------+--------------------+------------------------+
| dept_id    | name               | description            |
+------------+--------------------+------------------------+
| 1003       | Finance Department | Financial Management   |
| 1001       | HR Department      | Human Resources        |
| 1002       | IT Department      | Information Technology |
+------------+--------------------+------------------------+

Step 3: Create a Delta Live Materialized View

Create a DLMV based on the source table and declare the incremental refresh mode.

In this example, the SQL logic for the DLMV is SELECT * FROM t_department. Because the system can automatically deduce that the primary key is dept_id from the source table, you do not need to explicitly declare a PRIMARY KEY.

SET odps.task.major.version = sql_flighting_dlmv;

CREATE MATERIALIZED VIEW IF NOT EXISTS dlmv_department
LIFECYCLE 10
TBLPROPERTIES (
  'refresh_mode'         = 'incremental',
  'refresh_job_settings' = 'set odps.task.major.version=sql_flighting_dlmv;'
)
AS SELECT * FROM t_department;

The following table describes the key properties.

Step 4: Verify the initial data

After creation, the DLMV automatically performs an initial full refresh to populate the view. Run the following statement to view the data in the DLMV.

SELECT * FROM dlmv_department;

-- The following result is returned:
+------------+--------------------+------------------------+
| dept_id    | name               | description            |
+------------+--------------------+------------------------+
| 1003       | Finance Department | Financial Management   |
| 1001       | HR Department      | Human Resources        |
| 1002       | IT Department      | Information Technology |
+------------+--------------------+------------------------+

Step 5: Simulate incremental changes

Execute INSERT, UPDATE, and DELETE operations on the source table to simulate data changes in a real-world workload.

SET odps.task.major.version = sql_flighting_dlmv;

-- Insert a new record.
INSERT INTO t_department VALUES (1004, 'Marketing', 'Marketing Department');

-- Update a record.
UPDATE t_department SET description = 'HR and Admin' WHERE dept_id = 1001;

-- Delete a record.
DELETE FROM t_department WHERE dept_id = 1003;

Step 6: Trigger an incremental refresh

Run the following statement to manually trigger an incremental refresh. The system processes only the incremental changes from Step 5, rather than recomputing the entire table.

SET odps.task.major.version = sql_flighting_dlmv;

ALTER MATERIALIZED VIEW dlmv_department REBUILD;

Step 7: Verify the refresh result

SELECT * FROM dlmv_department;

-- The following result is returned:
+------------+---------------+------------------------+
| dept_id    | name          | description            |
+------------+---------------+------------------------+
| 1004       | Marketing     | Marketing Department   |  -- New record added.
| 1001       | HR Department | HR and Admin           |  -- The description is updated from 'Human Resources' to 'HR and Admin'.
| 1002       | IT Department | Information Technology |  -- The record with dept_id = 1003 is deleted.
+------------+---------------+------------------------+

Step 8: View the refresh history

Use the following statement to view the refresh history of the DLMV and check the status and duration of each refresh.

SELECT * FROM delta_live_mv_refresh_history('dlmv_department');

-- Sample result:
+--------------+-------------+------+--------------------+------------------+-------------+---------------------+-------+-----------------+--------------+---------------+---------------+-----------------+----------------+--------------------------+---------------------+
| project_name | schema_name | name | refresh_start_time | refresh_end_time | instance_id | duration_in_seconds | state | refresh_trigger | refresh_mode | error_message | source_tables | numinsertedrows | numdeletedrows | refresh_mode_reason_code | refresh_mode_reason |
+--------------+-------------+------+--------------------+------------------+-------------+---------------------+-------+-----------------+--------------+---------------+---------------+-----------------+----------------+--------------------------+---------------------+
| *testproject | default     | dlmv_department | 2026-07-01T17:19:07.37 | 2026-07-01T17:19:55.108 | 20260701091907370gkrrqdpv0gg | 47                  | TERMINATED | MANUAL          | INCREMENTAL  |               | [{"table_name":"*test_project.default.t_department","table_id":"757f8****fe012c","lsn":"0000000000000006","tx_id":"182****1794"}] | 2               | 2              | NULL                     | NULL                |
+--------------+-------------+------+--------------------+------------------+-------------+---------------------+-------+-----------------+--------------+---------------+---------------+-----------------+----------------+--------------------------+---------------------+

The following table describes the returned fields.

Step 9: Clean up test resources

After you complete the proof-of-concept (POC) test, run the following statements to clean up the resources and avoid unnecessary storage fees.

-- Drop the DLMV first. This is required before dropping the source table.
DROP MATERIALIZED VIEW IF EXISTS dlmv_department;

-- Then, drop the source table.
DROP TABLE IF EXISTS t_department;

You must drop the DLMV before you drop the source table. If you drop the source table first, the DLMV may be left in an inconsistent state.

Advanced: Create a partitioned DLMV

A partitioned DLMV manages incremental data in batches, such as by day, making it ideal for near-real-time data warehousing scenarios in a production environment. The following example uses the source table t_department to demonstrate how to create and refresh a partitioned DLMV.

SET odps.task.major.version = sql_flighting_dlmv;

CREATE MATERIALIZED VIEW IF NOT EXISTS part_dlmv_department
PRIMARY KEY(dept_id)        -- The PK must be explicitly declared (cannot be inferred in BUILD DEFERRED mode).
LIFECYCLE 10
BUILD DEFERRED              -- Creates only the table schema without immediately refreshing data.
PARTITIONED BY (pt)         -- Declares the partition column.
TBLPROPERTIES (
  'refresh_mode'         = 'incremental',
  'refresh_job_settings' = 'set odps.task.major.version=sql_flighting_dlmv;'
)
AS SELECT *, get_setting('odps.custom.setting.department.pt') AS pt FROM t_department;

SET odps.task.major.version = sql_flighting_dlmv;

-- Set the partition value for this refresh.
SET odps.custom.setting.department.pt = 20260526;

-- Refresh the specified partition.
ALTER MATERIALIZED VIEW part_dlmv_department REBUILD PARTITION(pt = get_setting('odps.custom.setting.department.pt'));

SELECT * FROM part_dlmv_department WHERE pt = '20260526';

-- The following result is returned:
-- All current data from the source table is expected, with each row containing the partition column pt = 20260526.
+------------+---------------+------------------------+----------+
| dept_id    | name          | description            | pt       |
+------------+---------------+------------------------+----------+
| 1004       | Marketing     | Marketing Department   | 20260526 |
| 1001       | HR Department | HR and Admin           | 20260526 |
| 1002       | IT Department | Information Technology | 20260526 |
+------------+---------------+------------------------+----------+

DROP MATERIALIZED VIEW IF EXISTS part_dlmv_department;

SQL error: Version not supported

Symptom: An error such as ODPS-0123xxx is returned, or a message indicates that the feature is not supported.

Solution:

• Make sure that the SQL statement starts with set odps.task.major.version = sql_flighting_dlmv;.

• Ensure that the DLMV feature is enabled for your account.

Refresh error: CDC data has expired

Symptom: A REBUILD operation fails with an error indicating that the incremental query range exceeds the retention period.

Cause: The time interval between two refresh operations exceeds the CDC data retention period of the source table, which is 24 hours by default.

Solution: Increase the CDC data retention period for the source table.

ALTER TABLE t_department SET TBLPROPERTIES(
  'acid.data.retain.hours' = '168',
  'cdc.data.retain.hours'  = '168'
);

The acid.data.retain.hours parameter, which specifies the table data retention period, must be greater than or equal to the cdc.data.retain.hours parameter, which specifies the CDC data retention period. Both parameters have a maximum value of 168 hours (7 days).

Creation error: Primary key required

Symptom: An error occurs when you create a DLMV, indicating that a primary key is required.

Cause: A DLMV must have a primary key. When the system cannot infer the primary key from the SQL logic, you must declare it explicitly.

Solution: Add a PRIMARY KEY declaration in the CREATE MATERIALIZED VIEW statement.

CREATE MATERIALIZED VIEW IF NOT EXISTS dlmv_example
PRIMARY KEY(your_key_column)   -- Explicitly declare the primary key.
TBLPROPERTIES ('refresh_mode' = 'incremental', ...)
AS SELECT ...;

For more information about primary key inference rules, see Delta Live Materialized Views (Delta Live MV).