Bulk data import pipeline
CSV upload, validation, transformation, database load with rollback on error.
Every data-driven business imports batches of CSV files — customer lists, product catalogs, transaction histories. This template shows a production-grade import pipeline that handles the hidden complexity: file format validation, duplicate detection, business-rule checks, staging-table reconciliation, and transaction rollback if anything goes wrong. Notice the decision points where the pipeline can halt and report specific errors — each one is a place operators need to fix the source data and retry.
Without a pipeline like this, imports often fail halfway, leaving the database in an inconsistent state. Teams spend hours debugging the partial import and manually cleaning it up. This diagram makes visible all the quality gates that prevent that.
When to use this template
- Designing data imports — plan the validation steps, staging logic, and rollback behavior before you code so the whole team understands what happens if the reconciliation check fails.
- Onboarding new operators — show them the import flow so they understand why imports take time (validation, transformation, reconciliation) and where to look when something goes wrong.
- Compliance and audit — document that every import is logged, validated, and can be rolled back. This satisfies auditors and regulators asking "how do you ensure data integrity?"
How to adapt it
Start by adding your specific validation rules and data sources:
- Replace Validate business rules with your actual checks (email format, customer tier exists, product SKU in catalog, date range is valid).
- Add a data transformation step if you need to map columns (legacy CSV column names to new schema), split fields (full name to first + last), or enrich rows (look up country from zip code).
- Insert a deduplication strategy if you're upserting (updating existing rows and inserting new ones) instead of strict insert-only.
Visual edits regenerate clean Mermaid code, so you can customize the diagram for your import workflow and share it with data ops and engineering.
Mermaid code
Copy it anywhere Mermaid is supported — GitHub, Notion, or your docs.
flowchart TD
A[User uploads CSV file] --> B[Validate file format]
B -->|Invalid format| C[Return error message]
C --> A
B -->|Valid| D[Parse CSV rows]
D --> E{Check for duplicates?}
E -->|Duplicates found| F[Log duplicate rows]
F --> G{Proceed anyway?}
G -->|No| H[Cancel import]
H --> A
G -->|Yes| I[Transform and enrich]
E -->|No duplicates| I
I --> J[Validate business rules]
J -->|Validation failed| K[Report error rows]
K --> G
J -->|All valid| L[Begin transaction]
L --> M[Load into staging table]
M --> N{Reconciliation check}
N -->|Count mismatch| O[Rollback transaction]
O --> K
N -->|Match| P[Move to production table]
P --> Q[Commit transaction]
Q --> R[Send success notification]
R --> S[Download import report]
Frequently asked questions
- What does a bulk data import pipeline show?
- It maps every step a file takes from upload to production database: validation (format, business rules), deduplication, transformation, staging, and reconciliation. Each step has decision points where the pipeline can halt and report errors. It makes clear where data quality checks happen and how rollback protects the database from partial or corrupt imports.
- Why use a staging table instead of loading directly to production?
- A staging table is a temporary landing zone where you can validate the data, reconcile row counts, and check for conflicts before moving it to production. If validation fails or the environment changes during the import, you roll back the staging table without touching live data. It's the only safe way to import large datasets.
- What happens if the import fails halfway through?
- The transaction rolls back completely — all rows are undone, as if the import never started. This prevents the database from ending up in a partially-imported state. The pipeline logs which rows failed and why, so operators can fix the source CSV and retry without manual cleanup.
- How do I handle duplicate detection across multiple import runs?
- Add a unique constraint or check for rows with duplicate business keys (customer ID, email, order number). The pipeline detects duplicates before loading and either rejects the entire import or allows manual override with logging. Some systems update existing rows and insert new ones — show this as a separate 'Upsert' path in the diagram.