Git for Data: Data Branch Management¶
In daily data development, you must have encountered scenarios like these:
The risk control team needs to modify rule tables while the operations team simultaneously adjusts campaign data — both sides worry about breaking each other’s data
The test environment needs an exact copy of production data, but copying TB-scale tables is slow and expensive
After adjusting data metrics, you want to know “exactly which rows changed,” but can only rely on manual comparison
The traditional approach is to “make a copy first” — duplicate databases, duplicate tables, run scripts. But copying is slow, costly, and most critically: it’s hard to articulate what changed, and even harder to safely bring those changes back.
MatrixOne’s Data Branch feature turns data change management into an engineering workflow similar to Git. You can use SQL to: create branches, view diffs, merge changes, and handle conflicts — managing data just like managing code.
This tutorial will guide you from scratch through a complete hands-on scenario to learn all the core operations of Data Branch.
Version Requirement: The Data Branch feature is available in MatrixOne v3.0 and above.
Core Concepts¶
Before getting started, understand these 5 key actions:
Action |
SQL Syntax |
Git Analogy |
Purpose |
|---|---|---|---|
Snapshot |
|
|
Mark a point-in-time for data as a safety anchor |
Create Branch |
|
|
Create an independent experimental table from the main table/snapshot |
View Diff |
|
|
Compare data differences between two branches row by row |
Merge |
|
|
Merge changes from a branch back into the target table |
Delete Branch |
|
|
Clean up branches no longer needed, retaining audit metadata |
Similar to how Git manages code, the typical Data Branch workflow is:
Main Table → Snapshot → Create Branch → Independent Modifications → Diff Review → Merge Back to Main Table
Note
Data Branch is based on a Copy-on-Write mechanism. Creating a branch does not actually copy data — new storage space is only allocated when modifications are made. Therefore, creating a branch is very fast and takes almost no extra storage.
Prerequisites¶
Before you begin, make sure:
You have a MatrixOne v3.0 or above instance and can connect to it
You have installed the MySQL client and can connect to MatrixOne
Connect to MatrixOne:
mysql -h 127.0.0.1 -P 6001 -u root -p111
Hands-on Scenario: Parallel Modifications on an Orders Table¶
We simulate a real-world scenario: a main orders table where the risk control team and the operations team need to make modifications simultaneously without interfering with each other, and finally merge their respective changes back into the main table.
Step 1: Prepare the Main Table Data¶
-- Create demo database
DROP DATABASE IF EXISTS demo_branch;
CREATE DATABASE demo_branch;
USE demo_branch;
-- Create the main orders table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer VARCHAR(20),
amount DECIMAL(10,2),
risk_flag TINYINT DEFAULT 0,
promo_tag VARCHAR(20)
);
-- Insert initial data
INSERT INTO orders VALUES
(1001, 'Alice', 99.90, 0, NULL),
(1002, 'Bob', 199.00, 0, NULL),
(1003, 'Charlie', 10.00, 0, NULL),
(1004, 'Diana', 350.00, 0, NULL),
(1005, 'Eve', 75.50, 0, NULL);
-- Verify data
SELECT * FROM orders ORDER BY order_id;
Result:
+----------+----------+--------+-----------+-----------+
| order_id | customer | amount | risk_flag | promo_tag |
+----------+----------+--------+-----------+-----------+
| 1001 | Alice | 99.90 | 0 | NULL |
| 1002 | Bob | 199.00 | 0 | NULL |
| 1003 | Charlie | 10.00 | 0 | NULL |
| 1004 | Diana | 350.00 | 0 | NULL |
| 1005 | Eve | 75.50 | 0 | NULL |
+----------+----------+--------+-----------+-----------+
Step 2: Take a Snapshot to Establish a Safety Anchor¶
Before any modifications, take a snapshot of the main table first. This is your “safety net” — no matter what happens next, you can always return to this point.
CREATE SNAPSHOT sp_orders_v1 FOR TABLE demo_branch orders;
Step 3: Create Two Branches¶
Create two independent branch tables from the main table, one for each team:
-- Risk control branch: for flagging high-risk orders
DATA BRANCH CREATE TABLE orders_risk FROM orders;
-- Operations branch: for adding campaign tags and adjusting prices
DATA BRANCH CREATE TABLE orders_promo FROM orders;
At this point, all three tables have identical data. Let’s verify:
SELECT * FROM orders_risk ORDER BY order_id;
SELECT * FROM orders_promo ORDER BY order_id;
Both queries return the same results as the main table.
Note
You can also create a branch from a snapshot: DATA BRANCH CREATE TABLE orders_risk FROM orders{SNAPSHOT='sp_orders_v1'};. This way, even if the main table was modified before creating the branch, the branch is still based on the data at the snapshot point.
Step 4: Independent Modifications on Two Branches¶
Now both teams work on their own branches independently, without interfering with each other.
Risk control team operates on orders_risk:
-- Flag Bob's order as high risk
UPDATE orders_risk SET risk_flag = 1 WHERE order_id = 1002;
-- Delete Charlie's abnormally small order
DELETE FROM orders_risk WHERE order_id = 1003;
-- Add a new order that needs review
INSERT INTO orders_risk VALUES (1006, 'Frank', 500.00, 1, NULL);
Operations team operates on orders_promo:
-- Add campaign tags to Alice's and Bob's orders, and apply a 10% discount
UPDATE orders_promo SET promo_tag = 'summer_sale', amount = amount * 0.9
WHERE order_id IN (1001, 1002);
-- Add a new campaign order
INSERT INTO orders_promo VALUES (1007, 'Grace', 39.90, 0, 'summer_sale');
At this point, the main table is completely unaffected:
SELECT * FROM orders ORDER BY order_id;
-- Still the original 5 rows, with no changes whatsoever
Step 5: Use DIFF to View Differences¶
Before merging, let’s see what each branch has changed relative to the main table.
View differences between the risk control branch and the main table:
DATA BRANCH DIFF orders_risk AGAINST orders;
Result:
+----------------------------+--------+----------+----------+--------+-----------+-----------+
| diff orders_risk against orders | flag | order_id | customer | amount | risk_flag | promo_tag |
+----------------------------+--------+----------+----------+--------+-----------+-----------+
| orders_risk | UPDATE | 1002 | Bob | 199.00 | 1 | NULL |
| orders_risk | DELETE | 1003 | Charlie | 10.00 | 0 | NULL |
| orders_risk | INSERT | 1006 | Frank | 500.00 | 1 | NULL |
+----------------------------+--------+----------+----------+--------+-----------+-----------+
You can clearly see: 1 row updated, 1 row deleted, 1 row inserted.
View differences between the operations branch and the main table:
DATA BRANCH DIFF orders_promo AGAINST orders;
Result:
+-----------------------------+--------+----------+----------+--------+-----------+-------------+
| diff orders_promo against orders | flag | order_id | customer | amount | risk_flag | promo_tag |
+-----------------------------+--------+----------+----------+--------+-----------+-------------+
| orders_promo | UPDATE | 1001 | Alice | 89.91 | 0 | summer_sale |
| orders_promo | UPDATE | 1002 | Bob | 179.10 | 0 | summer_sale |
| orders_promo | INSERT | 1007 | Grace | 39.90 | 0 | summer_sale |
+-----------------------------+--------+----------+----------+--------+-----------+-------------+
2 rows updated, 1 row inserted.
You can also directly compare the differences between two branches:
-- DATA BRANCH DIFF orders_risk AGAINST orders_promo;
This shows all differing rows between the two branches, helping you anticipate potential conflicts before merging.
Tip
For large tables, you can first use OUTPUT COUNT to understand the scale of differences:
-- DATA BRANCH DIFF orders_risk AGAINST orders OUTPUT COUNT;
You can also use OUTPUT LIMIT 10 to view only the first 10 rows of differences.
Export differences as a patch file:
If you need to bring changes to another environment (e.g., syncing from staging to production), you can export the DIFF results as a file before merging:
-- Export to local directory (execute before merge to ensure the patch reflects the original branch changes)
-- DATA BRANCH DIFF orders_risk AGAINST orders OUTPUT FILE '/tmp/diff_output/';
The system will generate a .sql file (for incremental scenarios) or a .csv file (for full scenarios), and tell you the file path and usage instructions.
Replay the patch in the target environment:
# Replay SQL patch
mysql -h <target_host> -P 6001 -u root -p111 demo_branch < /tmp/diff_output/diff_xxx.sql
You can also export to object storage (via Stage):
-- Create a Stage pointing to S3
-- CREATE STAGE my_stage URL = 's3://my-bucket/diffs/?region=us-east-1&access_key_id=<ak>&secret_access_key=<sk>';
-- Export to Stage
-- DATA BRANCH DIFF orders_risk AGAINST orders OUTPUT FILE 'stage://my_stage/';
Step 6: Merge Branches into the Main Table¶
First, merge the risk control branch (no conflict scenario):
DATA BRANCH MERGE orders_risk INTO orders;
Verify the main table:
SELECT * FROM orders ORDER BY order_id;
Result:
+----------+----------+--------+-----------+-----------+
| order_id | customer | amount | risk_flag | promo_tag |
+----------+----------+--------+-----------+-----------+
| 1001 | Alice | 99.90 | 0 | NULL |
| 1002 | Bob | 199.00 | 1 | NULL |
| 1004 | Diana | 350.00 | 0 | NULL |
| 1005 | Eve | 75.50 | 0 | NULL |
| 1006 | Frank | 500.00 | 1 | NULL |
+----------+----------+--------+-----------+-----------+
The risk control modifications are now in effect: Bob is flagged as high risk, Charlie’s order is deleted, and Frank’s order is added.
Step 7: Handle Merge Conflicts¶
Now merge the operations branch. Note that the operations branch also modified order_id = 1002 (Bob’s order), while the risk control branch has already merged its modification to the same row — this creates a conflict.
Default behavior — error on conflict:
-- DATA BRANCH MERGE orders_promo INTO orders;
-- ERROR: conflict on pk(1002)
The system tells you which rows have conflicts and will not silently overwrite data.
Three conflict resolution strategies:
Strategy |
Syntax |
Behavior |
Use Case |
|---|---|---|---|
FAIL |
Default |
Error immediately on conflict |
Scenarios requiring manual review |
SKIP |
|
Skip conflicting rows, keep main table data |
Main table data takes priority |
ACCEPT |
|
Overwrite main table with branch data |
Branch data takes priority |
In this scenario, the risk control flag is more important than the operations discount, so we choose SKIP (keep the risk control modifications in the main table):
DATA BRANCH MERGE orders_promo INTO orders WHEN CONFLICT SKIP;
Verify the final result:
SELECT * FROM orders ORDER BY order_id;
Result:
+----------+----------+--------+-----------+-------------+
| order_id | customer | amount | risk_flag | promo_tag |
+----------+----------+--------+-----------+-------------+
| 1001 | Alice | 89.91 | 0 | summer_sale |
| 1002 | Bob | 199.00 | 1 | NULL |
| 1004 | Diana | 350.00 | 0 | NULL |
| 1005 | Eve | 75.50 | 0 | NULL |
| 1006 | Frank | 500.00 | 1 | NULL |
| 1007 | Grace | 39.90 | 0 | summer_sale |
+----------+----------+--------+-----------+-------------+
Alice’s order: The operations discount and tag took effect (no conflict)
Bob’s order: The risk control
risk_flag = 1was preserved (conflict was SKIPped)Grace’s order: The new order added by operations was successfully merged
Step 8: Delete Branches¶
After branches are no longer needed, clean them up promptly:
DATA BRANCH DELETE TABLE orders_risk;
DATA BRANCH DELETE TABLE orders_promo;
Unlike a regular DROP TABLE, DATA BRANCH DELETE retains metadata records in the system table mo_catalog.mo_branch_metadata (marked as deleted), making it convenient for subsequent audit tracking.
Step 9: Rollback (If Needed)¶
If issues are discovered after merging, you can use the snapshot to return to the initial state:
RESTORE ACCOUNT sys DATABASE demo_branch TABLE orders FROM SNAPSHOT sp_orders_v1;
This is the value of snapshots — turning “rollback” from a high-risk operation into a routine action.
Clean Up the Environment¶
DROP SNAPSHOT sp_orders_v1;
DROP DATABASE demo_branch;
Diff and Merge Scenarios¶
The standard workflow above covers the common use case of parallel team branches. This section explores edge cases around DIFF verification and MERGE that are equally important for robust data management.
Surgical Repair with DIFF¶
Snapshots serve as reliable baselines. When you accidentally modify rows on the original table, you can use a branch created from the snapshot as an untouched reference. Running DIFF between the branch (snapshot baseline) and the modified original reveals exactly which rows changed. You can then surgically revert those rows and re-run DIFF to confirm the data is fully restored.
Key pattern:
Create a snapshot before risky operations.
Create a branch from that snapshot as a pristine copy.
If the origin is accidentally modified, DIFF the branch against the origin to see the damage.
Revert individual rows to their snapshot values.
Re-run DIFF to verify zero differences – the repair is complete.
Snapshot-Based MERGE¶
Merging a snapshot source into the current table is a no-op when the snapshot has no source-side changes. This is safe to run as a sanity check: current-side data is preserved unchanged.
When a branch has independent changes (non-overlapping with the origin), merging it back into the original applies those changes while leaving the origin’s untouched rows intact.
The following example demonstrates both surgical repair and branch merge:
DROP DATABASE IF EXISTS demo_diff;
CREATE DATABASE demo_diff;
USE demo_diff;
CREATE TABLE inventory (
id INT PRIMARY KEY,
item VARCHAR(50),
qty INT,
status VARCHAR(20)
);
INSERT INTO inventory VALUES
(1, 'widget', 100, 'active'),
(2, 'gadget', 200, 'active'),
(3, 'gizmo', 300, 'active'),
(4, 'tool', 400, 'active');
-- Snapshot as safety baseline
CREATE SNAPSHOT sp_baseline FOR TABLE demo_diff inventory;
-- Branch from snapshot as an untouched reference
DATA BRANCH CREATE TABLE inventory_branch FROM inventory{SNAPSHOT='sp_baseline'};
-- Accident: modify origin on two rows
UPDATE inventory SET qty = 9999 WHERE id IN (1, 2);
-- DIFF: branch (snapshot baseline) vs origin — reveals the two changed rows
DATA BRANCH DIFF inventory_branch AGAINST inventory;
-- Surgical repair: revert each row to its snapshot value
UPDATE inventory SET qty = 100 WHERE id = 1;
UPDATE inventory SET qty = 200 WHERE id = 2;
-- DIFF again: values restored, no remaining differences
DATA BRANCH DIFF inventory_branch AGAINST inventory;
-- Independent change on branch (does not overlap with origin modifications)
UPDATE inventory_branch SET status = 'reviewed' WHERE id = 4;
-- Diff before merge: branch has one new change
DATA BRANCH DIFF inventory_branch AGAINST inventory;
-- Merge branch changes into origin
DATA BRANCH MERGE inventory_branch INTO inventory;
-- Verify: branch change applied, origin data preserved
SELECT * FROM inventory ORDER BY id;
DROP SNAPSHOT sp_baseline;
DROP DATABASE demo_diff;
Advanced Usage¶
Database-Level Branches¶
In addition to table-level branches, you can also create branches for an entire database, copying all tables at once:
-- DATA BRANCH CREATE DATABASE dev_db FROM prod_db;
-- Freely modify within dev_db without affecting prod_db
-- Merge back after modifications are complete
Creating Branches from Snapshots¶
Create a branch from a specific historical point in time, suitable for “going back to yesterday’s data for analysis”:
-- CREATE SNAPSHOT sp_yesterday FOR TABLE mydb mytable;
-- ... time passes, data changes ...
-- DATA BRANCH CREATE TABLE mytable_analysis FROM mytable{SNAPSHOT='sp_yesterday'};
Multi-Level Branches¶
Branches can create further branches, forming a multi-level structure:
-- DATA BRANCH CREATE TABLE branch_v1 FROM main_table;
-- Modify on branch_v1...
-- DATA BRANCH CREATE TABLE branch_v2 FROM branch_v1;
-- Continue modifying on branch_v2...
Best Practices¶
Snapshot before operating: Before any batch modification,
CREATE SNAPSHOTfirst to give yourself a way backTables should strongly have primary keys: DIFF and MERGE rely on primary keys to locate rows. Although tables without primary keys are supported (the system uses internal hidden primary keys), results are more controllable and conflict identification is more precise with primary keys
DIFF before merging: Use
DATA BRANCH DIFF ... OUTPUT COUNTto understand the scale of changes and avoid blind mergingUnified naming conventions: Branch tables and snapshots should include purpose and date, such as
orders_risk_20260226,sp_orders_v1Clean up branches promptly:
DATA BRANCH DELETEbranches that are no longer needed to keep the environment tidyAgree on conflict strategies in advance: Teams should agree on FAIL/SKIP/ACCEPT usage rules beforehand, rather than relying on verbal communication
Syntax Quick Reference¶
Operation |
Syntax |
|---|---|
Create table branch |
|
Create database branch |
|
Create from snapshot |
|
View diff |
|
Diff count |
|
Diff with row limit |
|
Export diff file |
|
Merge (default error) |
|
Merge (skip conflicts) |
|
Merge (accept overwrite) |
|
Delete table branch |
|
Delete database branch |
|