snapshots-and-sharing

Purpose

This document explains how MotherDuck handles database snapshots and sharing, including the underlying Differential Storage architecture that enables efficient versioning and collaboration.

Overview

MotherDuck extends DuckDB with cloud-based snapshot and sharing capabilities, allowing teams to:

• Capture point-in-time database states
• Share read-only snapshots via URLs
• Collaborate without data duplication
• Travel back to any minute-level checkpoint

Core Architecture: Differential Storage

Layered Storage Model

Instead of storing databases as monolithic .duckdb files, MotherDuck uses ordered sequences of “layers”:

Time →
[Base Layer] → [Layer 1: +100 rows] → [Layer 2: -5 rows, +50 rows] → [Active Layer]
     ↓              ↓                        ↓                            ↓
  Snapshot 1    Snapshot 2              Snapshot 3                  Current

Key characteristics:

• Each layer captures only deltas (changes) since the previous checkpoint
• Layers are immutable once created
• Shared data across snapshots is stored only once

Components

1. Snapshot Layer Files (immutable)

   • Stored on S3 or object storage
   • Each represents a checkpoint in time
   • Deduplicated across versions

2. Write-Ahead Log (WAL)

   • Tracks individual commits between checkpoints
   • Enables granular time-travel beyond checkpoint boundaries

3. Metadata Database (separate OLTP DB)

   • Tracks current snapshot state
   • Maps layers to storage locations

How Reads Work

When querying data:

1. Differential Storage intercepts the read request (FUSE driver layer)
2. Splits the read across multiple snapshot layers
3. Retrieves data from the newest layer containing each range
4. Merges results and returns to DuckDB

This enables zero-copy reads - no data duplication required.

Creating Snapshots

Automatic Snapshots

MotherDuck automatically creates snapshots every minute:

• Skips snapshot if active writes are in progress
• Ensures read-scaling connections stay synchronized
• No configuration required

Manual Snapshots

Force an immediate snapshot:

CREATE SNAPSHOT OF my_database;

Behavior:

• Waits for all active writes to complete
• Blocks new writes during snapshot creation
• Creates checkpoint immediately (doesn’t wait for 1-minute timer)

Use cases:

• Before making risky schema changes
• After completing a significant data transformation
• When you need to share the current state immediately

Sharing Databases

Creating Shares

Share a point-in-time snapshot with others:

CREATE SHARE my_share FROM my_database
  ACCESS UNRESTRICTED      -- Who can access
  VISIBILITY PUBLIC;       -- Listing visibility

-- Returns URL: md:_share/my_database/abc123xyz

Access Control Levels

Visibility Options

Note: Hidden shares require ACCESS RESTRICTED.

Update Modes

Manual Update (Default):

-- Share stays frozen at creation time
UPDATE SHARE my_share;  -- Explicitly publish new snapshot

Automatic Update:

CREATE SHARE my_share FROM my_database
  UPDATE AUTOMATIC;  -- Auto-updates when database changes

Attaching Shares

Recipients attach shared databases:

ATTACH 'md:_share/my_database/abc123xyz' AS shared_db;
SELECT * FROM shared_db.my_table;

Characteristics:

• Read-only access
• Frozen at share creation time (unless UPDATE AUTOMATIC)
• Zero-copy - references same underlying layer files

Complete Workflow Example

Capture and Share a Moment

-- 1. Sync local database to MotherDuck
ATTACH 'md:recon_v2';
COPY FROM DATABASE local_db TO md:recon_v2;

-- 2. Make changes locally
INSERT INTO synthetic_fulfillment_package ...;
UPDATE service_charge SET ...;

-- 3. Force immediate snapshot
CREATE SNAPSHOT OF recon_v2;

-- 4. Create shareable snapshot
CREATE SHARE recon_jan7_ghost_fix FROM recon_v2
  ACCESS ORGANIZATION
  VISIBILITY PUBLIC;
-- Returns: md:_share/recon_v2/abc123

-- 5. Share URL with team
-- Team members can attach and query:
ATTACH 'md:_share/recon_v2/abc123' AS bug_state;
SELECT * FROM bug_state.synthetic_fulfillment_package;

-- 6. Continue working - share stays frozen
UPDATE service_charge ...;  -- Share doesn't see this

-- 7. Optionally update share with new changes
UPDATE SHARE recon_jan7_ghost_fix;  -- Now share sees latest

Storage Efficiency

Comparison with Traditional Snapshots

Zero-Copy Benefits

• Storage: 100 snapshots ≠ 100× storage cost
• Performance: Instant snapshot creation
• Sharing: Multiple shares reference same layer files
• Cloning: Database forks are zero-copy

Practical Use Cases

Debugging and Collaboration

Scenario: You discover a bug in synthetic data generation.

-- Capture the problematic state
CREATE SNAPSHOT OF recon_db;
CREATE SHARE bug_state_so11635787 FROM recon_db;
-- Share URL with team for investigation

Benefits:

• Team sees exact state that triggered the bug
• You can continue fixing without disrupting their investigation
• No need to export/import large database files

Version Control for Data Pipelines

Scenario: Testing a new matching algorithm.

-- Before: Snapshot baseline
CREATE SNAPSHOT OF recon_db;
CREATE SHARE baseline_before_algorithm_change FROM recon_db;

-- Run new algorithm
-- ... transformation code ...

-- After: Snapshot results
CREATE SNAPSHOT OF recon_db;
CREATE SHARE after_algorithm_change FROM recon_db;

-- Team can compare both snapshots side-by-side

Release Snapshots

Scenario: Freezing data state for a report or audit.

CREATE SHARE q4_2025_reconciliation_audit FROM recon_db
  ACCESS RESTRICTED
  VISIBILITY HIDDEN;

-- Share URL only with auditors
-- Data is frozen and won't change

Key Benefits Summary

Limitations and Considerations

Cloud Dependency

• Requires internet connection for sync/sharing
• Data stored in MotherDuck’s cloud (not self-hosted)
• Local queries still work offline

Cost

• Free tier available
• Usage-based pricing for larger datasets
• Storage costs scale with total data + delta history

Access Control

• Organization-level or public sharing only
• No fine-grained per-user permissions within a share
• Hidden shares require restricted access

Alternatives Comparison

Sources

1. Differential Storage Architecture
2. CREATE SNAPSHOT Documentation
3. CREATE SHARE Documentation
4. Sharing Overview
5. Managing Shares
6. Updating Shares
7. MotherDuck Sharing Announcement

Last Updated

January 7, 2026