Git for Data

Git for Data applies familiar version-control concepts — snapshots, branches, diffs, cherry-picks, and merges — directly to database tables and databases through SQL. It enables named restore points, point-in-time recovery, isolated data experimentation, row-level change review, selective promotion, and auditable merge workflows without exporting data to external tools.

Core Concepts

Git for Data treats your database data as versionable. Instead of copying data into a separate version-control system before every experiment, migration, or release, you work directly in MatrixOne with SQL commands that mirror Git operations:

Git Concept	Git for Data Equivalent	What It Does
git tag	CREATE SNAPSHOT	Capture a named restore point at cluster, account, database, or table scope.
git log / reflog	PITR + Time Travel	Query or restore data as it existed at any point within a configurable retention window.
git branch	DATA BRANCH CREATE	Fork a table or database from the current state or a named snapshot. The branch keeps lineage so the system knows how it relates to the source.
git diff	DATA BRANCH DIFF	Compare two tables row by row and see every INSERT, DELETE, and UPDATE.
git cherry-pick	DATA BRANCH PICK	Promote specific rows by primary key, or all changes between two snapshots, instead of merging the whole difference set.
git merge	DATA BRANCH MERGE	Apply a branch’s changes into a destination, with FAIL, SKIP, or ACCEPT conflict handling.
git branch -d	DATA BRANCH DELETE	Remove a branch table or database that is no longer needed.

All of these operate through standard SQL and are subject to fine-grained privilege checks. You do not need external tools, file exports, or separate version-control repositories.

Snapshot, Time Travel, and PITR — Choosing the Right Tool

These three capabilities address different recovery and history needs. The table below helps you choose:

Capability	Best For	How It Works	Retention
Snapshot	Pre-change baselines, release gates, manual checkpoints	CREATE SNAPSHOT name FOR {CLUSTER|ACCOUNT|DATABASE|TABLE} creates a named restore point. Restore the required scope with RESTORE ... {SNAPSHOT = 'name'}.	Persistent until explicitly dropped.
Time Travel	Ad-hoc historical queries, debugging past state	SELECT ... {SNAPSHOT = 'name'} or SELECT ... {TIMESTAMP = '...'} reads data as of a past point without restoring.	Bound by snapshot lifetime and PITR window.
PITR	Continuous protection, disaster recovery, compliance	CREATE PITR name FOR ... RANGE N {'h'|'d'|'mo'|'y'} sets up an automatic rolling history window. Restore with RESTORE ... FROM PITR name 'timestamp'.	Rolling window defined by RANGE.

You can use them together: configure PITR for continuous background protection, create snapshots before major changes, and use time-travel queries for ad-hoc investigation.

Snapshot and PITR Scope Levels

Both snapshots and PITR support four scope levels, from broadest to finest:

Level	Scope	Typical Use
CLUSTER	Every account, database, and table in the cluster	Full-cluster disaster recovery
ACCOUNT	All databases and tables within one account	Tenant-level checkpoint
DATABASE	All tables within a single database	Pre-migration safety net
TABLE	A single table	Targeted protection before a risky operation

Snapshots created at a broader scope can restore individual databases or tables within that scope. PITR at a broader scope similarly covers all contained objects.

Data Branches — Lineage and DAG

How Branch Lineage Works

When you create a data branch with DATA BRANCH CREATE, MatrixOne records the parent-child relationship between source and branch. This lineage forms a directed acyclic graph (DAG): a branch can itself be branched again, producing multi-level histories with arbitrary topology.

The lineage is what enables efficient diff and merge:

• Lowest Common Ancestor (LCA): The system automatically traces the DAG to find the common ancestor between any two tables that share lineage. DIFF and MERGE then compute only the incremental changes from that ancestor — not a full table scan.

• Arbitrary DAG depth: LCA resolution works across siblings, cousins, and cross-subtree nodes, not just direct parent-child pairs.

• No-lineage fallback: Two tables without a common ancestor can still be compared or merged, but the system falls back to full data comparison without lineage-based optimization.

Table Branches vs. Database Branches

• Table branch (DATA BRANCH CREATE TABLE): Forks a single table. Use when you only need to experiment with or transform one table’s data.

• Database branch (DATA BRANCH CREATE DATABASE): Forks every table in a database at once. Use when your change spans multiple related tables and you need a consistent snapshot across all of them.

Both can be created from the current state or from a named SNAPSHOT, and both preserve lineage.

CREATE CLONE vs. DATA BRANCH CREATE

CREATE CLONE creates an independent copy but does not record branch metadata or lineage. Use CREATE CLONE when you need a standalone copy without a later branch workflow. Use DATA BRANCH CREATE when you plan to later DIFF, PICK, or MERGE — the recorded lineage helps those operations calculate changes.

