Overview
Bulk imports combine throughput and data-quality risks. Directly inserting untrusted rows into production tables can create partial imports, duplicate records, invalid relationships, or long locks. A staging workflow separates ingestion from acceptance.
Start with the workload and the invariant
Create a staging table that preserves the source payload and records import batch ID, row number, validation status, and error details. Load data using a method appropriate to the environment. LOAD DATA can be fast, but file permissions, local versus server-side behavior, delimiter handling, and security settings must be understood.
Practical example
CREATE TABLE import_staging (
batch_id BIGINT NOT NULL,
source_row BIGINT NOT NULL,
external_id VARCHAR(100),
raw_payload TEXT,
validation_status VARCHAR(20) NOT NULL DEFAULT 'pending',
error_message VARCHAR(500) NULL,
PRIMARY KEY (batch_id, source_row)
);
Validate required fields, types, lengths, enumerated values, and references before merging. Normalize dates and decimal values explicitly. Do not rely on permissive SQL modes to coerce malformed input silently. Keep rejected rows available for correction and reporting.
Trade-offs and failure modes
Deduplicate using a defined business key, not merely row position. Decide whether a repeated external ID means update, ignore, or reject. Use unique constraints in the target table as the final guard. If the merge is large, process it in batches and record progress so the job can resume.
Verification and rollout
Make the batch atomic at the business level where possible. For extremely large imports, a single transaction may be impractical; instead use batch states and a clear publication point so users do not see half-imported data as complete. Prevent two imports for the same source from racing.
Engineering checklist
Report totals for received, accepted, rejected, inserted, updated, and skipped rows. Test malformed encodings, duplicate keys, missing references, interrupted runs, and reruns. A reliable import is observable and repeatable, not just fast.
Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.