Dives: Composable visualizations built by your favorite agentsRead the blog

Skip to main content

Querying historical data with time travel

MotherDuck's snapshot system automatically captures your database state whenever you insert, delete, or update rows in a table, or create a new table. This means you can query your database as it existed at any point within your retention window: this is called time travel, though there is no flux capacitor involved. Unlike the traditional backup strategy of copy-paste and restore workflows, time travel lets you read historical data directly alongside your current data without modifying anything.

This guide covers practical patterns for querying historical database states:

Prerequisites

Time travel requires a paid plan with snapshot_retention_days > 0. See snapshot features per plan for details.

Try it yourself: sample data setup​

The examples in this guide all use the same shop_db database. Run the following to create it and follow along.

CREATE DATABASE IF NOT EXISTS shop_db;
USE shop_db;

-- Customers table
CREATE OR REPLACE TABLE customers AS
SELECT * FROM (VALUES
    (1, 'Alice Johnson',  'alice@example.com',   'US-West',  '2025-11-01'::DATE),
    (2, 'Bob Smith',      'bob@example.com',     'US-East',  '2025-11-15'::DATE),
    (3, 'Carol Williams', 'carol@example.com',   'EU-West',  '2025-12-01'::DATE)
) AS t(customer_id, name, email, region, created_at);

-- Orders table
CREATE OR REPLACE TABLE orders AS
SELECT * FROM (VALUES
    (101, 1, 250.00,  '2026-01-15'::DATE, 'completed'),
    (102, 2, 89.99,   '2026-01-16'::DATE, 'completed'),
    (103, 3, 450.00,  '2026-01-20'::DATE, 'completed'),
    (104, 1, 125.50,  '2026-02-01'::DATE, 'completed'),
    (105, 2, 67.25,   '2026-02-10'::DATE, 'completed'),
    (106, 3, 215.75,  '2026-02-14'::DATE, 'pending'),
    (107, 1, 175.00,  '2026-02-15'::DATE, 'pending')
) AS t(order_id, customer_id, amount, order_date, status);

Now create a snapshot to mark this as a known-good baseline:

CREATE SNAPSHOT baseline OF shop_db;

To simulate changes over time (for testing the examples below), apply some modifications and snapshot again:

-- Simulate a data update: customer email change + new customer
UPDATE customers SET email = 'alice.j@newdomain.com' WHERE customer_id = 1;
INSERT INTO customers VALUES (6, 'Dave Miller', 'dave@example.com', 'US-East', '2026-02-16');

-- Simulate a pipeline issue: accidentally delete some orders
DELETE FROM orders WHERE order_id IN (106, 107);

-- Insert a new order
INSERT INTO orders VALUES (108, 6, 95.00, '2026-02-16', 'pending');

CREATE SNAPSHOT after_changes OF shop_db;

You now have two named snapshots (baseline and after_changes) you can use with the patterns below.

Core pattern: clone a point-in-time snapshot​

The fundamental time travel pattern is to create a temporary database from a historical snapshot, then query it alongside your current data:

-- Create a zero-copy clone of your database at a past point in time
CREATE DATABASE shop_db_yesterday FROM shop_db (
    SNAPSHOT_NAME 'baseline'
);

-- Query the historical clone
SELECT * FROM shop_db_yesterday.main.orders;

To make sure you don't unnecessary store data we clean up the database again.

DROP DATABASE shop_db_yesterday;

This uses a zero-copy clone, so no data is duplicated. The clone points to the same underlying storage objects.

To see what snapshots are available and find the right timestamp, query:

SELECT snapshot_id, created_ts, active_bytes
FROM md_information_schema.database_snapshots
WHERE database_name = 'shop_db'
ORDER BY created_ts DESC
LIMIT 10;

Comparing data across time periods​

Your operations team notices that order volume looks off this morning. Rather than waiting for a full data audit, you can instantly diff today's data against yesterday's snapshot to find new records, deleted rows, or unexpected changes — useful for anomaly detection, daily change tracking, and operational monitoring.

-- Clone yesterday's state
CREATE DATABASE shop_yesterday FROM shop_db (
    SNAPSHOT_NAME 'baseline' -- or use a timebased reference SNAPSHOT_TIME '2026-02-15 00:00:00'
);

-- Find new customers added since yesterday
SELECT c.customer_id, c.name, c.created_at
FROM shop_db.main.customers c
ANTI JOIN shop_yesterday.main.customers y
    ON c.customer_id = y.customer_id;

-- Compare daily order totals
SELECT
    'today' AS period,
    count(*) AS order_count,
    sum(amount) AS total_revenue
FROM shop_db.main.orders
WHERE order_date = CURRENT_DATE
UNION ALL
SELECT
    'yesterday' AS period,
    count(*) AS order_count,
    sum(amount) AS total_revenue
FROM shop_yesterday.main.orders
WHERE order_date = CURRENT_DATE - INTERVAL 1 DAY;