Data Branch Lifecycle

A typical branch goes through these stages:

CREATE  →  Work  →  DIFF (review)  →  PICK / MERGE (promote)  →  DELETE (clean up)

1. CREATE — Isolate Your Work

-- Protect the baseline first
CREATE SNAPSHOT before_release FOR DATABASE production_db;

-- Fork a table branch from a snapshot
DATA BRANCH CREATE TABLE staging_db.customers FROM production_db.customers {SNAPSHOT = 'before_release'};

After creation, the branch is an independent copy. Changes on the branch do not affect the source, and changes on the source do not affect the branch.

2. Work — Develop, Test, Transform

Treat the branch like any other table. Run application tests, validate data transformations, or simulate production scenarios while keeping the branch schema equivalent to the table you plan to compare or update. You can test schema changes in isolation, but DIFF, PICK, and MERGE require equivalent source and destination schemas.

USE staging_db;
UPDATE customers SET tier = 'premium' WHERE lifetime_value > 10000;
DELETE FROM customers WHERE last_active < '2020-01-01';
INSERT INTO customers SELECT * FROM imported_leads WHERE qualified = true;

3. DIFF — Review Row-Level Changes

Before promoting changes, inspect exactly what differs:

-- See the full row-by-row diff
DATA BRANCH DIFF staging_db.customers AGAINST production_db.customers;

-- Get aggregated counts only
DATA BRANCH DIFF staging_db.customers AGAINST production_db.customers OUTPUT SUMMARY;

-- Limit to a subset of columns
DATA BRANCH DIFF staging_db.customers AGAINST production_db.customers COLUMNS (tier, lifetime_value);

-- Export the diff to a stage directory
DATA BRANCH DIFF staging_db.customers AGAINST production_db.customers OUTPUT FILE 'stage://reviews/branch_diff/';

Each output row includes a flag column (INSERT, DELETE, or UPDATE) and all table columns (or the COLUMNS subset you specify). OUTPUT FILE writes an SQL file for an incremental diff. If the base table is empty, it writes a CSV file instead.

The OUTPUT SUMMARY variant returns only the aggregated counts:

DATA BRANCH DIFF staging_db.customers AGAINST production_db.customers OUTPUT SUMMARY;

4. PICK or MERGE — Promote Changes

Choose between selective and full promotion:

PICK — promote specific rows by primary key, or all changes within a snapshot window:

-- Pick a single known key
DATA BRANCH PICK staging_db.customers INTO production_db.customers KEYS (1001);

-- Pick multiple composite keys
DATA BRANCH PICK staging_db.customers INTO production_db.customers KEYS (('US', 1001), ('EU', 2042));

-- Pick all changes between two snapshots
DATA BRANCH PICK staging_db.customers INTO production_db.customers BETWEEN SNAPSHOT before_release AND after_qa;

-- Pick keys from a subquery result
DATA BRANCH PICK staging_db.customers INTO production_db.customers KEYS (SELECT id FROM review_approved);

PICK requires at least one of KEYS or BETWEEN SNAPSHOT, and the source table must have an explicit primary key. Tables that only have MatrixOne’s internal row identifier are not supported by DATA BRANCH PICK.

MERGE — apply all branch changes at once:

-- Default: abort on first conflict
DATA BRANCH MERGE staging_db.customers INTO production_db.customers;

-- Skip conflicting rows, keep destination data
DATA BRANCH MERGE staging_db.customers INTO production_db.customers WHEN CONFLICT SKIP;

-- Accept source data over destination on conflict
DATA BRANCH MERGE staging_db.customers INTO production_db.customers WHEN CONFLICT ACCEPT;

Conflict Handling in MERGE and PICK

Both MERGE and PICK can encounter conflicts — rows with the same primary key modified differently in source and destination. Three strategies are available:

Strategy	Behavior	Use When
FAIL (default)	Abort with an error when a conflicting row is detected.	You need to manually review every conflict before proceeding.
SKIP	Keep the destination row as-is; discard the conflicting change from the source.	The destination is authoritative and source changes are suggestions.
ACCEPT	Overwrite the destination row with the source value.	The source branch contains the definitive version.

5. DELETE — Clean Up

After changes are promoted and verified, remove the branch:

-- Drop a branch table
DATA BRANCH DELETE TABLE staging_db.customers;

-- Drop a branch database
DATA BRANCH DELETE DATABASE staging_db;

The system enforces that the target is an active branch (recorded in branch metadata) and that the caller has the required privileges.

