DATA BRANCH DELETE

The DATA BRANCH DELETE statement is used to delete data branches.

Description

The DATA BRANCH DELETE statement is used to delete data branches. This statement deletes the specified branch table or branch database while updating the branch metadata to mark the corresponding branch as deleted.

DATA BRANCH DELETE validates that the target is an active branch object before deleting it. Regular DROP TABLE and DROP DATABASE also mark and reclaim branch-protection metadata when the dropped objects participate in branch lineage; they do not bypass branch cleanup.

Syntax

Delete Table Branch

DATA BRANCH DELETE TABLE [database_name.]table_name

Delete Database Branch

DATA BRANCH DELETE DATABASE database_name

Arguments

Parameter Description

Parameter	Description
table_name	Name of the branch table to delete
database_name	Database name. Optional when deleting a table, required when deleting a database

Usage Notes

Permission Requirements

• User needs delete permission on the target table/database

Execution Effects

1. Delete Table Branch: Deletes the specified table and marks the table_deleted field as true in mo_catalog.mo_branch_metadata

2. Delete Database Branch: Deletes all tables in the specified database and marks all related table metadata as deleted

The table target must be an active branch table. A database target is accepted only when its objects form a deletable branch database: an empty database, a view-only or sequence-only database, or a database containing an ordinary non-branch table is rejected.

Relationship to DROP

Operation	Target validation	Branch metadata behavior
DROP TABLE / DROP DATABASE	Normal DROP rules	For objects in branch lineage, marks deletion and reclaims branch-protection metadata
DATA BRANCH DELETE TABLE	Requires an active branch table	Marks the branch table deleted and reclaims eligible protection metadata
DATA BRANCH DELETE DATABASE	Requires a deletable active branch database	Marks related branch tables deleted and reclaims eligible protection metadata

Examples

Example 1: Delete Table Branch

-- Expected-Rows: 0
CREATE DATABASE test_db;
-- Expected-Rows: 0
USE test_db;

-- Expected-Rows: 0
CREATE TABLE test_db.base_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
-- Expected-Rows: 0
INSERT INTO test_db.base_table VALUES (1, 'Alice'), (2, 'Bob');

-- Expected-Rows: 0
CREATE SNAPSHOT sp_base FOR TABLE test_db base_table;

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test_db.branch_table FROM test_db.base_table{SNAPSHOT='sp_base'};

-- Expected-Rows: 2
SHOW TABLES FROM test_db;
+-----------------+
| Tables_in_test_db |
+-----------------+
| base_table      |
| branch_table    |
+-----------------+

-- Expected-Rows: 1
SELECT table_id, table_deleted 
FROM mo_catalog.mo_branch_metadata 
WHERE table_id = (
    SELECT rel_id FROM mo_catalog.mo_tables 
    WHERE reldatabase = 'test_db' AND relname = 'branch_table'
);

-- Expected-Rows: 0
DATA BRANCH DELETE TABLE test_db.branch_table;

-- Expected-Rows: 1
SHOW TABLES FROM test_db;
+-----------------+
| Tables_in_test_db |
+-----------------+
| base_table      |
+-----------------+

-- Expected-Rows: 0
DROP SNAPSHOT sp_base;
-- Expected-Rows: 0
DROP DATABASE test_db;

Example 2: Delete Database Branch

-- Expected-Rows: 0
CREATE DATABASE source_db;
-- Expected-Rows: 0
USE source_db;

-- Expected-Rows: 0
CREATE TABLE source_db.t1 (a INT PRIMARY KEY);
-- Expected-Rows: 0
CREATE TABLE source_db.t2 (a INT PRIMARY KEY);
-- Expected-Rows: 0
INSERT INTO source_db.t1 VALUES (1), (2);
-- Expected-Rows: 0
INSERT INTO source_db.t2 VALUES (3), (4);

-- Expected-Rows: 0
DATA BRANCH CREATE DATABASE branch_db FROM source_db;

-- Expected-Rows: 1
SHOW DATABASES LIKE 'branch_db';
+--------------------+
| Database (branch_db) |
+--------------------+
| branch_db          |
+--------------------+

-- Expected-Rows: 2
SHOW TABLES FROM branch_db;
+-------------------+
| Tables_in_branch_db |
+-------------------+
| t1                |
| t2                |
+-------------------+

-- Expected-Rows: 0
DATA BRANCH DELETE DATABASE branch_db;

