Snapshots

​

What Makes a Snapshot

​

Key Characteristics

1. Referentially Complete: Every row includes all its foreign key dependencies
2. Point-in-Time: Captures database state at a specific moment
3. Self-Contained: Includes schema metadata for validation
4. Portable: Can be restored to any database with matching schema
5. Versioned: Multiple versions can coexist for the same snapshot name

​

Snapshot Anatomy

​

1. Manifest (manifest.json)

{
  "version": "1.0",
  "created_at": "2024-01-29T10:30:00Z",
  "source": {
    "host": "prod-db.internal",
    "database": "myapp",
    "database_name": "myapp_production"
  },
  "stats": {
    "total_rows": 2847,
    "table_count": 23,
    "warning_count": 0,
    "extraction_duration_ms": 45320,
    "uncompressed_size": 12543200,
    "compressed_size": 3145600
  },
  "config": {
    "root_table": "public.users",
    "max_upstream_depth": 5,
    "max_downstream_depth": 10,
    "sample_mode": "random"
  },
  "schema_hash": "a1b2c3d4e5f6..."
}

• Identifies the snapshot uniquely
• Records extraction context for debugging
• Stores git metadata for reproducibility
• Tracks performance metrics

​

2. Schema (schema.json)

• Validate target database compatibility before restore
• Detect schema drift between snapshot and target
• Document database structure at extraction time

​

3. Data Files (tables/*.csv.gz)

tables/
├── public.users.csv.gz                (234 KB, 847 rows)
├── public.organizations.csv.gz        (12 KB, 45 rows)
├── public.orders.csv.gz               (523 KB, 2,134 rows)
├── public.line_items.csv.gz           (891 KB, 8,912 rows)
├── public.products.csv.gz             (156 KB, 432 rows)
└── public.payment_methods.csv.gz      (34 KB, 178 rows)

• Standard CSV with header row
• Gzip compressed for efficiency
• NULL values represented as empty fields
• Special characters escaped per RFC 4180

​

4. Warnings (warnings.json) - Optional

{
  "warnings": [
    {
      "code": "LIMIT_REACHED",
      "message": "per_table limit (1000) reached for table 'audit_logs'",
      "table": "public.audit_logs",
      "severity": "warning"
    }
  ]
}

• Document extraction issues without failing
• Warn users about partial data
• Aid in debugging configuration problems

​

Snapshot Lifecycle

​

1. Creation

basecut snapshot create \
  --config basecut.yml \
  --name "dev-seed" \
  --source "$BASECUT_DATABASE_URL"

1. CLI reads configuration from basecut.yml
2. Connects to source database
3. Executes root queries to get seed rows
4. Recursively follows foreign keys (upstream + downstream)
5. Applies anonymization rules to PII fields
6. Compresses data to CSV.gz files
7. Generates manifest and schema metadata
8. Uploads to configured storage (S3/GCS/local)

​

2. Storage

s3://my-basecut-snapshots/
└── team-a/                         # optional prefix
    └── snapshots/
        └── 8f4f4b0e-fbc7-4f70.../ # job id
            ├── manifest.json
            ├── schema.json
            ├── warnings.json (optional)
            └── tables/
                ├── public.users.csv.gz
                └── public.orders.csv.gz

​

3. Restoration

basecut snapshot restore dev-seed:latest --target "$BASECUT_DATABASE_URL"

1. CLI resolves snapshot version (:latest → brave-lion)
2. Downloads manifest and schema from storage
3. Validates target database schema compatibility
4. Determines insertion order (respecting foreign keys)
5. Inserts data in dependency order (or uses an automatic two-phase fallback for eligible nullable FK cycles)
6. Verifies referential integrity
7. Reports completion with row counts

​

Guarantees

​

Referential Integrity

• Every foreign key constraint will be satisfied after restore
• No “dangling references” or broken relationships
• Can restore to an empty database without errors

• Basecut tracks all foreign key relationships during extraction
• Before including a row, ensures all referenced rows are included
• If a parent is missing, adds it (even beyond depth limits)

​

Schema Preservation

• Restore validates target schema matches snapshot schema
• Detects missing tables, columns, or type changes
• Prevents data corruption from schema drift

​

Size Estimation

Snapshot size ≈ (Raw data size × compression ratio) + overhead

Where:
- compression ratio ≈ 0.1 to 0.3 (gzip)
- overhead ≈ 50-100KB (manifest + schema)

​

Performance Characteristics

​

Creation Time

​

Snapshot vs Backup

• Seeding development databases
• Sharing realistic test data with team
• Reproducing production bugs locally

• Disaster recovery
• Point-in-time recovery
• Compliance archiving

​

Next Steps