ETL Design
ETL is correctness under change: schema drift, partial loads, retries, and reconciliation with upstream systems.
When to Offer This Workflow
Trigger conditions:
- Batch loads into warehouse or data lake
- Choosing between CDC, snapshots, and incremental watermarks
- Missing rows, duplicates, or inconsistent aggregates downstream
Initial offer:
Use six stages: (1) source contract, (2) extract strategy, (3) transform rules, (4) load & dedupe, (5) validation, (6) operations & backfill). Confirm batch window and SLA.
Stage 1: Source Contract
Goal: Document schema, primary keys, change indicators (updated_at, CDC log position), and access constraints (rate limits, read replicas).
Stage 2: Extract Strategy
Goal: Full dump vs incremental watermark vs CDC—trade freshness, source load, and complexity.
Practices
- CDC for large sources; snapshots for small or infrequent tables
Stage 3: Transform Rules
Goal: Deterministic transforms; surrogate keys; business rules versioned; handling of deletes (tombstones vs hard deletes).
Stage 4: Load & Dedupe
Goal: Upsert keys; partitions; rerunnable jobs with same batch id producing the same outcome (idempotent load).
Stage 5: Validation
Goal: Row counts, checksums, key uniqueness, referential checks; alert on threshold breaches.
Stage 6: Operations & Backfill
Goal: Replay by date range; monitor lag; dead-letter or quarantine bad rows with reason codes.
Final Review Checklist
- Source contract and keys documented
- Extract mode matches SLA and source constraints
- Transforms deterministic and versioned
- Idempotent load strategy
- Validation and reconciliation defined
Tips for Effective Guidance
- Plan for late-arriving facts and slowly changing dimensions in analytics paths.
- Pair with data-pipelines for orchestration and monitoring.
Handling Deviations
- Near-real-time: document micro-batch or streaming semantics separately.