Privilege Model

Every Git for Data operation requires specific, operation-scoped privileges. The model enforces least privilege: you need SELECT on sources you read from and the relevant write privilege on destinations you modify.

Operation	Source Privilege	Target Privilege
DATA BRANCH CREATE TABLE	SELECT on source table	CREATE TABLE on target database
DATA BRANCH CREATE DATABASE	SELECT on source tables	CREATE DATABASE on account
DATA BRANCH DIFF	SELECT on target table	SELECT on base table
DATA BRANCH MERGE	SELECT on source table	SELECT, INSERT, UPDATE, DELETE on destination table
DATA BRANCH PICK	SELECT on source and key-subquery tables	SELECT, INSERT, UPDATE, DELETE on destination table
DATA BRANCH DELETE TABLE	N/A	DROP TABLE on target database
DATA BRANCH DELETE DATABASE	N/A	DROP DATABASE on target account

See the Data Branch Privilege Model for the full reference.

Confirmed Limitations

The following constraints apply:

• No cross-account branching for non-sys tenants. Only the sys tenant can create branches in another account with TO ACCOUNT, and cross-account creation requires a snapshot.

• System databases cannot be used as branch destinations. For example, DATA BRANCH CREATE TABLE rejects a target in mo_catalog. The sys account can, however, branch a supported cluster table from mo_catalog into an ordinary database; source support depends on the object type.

• DATA BRANCH DIFF OUTPUT AS table_name is not supported. Use OUTPUT FILE or OUTPUT SUMMARY for exported results.

• DATA BRANCH DIFF COLUMNS is incompatible with OUTPUT FILE. Choose column projection or file export, not both.

• DATA BRANCH PICK cannot run inside explicit transactions (BEGIN ... COMMIT).

• DATA BRANCH PICK rejects NULL keys from subqueries.

• DATA BRANCH PICK requires an explicit primary key. Tables that only have MatrixOne’s internal row identifier are not supported.

• Branch comparison and apply operations require equivalent schemas. If only one side has a schema change, DIFF, PICK, and MERGE reject the operation. Test schema changes in isolation, or align both schemas before comparing or applying data changes.

• Branch-protect snapshots are system managed. DATA BRANCH CREATE automatically creates internal snapshots that protect ancestor data while descendant branches exist. They are hidden from SHOW SNAPSHOTS, cannot be dropped directly, and are reclaimed when the branch subtree is deleted.

• Branch metadata is account-scoped. Branches created in one account are not visible in another.

Typical Workflows

Development and Testing

1. Create a snapshot of the production database.

2. Fork a database branch from that snapshot.

3. Run application tests and data transformations on the branch while keeping its schema equivalent to the source.

4. Review the diff to confirm only expected changes occurred.

5. Discard the branch (DATA BRANCH DELETE) when testing is complete.

Safe Data Transformations

1. Take a snapshot of the target table before starting.

2. Create a table branch from the snapshot.

3. Run the transformation (bulk UPDATE, data cleansing, reformatting) on the branch.

4. DIFF to verify the transformation produced the expected changes.

5. MERGE the branch back into the source, or PICK only the validated subset.

Selective Release Promotion

1. Multiple teams work on separate branches of the same base table.

2. Each team’s changes are reviewed independently with DIFF.

3. QA approves specific rows from each branch.

4. Use PICK with KEYS to promote only approved rows to the main table.

5. Use PICK with BETWEEN SNAPSHOT to promote all changes from a time-bounded QA window.

Incident Recovery and Audit

1. Configure PITR on critical tables with a retention window appropriate for compliance.

2. When an incident occurs, query the table {TIMESTAMP = '...'} to inspect the state before the incident.

3. If a restore is needed, use RESTORE ... FROM PITR name 'timestamp' to recover to a point before the incident.

4. For audit purposes, use snapshots taken at regular intervals to compare states and trace changes over time.

Related Pages

• CREATE SNAPSHOT — full syntax reference

• CREATE PITR — PITR configuration reference

• RESTORE … {SNAPSHOT = ‘name’} — snapshot restore reference

• RESTORE … FROM PITR — PITR restore reference

• DATA BRANCH CREATE — branch creation reference

• DATA BRANCH DIFF — diff reference with all output options

• DATA BRANCH PICK — selective promotion reference

• DATA BRANCH MERGE — merge and conflict handling reference

• DATA BRANCH DELETE — branch deletion reference

• Data Branch Privilege Model — full privilege reference

• Backup and Restore Overview — backup strategy overview

• Snapshot Rollback Tutorial — hands-on tutorial

• Efficient Clone Tutorial — multi-team development with branches