-- Expected-Rows: 0
SHOW DATABASES LIKE 'branch_db';

-- Expected-Rows: 0
DROP DATABASE source_db;

Example 3: Metadata Status After Branch Deletion

-- Expected-Rows: 0
CREATE DATABASE br_meta_db;
-- Expected-Rows: 0
USE br_meta_db;

-- Expected-Rows: 0
CREATE TABLE br_meta_db.base_tbl (a INT PRIMARY KEY, b VARCHAR(10));
-- Expected-Rows: 0
INSERT INTO br_meta_db.base_tbl VALUES (1, 'a'), (2, 'b');

-- Expected-Rows: 0
CREATE SNAPSHOT sp_base_tbl FOR TABLE br_meta_db base_tbl;

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE br_meta_db.branch_tbl FROM br_meta_db.base_tbl{SNAPSHOT='sp_base_tbl'};

-- Expected-Rows: 0
SET @branch_tbl_id = (
    SELECT rel_id FROM mo_catalog.mo_tables
    WHERE reldatabase = 'br_meta_db' AND relname = 'branch_tbl'
);

-- Expected-Rows: 1
SELECT table_deleted FROM mo_catalog.mo_branch_metadata 
WHERE table_id = @branch_tbl_id;
+---------------+
| table_deleted |
+---------------+
| false         |
+---------------+

-- Expected-Rows: 0
DATA BRANCH DELETE TABLE br_meta_db.branch_tbl;

-- Expected-Rows: 1
SELECT table_deleted FROM mo_catalog.mo_branch_metadata 
WHERE table_id = @branch_tbl_id;
+---------------+
| table_deleted |
+---------------+
| true          |
+---------------+

-- Expected-Rows: 0
DROP SNAPSHOT sp_base_tbl;
-- Expected-Rows: 0
DROP DATABASE br_meta_db;

Example 4: Batch Delete All Branch Tables in a Database

-- Expected-Rows: 0
CREATE DATABASE src_db;
-- Expected-Rows: 0
USE src_db;

-- Expected-Rows: 0
CREATE TABLE src_db.t1 (a INT PRIMARY KEY);
-- Expected-Rows: 0
CREATE TABLE src_db.t2 (a INT PRIMARY KEY);
-- Expected-Rows: 0
INSERT INTO src_db.t1 VALUES (1);
-- Expected-Rows: 0
INSERT INTO src_db.t2 VALUES (2);

-- Expected-Rows: 0
DATA BRANCH CREATE DATABASE dst_db FROM src_db;

-- Expected-Rows: 0
SET @dst_t1_id = (
    SELECT rel_id FROM mo_catalog.mo_tables
    WHERE reldatabase = 'dst_db' AND relname = 't1'
);
-- Expected-Rows: 0
SET @dst_t2_id = (
    SELECT rel_id FROM mo_catalog.mo_tables
    WHERE reldatabase = 'dst_db' AND relname = 't2'
);

-- Expected-Rows: 2
SELECT table_deleted FROM mo_catalog.mo_branch_metadata
WHERE table_id IN (@dst_t1_id, @dst_t2_id)
ORDER BY table_id;
+---------------+
| table_deleted |
+---------------+
| false         |
| false         |
+---------------+

-- Expected-Rows: 0
DATA BRANCH DELETE DATABASE dst_db;

-- Expected-Rows: 2
SELECT table_deleted FROM mo_catalog.mo_branch_metadata
WHERE table_id IN (@dst_t1_id, @dst_t2_id)
ORDER BY table_id;
+---------------+
| table_deleted |
+---------------+
| true          |
| true          |
+---------------+

-- Expected-Rows: 0
DROP DATABASE src_db;

Notes

1. Irreversible: The delete operation is irreversible. Data will be permanently lost after deletion. Please confirm before executing.

2. Metadata Preservation: Although data is deleted, branch metadata is preserved in the system table with the table_deleted field marked as true. The metadata table (mo_catalog.mo_branch_metadata) is only accessible by the sys tenant.

3. Cascade Deletion: When deleting a database branch, all tables in that database are deleted, and all related table metadata is updated.

4. Permission Check: Executing delete operations requires appropriate permissions. Ensure the current user has sufficient privileges.

5. Audit Trail: Since metadata is preserved, the sys tenant can track branch history by querying the mo_catalog.mo_branch_metadata table.