Intent
Translate CSV inputs into normalized tables with upsert semantics.
When to use
- Upstream systems deliver CSV drops.
- You need database-backed reporting or analytics.
- CSV files are the contract boundary.
Core mechanics
- Parse and validate CSV inputs.
- Load into a staging table.
- Upsert into canonical tables and handle deletes.
Implementation checklist
- Define column mappings and data types.
- Validate required fields and quarantine bad rows.
- Load to staging with idempotent keys.
- Upsert into target tables.
- Report insert/update/delete counts.
Failure modes and mitigations
- Column mismatch -> enforce schema validation.
- Duplicate keys -> define stable identifiers.
- Partial loads -> use transactions and checkpoints.
Observability and validation
- Rows staged, inserted, updated, deleted.
- Rejected row counts with reasons.
Artifacts
- Schema mapping documentation.
- Rejected rows file.
- Sync summary report.