Data Transformation Etl

Data Transformation & ETL

Use this skill for data ingestion, cleanup, normalization, migration, and loading workflows.

Priorities

1. Correctness of data
2. Repeatability
3. Observability
4. Recovery from partial failure
5. 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

• Input schema is validated
• Raw and normalized records are separated
• Pipeline is idempotent
• Failure boundary is explicit
• Checkpoints/watermarks are defined where needed
• Metrics and rejection counts are visible
• Loading strategy is safe for reruns and partial failures