| name | data-transformation-etl |
| description | Practical ETL rules for parsing, validation, transformation, and safe loading. |
| license | See repository LICENSE |
| user-invocable | false |
Data Transformation & ETL
Use this skill for data ingestion, cleanup, normalization, migration, and loading workflows.
Priorities
- Correctness of data
- Repeatability
- Observability
- Recovery from partial failure
- Performance at scale
Core Rules
1. Validate at every boundary
- Validate file shape, schema, headers, and required fields before heavy processing.
- Parse untrusted input defensively.
- Distinguish malformed rows from recoverable business-rule failures.
2. Normalize before business logic
- Convert transport formats into stable internal records first.
- Normalize casing, whitespace, null conventions, timestamps, units, and IDs before downstream logic.
- Keep raw input and normalized output conceptually separate.
3. Make pipelines idempotent
- Re-running the same batch should not duplicate or corrupt data.
- Prefer deterministic keys, upserts, checkpoints, or watermarks.
- Avoid pipelines that are safe only if they run exactly once.
4. Handle partial failure deliberately
- Decide whether the unit of failure is row, file, batch, or whole run.
- Log and count rejected records explicitly.
- If partial success is allowed, make the boundary visible in metrics and outputs.
5. Keep transforms readable
- Prefer a sequence of named transformation steps over one giant pipeline.
- Separate parsing, validation, enrichment, deduplication, and loading.
- Avoid clever one-liners that make data loss hard to detect.
6. Preserve provenance
- Keep enough metadata to trace where a record came from.
- Include source file, batch ID, import timestamp, or checkpoint when the system needs auditability.
- Never destroy raw provenance before the load is verified.
7. Design for scale without hiding semantics
- Stream or chunk large inputs when full in-memory processing is risky.
- Parallelize only when ordering, deduplication, and side effects remain correct.
- Optimize the hot path after correctness and restart safety are established.
Parsing Rules
- Fail fast on missing required structure.
- Be explicit about encoding, delimiter, locale, and timestamp assumptions.
- Treat schema drift as a first-class risk.
- Prefer typed records or schema validation where the stack supports it.
Transformation Rules
- Make field derivations explicit.
- Keep unit conversions centralized.
- Deduplicate using business keys, not incidental row order.
- Flag suspicious records instead of silently coercing them into validity.
Loading Rules
- Define transaction boundaries intentionally.
- Use staging tables or temporary sinks when the load must be validated before publish.
- Prefer bulk operations when they preserve correctness and failure visibility.
- Avoid per-row write loops for large imports unless the scale is genuinely small.
Incremental Sync Rules
- Use durable checkpoints or watermarks.
- Handle late-arriving and out-of-order data intentionally.
- Decide what happens when a source mutates historical data.
- Document replay strategy.
Observability
Every meaningful ETL job should expose:
- records read
- records written
- records rejected
- batches retried
- time spent per stage
- checkpoint / watermark used
Log enough context to debug a bad batch without dumping sensitive data.
Review Heuristics
Look for:
- hidden schema assumptions
- non-idempotent inserts
- silent row drops
- transforms that mix parsing with business logic
- missing checkpoints or replay strategy
- poor failure visibility
- memory-heavy processing where streaming/chunking is safer
Anti-Patterns
Avoid:
- loading raw external data straight into domain tables
- silently coercing bad records into "valid" values
- pipelines that cannot be rerun safely
- giant monolithic transform functions
- success reports that ignore rejected rows
- batch jobs with no checkpoint or audit trail
Quick Checklist
