Rollback Product Import with Snapshot Preservation

Rollback Product Import with Snapshot Preservation

Import with a mapping error can rewrite prices on thousands of products with incorrect data. Imagine loading a CSV with swapped price and quantity columns — after import, 5000 products have prices that are really stock quantities. Without a rollback mechanism, the only option is restoring from a database backup, which takes hours and brings the entire site down. We built a fault-tolerant rollback mechanism that restores the catalog to its pre-import state in minutes. Our solution relies on snapshots of data taken before import and transactional recovery.

We have implemented rollback for catalogs up to 500,000 products. Instead of full backup restore (which can take 2-3 hours), snapshot-based rollback completes in 2-5 minutes for 10,000 rows. That's a 99% time reduction — saving resources without data loss. We guarantee integrity: the rollback executes in a single transaction — either fully completes or not at all.

What Problems Does the Rollback Mechanism Solve?

Key issues during import:

• N+1 queries when updating related entities — leads to slowdown.
• Partial import on failure — catalog left in an incomplete state.
• Long table locks during mass operations.

Our approach mitigates these: batching reduces load, transactions guarantee atomicity, and snapshots enable rollback without backup.

How Does the Rollback Mechanism Work?

The strategy is simple: before import, we save a snapshot of every row that will change. The snapshot is a JSON copy of product fields (price, quantity, description, category). On error, a reverse process runs: newly created products are deleted, updated ones are restored from snapshot. Details follow.

Snapshot Before Import

Before import, we store snapshots of affected rows:

CREATE TABLE import_product_snapshots ( id bigserial PRIMARY KEY, import_id int REFERENCES import_runs(id) ON DELETE CASCADE, product_id int, operation varchar(10), -- create | update (delete handled separately) data_before jsonb, -- state BEFORE import (NULL for create) created_at timestamptz DEFAULT now() ); 

Snapshot Capture Service

class ImportSnapshotService { public function captureBeforeImport(int $importId, array $skus, int $sourceId): void { // Fetch existing product data to be modified $products = Product::whereIn('sku', $skus) ->where('source_id', $sourceId) ->get(['id', 'sku', 'name', 'price', 'qty', 'description', 'category_id', 'deleted_at', 'updated_at']); $snapshots = $products->map(fn($p) => [ 'import_id' => $importId, 'product_id' => $p->id, 'operation' => 'update', 'data_before' => json_encode($p->toArray()), 'created_at' => now()->toDateTimeString(), ])->all(); // Batch insert foreach (array_chunk($snapshots, 1000) as $chunk) { ImportProductSnapshot::insert($chunk); } } public function captureNewProduct(int $importId, int $productId): void { ImportProductSnapshot::create([ 'import_id' => $importId, 'product_id' => $productId, 'operation' => 'create', 'data_before' => null, ]); } } 

Rollback Mechanism

class ImportRollbackService { public function rollback(ImportRun $import): RollbackResult { if (!in_array($import->status, ['success', 'partial', 'failed'])) { throw new \RuntimeException('Import is not in a rollbackable state'); } if ($import->rolled_back_at) { throw new \RuntimeException('Import already rolled back'); } $restored = $deleted = 0; DB::transaction(function () use ($import, &$restored, &$deleted) { $snapshots = ImportProductSnapshot::where('import_id', $import->id) ->orderByDesc('id') // reverse order for dependencies ->get(); foreach ($snapshots as $snapshot) { if ($snapshot->operation === 'create') { // Created products — delete (soft) Product::find($snapshot->product_id)?->delete(); $deleted++; } else { // Updated products — restore previous state $before = json_decode($snapshot->data_before, true); Product::where('id', $snapshot->product_id)->update($before); $restored++; } } $import->update([ 'rolled_back_at' => now(), 'rolled_back_by' => auth()->id(), 'rollback_result' => compact('restored', 'deleted'), ]); }); return new RollbackResult($restored, $deleted); } } 

Everything runs in a single transaction — either the rollback fully completes or nothing changes. For large imports (over 50,000 rows), we use batching of 1000 records each to avoid long locks. Rollback progress is tracked in real time via a web interface.

Step-by-Step Implementation Guide

1. Create the snapshot table (SQL above).
2. Implement the ImportSnapshotService — call it before import for each set of rows.
3. Implement ImportRollbackService — invoke on error or via button.
4. Check applicability (snapshot saved, within 7 days, import not already rolled back).
5. Integrate with admin UI: rollback button and progress bar.

Rollback Applicability Conditions

Not every import can be rolled back. We check:

public function canRollback(ImportRun $import): bool { return !$import->rolled_back_at && $import->created_at->isAfter(now()->subDays(7)) && ImportProductSnapshot::where('import_id', $import->id)->exists(); } 

Why Incremental Rollback Matters?

Rolling back 100,000 rows in a single transaction locks the database for tens of seconds. The incremental approach splits the operation into batches, reducing server load. We use a rollbackInBatches method that applies snapshots in chunks and updates progress. This allows rollback without interrupting store operations.

Cascading Rollback of Related Data

Import affects not only the products table. During rollback, we automatically delete associated images, specifications, and filters. In the applySnapshot method, cascading cleanup is performed. Snapshots are kept for 7 days after a successful import, then purged by a scheduler (artisan import:cleanup-snapshots --days=7).

Comparison of Rollback Approaches

Our choice is a combination of snapshots and batching. This is optimal for most online stores.

What's Included in Development

• Snapshot service (PHP/Laravel)
• Rollback mechanism with batching
• Admin interface (rollback button, progress bar)
• Applicability checks and cascading deletion
• TTL cleanup of snapshots
• API documentation and team training

Implementation Timeline

• Basic functionality (snapshot + rollback in transaction) — 2 days
• Batching, cascading, checks — +1 day
• Admin UI and testing — +1 day

Final timeline: 4 to 6 days depending on catalog complexity.

Request a free consultation — we'll assess your project in one day. We guarantee rollback will not lose a single product. We have completed 50+ successful import implementations for catalogs from 1,000 to 500,000 products. Contact us to discuss details.

According to the PostgreSQL documentation on transactions, snapshot isolation ensures data consistency. We apply this principle in our solution.