-- Detect changed records (e.g. email updates)
SELECT
    c.customer_id,
    y.email AS old_email,
    c.email AS new_email
FROM shop_db.main.customers c
JOIN shop_yesterday.main.customers y
    ON c.customer_id = y.customer_id
WHERE c.email != y.email;

DROP DATABASE shop_yesterday;

Debugging data pipeline issues​

A dashboard that was showing correct numbers yesterday is now off. You suspect a pipeline run corrupted or dropped data, but you're not sure when it happened. Time travel lets you clone the database at a known-good point and compare it to the current state to find exactly which records disappeared, changed, or were introduced incorrectly.

-- List recent snapshots to narrow down the issue
SELECT snapshot_id, created_ts, active_bytes
FROM md_information_schema.database_snapshots
WHERE database_name = 'shop_db'
    AND created_ts >= '2026-02-14 00:00:00'
ORDER BY created_ts;
-- Clone the database at a known-good time
CREATE DATABASE shop_before FROM shop_db (
    SNAPSHOT_ID 'b1ecf2f3-4567-8901-b23f-45c67890b12'
);
-- Compare row counts to spot unexpected changes
SELECT
    'before' AS state,
    count(*) AS row_count,
    count(DISTINCT customer_id) AS unique_customers
FROM shop_before.main.orders
UNION ALL
SELECT
    'current' AS state,
    count(*) AS row_count,
    count(DISTINCT customer_id) AS unique_customers
FROM shop_db.main.orders;

-- Find records that disappeared
SELECT b.order_id, b.customer_id, b.amount, b.order_date
FROM shop_before.main.orders b
ANTI JOIN shop_db.main.orders c
    ON b.order_id = c.order_id;

DROP DATABASE shop_before;

Reproducing past reports​

A stakeholder asks "why did last week's revenue report show different numbers?" Instead of guessing what data has changed since then, you can clone the exact database state from when the report ran and re-execute the same query. This is also useful for validating past analyses, debugging metric discrepancies, and ensuring reproducibility of historical results.

-- Recreate the database state from last Tuesday morning
CREATE DATABASE shop_last_tuesday FROM shop_db (
    SNAPSHOT_NAME 'baseline' -- or use a timebased reference SNAPSHOT_TIME '2026-02-15 00:00:00'
);

-- Re-run the same report query against the historical state
SELECT
    region,
    sum(amount) AS total_revenue,
    count(DISTINCT customer_id) AS active_customers
FROM shop_last_tuesday.main.orders o
JOIN shop_last_tuesday.main.customers c USING (customer_id)
WHERE order_date BETWEEN '2026-02-01' AND '2026-02-09'
GROUP BY region
ORDER BY total_revenue DESC;

DROP DATABASE shop_last_tuesday;

Creating audit checkpoints with named snapshots​

Regulatory audits, end-of-quarter financial reviews, and legal discovery often require proof of what data looked like at a specific moment. Named snapshots let you preserve the exact database state at key business milestones. Unlike automatic snapshots, named snapshots are not subject to garbage collection — they persist until you explicitly remove them. This feature is available on the Business plan.

-- Create a named snapshot at end-of-quarter close
CREATE SNAPSHOT q1_2026_close OF shop_db;

-- Months later, an auditor needs to verify the numbers
CREATE DATABASE audit_q1 FROM shop_db (
    SNAPSHOT_NAME 'q1_2026_close'
);

-- Re-run the audit query against the exact data from that moment
SELECT
    c.region,
    count(*) AS order_count,
    sum(o.amount) AS total_revenue
FROM audit_q1.main.orders o
JOIN audit_q1.main.customers c USING (customer_id)
WHERE o.order_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY c.region;

DROP DATABASE audit_q1;

To manage your named snapshots:

-- List all named snapshots
SELECT snapshot_id, snapshot_name, database_name, created_ts
FROM md_information_schema.database_snapshots
WHERE snapshot_name IS NOT NULL;

-- Rename a snapshot
ALTER SNAPSHOT q1_2026_close SET snapshot_name = 'audit_fy2026_q1';

-- Remove a snapshot name (makes it subject to garbage collection)
ALTER SNAPSHOT old_checkpoint SET snapshot_name = '';

Best practices​

  • Clean up clones promptly. Snapshot clones are zero-copy, but they may hold historical_bytes longer than necessary unless they are dropped. When they original database is deleted the clone may still hold retained_for_clone_bytes.
  • Use SNAPSHOT_TIME for exploration, SNAPSHOT_ID for precision, SNAPSHOT_NAME for re-usability. When narrowing down a time range, timestamps are convenient. Once you've identified the exact snapshot, switch to the ID to avoid ambiguity. See restoring a database to a historical snapshot.
  • Set retention to match your needs. Longer snapshot_retention_days gives you a wider time travel window but increases historical_bytes storage. See snapshot retention.
  • Use named snapshots for fixed checkpoints. Automatic snapshots are garbage-collected after the retention window. For audit or compliance points that need to persist, create a named snapshot.

See also​